c3p0连接池配置_Java使用c3p0连接池完成多表级联查询

引言：今天讲述实现一对一多表查询，完成两个表的关联，查询出两张表的所有字段信息。

准备工作：idea开发工具，可以在maven仓库下载c3p0连接池的jar包，对一对一，一对多有所了解，对多表查询的sql语句要有所了解，需要一定的Java基础。

1.建表

首先两张表要建立一定的关联，即一张表的主键作为另一张表的外键，本次我们有学生表和用户表，属于一对一的关系，我们就以学生表为主表，也就是他的主键当作用户表的外键

---

admin表，它的外键参考的是student表的主键

sutent作为主表，一对一的左侧，当然两张表调换也是可以

这个设置在admin表的外键序列里，我们参考的学生表的主键id，更新和删除设置restrict.具体的区别可以百度一下。

2.配置c3p0连接池

xml version="1.0" encoding="UTF-8"?><c3p0-config><default-config>

<property name="driverClass">com.mysql.jdbc.Driver

property>

<property name="jdbcUrl">jdbc:mysql://localhost:3306/

db_student_system?useUnicode=true&

characterEncoding=utf-8property>

<property name="user">rootproperty><property name="password">123456property><property name="acquireIncrement">5property><property name="initialPoolSize">10property><property name="minPoolSize">5property><property name="maxPoolSize">20property>default-config>c3p0-config>

3.获取链接

package utils;import java.sql.Connection;import java.sql.SQLException;import javax.sql.DataSource;import com.mchange.v2.c3p0.ComboPooledDataSource;public class C3p0Utils {private static DataSource ds;static{ds=new ComboPooledDataSource();
   }public static DataSource getDs(){return ds;
   }

public static Connection getConnection() throws

SQLException{

return ds.getConnection();
}

public static void main(String args[])

throws SQLException {

System.out.println(new C3p0Utils()

.getConnection()+"mysql is ok");

}
}

4.建数据模型层

public class Student {private int id;private String stuno;private String sclass;

private String sex;

    private Admin admin;

public Student( int id,String stuno, String sclass,

String sex,String political, String sname, Integer age,

String level, String phone, String qq) {

this.id=id;this.stuno = stuno;this.sclass = sclass;this.sex = sex;this.political = political;this.sname = sname;this.age = age;this.level = level;this.phone = phone;this.qq = qq;
}

public Student(Integer id, String stuno,

String sclass, String sex, String account,

String pwd,String type, String political, String sname, Integer age, String level, String phone, String qq) {

super();this.id = id;this.stuno = stuno;this.sclass = sclass;this.sex = sex;this.admin=new Admin(account,pwd,type);this.political = political;this.sname = sname;this.age = age;this.level = level;this.phone = phone;this.qq = qq;

}

     private String political;private String sname;private Integer age;private String level;private String phone;private String qq;

public Student(Admin admin) {this.admin = admin;}public Admin getAdmin() {return admin;}public void setAdmin(Admin admin) {this.admin = admin;} ***********各个属性的setter和getter方法**********

}

******这个是admin表的**

package model;/*public class Admin {private Integer id;private String account;private String pwd;

private String type;

    private int s_id;

public void setAccount(String account) { this.account = account;}public Admin() {}

//构造方法，初始化对象

public Admin(String account, String pwd, String type) {this.account = account;this.pwd = pwd;this.type = type;
}public Admin(String account, String pwd, String type, int s_id) {this.account = account;this.pwd = pwd;this.type = type;this.s_id = s_id; }

 ***********各个属性的setter和getter方法**********

}

5.建立dao层

import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.BeanHandler;import org.apache.commons.dbutils.handlers.BeanListHandler;import model.Student;import org.apache.commons.dbutils.handlers.MapListHandler;import org.apache.commons.dbutils.handlers.ScalarHandler;import utils.C3p0Utils;public class StudentDao {//获取连接执行查询

private QueryRunner runner=new QueryRunner(C3p0Utils.getDs()

);

public List> findAll() {
   List> list=null;try {

String sql = "select s.*,a.* from student s left join

admin a on s.id=a.s_id ";//左连接查询，查询的是两张表关联的字段。

//创建一个集合用来存储查询的参数，因为我们不清楚客户到底输入几个参数，所以用集合来存放list=runner.query(sql, new MapListHandler());
} catch (SQLException e) {//捕获异常throw new RuntimeException(e);//抛出运行异常}return list;

}

public static void main(String[] args){
StudentDao dao=new StudentDao();//调用对象

List> list=dao.findAll();//把多表两张表

查询的字段封装到Map里，再封装到List集合里。

Iterator> iterator=list.iterator();

//使用迭代器遍历集合。

while (iterator.hasNext()){
System.out.println(iterator.next());
}
}

}

6.运行结果截图

7.演示视频。