Solved

Java JDBC application

Posted on 2003-11-18
8
417 Views
Last Modified: 2013-11-23
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
Comment
Question by:edeita2
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 15

Expert Comment

by:dualsoul
ID: 9772959
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
 
LVL 15

Expert Comment

by:Javatm
ID: 9773939
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
 
LVL 2

Expert Comment

by:SuperKarateMonkey
ID: 9774197
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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:edeita2
ID: 9774341
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
 
LVL 2

Assisted Solution

by:SuperKarateMonkey
SuperKarateMonkey earned 50 total points
ID: 9774474
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
 
LVL 1

Expert Comment

by:oktexasguy
ID: 9774804
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
 
LVL 1

Accepted Solution

by:
oktexasguy earned 75 total points
ID: 9774823
try the code above
0
 

Author Comment

by:edeita2
ID: 9775110
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Notify sent to other threads in Java 9 34
hashmap order 17 37
Java 8 to Java 6 8 22
Tagging and Merging on Branch 1 30
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question