JDBC篇二:statement预处理
statement预处理语句对象
作用:
- 可以避免SQL注入攻击
- SQL注入:由于我们用户输入改变了程序中sql语句的本意
- 提高SQL语句在程序中的可读性
- 提高jdbc中同一条sql语句(装载参数)多次执行的效率
*使用
- List item
- 使用占位符?SQL语句需要拼接的时候
- delete from t_user where id=?
- 实例化:conn.preparedStatement();
编码步骤
- 1.建立连接
- 2.sql语句
- 3.设置参数----装载占位符
PreparedStatement ps = conn.prepareStatement(sql);----对sql语句设置参数
ps.setString(1, username);
ps.setString(2, password);-----------------按顺序装载占位符
ps.executeUpdate();
ResultSet rs = ps.executeQuery();----------------发送sql语句
(当返回值为结果集时)ResultSet rs = ps.executeQuery();-----------返回结果集 - 4.关闭连接
账号管理模块
保存信息(INSERT/UPDATE)
sql语句:
INSERT INTO t_user(username,password,email,phone,image,gender,created_user,created_time,modified_user,modified_time) VALUES(?,?,?,?,null,2,‘2’,‘2021-01-01’,‘system’,NOW());
发送sql语句:
ps.executeUpdate();
用户登录(SELECT/QUERY)
sql语句:
SELECT * FROM t_user where username=? and password =?
发送sql语句:
ResultSet rs = ps.executeQuery();-----结果集
if (rs.next()) {}----------------------若结果集有内容则……
修改密码(UPDATE/UPDATE)
sql语句:
UPDATE t_user SET password=? WHERE username=?
发送sql语句:
ps.executeUpdate();
将三个方法的代码放在UserDao中
package cn.aust.shopping.dao;
import cn.aust.jdbc.JDBCUtil;
import cn.aust.shopping.entity.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 账号管理模块
*/
public class UserDAO {
/**
* 保存信息
*/
public void save(User user) {
Connection conn = null;
try {
//1.
conn = JDBCUtil.getConn();
//2.SQL
String sql = "INSERT INTO t_user(username,password,email,phone,image,gender,created_user,created_time,modified_user,modified_time) " +
" VALUES(?,?,?,?,null,2,'2','2021-01-01','system',NOW()); ";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, user.getUsername());
ps.setString(2, user.getPassword());
ps.setString(3, user.getEmail());
ps.setString(4, user.getPhone());
ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtil.release(conn);
}
}
public void login(User user) throws Exception{
/**
* 用户登录
*/
Connection conn = null;
try {
//1.
conn = JDBCUtil.getConn();
String sql = "SELECT * FROM t_user where username=? and password =?";
PreparedStatement ps = conn.prepareStatement(sql);
//2.装载占位符
ps.setString(1, user.getUsername());
ps.setString(2, user.getPassword());
ResultSet rs = ps.executeQuery();
if (rs.next()) {
System.out.println("登录成功!!!");
} else {
System.out.println("账号或密码错误!!!");
}
}finally {
JDBCUtil.release(conn);
}
}
public void modify_password(User user) throws Exception{
/**
* 修改密码
*/
Connection conn = null;
try {
//1.
conn = JDBCUtil.getConn();
String sql = "UPDATE t_user SET password=? WHERE username=?";
PreparedStatement ps = conn.prepareStatement(sql);
//2.装载占位符
ps.setString(1, user.getPassword());
ps.setString(2, user.getUsername());
int row = ps.executeUpdate();
if (row > 0) {
System.out.println("修改密码成功!!!");
} else {
System.out.println("修改密码失败!!!");
}
} finally {
JDBCUtil.release(conn);
}
}
public void modify_phone(User user) throws Exception{
/**
* 修改手机号
*/
Connection conn = null;
try {
//1.
conn = JDBCUtil.getConn();
String sql = "UPDATE t_user SET phone=? WHERE username=? and password=?";
PreparedStatement ps = conn.prepareStatement(sql);
//2.装载占位符
ps.setString(1, user.getPhone());
ps.setString(2, user.getUsername());
ps.setString(3, user.getPassword());
int row = ps.executeUpdate();
if (row > 0) {
System.out.println("修改手机号成功!!!");
} else {
System.out.println("修改手机号失败!!!");
}
} finally {
JDBCUtil.release(conn);
}
}
public void modify_email(User user) throws Exception{
/**
* 修改手机号
*/
Connection conn = null;
try {
//1.
conn = JDBCUtil.getConn();
String sql = "UPDATE t_user SET email=? WHERE username=? and password=?";
PreparedStatement ps = conn.prepareStatement(sql);
//2.装载占位符
ps.setString(1, user.getEmail());
ps.setString(2, user.getUsername());
ps.setString(3, user.getPassword());
int row = ps.executeUpdate();
if (row > 0) {
System.out.println("修改邮箱成功!!!");
} else {
System.out.println("修改邮箱失败!!!");
}
} finally {
JDBCUtil.release(conn);
}
}
public void query_username(User user) throws Exception{
/**
* 查询账号是否被占用
*/
Connection conn = null;
try {
//1.
conn = JDBCUtil.getConn();
String sql = "SELECT * FROM t_user WHERE username=?";
PreparedStatement ps = conn.prepareStatement(sql);
//2.装载占位符
ps.setString(1, user.getUsername());
ResultSet rs = ps.executeQuery();
if (rs.next()) {
System.out.println("账号已存在!!!");
} else {
}
} finally {
JDBCUtil.release(conn);
}
}
public void query_email(User user) throws Exception{
/**
* 查询邮箱是否已存在
*/
Connection conn = null;
try {
//1.
conn = JDBCUtil.getConn();
String sql = "SELECT * FROM t_user WHERE email=?";
PreparedStatement ps = conn.prepareStatement(sql);
//2.装载占位符
ps.setString(1, user.getEmail());
ResultSet rs = ps.executeQuery();
if (rs.next()) {
System.out.println("该邮箱已被注册!!!");
} else {
}
} finally {
JDBCUtil.release(conn);
}
}
public void query_phone(User user) throws Exception{
/**
* 查询手机号是否已存在
*/
Connection conn = null;
try {
//1.
conn = JDBCUtil.getConn();
String sql = "SELECT * FROM t_user WHERE phone=?";
PreparedStatement ps = conn.prepareStatement(sql);
//2.装载占位符
ps.setString(1, user.getPhone());
ResultSet rs = ps.executeQuery();
if (rs.next()) {
System.out.println("手机号已被注册!!!");
} else {
}
} finally {
JDBCUtil.release(conn);
}
}
}