jQuery in Action, 3rd edition
The moose likes JDBC and Relational Databases and the fly likes Prepared Statement null pointer exception help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Prepared Statement null pointer exception help" Watch "Prepared Statement null pointer exception help" New topic

Prepared Statement null pointer exception help

Shari Griffin

Joined: Jan 07, 2004
Posts: 6
Sorry for the newb question but I'm really stumped and not too good when it comes to this jdbc stuff.
All I'm trying to do is get a resultset from a "select *" query. I want to write the values to another table with an insert statement using the prepared statement.
My output ends up looking like this:

> java ManualCreditInsertion
debtornum: 12345 833
ManualCreditInsertion class threw a SQL exception.
at ManualCreditInsertion.main(ManualCreditInsertion.java:28)
And my code looks like this:

import java.sql.*;
public class ManualCreditInsertion {

public static void main (String[] args) {
try {
String debtornum, amount;
Class.forName("oracle.jdbc.driver.OracleDriver");// Establish the database connection
String url = "jdbc racle:thin:@xxxxxxx:1521:xxxxxxx"; // Setup for xxx, will be XXXXX in prod
Connection conn = DriverManager.getConnection(url,"xxxxxxxx","xxxxxxx");

Statement s = conn.createStatement() ;
ResultSet rs;
String strSQL = new String("SELECT * FROM SKG_MANUALCREDIT");

rs = s.executeQuery(strSQL) ;
PreparedStatement pstmt_insertData;
pstmt_insertData = null;

while (rs.next() != false)
debtornum = rs.getString("DEBTORNUM");
amount = rs.getString("AMOUNT");
System.out.println("debtornum: " + debtornum + " " + amount);

pstmt_insertData.setString(1, debtornum);
pstmt_insertData.setString(2, amount);
pstmt_insertData = conn.prepareStatement("INSERT INTO SKG_FTCREDIT(DEBTORNUM,AMOUNT) VALUES(?,?)");

} catch (Exception e) {
System.err.println("ManualCreditInsertion class threw a SQL exception. ");

I "x'd" out the dbconn for obvious reasons. I'm sitting here wading through the "Java Programming with Oracle JDBC" book and I can't find an example of what I want to do.... have tried Google and everything.
How do you do a select and then write the results from that select using an insert (writing the values to another table)?

Shari<br />.: <a href="http://www.tekchic.com" target="_blank" rel="nofollow">www.tekchic.com</a> :.
Shari Griffin

Joined: Jan 07, 2004
Posts: 6
I got it to work -- but it doesn't make sense to me...
I had to put the line:
pstmt_insertData = conn.prepareStatement("INSERT INTO SKG_FTCREDIT(DEBTORNUM,AMOUNT) VALUES(?,?)");
before where I set the data.... that seems backwards to me...wouldn't you want to set it first and then it would know where to plug the values in into that insert statement?
I had to delete the conn.close() too, or it would just write one record. This is no fun learning on your own without any assistance, arrgh. Where should one normally put the conn.close()?
Wayne L Johnson
Ranch Hand

Joined: Sep 03, 2003
Posts: 399
There are a lot of good JDBC tutorials on the web, all you have to do is search on google or go to Java home and poke around.
As for the prepared statement, one of it's purposes is to provide a "pre-compiled" statement that can be used multiple time. But before you can use it you have to define it. When its declared:
pstmt_insertData = null;
the prepared statement is null, and Java won't let you call methods on a null object. You create it--passing in the desired SQL--and then can re-use it multiple times.
As for closing your connections (and statements and resultsets), you should do it when you are done with them, usually in the "finally{ }" block of the try/catch. This ensures that it will happen regardless of whether the code executed properly or threw an exception.
In your original code, the "conn.close();" sits inside of the "while()" statement. So what happens is that you return the first set of results, you
do the prepared statement stuff, and then you close the connection. Closing the connection will close all of the resources, so the next time you try and go through the loop the resultset will be closed. That's why you only got one record.
Also, you need to do a "pstmt_insertData.executeUpdate()" after setting the values.
The last thing is only a style issue, but you could re-do the "while()" as:

It's logically equivalent to what you have, but a little simpler to read.
I know it sounds like a lot to keep in mind, but once you've done JDBC a few times it'll become much easier.
Wayne L Johnson
Ranch Hand

Joined: Sep 03, 2003
Posts: 399
Here's a good skeleton to follow when doing JDBC stuff:

This works equally well with a PreparedStatement instead of a Statement. You define all of the database objects outside of the try/catch block so they are visible in the finally block. You close all of your database resources in order (result set, statement, database) within the finally block.
With some databases I've found that it's not necessary to close the resultset, but usually it can't hurt and I think it's a good habit to have.
You may need to make some modifications, such as when you need to run two separate queries using the same statement, but this is a good pattern to use.
If you don't close the database resources then Java will garbage collect the objects when they are no longer in scope, but the database may still keep them open. For example, if you don't close resultsets (and/or statements), the database may run out of cursors, even though the Java application has closed and garbage collected it's stuff.
Anyway, good luck. You'll get the hang of it.
Shari Griffin

Joined: Jan 07, 2004
Posts: 6
Thanks so much for the info and the template! I'm working a bit with the O'Reilly "Java Programming with Oracle JDBC" book too, some of the chapters are a bit too advanced for me, but the chapters on statements, resultsets, and prepared statements are helping out.
Thanks for the tips too, I made all the changes you suggested and it looks a lot cleaner now
I agree. Here's the link: http://aspose.com/file-tools
subject: Prepared Statement null pointer exception help
It's not a secret anymore!