Java - how to insert and update database inserts
I want to load multiple types of database calls into one PreparedStatement file. Is it possible?
Is there a way to do something like
PreparedStatement pstmt = connection.prepareStatement("?");
Where? could be either INSERT INTO MY_TABLE VALUES(1,2,3,4)
or could beUPDATE MY_TABLE, SET MY_VAL='1' WHERE MY_VAL IS NULL
Or do I always need to provide a table and action for my prepared statement?
source to share
Java won't let you add only? in the readystatement string file as it expects? for the owner of space for parameters only for the SQL query.
In your case, you might have to have 2 prepared statement objects and in the loop you can make the decision to be called. So, it would be something like below:
PreparedStatement insertPstmt = connection.prepareStatement("INSERT INTO MY_TABLE VALUES(?,?,?,?)");
PreparedStatement updatePstmt = connection.prepareStatement("UPDATE MY_TABLE, SET MY_VAL=? WHERE MY_VAL IS NULL");
While (<condition>) {
If (<insert condition>) {
// use insert pstmt and add batch
} else {
// use update pstmt and add batch
}
}
insertPstmt.executeBatch();
updatePstmt.executeBatch();
if you have any insert that depends on the update, you can batch execute accordingly. This will ensure that the update will work correctly. I would consider doing the insert first as they might not be update dependent.
source to share
On PreparedStatement, after variable binding for the first execution, call
pstmt.addBatch();
then bind the variables for the next one and call addBatch () each time. Then when you're done adding batches, you bacth by alling
pstmt.executeBatch();
See:
http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#addBatch%28%29
and
http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeBatch%28%29
BTW: Putting the whole variable into a variable won't work. This batch mechanism exists to reuse the same operator bind different variables each time.
source to share
Insert and update commands do not return data that needs to be processed. If you want to do things like in your examples, you can simply run the default query command and provide a concatenated string of all your sql strings separated by semicolons.
"INSERT INTO MY_TABLE VALUES(1,2,3,4)" + ";" +"UPDATE MY_TABLE, SET MY_VAL='1' WHERE MY_VAL IS NULL" + ";" +...
You do not need to prepare an expression in this case, nor do you get a performance gain in doing so.
source to share