JDBC 数据库基本操作以及对blob类型的操作

 1 private String dbUrl="jdbc:mysql://localhost:3306/test";
 2     private String dbUserName="root";
 3     private String dbPassword="ejokovic";
 4     private String jdbcName="com.mysql.jdbc.Driver";
 5     
 6     public Connection getCon() throws Exception{
 7         Class.forName(jdbcName);
 8         Connection con=DriverManager.getConnection(dbUrl,dbUserName,dbPassword);
 9         return con;
10     }
11     
12     public Connection getOrclCon() throws Exception{
13         Class.forName("oracle.jdbc.OracleDriver");
14         Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","uap","123456");
15         return con;
16     }
17     
18     /**
19      * 关闭数据库连接
20      * @param con
21      * @throws Exception
22      */
23     public void closeCon(Connection con) throws Exception{
24         if(con!=null){
25             con.close();
26         }
27     }

JDBC基本操作

public void preSave2(){
            Connection con1 = null;    // mysql
            Connection con2 = null;    // oracle
            PreparedStatement pstmt1 = null;    // 获取mysql中tt1表的所有数据
            PreparedStatement pstmt2 = null;    // 删除oracle中tt2表所有数据 不包括字段
            PreparedStatement pstmt3 = null;    // 获取oracle中tt2表的所有数据 主要是用来与tt1表中的字段进行匹配
            try {
                con1 = dbUtil.getCon();
                con2 = dbUtil.getOrclCon();
                con2.setAutoCommit(false);    // 非自动提交
                pstmt1 = con1.prepareStatement("select *  from tt1");
                pstmt2 = con2.prepareStatement("truncate table tt2");
                pstmt3 = con2.prepareStatement("select * from tt2");
                pstmt2.executeUpdate();// 先删除oracle中的数据
                pstmt2 = con2.prepareStatement("insert into tt2 values("+place(2)+")");    // 拼接添加的sql语句
                ResultSet rs = pstmt1.executeQuery();
                ResultSet rs2 = pstmt3.executeQuery();
                ResultSetMetaData rsmd = rs2.getMetaData();
                long t1 = System.currentTimeMillis();
                com.sgcc.timeUtil.BDUtil db = new com.sgcc.timeUtil.BDUtil();
                int num = db.rows(con1, "tt1");    // 获取tt1的总行数
                while(rs.next()){
                    for(int i=1;i<=rsmd.getColumnCount();i++){
                        pstmt2.setString(i, rs.getString(i));
                    }
                    pstmt2.addBatch();
                    // 五万条批量更新一次 不足五万全部更新
                    if(((rs.getRow() & 49999)==0 && (rs.getRow()!=0)) || num == rs.getRow()){
                        pstmt2.executeBatch();
                        con2.commit();
                        pstmt2.clearBatch();
                    }
                    long t2 = System.currentTimeMillis();
                    System.out.println("转存["+rs.getRow()+"]条数据 耗时"+(t2-t1)+" ms");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
// 拼接占位符
    private String place(int num){
        StringBuffer buf = new StringBuffer();
        for(int i=0;i<num;i++){
            buf.append("?,");
        }
        return buf.deleteCharAt(buf.length()-1).toString();
    }

需求: 将mysql中一张表的数据导入到Oracle中(没有日期字段)

需求: 将mysql中一张表的数据导入到Oracle中(有日期字段)

  1 /** 将数据库中含有图片信息的一张表中的内容完全拷贝到另一个数据库中 其中元数据表中的图片信息是经过Base64加密后的 需要先将其解密在做转存
  2  首先为了确保数据转存后的准确性 需要将主键和图片字段先转存到数据库中 然后在根据主键 把剩余的数据信息放到对应的位置即可
  3 */
  4 // 1. 获取所有主键以便根据主键获取对应的图片信息(图片字段数据很大 不能全部抽取出来)
  5 private List<String> idLists(){
  6         String sql="SELECT t.id FROM asset_attachment t";
  7         ArrayList<String> idList = new ArrayList<String>();
  8         Connection con = null;
  9         PreparedStatement ps = null;
 10         try {
 11             con = dbUtil.conmySqlLoc();
 12             ps = con.prepareStatement(sql);
 13             ResultSet rs = ps.executeQuery();
 14             while(rs.next()){
 15                 idList.add(rs.getString(1));
 16             }
 17             idList.trimToSize();
 18         } catch (Exception e) {
 19             e.printStackTrace();
 20         }finally{
 21             try {
 22                 dbUtil.closeCon(con);
 23             } catch (SQLException e) {
 24                 e.printStackTrace();
 25             }
 26         }
 27         return idList;
 28     }
 29 // 2.保存主键和图片到oracle中 - 注意:需要先对图片进行解密操作
 30 private void saveUrlAndId(List<String> idList){
 31         BASE64Decoder decoder = new BASE64Decoder();
 32         Connection con = null;
 33         Statement ps = null;
 34         try {
 35             con = dbUtil.conmySqlLoc();
 36             ps = con.createStatement();
 37             for(int i=0;i<idList.size();i++){
 38                 String sql = "select t.url from asset_attachment t where id='"+idList.get(i)+"'";
 39                 ResultSet rs = ps.executeQuery(sql);
 40                 // 读取表中加密后的图片信息
 41                 StringBuffer buf = new StringBuffer();
 42                 if(rs.next()){
 43                     InputStream is = rs.getAsciiStream(1);
 44                     Scanner cin = new Scanner(is);
 45                     while(cin.hasNext()){
 46                         buf.append(cin.next()).append("\n");
 47                     }
 48                     // 解密并存放到byte数组
 49                     byte[] decoderBytes = decoder.decodeBuffer(buf.toString());
 50                     // 调用保存到本地/另一个数据库中的方法
 51                     this.oraWrite(idList.get(i), decoderBytes);
 52                     is.close();
 53                 }
 54             }
 55         } catch (Exception e) {
 56             e.printStackTrace();
 57         }finally{
 58             try {
 59                 dbUtil.closeCon(con);
 60             } catch (SQLException e) {
 61                 e.printStackTrace();
 62             }
 63         }
 64     }
 65 // 转存blob类型数据信息
 66     private void oraWrite(String id,byte[] decoderBytes) throws Exception{
 67         //将数据库中的图片保存到本地 - 单张图片 如果是多张的 使用循环 可以把主键作为图片名称
 68 //        FileOutputStream write = new FileOutputStream(new File("d:/test2.png"));
 69 //      write.write(decoderBytes);
 70 //      write.close();
 71         // 保存到另个数据库
 72         Connection conn = dbUtil.conOraLoc();
 73         conn.setAutoCommit(false);
 74         // url 为图片保存字段
 75         PreparedStatement pstmt = conn.prepareStatement("insert into asset_attachment (id,url) values (?,?)");
 76         InputStream input = new ByteArrayInputStream(decoderBytes); 
 77         pstmt.setString(1, id);
 78         pstmt.setBinaryStream(2, input,decoderBytes.length);
 79         pstmt.executeUpdate();
 80         input.close();
 81         conn.commit();
 82         conn.setAutoCommit(true);
 83     }
 84 // 3. 根据主键将剩余信息更新到剩余的字段中
 85 private void updateDataById(){
 86         Connection con = null;
 87         PreparedStatement ps = null;
 88         Connection conOra = null;
 89         PreparedStatement psOra = null;
 90         try {
 91             con = dbUtil.conmySqlLoc();
 92             ps = con.prepareStatement("SELECT t.asset_id,t.type,t.upload_time,t.upload_user,t.remark,t.state,t.update_time,t.update_user_id,t.update_user_name,t.id FROM asset_attachment t ");//WHERE t.id=?
 93             conOra = dbUtil.conUap();
 94             conOra.setAutoCommit(false);
 95             psOra = conOra.prepareStatement("update asset_attachment_t t set t.asset_id=?,t.type=?,t.upload_time=?,t.upload_user=?,t.remark=?,t.state=?,t.update_time=?,t.update_user_id=?,t.update_user_name=? where id=?");
 96             ResultSet rs = ps.executeQuery();
 97             // 获取总行数 用于批量提交时条件判断
 98             int num = this.rows(con, "asset_attachment");
 99             while(rs.next()){
100                 for(int i=1;i<=10;i++){
101                     psOra.setString(i, rs.getString(i));
102                 }
103                 psOra.addBatch();
104                 // 批量提交
105                 if(((rs.getRow() & 49999)==0 && (rs.getRow()!=0)) || num == rs.getRow()){
106                     psOra.executeBatch();
107                     conOra.commit();
108                     psOra.clearBatch();
109                 }
110                 if(psOra!=null){
111                     psOra.clearParameters();
112                 }
113             }
114         } catch (Exception e) {
115             e.printStackTrace();
116         }finally{
117             try {
118                 dbUtil.closeCon(conOra);
119             } catch (SQLException e) {
120                 e.printStackTrace();
121             }
122         }
123     }
124 // 获取表的总行数
125 private int rows(Connection con,String tableName)throws Exception{
126         int num = 0;
127         PreparedStatement pstmt = con.prepareStatement("select count(*) from "+tableName);
128         ResultSet rs = pstmt.executeQuery();
129         while(rs.next()){
130             num = Integer.parseInt(rs.getString(1));
131         }
132         return num;
133     }

需求:对于表中含有blob类型数据转存

 1 /**
 2 将本地图片保存到数据库中
 3 首先 需要先把本地图片读取到内存中
 4 然后 循环将图片保存到数据库
 5 */
 6 // 1. 读取指定文件夹下的所有符合条件的图片
 7     private File[] files(){
 8         File file = new File("C:/Users/Administrator/Desktop/pic");
 9         // FilenameFilter 文件名过滤器
10         File[] files = file.listFiles(new FilenameFilter(){
11             public boolean accept(File filepath,String fileName){
12                 File f = new File(filepath,fileName);
13                 return f.isFile() && fileName.toLowerCase().endsWith(".png");
14             }
15         });
16         // 测试有没有获取到
17         for(File ff:files){
18             //System.out.println(ff.getAbsolutePath()+"=="+ff.getName());
19         }
20         return files;
21     }
22 
23 // 2. 将图片保存到数据库中
24     public void saveImg(File[] files) {
25         int num = 1; // 作为主键
26         Connection conn = null;
27         String sql = "insert into T_YDJH_PIC values (?,?,?)";// id,name,img
28         for (File f : files) {
29             // 将文件的长度读出，并转换成Long型
30             long l1 = f.length(); // 获取图片大小
31             int l2 = (int) l1;
32             System.out.println("文件大小:" + l2);
33             try {
34                 conn = dbUtil.conUap();
35                 // 以流的格式赋值
36                 FileInputStream fis = new FileInputStream(f.getAbsoluteFile());
37                 PreparedStatement ps = conn.prepareStatement(sql);
38                 System.out.println("====>" + fis.available());// 测试获取文件大小
39                 ps.setString(1, (num++) + "");
40                 ps.setString(2,f.getName().substring(0, f.getName().length() - 4));// abc.png => abc
41                 ps.setBinaryStream(3, fis, fis.available());
42                 ps.executeUpdate();
43                 ps.close();
44                 fis.close();
45             } catch (Exception e) {
46                 e.printStackTrace();
47                 try {
48                     conn.rollback();
49                 } catch (SQLException e1) {
50                     e1.printStackTrace();
51                 }
52             }
53         }
54     }

将本地图片添加到数据库中

 1 // 含有日期字段操作时 需要先获取对应的字段类型 然后在根据不同的类型进行赋值操作
 2 public void preSave(){
 3         Connection con1 = null;
 4         Connection con2 = null;
 5         PreparedStatement pstmt1 = null;
 6         PreparedStatement pstmt2 = null;
 7         PreparedStatement pstmt3 = null;
 8         try {
 9             con1 = dbUtil.getCon();
10             con2 = dbUtil.getOrclCon();
11             con2.setAutoCommit(false);
12             pstmt1 = con1.prepareStatement("select *  from t_testcn");
13             pstmt2 = con2.prepareStatement("truncate table mysqltest");
14             pstmt3 = con2.prepareStatement("select * from mysqltest");
15             pstmt2.executeUpdate();
16             pstmt2 = con2.prepareStatement("insert into mysqltest values("+place(5)+")");
17             ResultSet rs = pstmt1.executeQuery();
18             ResultSet rs2 = pstmt3.executeQuery();
19             ResultSetMetaData rsmd = rs2.getMetaData();
20             long t1 = System.currentTimeMillis();
21             com.sgcc.timeUtil.BDUtil db = new com.sgcc.timeUtil.BDUtil();
22             int num = db.rows(con1, "t_testcn");
23             while(rs.next()){
24                 for(int i=1;i<=rsmd.getColumnCount();i++){
25                     if("DATE".equals(rsmd.getColumnTypeName(i))){
26                         pstmt2.setDate(i, rs.getDate(i));
27                     }else{
28                         pstmt2.setString(i, rs.getString(i));
29                     }
30                 }
31                 try {
32                     pstmt2.addBatch();
33                     if(num == rs.getRow()){
34                         pstmt2.executeBatch();
35                         con2.commit();
36                         pstmt2.clearBatch();
37                     }
38                     if(pstmt2!=null){
39                         pstmt2.clearParameters();
40                     }
41                 } catch (Exception e) {
42                     e.printStackTrace();
43                 }
44                 long t2 = System.currentTimeMillis();
45                 System.out.println("转存["+rs.getRow()+"]条数据 耗时"+(t2-t1)+" ms");
46             }
47         } catch (Exception e) {
48             e.printStackTrace();
49         }
50     }

转存数据时含有日期类型

BASE64Decoder类详见:http://files.cnblogs.com/files/ejokovic/sun.misc.BASE64Decoder.zip

有点乱... 欢迎留言补充!

爱程序网

Tags：[db:关键字]