• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

Java JDBC application

I wrote java JDBC file  for connecting Database and create the tables,It did the table creation and insertion,but I`m having difficulty in SQL data retrival method:This is the method in my JDBC.java file:
 
public void  getAuthorData(String Sql) {

        try {
           PreparedStatement stmt = con.prepareStatement("SELECT SUBJECTAREA" +"FROM AUTHOR"+"WHERE LASTNAME=?");
                 stmt.setString(1,"EDWARDS");
                 ResultSet rs = stmt.executeQuery();
                  }

         catch (SQLException ex) {
            printException(ex);
         }

    }
And I have to run this from my "main" program, but I can`t make that connection between JDBC and main program file, here`s the
main:

import java.io.*;
import java.sql.*;
import cs1.Keyboard;
public class AuthorTable
{

public static void main(String[] args){


JDBCConnect c = new JDBCConnect("username","password");
Author[] a = c.getAuthorTable(13);
//Author newAuthor = new Author("EDWARDS","MIKE","156758932","PHYSICS");
//c.insertAuthor(newAuthor);
String SQL = "SELECT SUBJECTAREA FROM AUTHOR WHERE LASTNAME  = EDWARDS";
c.getAuthorData(SQL);
System.out.println(a[0]);
System.out.println(a[1]);
System.out.println(a[2]);
System.out.println(a[3]);
System.out.println(a[4]);
System.out.println(a[5]);
System.out.println(a[6]);
System.out.println(a[7]);
System.out.println(a[8]);
System.out.println(a[9]);
System.out.println(a[10]);
System.out.println(a[11]);
System.out.println(a[12]);
  }
}
Could you help me?
Thanks


0
edeita2
Asked:
edeita2
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
dualsoulCommented:
hm...please post code where you create connection

>  PreparedStatement stmt = con.prepareStatement("SELECT SUBJECTAREA" +"FROM AUTHOR"+"WHERE LASTNAME=?");

i you sure it's write statement? it seems you haven't spaces between parts of SQL, how about this:
..................
preparedStatement stmt = con.prepareStatement("SELECT SUBJECTAREA FROM AUTHOR WHERE LASTNAME=?");
.......................
0
 
JavatmCommented:
Friend;

May be this could help, a previous topic a very simple jdbc:
http://www.experts-exchange.com/Programming/Programming_Languages/Java/Q_20776556.html

JAVATM
0
 
SuperKarateMonkeyCommented:
I don't understand why you're passing that String variable into the getAuthorData() method.

Also, what is this JDBCConnect object you're creating?

And where are you getting the Connection object, con?

This is not code you're sending us, it's a mishmash.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
edeita2Author Commented:
Ok here it is:I`m already done with JDBCconnection and created /displayed the tables(my first method: getAuthorTable) and did the insertion, it`s working too(my second method: insertAuthor), but my final method :getAuthorData , I can`t make connection between my main and JDBC, I passed String, cos` aren`t we supposed to send the sql as a String , then it`ll retrieve the necessary data?
here`s my full JDBC file:
// DB connectivity for Oracle at kuniac.rutkers
// Cloned from sun's "simpleselect.java"
import java.net.URL;
import java.sql.*;
import java.io.*;

public class JDBCConnect {

    private Connection con;

    public JDBCConnect (String username, String password) {

        try {

            // Load the jdbc-odbc bridge driver
            Class.forName ("oracle.jdbc.driver.OracleDriver");

            con =
DriverManager.getConnection
("jdbc:oracle:thin:@kuniac.rutkers.edu:1521:jim9",username,password);
            }

        catch (SQLException ex) {
            printException(ex);
        }

        catch (java.lang.Exception ex) {

            // Got some other type of exception.  Dump it.
            ex.printStackTrace ();
            System.exit(0);
        }
    }

 private void printException(SQLException ex) {
        System.out.println ("\n*** SQLException caught ***\n");

        while (ex != null) {
            System.out.println ("SQLState: " + ex.getSQLState ());
            System.out.println ("Message:  " + ex.getMessage ());
            System.out.println ("Vendor:   " + ex.getErrorCode ());
            ex = ex.getNextException ();
            System.out.println ("");
            System.exit(0);
        }

    }
  public Author[] getAuthorTable(int maxNumRows) {

        Author[] tmpAuthors = new Author[maxNumRows];

        try {
            // Create a Statement object so we can submit
            // SQL statements to the driver
             Statement stmt = con.createStatement();

            // Submit a query, creating a ResultSet object
            ResultSet rs = stmt.executeQuery ("SELECT * FROM AUTHOR");

            int row = 0;
            while (rs.next () && row < maxNumRows) {
                tmpAuthors[row] = new
            Author(rs.getString(1),
            rs.getString(2),
            rs.getString(3),
            rs.getString(4));
                ++row;
            }

        }
        catch (SQLException ex) {
            printException(ex);
        }
        return tmpAuthors;
    }
  public  void insertAuthor(Author newAuthor){


       try{
       PreparedStatement stmt = con.prepareStatement
       ("INSERT INTO AUTHOR VALUES(?,?,?,?)");
       stmt.setString(1, newAuthor.LASTNAME);
       stmt.setString(2, newAuthor.FIRSTNAME);
       stmt.setString(3, newAuthor.AUTHOR_SSN );
       stmt.setString(4, newAuthor.SUBJECTAREA);
       stmt.executeUpdate();
       }
      catch (SQLException ex) {
            printException(ex);
        }

     }
 public void  getAuthorData(String Sql) {

        try {
           PreparedStatement stmt = con.prepareStatement
           ("SELECT SUBJECTAREA" +"FROM AUTHOR"+"WHERE LASTNAME=?");
                 stmt.setString(1,"EDWARDS");
                 ResultSet rs = stmt.executeQuery();
                  }

         catch (SQLException ex) {
            printException(ex);
         }

    }
   
}
----------------------------------------------------------------------------------------------------
Class file:/* File Name:Author.java
Author:
Author`s address:
Program`s purpose:Simply declares the variables and creates Author object
Creation Date:10/18/2003
*/

public class Author{

  String LASTNAME,
  FIRSTNAME,
  SUBJECTAREA,
  AUTHOR_SSN ;



Author(String last, String first, String ssn, String subject)
{
  LASTNAME = last;
  FIRSTNAME = first;
  AUTHOR_SSN  = ssn;
  SUBJECTAREA = subject;
}
public String toString()
{
String row;
row = LASTNAME+ "\t" +FIRSTNAME+ "\t" +AUTHOR_SSN + "\t" +SUBJECTAREA;

return row;
}
}
------------------------------------------------------------------------------------------------------
 main file which runs the program
/*Author:
Author`s address:
Program`s purpose:Runs the Author object
Creation Date:10/18/2003
*/
import java.io.*;
import java.sql.*;
import cs1.Keyboard;
public class AuthorTable
{

public static void main(String[] args){


JDBCConnect c = new JDBCConnect("jim1","jim");
Author[] a = c.getAuthorTable(13);
//Author newAuthor = new Author("EDWARDS","MIKE","156758932","PHYSICS");
//c.insertAuthor(newAuthor);
System.out.println(a[0]);
System.out.println(a[1]);
System.out.println(a[2]);
System.out.println(a[3]);
System.out.println(a[4]);
System.out.println(a[5]);
System.out.println(a[6]);
System.out.println(a[7]);
System.out.println(a[8]);
System.out.println(a[9]);
System.out.println(a[10]);
System.out.println(a[11]);
System.out.println(a[12]);
  }
}

0
 
SuperKarateMonkeyCommented:
First, the String you pass into the the getAuthorData() method never actually gets used.  That's why I was asking about it.  You pass it in and then you ignore it.  Also, your getAuthorData doesn't actually do anything.  Sure, you execute a query, but then you just throw it away, passing back nothing.  It's the programming equivalent of masturbation :D

It's possible that the garbage collector sees this and GC's your JDBC connection, so you might want to have your method actually pass something back.  But that's only about 10% likely that's really the problem.  It's more likely that your connection is just closed by the time you get there.  Try checking that it's not null and that it's not closed in the getAuthorData() method.
0
 
oktexasguyCommented:
SuperKarateMonkey(DeathCar?) is right.  The getAuthorData() method has a return type of void and does nothing with the string passed in.

 public Author getAuthorData() {
        Author toReturn = null;
        try {
           PreparedStatement stmt = con.prepareStatement
              ("SELECT SUBJECTAREA" +"FROM AUTHOR"+"WHERE LASTNAME=?");
           stmt.setString(1,"EDWARDS");
           ResultSet rs = stmt.executeQuery();
           while (rs.next()) {
              toReturn = new Author( [get rs info here to populate your Author object] );
            }
         }

         catch (SQLException ex) {
            printException(ex);
         }
         return toReturn;
    }
0
 
oktexasguyCommented:
try the code above
0
 
edeita2Author Commented:
I really appreciate your help, I think I am interpreting the flow wrong, anyway I tried the code you modified, it compiled right , but when I tried to run it from the main , it gave me exception..here`s my main:
import java.io.*;
import java.sql.*;
import cs1.Keyboard;
public class AuthorTable
{

public static void main(String[] args){


JDBCConnect c = new JDBCConnect("jim1","jim");
Author[] a = c.getAuthorTable(13);
//Author newAuthor = new Author("EDWARDS","MIKE","156758932","PHYSICS");
//c.insertAuthor(newAuthor);
Author authorData = c.getAuthorData();
System.out.println(a[0]);
System.out.println(a[1]);
System.out.println(a[2]);
System.out.println(a[3]);
System.out.println(a[4]);
System.out.println(a[5]);
System.out.println(a[6]);
System.out.println(a[7]);
System.out.println(a[8]);
System.out.println(a[9]);
System.out.println(a[10]);
System.out.println(a[11]);
System.out.println(authorData);
  }
}
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now