JDBC基础

1 JDBC概述

1.1 数据的持久化

• 持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。 大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以”固化”，而持久化的实现过程大多通过各种关系数据库来完成。
• 持久化的主要应用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数据文件中。
  

1.2 Java中的数据存储技术

• 在Java中，数据库存取技术可分为如下几类：
  • JDBC直接访问数据库
  • JDO (Java Data Object )技术
  • 第三方O/R工具，如Hibernate, Mybatis 等
• JDBC是java访问数据库的基石，JDO、Hibernate、MyBatis等只是更好的封装了JDBC。

1.3 JDBC介绍

• JDBC(Java Database Connectivity)是一个独立于特定数据库管理系统、通用的SQL数据库存取和操作的公共接口（一组API），定义了用来访问数据库的标准Java类库，（java.sql,javax.sql）使用这些类库可以以一种标准的方法、方便地访问数据库资源。
• JDBC为访问不同的数据库提供了一种统一的途径 ，为开发者屏蔽了一些细节问题。
• JDBC的目标是使Java程序员使用JDBC可以连接任何提供了JDBC驱动程序 的数据库系统，这样就使得程序员无需对特定的数据库系统的特点有过多的了解，从而大大简化和加快了开发过程。
• 如果没有JDBC，那么Java程序访问数据库时是这样的：
  
• 有了JDBC，Java程序访问数据库时是这样的：
  
• 总结如下：
  

1.4 JDBC体系结构

• JDBC接口（API）包括两个层次：
  • 面向应用的API： Java API，抽象接口，供应用程序开发人员使用（连接数据库，执行SQL语句，获得结果）。
  • 面向数据库的API： Java Driver API，供开发商开发数据库驱动程序用。
• JDBC是sun公司提供一套用于数据库操作的接口，java程序员只需要面向这套接口编程即可。
• 不同的数据库厂商，需要针对这套接口，提供不同实现。不同的实现的集合，即为不同数据库的驱动。 ————面向接口编程

2 JDBC详解

2.1 JDBC入门案例

2.1.1 导入数据

导入脚本

2.1.2 创建maven项目引入依赖

pom.xml中的依赖

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.31</version>
        </dependency>
    </dependencies>

2.1.3 HelloJDBC

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //Connection
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
        //Statement，PrepareStatement
        PreparedStatement ps = conn.prepareStatement("SELECT name FROM customers");
        //ResultSet
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString("name"));
        }
        //必须关闭连接
        rs.close();
        ps.close();
        conn.close();
    }

2.2 PreparedStatement 和 Statement

• 数据库连接被用于向数据库服务器发送命令和 SQL 语句，并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接。
• 在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式：
  • Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。
  • PrepatedStatement：SQL 语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句。
  • CallableStatement：用于执行 SQL 存储过程
    

2.2.1 PreparedStatement介绍

• 可以通过调用 Connection 对象的 preparedStatement(String sql) 方法获取 PreparedStatement 对象
• PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句
• PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始)，第二个是设置的 SQL 语句中的参数的值

2.2.2 PreparedStatement vs Statement

• PreparedStatement 能最大可能提高性能：
  • DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用，所以语句在被DBServer的编译器编译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数直接传入编译过的语句执行代码中就会得到执行。
  • 在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。
  • (语法检查，语义检查，翻译成二进制命令，缓存)
• PreparedStatement 可以防止 SQL 注入
• Statement需要拼接SQL语句代码繁琐，存在SQL注入问题

2.3 案例演示

2.3.1 登录Statement实现案例

public class StatementTest {

    /**
     * Statement
     *   - 不能预编译语句，性能差
     *   - sql语句，拼接字符串，繁琐
     *   - sql注入问题
     *      - 用户名输入：”1' or “
     *      - 密码输入：“=1 or '1' = '1”
     */
    public static void main(String[] args) throws Exception {
        Scanner sc = new Scanner(System.in);
        System.out.println("输入用户名");
        String username = sc.nextLine();
        System.out.println("输入密码");
        String password = sc.nextLine();

        String sql = "SELECT * FROM user_table WHERE USER = '" + username +  "' AND PASSWORD = '" + password +"'";
        Class.forName("com.mysql.cj.jdbc.Driver");
        //Connection
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
        //Statement，PrepareStatement
        Statement st = conn.createStatement();
        //ResultSet
        ResultSet rs = st.executeQuery(sql);
        if (rs.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
        //必须关闭连接
        rs.close();
        st.close();
        conn.close();
    }

}

2.3.2 登录ParepareStatement实现案例

public class PrepareStatementTest {

    /**
     * PrepareStatement
     *   - 不能预编译语句，性能差
     *   - sql语句，拼接字符串，繁琐
     *   - sql注入问题
     *      - 用户名输入：”1' or “
     *      - 密码输入：“=1 or '1' = '1”
     */
    public static void main(String[] args) throws Exception {
        Scanner sc = new Scanner(System.in);
        System.out.println("输入用户名");
        String username = sc.nextLine();
        System.out.println("输入密码");
        String password = sc.nextLine();

        String sql = "SELECT * FROM user_table WHERE USER = ? AND PASSWORD = ?";
        Class.forName("com.mysql.cj.jdbc.Driver");
        //Connection
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
        //Statement，PrepareStatement
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, username);
        ps.setString(2, password);
        //ResultSet
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
        //必须关闭连接
        rs.close();
        ps.close();
        conn.close();
    }
}

3 JDBC的CRUD实现

3.1 程序的分层

java开发手册推荐的工程的分层

• DAO（data access object）：数据访问层（jdbc）
• Service：业务逻辑层（调用dao）
• Controller：控制层
• po：实体类，领域模型(persistence object）

3.2 领域模型（实体类）

创建表对应的实体类，表->类，字段->属性

Java类型	SQL类型
boolean	BIT
byte	TINYINT
short	SMALLINT
int	INTEGER
long	BIGINT
String	CHAR,VARCHAR,LONGVARCHAR
byte array	BINARY , VAR BINARY
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP

User类

public class User{

    private int id; //实体类不要使用基本数据类型，使用包装类，原始基本数据类型不能接受null
    private String name;
    private String password;
    private String address;
    private String phone;

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    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;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }
}

Order类

public class Order {

    private Integer orderId;
    private String orderName;
    private Date orderDate;

    public Integer getOrderId() {
        return orderId;
    }

    public void setOrderId(Integer orderId) {
        this.orderId = orderId;
    }

    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }

    public Date getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }
}

3.3 Dao

UserDao

public class UserDao {

    /**
     * 查询所有
     * @return
     */
    public List<User> find() {
        List<User> list = new ArrayList<>(10);
        String sql = "SELECT `id`,  `name`,  `password`,  `address`,  `phone` from user";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt(1));
                user.setName(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setAddress(rs.getString("address"));
                user.setPhone("phone");
                list.add(user);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    /**
     * 查询主键对应的记录
     * @param id
     * @return
     */
    public User get(Integer id) {
        User user = new User();
        String sql = "SELECT `id`,  `name`,  `password`,  `address`,  `phone` from user where id = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                user.setId(rs.getInt(1));
                user.setName(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setAddress(rs.getString("address"));
                user.setPhone("phone");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }

    /**
     * 添加
     * @param user
     * @return 返回语句影响的函数
     */
    public int insert(User user) {
        int rows = 0;
        String sql = "insert into user(`name`, `password`, `address`, `phone`) values(?,?,?,?)";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getAddress());
            ps.setString(4, user.getPhone());

            rows = ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    /**
     * 删除
     * @param id
     * @return 返回语句影响的函数
     */
    public int delete(Integer id) {
        int rows = 0;
        String sql = "delete from user where id = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);

            rows = ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    /**
     * 添加
     * @param user
     * @return 返回语句影响的函数
     */
    public int update(User user) {
        int rows = 0;
        String sql = "update user set `name`=?, `password`=?,  `address`=?,  `phone`=? WHERE `id` = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getAddress());
            ps.setString(4, user.getPhone());
            ps.setInt(5, user.getId());

            rows = ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }
}

OrderDao

public class OrderDao {

    /**
     * `order_id`,  `order_name`,  `order_date`
     */

    /**
     * 查询所有
     * @return
     */
    public List<Order> find() {
        List<Order> list = new ArrayList<>(10);
        String sql = "SELECT `order_id`,  `order_name`,  `order_date` from `order`";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Order order = new Order();
                order.setOrderId(rs.getInt(1));
                order.setOrderName(rs.getString(2));
                order.setOrderDate(rs.getDate(3));
                list.add(order);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    /**
     * 查询主键对应的记录
     * @param id
     * @return
     */
    public Order get(Integer id) {
        Order order = new Order();
        String sql = "SELECT `order_id`,  `order_name`,  `order_date` from `order` where `order_id` = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                order.setOrderId(rs.getInt(1));
                order.setOrderName(rs.getString(2));
                order.setOrderDate(rs.getDate(3));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return order;
    }

    /**
     * 添加
     * @param order
     * @return 返回语句影响的函数
     */
    public int insert(Order order) {
        int rows = 0;
        String sql = "insert into `order`( `order_name`,  `order_date`) values(?,?)";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            ps.setString(1, order.getOrderName());
            ps.setDate(2, new Date(order.getOrderDate().getTime()) );

            rows = ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    /**
     * 删除
     * @param id
     * @return 返回语句影响的函数
     */
    public int delete(Integer id) {
        int rows = 0;
        String sql = "delete from `order` where order_id = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);

            rows = ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    /**
     * 添加
     * @param order
     * @return 返回语句影响的函数
     */
    public int update(Order order) {
        int rows = 0;
        String sql = "update `order` set `order_name`=?, `order_date`=? WHERE `order_id` = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
            ps = conn.prepareStatement(sql);
            ps.setString(1, order.getOrderName());
            ps.setDate(2, new Date(order.getOrderDate().getTime()));
            ps.setInt(3, order.getOrderId());

            rows = ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null && !rs.isClosed()) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null && !ps.isClosed()) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null && !conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }
}

3.4 测试

UserDaoTest测试

public class UserDaoTest {

    UserDao userDao = new UserDao();


    @Test
    public void find() {
        userDao.find().forEach(u -> System.out.println(u));
    }

    @Test
    public void get() {
        System.out.println(userDao.get(4));
    }

    @Test
    public void insert() {
        User user = new User(null, "周星驰", "123", "香港", "110");
        userDao.insert(user);
    }

    @Test
    public void delete() {
        userDao.delete(5);
    }

    @Test
    public void update() {
        User user = new User(1, "张学友", "123", "香港", "110");
        userDao.update(user);
    }
}

OrderDaoTest测试

public class OrderDaoTest {

    OrderDao dao = new OrderDao();

    @Test
    public void find() {
        dao.find().forEach(o -> System.out.println(o));
    }

    @Test
    public void get() {
        System.out.println(dao.get(1));
    }

    @Test
    public void insert() {
        Order o = new Order(null, "华为", new Date());
        dao.insert(o);
    }

    @Test
    public void delete() {
    }

    @Test
    public void update() {
    }
}

3.5 JDBCUtil

打开连接，关闭资源

属性文件jdbc.properties，配置连接字符串，驱动，用户，密码

user=root
password=123456
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.cj.jdbc.Driver

JdbcUtil

public class JdbcUtil {

    static String driver;
    static String url;
    static String user;
    static String password;

    static {
//        InputStream in = JdbcUtil.class.getResourceAsStream("/jdbc.properties");
        InputStream in = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties pro = new Properties();
        try {
            pro.load(in);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        driver = pro.getProperty("driverClass");
        url = pro.getProperty("url");
        user = pro.getProperty("user");
        password = pro.getProperty("password");

    }

    public static Connection getConnection() throws Exception {
        Class.forName(driver);
        return DriverManager.getConnection(url, user, password);
    }

    public static void closeResource(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if (rs != null && !rs.isClosed()) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (ps != null && !ps.isClosed()) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

在Dao层使用

    /**
     * 查询所有
     * @return
     */
    public List<User> find() {
        List<User> list = new ArrayList<>(10);
        String sql = "SELECT `id`,  `name`,  `password`,  `address`,  `phone` from user";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt(1));
                user.setName(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setAddress(rs.getString("address"));
                user.setPhone("phone");
                list.add(user);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);
        }
        return list;
    }

3.6 ResultSet和ResultSetMetaData

3.6.1 ResultSet

• 查询需要调用PreparedStatement 的 executeQuery() 方法，查询结果是一个ResultSet 对象
• ResultSet 对象以逻辑表格的形式封装了执行数据库操作的结果集，ResultSet 接口由数据库厂商提供实现
• ResultSet 返回的实际上就是一张数据表。有一个指针指向数据表的第一条记录的前面。
• ResultSet 对象维护了一个指向当前数据行的游标，初始的时候，游标在第一行之前，可以通过 ResultSet 对象的 next() 方法移动到下一行。调用 next()方法检测下一行是否有效。若有效，该方法返回 true，且指针下移。相当于Iterator对象的 hasNext() 和 next() 方法的结合体。
• 当指针指向一行时, 可以通过调用 getXxx(int index) 或 getXxx(int columnName) 获取每一列的值。
  • 例如: getInt(1), getString(“name”)
  • 注意：Java与数据库交互涉及到的相关Java API中的索引都从1开始。
• ResultSet 接口的常用方法：
  • boolean next()
  • getString()
  • …
    

3.6.2 ResultSetMetaData

• 可用于获取关于 ResultSet 对象中列的类型和属性信息的对象
• ResultSetMetaData meta = rs.getMetaData();
  • getColumnName(int column)：获取指定列的名称
  • getColumnLabel(int column)：获取指定列的别名
  • getColumnCount()：返回当前 ResultSet 对象中的列数。
  • getColumnTypeName(int column)：检索指定列的数据库特定的类型名称。
  • getColumnDisplaySize(int column)：指示指定列的最大标准宽度，以字符为单位。
  • isNullable(int column)：指示指定列中的值是否可以为 null。
  • isAutoIncrement(int column)：指示是否自动为指定列进行编号，这样这些列仍然是只读的。
    

问题1：得到结果集后, 如何知道该结果集中有哪些列 ？ 列名是什么？

需要使用一个描述 ResultSet 的对象， 即 ResultSetMetaData

问题2：关于ResultSetMetaData

1. 如何获取 ResultSetMetaData： 调用 ResultSet 的 getMetaData() 方法即可
2. 获取 ResultSet 中有多少列：调用 ResultSetMetaData 的 getColumnCount() 方法
3. 获取 ResultSet 每一列的列的别名是什么：调用 ResultSetMetaData 的getColumnLabel() 方法

3.7 BaseDao

Dao层的基类，把Dao层重复的代码逻辑放到BaseDado中，减少开发

public class BaseDao<T> {

    Class<T> clazz;

    //得到当前对象，父类声明的返回类型

    {
        //获取当前BaseDAO的子类继承的父类中的第一个泛型
        //    - this = UserDao extends BaseDao<User>
        //    - this = OrderDao extends BaseDao<Order>
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        if (genericSuperclass instance of ParameterizedType) {
    
        	ParameterizedType paramType = (ParameterizedType) genericSuperclass;

        	Type[] typeArguments = paramType.getActualTypeArguments();//获取了父类的泛型参数
        	clazz = (Class<T>) typeArguments[0];//泛型的第一个参数
    	}

    }

    /**
     * 查询List
     * @return
     */
    public List<T> findList(String sql, Object... params) {
        List<T> list = new ArrayList<>(10);

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);

            //处理查询条件
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }

            rs = ps.executeQuery();

            //查询结果的元数据（列名，类类型，类数量...)
            ResultSetMetaData metaData =  rs.getMetaData();
            int columnCount = metaData.getColumnCount(); //多少列

            while (rs.next()) {
                T instance = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    //列的值
                    Object value = rs.getObject(i + 1);

                    //列名（约定：列名等于属性名）
                    String name = metaData.getColumnLabel(i + 1);

                    Field field = clazz.getDeclaredField(name);
                    field.setAccessible(true);
                    field.set(instance, value);
                }
                list.add(instance);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);
        }
        return list;
    }

    /**
     * 查询List
     * @return
     */
    public T findOne(String sql, Object... params) {
        T instance  = null;

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);
            //处理查询条件
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            rs = ps.executeQuery();

            //查询结果的元数据（列名，类类型，类数量...)
            ResultSetMetaData metaData =  rs.getMetaData();
            int columnCount = metaData.getColumnCount(); //多少列

            while (rs.next()) {
                instance = clazz.newInstance();

                for (int i = 0; i < columnCount; i++) {
                    //列的值
                    Object value = rs.getObject(i + 1);

                    //列名（约定：列名等于属性名）
                    String name = metaData.getColumnLabel(i + 1);

                    Field field = clazz.getDeclaredField(name);
                    field.setAccessible(true);
                    field.set(instance, value);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);
        }
        return instance;
    }

    /**
     *
     * @param sql：语句
     * @param params：?参数
     * @return
     */
    public int update(String sql, Object... params) {
        int rows = 0;

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }

            rows = ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);
        }
        return rows;
    }
}

优化UserDao

public class UserDao extends BaseDao<User> {

    /**
     * 查询所有
     * @return
     */
    public List<User> find() {
        String sql = "SELECT `id`,  `name`,  `password`,  `address`,  `phone` from user";
        return super.findList(sql);
    }

    /**
     * 查询主键对应的记录
     * @param id
     * @return
     */
    public User get(Integer id) {
        String sql = "SELECT `id`,  `name`,  `password`,  `address`,  `phone` from user where id = ?";
        return super.findOne(sql, id);
    }

    /**
     * 添加
     * @param user
     * @return 返回语句影响的函数
     */
    public int insert(User user) {
        String sql = "insert into user(`name`, `password`, `address`, `phone`) values(?,?,?,?)";
        return super.update(sql, user.getName(), user.getPassword(), user.getAddress(), user.getPhone());
    }

    /**
     * 删除
     * @param id
     * @return 返回语句影响的函数
     */
    public int delete(Integer id) {
        String sql = "delete from user where id = ?";
        return super.update(sql, id);
    }

    /**
     * 添加
     * @param user
     * @return 返回语句影响的函数
     */
    public int update(User user) {
        String sql = "update user set `name`=?, `password`=?,  `address`=?,  `phone`=? WHERE `id` = ?";
        return super.update(sql, user.getName(), user.getPassword(), user.getAddress(), user.getPhone(), user.getId());

    }
}

4 MySQL BLOB类型

• MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。
• 插入BLOB类型的数据必须使用PreparedStatement，因为BLOB类型的数据无法使用字符串拼接写的。
• MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)
  
• 实际使用中根据需要存入的数据大小定义不同的BLOB类型。
• 需要注意的是：如果存储的文件过大，数据库的性能会下降。
• 如果在指定了相关的Blob类型以后，还报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数： max_allowed_packet=16M。同时注意：修改了my.ini文件之后，需要重新启动mysql服务。

public class BlobDemo {

    BaseDao dao = new BaseDao();

    /**
     * 添加文件
     *
     */
    @Test
    public void test1() {
        String sql = "insert into customers(name,email,birth, photo) values(?,?,?,?)";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        FileInputStream in = null;
        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,"mickey");
            ps.setString(2,"mickey@163.com");
            ps.setDate(3,new Date(new java.util.Date().getTime()));

            in = new FileInputStream("a.jpg");

            ps.setBlob(4, in);

            ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);
            try {
                in.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }

    }

    /**
     * 查询文件
     */
    @Test
    public void test2() {
        String sql = "select id, name, photo from customers where id = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        InputStream in = null;
        FileOutputStream out = null;

        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 20);

            rs = ps.executeQuery();

            if (rs.next()) {
                System.out.println(rs.getInt("id"));
                System.out.println(rs.getString(2));

                //读取blob
                Blob blob = rs.getBlob(3);
                in = blob.getBinaryStream();

                out = new FileOutputStream("a1.jpg");

                int length = -1;
                byte[] buffer = new byte[1024];
                while ((length = in.read(buffer)) != -1) {
                    out.write(buffer, 0, length);
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);

            try {
                in.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
            try {
                out.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }


    }


    /**
     * 修改BLOB
     *
     */
    @Test
    public void test3() {
        String sql = "update customers set photo = ? where id=?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        FileInputStream in = null;
        try {
            conn = JdbcUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(2, 20);

            in = new FileInputStream("b.jpg");

            ps.setBlob(1, in);

            ps.executeUpdate(); //执行修改,返回语句影响的行数

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(conn, ps, rs);
            try {
                in.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }

    }



}

5 批量插入

当需要成批插入或者更新记录时，可以采用Java的批量更新机制，这一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单独提交处理更有效率
JDBC的批量处理语句包括下面三个方法：

• addBatch(String)：添加需要批量处理的SQL语句或是参数；
• executeBatch()：执行批量处理语句；
• clearBatch():清空缓存的数据

通常我们会遇到两种批量执行SQL语句的情况：

• 多条SQL语句的批量处理；
• 一个SQL语句的批量传参；

public class BatchDemo {

    @Test
    public void test1() throws Exception {
        long start = System.currentTimeMillis();

        Connection conn = JdbcUtil.getConnection();
        conn.setAutoCommit(false);

        String sql = "insert into goods(name) values(?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        for(int i = 1; i <= 200000; i++){
            ps.setString(1, "name_" + i);
            ps.executeUpdate();
        }

        conn.commit();

        long end = System.currentTimeMillis();
        System.out.println("花费的时间为：" + (end - start));//182340


        JdbcUtil.closeResource(conn, ps, null);
    }

    @Test
    public void test2() throws Exception {
        long start = System.currentTimeMillis();

        Connection conn = JdbcUtil.getConnection();
        conn.setAutoCommit(false);

        String sql = "insert into goods(name) values(?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        for(int i = 1; i <= 200000; i++){
            ps.setString(1, "name_" + i);
//            ps.executeUpdate();
            ps.addBatch(); //添加语句到Batch,攒sql语句

            if (i % 100000 == 0) {
                ps.executeBatch(); //每500个语句，批量执行一次
                ps.clearBatch(); //清空批量
            }
        }
        conn.commit();

        long end = System.currentTimeMillis();
        System.out.println("花费的时间为：" + (end - start));//82340


        JdbcUtil.closeResource(conn, ps, null);
    }
}

6 数据库连接池

6.1 JDBC数据库连接池的必要性

• 在使用开发基于数据库的web程序时，传统的模式基本是按以下步骤：
  • 在主程序（如servlet、beans）中建立数据库连接
  • 进行sql操作
  • 断开数据库连接
• 这种模式开发，存在的问题:
  • 普通的JDBC数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection 加载到内存中，再验证用户名和密码(得花费0.05s～1s的时间)。需要数据库连接的时候，就向数据库要求一个，执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。数据库的连接资源并没有得到很好的重复利用。 若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严重的甚至会造成服务器的崩溃。
  • 对于每一次数据库连接，使用完后都得断开。 否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库。（回忆：何为Java的内存泄漏？）
  • 这种开发不能控制被创建的连接对象数， 系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内存泄漏，服务器崩溃。

6.2 数据库连接池技术

• 为解决传统开发中的数据库连接问题，可以采用数据库连接池技术。
• 数据库连接池的基本思想：就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。
• 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
• 数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。
  
• 工作原理：
  
• 数据库连接池技术的优点
  • 资源重用： 由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。
  • 更快的系统反应速度： 数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间
  • 新的资源分配手段： 对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源
  • 统一的连接管理，避免数据库连接泄漏： 在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露

6.3 多种开源的数据库连接池

• JDBC 的数据库连接池使用 javax.sql.DataSource 来表示，DataSource 只是一个接口，该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：
  • DBCP 是Apache提供的数据库连接池。tomcat 服务器自带dbcp数据库连接池。速度相对c3p0较快，但因自身存在BUG，Hibernate3已不再提供支持。
  • C3P0 是一个开源组织提供的一个数据库连接池，速度相对较慢，稳定性还可以。hibernate官方推荐使用
  • Druid 是阿里提供的数据库连接池，据说是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，但是速度不确定是否有BoneCP快
• DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池
• DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速度。
• 特别注意：
  • 数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。
  • 当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但conn.close()并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。

6.3.1 C3P0数据库连接池

导入依赖

        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.2.1</version>
        </dependency>

resources下的配置文件：【c3p0-config.xml】

<?xml version="1.0" encoding="UTF-8"?>
 <c3p0-config>
     <named-config name="helloc3p0">
         <!-- 获取连接的4个基本信息 -->
         <property name="user">root</property>
         <property name="password">abc123</property>
         <property name="jdbcUrl">jdbc:mysql:///test</property>
         <property name="driverClass">com.mysql.jdbc.Driver</property>
         
         <!-- 涉及到数据库连接池的管理的相关属性的设置 -->
         <!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->
         <property name="acquireIncrement">5</property>
         <!-- 初始化数据库连接池时连接的数量 -->
         <property name="initialPoolSize">5</property>
         <!-- 数据库连接池中的最小的数据库连接数 -->
         <property name="minPoolSize">5</property>
         <!-- 数据库连接池中的最大的数据库连接数 -->
         <property name="maxPoolSize">10</property>
         <!-- C3P0 数据库连接池可以维护的 Statement 的个数 -->
         <property name="maxStatements">20</property>
         <!-- 每个连接同时可以使用的 Statement 对象的个数 -->
         <property name="maxStatementsPerConnection">5</property>
 
     </named-config>
 </c3p0-config>

测试

public class C3p0Demo {

    @Test
    //使用C3P0数据库连接池的方式，获取数据库的连接：不推荐
    public void test1() throws Exception{
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass("com.mysql.jdbc.Driver");
        cpds.setJdbcUrl("jdbc:mysql://localhost:3306/test");
        cpds.setUser("root");
        cpds.setPassword("123456");

        //  cpds.setMaxPoolSize(100);

        Connection conn = cpds.getConnection();
        System.out.println(conn);
    }

    @Test
    public void test2() throws SQLException {
        DataSource cpds = new ComboPooledDataSource("hellc3p0");
        Connection conn = cpds.getConnection();
        System.out.println(conn);
    }
}

6.3.2 DBCP数据库连接池

• DBCP 是 Apache 软件基金组织下的开源连接池实现，该连接池依赖该组织下的另一个开源系统：Common-pool。如需使用该连接池实现，应在系统中增加如下两个 jar 文件：
  • Commons-dbcp.jar：连接池的实现
  • Commons-pool.jar：连接池实现的依赖库
• Tomcat 的连接池正是采用该连接池来实现的。该数据库连接池既可以与应用服务器整合使用，也可由应用程序独立使用。
• 数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。
• 当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但上面的代码并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。
• 配置属性说明

属性	默认值	说明
initialSize	0	连接池启动时创建的初始化连接数量
maxActive	8	连接池中可同时连接的最大的连接数
maxIdle	8	连接池中最大的空闲的连接数，超过的空闲连接将被释放，如果设置为负数表示不限制
minIdle	0	连接池中最小的空闲的连接数，低于这个数量会被创建新的连接。该参数越接近maxIdle，性能越好，因为连接的创建和销毁，都是需要消耗资源的；但是不能太大。
maxWait	无限制	最大等待时间，当没有可用连接时，连接池等待连接释放的最大时间，超过该时间限制会抛出异常，如果设置-1表示无限等待
poolPreparedStatements	false	开启池的Statement是否prepared
maxOpenPreparedStatements	无限制	开启池的prepared 后的同时最大连接数
minEvictableIdleTimeMillis		连接池中连接，在时间段内一直空闲， 被逐出连接池的时间
removeAbandonedTimeout	300	超过时间限制，回收没有用(废弃)的连接
removeAbandoned	false	超过removeAbandonedTimeout时间后，是否进 行没用连接（废弃）的回收

导入依赖

        <dependency>
            <groupId>org.apache.tomcat</groupId>
            <artifactId>dbcp</artifactId>
            <version>6.0.29</version>
        </dependency>

resources下的配置文件：【dbcp.properties】

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///test
username=root
password=123456

initialSize=10

测试

public class DBCPTest {

	//方式一：不推荐
	@Test
	public void testGetConnection() throws SQLException{
		//创建了DBCP的数据库连接池
		BasicDataSource source = new BasicDataSource();
		
		//设置基本信息
		source.setDriverClassName("com.mysql.jdbc.Driver");
		source.setUrl("jdbc:mysql:///test");
		source.setUsername("root");
		source.setPassword("123456");
		
		//还可以设置其他涉及数据库连接池管理的相关属性：
		source.setInitialSize(10);
		source.setMaxActive(10);
		//。。。
		
		Connection conn = source.getConnection();
		System.out.println(conn);
	}
	
	//方式二：推荐：使用配置文件
	@Test
	public void testGetConnection1() throws Exception{
		Properties pros = new Properties();
		
		//方式1：
		InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
		//方式2：
//		FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
		
		
		pros.load(is);
		DataSource source = BasicDataSourceFactory.createDataSource(pros);
		
		Connection conn = source.getConnection();
		System.out.println(conn);
	}
}

6.3.3 Druid（德鲁伊）数据库连接池

Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池，可以说是目前最好的连接池之一。

导入依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>

src下的配置文件：【druid.properties】

url=jdbc:mysql:///test
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=10

• 详细配置参数

配置	缺省	说明
name		配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。 如果没有配置，将会生成一个名字，格式是：”DataSource-” + System.identityHashCode(this)
url		连接数据库的url，不同数据库不一样。例如：mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username		连接数据库的用户名
password		连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用ConfigFilter。详细看这里：https://github.com/alibaba/druid/wiki/%E4%BD%BF%E7%94%A8ConfigFilter
driverClassName		根据url自动识别 这一项可配可不配，如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName(建议配置下)
initialSize	0	初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
maxActive	8	最大连接池数量
maxIdle	8	已经不再使用，配置了也没效果
minIdle		最小连接池数量
maxWait		获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
poolPreparedStatements	false	是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
maxOpenPreparedStatements	-1	要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
validationQuery		用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。
testOnBorrow	true	申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testOnReturn	false	归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testWhileIdle	false	建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
timeBetweenEvictionRunsMillis		有两个含义： 1)Destroy线程会检测连接的间隔时间2)testWhileIdle的判断依据，详细看testWhileIdle属性的说明
numTestsPerEvictionRun		不再使用，一个DruidDataSource只支持一个EvictionRun
minEvictableIdleTimeMillis
connectionInitSqls		物理连接初始化的时候执行的sql
exceptionSorter		根据dbType自动识别 当数据库抛出一些不可恢复的异常时，抛弃连接
filters		属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有： 监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
proxyFilters		类型是List，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系

测试

public class DruidTest {
	
	@Test
	public void test1() throws Exception{
		Properties pros = new Properties();
		
		InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
		
		pros.load(is);
		
		DataSource source = DruidDataSourceFactory.createDataSource(pros);
		Connection conn = source.getConnection();
		System.out.println(conn);
		
	}
}

7 数据库事务

7.1 数据库事务介绍

• 事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。
• 事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。
• 为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

7.2 JDBC事务处理

• 数据一旦提交，就不可回滚。
• 数据什么时候意味着提交？
  • 当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
  • 关闭数据库连接，数据就会自动的提交。如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即同一个事务的多个操作必须在同一个连接下。
• JDBC程序中为了让多个 SQL 语句作为一个事务执行：
  • 调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务
  • 在所有的 SQL 语句都成功执行后，调用 commit(); 方法提交事务
  • 在出现异常时，调用 rollback(); 方法回滚事务
• 若此时 Connection 没有被关闭，还可能被重复使用，则需要恢复其自动提交状态 setAutoCommit(true)。尤其是在使用数据库连接池技术时，执行close()方法前，建议恢复自动提交状态。

案例演示

用户AA向用户BB转账100

    @Test
    public void test1() {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = JdbcUtil.getConnection();
            // 2.开启事务
            conn.setAutoCommit(false);
            // 3.进行数据库操作
            String sql1 = "update user_table set balance = balance - 100 where user = ?";
            ps = conn.prepareStatement(sql1);
            ps.setString(1, "AA");
            ps.executeUpdate();

            // 模拟网络异常
//            int i = 10 / 0;

            String sql2 = "update user_table set balance = balance + 100 where user = ?";
            ps = conn.prepareStatement(sql2);
            ps.setString(1, "BB");
            ps.executeUpdate();
            // 4.若没有异常，则提交事务
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            // 5.若有异常，则回滚事务
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                ps.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            ;

        }
    }

其中，对数据库操作的方法

//使用事务以后的通用的增删改操作（version 2.0）
 public void update(Connection conn ,String sql, Object... args) {
     PreparedStatement ps = null;
     try {
         // 1.获取PreparedStatement的实例 (或：预编译sql语句)
         ps = conn.prepareStatement(sql);
         // 2.填充占位符
         for (int i = 0; i < args.length; i++) {
             ps.setObject(i + 1, args[i]);
         }
         // 3.执行sql语句
         ps.execute();
     } catch (Exception e) {
         e.printStackTrace();
     } finally {
         // 4.关闭资源
         JDBCUtils.closeResource(null, ps);
     }
 }

7.3 事务的ACID属性

• 原子性（Atomicity） 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性（Consistency） 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
• 隔离性（Isolation） 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持久性（Durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

7.3.1 数据库的并发问题

• 对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
  • 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的。
  • 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。
  • 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如果 T1 再次读取同一个表, 就会多出几行。
• 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。
• 一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

7.3.2 四种隔离级别

• 数据库提供的4种事务隔离级别
  
• Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED 。
• Mysql 支持 4 种事务隔离级别。Mysql 默认的事务隔离级别为: REPEATABLE READ。

7.3.3 在MySql中设置隔离级别

• 每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别。
• 查看当前的隔离级别

SELECT @@tx_isolation; ## 5.7
 select @@transaction_isolation; ## 8.0

• 设置当前 mySQL 连接的隔离级别

set session transaction isolation level read committed;

• 设置数据库系统的全局的隔离级别

set global transaction isolation level read committed;

7.3.4 隔离级别实验

读未提交实验

读已提交实验

设置读可提交

set session transaction isolation level read committed;

未提交时的效果：

提交后的效果：

可重复读

设置可重复读

 set session transaction isolation level repeatable read; #设置可重复读

事务1提交前：

事务1提交后：

幻读

8 MVCC

8.1 简介

MVCC（Multi-Version Concurrency Control）多版本并发控制，是⽤来在数据库中控制并发的⽅法，实现对数据库的并发访问⽤的，就是⼀种写时复制的思想的应⽤。
在MySQL中，MVCC只在读取已提交（Read Committed）和可重复读（Repeatable Read）两个事务级别下有效。其是通过Undo⽇志中的版本链和ReadView⼀致性视图来实现的。
MVCC就是在多个事务同时存在时，SELECT语句找寻到具体是版本链上的哪个版本，然后在找到的版本上返回其中所记录的数据的过程。
⾸先需要知道的是，在MySQL中，会默认为我们的表后⾯添加三个隐藏字段：

• DB_ROW_ID：⾏ID，MySQL的B+树索引特性要求每个表必须要有⼀个主键。如果没有设置的话，会⾃动寻找第⼀个不包含NULL的唯⼀索引列作为主键。如果还是找不到，就会在这个DB_ROW_ID上⾃动⽣成⼀个唯⼀值，以此来当作主键（该列和MVCC的关系不⼤）；
• DB_TRX_ID：事务ID，记录的是当前事务在做INSERT或UPDATE语句操作时的事务ID（DELETE语句被当做是UPDATE语句的特殊情况，后⾯会进⾏说明）；
• DB_ROLL_PTR：回滚指针，通过它可以将不同的版本串联起来，形成版本链。相当于链表的next指针。

8.2 ReadVIew

ReadView⼀致性视图主要是由两部分组成：所有未提交事务的ID数组和已经创建的最⼤事务ID组成（实际上ReadView还有其他的字段，但不影响这⾥对MVCC的讲解）。⽐如：[100,200],300。事务100和200是当前未提交的事务，⽽事务300是当前创建的最⼤事务（已经提交了）。
当执⾏SELECT语句的时候会创建ReadView，但是在读取已提交和可重复读两个事务级别下，⽣成ReadView的策略是不⼀样的：读取已提交级别是每执⾏⼀次SELECT语句就会重新⽣成⼀份ReadView，⽽可重复读级别是只会在第⼀次SELECT语句执⾏的时候会⽣成⼀份，后续的SELECT语句会沿⽤之前⽣成的ReadView（即使后⾯有更新语句的话，也会继续沿⽤）。

8.3 版本链

所有版本的数据都只会存⼀份，然后通过回滚指针连接起来，之后就是通过⼀定的规则找到具体是哪个版本上的数据就⾏了。假设现在有⼀张account表，其中有id和name两个字段，那么版本链的示意图如下：

⽽具体版本链的⽐对规则如下，⾸先从版本链中拿出最上⾯第⼀个版本的事务ID开始逐个往下进⾏⽐对：

（其中min_id指向ReadView中未提交事务数组中的最⼩事务ID，⽽max_id指向ReadView中的已经创建的最⼤事务ID）

• 如果落在绿⾊区间（DB_TRX_ID < min_id）：这个版本⽐min_id还⼩（事务ID是从⼩往⼤顺序⽣成的），说明这个版本在SELECT之前就已经提交了，所以这个数据是可⻅的。或者（这⾥是短路或，前⾯条件不满⾜才会判断后⾯这个条件）这个版本的事务本身就是当前SELECT语句所在事务的话，也是⼀样可⻅的；
• 如果落在红⾊区间（DB_TRX_ID > max_id）：表示这个版本是由将来启动的事务来⽣成的，当前还未开始，那么是不可⻅的；
• 如果落在⻩⾊区间（min_id <= DB_TRX_ID <= max_id）：这个时候就需要再判断两种情况：
  • 如果这个版本的事务ID在ReadView的未提交事务数组中，表示这个版本是由还未提交的事务⽣成的，那么就是不可⻅的；
  • 如果这个版本的事务ID不在ReadView的未提交事务数组中，表示这个版本是已经提交了的事务⽣成的，那么是可⻅的。

如果在上述的判断中发现当前版本是不可⻅的，那么就继续从版本链中通过回滚指针拿取下⼀个版本来进⾏上述的判断。

8.4 案例演示

下⾯通过⼀个示例来具体演示MVCC的执⾏过程（假设是在可重复读事务级别下），当前account表中已经有了⼀条初始数据（id=1,name=monkey）：

	Transaction 100	Transaction 200	Transaction 300	⽆事务ID	⽆事务ID
1	begin;	begin;	begin;	begin;	begin;
2	UPDATE test SET a=‘1’ WHERE id = 1;
3		UPDATE test SET a=‘2’ WHERE id = 2;
4			UPDATE account SET name = ‘monkey301’ WHERE id = 1;
5			commit;
6				SELECT name FROM account WHERE id = 1;
7	UPDATE account SET name = ‘monkey101’ WHERE id = 1;
8	UPDATE account SET name = ‘monkey102’ WHERE id = 1;
9				SELECT name FROM account WHERE id = 1;
10	commit;	UPDATE account SET name = ‘monkey201’ WHERE id = 1;
11		UPDATE account SET name = ‘monkey202’ WHERE id = 1;
12				SELECT name FROM account WHERE id = 1;	SELECT name FROM account WHERE id = 1;
13		commit;

从左往右分别是五个事务，从上到下是时刻点。其中在第2和3时刻点中事务100和事务200（这⾥两个事务之间相差100只是为了更加⽅便去看，正常来说下个事务的ID是以+1的⽅式来创建的）分别执⾏了⼀条UPDATE语句，这两条语句并⽆实际作⽤，只是为了⽣成事务ID的，所以在下⾯的MVCC执⾏过程中就不分析这两条语句所带来的影响了，我们只研究account表。⽽其中最后两个事务，我是注明没有事务ID的。
因为事务ID是执⾏⼀条更新操作（增删改）的语句后才会⽣成（这也是事务100和事务200要先执⾏⼀条更新语句的意义），并不是开启事务的时候就会⽣成。最后两个事务中可以看到就是执⾏了⼀些SELECT语句⽽已，所以它们并没有事务ID。

⾸先来看⼀下初始状态时的版本链和ReadView（ReadView此时还未⽣成）：

其中事务1在account表中创建了⼀条初始数据。

• 之后在第1时刻点，五个事务分别开启了事务（如上所说，这个时候还没有⽣成事务ID）。
• 在第2时刻点，第⼀个事务执⾏了⼀条UPDATE语句，⽣成了事务ID为100。
• 在第3时刻点，第⼆个事务执⾏了⼀条UPDATE语句，⽣成了事务ID为200。
• 在第4时刻点，第三个事务执⾏了⼀条UPDATE语句，将account表中id为1的name改为了monkey301。同时⽣成了事务ID为300。
• 在第5时刻点，事务300也就是上⾯的事务执⾏了commit操作。
• 在第6时刻点，第四个事务执⾏了⼀条SELECT语句，想要查询⼀下当前id为1的数据（如上所说，该事务没有⽣成事务ID）。此时的版本链和ReadView如下：
  

因为在第5时刻点，事务300已经commit了，所以ReadView的未提交事务数组中不包含它。此时根据上⾯所说的⽐对规则，拿版本链中的第⼀个版本的事务ID为300进⾏⽐对，⾸先当前这条SELECT语句没有在事务300中进⾏查询，然后发现是落在⻩⾊区间，⽽且事务300也没有在ReadView的未提交事务数组中，所以是可⻅的。即此时在第6时刻点，第四个事务所查找到的结果是monkey301。

• 在第7时刻点，事务100执⾏了⼀条UPDATE语句，将account表中id为1的name改为了monkey101。
• 在第8时刻点，事务100⼜执⾏了⼀条UPDATE语句，将account表中id为1的name改为了monkey102。
• 在第9时刻点，第四个事务执⾏了⼀条SELECT语句，想要查询⼀下当前id为1的数据。此时的版本链和ReadView如下：
  

注意，因为当前是在可重复读的事务级别下，所以此时的ReadView沿⽤了在第6时刻点⽣成的ReadView（如果是在读取已提交的事务级别下，此时就会重新⽣成⼀份ReadView了）。然后根据上⾯所说的⽐对规则，拿版本链中的第⼀个版本的事务ID为100进⾏⽐对，⾸先当前这条SELECT语句没有在事务100中进⾏查询，然后发现是落在⻩⾊区间，⽽且事务100是在ReadView的未提交事务数组中，所以是不可⻅的。此时通过回滚指针拿取下⼀个版本，发现事务ID仍然为100，经过分析后还是不可⻅的。此时⼜拿取下⼀个版本：事务ID为300进⾏⽐对，⾸先当前这条SELECT语句没有在事务300中进⾏查询，然后发现是落在⻩⾊区间，但是事务300没有在ReadView的未提交事务数组中，所以是可⻅的。即此时在第9时刻点，第四个事务所查找到的结果仍然是monkey301（这也就是可重复读的含义）。

• 在第10时刻点，事务100commit提交事务了。同时事务200执⾏了⼀条UPDATE语句，将account表中id为1的name改为了monkey201。
• 在第11时刻点，事务200⼜执⾏了⼀条UPDATE语句，将account表中id为1的name改为了monkey202。
• 在第12时刻点，第四个事务执⾏了⼀条SELECT语句，想要查询⼀下当前id为1的数据。此时的版本链和ReadView如下：
  

跟第9时刻点⼀样，在可重复读的事务级别下，ReadView沿⽤了在第6时刻点⽣成的ReadView。然后根据上⾯所说的⽐对规则，拿版本链中的第⼀个版本的事务ID为200进⾏⽐对，⾸先当前这条SELECT语句没有在事务200中进⾏查询，然后发现是落在⻩⾊区间，⽽且事务200是在ReadView的未提交事务数组中，所以是不可⻅的。此时通过回滚指针拿取下⼀个版本，发现事务ID仍然为200，经过分析后还是不可⻅的。此时⼜拿取下⼀个版本：事务ID为100进⾏⽐对，⾸先当前这条SELECT语句没有在事务100中进⾏查询，然后发现是落在⻩⾊区间内，同时在ReadView的未提交数组中，所以依然是不可⻅的。此时⼜拿取下⼀个版本，发现事务ID仍然为100，经过分析后还是不可⻅的。此时再拿取下⼀个版本：事务ID为300进⾏⽐对，⾸先当前这条SELECT语句没有在事务300中进⾏查询，然后发现是落在⻩⾊区间，但是事务300没有在ReadView的未提交事务数组中，所以是可⻅的。即此时在第12时刻点，第四个事务所查找到的结果仍然是monkey301。
同时在第12时刻点，第五个事务执⾏了⼀条SELECT语句，想要查询⼀下当前id为1的数据。此时的版本链和ReadView如下：

注意，此时第五个事务因为是该事务内的第⼀条SELECT语句，所以会重新⽣成在当前情况下的ReadView，即上图中所示的内容。可以看到，和第四个事务⽣成的ReadView并不⼀样，因为在之前的第10时刻点，事务100已经提交事务了。然后根据上⾯所说的⽐对规则，拿版本链中的第⼀个版本的事务ID为200进⾏⽐对，⾸先当前这条SELECT语句没有在事务200中进⾏查询，然后发现是落在⻩⾊区间，⽽且事务200是在ReadView的未提交事务数组中，所以是不可⻅的。此时通过回滚指针拿取下⼀个版本，发现事务ID仍然为200，经过分析后还是不可⻅的。此时⼜拿取下⼀个版本：事务ID为100进⾏⽐对，发现是在绿⾊区间，所以是可⻅的。即此时在第12时刻点，第五个事务所查找到的结果是monkey102（可以看到，即使是同⼀条SELECT语句，在不同的事务中，查询出来的结果也可能是不同的，究其原因就是因为ReadView的不同）。

• 在第13时刻点，事务200执⾏了commit操作，整段分析过程结束。

以上演示的就是MVCC的具体执⾏过程，在多个事务下，版本链和ReadView是如何配合进⾏查找的。上⾯还遗漏了⼀种情况没有进⾏说明，就是如果是DELETE语句的话，也会在版本链上将最新的数据插⼊⼀份，然后将事务ID赋值为当前进⾏删除操作的事务ID。但是同时会在该条记录的信息头（recordheader）⾥⾯的deleted_flag标记位置为true，以此来表示当前记录已经被删除。所以如果经过版本⽐对后发现找到的版本上的deleted_flag标记位为true的话，那么也不会返回，⽽是继续寻找下⼀个。

另外，如果当前事务执⾏rollback回滚的话，会把版本链中属于该事务的所有版本都删除掉。