大家好，又见面了，我是你们的朋友全栈君。
使用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语句（增加）*/@Testpublic 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.发送参数，执行sqlint count = preStmt.executeUpdate();System.out.println(count);} catch (Exception e) {throw new RuntimeException(e);}finally{JdbcUtil.close(conn, preStmt);}}/*** 使用PreparedStatement执行sql语句（修改）*/@Testpublic 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.发送参数，执行sqlint count = preStmt.executeUpdate();System.out.println(count);} catch (Exception e) {throw new RuntimeException(e);}finally{JdbcUtil.close(conn, preStmt);}}/*** 使用PreparedStatement执行sql语句（删除）*/@Testpublic 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.发送参数，执行sqlint count = preStmt.executeUpdate();System.out.println(count);} catch (Exception e) {throw new RuntimeException(e);}finally{JdbcUtil.close(conn, preStmt);}}/*** 使用PreparedStatement执行sql语句（查询）*/@Testpublic void test4() {ResultSet rs = null;try {//1.获取连接conn = JdbcUtil.getConnection();//2.准备预编译的sql语句String sql = "select * from employee";//3.执行预编译sql语句（检查语法）preStmt = conn.prepareStatement(sql);//4.无参数，则直接执行sqlrs = 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 blocke.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 blocke.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);}}}}
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/136429.html原文链接：https://javaforall.cn
【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛
【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...
