使用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), -- 专业班级
	
);

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);
 }
 }
}

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);
 }
 }
 }
}