1、原理框图
2、PreparedStatement介绍
-
可以通过调用 Connection 对象的 preparedStatement(String sql) 方法获取 PreparedStatement 对象
-
PreparedStatement 接口是 Statement 的子接口,它表示一条预编译过的 SQL 语句
-
PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示,调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数,第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始),第二个是设置的 SQL 语句中的参数的值
3、PreparedStatement替换Statement
-
代码的可读性和可维护性。
-
PreparedStatement 能最大可能提高性能:
- DBServer会对预编译语句提供性能优化。因为预编译语句会被缓存下来,防止重复编译浪费时间。
- 在statement语句中整个语句本身不能匹配,每执行一次都要对传入的语句编译一次。
-
PreparedStatement 可以防止 SQL 注入
4、准备
JDBCUtils 用于连接和关闭连接,使用反射和流
package com.su3.util;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
*
* 操作数据库的工具类
*
*/
public class JDBCUtils {
public static Connection getConnection() throws Exception {
// 1.读取配置文件中的4个基本信息 反射
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
// 2.加载驱动,反射
Class.forName(driverClass);
// 3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
/**
* 关闭连接和Statement的操作,增删改
*/
public static void closeResource(Connection conn, Statement ps){
try {
if(ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
*
* @Description 关闭资源操作,查
* @param conn
* @param ps
* @param rs
*/
public static void closeResource(Connection conn, Statement ps, ResultSet rs){
try {
if(ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
customer类用于接收查询的返回值
package com.su3.bean;
import java.sql.Date;
public class Customer {
private int id;
private String name;
private String email;
private Date birth;
public Customer() {
}
public Customer(int id, String name, String email, Date birth) {
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
@Override
public String toString() {
return "Customer{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
", birth=" + birth +
'}';
}
}
Order 类用于接收查询的返回值
package com.su3.bean;
import java.sql.Date;
public class Order {
private int orderId;
private String orderName;
private Date orderDate;
public Order() {
}
public Order(int orderId, String orderName, Date orderDate) {
this.orderId = orderId;
this.orderName = orderName;
this.orderDate = orderDate;
}
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public String getOrderName() {
return orderName;
}
public void setOrderName(String orderName) {
this.orderName = orderName;
}
public Date getOrderDate() {
return orderDate;
}
public void setOrderDate(Date orderDate) {
this.orderDate = orderDate;
}
@Override
public String toString() {
return "Order{" +
"orderId=" + orderId +
", orderName='" + orderName + '\'' +
", orderDate=" + orderDate +
'}';
}
}