aspose file tools*
The moose likes JDBC and the fly likes Insert & Update Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Insert & Update" Watch "Insert & Update" New topic
Author

Insert & Update

Drew Lane
Ranch Hand

Joined: May 13, 2001
Posts: 296
Disclaimer: I'm not much of a database guy - I just know how to do some basic SQL stuff in Java w/ MySQL.

I have created a table which will store user info (ie. name, address, etc.)

How should I test to see if the record already exists?

Should the first thing I do be a SELECT statement to see if the user record already exists before I do an INSERT or UPDATE?

Or can I just do an INSERT everytime I want to completely overwrite a record? (I don't want to create a bunch of unnecessary records, however)

A little pseudo code might be very helpful here.

Thanks,

Drew
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Drew,
In the rest of the post, I assume the primary key of this table is user_id. Please subsitute the real key for that.

I agree that it is best to not put multiple rows for the same user in the database. In fact, the primary key will prevent you from doing so because the rows will not be unique. There are two approaches you can use:
1) Run "select count(*) from table where user_id = ?". If the result is non-zero, update the existing row. Otherwise add a new one.
2) Insert a new record. If it throws a duplicate key exception, you know the user exists.

I favor the first option as it is cleaner. The second one is to keep in mind if you have a performance issue and most users are not duplicates.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Drew Lane
Ranch Hand

Joined: May 13, 2001
Posts: 296
Originally posted by Jeanne Boyarsky:

1) Run "select count(*) from table where user_id = ?". If the result is non-zero, update the existing row. Otherwise add a new one.


I found this code also, and it seems to do the same thing:


Is this code better than option number one mentioned above for determining row count?

Drew
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Drew,
Option 1 is faster and therefore a better habit to get into. It uses a PreparedStatement instead of a Statement. (note the question mark for user_id) This allows the execution plan to be cached instead of generated for each query.

By returning a count instead of the whole row, the database can return the result by using only the index. It doesn't have to go to disk to get the whole row. It also has less network traffic because it doesn't have to transmit as much data. A single number vs a whole row.

Expanded option 1 to be equivalent to the code you posted:
Drew Lane
Ranch Hand

Joined: May 13, 2001
Posts: 296
Huh.

Here's where I found the code:

Get Row Count

"JDBC 2.0 provides a way to retrieve a rowcount from a ResultSet without having to scan through all the rows or issue a separate SELECT COUNT(*)."

Is this incorrect or is it just in the wrong place?
(I assumed it was referring to the code underneath it, not above it.)

Drew
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
Drew, the code you found is useful for a common problem with JDBC: you want to perform a query and store the results (this is the key difference) in an array or other data structure where you need to know the number of rows ahead of time.

Normally, you execute your query and iterate through the results, adding them one by one to a List or other Collection but without scrollable result sets, you only knew how many rows your query returned once you iterated past the last one. You had to keep a running total along the way. This meant that you couldn't presize your ArrayList to avoid resizing it as you iterated.

In your case, you don't care about the existing user data (my assumption from your first post) -- you only want to know if there is an existing row or not. To know that, you just need the row count, not the data. So the page you linked would give you extra information, with the costs Jeanne pointed out.

Now, if you need to update the user using existing information -- for example you have a login_count column that you must increment -- then the tip from that page would be useful.

Finally, one line needs to be added to Jeanne's code: setting the parameter. Just before executing the statement, you must set the user_id parameter. If you're reusing the PS, you must clear the parameters as well, but you probably won't in this case.Note that in JDBC, parameters and columns start with 1 instead of 0.
Drew Lane
Ranch Hand

Joined: May 13, 2001
Posts: 296
Very good.

Thank you both!

Drew
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30762
    
156

Originally posted by David Harkness:
Finally, one line needs to be added to Jeanne's code: setting the parameter.

David,
I can't believe I forgot that! Thanks for explaining it so well too.
Hareesh Ram Chanchali
Ranch Hand

Joined: Jan 31, 2005
Posts: 110
hi,

to count rows we have

One approach is to execute a "SELECT COUNT(*)..." before the actual query.
This means the database engine has to parse the same data twice (once for the count, once for the data itself).


The second approach is to use the JDBC 2.0 way:
one using Scrollable ResultSet and the other using Cached RowSet and plain(non-scrollable) ResultSet combination.

Using JDBC Scrollable ResultSet: ..............

sqlString = "SELECT * FROM emp";

// Create a scrollable ResultSet.
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);rs = stmt.executeQuery(sqlString);
// Point to the last row in resultset.
rs.last();
// Get the row position which is also the number of rows in the ResultSet.
int rowcount = rs.getRow();
System.out.println("Total rows for the query: "+rowcount);


// Reposition at the beginning of the ResultSet to take up rs.next() call.
rs.beforeFirst();
................






Using Oracle JDBC Cached RowSet

.........................
ResultSet rs = null;........................
// Create and initialize Cached RowSet object.
OracleCachedRowSet ocrs = new OracleCachedRowSet();

// Create a string that has the SQL statement that gets all the records.
String sqlString = "SELECT empno FROM emp";


// Create a statement, resultset objects.
stmt = conn.createStatement();
rs = stmt.executeQuery(sqlString);
// Populate the Cached RowSet using the above Resultset.
ocrs.populate(rs);

// Point to the last row in Cached RowSet.
ocrs.last();

// Get the row position which is also the number of rows in the Cached
// RowSet.
int rowcount = ocrs.getRow();

System.out.println("Total rows for the query using Cached RowSet: "+
rowcount);

// Close the Cached Rowset object.

if (ocrs != null)
ocrs.close();.............


plz see the following link
http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/rs/CountResult.html


Hareesh Ram Chanchali
SCJP 5.0, IBM Certified Solution Designer
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
Yes, Hareesh, that's what the link Drew posted discusses. But Drew doesn't need the data in this case, so using the technique the two links mention causes more work to be done which is just thrown away.

Jeanne, I didn't even notice it at first either. Then something in the back of my mind said, "Wait, that looks too simple!"
Drew Lane
Ranch Hand

Joined: May 13, 2001
Posts: 296
Maybe I will need to get that data afterall.

I have added another twist:

Comparing Dates

This is actally a continuation of the current situation, but I thought it might be better to start a new thread.

If any of you have a suggestion on how best to solve this, would be greatly appreciated!

<bows to the SQL gods>

Drew
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Insert & Update