the best description would be a small code-snippet ... start at DButil.executeSQL
/**
* is responsible for DatabaseConnection Utilities
*/
public class DbUtil {
// TODO Test 25.08.2008
private static long warningThreshold=10000;
/**
* Constructor for DataBaseUtil
*/
public DbUtil() {
super();
}
/**
* does close a DBConnection and a PreparedStatement and a ResultSet
*/
public static void closeDB(Connection con,PreparedStatement ps,ResultSet rs){
try {
if (rs != null) rs.close();
rs=null;
} catch (SQLException Se) {rs=null;FerpsServerLogger.error(DbUtil.class,"ErrorClosingResultSet" );Se.printStackTrace();}
try {
if (ps != null) ps.close();
ps=null;
} catch (SQLException Se) {ps=null;FerpsServerLogger.error(DbUtil.class,"ErrorClosingPreparedStatement" );Se.printStackTrace();}
try {
if (con != null) con.close();
con=null;
} catch (SQLException Se) {con=null;FerpsServerLogger.error(DbUtil.class,"ErrorClosingConnection" );Se.printStackTrace();}
}
/**
* does create a DBConnection ... this Method must not be called from client because the InitialContext
* does only work from EJB !
*/
public static Connection getDBConnectionFromEJB(String aDSName) throws java.rmi.RemoteException {
InitialContext initialContext = null;
DataSource ds = null;
Connection con = null;
try {
initialContext = new InitialContext();
ds = (DataSource) initialContext.lookup(aDSName);
con = ds.getConnection();
} catch (SQLException Se) {
throw new RemoteException("getConnection SQL-Exception", Se);
} catch (NamingException Ne) {
throw new RemoteException("getConnection NamingException", Ne);
}
return con;
}
/**
* does set all Parameters in the prepared Statement with elements from 'parameters'
* @param ps
* @param parameters
* @param startPos = startingposition in prepearedStatement (first position = 1)
* @return endingposition + 1
* @throws SQLException
*/
public static int setStatementParameters(PreparedStatement ps,Vector parameters,int startPos) throws SQLException{
Iterator i = parameters.iterator();
while (i.hasNext()) {
ps.setObject(startPos++, i.next());
}
return startPos;
}
/**
* does set all Parameters in the prepared Statement with elements from 'parameters'
* @param ps
* @param parameters
* @param startPos = startingposition in prepearedStatement (first position = 1)
* @return endingposition + 1
* @throws SQLException
*/
public static int setStatementParameters(PreparedStatement ps,Object[] parameters,int startPos) throws SQLException{
for (int j = 0; j < parameters.length; j++) {
ps.setObject(startPos++, parameters[j]);
}
return startPos;
}
/**
* does close a DBConnection and a PreparedStatement and a ResultSet
*/
public static void closeDB(SQLResultComposition rc) {
if (null!=rc){
closeDB(rc.getCon(),rc.getPs(),rc.getRs());
}
}
public static SQLResultComposition executeQuery(SQLStatementComposition sql) throws SQLException, RemoteException {
return executeQuery(sql, new Object[]{},1);
}
public static SQLResultComposition executeQuery(SQLStatementComposition sql,Object[] parameters, int parameterPos) throws SQLException, RemoteException {
return executeSQL(sql, parameters, parameterPos, false);
}
public static SQLResultComposition executeUpdate(SQLStatementComposition sql,Object[] parameters, int parameterPos) throws SQLException, RemoteException {
return executeSQL(sql, parameters, parameterPos, true);
}
private static SQLResultComposition executeSQL(SQLStatementComposition sql,Object[] parameters, int parameterPos,boolean update) throws RemoteException, SQLException {
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
int affectedRows=0;
try {
con = getDBConnectionFromEJB(LookupService.getDefaultDataSource());
ps = con.prepareStatement(sql.getStatement());
DbUtil.setStatementParameters(ps, parameters, parameterPos);
long start=System.currentTimeMillis();
affectedRows= (update) ? ps.executeUpdate():0;
rs=(!update)? ps.executeQuery():null;
long stop=System.currentTimeMillis();
long responseTime = (stop-start);
if (responseTime>=warningThreshold){
String msg = StringUtilities.create(new String[]{"SQL-LONGTIME-Execution: ",String.valueOf(responseTime),"ms...",sql.getStatementID(),"...",sql.getStatement()});
FerpsServerLogger.warn(DbUtil.class, msg);
}
} catch (RemoteException e) {
closeDB(con, ps, rs);
throw e;
} catch (SQLException e) {
closeDB(con, ps, rs);
throw e;
} catch (Exception e){
closeDB(con, ps, rs);
throw new SQLException(e.getMessage());
}
return new SQLResultComposition(con,ps,rs,affectedRows);
}
}
public class SQLStatementComposition implements Serializable {
private String statementID;
private String statement;
public SQLStatementComposition(String statementID,String statement) {
super();
this.statementID = statementID;
this.statement = statement;
}
public String getStatementID() {
return statementID;
}
public String getStatement() {
return statement;
}
public void setStatement(String statement) {
this.statement = statement;
}
}
[Message sent by forum member 'thomas_x' (thomas_x)]
http://forums.java.net/jive/thread.jspa?messageID=295158