使用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);

 }

 }

 }

}