使用PreparedStatement执行sql语句

时间:2022-12-27 11:56:02
使用PreparedStatement执行sql语句
存储过程:
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
PASSWORD VARCHAR(20)
);
INSERT INTO users(NAME, PASSWORD) VALUES("木丁西", "1234");
INSERT INTO users(NAME, PASSWORD) VALUES("admin", "admin");

SELECT * FROM users WHERE NAME ='admin' AND PASSWORD='admin2' OR 1=1;

-- 创建带有输入参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_findById(IN uid INT)
BEGIN
SELECT * FROM users WHERE id = uid;
END $

-- 创建带有输入输出参数的存储过程
DELIMITER $
CREATE PROCEDURE pro_getNameById(IN uid INT, OUT uname VARCHAR(20))
BEGIN
SELECT NAME INTO uname FROM users WHERE id = uid;
END $

CALL pro_getNameById(1, @NAME);
SELECT @NAME;

CREATE DATABASE infoSystem;
USE infoSystem;
CREATE TABLE contact(
id VARCHAR(32) PRIMARY KEY, -- 学号
NAME VARCHAR(20), -- 姓名
gender VARCHAR(2),
major VARCHAR(20), -- 专业班级

);

jdbc调用存储过程:
  
  
  1. package com.cn.preparedStatement;
  2. import java.sql.Connection;
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import org.junit.Test;
  6. import com.cn.Util.JdbcUtil;
  7. /**
  8. * 使用PreparedStatement执行sql语句
  9. * @author liuzhiyong
  10. *
  11. */
  12. public class Demo1 {
  13. private Connection conn = null;
  14. private PreparedStatement preStmt = null;
  15. /**
  16. * 使用PreparedStatement执行sql语句(增加)
  17. */
  18. @Test
  19. public void test1() {
  20. try {
  21. //1.获取连接
  22. conn = JdbcUtil.getConnection();
  23. //2.准备预编译的sql语句
  24. String sql = "INSERT INTO employee(ename, gender, dept,email,phone) VALUES(?,?,?,?,?)";//?表示一个参数的占位符
  25. //3.执行预编译sql语句(检查语法)
  26. preStmt = conn.prepareStatement(sql);
  27. /**
  28. * 4.设置参数
  29. * 参数1:参数位置 从1开始
  30. * 参数2:参数值
  31. */
  32. preStmt.setString(1, "李小乐");
  33. preStmt.setString(2, "女");
  34. preStmt.setString(3, "销售部");
  35. preStmt.setString(4, "541247820@qq.com");
  36. preStmt.setString(5, "18071897999");
  37. //5.发送参数,执行sql
  38. int count = preStmt.executeUpdate();
  39. System.out.println(count);
  40. } catch (Exception e) {
  41. throw new RuntimeException(e);
  42. }finally{
  43. JdbcUtil.close(conn, preStmt);
  44. }
  45. }
  46. /**
  47. * 使用PreparedStatement执行sql语句(修改)
  48. */
  49. @Test
  50. public void test2() {
  51. try {
  52. //1.获取连接
  53. conn = JdbcUtil.getConnection();
  54. //2.准备预编译的sql语句
  55. String sql = "UPDATE employee SET ename=? where eid = ?";//?表示一个参数的占位符
  56. //3.执行预编译sql语句(检查语法)
  57. preStmt = conn.prepareStatement(sql);
  58. /**
  59. * 4.设置参数
  60. * 参数1:参数位置 从1开始
  61. * 参数2:参数值
  62. */
  63. preStmt.setString(1, "王宝强");
  64. preStmt.setInt(2, 8);
  65. //5.发送参数,执行sql
  66. int count = preStmt.executeUpdate();
  67. System.out.println(count);
  68. } catch (Exception e) {
  69. throw new RuntimeException(e);
  70. }finally{
  71. JdbcUtil.close(conn, preStmt);
  72. }
  73. }
  74. /**
  75. * 使用PreparedStatement执行sql语句(删除)
  76. */
  77. @Test
  78. public void test3() {
  79. try {
  80. //1.获取连接
  81. conn = JdbcUtil.getConnection();
  82. //2.准备预编译的sql语句
  83. String sql = "delete from employee where eid = ?";//?表示一个参数的占位符
  84. //3.执行预编译sql语句(检查语法)
  85. preStmt = conn.prepareStatement(sql);
  86. /**
  87. * 4.设置参数
  88. * 参数1:参数位置 从1开始
  89. * 参数2:参数值
  90. */
  91. preStmt.setInt(1, 8);
  92. //5.发送参数,执行sql
  93. int count = preStmt.executeUpdate();
  94. System.out.println(count);
  95. } catch (Exception e) {
  96. throw new RuntimeException(e);
  97. }finally{
  98. JdbcUtil.close(conn, preStmt);
  99. }
  100. }
  101. /**
  102. * 使用PreparedStatement执行sql语句(查询)
  103. */
  104. @Test
  105. public void test4() {
  106. ResultSet rs = null;
  107. try {
  108. //1.获取连接
  109. conn = JdbcUtil.getConnection();
  110. //2.准备预编译的sql语句
  111. String sql = "select * from employee";
  112. //3.执行预编译sql语句(检查语法)
  113. preStmt = conn.prepareStatement(sql);
  114. //4.无参数,则直接执行sql
  115. rs = preStmt.executeQuery();
  116. while(rs.next()){
  117. System.out.println(rs.getInt(1) + "#" + rs.getString(2) + "#" + rs.getString(3) + "#" + rs.getString(4) + "#" + rs.getString(5) + "#" + rs.getString(6));
  118. }
  119. } catch (Exception e) {
  120. throw new RuntimeException(e);
  121. }finally{
  122. JdbcUtil.close(conn, preStmt, rs);
  123. }
  124. }
  125. }
抽取jdbc获取Connection对象和关闭Connection对象和Statement对象的工具类JdbcUtil.java
   
   
  1. package com.cn.Util;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. /**
  8. * jdbc的工具类
  9. * @author liuzhiyong
  10. *
  11. */
  12. public class JdbcUtil {
  13. private static String url = "jdbc:mysql://localhost:3306/mydb";
  14. private static String user = "root";
  15. private static String password = "root";
  16. /**
  17. * 静态代码块(只调用一次)
  18. */
  19. static{
  20. //注册驱动程序
  21. try {
  22. Class.forName("com.mysql.jdbc.Driver");
  23. } catch (ClassNotFoundException e) {
  24. // TODO Auto-generated catch block
  25. e.printStackTrace();
  26. System.out.println("驱动程序注册出错!");
  27. }
  28. }
  29. /**
  30. * 获取连接对象的方法
  31. */
  32. public static Connection getConnection(){
  33. try {
  34. Connection conn = DriverManager.getConnection(url, user, password);
  35. return conn;
  36. } catch (SQLException e) {
  37. // TODO Auto-generated catch block
  38. e.printStackTrace();
  39. throw new RuntimeException(e);
  40. }
  41. }
  42. /**
  43. * 释放资源的方法
  44. */
  45. public static void close(Connection conn, Statement stmt, ResultSet rs){
  46. //关闭资源(顺序:后打开,先关闭)
  47. if(rs != null){
  48. try {
  49. rs.close();
  50. } catch (SQLException e) {
  51. System.out.println("ResultSet关闭失败!");
  52. throw new RuntimeException(e);
  53. }
  54. }if(stmt != null){
  55. try {
  56. stmt.close();
  57. } catch (SQLException e) {
  58. System.out.println("Statement关闭失败!");
  59. throw new RuntimeException(e);
  60. }
  61. }
  62. if(conn != null){
  63. try {
  64. conn.close();
  65. } catch (SQLException e) {
  66. System.out.println("Connection关闭失败!");
  67. throw new RuntimeException(e);
  68. }
  69. }
  70. }
  71. public static void close(Connection conn, Statement stmt){
  72. //关闭资源(顺序:后打开,先关闭)
  73. if(stmt != null){
  74. try {
  75. stmt.close();
  76. } catch (SQLException e) {
  77. System.out.println("Statement关闭失败!");
  78. throw new RuntimeException(e);
  79. }
  80. }
  81. if(conn != null){
  82. try {
  83. conn.close();
  84. } catch (SQLException e) {
  85. System.out.println("Connection关闭失败!");
  86. throw new RuntimeException(e);
  87. }
  88. }
  89. }
  90. }