Re: Postgres Issue with JDBC - Mailing list pgsql-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 {@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;
    }