JavaWeb01-JDBC、Druid连接池

获取执行SQL的对象
管理事务
- JDBC事务管理:Connection接口中定义了3个对应的方法
- 开启事务: setAutoCommit(boolean autoCommit): true为自动提交事务; false为手动提交事务，即为开启事务
- 提交事务commit()
- 回滚事务: rollback()

主要方法：

执行SQL对象（普通）

方法名
Statement createStatement（sql）;

执行SQL对象（预编译SQL）：防止SQL注入

方法名
PreparedStatement prepareStatement（sql）;

执行存储过程的对象

方法名
CallableStatement prepareCall (sql)

（3）Statement

主要作用

执行SQL语句

方法名	说明
int executeUpdate（sql）；	执行DML、DDL语句返回值为DML语句影响的行数 DDL语句执行后，执行成功也可能返回0
ResultSet executeQuery（sql）；	执行DQL语句返回值为ResultSet结果集对象

（4）ResultSet（结果集对象）

主要作用：

获取查询结果

主要方法

方法名	说明
boolean next（）	将光标从当前位置向前移动一行判断当前行是否为有效行（有数据）
XXX【数据类型】 getXxx（参数）	获取数据参数： int：列的编号，从1开始 String：列的名称更多参数看jdk帮助文档

使用：

while（rs.next（））{
    //获取数据
    rs.getXxx（参数）;
}

（5）PreparedStatement

主要作用：

预编译SQL并执行
防止SQL注入

SQL注入：

SQL注入是通过操作输入来修改事先定义好的SQL语句，用以达到执行代码对服务器进行攻击的方法。

好处：

预编译SQL，性能更高
防止SQL注入：将敏感字符进行转义

注意：

默认只是防止SQL注入，并没有预编译SQL提高性能
PreparedStatement的预编译功能默认是关闭的，需要在url后架上useServerPrepStmts=true，才会开启

6.使用

（1）快速上手

import java.sql.*;

public class Demo01 {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false";
        String username = "root";
        String password = "root";
        //1.注册驱动，mysql5版本及之后的版本该步骤可以省略
        registrationDriver();
        //2.获取数据库连接
        Connection connection = getConnection(url, username, password);
        //3.定义SQL语句
        String sql = "sqls";
        //4.获取执行SQL的对象
        Statement statement = connection.prepareStatement(sql);
        /*开启事务*/
        connection.setAutoCommit(false);

        try {
            //5.执行sql,根据方法会返回不同类型的值，详细可查看JDK帮助文档了解
            boolean execute = statement.execute(sql);
            //验证SQL语句是否执行成功
            System.out.println(execute);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            /*出现异常就回滚事务*/
            connection.rollback();
        }

        /*提交事务*/
        connection.commit();

        //6.释放资源
        statement.close();
        connection.close();

    }

    /**
     * 获取数据库连接
     * @param url:数据库连接，
     *           格式:jdbc:mysql://ip地址(域名):端口号/数据库名称？参数键值对1&参数键值对2...
     *           如果连接的是本机服务器，且端口号为默认，可简写为：jdbc:mysql///数据库名称？参数键值对
     *           由于没有禁用安全连接方式，会输出警告提示，将参数配置设置为 useSSL = false 即可
     * @param username:数据库用户名
     * @param password:数据库密码
     * @return 连接对象
     * @throws SQLException: SQLException异常
     */
    private static Connection getConnection(String url, String username, String password) throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    private static void registrationDriver() throws ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
    }
}

（2）ResultSet使用

查询user表中数据，并根据其数据创建User对象，再将所有的User对象存储到arraysList集合中

实体类User：

public class User {
    private Long id;
    private String userName;
    private String password;
    private String gmtCreate;
    private String gmtModified;

    public User() {
    }

    public User(Long id, String userName, String password, String gmtCreate, String gmtModified) {
        this.id = id;
        this.userName = userName;
        this.password = password;
        this.gmtCreate = gmtCreate;
        this.gmtModified = gmtModified;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long 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 getGmtCreate() {
        return gmtCreate;
    }

    public void setGmtCreate(String gmtCreate) {
        this.gmtCreate = gmtCreate;
    }

    public String getGmtModified() {
        return gmtModified;
    }

    public void setGmtModified(String gmtModified) {
        this.gmtModified = gmtModified;
    }

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

测试

public class TestDemo {
    String url = "jdbc:mysql:///test?useSSL=false";
    String userName = "root";
    String password = "root";
    @Test
    public void Demo01 () throws Exception {
        Connection connection = DriverManager.getConnection(url, userName, password);

        String sql = "select * from users";

        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);

        ArrayList<User> arrayList = new ArrayList<>();
        while (resultSet.next()){

            User user = new User(resultSet.getLong("id"),
                                 resultSet.getString("user_name"),
                                 resultSet.getString("password"),
                                 resultSet.getString("gmt_create"),
                                 resultSet.getString("gmt_modified"));
            arrayList.add(user);
        }
        
        resultSet.close();
        statement.close();
        connection.close();

        arrayList.forEach(user -> System.out.println(user));
    }
}

（3）SQL注入模拟及解决

模拟登录：sql注入

@Test
    public void sout() throws Exception {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = scanner.nextLine();
        System.out.println("请输入密码");
        String password = "' or 'a' = 'a";

        Connection connection = DriverManager.getConnection(TestDemo.this.url,
                TestDemo.this.userName,
                TestDemo.this.password);
        Statement statement = connection.createStatement();

        String sql = "select * from users where user_name = '"+username+"' and password = '"+password+"'";

        ResultSet resultSet = statement.executeQuery(sql);

        if (resultSet.next()){
            System.out.println("登录成功");
        }else {
            System.out.println("登录失败");
        }

        resultSet.close();
        statement.close();
        connection.close();
    }
}

SQL注入本质：由于用户输入导致SQL语句发生变化，如where username = ？ and ... +（or 1=1）【后面追加一个恒成立等式，就会导致where条件恒成立】

解决方式1：更多的逻辑判断

@Test
public void demo02() throws Exception {
    Scanner scanner = new Scanner(System.in);
    System.out.println("请输入用户名");
    String username = scanner.nextLine();
    System.out.println("请输入密码");
    String password = scanner.nextLine();

    Connection connection = DriverManager.getConnection(TestDemo.this.url,
                                                        TestDemo.this.userName,
                                                        TestDemo.this.password);
    Statement statement = connection.createStatement();

    String sql = "select * from users where user_name = '"+username+"' and password = '"+password+"'";

    ResultSet resultSet = statement.executeQuery(sql);

    while (true){
        boolean next = resultSet.next();
        if (next){
            if (username.equals(resultSet.getString("user_name"))){
                if (password.equals(resultSet.getString("password"))){
                    System.out.println("登录成功");
                }else {
                    System.out.println("密码错误");
                }
                break;
            }
        }else {
            System.out.println("没有此用户");
            break;
        }

    }

    resultSet.close();
    statement.close();
    connection.close();
}

解决方式2：预编译SQL

（4）perparedStatement使用

获取PerparedStatement对象
- 使用Connection的prepareStatement（sql）
- SQL语句中的参数值，使用?占位符替代
设置参数
- setXxx【数据类型】（int ？的位置编号，参数值）
- ？位置从1开始

执行SQL

主要方法

方法名	返回值类型	说明
execute（）	boolean	可执行所有sql语句，返回值为是否执行成功
executeUpdate（）	int	执行数据操纵语言DML或者没有返回值类型的SQL，如DDL
executeQuery（）	ResultSet	执行DQL，返回ResultSet对象

@Test
    public void sout() throws Exception {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = scanner.nextLine();
        System.out.println("请输入密码");
        String password = scanner.nextLine();

        Connection connection = DriverManager.getConnection(TestDemo.this.url,
                TestDemo.this.userName,
                TestDemo.this.password);


        String sql = "select * from users where user_name=? and password=?";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //设置？的值
        preparedStatement.setString(1,username);
        preparedStatement.setString(2,password);

        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.next()){
            System.out.println("登录成功");
        }else {
            System.out.println("登录失败");
        }

        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

7.关于测试类无法使用Scanner的问题解决

点击Help
选择Edit Custom VM Options...

-Deditable.java.test.console=true

之后重启idea即可

二、数据库连接池

1.概述

数据库连接池是个容器，负责分配、管理数据库连接(Connection)
它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个;
释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏

好处:

资源重用
提升系统响应速度
避免数据库连接遗漏

2.数据库连接池的实现

（1）标准接口：DataSource

官方(SUN)提供的数据库连接池标准接口，由第三方组织实现此接口。

功能：

获取连接：getConnection（）

（1）常见的数据库连接池

DBCP
C3P0
Druid（德鲁伊）
- Druid连接池是阿里巴巴开源的数据库连接池项目
- 功能强大，性能优秀，是Java语言最好的数据库连接池之一

3.Druid数据库连接池的使用

导入jar包：druid
定义配置文件
加载配置文件
获取数据库连接池对象
获取连接

配置文件：

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///test?useSSL=false&useServerPrepStmts=true
username=root
password=root
#初始化连接数量
initialSize=5
#最大连接数量
maxActive=10
#最大等待时间（ms）
maxWait=3000

java代码：

@Test
public void connectionPool() throws Exception {
    //加载配置文件
    Properties properties = new Properties();
    properties.load(new FileInputStream("src\\druid.properties"));

    //创建连接池对象
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
    //获取数据库连接
    final Connection connection = dataSource.getConnection();

    System.out.println(connection);

}

三、综合练习

主要目标：

建表：
添加数据：
表中数据的增、删、改、查
表中数据的存储
使用数据库连接池

1.创建表：

这玩意后期不会纯手敲的，除非业务需要

-- auto-generated definition
create table product
(
    id           bigint auto_increment comment '商品ID'
        primary key,
    name         varchar(20)                        not null comment '商品名称',
    price        decimal(10, 2)                     not null comment '商品价格',
    category     int      default 1                 not null comment '商品分类',
    gmt_modified datetime default CURRENT_TIMESTAMP not null comment '最后修改日期',
    gmt_create   datetime default CURRENT_TIMESTAMP not null comment '创建日期',
    constraint product_name_uindex
        unique (name)
)
    comment '商品表';

2.添加数据：表中数据增

id设置主键和自增约束可不写

public class Practice {
    public static void main(String[] args) throws Exception {

        //加载配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("JDBC\\src\\practice\\druidDemo.properties"));

        //获取连接池连接对象
        final Connection connection = DruidDataSourceFactory.createDataSource(properties).getConnection();

        String sql = "insert into product (name,price,category)" +
                     "values(?,?,?)";
        Random random = new Random();


        final PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 1; i <= 40; i++) {
            int integerPart = random.nextInt(10001);
            int decimalPart = random.nextInt(100);
            preparedStatement.setString(1,"商品"+i);
            if (decimalPart<10){
                preparedStatement.setString(2,integerPart+".0"+decimalPart);
            }else{
                preparedStatement.setString(2,integerPart+"."+decimalPart);
            }
            preparedStatement.setInt(3,1);
            try {
                preparedStatement.execute();
                System.out.println("第"+i+"条数据添加成功");
            } catch (SQLException e) {
                System.out.println("第"+i+"条数据添加失败");
                e.printStackTrace();
            }

        }

        preparedStatement.close();
        connection.close();

    }
}

3.表中数据的删、改、查

实体类：后面会有优化，也不会手敲的~

public class Product {
    private Long id;
    private String name;
    private BigDecimal bigDecimal;
    private Integer category;
    private Timestamp gmtCreate;
    private Timestamp gmtModified;

    public Product() {
    }

    public Product(Long id, String name, BigDecimal bigDecimal, Integer category, Timestamp gmtCreate, Timestamp gmtModified) {
        this.id = id;
        this.name = name;
        this.bigDecimal = bigDecimal;
        this.category = category;
        this.gmtCreate = gmtCreate;
        this.gmtModified = gmtModified;
    }

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public BigDecimal getBigDecimal() {
        return bigDecimal;
    }

    public void setBigDecimal(BigDecimal bigDecimal) {
        this.bigDecimal = bigDecimal;
    }

    public Integer getCategory() {
        return category;
    }

    public void setCategory(Integer category) {
        this.category = category;
    }

    public Timestamp getGmtCreate() {
        return gmtCreate;
    }

    public void setGmtCreate(Timestamp gmtCreate) {
        this.gmtCreate = gmtCreate;
    }

    public Timestamp getGmtModified() {
        return gmtModified;
    }

    public void setGmtModified(Timestamp gmtModified) {
        this.gmtModified = gmtModified;
    }

    @Override
    public String toString() {
        return "Product{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", bigDecimal=" + bigDecimal +
                ", category=" + category +
                ", gmtCreate=" + gmtCreate +
                ", gmtModified=" + gmtModified +
                '}';
    }
}

（1）查（需要啥写啥）+存

        //查询所有
        String sql = "select * from product";
        final PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        ArrayList<Product> productArrayList = new ArrayList<>();
        while (resultSet.next()){
            Product product = new Product(resultSet.getLong(1),
                                          resultSet.getString(2),
                                          resultSet.getBigDecimal(3),
                    resultSet.getInt(4),
                    resultSet.getTimestamp(5),
                    resultSet.getTimestamp(6));
            productArrayList.add(product);
        }

        productArrayList.forEach(p -> System.out.println(p));

        resultSet.close();
        preparedStatement.close();
        connection.close();

        //根据条件查
        String sql = "select * from product where id = ?";
        final PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setLong(1,10126);
        ResultSet resultSet = preparedStatement.executeQuery();
        ArrayList<Product> productArrayList = new ArrayList<>();
        while (resultSet.next()){
            Product product = new Product(resultSet.getLong(1),
                                          resultSet.getString(2),
                                          resultSet.getBigDecimal(3),
                                          resultSet.getInt(4),
                                          resultSet.getTimestamp(5),
                                          resultSet.getTimestamp(6));
            productArrayList.add(product);
        }

        productArrayList.forEach(p -> System.out.println(p));

        resultSet.close();
        preparedStatement.close();
        connection.close();

（2）改

public class Practice {
    public static void main(String[] args) throws Exception {

        //加载配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\practice\\druidDemo.properties"));

        //获取连接池连接对象
        final Connection connection = DruidDataSourceFactory.createDataSource(properties).getConnection();
        //输入sql语句
        String sql = sqls();




        final PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //设置参数
        parameterSetting(preparedStatement);

       //执行sql
        //execute()，可执行所有SQL语句
        //executeUpdate()，执行DML（返回受影响的行数）或者无返回值的SQL语句，如DDL
        //executeQuery()，DQL，返回ResultSet对象
        preparedStatement.executeUpdate();

        //查询专用
        //query(preparedStatement);

        preparedStatement.close();
        connection.close();

    }

    private static void parameterSetting(PreparedStatement preparedStatement) throws SQLException {
        preparedStatement.setInt(1,3);
        preparedStatement.setLong(2,10126);
    }

    private static void query(PreparedStatement preparedStatement) throws SQLException {

        ResultSet resultSet = preparedStatement.executeQuery();

        ArrayList<Product> productArrayList = new ArrayList<>();
        while (resultSet.next()){
            Product product = new Product(resultSet.getLong(1),
                    resultSet.getString(2),
                    resultSet.getBigDecimal(3),
                    resultSet.getInt(4),
                    resultSet.getTimestamp(5),
                    resultSet.getTimestamp(6));
            productArrayList.add(product);
        }

        productArrayList.forEach(p -> System.out.println(p));

        resultSet.close();
    }

    private static String sqls() {
        //查询所有
//        String sql = "select * from product";
        //根据条件查
//        String sql = "select * from product where id = ?";
        //根据条件改
        return "update product set category = ? where id = ?";
    }
}

（3）删除

改SQL，preparedStatement.set一下就行了

String sql = "delete from product where id = ?";