2022.4.23 用配置类测试PreparedStatement对象

注意：JDBC中的所有参数都由？符号占位，这被称为参数标记。在执行SQL语句之前，必须为每个参数提供值

插入测试

 1 package com.xing.lesson03;
 2 
 3 import com.xing.lesson02.utils.JdbcUtils;
 4 
 5 import java.sql.Connection;
 6 import java.sql.PreparedStatement;
 7 import java.sql.SQLException;
 8 import java.util.Date;
 9 
10 public class TestInsert {
11     public static void main(String[] args) {
12         Connection conn = null;
13         PreparedStatement st = null;
14 
15         try {
16             conn = JdbcUtils.getConnection();
17 
18             //区别   使用?占位符代替参数
19             String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUE(?,?,?,?,?)";
20 
21             st = conn.prepareStatement(sql);//预编译  先写sql 然后不执行
22 
23             //手动给参数赋值
24             st.setInt(1,4);//给第1个参数?(对应id)赋值为4    不知道类型用setObject();
25             st.setString(2,"xiaohong");
26             st.setString(3,"132213");
27             st.setString(4,"4654656@qq.com");
28 
29             // util.Data  Java         new Date().getTime()获得时间戳
30             // sql.Data   数据库       new java.sql.Date转化为sql时间
31             st.setDate(5,new java.sql.Date(new Date().getTime()));
32 
33             //执行   区别 不用传参
34             int i = st.executeUpdate();
35             if(i>0){
36                 System.out.println("插入成功");
37             }
38 
39         } catch (SQLException e) {
40             e.printStackTrace();
41         }finally {
42             JdbcUtils.release(conn,st,null);
43         }
44     }
45 }

删除测试

 1 package com.xing.lesson03;
 2 
 3 import com.xing.lesson02.utils.JdbcUtils;
 4 
 5 import java.sql.Connection;
 6 import java.sql.PreparedStatement;
 7 import java.sql.SQLException;
 8 import java.util.Date;
 9 
10 public class TestDelect {
11     public static void main(String[] args) {
12         Connection conn = null;
13         PreparedStatement st = null;
14 
15         try {
16             conn = JdbcUtils.getConnection();
17 
18             //区别   使用?占位符代替参数
19             String sql = "delete from users where id=?";
20 
21             st = conn.prepareStatement(sql);//预编译  先写sql 然后不执行
22 
23             //手动给参数赋值
24             st.setInt(1,4);//给第1个参数?(对应id)赋值为4    不知道类型用setObject();
25             
26             //执行   区别 不用传参
27             int i = st.executeUpdate();
28             if(i>0){
29                 System.out.println("删除成功");
30             }
31 
32         } catch (SQLException e) {
33             e.printStackTrace();
34         }finally {
35             JdbcUtils.release(conn,st,null);
36         }
37     }
38 }

更新测试

 1 package com.xing.lesson03;
 2 
 3 import com.xing.lesson02.utils.JdbcUtils;
 4 
 5 import java.sql.Connection;
 6 import java.sql.PreparedStatement;
 7 import java.sql.SQLException;
 8 import java.util.Date;
 9 
10 public class TestUpdate {
11     public static void main(String[] args) {
12         Connection conn = null;
13         PreparedStatement st = null;
14 
15         try {
16             conn = JdbcUtils.getConnection();
17 
18             //区别   使用?占位符代替参数
19             String sql = "update users set `NAME` = ? where id=?;";
20 
21             st = conn.prepareStatement(sql);//预编译  先写sql 然后不执行
22 
23             //手动给参数赋值
24             st.setString(1,"小黑");
25             st.setInt(2,1);
26 
27             //执行   区别 不用传参
28             int i = st.executeUpdate();
29             if(i>0){
30                 System.out.println("更新成功");
31             }
32 
33         } catch (SQLException e) {
34             e.printStackTrace();
35         }finally {
36             JdbcUtils.release(conn,st,null);
37         }
38     }
39 }

查询测试

 1 package com.xing.lesson03;
 2 
 3 import com.xing.lesson02.utils.JdbcUtils;
 4 
 5 import java.sql.Connection;
 6 import java.sql.PreparedStatement;
 7 import java.sql.ResultSet;
 8 import java.sql.SQLException;
 9 
10 public class TestSelect {
11     public static void main(String[] args) {
12         Connection conn = null;
13         PreparedStatement st = null;
14         ResultSet rs = null;
15 
16         try {
17             conn = JdbcUtils.getConnection();
18 
19             //区别   使用?占位符代替参数
20             String sql = "select * from users where id = ?";
21 
22             st = conn.prepareStatement(sql);//预编译  先写sql 然后不执行
23 
24             st.setInt(1,1);//传递参数
25 
26             //执行  不用传参
27             rs = st.executeQuery();
28             while(rs.next()){
29                 System.out.println(rs.getString("NAME"));
30             }
31 
32         } catch (SQLException e) {
33             e.printStackTrace();
34         }finally {
35             JdbcUtils.release(conn,st,rs);
36         }
37     }
38 }

正常查询用户

PreparedStatement防止SQL注入的本质，把传递进来的参数当做字符

假设其中存在转义字符，就直接忽略，比如''引号会被直按转义

 1 package com.xing.lesson03;
 2 
 3 import com.xing.lesson02.utils.JdbcUtils;
 4 
 5 import java.sql.*;
 6 
 7 public class SQL注入 {
 8     public static void main(String[] args) {
 9         login("list","123456");
10         //login("''or 1=1","123456");
11     }
12 
13     public static void login(String username,String password) {
14         Connection conn = null;
15         PreparedStatement st = null;
16         ResultSet rs = null;
17         try {
18             conn = JdbcUtils.getConnection();//获取数据库连接
19 
20             String sql = "select * from users where `NAME` =? and `PASSWORD` =?";
21 
22             st = conn.prepareStatement(sql);//预编译
23             st.setString(1,username);
24             st.setString(2,password);
25 
26             rs = st.executeQuery();//查询 完毕返回结果集
27             while(rs.next()){
28                 System.out.println(rs.getString("NAME"));
29                 System.out.println(rs.getString("password"));
30             }
31         } catch (SQLException e) {
32             e.printStackTrace();
33         }finally {
34             JdbcUtils.release(conn,st,rs);
35         }
36     }
37 
38 }

 

 

posted @ 2022-04-26 20:16  暴躁C语言  阅读(14)  评论(0)    收藏  举报