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注入问题；
具体实现代码如下：
1 package cn.yif.dao.impl; 2 3 import cn.yif.domain.Student; 4 import cn.yif.dao.IStudentDao; 5 import cn.yif.domain.User; 6 import cn.yif.utils.DBCPUtil; 7 import cn.yif.utils.JDBCUtil; 8 9 import java.sql.*; 10 import java.util.ArrayList; 11 import java.util.List; 12 13 /** 14 * @author Administrator 15 * @create 2019-09-13-20:33 16 */ 17 public class StudentDaoImpl implements IStudentDao { 18 @Override 19 public void insert(Student student) { 20 Connection conn = null; 21 PreparedStatement preStatement = null; 22 try { 23 //①加载（注册）驱动，这部分硬编码可以写也可以不写，jdbc4.0版本自带了 24 //Class.forName("com.mysql.jdbc.Driver"); 25 //②获取连接 26 //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin"); 27 conn = JDBCUtil.getInstance().getConnection(); 28 //③预编译的语句对象：提前编译好SQL语句 29 String insertSql = "insert into jdbcdemo02 (name, age) values (?, ?)"; 30 preStatement = conn.prepareStatement(insertSql); 31 preStatement.setString(1, student.getName()); 32 preStatement.setInt(2, student.getAge()); 33 //④执行SQL语句：执行时无需代入SQL 34 preStatement.execute(); 35 }catch (Exception ex){ 36 ex.printStackTrace(); 37 }finally { 38 //⑤释放资源 39 JDBCUtil.getInstance().close(null, preStatement, conn); 40 } 41 } 42 43 @Override 44 public void update(Student student) { 45 Connection conn = null; 46 PreparedStatement preStatement = null; 47 try { 48 //①加载（注册）驱动 49 //Class.forName("com.mysql.jdbc.Driver"); 50 //②获取连接 51 //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin"); 52 conn = JDBCUtil.getInstance().getConnection(); 53 //③预编译的语句对象：提前编译好SQL语句 54 String updateSql = "update jdbcdemo02 set name = ?, age = ? where id = ?"; 55 preStatement = conn.prepareStatement(updateSql); 56 preStatement.setString(1, student.getName()); 57 preStatement.setInt(2, student.getAge()); 58 preStatement.setInt(3, student.getId()); 59 //④执行SQL语句：执行时无需代入SQL 60 preStatement.execute(); 61 }catch (Exception ex){ 62 ex.printStackTrace(); 63 }finally { 64 //⑤释放资源 65 JDBCUtil.getInstance().close(null, preStatement, conn); 66 } 67 } 68 69 @Override 70 public void delete(Integer id) { 71 Connection conn = null; 72 PreparedStatement preStatement = null; 73 try { 74 //①加载（注册）驱动 75 //Class.forName("com.mysql.jdbc.Driver"); 76 //②获取连接 77 //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin"); 78 conn = JDBCUtil.getInstance().getConnection(); 79 //③预编译的语句对象：提前编译好SQL语句 80 String deleteSql = "delete from jdbcdemo02 where id = ?"; 81 preStatement = conn.prepareStatement(deleteSql); 82 preStatement.setInt(1, id); 83 //④执行SQL语句：执行时无需代入SQL 84 preStatement.execute(); 85 }catch (Exception ex){ 86 ex.printStackTrace(); 87 }finally { 88 //⑤释放资源 89 JDBCUtil.getInstance().close(null, preStatement, conn); 90 } 91 } 92 93 @Override 94 public Student queryOne(Integer id) { 95 Connection conn = null; 96 PreparedStatement preStatement = null; 97 ResultSet resultSet = null; 98 Student student = null; 99 try { 100 //①加载（注册）驱动 101 //Class.forName("com.mysql.jdbc.Driver"); 102 //②获取连接 103 //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin"); 104 conn = JDBCUtil.getInstance().getConnection(); 105 //③预编译的语句对象：提前编译好SQL语句 106 String queryOneSql = "select * from jdbcdemo02 where id = ?"; 107 preStatement = conn.prepareStatement(queryOneSql); 108 preStatement.setInt(1, id); 109 //④执行SQL语句：执行时无需代入SQL 110 resultSet = preStatement.executeQuery(); 111 if(resultSet.next()) 112 { 113 student = new Student(); 114 student.setId(resultSet.getInt("id")); 115 student.setName(resultSet.getString("name")); 116 student.setAge(resultSet.getInt("age")); 117 } 118 }catch (Exception ex){ 119 120 }finally { 121 //⑤释放资源 122 JDBCUtil.getInstance().close(resultSet, preStatement, conn); 123 } 124 return student; 125 } 126 127 @Override 128 public List<Student> queryAll() { 129 Connection conn = null; 130 PreparedStatement preStatement = null; 131 ResultSet resultSet = null; 132 Student student = null; 133 List<Student> list = new ArrayList<Student>(); 134 try { 135 //①加载（注册）驱动 136 //Class.forName("com.mysql.jdbc.Driver"); 137 //②获取连接 138 //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin"); 139 conn = JDBCUtil.getInstance().getConnection(); 140 //③获取语句对象：用来执行SQL语句 141 String queryAllSql = "select * from jdbcdemo02"; 142 preStatement = conn.prepareStatement(queryAllSql); 143 //④执行SQL语句 144 resultSet = preStatement.executeQuery(queryAllSql); 145 while (resultSet.next()) 146 { 147 student = new Student(); 148 student.setId(resultSet.getInt("id")); 149 student.setName(resultSet.getString("name")); 150 student.setAge(resultSet.getInt("age")); 151 list.add(student); 152 } 153 }catch (Exception ex){ 154 ex.printStackTrace(); 155 }finally { 156 //⑤释放资源 157 JDBCUtil.getInstance().close(resultSet, preStatement, conn); 158 } 159 return list; 160 }
3.登录方式实现
这里采用简单模拟的方式，比较Statement与PreparedStatement的不同，以及SQL注入问题带来的影响：
登录实现代码：
1 @Override 2 public User Login1_PreparedSt(String userName, String passWord) { 3 Connection conn = null; 4 PreparedStatement preStatement = null; 5 ResultSet resultSet = null; 6 User user = null; 7 try { 8 conn = JDBCUtil.getInstance().getConnection(); 9 String queryOneSql = "select * from jdbcdemo02_user where username = ? and password = ?"; 10 preStatement = conn.prepareStatement(queryOneSql); 11 preStatement.setString(1, userName); 12 preStatement.setString(2, passWord); 13 resultSet = preStatement.executeQuery(); 14 if(resultSet.next()) 15 { 16 user = new User(); 17 user.setId(resultSet.getInt("id")); 18 user.setUsername(resultSet.getString("username")); 19 user.setPassword(resultSet.getString("password")); 20 user.setAge(resultSet.getInt("age")); 21 user.setSex(resultSet.getInt("sex")); 22 } 23 }catch (Exception ex){ 24 25 }finally { 26 //⑤释放资源 27 JDBCUtil.getInstance().close(resultSet, preStatement, conn); 28 } 29 return user; 30 } 31 32 @Override 33 public User Login2_PreparedSt(String userName) { 34 Connection conn = null; 35 PreparedStatement preStatement = null; 36 ResultSet resultSet = null; 37 User user = null; 38 try { 39 conn = JDBCUtil.getInstance().getConnection(); 40 String queryOneSql = "select * from jdbcdemo02_user where username = ?"; 41 preStatement = conn.prepareStatement(queryOneSql); 42 preStatement.setString(1, userName); 43 resultSet = preStatement.executeQuery(); 44 if(resultSet.next()) 45 { 46 user = new User(); 47 user.setId(resultSet.getInt("id")); 48 user.setUsername(resultSet.getString("username")); 49 user.setPassword(resultSet.getString("password")); 50 user.setAge(resultSet.getInt("age")); 51 user.setSex(resultSet.getInt("sex")); 52 } 53 }catch (Exception ex){ 54 55 }finally { 56 //⑤释放资源 57 JDBCUtil.getInstance().close(resultSet, preStatement, conn); 58 } 59 return user; 60 } 61 62 @Override 63 public User Login1_St(String userName, String passWord) { 64 Connection conn = null; 65 Statement statement = null; 66 ResultSet resultSet = null; 67 User user = null; 68 try { 69 conn = JDBCUtil.getInstance().getConnection(); 70 statement = conn.createStatement(); 71 String queryOneSql = "select * from jdbcdemo02_user where username = ‘" userName "‘ and password = ‘" passWord "‘"; 72 resultSet = statement.executeQuery(queryOneSql); 73 if(resultSet.next()) 74 { 75 user = new User(); 76 user.setId(resultSet.getInt("id")); 77 user.setUsername(resultSet.getString("username")); 78 user.setPassword(resultSet.getString("password")); 79 user.setAge(resultSet.getInt("age")); 80 user.setSex(resultSet.getInt("sex")); 81 } 82 }catch (Exception ex){ 83 84 }finally { 85 //⑤释放资源 86 JDBCUtil.getInstance().close(resultSet, statement, conn); 87 } 88 return user; 89 }
后台使用SQL注入测试代码：
1 @org.junit.Test 2 public void Login1_PreparedSt() { 3 User test1 = new User(); 4 //正常情况：未有SQL注入 5 test1.setUsername("李华"); 6 test1.setPassword("123456"); 7 test1.setSex(2); 8 //异常情况：有SQL注入 9 // test1.setUsername(" ‘or 1=1 or‘ "); 10 // test1.setPassword("456"); 11 // test1.setSex(2); 12 IStudentDao userDao = new StudentDaoImpl(); 13 User user = userDao.Login1_PreparedSt(test1.getUsername(), test1.getPassword()); 14 if(user != null){ 15 System.out.println(String.format("恭喜%s登录成功！", user.getUsername())); 16 } 17 else { 18 System.out.println("用户名或密码错误，登录失败！"); 19 } 20 } 21 22 @org.junit.Test 23 public void Login2_PreparedSt() { 24 User test1 = new User(); 25 test1.setUsername("李华"); 26 test1.setPassword("123456"); 27 test1.setSex(2); 28 IStudentDao userDao = new StudentDaoImpl(); 29 User user = userDao.Login2_PreparedSt(test1.getUsername()); 30 if(user != null){ 31 if(user.getPassword().equals(test1.getPassword())){ 32 System.out.println(String.format("恭喜%s登录成功！", user.getUsername())); 33 } 34 else { 35 System.out.println("密码错误，登录失败！"); 36 } 37 } 38 else { 39 System.out.println("用户名错误，登录失败！"); 40 } 41 } 42 43 @org.junit.Test 44 public void Login1_St() { 45 User test1 = new User(); 46 //正常情况：未有SQL注入 47 //test1.setUsername("李华"); 48 //test1.setPassword("123456"); 49 //test1.setSex(2); 50 //异常情况：有SQL注入 51 test1.setUsername(" ‘or 1=1 or‘ "); 52 //这里就是一条拼接的SQL -- select * from jdbcdemo02_user where username = ‘‘or 1=1 or‘‘ and password = 12344 53 test1.setPassword("145"); 54 test1.setSex(2); 55 IStudentDao userDao = new StudentDaoImpl(); 56 User user = userDao.Login1_St(test1.getUsername(), test1.getPassword()); 57 if(user != null){ 58 System.out.println(String.format("恭喜%s登录成功！", user.getUsername())); 59 } 60 else { 61 System.out.println("用户名或密码错误，登录失败！"); 62 } 63 }
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
具体代码实现如下：
1 @Override 2 public Student queryOneByDBCP(Integer id) { 3 Connection conn = null; 4 PreparedStatement preStatement = null; 5 ResultSet resultSet = null; 6 Student student = null; 7 try { 8 //①加载（注册）驱动 9 //Class.forName("com.mysql.jdbc.Driver"); 10 //②获取连接 11 //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin"); 12 conn = DBCPUtil.getInstance().getConnection(); 13 //③预编译的语句对象：提前编译好SQL语句 14 String queryOneSql = "select * from jdbcdemo02 where id = ?"; 15 preStatement = conn.prepareStatement(queryOneSql); 16 preStatement.setInt(1, id); 17 //④执行SQL语句：执行时无需代入SQL 18 resultSet = preStatement.executeQuery(); 19 if(resultSet.next()) 20 { 21 student = new Student(); 22 student.setId(resultSet.getInt("id")); 23 student.setName(resultSet.getString("name")); 24 student.setAge(resultSet.getInt("age")); 25 } 26 }catch (Exception ex){ 27 28 }finally { 29 //⑤释放资源 30 DBCPUtil.getInstance().close(resultSet, preStatement, conn); 31 } 32 return student; 33 }
DBCPUtil.java文件：
1 package cn.yif.utils; 2 3 import org.apache.commons.dbcp.BasicDataSource; 4 import org.apache.commons.dbcp.BasicDataSourceFactory; 5 6 import javax.sql.DataSource; 7 import java.io.IOException; 8 import java.sql.*; 9 import java.util.Properties; 10 11 public class DBCPUtil { 12 //使用静态static类构造单例模式 13 private DBCPUtil(){ } 14 //在静态代码块中创建instance的实例，这里使用static静态变量来保存 15 private static DBCPUtil instance = null; 16 //每次都要加载驱动，这里定义一个Properties，把资源文件里面的内容读到Properties里面 17 private static Properties properties = null; 18 //定义一个连接池对象 19 //private static BasicDataSource ds = null; 20 private static DataSource ds = null; 21 //JDBCUtil类加载的时候，就加载注册驱动 22 static { 23 properties = new Properties(); 24 try { 25 //当前线程类加载器加载获取properties文件 26 properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties")); 27 //方式一：创建一个BasicDataSource对象来读取配置文件 28 // ds = new BasicDataSource(); 29 // ds.setDriverClassName(properties.getProperty("driverClassName")); 30 // ds.setUrl(properties.getProperty("url")); 31 // ds.setUsername(properties.getProperty("username")); 32 // ds.setPassword(properties.getProperty("password")); 33 //方式二：使用工厂DataSourceFactory来加载配置文件 34 try { 35 ds = BasicDataSourceFactory.createDataSource(properties); 36 } catch (Exception e) { 37 e.printStackTrace(); 38 } 39 } catch (IOException e) { 40 e.printStackTrace(); 41 } 42 instance = new DBCPUtil(); 43 } 44 45 public static DBCPUtil getInstance(){ 46 return instance; 47 } 48 49 //抽取获取连接Connection的方法 50 public Connection getConnection() throws SQLException { 51 //这里不再使用DriverManager，而应该使用BasicDataSource，从连接池中拿到这个连接 52 return ds.getConnection(); 53 } 54 55 //抽取JDBC关闭资源的close方法 56 public void close(ResultSet resultSet, Statement statement, Connection conn){ 57 try { 58 if(resultSet != null){ 59 resultSet.close(); 60 } 61 } catch (SQLException e) { 62 e.printStackTrace(); 63 } 64 try { 65 if(statement != null){ 66 statement.close(); 67 } 68 } catch (SQLException e) { 69 e.printStackTrace(); 70 } 71 try { 72 if(conn != null){ 73 conn.close(); 74 } 75 } catch (SQLException e) { 76 e.printStackTrace(); 77 } 78 } 79 }