JDBC链接数据库版本三,使用C3P0,使用jar文件两个
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() });
- }
- }
jar文件:c3p0-0.9.1.2.jar (关键) mysql-connector-java-5.1.22-bin.jar(关键) 在dao中 使用到commons-dbutils-1.5.jar
- 顶