c3p0连接Mysql数据库
注:测试使用maven构建的项目,如需学习maven构建项目,请先到maven分类学习如何构建maven项目
1、创建一个maven项目,然后找到pom.xml文件,打开(第一次打开不是通过.xml文件格式打开,可通过右键 -- Open With -- XML Editor 打开)
打开后在配置文件中添加依赖,也就是jar包。
2、添加依赖:
(1)c3p0核心依赖
c3p0
c3p0
0.9.1.1
(2)MySQL依赖
mysql
mysql-connector-java
5.1.38
(3)JDBC工具类库
commons-dbutils
commons-dbutils
1.2
3、新建一个数据库,执行数据库脚本代码,创建表
drop table if existscustomer;create tablecustomer
(
cus_idint not nullauto_increment,
cus_namevarchar(10),
cus_ageint,primary key(cus_id)
);
4、新建一个实体类,对应数据库中的表结构
1 public classCustomer {2 privateString cus_id;3 privateString cus_name;4 private intage;5 public Customer(String cus_id, String cus_name, intage) {6 super();7 this.cus_id =cus_id;8 this.cus_name =cus_name;9 this.age =age;10 }11 publicCustomer() {12 super();13 }14 publicString getCus_id() {15 returncus_id;16 }17 public voidsetCus_id(String cus_id) {18 this.cus_id =cus_id;19 }20 publicString getCus_name() {21 returncus_name;22 }23 public voidsetCus_name(String cus_name) {24 this.cus_name =cus_name;25 }26 public intgetAge() {27 returnage;28 }29 public void setAge(intage) {30 this.age =age;31 }32 @Override33 publicString toString() {34 return "Customer [cus_id=" + cus_id + ", cus_name=" + cus_name + ", age=" + age + "]";35 }36
37 }
5、新建一个工具类,用于连接设置数据源和连接数据库信息(连接数据库的地址和用户根据自己的来修改)
我的url:jdbc:mysql://192.168.1.222:3306/test ;用户名:root 密码:root
1 importjava.beans.PropertyVetoException;2 importjava.sql.Connection;3 importjava.sql.PreparedStatement;4 importjava.sql.ResultSet;5 importjava.sql.SQLException;6 importcom.mchange.v2.c3p0.ComboPooledDataSource;7
8 public classJDBCUtils {9 //private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
10 static ComboPooledDataSource dataSource = newComboPooledDataSource();11 //静态代码块自动装载,连接数据库的参数
12 static{13
14 try{15 dataSource.setDriverClass("com.mysql.jdbc.Driver");16 dataSource.setJdbcUrl("jdbc:mysql://192.168.1.222:3306/test");17 dataSource.setUser("root");18 dataSource.setPassword("root");19 dataSource.setMaxPoolSize(10);//最大连接数
20 dataSource.setMinPoolSize(0);//最小连接数
21 dataSource.setInitialPoolSize(5);//初始化连接数
22 dataSource.setAcquireIncrement(5);//连接增量
23 } catch(PropertyVetoException e) {24 e.printStackTrace();25 }26
27 }28 //获得连接
29 public staticConnection getCon() {30 try{31 returndataSource.getConnection();32 } catch(Exception e) {33 e.printStackTrace();34 return null;35 }36 }37 public staticComboPooledDataSource getDataSource() {38 returndataSource;39 }40 //关闭连接
41 public static voidcloseCon(Connection conn,PreparedStatement pst,ResultSet rs) {42 if(rs != null) {43 try{44 rs.close();45 } catch(SQLException e) {46 e.printStackTrace();47 }48 finally {rs = null;}49 }50 if(pst != null) {51 try{52 pst.close();53 } catch(SQLException e) {54 e.printStackTrace();55 }56 finally {pst = null;}57 }58 if(conn != null) {59 try{60 conn.close();61 } catch(SQLException e) {62 e.printStackTrace();63 }64 finally {conn = null;}65 }66 }67 public voidcloseCon(Connection conn) {68 if(conn != null) {69 try{70 conn.close();71 } catch(SQLException e) {72 e.printStackTrace();73 }74 finally {conn = null;}75 }76 }77 }
6、建立一个测试类,测试数据连接
1 importjava.sql.Connection;2 importjava.sql.SQLException;3 importorg.apache.commons.dbutils.QueryRunner;4 importorg.apache.commons.dbutils.handlers.BeanHandler;5 importorg.junit.Test;6 importcom.li.spring.pojo.Customer;7 importcom.li.spring.utils.JDBCUtils;8
9 public classConnTest {10 //要使用QueryRunner,需要导入commons.dbutils的jar包11 //通过工具类获取数据源
12 QueryRunner queryrunner = newQueryRunner(JDBCUtils.getDataSource());13 //获取连接
14 Connection conn =JDBCUtils.getCon();15 @Test16 public void test() throwsSQLException {17 //新建一个对象,接收查询的结果集
18 Customer cus = newCustomer();19 //查询语句
20 String sql = "select * from customer";21 cus = (Customer) queryrunner.query(conn, sql,new BeanHandler(Customer.class));22 //System.out.println(conn.getClass().getName());
23 System.out.println(cus.toString());24 //关闭资源
25 newJDBCUtils().closeCon(conn);26 }27
28 }
7、运行测试类