SQL基础之JDBC、连接池

JDBC

JDBC四个核心对象

这几个类都是在java.sql包中

1. DriverManager(类): 数据库驱动管理类。这个类的作用：1）注册驱动; 2)创建java代码和数据库之间的连接，即获取Connection接口;
2. Connection(接口): 是一个接口, 建立数据库连接的一个接口。作用：建立数据库和java代码之间的连接。表示与数据库创建的连接
3. Statement(接口)、PreparedStatement(接口) (解决安全隐患问题，比如sql注入的问题)： 数据库操作，向数据库发送sql语句。执行SQL语句的对象
4. ResultSet(接口): 结果集或一张虚拟表。 Statement 发送sql语句，得到的结果 封装在 ResultSet 中。

JDBC访问数据库的步骤

1. 由DriverManager注册驱动程序
2. 创建和数据库的连接对象Connection
3. 由客户端发送SQL语句给服务器执行，SQL语句封装成Statement对象
4. 查询到的结果集封装成ResultSet对象
5. 在客户端可以从ResultSet中取出数据，处理结果集
6. 释放资源，关闭连接对象

注册驱动（一般使用Class.forName()方法）：Class.forName("com.mysql.jdbc.Driver");
获取Connection连接：DriverManager.getConnection(数据库url, 账号, 密码)
url格式：jdbc:mysql://localhost:3306/数据库

JDBC连接数据库出现时区错误（MySQL jdbc 6.0 版本以上必须配置此参数）：

1. 修改数据库时区
2. 修改链接时区jdbc:mysql://localhost:3306/dataBaseName?serverTimezone=Asia/Shanghai//或者serverTimezone=UTC（标准时区）

出现字符编码问题：（中文无法查询，中文插入乱码等）
添加?useUnicode=true&characterEncoding=utf8

获取Statement对象：connection对象成员方法createStatement();
或者PreparedStatement对象.
使用statement成员方法：executeQuery(sql语句)、executeUpdate(sql语句)执行查询和增删改。
获取PreparedStatement：使用connection成员方法prepareStatement(sql)预编译sql语言，然后使用setString(index，value)设置值（或设置其他类型）
从获取到的ResultSet对象来获取数据。

Connection设置事务：setAutoCommit(boolean autoCommit)
提交：commit()，回滚：rollback()

连接池

常用的连接池实现组件有以下这些：

1. 阿里巴巴-德鲁伊Druid连接池：Druid是阿里巴巴开源平台上的一个项目，整个项目由数据库连接池、插件框架和SQL解析器组成。该项目主要是为了扩展JDBC的一些限制，可以让程序员实现一些特殊的需求。
2. C3P0是一个开源的JDBC连接池，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。C3P0有自动回收空闲连接功能。
3. DBCP(DataBase Connection Pool)数据库连接池，是Apache上的一个Java连接池项目。dbcp没有自动回收空闲连接的功能。

C3P0连接池

C3P0常用的配置参数解释

参数	说明
driverClass	数据库驱动类。例如：com.mysql.jdbc.Driver（MySQL 8.0需要加载这个：com.mysql.cj.jdbc.Driver）
jdbcUrl	连接数据库的url地址。例如：jdbc:mysql://localhost:3306/day05_db（有时需要设置时区或者编码格式）
user	连接数据库的用户名。例如：root
password	连接数据库的密码。例如：1234
initialPoolSize	刚创建好连接池的时候连接数量
maxPoolSize	连接池中最多可以放多少个连接

src下新建c3p0配置文件：c3p0-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
    <default-config>
        <property name = "driverClacc">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/how2sql?serverTimezone=Asia/Shanghai</property>
        <property name="user">user</property>
        <property name="passwd">passwd</property>
    </default-config>

    <named-config name = "intergalactoApp">
    </named-config>
</c3p0-config>

com.mchange.v2.c3p0.ComboPooledDataSource类表示C3P0的连接池对象，常用2种创建连接池的方式：

//无参构造使用默认配置（使用xml中default-config标签中对应的参数）
public ComboPooledDataSource()

//从连接池中取出一个连接
public Connection getConnection() throws SQLException

//有参构造使用命名配置（configName：xml中配置的名称，使用xml中named-config标签中对应的参数）
public ComboPooledDataSource(String configName)//c3p0可以直接加载xml而Druid必须加载Property对象

只添加c3p0的jar包会报找不到类的错误，需要同时添加mchange-commons-java-0.2.15包

示例：

JDBC工具类

public class JDBCUtils {
    static String driverClass = null;
    static String url = null;
    static String user = null;
    static String password = null;

    static {
        // 需求： 通过properties对象读取 外部配置的内容
        Properties prop = new Properties();

        try {
            FileInputStream in = new FileInputStream("files/jdbc.properties");
            // 加载外部的配置文件
            prop.load(in);
            // 读取外部配置文件的内容
            driverClass = prop.getProperty("driverClass");
            url = prop.getProperty("url");
            user = prop.getProperty("user");
            password = prop.getProperty("password");
            // 注册驱动
            Class.forName(driverClass);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    // 获得连接
    public static Connection getConnection() {
        Connection con = null;
        try {
            con = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return con;
    }

    public static void release(Connection connection, Statement statement, ResultSet resultSet) {
        // 释放资源
        try {
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

上面这个有点类似DAO类。
配置文件

c3p0配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
    <default-config>
        <property name="driverClacc">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/how2sql?serverTimezone=Asia/Shanghai</property>
        <property name="user">user</property>
        <property name="password">passwd</property>
    </default-config>

    <named-config name="intergalactoApp">
    </named-config>

    <named-config name="day04">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/day_04db</property>
        <property name="user">root</property>
        <property name="password">123</property>
    </named-config>
</c3p0-config>

测试类

测试类

ComboPooledDataSource cpds = new ComboPooledDataSource();//可以在这填入参数选择不同的参数
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
    conn = cpds.getConnection();
    String sql = "select * from user";
    stmt = conn.prepareStatement(sql);
    rs = stmt.executeQuery();
    while (rs.next()) {
        System.out.println(rs.getInt("id") + " "
                + rs.getString("username") + " "
                + rs.getString("password"));
    }
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    JDBCUtils.release(conn, stmt, rs);
}

配置文件名称是 c3p0-config.xml 文件，并且必须在src 目录下

Druid连接池

Druid是阿里巴巴开发的数据库连接池，Gayhub地址

Druid常用的配置参数

url	数据库连接字符串jdbc:mysql://localhost:3306/数据库名
username	数据库的用户名
password	数据库的密码
driverClassName	驱动类名。根据url自动识别，这一项可配可不配，如果不配置druid会根据url自动识别数据库的类型，然后选择相应的数据库驱动名
initialSize	初始化时建立的物理连接的个数。初始化发生在显式调用init方法，或者第一次获取连接对象时
maxActive	连接池中最大连接数
maxWait	获取连接时最长等待时间，单位是毫秒。

基本使用

核心类：DruidDataSourceFactory

获取数据源的方法：使用com.alibaba.druid.pool.DruidDataSourceFactory类中的静态方法：

创建一个连接池，连接池的参数使用properties中的数据
public static DataSource createDataSource(Properties properties)

栗子：

fdruid.properties配置文件

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/how2sql?serverTimezone=Asia/Shanghai
username=user
password=passwd

测试类：

测试类

@Test
public void druid() throws Exception {
    //加载properties文件的内容到Properties对象中
    Properties info = new Properties();
    //加载项目下的属性文件
    FileInputStream fis = new FileInputStream("files/druid.properties");
    //从输入流中加载属性
    info.load(fis);
    System.out.println(info);
    //创建DRUID连接池，使用配置文件中的参数
    DataSource dataSource = DruidDataSourceFactory.createDataSource(info);
    //从DRUID连接池中取出连接
    //Connection conn = dataSource.getConnection();
    //System.out.println("conn = " + conn);
// 需求: 根据用户名和密码 查询用户信息
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        // 获得连接
        conn = dataSource.getConnection();
        // 获得发送sql的对象
        String sql = "select * from user where id = ?";
        pstmt = conn.prepareStatement(sql);
        // 如果有问号,需要 设置参数,注意:下标从1开始
        pstmt.setInt(1, 1);
        // 执行sql 获得结果
        rs = pstmt.executeQuery();
        // 处理结果
        if (rs.next()) {
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String pwd = rs.getString("password");
            System.out.println(id + ":::" + username + "===" + pwd);
        } else {
            System.out.println("没有查到对应的用户信息!");
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.release(conn, pstmt, rs);
    }
}

工具类优化：

工具类优化

public static Connection getDruidConnection()
{
    try {
        //创建Properties集合对象
        Properties p = new Properties();
        //调用方法加载配置文件中的数据
        p.load(new FileInputStream("files/druid.properties"));
        //使用德鲁伊核心类调用静态方法获取配置文件中的数据
        DataSource ds = DruidDataSourceFactory.createDataSource(p);
        //从德鲁伊数据库连接池中获取连接
        Connection conn = ds.getConnection();
        //返回连接
        return conn;
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

---

JdbcTemplate

jar包介绍：

在JdbcTemplate类中执行SQL语句的方法大致分为3类：

JdbcTemplate中的方法	功能说明
execute()	用于执行DDL语句，如：建表 了解
update()	用于执行DML语句，实现对数据库表的增删改操作
queryXxx()	用于执行DQL语句，实现对数据库表的各种查询的操作

创建表示例：

String sql = "create table student(id int primary key auto_increment,name varchar(20) not null,birthday date)";
        //1. 创建JdbcTemplate对象，传入C3P0连接池,模板对象自己获得连接并且自己释放资源
JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());//这里传入的是连接池，可以使用c3p0的，也可以使用Druid的（c3p0：ComboPooledDataSource，）
//2. 创建表，执行DDL语句
jdbcTemplate.execute(sql);

增删改：
org.springframework.jdbc.core.JdbcTemplate类方便执行SQL语句

JdbcTemplate类的方法	说明
public int update(String sql, Object…args)	执行DML语句，对数据库表中的数据进行增删改操作返回：影响的行数参数：1) sql语句2) args是用来替换占位符的真实值，可变的参数

示例：

增删改例子

private static JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());

@Test
public void delete2() {
    int row = jdbcTemplate.update("delete from student where id=?", 6);
    System.out.println("删除" + row + "行记录");
}

@Test
public void update() {
    int row = jdbcTemplate.update("update student set name=?, birthday=? where id=?",
            "克林", "1994-12-10", 5);
    System.out.println("更新了" + row + "行记录");
}

@Test
public void insert() {
    jdbcTemplate.update("insert into student values (null, ?,?)", "孙悟空", "1999-12-10");
    jdbcTemplate.update("insert into student values (null, ?,?)", "孙悟天", "1999-12-10");
    jdbcTemplate.update("insert into student values (null, ?,?)", "孙悟饭", "1999-12-10");
    jdbcTemplate.update("insert into student values (null, ?,?)", "龟仙人", "1999-12-10");
    jdbcTemplate.update("insert into student values (null, ?,?)", "牛魔王", "1999-12-10");
    jdbcTemplate.update("insert into student values (null, ?,?)", "18号", "1997-12-10");
}

查

单个数据查询：queryForobject

public <T> T queryForObject(String sql, Class<T> requiredType, Object... args):传入参数, 执行查询语句，返回一个指定类型的数据。
说明：

1. 返回查询只有单一对象的结果，这个单一结果应该是简单的数据类型，如：int.class、long.class、 String.class。也可以用于聚合函数的查询结果。
2. 参数：
   sql:表示要执行的sql语句.
   requiredType:表示必须给的类型，最后需要返回的Class类的对象.
   args：属于可变参数，传入的真实参数，例如给sql语句的占位符赋值的真实值.
   使用举例：
   String name= jdbcTemplate.queryForObject("select name from student where id=?",String.class,2);
   第一个参数表示一条sql语句，
   String.class表示查询出来name列的类型的Class对象，
   2 表示id是2的数据
3. 查询的是单行单列。（或者说单个数据）

单行多列查询:queryForMap

public Map<String, Object> queryForMap(String sql, Object... args) 传入参数，执行查询语句，将一条记录放到一个Map中。
说明：

1. 参数：
   sql 表示查询的sql语句
   args 表示给第一个参数的sql语句的占位符，设置值
   使用举例：
   Map<String, Object> map = jdbcTemplate.queryForMap("select * from student where id=?", 2);
   第一个参数表示一条sql语句
   2 表示id是2的数据
2. 查询单行多列

多行多列查询：queryForList

public List<Map<String, Object>> queryForList(String sql, Object... args) 传入参数，执行查询语句，返回一个List集合，List中存放的是Map类型的数据。
说明：

1. 使用举例：
   List<Map<String, Object>> mapList = jdbcTemplate.queryForList("select * from student");
   Key :表示列名
   Object:value 表示列值
2. 多行多列。

BeanPropertyRowMapper做映射返回对象封装成List<JavaBean>
public List query(String sql, RowMapper rowMapper)执行查询语句，返回一个List集合，List中存放的是RowMapper指定类型的数据。
说明：
1）query()必须要指定查询的结果集与JavaBean属性之间的对应关系，而结果集与JavaBean属性之间的对应关系需要传递一个接口作为query方法参数来确定。这个接口就是：RowMapper。
而RowMapper属于接口，不能创建对象，我们使用其实现类 BeanPropertyRowMapper来创建对象。
BeanPropertyRowMapper类如下所示：

public class BeanPropertyRowMapper<T> implements RowMapper<T>

我们发现：BeanPropertyRowMapper类实现了RowMapper接口。所以我们只需要创建BeanPropertyRowMapper类的对象，然后将创建好的对象作 为query方法的第二个参数传递即可。
2）BeanPropertyRowMapper类的构造方法说明：BeanPropertyRowMapper(Class<T> mappedClass)
补充：mappedClass表示某个类的Class对象，这里我们在使用的时候只需要在构造方法中给某个类的.class即可。
例如：Student.class。
只要我们给某个类的.class对象，在底层那么就会将数据库表中对应的字段值给这个类的成员变量赋值，所以在创建JavaBean的时候，要求成员变量名一定要和数据库表中的字段名一致，这样才可以完成封装。
使用举例：

List<Student> students = jdbcTemplate.query("select * from student", new BeanPropertyRowMapper<>(Student.class));