JavaWeb学习笔记（十二）—— JDBC的基本使用
一、JDBC概述
1.1 数据库驱动
这里的驱动的概念和平时听到的那种驱动的概念是一样的，比如平时购买的声卡，网卡直接插到计算机上面是不能用的，必须要安装相应的驱动程序之后才能够使用声卡和网卡，同样道理，我们安装好数据库之后，我们的应用程序也是不能直接使用数据库的，必须要通过相应的数据库驱动程序，通过驱动程序去和数据库打交道，如下所示：
1.2 什么是JDBC
JDBC（Java DataBase Connectivity）就是Java数据库连接，说白了就是用Java语言来操作数据库。原来我们操作数据库是在控制台使用SQL语句来操作数据库，JDBC是用Java语言向数据库发送SQL语句。
1.3 JDBC原理
早期SUN公司的天才们想编写一套可以连接天下所有数据库的API，但是当他们刚刚开始时就发现这是不可完成的任务，因为各个厂商的数据库服务器差异太大了。后来SUN开始与数据库厂商们讨论，最终得出的结论是，由SUN提供一套访问数据库的规范（就是一组接口），并提供连接数据库的协议标准，然后各个数据库厂商会遵循SUN的规范提供一套访问自己公司的数据库服务器的API出现。SUN提供的规范命名为JDBC，而各个厂商提供的，遵循了JDBC规范的，可以访问自己数据库的API被称之为驱动！
JDBC是接口，而JDBC驱动才是接口的实现，没有驱动无法完成数据库连接！每个数据库厂商都有自己的驱动，用来连接自己公司的数据库。
当然还有第三方公司专门为某一数据库提供驱动，这样的驱动往往不是开源免费的！
1.3 JDBC连接数据库
第一步：导入mysql数据库的驱动jar包（mysql-connector-java-5.1.13-bin.jar）
第二步：准备四大参数（driverClassName、url、username、password）
driverClassName：com.mysql.jdbc.Driver
url：URL用于标识数据库的位置，通过URL地址告诉JDBC程序连接哪个数据库，URL的写法为：
常用数据库URL地址的写法：
- Oracle写法：jdbc:oracle:thin:@localhost:1521:sid
- SqlServer写法：jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=sid
- MySql写法：jdbc:mysql://localhost:3306/sid
如果连接的是本地的Mysql数据库，并且连接使用的端口是3306，那么的url地址可以简写为： jdbc:mysql:///数据库
第三步：获取连接
- 加载驱动类：Class.forName("com.mysql.jdbc.Driver");
- 获取Connection对象：Connection con = DriverManager.getConnection(url, username, password);
二、四大核心类
2.1 DriverManager
JDBC程序中的DriverManager用于加载驱动，并创建与数据库的连接，这个API的常用方法：
- DriverManager.registerDriver(new Driver())
- DriverManager.getConnection(url, user, password)，
注意：在实际开发中并不推荐采用registerDriver方法注册驱动。原因有二：
1、查看Driver的源代码可以看到，如果采用此种方式，会导致驱动程序注册两次（在我们执行DriverManager.registerDriver(new Driver()的时候，静态代码块也已经执行了，相当于是实例化了两个Driver对象），也就是在内存中会有两个Driver对象。
2、这种方式会出现硬编码：代码依赖mysql的api，脱离mysql的jar包，程序将无法编译，如果将来想连接Oracle数据库，那么必须要修改代码的。
推荐方式：Class.forName("com.mysql.jdbc.Driver");
采用此种方式不会导致驱动对象在内存中重复出现，并且采用此种方式，程序仅仅只需要一个字符串，不需要依赖具体的驱动，使程序的灵活性更高。
查看com.mysql.jdbc.Driver.java：
public class Driver extends NonRegisteringDriver implements java.sql.Driver { static { try { java.sql.DriverManager.registerDriver(new Driver()); } catch (SQLException E) { throw new RuntimeException("Can't register driver!"); } } …… }
从上边可以看到，它是用静态代码块实现的。
根据类加载机制，当执行 Class.forName(driverClass) 获取其Class对象时， com.mysql.jdbc.Driver 就会被JVM加载，连接，并进行初始化，初始化就会执行静态代码块，也就会执行下边这句代码：
java.sql.DriverManager.registerDriver(new Driver());
这就和第一种方式相同了。
事实上，所有java.sql.Driver的实现类，都提供了static块，块内的代码就是把自己注册到DriverManager中。
2.2 Connection
Jdbc程序中的Connection，它用于代表数据库的连接，Connection是数据库编程中最重要的一个对象，客户端与数据库所有交互都是通过connection对象完成的，这个对象的常用方法：
- createStatement()：创建向数据库发送sql的statement对象。
- prepareStatement(sql) ：创建向数据库发送预编译sql的PrepareSatement对象。
2.3 Statement
Jdbc程序中的Statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。 Statement对象常用方法：
- executeQuery(String sql) ：用于向数据发送查询语句。
- executeUpdate(String sql)：用于向数据库发送insert、update或delete语句
- execute(String sql)：用于向数据库发送任意sql语句
- addBatch(String sql) ：把多条sql语句放到一个批处理中。
- executeBatch()：向数据库发送一批sql语句执行。
2.4 ResultSet
Jdbc程序中的ResultSet用于代表Sql语句的执行结果。
【ResultSet之滚动结果集】
Resultset封装执行结果时，采用的类似于二维表格的方式。ResultSet 对象维护了一个指向表格数据行的游标，初始的时候，游标在第一行之前。ResultSet提供了一系列的方法来移动游标：
- boolean previous()：把光标向上挪一行；
- boolean next()：把光标向下挪一行；
- boolean absolute(int row)：绝对位移，把光标移动到指定的行上；
- void beforeFirst()：把光标放到第一行的前面，这也是光标默认的位置；
- void afterLast()：把光标放到最后一行的后面；
- boolean isFirst()：当前光标位置是否在第一行上；
- boolean isLast()：当前光标位置是否在最后一行上；
【ResultSet之获取结果集元数据】
- 得到元数据：rs.getMetaData()，返回值为ResultSetMetaData；
- 获取结果集列数：int getColumnCount()
- 获取指定列的列名：String getColumnName(int colIndex)
【ResultSet之获取列数据】
可以通过next()方法使ResultSet的游标向下移动，当游标移动到你需要的行时，就需要来获取该行的数据了，ResultSet提供了一系列的获取列数据的方法：
获取任意类型的数据：
-
- Object getObject(int columnIndex)：获取指定列的Object类型的数据。
- Object getObject(string columnName)：获取名称为columnName的列的Object数据；
获取指定类型的数据：
-
- String getString(int columnIndex)：获取指定列的String类型数据；
- String getString(String columnName)：获取名称为columnName的列的String数据；
- int getInt(int columnIndex)：获取指定列的int类型数据；
- int getInt(String columnName)：获取名称为columnName的列的int数据；
注意：上面方法中，参数columnIndex表示列的索引，列索引从1开始，而不是0，这第一点与数组不同。
三、使用JDBC对数据库进行CRUD
3.1 对数据库做增、删、改
/** * 对数据库做增、删、改 */ @Test public void fun1() throws ClassNotFoundException, SQLException { // 准备四大参数 String driverClassName = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/test"; String username = "root"; String password = "root"; // 加载驱动类 Class.forName(driverClassName); // 使用DriverManager,以及剩下的3个参数,得到Connection Connection connection = DriverManager.getConnection(url, username, password); // 通过Connection对象创建Statement Statement statement = connection.createStatement(); // 使用statement发送sql语句 String sql = "DELETE FROM t_user"; int i = statement.executeUpdate(sql); System.out.println(i); }
3.2 对数据库进行查询
/** * 执行查询 */ @Test public void fun2() throws ClassNotFoundException, SQLException { // 准备四大参数 String driverClassName = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/test"; String username = "root"; String password = "root"; // 加载驱动类 Class.forName(driverClassName); // 使用DriverManager,以及剩下的3个参数,得到Connection Connection connection = DriverManager.getConnection(url, username, password); // 通过Connection对象创建Statement Statement statement = connection.createStatement(); // 调用statement的ResultSet rs = executeQuery(String querySql) ResultSet rs = statement.executeQuery("select * from t_account"); // 解析ResultSet // 把行光标移动到第一行,可以调用next()方法完成 while (rs.next()) { //把光标向下移动一行,并判断下一行是否存在 int id = rs.getInt(1);//通过列编号来获取该列的值 String name = rs.getString("name");//通过列名称来获取该列的值 double money = rs.getDouble("money"); System.out.println(id + "," + name + "," + money); } // 关闭资源(倒关) rs.close(); statement.close(); connection.close(); }
注意：Jdbc程序运行完后，切记要释放程序在运行过程中，创建的那些与数据库进行交互的对象，这些对象通常是ResultSet, Statement和Connection对象，特别是Connection对象，它是非常稀有的资源，用完后必须马上释放，如果Connection不能及时、正确的关闭，极易导致系统宕机。Connection的使用原则是尽量晚创建，尽量早的释放。
为确保资源释放代码能运行，资源释放代码也一定要放在finally语句中。
四、JdbcUtils工具类
你也看到了，连接数据库的四大参数是：驱动类、url、用户名，以及密码。这些参数都与特定数据库关联，如果将来想更改数据库，那么就要去修改这四大参数，那么为了不去修改代码，我们写一个JdbcUtils类，让它从配置文件中读取配置参数，然后创建连接对象。
JdbcUtils.java
public class JdbcUtils { private static final String dbconfig = "dbconfig.properties"; private static Properties prop = new Properties(); // 只在JdbcUtils类被加载时执行一次 static{ /* 把配置文件内容加载到prop对象中。 因为是放到static块中完成的加载操作, 所有加载操作只会在JdbcUtils类被加载时完成对配置文件的加载。 */ try { // 给prop进行初始化，即加载dbconfig.properties文件到prop对象中 InputStream inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream(dbconfig); prop.load(inputStream); // 加载驱动类 Class.forName(prop.getProperty("driverClassName")); } catch (Exception e) { throw new RuntimeException(e); } } // 获取连接 public static Connection getConnection() { try { return DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password")); } catch (SQLException e) { throw new RuntimeException(e); } } // 释放连接 public static void release(Connection connection, PreparedStatement pstmt, ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
dbconfig.properties
driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF8 username=root password=root
五、PreparedStatement
5.1 什么是SQL攻击
在需要用户输入的地方，用户输入的是SQL语句的片段，最终用户输入的SQL片段与我们DAO中写的SQL语句合成一个完整的SQL语句！例如用户在登录时输入的用户名和密码都是为SQL语句的片段！下面我们通过一个案例来演示SQL攻击。
首先我们需要创建一张用户表，用来存储用户的信息。
CREATE TABLE user( uid CHAR(32) PRIMARY KEY, username VARCHAR(30) UNIQUE KEY NOT NULL, PASSWORD VARCHAR(30) ); INSERT INTO user VALUES('U_1001', 'zs', 'zs'); SELECT * FROM user;
现在用户表中只有一行记录，就是zs。
下面我们写一个login()方法！
public void login(String username, String password) { Connection con = null; Statement stmt = null; ResultSet rs = null; try { con = JdbcUtils.getConnection(); stmt = con.createStatement(); String sql = "SELECT * FROM user WHERE " + "username='" + username + "' and password='" + password + "'"; rs = stmt.executeQuery(sql); if(rs.next()) { System.out.println("欢迎" + rs.getString("username")); } else { System.out.println("用户名或密码错误！"); } } catch (Exception e) { throw new RuntimeException(e); } finally { JdbcUtils.close(con, stmt, rs); } }
下面是调用这个方法的代码：
login("a' or 'a'='a", "a' or 'a'='a");
这行当前会使我们登录成功！因为是输入的用户名和密码是SQL语句片段，最终与我们的login()方法中的SQL语句组合在一起！我们来看看组合在一起的SQL语句：
SELECT * FROM tab_user WHERE username='a' or 'a'='a' and password='a' or 'a'='a'
知道了SQL攻击的原因后，我们可以通过一下方法来防止SQL攻击：
- 过滤用户输入的数据中是否包含非法字符；
- 分步交验！先使用用户名来查询用户，如果查找到了，再比较密码；
- 使用PreparedStatement。
5.2 PreparedStatement是什么
PreparedStatement叫预编译声明。PreparedStatement是Statement的子接口，我们可以使用PreparedStatement来替换Statement。PreparedStatement的实例对象可以通过调用Connection.preparedStatement()方法获得。相对于Statement对象而言，PreperedStatement有如下好处：
- 防止SQL攻击；
- 提高代码的可读性，以可维护性；
- 提高效率。
Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。PreparedStatement可对SQL进行预编译，从而提高数据库的执行效率。并且PreperedStatement对于sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写。
5.3 PreparedStatement的使用
第一步：给出sql模板：所有的参数使用?来替代
第二步：调用Connection的PreparedStatement prepareStatement(String sql模板)，即创建它时就让它与一条SQL模板绑定；
第三步：调用PreparedStatement的setXXX()系列方法为问号设置值
第四步：调用executeUpdate()或executeQuery()方法，但要注意，调用没有参数的方法；
/** * 通过PreparedStatement对象完成对数据库的CRUD操作 */ @Test public void insert() { Connection connection = null; PreparedStatement pstmt = null; ResultSet rs = null; try { // 获取一个数据库连接 connection = JdbcUtils.getConnection(); // 要执行的SQL命令，SQL中的参数使用?作为占位符 String sql = "insert into t_user(id,name) values(?,?)"; // 通过connection对象获取负责执行SQL命令的prepareStatement对象 pstmt = connection.prepareStatement(sql); // 为SQL语句中的参数赋值,注意,索引是从1开始的 pstmt.setInt(1, 1); //id是int类型的 pstmt.setString(2, "张三"); //name是varchar(字符串类型) // 执行插入操作，executeUpdate方法返回成功的条数 int num = pstmt.executeUpdate(); if (num > 0) { System.out.println("插入成功"); } }catch (Exception e){ e.printStackTrace(); }finally { // SQL执行完成之后释放相关资源 JdbcUtils.release(connection, pstmt, rs); } } @Test public void delete() { Connection connection = null; PreparedStatement pstmt = null; ResultSet rs = null; try { connection = JdbcUtils.getConnection(); String sql = "delete from t_user where id=?"; pstmt = connection.prepareStatement(sql); pstmt.setInt(1, 1); int num = pstmt.executeUpdate(); if (num > 0) { System.out.println("删除成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(connection, pstmt, rs); } } @Test public void update() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); String sql = "update t_user set name=? where id= ?"; pstmt = conn.prepareStatement(sql); pstmt.setString(1, "李四"); pstmt.setInt(2, 1); int num = pstmt.executeUpdate(); if (num > 0) { System.out.println("更新成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn, pstmt, rs); } } @Test public void find() { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); String sql = "select * from t_user where id=?"; pstmt = conn.prepareStatement(sql); pstmt.setInt(1, 1); rs = pstmt.executeQuery(); if (rs.next()) { System.out.println(rs.getString("name")); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn, pstmt, rs); } }
注意：PreparedStatement对象独有的executeQuery()方法是没有参数的，而Statement的executeQuery()是需要参数（SQL语句）的。因为在创建PreparedStatement对象时已经让它与一条SQL模板绑定在一起了，所以在调用它的executeQuery()和executeUpdate()方法时就不再需要参数了。
PreparedStatement最大的好处就是在于重复使用同一模板，给予其不同的参数来重复的使用它。这才是真正提高效率的原因。
所以，建议在今后的开发中，无论什么情况，都去需要PreparedStatement，而不是使用Statement。
5.4 预处理的原理
【服务器（数据库）的工作】
- 校验sql语句的语法！
- 编译：一个与函数相似的东西！
- 执行：调用函数
【PreparedStatement】
使用前提：连接的数据库必须支持预处理！几乎没有不支持的！
特性：每个pstmt都与一个sql模板绑定在一起，先把sql模板给数据库，数据库先进行校验，再进行编译。执行时只是把参数传递过去而已！若二次执行时，就不用再次校验语法，也不用再次编译！直接执行！
参考：https://www.cnblogs.com/xdp-gacl/p/3946207.html
