JDBC(2) -- JDBC使用PreparedStatement完成CRUD

1.   PreparedStatement预编译的语句对象

预编译语句PreparedStatement 是java.sql中的一个接口，它是Statement的子接口。通过Statement对象执行SQL语句时，需要将SQL语句发送给DBMS，由 DBMS首先进行编译后再执行。预编译语句和Statement不同，在创建PreparedStatement 对象时就指定了SQL语句，该语句立即发送给DBMS进行编译。当该编译语句被执行时，DBMS直接运行编译后的SQL语句，而不需要像其他SQL语句那样首先将其编译,在执行。

2.PreparedStatement和Statement的区别

在用法上不一样：

①    Statement是普通的语句对象，PreparedStatement是预编译的语句对象；

Statement创建对象时不传入SQL，PreparedStatement创建对象是需要传入SQL；

Statement在执行时需传入SQL，PreparedStatement执行时不需传入SQL。

②    Statement是拼接字符串效果，PreparedStatement不用拼接字符串；

③    PreparedStatement执行效率高于Statement；

④    PreparedStatement不是拼接字符串可以防止SQL注入问题；

具体实现代码如下：

package cn.yif.dao.impl;

import cn.yif.domain.Student;
import cn.yif.dao.IStudentDao;
import cn.yif.domain.User;
import cn.yif.utils.DBCPUtil;
import cn.yif.utils.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Administrator
 * @create 2019-09-13-20:33
 */
public class StudentDaoImpl implements IStudentDao {
    @Override
    public void insert(Student student) {
        Connection conn = null;
        PreparedStatement preStatement = null;
        try {
            //①加载（注册）驱动，这部分硬编码可以写也可以不写，jdbc4.0版本自带了
            //Class.forName("com.mysql.jdbc.Driver");
            //②获取连接
            //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
            conn = JDBCUtil.getInstance().getConnection();
            //③预编译的语句对象：提前编译好SQL语句
            String insertSql = "insert into jdbcdemo02 (name, age) values (?, ?)";
            preStatement = conn.prepareStatement(insertSql);
            preStatement.setString(1, student.getName());
            preStatement.setInt(2, student.getAge());
            //④执行SQL语句：执行时无需代入SQL
            preStatement.execute();
        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            //⑤释放资源
            JDBCUtil.getInstance().close(null, preStatement, conn);
        }
    }

    @Override
    public void update(Student student) {
        Connection conn = null;
        PreparedStatement preStatement = null;
        try {
            //①加载（注册）驱动
            //Class.forName("com.mysql.jdbc.Driver");
            //②获取连接
            //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
            conn = JDBCUtil.getInstance().getConnection();
            //③预编译的语句对象：提前编译好SQL语句
            String updateSql = "update jdbcdemo02 set name = ?, age = ? where id = ?";
            preStatement = conn.prepareStatement(updateSql);
            preStatement.setString(1, student.getName());
            preStatement.setInt(2, student.getAge());
            preStatement.setInt(3, student.getId());
            //④执行SQL语句：执行时无需代入SQL
            preStatement.execute();
        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            //⑤释放资源
            JDBCUtil.getInstance().close(null, preStatement, conn);
        }
    }

    @Override
    public void delete(Integer id) {
        Connection conn = null;
        PreparedStatement preStatement = null;
        try {
            //①加载（注册）驱动
            //Class.forName("com.mysql.jdbc.Driver");
            //②获取连接
            //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
            conn = JDBCUtil.getInstance().getConnection();
            //③预编译的语句对象：提前编译好SQL语句
            String deleteSql = "delete from jdbcdemo02 where id = ?";
            preStatement = conn.prepareStatement(deleteSql);
            preStatement.setInt(1, id);
            //④执行SQL语句：执行时无需代入SQL
            preStatement.execute();
        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            //⑤释放资源
            JDBCUtil.getInstance().close(null, preStatement, conn);
        }
    }

    @Override
    public Student queryOne(Integer id) {
        Connection conn = null;
        PreparedStatement preStatement = null;
        ResultSet resultSet = null;
        Student student = null;
        try {
            //①加载（注册）驱动
            //Class.forName("com.mysql.jdbc.Driver");
            //②获取连接
            //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
            conn = JDBCUtil.getInstance().getConnection();
            //③预编译的语句对象：提前编译好SQL语句
            String queryOneSql = "select * from jdbcdemo02 where id = ?";
            preStatement = conn.prepareStatement(queryOneSql);
            preStatement.setInt(1, id);
            //④执行SQL语句：执行时无需代入SQL
            resultSet = preStatement.executeQuery();
            if(resultSet.next())
            {
                student = new Student();
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));
            }
        }catch (Exception ex){

        }finally {
            //⑤释放资源
            JDBCUtil.getInstance().close(resultSet, preStatement, conn);
        }
        return student;
    }

    @Override
    public List<Student> queryAll() {
        Connection conn = null;
        PreparedStatement preStatement = null;
        ResultSet resultSet = null;
        Student student = null;
        List<Student> list = new ArrayList<Student>();
        try {
            //①加载（注册）驱动
            //Class.forName("com.mysql.jdbc.Driver");
            //②获取连接
            //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
            conn = JDBCUtil.getInstance().getConnection();
            //③获取语句对象：用来执行SQL语句
            String queryAllSql = "select * from jdbcdemo02";
            preStatement = conn.prepareStatement(queryAllSql);
            //④执行SQL语句
            resultSet = preStatement.executeQuery(queryAllSql);
            while (resultSet.next())
            {
                student = new Student();
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));
                list.add(student);
            }
        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            //⑤释放资源
            JDBCUtil.getInstance().close(resultSet, preStatement, conn);
        }
        return list;
    }

3.登录方式实现

这里采用简单模拟的方式，比较Statement与PreparedStatement的不同，以及SQL注入问题带来的影响：

登录实现代码：

 @Override
    public User Login1_PreparedSt(String userName, String passWord) {
        Connection conn = null;
        PreparedStatement preStatement = null;
        ResultSet resultSet = null;
        User user = null;
        try {
            conn = JDBCUtil.getInstance().getConnection();
            String queryOneSql = "select * from jdbcdemo02_user where username = ? and password = ?";
            preStatement = conn.prepareStatement(queryOneSql);
            preStatement.setString(1, userName);
            preStatement.setString(2, passWord);
            resultSet = preStatement.executeQuery();
            if(resultSet.next())
            {
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setAge(resultSet.getInt("age"));
                user.setSex(resultSet.getInt("sex"));
            }
        }catch (Exception ex){

        }finally {
            //⑤释放资源
            JDBCUtil.getInstance().close(resultSet, preStatement, conn);
        }
        return user;
    }

    @Override
    public User Login2_PreparedSt(String userName) {
        Connection conn = null;
        PreparedStatement preStatement = null;
        ResultSet resultSet = null;
        User user = null;
        try {
            conn = JDBCUtil.getInstance().getConnection();
            String queryOneSql = "select * from jdbcdemo02_user where username = ?";
            preStatement = conn.prepareStatement(queryOneSql);
            preStatement.setString(1, userName);
            resultSet = preStatement.executeQuery();
            if(resultSet.next())
            {
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setAge(resultSet.getInt("age"));
                user.setSex(resultSet.getInt("sex"));
            }
        }catch (Exception ex){

        }finally {
            //⑤释放资源
            JDBCUtil.getInstance().close(resultSet, preStatement, conn);
        }
        return user;
    }

    @Override
    public User Login1_St(String userName, String passWord) {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        User user = null;
        try {
            conn = JDBCUtil.getInstance().getConnection();
            statement = conn.createStatement();
            String queryOneSql = "select * from jdbcdemo02_user where username = '"+userName+"' and password = '"+passWord+"'";
            resultSet = statement.executeQuery(queryOneSql);
            if(resultSet.next())
            {
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setAge(resultSet.getInt("age"));
                user.setSex(resultSet.getInt("sex"));
            }
        }catch (Exception ex){

        }finally {
            //⑤释放资源
            JDBCUtil.getInstance().close(resultSet, statement, conn);
        }
        return user;
    }

后台使用SQL注入测试代码：

@org.junit.Test
    public void Login1_PreparedSt() {
        User test1 = new User();
        //正常情况：未有SQL注入
        test1.setUsername("李华");
        test1.setPassword("123456");
        test1.setSex(2);
        //异常情况：有SQL注入
//        test1.setUsername(" 'or 1=1 or' ");
//        test1.setPassword("456");
//        test1.setSex(2);
        IStudentDao userDao = new StudentDaoImpl();
        User user = userDao.Login1_PreparedSt(test1.getUsername(), test1.getPassword());
        if(user != null){
            System.out.println(String.format("恭喜%s登录成功！", user.getUsername()));
        }
        else {
            System.out.println("用户名或密码错误，登录失败！");
        }
    }

    @org.junit.Test
    public void Login2_PreparedSt() {
        User test1 = new User();
        test1.setUsername("李华");
        test1.setPassword("123456");
        test1.setSex(2);
        IStudentDao userDao = new StudentDaoImpl();
        User user = userDao.Login2_PreparedSt(test1.getUsername());
        if(user != null){
            if(user.getPassword().equals(test1.getPassword())){
                System.out.println(String.format("恭喜%s登录成功！", user.getUsername()));
            }
            else {
                System.out.println("密码错误，登录失败！");
            }
        }
        else {
            System.out.println("用户名错误，登录失败！");
        }
    }

    @org.junit.Test
    public void Login1_St() {
        User test1 = new User();
        //正常情况：未有SQL注入
        //test1.setUsername("李华");
        //test1.setPassword("123456");
        //test1.setSex(2);
        //异常情况：有SQL注入
        test1.setUsername(" 'or 1=1 or' ");
        //这里就是一条拼接的SQL -- select * from jdbcdemo02_user where username = ''or 1=1 or'' and password = 12344
        test1.setPassword("145");
        test1.setSex(2);
        IStudentDao userDao = new StudentDaoImpl();
        User user = userDao.Login1_St(test1.getUsername(), test1.getPassword());
        if(user != null){
            System.out.println(String.format("恭喜%s登录成功！", user.getUsername()));
        }
        else {
            System.out.println("用户名或密码错误，登录失败！");
        }
    }

4.连接池

4.1.连接池介绍

连接池：简单地说，就是用来装连接对象的容器。

背景：在高并发访问的网页上，每次请求都会创建一个connection，因此会非常浪费资源（内存），当同时1000人访问的时候，那就会占用很多资源，因此浪费很多时间并且导致容器操作系统崩溃。

而连接池里面取connection则只需要从连接池里面拿到，不需要用户名和密码，用完之后，还回到连接池。

在Java中，在Java中,连接池使用javax.sql.DataSource接口来表示连接池. 这里的DataSource就是连接池。连接池就是DataSource。DataSource是接口，和JDBC一样，是Sun公司开发的一套接口，需要各大厂商去实现：导入各大厂商对应的jar包；

常用的DataSource的实现有下面两种方式:

  DBCP:  Spring推荐的（Spring框架已经集成DBCP）

  C3P0:  Hibernate推荐的(早期)（Hibernate框架已经集成C3P0）

DBCP连接池配置文件

dbcp.properties

#连接字符串

url=jdbc:mysql://localhost:3306/jdbcdemo

#用户名

username=root

#密码

password=admin

#驱动的类路径

driverClassName=com.mysql.jdbc.Driver

#连接池启动时的初始值

initialSize=1

#连接池的最大值

maxActive=50

#连接池的最大空闲数

maxIdle=20

具体代码实现如下：

 @Override
    public Student queryOneByDBCP(Integer id) {
        Connection conn = null;
        PreparedStatement preStatement = null;
        ResultSet resultSet = null;
        Student student = null;
        try {
            //①加载（注册）驱动
            //Class.forName("com.mysql.jdbc.Driver");
            //②获取连接
            //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
            conn = DBCPUtil.getInstance().getConnection();
            //③预编译的语句对象：提前编译好SQL语句
            String queryOneSql = "select * from jdbcdemo02 where id = ?";
            preStatement = conn.prepareStatement(queryOneSql);
            preStatement.setInt(1, id);
            //④执行SQL语句：执行时无需代入SQL
            resultSet = preStatement.executeQuery();
            if(resultSet.next())
            {
                student = new Student();
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));
            }
        }catch (Exception ex){

        }finally {
            //⑤释放资源
            DBCPUtil.getInstance().close(resultSet, preStatement, conn);
        }
        return student;
    }

DBCPUtil.java文件：

package cn.yif.utils;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class DBCPUtil {
    //使用静态static类构造单例模式
    private DBCPUtil(){ }
    //在静态代码块中创建instance的实例，这里使用static静态变量来保存
    private static DBCPUtil instance = null;
    //每次都要加载驱动，这里定义一个Properties，把资源文件里面的内容读到Properties里面
    private static Properties properties = null;
    //定义一个连接池对象
    //private static BasicDataSource ds = null;
    private static DataSource ds = null;
    //JDBCUtil类加载的时候，就加载注册驱动
    static {
        properties = new Properties();
        try {
            //当前线程类加载器加载获取properties文件
            properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties"));
            //方式一：创建一个BasicDataSource对象来读取配置文件
//            ds = new BasicDataSource();
//            ds.setDriverClassName(properties.getProperty("driverClassName"));
//            ds.setUrl(properties.getProperty("url"));
//            ds.setUsername(properties.getProperty("username"));
//            ds.setPassword(properties.getProperty("password"));
            //方式二：使用工厂DataSourceFactory来加载配置文件
            try {
                ds = BasicDataSourceFactory.createDataSource(properties);
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        instance = new DBCPUtil();
    }

    public static DBCPUtil getInstance(){
        return instance;
    }

    //抽取获取连接Connection的方法
    public Connection getConnection() throws SQLException {
        //这里不再使用DriverManager，而应该使用BasicDataSource，从连接池中拿到这个连接
        return ds.getConnection();
    }

    //抽取JDBC关闭资源的close方法
    public void close(ResultSet resultSet, Statement statement, Connection conn){
        try {
            if(resultSet != null){
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(statement != null){
                statement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}