使用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, "[e-mail]");
   
   
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);
    
    
}
    
    
}
    
    
}
    
    

    
    
}