传统的jdbc操作数据库，导出和导入含有blob类型的sql文件

最近项目需要把oracle数据库中的数据导出几条作为备用数据使用，在使用的时候再次导入到数据库，前面以为很简单，可是在做的时候遇到了困难，因为看到了blob这种类型的数据，比较苦恼，传统的导出已经不能使用，于是想了一个办法，在查询到数据后使用rs.getMetaData,得到结果集的结构信息，不明白的同学可以在网上查找下，然后获取字段的列名和类型，判断是否是blob类型，如果是就用字节流写到文件中。

下面是代码：

package com.travelsky.pss.bkg.asom.pnrBackups;import java.io.BufferedWriter;import java.io.File;import java.io.FileOutputStream;import java.io.FileWriter;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.sql.Blob;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.sql.Types;import java.util.ArrayList;import java.util.List;public class PnrBackups {private static Connection conn = null;private static Statement sm = null;// private static String schema="ORCL";//模式名private static String select = "SELECT * FROM";// 查询sqlprivate static String insert = "INSERT INTO";// 插入sqlprivate static String values = "VALUES";// values关键字private static String[] table = { "CTR", "CTR_TKNE" };// table数组private static List<String> insertList = new ArrayList<String>();// 全局存放insertsql文件的数据private static String filePath = "./usecase/";// 绝对路径 导出数据的文件private static String driver = "oracle.jdbc.driver.OracleDriver";private static String url = "jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = IP地址)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = ORCL)))";private static String userName = "";// 用户名private static String passWord = "";// 密码/** * 导出数据库表 *  * @param args * @throws SQLException */public static void main(String[] args) throws SQLException {List<String> listSQL = new ArrayList<String>();connectSQL(driver, url, userName, passWord);// 连接数据库// connectSQL("com.mysql.jdbc.Driver",// "jdbc:mysql://127.0.0.1:3306/test", "root", "root");Long pnrbkey = os.ant.usas1.PNR.pplcky("MGRNV6");String ctrKey = "-2542916197493062913";listSQL = createSQL(pnrbkey, ctrKey);// 创建查询语句executeSQL(conn, sm, listSQL,ctrKey);// 执行sql并拼装createFile(ctrKey);// 创建文件}/** * 创建insertsql.txt并导出数据 */private static void createFile(String ctrKey) {filePath = filePath + ctrKey + ".sql";File file = new File(filePath);if (!file.exists()) {try {file.createNewFile();} catch (IOException e) {System.out.println("创建文件名失败！！");e.printStackTrace();}}FileWriter fw = null;BufferedWriter bw = null;try {fw = new FileWriter(file);bw = new BufferedWriter(fw);if (insertList.size() > 0) {for (int i = 0; i < insertList.size(); i++) {bw.append(insertList.get(i));bw.append("\n");}}} catch (IOException e) {e.printStackTrace();} finally {try {bw.close();fw.close();} catch (IOException e) {e.printStackTrace();}}}/** * 拼装查询语句 *  * @return 返回select集合 */private static List<String> createSQL(Long pnrbkey, String ctrKey) {List<String> listSQL = new ArrayList<String>();for (int i = 0; i < table.length; i++) {StringBuffer sb = new StringBuffer();if (table[i].equals("CTR_TKNE")) {sb.append(select).append(" ").append("CTR_TKNE").append(" ").append("where ").append("CTR_KEY").append(" = '").append(ctrKey).append("'");listSQL.add(sb.toString());} else {// sb.append(select).append(" ").append(schema).append(".").append(table[i]);sb.append(select).append(" ").append(table[i]).append(" ").append("where ").append("PNRB_KEY").append(" = '").append(pnrbkey).append("'");listSQL.add(sb.toString());}}return listSQL;}/** * 连接数据库 创建statement对象 *  * @param driver * @param url * @param UserName * @param Password */public static void connectSQL(String driver, String url, String UserName, String Password) {try {Class.forName(driver).newInstance();conn = DriverManager.getConnection(url, UserName, Password);sm = conn.createStatement();} catch (Exception e) {e.printStackTrace();}}/** * 执行sql并返回插入sql *  * @param conn * @param sm * @param listSQL * @throws SQLException */public static void executeSQL(Connection conn, Statement sm, List listSQL,String ctrKey) throws SQLException {List<String> insertSQL = new ArrayList<String>();ResultSet rs = null;try {rs = getColumnNameAndColumeValue(sm, listSQL, rs, ctrKey);} catch (SQLException e) {e.printStackTrace();} finally {rs.close();sm.close();conn.close();}}/** * 获取列名和列值 *  * @param sm * @param listSQL * @param rs * @return * @throws SQLException */private static ResultSet getColumnNameAndColumeValue(Statement sm, List listSQL, ResultSet rs, String ctrKey) throws SQLException {if (listSQL.size() > 0) {for (int j = 0; j < listSQL.size(); j++) {String sql = String.valueOf(listSQL.get(j));int number = sql.indexOf("where");String tableName = sql.substring(14,number);rs = sm.executeQuery(sql);ResultSetMetaData rsmd = rs.getMetaData();int columnCount = rsmd.getColumnCount();while (rs.next()) {StringBuffer ColumnName = new StringBuffer();StringBuffer ColumnValue = new StringBuffer();for (int i = 1; i <= columnCount; i++) {Object value = rs.getObject(i);if ("".equals(value)) {value = " ";}if (i == 1 || i == columnCount) {String lineName = rsmd.getColumnName(i);if(i == columnCount){ColumnName.append("," + lineName);}else{ColumnName.append(lineName);}if (Types.BLOB == rsmd.getColumnType(i)) {Blob blob = rs.getBlob(lineName);if(blob == null){if(i == columnCount){ColumnValue.append(value);}else{ColumnValue.append(value).append(",");}continue;}InputStream ins = blob.getBinaryStream();//去掉ctrKey前的-ctrKey = ctrKey.replaceAll("-", "");//创建文件String fileNameTemp = "./usecase/" + ctrKey+ "_" + lineName + ".txt";File file = new File(fileNameTemp);try{//如果文件不存在则创建文件if(!file.exists()){file.createNewFile();}}catch(Exception e){e.printStackTrace();}// 输出到文件try {OutputStream fout = new FileOutputStream(file);// 下面将BLOB数据写入文件byte[] b = new byte[1024];int len = 0;while ((len = ins.read(b)) != -1) {fout.write(b, 0, len);}// 依次关闭fout.close();ins.close();if(i == columnCount){ColumnValue.append(fileNameTemp);}else{ColumnValue.append(fileNameTemp).append(",");}} catch (Exception e) {e.printStackTrace();}}else if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i)|| Types.LONGVARCHAR == rsmd.getColumnType(i)) {if(i == columnCount){ColumnValue.append("'").append(value);}else{ColumnValue.append("'").append(value).append("',");}} else if (Types.SMALLINT == rsmd.getColumnType(i)|| Types.INTEGER == rsmd.getColumnType(i) || Types.BIGINT == rsmd.getColumnType(i)|| Types.FLOAT == rsmd.getColumnType(i) || Types.DOUBLE == rsmd.getColumnType(i)|| Types.NUMERIC == rsmd.getColumnType(i) || Types.DECIMAL == rsmd.getColumnType(i)) {if(i == columnCount){ColumnValue.append(value);}else{ColumnValue.append(value).append(",");}} else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i)|| Types.TIMESTAMP == rsmd.getColumnType(i)) {if(i == columnCount){ColumnValue.append("to_date('").append(value).append("',").append("'YYYY-MM-DD HH24:MI:SS')");}else{ColumnValue.append("to_date('").append(value).append("',").append("'YYYY-MM-DD HH24:MI:SS')").append(",");}} else {if(i == columnCount){ColumnValue.append(value);}else{ColumnValue.append(value).append(",");}}} else {String lineName = rsmd.getColumnName(i);ColumnName.append("," + lineName);if (Types.BLOB == rsmd.getColumnType(i)) {Blob blob = rs.getBlob(lineName);if(blob == null){ColumnValue.append(value).append(",");continue;}InputStream ins = blob.getBinaryStream();ctrKey = ctrKey.replaceAll("-", "");//创建文件String fileNameTemp = "./usecase/" + ctrKey+ "_" + lineName + ".txt";File file = new File(fileNameTemp);try{//如果文件不存在则创建文件if(!file.exists()){file.createNewFile();}}catch(Exception e){e.printStackTrace();}// 输出到文件try {OutputStream fout = new FileOutputStream(file);// 下面将BLOB数据写入文件byte[] b = new byte[1024];int len = 0;while ((len = ins.read(b)) != -1) {fout.write(b, 0, len);}// 依次关闭fout.close();ins.close();ColumnValue.append(fileNameTemp).append(",");                                                                       //把文件路径做参数传入values中后期操作会使用到                                                                     } catch (Exception e) {e.printStackTrace();}}else if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i)|| Types.LONGVARCHAR == rsmd.getColumnType(i)) {ColumnValue.append("'").append(value).append("'").append(",");} else if (Types.SMALLINT == rsmd.getColumnType(i)|| Types.INTEGER == rsmd.getColumnType(i) || Types.BIGINT == rsmd.getColumnType(i)|| Types.FLOAT == rsmd.getColumnType(i) || Types.DOUBLE == rsmd.getColumnType(i)|| Types.NUMERIC == rsmd.getColumnType(i) || Types.DECIMAL == rsmd.getColumnType(i)) {ColumnValue.append(value).append(",");} else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i)|| Types.TIMESTAMP == rsmd.getColumnType(i)) {String dateStr = value.toString();String date = null;if(dateStr != null){if(dateStr.contains(".")){String[] time = dateStr.split("\\.");date = time[0];}}ColumnValue.append("to_date('").append(date).append("',").append("'YYYY-MM-DD HH24:MI:SS')").append(",");} else {ColumnValue.append(value).append(",");}}}System.out.println(ColumnName.toString());System.out.println(ColumnValue.toString());insertSQL(ColumnName, ColumnValue,tableName.trim());}}}return rs;}/** * * 拼装insertsql 放到全局list里面 * @param ColumnName * @param ColumnValue * */private static void insertSQL(StringBuffer ColumnName, StringBuffer ColumnValue,String tableName) {StringBuffer insertSQL = new StringBuffer();// insertSQL.append(insert).append(" ").append(schema).append(".").append(table[i])insertSQL.append(insert).append(" ").append(tableName).append("(").append(ColumnName.toString()).append(")").append(values).append("(").append(ColumnValue.toString()).append(")");insertList.add(insertSQL.toString());System.out.println(insertSQL.toString());}}

下面是将导出的数据导入到oracle数据库中：

说到导入，这里需要拿到blob文件的路径，我导出的时候是直接把blob文件的路径填写在blob字段中的，如：

insert into table(id,title,context) values(1,'ceshi',F://blob.txt);

其中context就是blob类型的字段，在valyes中这个字段的值却放的是对应的F://blob.txt文件的路径，这就是我们在导入的时候直接截取到就可以使用了，不多说，上传代码：

package com.travelsky.pss.bkg.asom.pnrBackups;import java.io.File;import java.io.FileInputStream;import java.io.FileReader;import java.io.InputStream;import java.io.OutputStream;import java.io.Reader;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.regex.Matcher;import java.util.regex.Pattern;import com.travelsky.pss.bkg.asom.utils.MyBufferedReader;public class InsertPnrBackups {private static Connection connection;private static Statement st;private static ResultSet rs;private static String url = "jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = ip地址)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = ORCL)))";private static String userName = "";// 用户名private static String passWord = "";// 密码public static void main(String[] args) throws SQLException {try {Class.forName("oracle.jdbc.driver.OracleDriver");System.out.println("开始尝试连接数据库！");//通过DeriverManager类创建Connection连接对象connection = DriverManager.getConnection(url, userName, passWord);// 获取连接connection.setAutoCommit(false);if(connection == null){System.out.println("连接不成功！");return false;}st = connection.createStatement();//ctrKey = "-2542916197493062913";String filePath = "./usecase/" + ctrKey + ".sql";Reader read = new FileReader(new File(filePath));MyBufferedReader mr=new MyBufferedReader(read);String line;String[] fileNamePath = new String[12];while ((line=mr.readLine())!=null) {String sql = line.toString();                                //这里需要获取到有多少个blob文件路径，然后把路径放入到fileNamePath中                               if(sql.contains("./usecase/")){ctrKey = ctrKey.replace("-", "");Matcher m = Pattern.compile("./usecase/"+ ctrKey +"_[A-Z]{3,}_[A-Z0-9]{3,}.txt").matcher(sql);int i = 0;while(m.find()){String fileName = sql.substring(m.start(),m.end()); fileNamePath[i++] = fileName;}//这里是将文件路径全部替换成EMPTY_BLOB()，这个的意思就是放入一个空的blob文件，具体解释各位找度娘吧for(int j = 0; j < fileNamePath.length; j++){if(fileNamePath[j] != "" && fileNamePath[j] != null){sql = sql.replaceAll(fileNamePath[j], "EMPTY_BLOB()");}}}if(!"".equals(line) && line != null){System.out.println(sql);st.addBatch(sql);}}st.executeBatch();//connection.commit();read.close();/* 查询此CLOB对象并锁定 */                        //这里是查询出所有的blob类型的字段，切记，这里的查询必须要加上FOR UPDATE，否则会报错                        rs = st.executeQuery("SELECT INDEX_DATA, CTR_DATA, CTR_DAT0, CTR_DAT1, "+"CTR_DAT2, CTR_DAT3, CTR_DAT4, CTR_DAT5, CTR_DAT6, "+"CTR_DAT7, CTR_DAT8, CTR_DAT9 FROM CTR "+"WHERE CTR_KEY = '"+ ctrKey + "' FOR UPDATE");//有关 ResultSet 中列的名称和类型的信息ResultSetMetaData rsmd = rs.getMetaData();int columnCount = rsmd.getColumnCount();String[] lineName = new String[columnCount];if(rs.next()){                                //这里是拿到所有的列，也就是上面查询出来的字段，然后放到String数组中，在后面会用到                               for(int i = 1; i <= columnCount; i++){lineName[i-1] = rsmd.getColumnName(i);}boolean falg = updateCtr(lineName,fileNamePath);if(falg){identifying = false;System.out.println("操作成功");}}} catch (Exception e) {connection.rollback();e.printStackTrace();}finally{try{if(rs != null){rs.close();}if(st != null){st.close();}if(connection != null){connection.close();}}catch(Exception e){e.printStackTrace();}}}private static boolean updateCtr(String[] lineName, String[] fileNamePath) throws SQLException{//判断是否成功boolean falg = false;//读取blob类型文件数据oracle.sql.BLOB blob = null;for(int i = 0; i < lineName.length; i++){try {//在这里我们就用到了上面的列名数据，和在发送insert into语句时截取出来文件路径，这里才是重中之重blob =  (oracle.sql.BLOB)rs.getBlob(lineName[i]);//得到数据库的输出流OutputStream out = blob.getBinaryOutputStream();//这里用一个文件模拟输入流File file = new File(fileNamePath[i]);InputStream in = new FileInputStream(file);//将输入流写到输出流byte[] temp = new byte[blob.getBufferSize()];int len = 0;while ( (len = in.read(temp)) != -1) {out.write(temp, 0, len);        //blob.putBytes(1,b);    }in.close();out.flush();out.close();falg = true;} catch (Exception e) {falg = false;e.printStackTrace();}}connection.commit();return falg;}}

其实，这里的添加blob类型的字段是分为两个部分的，第一部分就是先添加数据，也就是insert  into table(id,title,context) values (1,'cesi',EMPTY_BLOB()),这也就是上面我们需要把文件路径替换成这个的原因，EMPTY_BLOB()会直接在数据库中插入一个空白的blob文件，然后我们利用查询，查询出刚才添加进去的数据，把blob类型的数据给查询出来，然后使用流一一写入数据库就可以了，我这里是有很多字段所以就写了一个方法，循环读取，如果您是一个字段，那就不需要使用方法了，在if（rs.next()）中直接流读取，写入即可，更方便