本文主要讲解jdbc连接mySQL。
一、在idea上新建maven工程
二、添加依赖包
<!-- 连接mysql工具包--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency> <!-- 使用c3p0——jdbc连接池--> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.4</version> </dependency>
三、创建资源目录，并在其中创建c3p0的xml文件
resource可以在工程目录或src下创建
resource/c3p0-config.xml
c3p0-config.xml配置如下：
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!--默认配置-->
<default-config>
<!-- initialPoolSize：初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。 -->
<property name="initialPoolSize">10</property>
<!-- maxIdleTime：最大空闲时间,30秒内未使用则连接被丢弃。若为0则永不丢弃。-->
<property name="maxIdleTime">30</property>
<!-- maxPoolSize：连接池中保留的最大连接数 -->
<property name="maxPoolSize">100</property>
<!-- minPoolSize: 连接池中保留的最小连接数 -->
<property name="minPoolSize">10</property>
<property name="checkoutTimeout">3000</property>
</default-config>
<!--配置连接池mysql-->
<named-config name="single01mysql">
<!-- 连接参数 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://single01:3306/school?useSSL=false</property>
<property name="user">root</property>
<property name="password">ok</property>
<property name="initialPoolSize">10</property>
<property name="maxPoolSize">100</property>
<property name="checkoutTimeout">3000</property>
<property name="maxIdleTime">30</property>
<property name="minPoolSize">10</property>
</named-config>
<!--配置连接池2,可以配置多个-->
</c3p0-config>三、建立连接池测试类
public class C3p0Utils {
static ComboPooledDataSource source=new ComboPooledDataSource("single01mysql");
//创建连接
public static Connection getConnection(){
Connection connection=null;
try {
connection=source.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//关闭连接
public static void close(Connection conn, PreparedStatement pstmt, ResultSet rs){
try {
if (rs!=null) {
rs.close();
}
if (pstmt!=null){
pstmt.close();
}
if (conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/* 关闭连接——可变参数
public static void close(AutoCloseable...closes){
if (null !=closes){
for (AutoCloseable close : closes) {
try {
close.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}*/
public static void main(String[] args) {
//打印连接
for (int i = 0; i < 10; i++) {
Connection connection = C3p0Utils.getConnection();
System.out.println(connection+ " "+i);
C3p0Utils.close(connection,null,null);
// C3p0Utils.close(connection);
}
}
}
四:通过c3p0连接池往MySQL里插入，查询数据
1.创建接口程序，定义插入，查询方法；
public interface IStudentDao {
public void insertStudent(List<Student> students); //插入
public List<Student> getStudents(); //查询
}
2、创建要查询的表格的数据的实例类；
属性，构造方法，set/get方法，重写toString方法
public class Student {
private String id;
private String name;
public Student(String id, String name) {
this.id = id;
this.name = name;
}
@Override
public String toString() {
return "Student{" +
"id='" + id + '\'' +
", name='" + name + '\'' +
'}';
}
public String getId() {
return id;
}
public String getName() {
return name;
}
public void setId(String id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
}
3.创建实现类；实现插入，查询数据
public class StudentDao implements IStudentDao{
//插入mysql表格
public void insertStudent(List<Student> students){
//insert into student(id,name) values("001","as"),("002","ls");
String sql="insert into student(id,name) values";
if (students!=null && students.size()>0){
for (Student stu : students) {
sql=sql+"('"+stu.getId()+"','"+stu.getName()+"'),";
}
sql=sql.substring(0,sql.length()-1);
System.out.println(sql);
Connection connection = C3p0Utils.getConnection();
PreparedStatement preparedStatement =null;
try {
preparedStatement =connection.prepareStatement(sql);
int i = preparedStatement.executeUpdate();
System.out.println(i);
} catch (SQLException e) {
e.printStackTrace();
}finally {
C3p0Utils.close(connection,preparedStatement,null);
}
}
}
//查询mysql表格
public List<Student> getStudents() {
Connection connection = C3p0Utils.getConnection();
String sql ="select id,name from student";
ArrayList<Student> students = new ArrayList<>();
PreparedStatement preparedStatement = null;
ResultSet resultSet=null;
try {
preparedStatement=connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
String id = resultSet.getString("id");
String name = resultSet.getString("name");
/*String id = resultSet.getString(1);
String name = resultSet.getString(2);*/
// System.out.println(id+" "+name);
Student student = new Student(id, name);
students.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
C3p0Utils.close(connection,preparedStatement,resultSet);
}
return students;
}
public static void main(String[] args) {
StudentDao studentDao = new StudentDao();
//插入mysql
Student stu1 = new Student("003", "ww");
Student stu2 = new Student("004", "zl");
Student stu3 = new Student("005", "zq");
ArrayList<Student> students = new ArrayList<>();
students.add(stu1);
students.add(stu2);
students.add(stu3);
studentDao.insertStudent(students);
//查询mysql表格
/*List<Student> students = studentDao.getStudents();
for (Student stu : students) {
System.out.println(stu.toString());
}*/
}
}
