File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Insert & Update

 
Drew Lane
Ranch Hand
Posts: 296
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 33694
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Drew Lane
Ranch Hand
Posts: 296
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 33694
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 296
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1646
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 296
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Very good.

Thank you both!

Drew
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33694
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 110
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
David Harkness
Ranch Hand
Posts: 1646
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 296
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic