JDBC高级
1. PreparedStatement
1.1 PreparedStatement第一个案例 insert操作
public static void testInsert() {
Connection connection = null;
PreparedStatement statement = null;
connection = JDBCUtil.getConnection();
String sql = "insert into javaee2009.student(id, name, age, info, score) VALUES (?,?,?,?,?)";
try {
statement = connection.prepareStatement(sql);
statement.setObject(1, 5);
statement.setObject(2, "丢钥匙的航海中路彭于晏");
statement.setObject(3, 6);
statement.setObject(4, "他喵的就是一个ZZ");
statement.setObject(5, -50);
int i = statement.executeUpdate();
System.out.println(i);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(statement, connection);
}
}
1.2 PreparedStatement update操作
public static void testUpdate() {
Connection connection = null;
PreparedStatement statement = null;
connection = JDBCUtil.getConnection();
String sql = "update javaee2009.student set name = ?, age = ?, info = ?, score = ? where id = ?";
try {
statement = connection.prepareStatement(sql);
statement.setObject(1, "尼古拉斯·赵四");
statement.setObject(2, "66");
statement.setObject(3, "你愁啥~~~");
statement.setObject(4, "5");
statement.setObject(5, "1");
int i = statement.executeUpdate();
System.out.println(i);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(statement, connection);
}
}
1.3 PreparedStatment delete操作
public static void testDelete() {
Connection connection = null;
PreparedStatement statement = null;
connection = JDBCUtil.getConnection();
String sql = "delete from javaee2009.student where id = ?";
try {
statement = connection.prepareStatement(sql);
statement.setObject(1, 4);
int i = statement.executeUpdate();
System.out.println(i);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(statement, connection);
}
}
1.4 PreparedStatement select One操作
public static void testSelectOne() {
ResultSet resultSet = null;
Connection connection = null;
PreparedStatement statement = null;
connection = JDBCUtil.getConnection();
String sql = "select * from javaee2009.student where id = ?";
try {
statement = connection.prepareStatement(sql);
statement.setObject(1, 2);
resultSet = statement.executeQuery();
Student student = null;
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String info = resultSet.getString("info");
float score = resultSet.getFloat("score");
student = new Student(id, name, age, info, score);
}
System.out.println(student);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(resultSet, statement, connection);
}
}
1.5 PreparedStatement select All操作
public static void testSelectAll() {
ResultSet resultSet = null;
Connection connection = null;
PreparedStatement statement = null;
connection = JDBCUtil.getConnection();
String sql = "select * from javaee2009.student";
try {
statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
ArrayList<Student> studentList = new ArrayList<>();
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String info = resultSet.getString("info");
float score = resultSet.getFloat("score");
studentList.add(new Student(id, name, age, info, score));
}
for (Student student : studentList) {
System.out.println(student);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(resultSet, statement, connection);
}
}
2. Statement和PreparedStatement对比
Statment执行的SQL语句为固定SQL语句,需要SQL语句在执行之前明确所有内容。
PreparedStatement执行的SQL语句,就可以后期赋值操作。
1. 预处理SQL语句。告知MySQL数据库,这里目前需要执行一个SQL语句,参数还不确定,MySQL请准备。后期给予MySQL是对应参数,可以降低程序和数据库之间的数据交换量,提高效率。
2. PreparedStatement可以有效的防止【SQL注入】
package com.qfedu.a_preparestatement;
import util.JDBCUtil;
import java.sql.*;
public class Demo2 {
private static String userName = "苟磊";
private static String password = "fdjsaklfjdklsajfl' or 1=1 -- ";
public static void main(String[] args) {
testStatement();
testPreparedStatement();
}
public static void testStatement() {
ResultSet resultSet = null;
Statement statement = null;
Connection connection = null;
connection = JDBCUtil.getConnection();
try {
statement = connection.createStatement();
String sql = "select * from javaee2009.person where userName = '" + userName + "' and password = '" + password + "'";
resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
System.out.println("Statement 登陆成功");
} else {
System.out.println("Statement 登陆失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(resultSet, statement, connection);
}
}
public static void testPreparedStatement() {
ResultSet resultSet = null;
Connection connection = null;
PreparedStatement statement = null;
connection = JDBCUtil.getConnection();
String sql = "select * from javaee2009.person where userName = ? and password = ?;";
try {
statement = connection.prepareStatement(sql);
statement.setObject(1, userName);
statement.setObject(2, password);
resultSet = statement.executeQuery();
if (resultSet.next()) {
System.out.println("PreparedStatement 登陆成功");
} else {
System.out.println("PreparedStatement 登陆失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.close(resultSet, statement, connection);
}
}
}
3. PreparedStatement封装
3.1 功能分析和总结
对于修改方法 ==> update delete insert SQL语句
1. 目标任务的SQL语句
2. 目标任务SQL语句对应的参数个数
对于查询方法 ==> select SQL语句
1. 目标任务的SQL语句
2. 目标任务SQL语句对应的参数个数
3. 【目标】数据类型 当前查询数据库数据行对应的是哪一个类的对象。
通用查询和修改方法。
query
update
3.2 技术点缺失分析
1. SQL语句中 ? 占位符个数如何获得???
int getParameterCount(); 获取SQL语句参数个数
【参数元数据】 MetaData
2. SQL语句对应的参数如何处理???
a. 个数不定
b. 类型多样
Object...
3.2.1 参数元数据提供参数个数
Connection connection = JDBCUtil.getConnection();
String sql = "select * from javaee2009.person where userName = ? and password = ?";
PreparedStatement statement = connection.prepareStatement(sql);
ParameterMetaData parameterMetaData = statement.getParameterMetaData();
System.out.println(parameterMetaData.getParameterCount());
JDBCUtil.close(statement, connection);
3.2.2 BaseDao Update方法封装
package util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class BaseDao {
public int update(String sql, Object... parameters) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
connection = JDBCUtil.getConnection();
statement = connection.prepareStatement(sql);
int parameterCount = statement.getParameterMetaData().getParameterCount();
if (parameters != null && parameterCount == parameters.length && parameterCount != 0) {
for (int i = 0; i < parameterCount; i++) {
statement.setObject(i + 1, parameters[i]);
}
}
int affectedRows = statement.executeUpdate();
JDBCUtil.close(statement, connection);
return affectedRows;
}
}