Java JDBC/SQL help please

[Platypus]

If anyone is familiar enough with manipulating SQL resultsets please AIM me or PM me as soon as you can.

 

[Platypus]

desperation bump

 

[znaps]

What's the problem? Look through the ResultSet by doing:

while (rs.next(){
    Object o = rs....
    // do something with o
}

 

[Platypus]

Originally posted by: znaps
What's the problem? Look through the ResultSet by doing:

while (rs.next(){
    Object o = rs....
    // do something with o
}

I need to check for the existance of something in the result set, then if it's true do an insert

 

[znaps]

Where might this "something" be in the resultsset? Will you have one row or multiple rows? Will the result set always have the same number of columns?

 

[kamper]

Are you looking to insert something directly into the ResultSet or will you be doing it manually with an INSERT query? If you want to manipulate a ResultSet and have it reflected in the database you probably have to specify certain properties of the ResultSet when calling Connection.createStatement(int, ...)

But this sort of thing will likely be dependent on the specific implementation of the driver so you might have to fool with it for a bit.

 

[Platypus]

It will be a huge list I have to sort through, I want to match a last name in a database, if it exists, then ignore, if it doesn't then insert it.

 

[Kilrsat]

Originally posted by: CorporateRecreation
It will be a huge list I have to sort through, I want to match a last name in a database, if it exists, then ignore, if it doesn't then insert it.

So you did something like:
ResultSet rs = stmt.executeQuery("SELECT * From BigTable");

And you want to go through the set looking for a specific entry in a specific column?

Just trying to make sure I understand the problem before diving into solutions.

 

[kamper]

I'd personally make that two queries and not bother with fancy ResultSet stuff. Just a SELECT and, if the ResultSet is empty, an insert. The only thing is you need to enforce a transaction on those two queries to make sure you don't do the insert twice. That sort of thing can be accomplished at the Statement level if I remember correctly. Or can you actually fashion a single sql statement that will conditionally insert the name?

If you want to do it all using the ResultSet check out the javadoc page for it and Connection and see what options you have to use when creating the Statement so that the ResultSet is transaction safe (you'd probably have to lock the whole table) and can be updated.

 

[mundane]

SQL noob here ... but assuming your table is keyed on last name (a big assumption, but it could happen, and might be the case), couldn't you just do an INSERT IGNORE?

 

[Platypus]

Here is some pseudocode:

try{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection myCon = DriverManager.getConnection(edited out);

String query = "SELECT * FROM CUSTOMER WHERE CUSTLNAME ='" + name + "'";
statement = myCon.createStatement();
rs = statement.executeQuery(query);

while(rs.next())
{

db = rs.getString("custlname");
if(db.equals(name))
{
System.out.println("Already in database!");
}

//statement.close();

else

{
String query2 = "SELECT max(custID) from CUSTOMER";
statement2=myCon.createStatement();
rs2=statement.executeQuery(query2);
id = rs2.getString(1) + 1;
statement.executeUpdate("INSERT INTO Customers " +
"VALUES (id,custlname, custfname, address, zip)";
}
}

 

[Kilrsat]

Originally posted by: CorporateRecreation
Here is some pseudocode:

try{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection myCon = DriverManager.getConnection(edited out);

String query = "SELECT * FROM CUSTOMER WHERE CUSTLNAME ='" + name + "'";
statement = myCon.createStatement();
rs = statement.executeQuery(query);

while(rs.next())
{

db = rs.getString("custlname");
if(db.equals(name))
{
System.out.println("Already in database!");
}

//statement.close();

else

{
String query2 = "SELECT max(custID) from CUSTOMER";
statement2=myCon.createStatement();
rs2=statement.executeQuery(query2);
id = rs2.getString(1) + 1;
statement.executeUpdate("INSERT INTO Customers " +
"VALUES (id,custlname, custfname, address, zip)";
}
}

Why not just use a more refined intial query?

SELECT * From Customer WHERE CUSTLNAME = "lname" AND CUSTFNAME = "fname" AND ... ;
Then
if (rs.last()) {
//rs.last() returns false if the resultset is empty
System.out.println("already in db");
} else {
//Do your insert stuff
}

You would still need to set the appropriate transaction level the insure that this will always give correct results, but it looks easier than what you're doing. Remember, the database is optimized for searching its data already.

 

[manly]

My SQL is a bit rusty, but that's the default behavior for the INSERT statement anyway, assuming lname is a candidate key (i.e. create a secondary index over lname, or declare it UNIQUE).

Allow the database to generate a primary key for you (i.e. an Oracle sequence or TSQL identity).

 

[kamper]

Kilsrat's logic looks like what you want. You don't need a second Statement for the next query. I know you are only showing a quick example but in practice you should make a try/finally block immediately following the creation of any database related resource and close the resource in the finally block. Also, I don't know what your end use is but your method of establishing a connection is really only a possibility for a single user app that does not access the database very often. Anything with a chance of threading and concurrent database access should be using a connection pool and transactions.

 

[Platypus]

thank you all for your contributions, Kilrsat simplified my life by 100%

 

[Koing]

Originally posted by: kamper
Kilsrat's logic looks like what you want. You don't need a second Statement for the next query. I know you are only showing a quick example but in practice you should make a try/finally block immediately following the creation of any database related resource and close the resource in the finally block. Also, I don't know what your end use is but your method of establishing a connection is really only a possibility for a single user app that does not access the database very often. Anything with a chance of threading and concurrent database access should be using a connection pool and transactions.

Hey how wouldyou go about doing this then?

Just asking as I got to do a project and adding threading and concurrent db access would earn me more marks.

Thanks.

Koing

 

[AmigaMan]

Originally posted by: Koing

Originally posted by: kamper
Kilsrat's logic looks like what you want. You don't need a second Statement for the next query. I know you are only showing a quick example but in practice you should make a try/finally block immediately following the creation of any database related resource and close the resource in the finally block. Also, I don't know what your end use is but your method of establishing a connection is really only a possibility for a single user app that does not access the database very often. Anything with a chance of threading and concurrent database access should be using a connection pool and transactions.

Hey how wouldyou go about doing this then?

Just asking as I got to do a project and adding threading and concurrent db access would earn me more marks.

Thanks.

Koing

You'd first create a pool of connections to your db, let's say 5, and store them in a hashtable. Then create a bunch of threads that block until they're all told to start. When each thread is started, it would grab an available connection to your db from your pool and do it's business. If all the db conns from your pool of 5 are taken, then the thread would wait() until another thread is done. When a connection becomes available, the previously waiting thread grabs a connection, does it's business, and returns the connection to the pool. Then it does a notifyAll() to tell any other waiting threads that they should wake up and try to get a connection from the pool. The thread then exits gracefully and the process continues until all the threads are done.

This is all quite academic, you still have to worry about synchronization issues and actually implementing it. There are also open source libraries out there that take care of a lot of this for you. But it sounds like this is a college class project and I doubt the prof would look kindly on just using a library. He/she would probably want to know if you understood the principles behind the library.