使用PreparedStatement执行sql语句

大家好，又见面了，我是你们的朋友全栈君。

使用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调用存储过程：

            package com.cn.preparedStatement;
      
      import java.sql.Connection;
      import java.sql.PreparedStatement;
      import java.sql.ResultSet;
      
      import org.junit.Test;
      
      import com.cn.Util.JdbcUtil;
      
      /**
       * 使用PreparedStatement执行sql语句
       * @author liuzhiyong
       *
       */
      public class Demo1 {       
       
       private Connection conn = null;
       private PreparedStatement preStmt = null;
      
       /**
       * 使用PreparedStatement执行sql语句（增加）
       */
       @Test
       public void test1() {       
       try {       
       //1.获取连接
       conn = JdbcUtil.getConnection();
       
       //2.准备预编译的sql语句
       String sql = "INSERT INTO employee(ename, gender, dept,email,phone) VALUES(?,?,?,?,?)";//?表示一个参数的占位符
       
       //3.执行预编译sql语句（检查语法）
       preStmt = conn.prepareStatement(sql);
       
       /**
       * 4.设置参数
       * 参数1：参数位置 从1开始
       * 参数2：参数值
       */
       preStmt.setString(1, "李小乐");
       preStmt.setString(2, "女");
       preStmt.setString(3, "销售部");
       preStmt.setString(4, "541247820@qq.com");
       preStmt.setString(5, "18071897999");
       
       //5.发送参数，执行sql
       int count = preStmt.executeUpdate();
       System.out.println(count);
       } catch (Exception e) {       
       throw new RuntimeException(e);
       }finally{       
       JdbcUtil.close(conn, preStmt);
       }
       }
       
       /**
       * 使用PreparedStatement执行sql语句（修改）
       */
       @Test
       public void test2() {       
       try {       
       //1.获取连接
       conn = JdbcUtil.getConnection();
       
       //2.准备预编译的sql语句
       String sql = "UPDATE employee SET ename=? where eid = ?";//?表示一个参数的占位符
       
       //3.执行预编译sql语句（检查语法）
       preStmt = conn.prepareStatement(sql);
       
       /**
       * 4.设置参数
       * 参数1：参数位置 从1开始
       * 参数2：参数值
       */
       preStmt.setString(1, "王宝强");
       preStmt.setInt(2, 8);
       
       //5.发送参数，执行sql
       int count = preStmt.executeUpdate();
       System.out.println(count);
       } catch (Exception e) {       
       throw new RuntimeException(e);
       }finally{       
       JdbcUtil.close(conn, preStmt);
       }
       }
       
       /**
       * 使用PreparedStatement执行sql语句（删除）
       */
       @Test
       public void test3() {       
       try {       
       //1.获取连接
       conn = JdbcUtil.getConnection();
       
       //2.准备预编译的sql语句
       String sql = "delete from employee where eid = ?";//?表示一个参数的占位符
       
       //3.执行预编译sql语句（检查语法）
       preStmt = conn.prepareStatement(sql);
       
       /**
       * 4.设置参数
       * 参数1：参数位置 从1开始
       * 参数2：参数值
       */
       preStmt.setInt(1, 8);
       
       //5.发送参数，执行sql
       int count = preStmt.executeUpdate();
       System.out.println(count);
       } catch (Exception e) {       
       throw new RuntimeException(e);
       }finally{       
       JdbcUtil.close(conn, preStmt);
       }
       }
       
       /**
       * 使用PreparedStatement执行sql语句（查询）
       */
       @Test
       public void test4() {       
       ResultSet rs = null;
       try {       
       //1.获取连接
       conn = JdbcUtil.getConnection();
       
       //2.准备预编译的sql语句
       String sql = "select * from employee";
       
       //3.执行预编译sql语句（检查语法）
       preStmt = conn.prepareStatement(sql);
       
       //4.无参数，则直接执行sql
       rs = preStmt.executeQuery();
       
       while(rs.next()){       
       System.out.println(rs.getInt(1) + "#" + rs.getString(2) + "#" + rs.getString(3) + "#" + rs.getString(4) + "#" + rs.getString(5) + "#" + rs.getString(6));
       }
       } catch (Exception e) {       
       throw new RuntimeException(e);
       }finally{       
       JdbcUtil.close(conn, preStmt, rs);
       }
       }
      }

抽取jdbc获取Connection对象和关闭Connection对象和Statement对象的工具类

JdbcUtil.java

                 package com.cn.Util;
        
        import java.sql.Connection;
        import java.sql.DriverManager;
        import java.sql.ResultSet;
        import java.sql.SQLException;
        import java.sql.Statement;
        
        /**
         * jdbc的工具类
         * @author liuzhiyong
         *
         */
        public class JdbcUtil {        
         private static String url = "jdbc:mysql://localhost:3306/mydb";
         private static String user = "root";
         private static String password = "root";
         
         /**
         * 静态代码块（只调用一次）
         */
         static{        
         
         //注册驱动程序
         try {        
         Class.forName("com.mysql.jdbc.Driver");
         } catch (ClassNotFoundException e) {        
         // TODO Auto-generated catch block
         e.printStackTrace();
         System.out.println("驱动程序注册出错！");
         }
         }
         
         /**
         * 获取连接对象的方法
         */
         public static Connection getConnection(){        
         
         try {        
         Connection conn = DriverManager.getConnection(url, user, password);
         return conn;
         } catch (SQLException e) {        
         // TODO Auto-generated catch block
         e.printStackTrace();
         throw new RuntimeException(e);
         }
         }
         
         /**
         * 释放资源的方法
         */
         public static void close(Connection conn, Statement stmt, ResultSet rs){        
         
         //关闭资源（顺序：后打开，先关闭）
         if(rs != null){        
         try {        
         rs.close();
         } catch (SQLException e) {        
         System.out.println("ResultSet关闭失败！");
         throw new RuntimeException(e);
         }
         }if(stmt != null){        
         try {        
         stmt.close();
         } catch (SQLException e) {        
         System.out.println("Statement关闭失败！");
         throw new RuntimeException(e);
         }
         }
         if(conn != null){        
         try {        
         conn.close();
         } catch (SQLException e) {        
         System.out.println("Connection关闭失败！");
         throw new RuntimeException(e);
         }
         }
         }
         public static void close(Connection conn, Statement stmt){        
         
         //关闭资源（顺序：后打开，先关闭）
         if(stmt != null){        
         try {        
         stmt.close();
         } catch (SQLException e) {        
         System.out.println("Statement关闭失败！");
         throw new RuntimeException(e);
         }
         }
         if(conn != null){        
         try {        
         conn.close();
         } catch (SQLException e) {        
         System.out.println("Connection关闭失败！");
         throw new RuntimeException(e);
         }
         }
         }
         
        }