JDBC连接

数据库的连接

JDBC规范（掌握四个核⼼对象）：

DriverManager:⽤于注册驱动

Connection: 表示与数据库创建的连接

Statement: 操作数据库sql语句的对象

ResultSet: 结果集或⼀张虚拟表

JDBC ⼊⻔案例

开发步骤

\1. 注册驱动.

\2. 获得连接.

\3. 获得执⾏sql语句的对象

\4. 执⾏sql语句，并返回结果

\5. 处理结果

\6. 释放资源

JDBC查询连接测试：

package demo.jdbc;
​
import java.sql.*;
​
public class JDBC_Demo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. 注册驱动.
        Class.forName("com.mysql.jdbc.Driver");
​
        //2. 获得连接.
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test7", "root", "123456");
​
        //3. 获得执⾏sql语句的对象
        Statement statement = connection.createStatement();
​
        //4. 执⾏sql语句，并返回结果
        ResultSet resultSet = statement.executeQuery("select * from students;");
​
        //5. 处理结果
        while (resultSet.next()) {
            String id = resultSet.getString("id");
            String name = resultSet.getString("name");
            String age = resultSet.getString("age");
            System.out.println(id+"\t"+name+"\t"+age);
        }
​
        //6. 释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

JDBC对数据库进行添加、删除、修改操作

package demo.Student_Management.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;


public class JDBC_Revise {
    //注册驱动
    String ClassName = "com.mysql.jdbc.Driver";
    //获取连接
    String URL = "jdbc:mysql://localhost:3306/test7?useUnicode=true&characterEncoding=UTF-8";
    String UserName = "root";
    String password = "123456";

    //删除
    public void Revise(int stuid,String sql) throws ClassNotFoundException, SQLException {
        //注册驱动
        Class.forName(ClassName);
        //获取连接
        Connection connection = DriverManager.getConnection(URL, UserName, password);
//        String sql="delete from Students where stuid=?;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //向数据库插入数据
        preparedStatement.setInt(1,stuid);
        preparedStatement.executeUpdate();

        //5、关闭资源
        preparedStatement.close();
        connection.close();
    }

    //修改
    public void update(int stuid,String uname,int age) throws ClassNotFoundException, SQLException {
        Class.forName(ClassName);
        Connection connection = DriverManager.getConnection(URL, UserName, password);

        String sql="update students set name=?,age=? where stuid=?;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,uname);
        preparedStatement.setInt(2,age);
        preparedStatement.setInt(3,stuid);
        preparedStatement.executeUpdate();

        //关闭
        preparedStatement.close();
        connection.close();
    }

    //增加
    public void Increase(int stuids,String uname,int ages){
        //注册驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //获取连接
        Connection connection = null;
        String sql="insert into Students(id,stuid,name,age) values(0,?,?,?); ";
        PreparedStatement preparedStatement = null;
        try {
            connection = DriverManager.getConnection(URL, UserName, password);
            preparedStatement = connection.prepareStatement(sql);
            //向数据库插入数据
            preparedStatement.setInt(1,stuids);
            preparedStatement.setString(2,uname);
            preparedStatement.setInt(3,ages);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //5、关闭资源
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

JDBC连接池： 

c3p0：

首先需要添加jar包：

• c3p0-0.9.2.1.jar

• 当有Exception in thread "main" java.lang.NoClassDefFoundError: com/mchange/v2/ser/Indirector错误时，需要在添加一个jar包：mchange-commons-java-0.2.3.4.jar

需要在src目录下创建一个c3p0-config.xml配置文件（注意：这个文件名字不能改）

<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test7</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="initialPoolSize">10</property>
    </default-config>
</c3p0-config>

测试：

package com.Demo01;
​
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.*;
​
​
public class c3p0_test {
    public static void main(String[] args) throws SQLException {
        //创建一个连接池对象
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
​
        //得到连接
        Connection connection = comboPooledDataSource.getConnection();
​
        //创建操作的sql语句对象
        String sql="select * from students";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
​
        while (resultSet.next()) {
            int stuid = resultSet.getInt("stuid");
            System.out.println(stuid);
        }
    }
}

DBUtils：

导入jar包：commons-dbutils-1.4.jar

编写测试：

package com.Demo02;
​
import com.Demotest.User;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
​
import java.sql.SQLException;
import java.util.List;
​
public class DBUtils_test {
    public static void main(String[] args) throws SQLException {
        //初始化dbutils类，QueryRunner
        QueryRunner queryRunner = new QueryRunner(new ComboPooledDataSource());
​
        //得到一个对象
        List<User> query = queryRunner.query("select * from students", new BeanListHandler<User>(User.class));
​
        //操作对象
        for (User user : query) {
            System.out.println(user.getStuID()+"\t"+user.getName()+"\t"+user.getAge());
        }
    }
}