当前位置：首页 > news >正文

PreparedStatement对象

news 2026/6/4 19:42:45

MySQL 数据库学习笔记（续）

十、JDBC 重点！！！

10.7 PreparedStatement对象

PreparedStatement可以防止SQL注入，效率更高

1. 增

package com.guo.lesson03;import com.guo.lesson02.utils.JdbcUtils;import java.sql.*;public class TestInsert {public static void main(String[] args) {Connection conn = null;PreparedStatement st = null;try {conn = JdbcUtils.getConnection();//区别//使用?占位符代替参数String sql = "INSERT INTO users(id,NAME,PASSWORD,email,birthday) VALUES(?,?,?,?,?)";st = conn.prepareStatement(sql);//预编译SQL 先写sql，不执行//手动给参数赋值st.setInt(1,4);//idst.setString(2,"chenguo");//NAMEst.setString(3,"123456");//PASSWORDst.setString(4,"2981367375@qq.com");//email//注意点：  sql.Date        数据库    java.sql.Date()//         util.Date       Java     new Date().getTime() --- 获得时间戳st.setDate(5,new java.sql.Date(new Date(2001,12,5).getTime()));//birthday//执行int i = st.executeUpdate();if (i>0){System.out.println("插入成功");}} catch (SQLException e) {throw new RuntimeException(e);} finally {JdbcUtils.release(conn,st,null);}}
}

2. 删

package com.guo.lesson03;import com.guo.lesson02.utils.JdbcUtils;import java.sql.*;public class TestDelete {public static void main(String[] args) {Connection conn = null;PreparedStatement st = null;try {conn = JdbcUtils.getConnection();//区别//使用?占位符代替参数String sql = "DELETE FROM users where id=?";st = conn.prepareStatement(sql);//预编译SQL 先写sql，不执行//手动给参数赋值st.setInt(1,4);//执行int i = st.executeUpdate();if (i>0){System.out.println("删除成功");}} catch (SQLException e) {throw new RuntimeException(e);} finally {JdbcUtils.release(conn,st,null);}}
}

3. 改

package com.guo.lesson03;import com.guo.lesson02.utils.JdbcUtils;import java.sql.*;public class TestUpdate {public static void main(String[] args) {Connection conn = null;PreparedStatement st = null;try {conn = JdbcUtils.getConnection();//区别//使用?占位符代替参数String sql = "UPDATE users SET `NAME` =? WHERE id =?";st = conn.prepareStatement(sql);//预编译SQL 先写sql，不执行//手动给参数赋值st.setString(1,"李悦");//NAMEst.setInt(2,4);//id//执行int i = st.executeUpdate();if (i>0){System.out.println("更新成功");}} catch (SQLException e) {throw new RuntimeException(e);} finally {JdbcUtils.release(conn,st,null);}}
}

4. 查

package com.guo.lesson03;import com.guo.lesson02.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class TestSelect {public static void main(String[] args) {Connection conn = null;PreparedStatement st = null;ResultSet rs = null;try {conn = JdbcUtils.getConnection();String sql = "select * from users where id =?";st = conn.prepareStatement(sql);st.setInt(1,4);//传递参数rs = st.executeQuery();if (rs.next()){System.out.println(rs.getString("NAME"));}} catch (SQLException e) {throw new RuntimeException(e);} finally {JdbcUtils.release(conn,st,rs);}}
}

5. 防止SQL注入

-- PreparedStatement 防止SQL注入的本质：把传递进来的参数当做字符
-- 假设其中存在转义字符，就直接忽略，比如说：引号会被直接转义

package com.guo.lesson03;import com.guo.lesson02.utils.JdbcUtils;import java.sql.*;public class SQL注入 {public static void main(String[] args) {//login("李悦","123456"); // 正常登录login(" ' or '1=1"," ' or '1=1");  //SQl注入失败}//登录业务public static void login(String username,String password){Connection conn = null;PreparedStatement st = null;ResultSet rs = null;try {conn = JdbcUtils.getConnection();// PreparedStatement 防止SQL注入的本质：把传递进来的参数当做字符// 假设其中存在转义字符，就直接忽略。比如说：引号会被直接转义String sql = "SELECT * FROM users WHERE `NAME` =? AND `PASSWORD` =?";st = conn.prepareStatement(sql);st.setString(1,username);st.setString(2,password);rs = st.executeQuery(); //查询完毕会返回一个结果集if (rs.next()){System.out.println(rs.getString("NAME"));System.out.println(rs.getString("PASSWORD"));System.out.println("==================================");}else {System.out.println("用户名或密码错误");}} catch (SQLException e) {throw new RuntimeException(e);} finally {JdbcUtils.release(conn,st,rs);}}
}