APP
使用PreparedStatement執(zhí)行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;
-- 創(chuàng)建帶有輸入?yún)?shù)的存儲過程
DELIMITER $
CREATE PROCEDURE pro_findById(IN uid INT)
BEGIN
SELECT * FROM users WHERE id = uid;
END $
-- 創(chuàng)建帶有輸入輸出參數(shù)的存儲過程
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), -- 專業(yè)班級
);
jdbc調(diào)用存儲過程：
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執(zhí)行sql語句
* @author liuzhiyong
*
*/
public class Demo1 {
private Connection conn = null;
private PreparedStatement preStmt = null;
/**
* 使用PreparedStatement執(zhí)行sql語句（增加）
*/
@Test
public void test1() {
try {
//1.獲取連接
conn = JdbcUtil.getConnection();
//2.準備預編譯的sql語句
String sql = "INSERT INTO employee(ename, gender, dept,email,phone) VALUES(?,?,?,?,?)";//?表示一個參數(shù)的占位符
//3.執(zhí)行預編譯sql語句（檢查語法）
preStmt = conn.prepareStatement(sql);
/**
* 4.設(shè)置參數(shù)
* 參數(shù)1：參數(shù)位置 從1開始
* 參數(shù)2：參數(shù)值
*/
preStmt.setString(1, "李小樂");
preStmt.setString(2, "女");
preStmt.setString(3, "銷售部");
preStmt.setString(4,
preStmt.setString(5, "18071897999");
//5.發(fā)送參數(shù)，執(zhí)行sql
int count = preStmt.executeUpdate();
System.out.println(count);
} catch (Exception e) {
throw new RuntimeException(e);
}finally{
JdbcUtil.close(conn, preStmt);
}
}
/**
* 使用PreparedStatement執(zhí)行sql語句（修改）
*/
@Test
public void test2() {
try {
//1.獲取連接
conn = JdbcUtil.getConnection();
//2.準備預編譯的sql語句
String sql = "UPDATE employee SET ename=? where eid = ?";//?表示一個參數(shù)的占位符
//3.執(zhí)行預編譯sql語句（檢查語法）
preStmt = conn.prepareStatement(sql);
/**
* 4.設(shè)置參數(shù)
* 參數(shù)1：參數(shù)位置 從1開始
* 參數(shù)2：參數(shù)值
*/
preStmt.setString(1, "王寶強");
preStmt.setInt(2, 8);
//5.發(fā)送參數(shù)，執(zhí)行sql
int count = preStmt.executeUpdate();
System.out.println(count);
} catch (Exception e) {
throw new RuntimeException(e);
}finally{
JdbcUtil.close(conn, preStmt);
}
}
/**
* 使用PreparedStatement執(zhí)行sql語句（刪除）
*/
@Test
public void test3() {
try {
//1.獲取連接
conn = JdbcUtil.getConnection();
//2.準備預編譯的sql語句
String sql = "delete from employee where eid = ?";//?表示一個參數(shù)的占位符
//3.執(zhí)行預編譯sql語句（檢查語法）
preStmt = conn.prepareStatement(sql);
/**
* 4.設(shè)置參數(shù)
* 參數(shù)1：參數(shù)位置 從1開始
* 參數(shù)2：參數(shù)值
*/
preStmt.setInt(1, 8);
//5.發(fā)送參數(shù)，執(zhí)行sql
int count = preStmt.executeUpdate();
System.out.println(count);
} catch (Exception e) {
throw new RuntimeException(e);
}finally{
JdbcUtil.close(conn, preStmt);
}
}
/**
* 使用PreparedStatement執(zhí)行sql語句（查詢）
*/
@Test
public void test4() {
ResultSet rs = null;
try {
//1.獲取連接
conn = JdbcUtil.getConnection();
//2.準備預編譯的sql語句
String sql = "select * from employee";
//3.執(zhí)行預編譯sql語句（檢查語法）
preStmt = conn.prepareStatement(sql);
//4.無參數(shù)，則直接執(zhí)行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對象和關(guān)閉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";
/**
* 靜態(tài)代碼塊（只調(diào)用一次）
*/
static{
//注冊驅(qū)動程序
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("驅(qū)動程序注冊出錯！");
}
}
/**
* 獲取連接對象的方法
*/
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){
//關(guān)閉資源（順序：后打開，先關(guān)閉）
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
System.out.println("ResultSet關(guān)閉失敗！");
throw new RuntimeException(e);
}
}if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
System.out.println("Statement關(guān)閉失?。?);
throw new RuntimeException(e);
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
System.out.println("Connection關(guān)閉失??！");
throw new RuntimeException(e);
}
}
}
public static void close(Connection conn, Statement stmt){
//關(guān)閉資源（順序：后打開，先關(guān)閉）
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
System.out.println("Statement關(guān)閉失敗！");
throw new RuntimeException(e);
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
System.out.println("Connection關(guān)閉失?。?);
throw new RuntimeException(e);
}
}
}
}
本文摘自 ：https://blog.51cto.com/u
