一、前言
通过DataBackupManager类实现关于数据库备份转移,其中包括数据库SQL执行类SqlExecutor及备份backup接口定义实现
二、代码示例
1. 数据库SQL执行类SqlExecutor,其中包括工具ClassUtils类
import java.sql.Array;@b@import java.sql.Connection;@b@import java.sql.DriverManager;@b@import java.sql.PreparedStatement;@b@import java.sql.SQLException;@b@import java.util.Properties;@b@import com.woopa.common.util.ClassUtils;@b@@b@public class SqlExecutor {@b@ @b@ static Properties prop = null;@b@ @b@ static{@b@ prop = new Properties();@b@ try {@b@ prop.load(SqlExecutor.class.getClassLoader().getResourceAsStream("db.properties"));@b@ Class.forName(prop.getProperty("jdbc.driver"));@b@ } catch (Throwable e) {@b@ e.printStackTrace();@b@ }@b@ }@b@@b@ private Connection conn;@b@ private boolean readOnly;@b@ private boolean autoCommit;@b@ private String catalog;@b@ private int transactionIsolation = 2;@b@ @b@ public boolean isReadOnly() {@b@ return readOnly;@b@ }@b@@b@ public void setReadOnly(boolean readOnly) {@b@ this.readOnly = readOnly;@b@ }@b@@b@ public boolean isAutoCommit() {@b@ return autoCommit;@b@ }@b@@b@ public void setAutoCommit(boolean autoCommit) {@b@ this.autoCommit = autoCommit;@b@ }@b@ @b@ public String getCatalog() {@b@ return catalog;@b@ }@b@@b@ public void setCatalog(String catalog) {@b@ this.catalog = catalog;@b@ }@b@@b@ public int getTransactionIsolation() {@b@ return transactionIsolation;@b@ }@b@@b@ public void setTransactionIsolation(int transactionIsolation) {@b@ this.transactionIsolation = transactionIsolation;@b@ }@b@@b@ /**@b@ * 执行sql@b@ * @param sql 脚本@b@ * @return 执行结果成功与否@b@ */@b@ public boolean execute(final String sql,final Object... params) {@b@ return (Boolean) doExecute(sql, new PreparedStatementCallback(){@b@ public Object execute(Connection conn, PreparedStatement stmt)@b@ throws SQLException {@b@ setParameters(stmt, params);@b@ return stmt.execute();@b@ }@b@ });@b@ }@b@ @b@ public Object execute(String sql, PreparedStatementCallback callback){@b@ return doExecute(sql, callback);@b@ }@b@ @b@ public Object execute(ConnectionCallback callback){@b@ return doExecute(callback);@b@ }@b@ @b@ private Object doExecute(ConnectionCallback callback){@b@ Object result = null;@b@ try {@b@ conn = getConnection();@b@ @b@ beforeExecute(conn);@b@ @b@ result = callback.execute(conn);@b@ @b@ conn.commit();@b@ } catch (SQLException e) {@b@ try {@b@ conn.rollback();@b@ } catch (SQLException e1) {@b@ System.out.println(e1.getMessage());@b@ }@b@ throw new RuntimeException(e);@b@ }finally{@b@ try {@b@ if(conn != null)conn.close();@b@ } catch (SQLException e) {@b@ System.out.println(e.getMessage());@b@ }@b@ }@b@ return result;@b@ }@b@ @b@ private Object doExecute(String sql, PreparedStatementCallback callback) {@b@ if(sql == null) return null;@b@ PreparedStatement stmt = null;@b@ Object result = null;@b@ try {@b@ conn = getConnection();@b@ @b@ beforeExecute(conn);@b@ @b@ stmt = conn.prepareStatement(sql);@b@ result = callback.execute(conn, stmt);@b@ @b@ conn.commit();@b@ } catch (SQLException e) {@b@ try {@b@ conn.rollback();@b@ } catch (SQLException e1) {@b@ System.out.println(e1.getMessage());@b@ }@b@ throw new RuntimeException(e);@b@ }finally{@b@ try {@b@ if(stmt != null)stmt.close();@b@ if(conn != null)conn.close();@b@ } catch (SQLException e) {@b@ System.out.println(e.getMessage());@b@ }@b@ }@b@ return result;@b@ }@b@ @b@ protected void beforeExecute(Connection conn) throws SQLException {@b@ conn.setAutoCommit(autoCommit);@b@ conn.setReadOnly(readOnly);@b@ conn.setTransactionIsolation(transactionIsolation);@b@ if(catalog != null) conn.setCatalog(catalog);@b@ }@b@ @b@ protected void setParameters(PreparedStatement ps, Object[] params)@b@ throws SQLException {@b@ for(int i=0; i<params.length; i++){@b@ setValue(ps, params[i], i+1);@b@ }@b@ }@b@ @b@ public Connection getCurrentConnection(){@b@ return conn;@b@ }@b@ @b@ private Connection getConnection() throws SQLException {@b@ if(conn == null || conn.isClosed()){@b@ conn = DriverManager.getConnection(prop.getProperty("jdbc.url")@b@ , prop.getProperty("jdbc.username")@b@ , prop.getProperty("jdbc.password"));@b@ }@b@ //conn = dataSource.getConnection();@b@ return conn;@b@ }@b@ @b@ protected void setValue(PreparedStatement ps , Object value, int index)@b@ throws SQLException {@b@ if(value == null){@b@ ps.setNull(index, java.sql.Types.NULL);@b@ return;@b@ }@b@ Class<?> type = value.getClass();@b@ if(ClassUtils.isPrimitiveWrapper(type)){@b@ type = ClassUtils.resolvePrimitiveClassName(type);@b@ }@b@ if(int.class.isAssignableFrom(type)){@b@ ps.setInt(index, (Integer)value);@b@ }else if(String.class.isAssignableFrom(type)){@b@ ps.setString(index, (String)value);@b@ }else if(long.class.isAssignableFrom(type)){@b@ ps.setLong(index, (Long)value);@b@ }else if(boolean.class.isAssignableFrom(type)){@b@ ps.setBoolean(index, (Boolean)value);@b@ }else if(double.class.isAssignableFrom(type)){@b@ ps.setDouble(index, (Double)value);@b@ }else if(char.class.isAssignableFrom(type)){@b@ ps.setString(index, (Character)value+"");@b@ }else if(short.class.isAssignableFrom(type)){@b@ ps.setShort(index, (Short)value);@b@ }else if(float.class.isAssignableFrom(type)){@b@ ps.setFloat(index, (Float)value);@b@ }else if(byte.class.isAssignableFrom(type)){@b@ ps.setByte(index, (Byte)value);@b@ }else if(type.isArray()){@b@ ps.setArray(index, (Array)value);@b@ }else if(value instanceof java.sql.Timestamp){@b@ ps.setTimestamp(index, (java.sql.Timestamp) value);@b@ }else if(value instanceof java.sql.Date){@b@ ps.setDate(index, (java.sql.Date)value );@b@ }else if(value instanceof java.util.Date){@b@ ps.setDate(index, new java.sql.Date( ((java.util.Date)value).getTime() ));@b@ }else if(value instanceof java.sql.Time){@b@ ps.setTime(index, (java.sql.Time)value );@b@ }@b@ }@b@ @b@ public static interface PreparedStatementCallback{@b@ Object execute(Connection conn, PreparedStatement stmt) throws SQLException;@b@ }@b@ @b@ public static interface ConnectionCallback{@b@ Object execute(Connection conn) throws SQLException;@b@ }@b@}
2. 数据备份迁移管理类DataBackupManager
import java.io.IOException;@b@import java.io.OutputStream;@b@import java.sql.Connection;@b@import java.sql.ResultSet;@b@import java.sql.ResultSetMetaData;@b@import java.sql.SQLException;@b@import java.sql.Statement;@b@import java.util.ArrayList;@b@import java.util.Iterator;@b@import java.util.List;@b@@b@public class DataBackupManager {@b@ @b@ private static String VERSION = "1.0";@b@ @b@ public static void backup(String[] tables, final OutputStream os) throws IOException {@b@ if(tables == null) throw new IllegalArgumentException("Argument tables cannot be null");@b@ String[] selects = new String[tables.length];@b@ @b@ for(int i=0; i<tables.length; i++){@b@ int index = tables[i].indexOf(",");@b@ if(index != -1){@b@ String spt= tables[i];@b@ tables[i] = spt.substring(0, index);@b@ selects[i] = spt.substring(index+1);@b@ }else{@b@ selects[i] = "select * from "+tables[i];@b@ }@b@ }@b@ backup(selects, tables, os, "UTF-8");@b@ }@b@ @b@ public static void backup(String[] selects, String[] tables, final OutputStream os) throws IOException {@b@ if(selects == null) throw new IllegalArgumentException("Argument selects cannot be null");@b@ if(tables == null) throw new IllegalArgumentException("Argument tables cannot be null");@b@ backup(selects, tables, os, "UTF-8");@b@ }@b@@b@ public static void backup(final String[] selects, final String[] tables, final OutputStream os, final String encoding) throws IOException {@b@ if(selects == null) throw new IllegalArgumentException("Argument selects cannot be null");@b@ if(tables == null) throw new IllegalArgumentException("Argument tables cannot be null");@b@ if(selects.length != tables.length){@b@ throw new IllegalArgumentException("select's length muth equals tables's length");@b@ }@b@ if(os == null) throw new IllegalArgumentException("Argument os cannot be null");@b@@b@ String encode = encoding == null ? "UTF-8" : encoding;@b@@b@ final java.io.OutputStreamWriter osw = new java.io.OutputStreamWriter(os, encode);@b@ final java.io.BufferedWriter bw = new java.io.BufferedWriter(osw);@b@ try{@b@ if(tables != null){@b@ SqlExecutor se = new SqlExecutor();@b@ @b@ se.execute(new SqlExecutor.ConnectionCallback(){@b@ public Object execute(Connection conn) throws SQLException {@b@ java.sql.DatabaseMetaData dbmd = conn.getMetaData();@b@ @b@ printDatabaseMetaData(dbmd);@b@ @b@ //统计总数据行数@b@ long totalRecords = totalRecords(selects, conn);@b@ @b@ System.out.println("********** TotalRecords: "+totalRecords+" **********");@b@ @b@ //加上数据库元数据@b@ writeMetaData(tables, totalRecords, bw, dbmd);@b@ @b@ long index = 0;@b@ @b@ //当前进度@b@ int currentProcess = 0;@b@ @b@ for(int x=0; x<tables.length; x++){@b@ String table = tables[x];@b@ String sql = selects[x];@b@ @b@ System.out.println("================== Table:"+table);@b@ System.out.println("================== SQL:"+sql);@b@ @b@ Statement stmt = null;@b@ ResultSet rs = null;@b@ try{@b@ stmt = conn.createStatement();@b@ @b@ rs = stmt.executeQuery(sql);@b@ @b@ List<String> columnNames = new ArrayList<String>();@b@ @b@ java.util.Map<String, Integer> assigned = new java.util.HashMap<String, Integer>();@b@ @b@ String insert = createInsertString(rs, columnNames, table, assigned);@b@ @b@ while(rs.next()){@b@ Iterator<String> ite = columnNames.iterator();@b@ @b@ final List<String> params = new ArrayList<String>();@b@ @b@ final String values = createValueString(rs, assigned, ite, params);@b@ @b@ StringBuilder line = new StringBuilder(insert + values + "\n");@b@ @b@ if(params.size() > 0){@b@ for(int z=0; z<params.size(); z++){@b@ if(z>0)line.append("\n");@b@ line.append(params.get(z));@b@ }@b@ }@b@ @b@ bw.write(line.toString());@b@ bw.newLine();@b@ @b@ final int percent = (int) ( round((index++) / (totalRecords*1.0F)) * 100F);@b@ @b@ if(percent > 0 && percent > currentProcess){@b@ currentProcess = percent;@b@ System.out.println("当前:"+currentProcess+"%");@b@ }@b@ }@b@ }catch(IOException e){@b@ throw new RuntimeException(e);@b@ }finally{@b@ if(rs != null)rs.close();@b@ if(stmt != null)stmt.close();@b@ }@b@ }@b@ return null;@b@ }@b@@b@ private String createValueString(ResultSet rs,@b@ java.util.Map<String, Integer> assigned,@b@ Iterator<String> ite,@b@ final List<String> params) throws SQLException {@b@ @b@ final StringBuilder values = new StringBuilder(" values(");@b@ @b@ int i = 0;@b@ while(ite.hasNext()){@b@ if(i>0){values.append(",");}@b@ @b@ String columnName = ite.next();@b@ Object value = rs.getObject(columnName);@b@ @b@ int type = getValueSql(columnName, value, assigned);@b@ @b@ switch(type){@b@ case 0: values.append(rs.getObject(columnName).toString()); break;@b@ case 1: {@b@ values.append("?");@b@ params.add("#STRING:<![CDATA[\n"+rs.getString(columnName).toString()+"\n]]>");@b@ break;@b@ }@b@ case 2: {@b@ values.append("?");@b@ params.add("#STRING:<![CDATA[\n"+rs.getString(columnName).toString()+"\n]]>");@b@ }@b@ case 3: {@b@ values.append("?");@b@ params.add("#BOOLEAN:"+rs.getBoolean(columnName));@b@ break;@b@ }@b@ case 4: {@b@ values.append("?");@b@ params.add("#DATE:"+rs.getDate(columnName).getTime());@b@ break;@b@ }@b@ case 5: {@b@ values.append("?");@b@ params.add("#DATETIME:"+rs.getTimestamp(columnName).getTime());@b@ break;@b@ }@b@ case 6: {@b@ values.append("?");@b@ params.add("#TIME:"+rs.getTime(columnName).getTime());@b@ break;@b@ }@b@ case -1: {@b@ values.append("?");@b@ params.add("#NULL");@b@ break;@b@ }@b@ }@b@ i ++;@b@ }@b@ return values.append(")").toString();@b@ }@b@@b@ private String createInsertString(ResultSet rs,@b@ List<String> columnNames, String table,@b@ java.util.Map<String, Integer> assigned)@b@ throws SQLException {@b@ StringBuilder insert = new StringBuilder("insert into "+table+"(");@b@ @b@ ResultSetMetaData rsmd = rs.getMetaData();@b@ @b@ int columnCount = rsmd.getColumnCount();@b@ for(int i=0; i<columnCount; i++){@b@ String columnName = rsmd.getColumnName(i+1);@b@ if( "BIT".equals(rsmd.getColumnTypeName(i+1)) && rsmd.getColumnDisplaySize(i+1) == 1){@b@ assigned.put(columnName, 3);@b@ }else if("CHAR".equals(rsmd.getColumnTypeName(i+1)) && rsmd.getColumnDisplaySize(i+1) == 1){@b@ assigned.put(columnName, 3);@b@ }else if( "TIMESTAMP".equals(rsmd.getColumnTypeName(i+1)) ){@b@ assigned.put(columnName, 5);@b@ }else if("DATE".equals(rsmd.getColumnTypeName(i+1))){@b@ assigned.put(columnName, 4);@b@ }else if("TIME".equals(rsmd.getColumnTypeName(i+1))){@b@ assigned.put(columnName, 6);@b@ }@b@ columnNames.add(columnName);@b@ /*System.out.println("Column:"+columnName+" - Type:"+rsmd.getColumnTypeName(i+1)@b@ +" - ClassType:"+rsmd.getColumnClassName(i+1)+" - IntType:"+rsmd.getColumnType(i+1)@b@ +" - Length:"+rsmd.getColumnDisplaySize(i+1));*/@b@ }@b@ if(columnNames.size() > 0){@b@ for(int z=0; z<columnNames.size(); z++){@b@ if(z>0)insert.append(",");@b@ insert.append(columnNames.get(z));@b@ }@b@ }@b@ insert.append(")");@b@ @b@ return insert.toString();@b@ }@b@@b@ private void printDatabaseMetaData(@b@ java.sql.DatabaseMetaData dbmd) throws SQLException {@b@ System.out.println("DatabaseProductName:"+dbmd.getDatabaseProductName());@b@ System.out.println("DatabaseProductVersion:"+dbmd.getDatabaseProductVersion());@b@ System.out.println("DatabaseDriverName:"+dbmd.getDriverName());@b@ System.out.println("DatabaseDriverVersion:"+dbmd.getDriverVersion());@b@ }@b@@b@ private void writeMetaData(final String[] tables, final long totalRecords,@b@ final java.io.BufferedWriter bw,@b@ java.sql.DatabaseMetaData dbmd) throws SQLException {@b@ try{@b@ bw.write("@Version:"+VERSION);@b@ bw.newLine();@b@ bw.write("@Date:"+new java.util.Date().toString());@b@ bw.newLine();@b@ bw.write("@Author:NiJun");@b@ bw.newLine();@b@ bw.write("@DatabaseProductName:"+dbmd.getDatabaseProductName().replaceAll("\\r", " ").replaceAll("\\n", " "));@b@ bw.newLine();@b@ bw.write("@DatabaseProductVersion:"+dbmd.getDatabaseProductVersion().replaceAll("\\r", " ").replaceAll("\\n", " "));@b@ bw.newLine();@b@ bw.write("@DatabaseDriverName:"+dbmd.getDriverName().replaceAll("\\r", " ").replaceAll("\\n", " "));@b@ bw.newLine();@b@ bw.write("@DatabaseDriverVersion:"+dbmd.getDriverVersion().replaceAll("\\r", " ").replaceAll("\\n", " "));@b@ bw.newLine();@b@ bw.write("@Table:[");@b@ for(int x=0; x<tables.length; x++){@b@ if(x>0)bw.write(",");bw.write(tables[x]);@b@ }@b@ bw.write("]");@b@ bw.newLine();@b@ bw.write("@TotalRecords:"+totalRecords);@b@ bw.newLine();@b@ }catch(IOException e){@b@ throw new RuntimeException(e);@b@ }@b@ }@b@ @b@ private long totalRecords(String[] selects, Connection conn) throws SQLException {@b@ long result = 0;@b@ Statement stmt = null;@b@ ResultSet rs = null;@b@ try{@b@ for(String select : selects){@b@ stmt = conn.createStatement();@b@ rs = stmt.executeQuery("SELECT COUNT(*) FROM ("+select+") cr");@b@ if(rs.next()){@b@ result += rs.getLong(1);@b@ }@b@ rs.close();@b@ stmt.close();@b@ }@b@ }finally{@b@ if(rs != null)rs.close();@b@ if(stmt != null)stmt.close();@b@ }@b@ return result;@b@ }@b@ });@b@ }@b@ }finally{@b@ bw.close();@b@ osw.close();@b@ os.close();@b@ }@b@ }@b@ @b@ private static float round(float f){@b@ return Math.round(f * 100F) / 100F;@b@ }@b@ @b@ private static int getValueSql(String columnName, Object value, java.util.Map<String, Integer> assigned){@b@ if(value == null) return -1;@b@ //System.out.println("--------- ColumnName:"+columnName+" - Type:"+value.getClass().getName());@b@ if(assigned.containsKey(columnName)){@b@ return assigned.get(columnName);@b@ }@b@ Class<?> type = value.getClass();@b@ if(Number.class.isAssignableFrom(type)){@b@ return 0;@b@ }else if(java.sql.Time.class.isAssignableFrom(type)){@b@ return 6;@b@ }else if(java.sql.Timestamp.class.isAssignableFrom(type)){@b@ return 5;@b@ }else if(java.util.Date.class.isAssignableFrom(type)){@b@ return 4;@b@ }else if(boolean.class.isAssignableFrom(type)){@b@ return 3;@b@ }else if(char.class.isAssignableFrom(type)){@b@ return 2;@b@ }@b@ return 1;@b@ }@b@ @b@ public static void showUsage(){@b@ System.out.println("USAGE:java DataBackupManager -s[ql]=SQL语句(多个SQL用冒号分隔)[可选] -t[able]=表名(多个表名用冒号分隔) -f[ile]=导出文件名 -e[ncoding]=编码[可选,默认为UTF-8]");@b@ }@b@ @b@ public static void main(String[] args) throws Exception {@b@ if(args.length <2){@b@ showUsage();@b@ return;@b@ }@b@ String sqls = null;@b@ String tables = null;@b@ String file = null;@b@ String encoding = "UTF-8";@b@ for(int i=0; i<args.length; i++){@b@ String arg = args[i];@b@ if(arg.startsWith("-s=") || arg.startsWith("-sql=")){@b@ sqls = arg.split("=", 2)[1];@b@ }else if(arg.startsWith("-t=") || arg.startsWith("-table=")){@b@ tables = arg.split("=", 2)[1];@b@ }else if(arg.startsWith("-f=") || arg.startsWith("-file=")){@b@ file = arg.split("=", 2)[1];@b@ }else if(arg.startsWith("-e=") || arg.startsWith("-encoding=")){@b@ encoding = arg.split("=", 2)[1];@b@ }@b@ }@b@ if(tables == null || file == null){@b@ showUsage();@b@ return;@b@ }@b@ java.io.OutputStream os = new java.io.FileOutputStream(file);@b@ if(sqls != null){@b@ backup(sqls.split(":"), tables.split(":"), os, encoding);@b@ }else{@b@ backup(tables.split(":"), os);@b@ }@b@ }@b@}