package dao; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import entity.Student; public class Test { public boolean addStudent(Student student){ Connection connection = DBConnection.getConnection(); PreparedStatement ps = null; //ResultSet rs = null; try { ps = connection.prepareStatement("insert into student values(?,?,?)"); ps.setString(1, student.getName()); ps.setString(2, student.getPassword()); ps.setDate(3, student.getBirthday()); int result = ps.executeUpdate(); System.out.println("影响的行数为:" + result); } catch (SQLException e) { e.printStackTrace(); }finally{ try { ps.close(); connection.close(); } catch (SQLException e) { e.printStackTrace(); } } return false; } /** * 测试一,最起初的测试 */ public void testProcedure1(){ Connection connection = DBConnection.getConnection(); CallableStatement cs = null; ResultSet rs = null; try { cs = connection.prepareCall("{call p_test}"); boolean isResultSet = cs.execute(); if(isResultSet){ rs = cs.executeQuery(); while(rs.next()){ System.out.println(rs.getString("name")); } } } catch (SQLException e) { e.printStackTrace(); }finally{ try { cs.close(); connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 测试二,执行带参数的存储过程 * create procedure p_show_info * @stu_name varchar(10) * as * select id,password from student where name=@stu_name */ public void testProcedure2(){ Connection connection = DBConnection.getConnection(); CallableStatement cs = null; ResultSet rs = null; try { cs = connection.prepareCall("{call p_show_info(?)}"); cs.setString(1, "jerry"); rs = cs.executeQuery(); while(rs.next()){ System.out.println(rs.getLong("id")+" ,"+rs.getString("password")); } } catch (SQLException e) { e.printStackTrace(); }finally{ try { cs.close(); connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 测试三,测试有返回值的存储过程(有单个返回值的情况) * create procedure p_get_return * @test_return_string varchar(20) output, * @test_string varchar(20)='hello,java' * as * set @test_return_string = @test_string */ public void testProcedure3(){ Connection connection = DBConnection.getConnection(); CallableStatement cs = null; ResultSet rs = null; try { cs = connection.prepareCall("{call p_get_return(?,?)}"); cs.registerOutParameter(1, Types.VARCHAR); cs.setString(2, "kkk"); cs.execute(); System.out.println(cs.getString(1)); } catch (SQLException e) { e.printStackTrace(); }finally{ try { cs.close(); connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 测试有多个返回值的情况,以及如何用jdbc执行事务,函数等等 */ /** * 测试四,测试有返回值的存储过程(有多个返回值的情况) * -- 获得指定用户名的密码和生日 * create procedure get_password_birth * @stu_name varchar(20), * @pass varchar(20) output, * @birth smalldatetime output * as * select @pass=password,@birth=birthday from student where name=@stu_name * */ public void testProcedure4(){ Connection connection = DBConnection.getConnection(); CallableStatement cs = null; ResultSet rs = null; try { cs = connection.prepareCall("{call get_password_birth(?,?,?)}"); cs.setString(1, "merry"); cs.registerOutParameter(2, Types.VARCHAR); cs.registerOutParameter(3, Types.DATE); cs.execute(); System.out.println(cs.getString(2)+ " , " + cs.getDate(3)); } catch (SQLException e) { e.printStackTrace(); }finally{ try { cs.close(); connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 测试五,事务的控制 * 给student表插入一条数据,然后回滚事务,查看数据 * @param args */ public void testProcedure5(){ Connection connection = DBConnection.getConnection(); PreparedStatement ps = null; try { connection.setAutoCommit(false); ps = connection.prepareStatement("insert into student values('test','1233554','2010-10-1')"); int i = ps.executeUpdate(); System.out.println(i); connection.rollback();//回滚,再查看数据库中的数据 } catch (SQLException e) { e.printStackTrace(); }finally{ try { ps.close(); connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) { new Test().testProcedure5(); } }