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, 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, 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.
Joined: May 13, 2001
Thank you both!
author & internet detective
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);
Hareesh Ram Chanchali
SCJP 5.0, IBM Certified Solution Designer
Joined: Aug 07, 2003
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!"