以mysql数据库为基础,用junit做jdbc技术之CallableStatement的相关测试,实现调用无参存储过程、调用带输入参数的存储过程以及调用带输出参数的存储过程。
该测试所需的表book,建表语句如下:
CREATE TABLE `book` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`price` double DEFAULT NULL,
`birth` datetime NOT NULL,
PRIMARY KEY (`id`)
);
该测试所需的存储过程,建存储过程语句如下:
DROP PROCEDURE IF EXISTS sp_select_book_nofilter;
CREATE PROCEDURE sp_select_book_nofilter()
BEGIN
SELECT * FROM book;
END;
DROP PROCEDURE IF EXISTS sp_select_book_filter;
CREATE PROCEDURE sp_select_book_filter(IN sp_name VARCHAR(30))
BEGIN
IF sp_name IS NULL OR sp_name = '' THEN
SELECT * FROM book;
ELSE
SELECT * FROM book WHERE name LIKE CONCAT('%',sp_name,'%');
END IF;
END;
DROP PROCEDURE IF EXISTS sp_select_book_count;
CREATE PROCEDURE sp_select_book_count(OUT count INT(10))
BEGIN
SELECT COUNT(DISTINCT name) INTO count FROM book;
END;
该测试创建了一个数据库对象,用来操作数据库,附代码:
package com.cw.cw.mysql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Mysql {
private static final String URL = "jdbc:mysql://localhost:3306/dbgirl";
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String USER = "root";
private static final String PWD = "123456";
public Connection conn = null;
public Statement stmt = null;
public PreparedStatement pstmt = null;
public ResultSet rs = null;
public Mysql() throws SQLException{
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL,USER,PWD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public void getStatement() throws SQLException, ClassNotFoundException{
stmt = conn.createStatement();
}
public void getPsStatement(String sql) throws SQLException, ClassNotFoundException{
pstmt = conn.prepareStatement(sql);
}
public void close(){
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
该测试创建的测试类,附代码:
package com.cw.cw;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Scanner;
import org.junit.Test;
import com.cw.cw.mysql.Mysql;
public class JdbcProcedureTest {
/**
* CallableStatement的execute
* 调用无参存储过程
* 在navicat里面执行存储过程:CALL sp_select_book_nofilter();
*/
@Test
public void test1() throws SQLException{
System.out.println("test1");
Mysql mysql = new Mysql();
//获取CallableStatement对象
CallableStatement cs = mysql.conn.prepareCall("call sp_select_book_nofilter()");
cs.execute();
mysql.rs = cs.getResultSet();
while(mysql.rs.next()){
Integer id = mysql.rs.getInt(1);
String name = mysql.rs.getString(2);
double price = mysql.rs.getDouble("price");
String birth = mysql.rs.getDate(4)+" "+ mysql.rs.getTime(4);//注意获取日期时间型数据的方式
System.out.println(id+","+name+","+price+","+birth);
}
mysql.close();
System.err.println("=================================================");
}
/**
* CallableStatement的execute
* 调用带输入参数的存储过程
* 在navicat里面执行存储过程:CALL sp_select_book_filter('c');
*/
@SuppressWarnings("resource")
@Test
public void test2() throws SQLException{
System.out.println("test2");
Scanner sc = new Scanner(System.in);
String str = sc.nextLine();
Mysql mysql = new Mysql();
//获取CallableStatement对象
CallableStatement cs = mysql.conn.prepareCall("call sp_select_book_filter(?)");
cs.setString(1, str);
cs.execute();
mysql.rs = cs.getResultSet();
while(mysql.rs.next()){
Integer id = mysql.rs.getInt(1);
String name = mysql.rs.getString(2);
double price = mysql.rs.getDouble("price");
String birth = mysql.rs.getDate(4)+" "+ mysql.rs.getTime(4);//注意获取日期时间型数据的方式
System.out.println(id+","+name+","+price+","+birth);
}
mysql.close();
System.err.println("=================================================");
}
/**
* CallableStatement的execute
* 调用带输出参数的存储过程
* 在navicat里面执行存储过程:不能用call命令,右键存储过程,点击运行函数,提示输入参数,输入@count
*/
@Test
public void test3() throws SQLException{
System.out.println("test3");
Mysql mysql = new Mysql();
//获取CallableStatement对象
CallableStatement cs = mysql.conn.prepareCall("call sp_select_book_count(?)");
//注册输出参数,设置输出参数的类型
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
//获取输出参数
int count = cs.getInt(1);
System.out.println(count);
mysql.close();
System.err.println("=================================================");
System.out.println("junit测试方法执行完毕");
}
}