Re: JDBC: getMoreResults() versus rs.next() & autoGeneratedKeys
On 12/21/2013 8:10 PM, Arne Vajh??j wrote:
On 12/21/2013 7:47 PM, Andreas Leitgeb wrote:
Arne Vajh??j <arne@vajhoej.dk> wrote:
Except that I could only request reporting of generated keys, if I knew
beforehand that I wouldn't need any isolation,etc.-flags for the
select-
case.
getGeneratedKeys() should be good no matter transaction isolation level
etc. - I have not read the fine print in the JDBC spec, but all the
implementations discussed in this thread are concurrency safe (assuming
you do not make concurrent calls on the same connection object).
There's two kinds of "prepareStatement" overloads:
- those with options relevant to selects (isolation,...)
- those with options relevant to the other ones (generatedkeys)
Then there is execute() on the preparedStatement, that
will work on any kind of sql-statement and will give me all
the necessary information/behaviour... provided I was lucky
with my choice of prepareStatement().
By the time I find out that the sql was e.g. an insert/update...,
(namely when .execute() returns false), then it is already
too late to pick the "generatedkeys"-overload of prepareStatement,
which I'd need to have specified for prepareStatement(), to now
be able to actually obtain the generated keys.
In the other case, if I used the "generatedkeys"-overload
and it turns out (from execute() returning true) that it was a
query, then I can no longer specify isolation levels, holdability
or scollability for the ResultSet.
I think you should set transaction isolation level on the connection
when you create it and use the same for queries and updates.
But I still think that the abstract super class and two sub classes
for query and update will make you code be so much nicer.
I know very little about your context.
But here is a generic example to illustrate what I am talking about:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class OODBA {
public static class RowConsoleDisplay implements RowProcessor {
public void processRow(ResultSet rs) throws SQLException {
System.out.printf("%d %s\n", rs.getInt(1), rs.getString(2));
}
}
public static class RowCountConsoleDisplay implements
RowCountAndKeyProcessor {
public void processRowCount(int rc) {
System.out.printf("rc = %d\n", rc);
}
public void processKeyRow(ResultSet rs) {
// not used
}
}
public static void main(String[] args) throws
ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost/Test", "root", "");
DatabaseCommand q1 = new QueryCommand(con, "SELECT f1,f2 FROM
t1", new RowConsoleDisplay());
q1.execute();
DatabaseCommand u = new UpdateCommand(con, "INSERT INTO t1
VALUES(?,?)", new RowCountConsoleDisplay());
u.execute(new ParameterSetter() {
public void set(PreparedStatement pstmt) throws SQLException {
pstmt.setInt(1, 9);
pstmt.setString(2, "IIIIIIIII");
}
});
q1.execute();
DatabaseCommand q2 = new QueryCommand(con, "SELECT f1,f2 FROM
t1 WHERE f1 = ?", new RowConsoleDisplay());
q2.execute(new ParameterSetter() {
public void set(PreparedStatement pstmt) throws SQLException {
pstmt.setInt(1, 3);
};
});
q2.execute(new ParameterSetter() {
public void set(PreparedStatement pstmt) throws SQLException {
pstmt.setInt(1, 5);
};
});
con.close();
}
}
interface ParameterSetter {
public void set(PreparedStatement pstmt) throws SQLException;
}
interface RowProcessor {
public void processRow(ResultSet rs) throws SQLException;
}
interface RowCountAndKeyProcessor {
public void processRowCount(int rc);
public void processKeyRow(ResultSet rs);
}
abstract class DatabaseCommand {
private Connection con;
private String sqlstr;
public DatabaseCommand(Connection con, String sqlstr) {
this.con = con;
this.sqlstr = sqlstr;
}
public void execute() throws SQLException {
execute(new ParameterSetter() {
public void set(PreparedStatement pstmt) {
}
});
}
public void execute(ParameterSetter params) throws SQLException {
PreparedStatement pstmt = getPreparedStatement(con, sqlstr);
params.set(pstmt);
process(pstmt);
pstmt.close();
}
public abstract PreparedStatement getPreparedStatement(Connection
con, String sqlstr) throws SQLException;
public abstract void process(PreparedStatement pstmt) throws
SQLException;
}
class QueryCommand extends DatabaseCommand {
private int rstype;
private int rsconcur;
private RowProcessor rowproc;
public QueryCommand(Connection con, String sqlstr, RowProcessor
rowproc) {
this(con, sqlstr, ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY, rowproc);
}
public QueryCommand(Connection con, String sqlstr, int rstype, int
rsconcur, RowProcessor rowproc) {
super(con, sqlstr);
this.rstype = rstype;
this.rsconcur = rsconcur;
this.rowproc = rowproc;
}
public PreparedStatement getPreparedStatement(Connection con,
String sqlstr) throws SQLException {
return con.prepareStatement(sqlstr, rstype, rsconcur);
}
public void process(PreparedStatement pstmt) throws SQLException {
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
rowproc.processRow(rs);
}
rs.close();
}
}
class UpdateCommand extends DatabaseCommand {
private boolean genkey;
private RowCountAndKeyProcessor rcakproc;
public UpdateCommand(Connection con, String sqlstr,
RowCountAndKeyProcessor rcproc) {
this(con, sqlstr, false, rcproc);
}
public UpdateCommand(Connection con, String sqlstr, boolean genkey,
RowCountAndKeyProcessor rcakproc) {
super(con, sqlstr);
this.genkey = genkey;
this.rcakproc = rcakproc;
}
public PreparedStatement getPreparedStatement(Connection con,
String sqlstr) throws SQLException {
return con.prepareStatement(sqlstr, genkey ?
PreparedStatement.RETURN_GENERATED_KEYS :
PreparedStatement.NO_GENERATED_KEYS);
}
public void process(PreparedStatement pstmt) throws SQLException {
int rc = pstmt.executeUpdate();
rcakproc.processRowCount(rc);
if(genkey) {
ResultSet rs = pstmt.getGeneratedKeys();
while(rs.next()) {
rcakproc.processKeyRow(rs);
}
}
}
}
Arne