![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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 }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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(); }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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 }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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 }
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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
有点乱... 欢迎留言补充!