Re: Postgres Issue with JDBC

Hi John,

I return object wrappers to the JSP. F.e. my Java class has a method:

public MyBean getMyBean() {
// JDBC stuff to do SQL query, read out ResultSet and put values in MyBean
myBean.setField(rs.getString(1));
// close result set, statement and connection
return myBean;
}

When Controller puts MyBean in request (setAttribute) In JSP page I use

<jsp:useBean name="..." class="MyBean">

Without any JDBC stuff.

Jeroen

Example Code
(Note: My ConnectionProvider implementation uses a JNDI DataSource but could
also be implemented using pooling or the good old class.forName and
DriverManager.getConnection)

public class UserBalanceProvider implements TransTypeConstants {

private static UserBalanceProvider singleton = new
UserBalanceProvider();
private UserBalanceProvider() {
}
public static UserBalanceProvider getInstance() { return singleton; }

private final static String BALANCE_TABLE_ALIAS = "bal";
private final static String BALANCE_TABLE = "user_balance";
private final static String BALANCE_COLUMN_PREFIX =
BALANCE_TABLE_ALIAS+".";
private final static String BALANCE_COLUMNS =
BALANCE_COLUMN_PREFIX+"user_id,"
+ BALANCE_COLUMN_PREFIX+"credits100,"
+ BALANCE_COLUMN_PREFIX+"megapoints,"
+ BALANCE_COLUMN_PREFIX+"requested_pay_out_credits100,"
+ BALANCE_COLUMN_PREFIX+"confirmed_pay_out_credits100,"
+ BALANCE_COLUMN_PREFIX+"completed_pay_out_credits100,"
+ BALANCE_COLUMN_PREFIX+"games_played,"
+ BALANCE_COLUMN_PREFIX+"last_trans_id,"
+ BALANCE_COLUMN_PREFIX+"modify_date";

private UserBalance fillUserBalance(ResultSet rs) throws SQLException {
return new UserBalance(rs.getInt(1), // userId
rs.getInt(2), // credits100
rs.getInt(3), // megapoints
rs.getInt(4), // requested_pay_out_credits100
rs.getInt(5), // confirmed_pay_out_credits100
rs.getInt(6), // completed_pay_out_credits100
rs.getInt(7), // games_played
rs.getInt(8), // last_trans_id
rs.getTimestamp(9) // modify_date
);
}

/**
* returns current {(at)link UserBalance user balance} from database.
* @param userId ID of balance owner.
* @return UserBalance
*/
public UserBalance getUserBalance(int userId) throws AccountingException
{
UserBalance result = null;
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = ConnectionProvider.getReadOnlyConnection();

ps = con.prepareStatement("SELECT "+BALANCE_COLUMNS+" FROM
"+BALANCE_TABLE+" "+BALANCE_TABLE_ALIAS+" WHERE
"+BALANCE_COLUMN_PREFIX+"user_id=?");

ps.setInt(1, userId);

rs = ps.executeQuery();

if (rs.first()) {
result = fillUserBalance(rs);
}
rs.close();
rs = null;
ps.close();
ps = null;
con.close();
con = null;
} catch (Exception e) {
throw new
AccountingException("AccountingProvider.getUserBalance("+userId+"):
caught="+e, e);
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException rsCloseE) { }
}
if (ps != null) {
try {
ps.close();
} catch (SQLException psCloseE) { }

}
if (con != null) {
try {
con.close();
} catch (SQLException conCloseE) {
}
}
}
return result;
}