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

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