Solved

Java JDBC application

Posted on 2003-11-18
8
416 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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

778 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