jdbc（插入大对象及读取大对象、存储过程）

 1 /**
 2  * 插入大对象
 3  */
 4     @Test
 5     public void testSavePic(){
 6         Connection conn=null;
 7         Statement st=null;
 8         PreparedStatement pst=null;
 9         try {
10             conn=DBUtils.getConn();
11             conn.setAutoCommit(false);//事物不能自动提交
12             st=conn.createStatement();
13             long start = System.currentTimeMillis();
14             String sql="insert into stu(sid,name,pic,info) values(?,?,?,?)";
15             pst=conn.prepareStatement(sql);
16             pst.setInt(1, 1);
17             pst.setString(2, "tim");
18             //设置大对象
19             File file =new File("D:\arch\unarch/b.jpg");
20             FileInputStream fis = new FileInputStream(file);
21             pst.setBinaryStream(3, fis,file.length());//设置二进制流，指定长度
22             pst.setString(4, "xxxxxx");
23             pst.executeUpdate();
24              conn.commit();//提交事务
25              System.out.println(System.currentTimeMillis()-start);
26         } catch (Exception e) {
27             // TODO Auto-generated catch block
28             e.printStackTrace();
29         }
30         finally{
31             DBUtils.closeAll(null, st, conn);
32         }
33     }

2、读取大对象

 1 /**
 2  * 对取大对象
 3  */
 4     @Test
 5     public void testReadPic(){
 6         Connection conn=null;
 7         Statement st=null;
 8         PreparedStatement pst=null;
 9         try {
10             conn=DBUtils.getConn();
11             conn.setAutoCommit(false);//事物不能自动提交
12             st=conn.createStatement();
13             long start = System.currentTimeMillis();
14             String sql="select pic from stu where sid=?";
15             pst=conn.prepareStatement(sql);
16             pst.setInt(1, 1);
17             ResultSet rs = pst.executeQuery();
18             if(rs.next()){
19                 byte[] bytes = rs.getBytes(1);
20                 FileOutputStream fos = new FileOutputStream("d:/kk.jpg");
21                 fos.write(bytes);
22                 fos.close();
23             }
24                     
25              conn.commit();//提交事务
26              System.out.println("over");
27              System.out.println(System.currentTimeMillis()-start);
28         } catch (Exception e) {
29             // TODO Auto-generated catch block
30             e.printStackTrace();
31         }
32         finally{
33             DBUtils.closeAll(null, st, conn);
34         }
35     }

3、存储过程

 1     /**
 2      * 测试存储过程1
 3      */
 4         @Test
 5         public void testInsertBig(){
 6             Connection conn=null;
 7             Statement st=null;
 8             PreparedStatement pst=null;
 9             long start = System.currentTimeMillis();
10             try {
11                 conn=DBUtils.getConn();
12                 conn.setAutoCommit(false);//事物不能自动提交
13                 CallableStatement cas = conn.prepareCall("{call up_biginsert(?)}");
14                 cas.setInt(1, 1000000);
15                 cas.execute();
16                  conn.commit();//提交事务
17                  cas.close();
18                  System.out.println("over");
19                  System.out.println(System.currentTimeMillis()-start);
20             } catch (Exception e) {
21                 // TODO Auto-generated catch block
22                 e.printStackTrace();
23             }
24             finally{
25                 DBUtils.closeAll(null, st, conn);
26             }
27         }
28     /**
29      * 测试存储过程2
30      */
31         @Test
32         public void testProcedure(){
33             Connection conn=null;
34             Statement st=null;
35             PreparedStatement pst=null;
36             long start = System.currentTimeMillis();
37             try {
38                 conn=DBUtils.getConn();
39                 conn.setAutoCommit(false);//事物不能自动提交
40                 CallableStatement cas = conn.prepareCall("{call up_add(?,?,?)}");
41                 cas.setInt(1, 1);
42                 cas.setInt(2, 2);
43                 cas.registerOutParameter(3, Types.INTEGER);
44                 cas.execute();
45                 int r=cas.getInt(3);
46                 System.out.println(r);
47                  conn.commit();//提交事务
48                  cas.close();
49                  System.out.println("over");
50                  System.out.println(System.currentTimeMillis()-start);
51             } catch (Exception e) {
52                 // TODO Auto-generated catch block
53                 e.printStackTrace();
54             }
55             finally{
56                 DBUtils.closeAll(null, st, conn);
57             }
58         }