PreparedStatement是Statement的子接口,它的实例对象可以通过调用Connection.preparedStatement(sql)方法获得
PreparedStatement与Statement的区别:
PreparedStatement可以避免SQL注入的问题
Statement会使数据库频繁编译SQL,可能造成数据库缓冲区溢出
PreparedStatement可对SQL进行预编译,从而提高数据库的运行效率
PreparedStatement对SQL的参数,允许用占位符进行替换,简化SQL语句的编写
package com.zhangxin9727.preparedStatementTest;
import org.junit.Test;
import java.sql.*;
//PreparedStatement的使用
public class PstmtTest {
@Test
//保存数据
public void save() {
Connection conn = null;
PreparedStatement pstmt = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/jdbctest?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT%2B8",
"root", "mypassword");
String sql = "INSERT user VALUES (null, ?, ?, ?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "zx");
pstmt.setString(2, "password");
pstmt.setString(3, "zhangxin");
int i = pstmt.executeUpdate();
if (i > 0) {
System.out.println("保存成功");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
pstmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
@Test
//修改数据
public void update() {
Connection conn = null;
PreparedStatement pstmt = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/jdbctest?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT%2B8",
"root", "mypassword");
String sql = "UPDATE user SET username = ?, password = ?, name = ? WHERE uid = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "ZX");
pstmt.setString(2, "passwd");
pstmt.setString(3, "Xin");
pstmt.setInt(4, 1);
int i = pstmt.executeUpdate();
if (i > 0) {
System.out.println("修改成功");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
pstmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
@Test
//删除数据
public void delete() {
Connection conn = null;
PreparedStatement pstmt = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/jdbctest?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT%2B8",
"root", "mypassword");
String sql = "DELETE FROM user WHERE uid = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 1);
int i = pstmt.executeUpdate();
if (i > 0) {
System.out.println("删除成功");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
pstmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
@Test
//查询多个数据
public void findAll() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/jdbctest?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT%2B8",
"root", "mypassword");
String sql = "SELECT * FROM user";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("uid") + "\t" + rs.getString("username") + "\t" + rs.getString("password") + "\t" + rs.getString("name"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
pstmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
@Test
//查询单个数据
public void find() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/jdbctest?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT%2B8",
"root", "mypassword");
String sql = "SELECT * FROM user WHERE uid = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setObject(1, 2);
rs = pstmt.executeQuery();
if (rs.next()) {
System.out.println(rs.getInt("uid") + "\t" + rs.getString("username") + "\t" + rs.getString("password") + "\t" + rs.getString("name"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
pstmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
}
-- 数据库代码
CREATE DATABASE jdbctest;
USE jdbctest;
CREATE TABLE user(
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
password VARCHAR(20),
name VARCHAR(20)
);
INSERT user VALUES (NULL, 'aaa', '111', 'Alice'), (NULL, 'bbb', '222', 'Bob'), (NULL,'ccc', '333', 'Cidy');
<!--pom.xml中的dependencies-->
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.14</version>
</dependency>
注:本案例使用IDEA编写,编译环境jdk11,MySQL版本8.0.14,使用maven导入依赖包