JdbcUtil类:
package com.xiaohui.jdbc.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public final class JdbcUtil {
private static ComboPooledDataSource dataSource;
static {
dataSource = new ComboPooledDataSource();
}
// 取得链接
public static Connection getMySqlConnection() throws SQLException {
return dataSource.getConnection();
}
//
public static DataSource getDataSource(){
return dataSource;
}
// 关闭链接
public static void close(Connection conn) throws SQLException {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw e;
}
}
}
public static void close(PreparedStatement pstate) throws SQLException {
if(pstate!=null){
pstate.close();
}
}
public static void close(ResultSet rs) throws SQLException {
if(rs!=null){
rs.close();
}
}
}
c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/mysql4</property>
</default-config>
</c3p0-config>
分页的一个dao:
package com.xiaohui.cusSys.dao;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.xiaohui.cusSys.domain.Customer;
import com.xiaohui.cusSys.util.JdbcUtil;
public class Dao {
private QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
// 根据id返回 Customer 对象
public Customer getCustomerById(int id) throws SQLException {
String sql = "select * from customer where id = ?";
Customer cus = (Customer) qr.query(sql,
new BeanHandler(Customer.class), id);
return cus;
}
// 分页返回
public List<Customer> getFyList(int start, int size) throws SQLException {
List<Customer> list = null;
String sql = "select * from customer limit ?,?";
list = qr.query(sql, new BeanListHandler(Customer.class), new Object[] {
start, size });
return list;
}
// 返回记录的总数目
public int getAllRecordsCount() throws SQLException {
String sql = "select count(*) from customer";
Long temp = qr.query(sql, new ScalarHandler());
return temp.intValue();
}
// 根据ID删除指定的记录
public void deleteRecordById(int id) throws SQLException {
String sql = "delete from customer where id = ?";
qr.update(sql, id);
}
// 根据id更新记录信息
public void updateRecordById(Customer newCus) throws SQLException {
String sql = "update customer set name= ?,address= ?,tel= ?,mail= ?,birthday= ? where id= ?";
qr.update(
sql,
new Object[] { newCus.getName(), newCus.getAddress(),
newCus.getTel(), newCus.getMail(),
newCus.getBirthday(), newCus.getId() });
}
// 添加记录
public void addRecord(Customer newCus) throws SQLException {
String sql = "insert into customer(name,address,tel,mail,birthday) values(?,?,?,?,?)";
qr.update(sql, new Object[] { newCus.getName(), newCus.getAddress(),
newCus.getTel(), newCus.getMail(),
// //将java.util.Date 转换为 java.sql.Date
// new java.sql.Date( newCus.getBirthday().getTime())
newCus.getBirthday() });
}
}