A
Arne Vajhøj
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
