JDBC的基本使用2
1、maven项目使用JDBC
新建一个 maven JavaSE 项目，往 pom.xml 文件中添加以下依赖：
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.12</version> </dependency>
依赖添加完成后就可以直接使用 JDBC 连接数据库并操作了：
public class JdbcTest01 { public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { //1.注册驱动（mysql5之后的驱动jar包可以省略注册驱动的步骤） Class.forName("com.mysql.jdbc.Driver"); //2.获取数据库连接对象 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456"); //3.定义sql语句 String sql = "update student set name = 'hahaha' where id = 1"; //4.获取执行sql的对象 stmt = conn.createStatement(); //5.执行sql int count = stmt.executeUpdate(sql); System.out.println(count); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } finally { //6.释放资源。为了避免空指针异常，必须先判断是否为null if(stmt != null) { try { stmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } }
上面执行完成后输出结果为 1 。
2、Statement 对象实现增删改查
2.1、statement对象基本介绍
我们可以通过 Connection 对象的 createStatement() 方法来创建一个 Statement 对象，以此来执行 SQL 语句：
Connection conn = DriverManager.getConnection(URL, 用户名, 密码); //建立连接 Statement stmt = conn.createStatement(); //创建statement对象 stmt.execute(SqlStr); //执行SQL
当你创建了一个 Statement 对象之后，你可以用它的三个执行方法的任一方法来执行 SQL 语句。
-
boolean execute(String SQL) : 可以执行任何SQL语句，但是这个语句较少使用。如果第一个结果是一个 ResultSet 对象，则返回的布尔值为 true ，否则都会返回 false 。当你需要使用真正的动态 SQL 时，可以使用这个方法来执行 SQL DDL 语句。
-
int executeUpdate(String SQL) : 常用该方法来执行DML语句（增删改），也可执行DDL语句（操作数据库和表结构）。它返回的是执行 SQL 语句影响的行的数目。
- ResultSet executeQuery(String SQL) : 常用该方法执行DQL语句（查询），它返回一个 ResultSet 对象。
在使用后 statement 对象后我们应该关闭它，通过调用 close() 方法就可以关闭。其实在我们关闭了 Connection 对象后，它也会自动关闭 Statement 对象。但我们应该始终明确关闭 Statement 对象，以确保真正的清除。
Statement stmt = null; try { stmt = conn.createStatement( ); ... } catch (SQLException e) { ... } finally { stmt.close(); }
2.2、statement对象实现DML（增删改）
DML（增删改）语句都可以使用 execute() 或者 executeUpdate() 方法，一般我们可以使用 executeUpdate() 方法，因为该方法能返回 SQL 语句影响的行的数目。
-
boolean execute(String SQL) : 该方法可以执行任何的SQL语句。如果第一个结果是一个 ResultSet 对象，则返回的布尔值为 true ，否则返回 false 。当你需要使用真正的动态 SQL 时，可以使用这个方法来执行 SQL DDL 语句。
-
int executeUpdate(String SQL) : 常用该方法来执行DML语句（增删改），也可执行DDL语句（操作数据库和表结构）。它返回的是执行 SQL 语句影响的行的数目。
增删改语句执行方式都一样，只是SQL不一样而已：
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
String insertSql = "insert into student values (null, 'aaa', 22)"; //添加语句，自增主键可以赋值为null，数据库会自己处理
String delSql = "delete from student where name = 'aaa'"; //删除语句
String updateSql = "update student set name = 'newName' where name = 'aaaa'"; //修改语句
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(updateSql); //增删改只需直接替换sql语句就行，执行方式都一样
if(count > 0) {
System.out.println("成功" + count);
}else {
System.out.println("失败" + count);
}
2.3、statement对象实现DDL（操作数据库和表）
DDL 语句也同样可以使用 executeUpdate() 方法：
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
String insertSql = "alter table student add score varchar(20)"; //增加一列
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(insertSql);
executeUpdate() 执行 DDL 语句时返回值为 0
2.4、statement对象实现DQL（查询）
查询语句应该使用 executeQuery() 方法，该方法返回一个 ResultSet 对象，我们可以通过遍历该对象来获取到查询到的结果集：
public void selectTest() { Connection conn = null; Statement stmt = null; ResultSet resultSet = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456"); String selectSql = "select * from student"; stmt = conn.createStatement(); resultSet = stmt.executeQuery(selectSql); while (resultSet.next()){ //循环判断此时是否还有数据 int id = resultSet.getInt("id"); String username = resultSet.getString("name"); int age = resultSet.getInt("age"); System.out.println(id + "," + username + "," + age); } } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } finally { //释放资源,最后用的最先释放 if (resultSet != null){ try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } } if(stmt != null) { try { stmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }
ResultSet 资源也应该要主动释放。
2.4.1、ResultSet 对象
ResultSet 结果集对象，用于封装查询结果。
该对象的常用方法：
- boolean resultsetObj.next()：游标向下移动一行，并且判断当前行是否是最后一行的末尾，即是否还有数据，有则返回 true，否则返回 false。游标在开始时指向的是查询到的数据集的第一行的上一行，也就是开始时就必须使用 next() 才有数据。
- getXxx(参数）：获取数据。参数可以是列的编号（从1开始），比如 getString(1)，或者是列的名称，如 getString("username")。
游标，类似指针索引。可以理解为，最初一开始时游标指在“列名”上，要取到数据就需要让游标向下移动，移动后就指向了第一行数据，然后就可以把第一行的每一列都取出来，一次只能获取一行中的一列数据。
3、PreparedStatement对象实现增删改查
PreparedStatement 接口扩展了 Statement 接口，它让你用一个常用的 Statement 对象增加几个高级功能，这个 statement 对象可以提供灵活多变的动态参数。
创建 PreparedStatement 对象：
String sqlStr = "Update Employees SET age = ? WHERE id = ?"; PreparedStatement pstmt = conn.prepareStatement(sqlStr);
JDBC 中所有的参数都被用 ? 符号表示，这是已知的参数标记。在执行 SQL 语句之前，你必须赋予每一个参数确切的数值。
setXXX() 方法将值绑定到参数，其中 XXX 表示你希望绑定到输入参数的 Java 数据类型。如果你忘了赋予值，你将收到一个 SQLException。每个参数标记映射它的序号位置，第一标记表示位置 1 ，下一个位置为 2 等等。
String sql = "SELECT * FROM user WHERE login=? AND pass=?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setObject(1, name); ps.setObject(2, pass);
所有的 Statement 对象的方法都与数据库交互，execute()、executeQuery()、及executeUpdate() 也能被 PreparedStatement 对象引用。然而，这些方法被 SQL 语句修改后是可以输入参数的。
PreparedStatement可以有效防止sql注入，所以生产环境上一定要使用PreparedStatement，而不能使用Statement。
3.1、PreparedStatement对象实现增删改
使用 PreparedStatement 对象来执行 SQL 跟使用 statement 对象的方式差不多。
@Test public void jdbcTest() { System.out.println(1111); Connection conn = null; PreparedStatement ps = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_test", "root", "123456"); //改 //String updatesql = "update students set name = ? where id = ?"; //ps = conn.prepareStatement(updatesql); //ps.setObject(1, "newName"); //ps.setInt(2, 1); //增 //String insertSql = "insert into students2 values (?, ?)"; //ps = conn.prepareStatement(insertSql); //ps.setInt(1, 3); //ps.setString(2, "lisi"); //删 String deleteSql = "delete from students2 where id = ?"; ps = conn.prepareStatement(deleteSql); ps.setInt(1, 3); //执行sql。注意，这里无需传递sql作为参数 int count = ps.executeUpdate(); System.out.println(count); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } finally { //6.释放资源。为了避免空指针异常，必须先判断是否为null if(ps != null) { try { ps.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }
PreparedStatement 对象在使用后也需要关闭，只需简单调用 close() 方法就可以完成这项工作。如果你关闭了 Connection 对象，那么它也会关闭 PreparedStatement 对象。然而，你应该始终明确关闭 PreparedStatement 对象，以确保真正的清除。
4、SQL注入问题
使用Statement拼字符串非常容易引发SQL注入的问题，这是因为SQL参数往往是从方法参数传入的。
假设用户登录的验证方法如下：
User login(String name, String pass) {
...
stmt.executeQuery("SELECT * FROM user WHERE login='" + name + "' AND pass='" + pass + "'");
...
}
其中，参数name和pass通常都是Web页面输入后由程序接收到的。
如果用户的输入是程序期待的值，就可以拼出正确的SQL。例如：name = "bob"，pass = "1234"：
SELECT * FROM user WHERE login='bob' AND pass='1234'
但是，如果用户的输入是一个精心构造的字符串，就可以拼出意想不到的SQL，这个SQL也是正确的，但它查询的条件不是程序设计的意图。例如：name = "bob' OR pass=", pass = " OR pass='"：
SELECT * FROM user WHERE login='bob' OR pass=' AND pass=' OR pass=''
上面的SQL语句执行的时候，根本就不用判断密码是否正确，这样一来，登录就形同虚设。
要避免SQL注入攻击，一个办法是针对所有字符串参数进行转义，但是转义很麻烦，而且需要在任何使用SQL的地方增加转义代码。
还有一个办法就是使用PreparedStatement。使用PreparedStatement可以完全避免SQL注入的问题，因为PreparedStatement始终使用?作为占位符，并且把数据连同SQL本身传给数据库，这样可以保证每次传给数据库的SQL语句是相同的，只是占位符的数据不同，还能高效利用数据库本身对查询的缓存。上述登录SQL如果用PreparedStatement可以改写如下：
User login(String name, String pass) {
...
String sql = "SELECT * FROM user WHERE login=? AND pass=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, name);
ps.setObject(2, pass);
...
}
所以，PreparedStatement比Statement更安全，而且更快。
注意：使用Java对数据库进行操作时，必须使用PreparedStatement，严禁任何通过参数拼字符串的代码！
把上面使用Statement的代码改为使用PreparedStatement，如下：
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
try (PreparedStatement ps = conn.prepareStatement("SELECT id, grade, name, gender FROM students WHERE gender=? AND grade=?")) {
ps.setObject(1, "M"); // 注意：索引从1开始
ps.setObject(2, 3);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
long id = rs.getLong("id");
long grade = rs.getLong("grade");
String name = rs.getString("name");
String gender = rs.getString("gender");
}
}
}
}
使用PreparedStatement和Statement稍有不同，必须首先调用setObject()设置每个占位符?的值，最后获取的仍然是ResultSet对象。另外注意到从结果集读取列时，使用String类型的列名比索引要易读，而且不易出错。
注意到JDBC查询的返回值总是ResultSet，即使我们写这样的聚合查询SELECT SUM(score) FROM ...，也需要按结果集读取：
ResultSet rs = ...
if (rs.next()) {
double sum = rs.getDouble(1);
}
5、mysql数据类型和java数据类型映射关系
使用JDBC的时候，我们需要在SQL数据类型和Java数据类型之间进行转换。JDBC在java.sql.Types定义了一组常量来表示如何映射SQL数据类型，但是平时我们使用的类型通常也就以下几种：
只有最新的JDBC驱动才支持LocalDate和LocalTime。
详细表格：
