mysql存储过程写预处理_数据的存储过程（通过预处理）

package cn.hncu;

import java.io.BufferedReader;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.io.InputStreamReader;

import java.io.OutputStream;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Types;

import java.util.Scanner;

import java.util.UUID;

import javax.imageio.stream.FileImageInputStream;

import org.junit.Test;

import cn.hncu.pubs.ConnFactory;

//演示存储过程

public class JdbcDemo3 {

@Test//无参

public void callProcedureDemo() throws Exception{

Connection con = ConnFactory.getConn();

CallableStatement cs = con.prepareCall("call p1()"); //参数即是调用存储过程的sql语句；返回是CallableStatement对象，它也是Statement的子类

ResultSet rs = cs.executeQuery(); //如果存储过程中包含select，用executeQuery()

while( rs.next() ){

String name = rs.getString("name");

System.out.println(name);

}

}

@Test//有输入参数

public void callProcedureDemo2() throws Exception{

Connection con = ConnFactory.getConn();

//CallableStatement cs = con.prepareCall("call p2('P06','XYZ')"); //可以，但是参数写死了。如果要接收用户输入，则用占位符

CallableStatement cs = con.prepareCall("call p2(?,?)"); //用占位符写活

cs.setString(1, "P07");

cs.setString(2, "曹操");

ResultSet rs = cs.executeQuery(); //如果存储过程中包含select，用executeQuery()

while( rs.next() ){

String name = rs.getString("name");

System.out.println(name);

}

}

@Test//有输入、输出参数

public void callProcedureDemo3() throws Exception{

Connection con = ConnFactory.getConn();

CallableStatement cs = con.prepareCall("call p3(?,?,?)"); //用占位符写活

cs.setString(1, "P10");

cs.setString(2, "曹植2");

cs.registerOutParameter(3, Types.INTEGER);//指定第3个占位符是输出参数,为其指定数据类型

//cs.execute(); //OK

cs.executeUpdate(); //OK

int a = cs.getInt(3); //获取返回值

System.out.println("a:"+a);

}

}

--------------------------以下是 ConnFactory工厂类----------------------------------------

package cn.hncu.pubs;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.util.Properties;

public class ConnFactory {

private static Connection conn;

private ConnFactory(){

}

static{

try {

Properties p = new Properties();

p.load(ConnFactory.class.getClassLoader().getResourceAsStream("jdbc.properties"));//读取classpath下的资源文件

String driver = p.getProperty("driver");

String url = p.getProperty("url");

String name = p.getProperty("username");

String pwd = p.getProperty("password");

Class.forName(driver);

conn = DriverManager.getConnection(url, name, pwd);

System.out.println("获取连接:"+conn);

} catch (Exception e) {

e.printStackTrace();

}

}

public static Connection getConn(){

return conn;

}

}

-------------------------------jdbc.perproties文件----------这个是写在跟src同目录下------------------------------------

##MySQL

driver=com.mysql.jdbc.Driver

url=jdbc:mysql://127.0.0.1:3306/hncu?useUnicode=true&characterEncoding=utf-8

username=root

password=1234

##Oracle

#driver=oracle.jdbc.driver.OracleDriver

#url=jdbc:oracle:thin:@127.0.0.1:1521:orcl

#username=scott

#password=tiger