一、JDBC

1.1概念

	JDBC（Java Database Connectivity） Java 连接数据库的规范（标准），可以使用 Java 语言连接数据库完成 CRUD 操作。

1.2 API

类型	权限定名	简介
class	java.sql.DriverManager	管理多个数据库驱动类，提供了获取数据库连接的方法
interface	java.sql.Connection	代表一个数据库连接（当connection不是null时，表示已连接数据库）
interface	java.sql.Statement	发送SQL语句到数据库工具
interface	java.sql.ResultSet	保存SQL查询语句的结果数据（结果集）
class	java.sql.SQLException	处理数据库应用程序时所发生的异常

1.3 环境搭建

1. 在项目下新建一个lib目录，在目录中导入相关的jar包，直接复制进目录（注意mysql版本，不同版本不同驱动）
   
2. 将mysql-connector-java-5.1.19 添加到项目库，右键 Add as Libraay，点击 OK。

二、开发步骤

2.1 注册驱动

//mysql 5.X 版本
Class.forName("com.mysql.jdbc.Driver");//加载驱动

2.2 获取连接对象

• 通过 DriverManager.getConnection(url,user,password) 获取数据库连接对象
  • URL:jdbc:mysql://localhost:3306/database
  • username:root
  • password:1234

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "123456");

2.3 获取发送 SQL 的对象

通过 Connection 对象获得 Statement 对象，用于对数据库进行通用访问。

Statement statement = connection.createStatement();

2.4 执行SQL 语句

执行 SQL 语句并接收执行结果。

String sql = "INSERT INTO `user`( `name`, `password`) VALUES ('ahao', '123456')";
        int i = statement.executeUpdate(sql);

• 注意：在编写 DML 语句时，一定要注意字符串参数的符号是单引号 ‘值’
• DML 语句：增删改时，返回受影响行数（int 类型）。
• DQL 语句：查询时，返回结果数据(ResultSet 结果集)。

2.5 处理结果

接受处理操作结果。

if(i == 1){
	System.out.println("受影响的行： "+i);
}

• 受影响行数：逻辑判断、方法返回。
• 查询结果集：迭代、依次获取。

2.6 释放资源

遵循先开后关原则，释放所使用到的资源对象。

statement.close();
connection.close();

三、CRUD 案例

数据库表结构素材：

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `password` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

3.1 插入数据

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

public class InsertDemo {
    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/jdbc?useUnicode=true&characterEncoding=utf-8", "root", "123456");
        //3.获取statement对象
        Statement statement = connection.createStatement();
        //4.执行sql
        String sql = "INSERT INTO `user`( `username`, `password`) VALUES ('ahao', '123456')";
        int i = statement.executeUpdate(sql);
        //5.处理结果
        if (i>0){
            System.out.println("受影响的行："+i);
        }
        //6.关闭资源
        statement.close();
        connection.close();
    }
}

3.2 删除数据（根据id）

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

public class DeleteDemo {
    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/jdbc?useUnicode=true&characterEncoding=utf-8", "root", "123456");
        //3.获取statement对象
        Statement statement = connection.createStatement();
        //4.执行sql  删除id为1 的数据
        String sql = "DELETE FROM USER WHERE id=1";
        int i = statement.executeUpdate(sql);
        //5.处理结果
        if (i>0){
            System.out.println("受影响的行："+i);
        }
        //6.关闭资源
        statement.close();
        connection.close();
    }
}

3.3 更新数据（修改）

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

public class UpdateDemo {
    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/jdbc?useUnicode=true&characterEncoding=utf-8", "root", "123456");
        //3.获取statement对象
        Statement statement = connection.createStatement();
        //4.执行sql  将id为2的用户名改为 法外狂徒
        String sql = "update user set username='法外狂徒' where id=2";
        int i = statement.executeUpdate(sql);
        //5.处理结果
        if (i>0){
            System.out.println("受影响的行："+i);
        }
        //6.关闭资源
        statement.close();
        connection.close();
    }
}

3.4 查询

import java.sql.*;

public class SelectDemo {
    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/jdbc?useUnicode=true&characterEncoding=utf-8", "root", "123456");
        //3.获取statement对象
        Statement statement = connection.createStatement();
        //4.执行sql  查询user表中的所有数据
        String sql = "select * from user";
        ResultSet rs = statement.executeQuery(sql);
        //5.处理结果
        while (rs.next()){
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            String address = rs.getString("address");
            System.out.println(id+"\t"+username+"\t"+password+"\t"+address);
        }
        //6.关闭资源
        statement.close();
        connection.close();
    }
}

四、 SQL注入

4.1 什么是 SQL 注入

用户输入的数据中有 SQL 关键字或语法并且参与了 SQL 语句的编译，导致 SQL 语句编译后的条件含义为 true，一直得到正确的结果。这种现象称为 SQL 注入。

4.2 如何避免 SQL 注入

使 SQL 语句在用户输入数据前就已进行编译成完整的 SQL 语句，再进行填充数据。

五、prepareStatement

5.1 PreparedStatement的应用

• 预编译SQL 语句，效率高。
• 安全，避免SQL注入 。
• 可以动态的填充数据，执行多个同构的 SQL 语句。

5.1.1 参数标记

//1.预编译 SQL 语句
PreparedStatement  ps = connection.prepareStatement("insert into user (username,password,address) values (?,?,?)");

• 注意：JDBC中的所有参数都由 ？符号占位，这被称为参数标记。在执行SQL语句之前，必须为每个参数提供值。

5.1.2 动态参数绑定

ps.setXxx(下标,值) 参数下标从 1 开始，为指定参数下标绑定值

//1.预编译 SQL 语句
PreparedStatement  ps = connection.prepareStatement("insert into user (username,password,address) values (?,?,?)"); 
//2.为参数下标赋值
ps.setString(1, user.getUsername());
ps.setString(2, user.getPassword());
ps.setString(3, user.getAddress());

六、封装工具类

6.1 重用

import java.sql.*;

public class DBUtils {

    static {//类加载，执行一次
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取连接对象
    public static Connection getConnection(){
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8", "root", "123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //释放资源
    public static void close(ResultSet resultSet){
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(PreparedStatement ps){
        if (ps!=null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Connection con){
        if (con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

七、Druid连接池

7.1 概念

在程序初始化时，预先创建指定数量的数据库连接对象存储在池中。当需要连接数据库时，从连接池中取出现有连接；使用完毕后，也不会进行关闭，而是放回池中，实现复用，节省资源。

7.2 Druid连接池使用步骤

1. 引入druid -1.2.8.jar
2. 创建 db.properties 配置文件。

db.properties文件：

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///jdbc?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
#设置连接数据库用户名
username=root
#设置连接密码
password=123456
#初始化连接数
initialSize=100
#最小空闲数，如果当前没有人使用 connection，连接池中也会随时保证有 10 个 connection 待命
minIdle=10
#最大等待时间，当所有的 connection 都被使用时，新的操作只能等待，等待时间超过 1 分钟，就会抛出异常
maxWait=60000
#连接池最大连接数
maxActive=200

连接池工具类：

public class DbUtils {
	//声明连接池对象
	private static DruidDataSource ds;
	static{
		//实例化配置对象
		Properties properties=new Properties();
		try {
			  //加载配置文件内容
              properties.load(DbUtils.class.getResourceAsStream("db.properties"));
              ds = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);	
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	//获取连接对象
	public static Connection getConnection() {
		try {
			return ds.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
  //释放资源。。
}

八、事务

8.1 jdbc中的事务

在JDBC 中，获得 Connection 对象开始事务–提交或回滚–关闭连接。其事务策略是

• conn.setAutoCommit(false);//true 等价于 1，false 等价于 0
• conn.commit();//手动提交事务
• conn.rollback();//手动回滚事务

service层中控制事务

import java.sql.Connection;
import java.sql.SQLException;

public class AccountService {
    AccountDao accountDao = new AccountDao();

    /**
     * 转账的业务方法
     * @param from 来自谁
     * @param to 给谁转
     * @param money 转账金额
     */
    public void transferAccount(String from,String to,Double money){
        Connection connection = DBUtils.getConnection();
        try {
            connection.setAutoCommit(false);//开启事务
            accountDao.drawMoney(from,money,connection);
            accountDao.saveMoney(to,money,connection);
            //提交事务
            connection.commit();
        } catch (SQLException e) {
            try {
                //回滚
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }
        DBUtils.close(connection);
    }
}

8.2 解决方案1：传递 Connection

为了解决线程中Connection 对象不同步的问题，可以将 Connection对象通过 service传递给各个DAO 方法吗？

8.2.1 传递的问题

• 如果使用传递Connection，容易造成接口污染（BadSmell）。
• 定义接口是为了更容易更换实现，而将 Connection定义在接口中，会造成污染当前接口。

8.3 解决方案2：ThreadLocal

• 可以将整个线程中（单线程）中，存储一个共享值。
• 线程拥有一个类似 Map 的属性，键值对结构<ThreadLocal对象，值>。

8.4 ThreadLocal应用

一个线程共享同一个 ThreadLocal，在整个流程中任一环节可以存值或取值。

8.4.1 参数绑定

在 DBUtils中，将当前 Connection对象添加到 ThreadLocal 中。

ThreadLocal<Connection> tl = new ThreadLocal<>();

//getConnection 方法修改
public static Connection getConnection(){
  Connection conn = tl.get();//获取线程中存储的 Connection 对象
  if(conn ==null){
    conn = ds.getConnection();//从连接池中获取一个连接
    tl.set(conn);//存储到线程对象中。
  }
  return conn;
}
 //关闭所有连接  增加 tl.remove(); 移除
    public static void closeAll(ResultSet rs, Statement st, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (conn != null) {
                conn.close();
                tl.remove();//将 conn 移除。
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

九、Apache的DbUtils使用

---

Commons DbUtils 是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能勾简化JDBC应用程序的开发！同时，不会影响程序的性能。

9.1 DbUtils简介

• DbUtils是Java编程中数据库操作实用小工具，小巧、简单、实用
  • 对于数据表的查询操作，可以把结果转换为List、Array、Set等集合。便于操作。
  • 对于数据表的DML操作，也变得很简单(只需要写SQL语句)。

9.1.1 DbUtils主要包含

• ResultSetHandler接口：转换类型接口
  • BeanHandler类：实现类，把一条记录转换成对象
  • BeanListHandler类：实现类，把多条记录转换成List集合。
  • ScalarHandler类：实现类，适合获取一行一列的数据。
• QueryRunner：执行sql语句的类
  • 增、删、改：update();
  • 查询：query();

十、Apache DbUtils的使用案例

• 导入jar包
  • mysql连接驱动jar包
  • druid-1.1.5.jar
  • database.properties配置文件
  • commons-dbutils-1.6.jar

stop-01：
引入相关jar包
jar包下载地址：https://search.maven.org/

step-02：
配置db.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///jdbc?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
#设置连接数据库用户名
username=root
#设置连接密码
password=123456
#初始化连接数
initialSize=100
#最小空闲数，如果当前没有人使用 connection，连接池中也会随时保证有 10 个 connection 待命
minIdle=10
#最大等待时间
maxWait=60000
#连接池最大连接数
maxActive=200

step-03：数据库表（id，username,password,address）

工具类-DBUtils.java

package com.ahao.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.util.Properties;

public class DBUtils {
    private static DataSource dataSource = null;
    private static final Properties PROPERTIES = new Properties();

    static {
        try {
            PROPERTIES.load(DBUtils.class.getResourceAsStream("/jdbc.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(PROPERTIES);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static DataSource getDataSource(){
        return dataSource;
    }
}

实体类-User.java

package com.ahao.model;

public class User {
    private Integer id;
    private String username;
    private String password;
    private String address;

    public User() {
    }

    public User(Integer id, String username, String password, String address) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.address = address;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

UserDao.java

package com.ahao.dao;

import com.ahao.model.User;
import com.ahao.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao {

    QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());


    /**
     * 添加用户
     * @param user
     * @return
     * @throws SQLException
     */
    public Integer addUser(User user) throws SQLException {
        return queryRunner.update("insert into user (username,password,address) values (?,?,?)",user.getUsername(),user.getPassword(),user.getAddress());
    }

    /**
     * 根据 id 删除用户
     * @param id
     * @return
     * @throws SQLException
     */
    public Integer deleteUserById(Integer id) throws SQLException {
        return queryRunner.update("delete from user where id=?",id);
    }

    /**
     * 根据id修改用户名
     * @param id
     * @param username
     * @return
     * @throws SQLException
     */
    public Integer updateUserByid(Integer id,String username) throws SQLException {
        return queryRunner.update("update user set username=? where id=?",username,id);
    }

    /**
     * 根据id查用户
     * @param id
     * @return
     */
    public User selectUserById(Integer id) throws SQLException {
        User query = queryRunner.query("select * from user where id=?", new ResultSetHandler<User>() {
            @Override
            public User handle(ResultSet resultSet) throws SQLException {
                resultSet.next();
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String address = resultSet.getString("address");
                return new User(id, username, password, address);
            }
        }, id);
        return query;
    }

    public List<User> selectAllUsers() throws SQLException {
        List<User> query = queryRunner.query("select * from user", new ResultSetHandler<List<User>>() {
            @Override
            public List<User> handle(ResultSet resultSet) throws SQLException {
                ArrayList<User> users = new ArrayList<>();
                User user = null;
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String username = resultSet.getString("username");
                    String password = resultSet.getString("password");
                    String address = resultSet.getString("address");
                    user = new User(id, username, password, address);
                    users.add(user);
                }
                return users;
            }
        });
        return query;
    }

}

Test.java

package com.ahao;

import com.ahao.dao.UserDao;
import com.ahao.model.User;

import java.sql.SQLException;
import java.util.List;

public class Test{
    public static void main(String[] args) throws SQLException {
        UserDao userDao = new UserDao();

        //添加
        /*User user = new User(6,"干将莫邪","1231564","外星人");
        userDao.addUser(user);*/

        //删除
        //userDao.deleteUserById(11);

        //修改
        //userDao.updateUserByid(6,"黄牛");

        //查询
        //User user = userDao.selectUserById(2);
        //System.out.println(user);

        //查询所有
        List<User> users = userDao.selectAllUsers();
        users.forEach(o-> System.out.println(o));
    }
}

数据的进一步封装

public class UserDao {
    /**
     * 创建一个 queryrunner 对象，增删改查操作将来都通过这个对象来完成
     *
     * 构造方法有一个数据源参数，这个参数可传可不传
     * 传：以后的增删改查就不需要传递数据源或者 Connection
     * 不传：以后的增删改查就需要传递 Connection
     */
    private QueryRunner queryRunner = new QueryRunner(DBUtils.getDs());

    public Long getCount() throws SQLException {
        /**
         * 如果查询的结果是一行一列，那么可以使用 ScalarHandler 自动将这一行一列读取出来
         */
        Long count = queryRunner.query("select count(*) from user", new ScalarHandler<Long>());
        return count;
    }

    public List<User> getAllUsers2() throws SQLException {
        //BeanListHandler 表示返回的是一个 List 集合
        return queryRunner.query("select * from user", new BeanListHandler<>(User.class));
    }

    /**
     * 如果数据库中的字段和对象的属性名一一对应的话，那么有一个简写的类可以直接使用
     * @param id
     * @return
     */
    public User getUserById2(Integer id) throws SQLException {
        /**
         * 注意这里的第二个参数，这个是系统提供的。
         * 如果数据库查询结果的字段名和Java对象中的get/set 方法推断出来的属性名是一一对应的，那么可以自动映射
         */
        return queryRunner.query("SELECT * FROM USER WHERE id=?", new BeanHandler<>(User.class), id);
    }

    public List<User> getAllUsers() throws SQLException {
        List<User> list = queryRunner.query("select * from user", new ResultSetHandler<List<User>>() {
            @Override
            public List<User> handle(ResultSet resultSet) throws SQLException {
                List<User> users = new ArrayList<>();
                while (resultSet.next()) {
                    User u = new User();
                    int id = resultSet.getInt("id");
                    String username = resultSet.getString("username");
                    String password = resultSet.getString("password");
                    String address = resultSet.getString("address");
                    u.setId(id);
                    u.setName(username);
                    u.setPassword(password);
                    u.setAddress(address);
                    users.add(u);
                }
                return users;
            }
        });
        return list;
    }

    /**
     * 根据 id 查询一个用户对象
     * @param id
     * @return
     */
    public User getUserById(Integer id) throws SQLException {
        /**
         * 查询用户
         * 1. 查询的 sql
         * 2. ResultSetHandler 结果集处理器
         */
        User user = queryRunner.query("select * from user where id=?", new ResultSetHandler<User>() {
            /**
             * 自己在这里手动处理查询结果集的映射
             * @param resultSet 这个就是查询结果集
             * @return
             * @throws SQLException
             */
            @Override
            public User handle(ResultSet resultSet) throws SQLException {
                User u = new User();
                //由于是根据id查询的，所以结果集中只有一行，用 if 即可
                if (resultSet.next()) {
                    int id1 = resultSet.getInt("id");
                    String username = resultSet.getString("username");
                    String address = resultSet.getString("address");
                    String password = resultSet.getString("password");
                    u.setId(id1);
//                    u.setUsername(username);
                    u.setPassword(password);
                    u.setAddress(address);
                }
                return u;
            }
        }, id);
        return user;
    }

    /**
     * 根据 id 删除一条记录
     * @param id
     * @return
     * @throws SQLException
     */
    public Integer deleteUserById(Integer id) throws SQLException {
        return queryRunner.update("delete from user where id=?", id);
    }

    /**
     * 根据 id 修改用户名
     * @param username
     * @param id
     * @return
     */
    public Integer updateUsernameById(String username, Integer id) throws SQLException {
        return queryRunner.update("update user set username=? where id=?", username, id);
    }

    /**
     * 添加用户
     * @param user
     * @return
     */
    public Integer addUser(User user) throws SQLException {
        /**
         * update 方法可以做增删改
         * 1. 执行的 SQL，和 jdbc 中一样，也是使用 ? 做占位符
         * 2. 可变长度的参数，和 sql 中的占位符一一对应
         *
         * 返回值表示受影响的行数
         */
        int update = queryRunner.update("insert into user(username,address,password) values(?,?,?)", user.getName(), user.getAddress(), user.getPassword());
        return update;
    }
}