Question

jdbc dao层方法

//查询数量
public int searchCount(String bnm,String btxt){

String sql="select count(*) from book where bid!=0 ";
if(bnm!=null&& !"".equals(bnm)){
sql+=" and bname like '%"+bnm+"%'";
}
if(btxt!=null && !"".equals(btxt)){
sql+=" and btext like '%"+btxt+"%'";
}

int temp=0;
hp=new DBHelp();
try {
ResultSet rs=hp.query(sql);
if(rs.next()){
temp=rs.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
hp.closeAll();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

return temp;
}

//插入
public int insert(Book book){
int temp=-1;
String sql="insert into book(btid,aid,cid,bimage,bname,btext,btime,bprice,bnumber) values(?,?,?,?,?,?,?,?,?)";
hp=new DBHelp();
try {
temp=hp.update(sql, book.getBtype().getBtid(),book.getAuthor().getAid(),book.getCbs().getCid(),book.getBimage(),book.getBname(),book.getBtext(),book.getBtime(),book.getBprice(),book.getBnumber());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
hp.closeAll();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

return temp;
}

//删除
public int delete(String bid){
String sql="delete from book where bid="+bid;
hp=new DBHelp();
int temp=-1;
try {
temp=hp.update(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
hp.closeAll();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

return temp;
}

//修改的方法
public int update(Book book){
String sql="UPDATE book set btid=?,aid=?,cid=?,bimage=?,bname=?,btext=?,btime=?,bprice=?,bnumber=? where bid=?";
int temp=-1;
hp=new DBHelp();
try {
temp=hp.update(sql,book.getBtype().getBtid(),book.getAuthor().getAid(),book.getCbs().getCid(),book.getBimage(),book.getBname(),book.getBtext(),book.getBtime(),book.getBprice(),book.getBnumber(),book.getBid());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
hp.closeAll();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

return temp;
}
}

Answer 1

jdbc和连接池对于你这个场景来说，都足够，既然用spring管理了，建议还是使用连接池，另外，spring自身没有实现连接池，一般都是对第三方连接池的包装，常见的有C3P0，dbcp以及最近比较流行的boneCP等，这几个配置都差不多太多，以boneCP为例：
<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource"
destroy-method="close">
<property name="driverClass" value="${jdbc.driverClass}" />
<property name="jdbcUrl" value="${jdbc.url}" />
<property name="username" value="${jdbc.user}" />
<property name="password" value="${jdbc.password}" />
<property name="idleConnectionTestPeriod" value="60" />
<property name="idleMaxAge" value="240" />
<property name="maxConnectionsPerPartition" value="30" />
<property name="minConnectionsPerPartition" value="10" />
<property name="partitionCount" value="2" />
<property name="acquireIncrement" value="5" />
<property name="statementsCacheSize" value="100" />
<property name="releaseHelperThreads" value="3" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" />
</bean>