spring + c3p0 连接池

package com.pb.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; import com.pb.dao.DAO; import com.pb.entity.RedAndBlueCardHead; public class JdbcDao implements DAO { private DataSource dataSource; public DataSource getDataSource() { return dataSource; } public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } @SuppressWarnings("unused") @Override public int Select() { Connection conn = null; PreparedStatement ps1 = null; ResultSet rs = null; int seq = 1; try { conn = dataSource.getConnection(); ComboPooledDataSource ds = (ComboPooledDataSource) dataSource; String sql = "select REC_NUM from BHZT where TBLNAME = ?"; ps1 = (PreparedStatement) conn.prepareStatement(sql); ps1.setString(1, "RedAndBlueCardHead"); rs = ps1.executeQuery(); while (rs.next()) { seq = rs.getInt("REC_NUM"); // 取得数据库中的数据 } } catch (SQLException e) { e.printStackTrace(); } finally { release(conn, ps1, rs); } return seq; } @SuppressWarnings("resource") @Override public int GetSeq(String tableName) { Connection conn = null; PreparedStatement ps1 = null; ResultSet rs = null; int seq = 1; try { conn = dataSource.getConnection(); String sql = "update BHZT set REC_NUM = REC_NUM+1 where TBLNAME = ?"; ps1 = (PreparedStatement) conn.prepareStatement(sql); ps1.setString(1, tableName); if (ps1.executeUpdate() == 0) { sql = "insert into BHZT (TBLNAME,REC_NUM) values(?,?)"; ps1 = (PreparedStatement) conn.prepareStatement(sql); ps1.setString(1, tableName); ps1.setInt(2, 1); ps1.execute(); } else { // System.out.println("=============select REC_NUM from BHZT where TBLNAME = ?======"); sql = "select REC_NUM from BHZT where TBLNAME = ?"; ps1 = (PreparedStatement) conn.prepareStatement(sql); ps1.setString(1, tableName); rs = ps1.executeQuery(); while (rs.next()) { seq = rs.getInt("REC_NUM"); // 取得数据库中的数据 } } } catch (SQLException e) { e.printStackTrace(); } finally { release(conn, ps1, rs); } return seq; } /* * @SuppressWarnings("resource") * * @Override public int GetSeq(String tableName) { Connection conn = null; * PreparedStatement ps1 = null; ResultSet rs = null; int seq = 1; try { * conn = dataSource.getConnection(); conn.setAutoCommit(false); String sql * = "update BHZT set REC_NUM = REC_NUM+1 where TBLNAME = ?"; ps1 = * (PreparedStatement) conn.prepareStatement(sql); ps1.setString(1, * tableName); if (ps1.executeUpdate() == 0) { sql = * "insert into BHZT (TBLNAME,REC_NUM) values(?,?)"; ps1 = * (PreparedStatement) conn.prepareStatement(sql); ps1.setString(1, * tableName); ps1.setInt(2, 1); ps1.execute(); } else { // * System.out.println * ("=============select REC_NUM from BHZT where TBLNAME = ?======"); sql = * "select REC_NUM from BHZT where TBLNAME = ?"; ps1 = (PreparedStatement) * conn.prepareStatement(sql); ps1.setString(1, tableName); rs = * ps1.executeQuery(); while (rs.next()) { seq = rs.getInt("REC_NUM"); // * 取得数据库中的数据 } } conn.commit(); } catch (SQLException e) { try { * conn.rollback(); } catch (SQLException e1) { // TODO Auto-generated catch * block e1.printStackTrace(); } e.printStackTrace(); } finally { try { * conn.setAutoCommit(true); } catch (SQLException e) { e.printStackTrace(); * * } release(conn, ps1, rs); } return seq; } */ @Override public void SaveHead(RedAndBlueCardHead head, Connection conn, PreparedStatement ps) throws SQLException { head.OId = GetSeq("RedAndBlueCardHead"); String sql = "insert into RedAndBlueCardHead(OId,Status,Creator,CreateTime,operationType,BrandId, " + " RedGoodsId,CategoryId,SupplierId,GoodsId,ContractId,QRH,DictExpense,CXJFBH,PlanMoney,Continued,AClass," + " BClass,Reason,ExpenseMoney,CaTegoryCode) " + " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; // 21个 ps = conn.prepareStatement(sql); ps.setInt(1, head.OId); ps.setInt(2, head.Status); ps.setInt(3, head.Creator); // ps.setDate(4, head.CreatorTime); ps.setDate(4, new java.sql.Date(new java.util.Date().getTime())); ps.setInt(5, head.OperationType); ps.setInt(6, head.BrandId); ps.setInt(7, head.RedGoodsId); ps.setInt(8, head.CategoryId); ps.setInt(9, head.SupplierId); ps.setInt(10, head.GoodsId); ps.setString(11, head.ContractId); ps.setString(12, head.QRH); ps.setInt(13, head.DictExpense); ps.setString(14, head.CXJFBH); ps.setDouble(15, head.PlanMoney); ps.setInt(16, head.Continue); ps.setInt(17, head.AClass); ps.setInt(18, head.BClass); ps.setString(19, head.Reason); ps.setDouble(20, head.ExpenseMoney); ps.setString(21, head.CaTegoryCode); ps.execute(); } @Override public void SaveDetail(RedAndBlueCardHead head, Connection conn, PreparedStatement ps) throws SQLException { for (int i = 0; i < head.details.size(); i++) { String sql = "insert into RedAndBlueCardDetail(OId,INX,Organization,Store,DQXSRW,BQFPE) " + "values(?,?,?,?,?,?)"; ps = conn.prepareStatement(sql); ps.setInt(1, head.OId); ps.setInt(2, i + 1); ps.setInt(3, head.details.get(i).Organization); ps.setInt(4, head.details.get(i).Store); ps.setDouble(5, head.details.get(i).DQXSRW); ps.setDouble(6, head.details.get(i).BQFPE); ps.execute(); } } @Override public boolean SaveCard(RedAndBlueCardHead head) { Connection conn = null; PreparedStatement ps1 = null; ResultSet rs = null; try { conn = dataSource.getConnection(); SaveHead(head, conn, ps1); SaveDetail(head, conn, ps1); return true; } catch (SQLException e) { e.printStackTrace(); return false; } finally { release(conn, ps1, rs); } } @Override public void release(Connection conn, Statement st, ResultSet rs) { if (rs != null) { try { rs.close(); // throw new } catch (Exception e) { e.printStackTrace(); } rs = null; } if (st != null) { try { st.close(); } catch (Exception e) { e.printStackTrace(); } st = null; } if (conn != null) { try { conn.close(); } catch (Exception e) { e.printStackTrace(); } } } }