你的位置:首页 > Java教程

[Java教程]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 con21    * @throws Exception22   */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,img28     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 => abc41         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

 

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