使⽤java⽣成备份sqlserver数据表的insert语句
针对sqlserver数据表的备份⼯具很多，有时候条件限制需要我们⾃⼰⽣成insert语句，以便后期直接执⾏这些插⼊语句。下⾯提供了⼀个简单的思路，针对mysql或oracle有兴趣的以后可以试着修改。
public class GenInsertSql {
private static Connection conn =null;
private static Statement sm=null;
private static String schema="dbo";//模式名
private static String select="SELECT * FROM";//查询sql（针对⼀个表的时候，可以将*换成相应的列名）
private static String where="WHERE 1=1 ";//where⼦句（慎⽤，最好针对⼀个表的时候⽤，注意修改createSQL⽅法）
private static String insert="INSERT INTO";//插⼊sql
private static String values="VALUES";//values关键字
private static List <String> insertList=new ArrayList <String>();//全局insertsql⽂件的数据
//存储路径
private static String sqlfilePath ="D:/platform/new/backinsertsql/";//绝对路径，还未到最后⼀层
private static String xmname = "xxx";// 哪个项⽬
private static Boolean isOneFile = false; //是否写到⼀个⽂件中取，如果为true，将写到filePath中，否则写到singleFilePath+表名中
private static String filePath = sqlfilePath+xmname+"/1.txt";
private static String singleFilePath = sqlfilePath+xmname;
//备份哪些表
private static String [] table={"BA_CK_KCPD","SA_RY_GROUP"};//table数组，后期可以修改成list
// 数据库连
private static final String URL = "jdbc:jtds:sqlserver://11.11.11.11:1433;databaseName=xxx";
private static final String NAME = "sa";
private static final String PASS = "111111";
private static final String DRIVER = "net.sourceforge.jtds.jdbc.Driver";
/**
* 导出数据库表
* @param args
* @throws SQLException
*/
//所有sql写到⼀个⽂件中
private static void createFile() {
File file= new File( filePath );
if (!ists()){
try {
} catch (IOException e) {
sqlserver备份表语句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();
}
}
}
//sql分别写到对应表名的⽂件中
private static void createFile(String filename,List <String> insertSqls) {
File file= new File( singleFilePath+"/"+filename+".txt" );
if (!ists()){
try {
} catch (IOException e) {
System. out .println( " 创建⽂件名失败！！ " );
e.printStackTrace();
}
}
FileWriter fw= null ;
BufferedWriter bw= null ;
try {
fw = new FileWriter(file);
bw = new BufferedWriter(fw);
if ( insertSqls .size()>0){
for ( int i=0;i< insertSqls .size();i++){
bw.append( insertSqls .get(i));
bw.append( "\n" );
}
}
insertList.clear();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
bw.close();
fw.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 拼装查询语句
* @return返回select 集合
*/
private static List<String> createSQL() {
List<String> listSQL= new ArrayList<String>();
for ( int i=0;i< table . length ;i++){
StringBuffer sb= new StringBuffer();
sb.append( select ).append( " " ).append( schema ).append( "." ).append( table [i]) ; // .append( " " ).append(where) listSQL.String());
}
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
*/
@SuppressWarnings({ "unused", "rawtypes" })
public static void executeSQL(Connection conn,Statement sm,List listSQL)throws SQLException{
List<String> insertSQL= new ArrayList<String>();
ResultSet rs= null ;
try {
rs = getColumnNameAndColumeValue (sm, listSQL, rs);
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs.close();
sm.close();
conn.close();
}
}
/**
* 获取列名和列值
* @param sm
* @param listSQL
* @param rs
* @return
* @throws SQLException
*/
@SuppressWarnings("rawtypes")
private static ResultSet getColumnNameAndColumeValue(Statement sm,List listSQL, ResultSet rs) throws SQLException { for (int j = 0; j < listSQL.size(); j++) {
String sql = String.(j));
rs = sm.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = ColumnCount();
while (rs.next()) {
StringBuffer ColumnName = new StringBuffer();
StringBuffer ColumnValue = new StringBuffer();
for (int i = 1; i <= columnCount; i++) {
String value = rs.getString(i);
if (i == columnCount) {
ColumnName.ColumnName(i));
if (Types.CHAR == ColumnType(i) || Types.VARCHAR == ColumnType(i)
|| Types.LONGVARCHAR == ColumnType(i)) {
if (value == null) {
ColumnValue.append("null");
} else {
ColumnValue.append("'").append(value).append("'");
}
} else if (Types.SMALLINT == ColumnType(i) || Types.INTEGER == ColumnType(i)
|| Types.BIGINT == ColumnType(i) || Types.FLOAT == ColumnType(i)
|| Types.DOUBLE == ColumnType(i) || Types.NUMERIC == ColumnType(i)
|| Types.DECIMAL == ColumnType(i)) {
if (value == null) {
ColumnValue.append("null");
} else {
ColumnValue.append(value);
}
} else if (Types.DATE == ColumnType(i) || Types.TIME == ColumnType(i)
|| Types.TIMESTAMP == ColumnType(i)) {
if (value == null) {
ColumnValue.append("null");
} else {
// ColumnValue.append("timestamp'").append(value).append("'"); //对于mysql可能需要timestamp，未测试 ColumnValue.append("'").append(value).append("'"); //对于sqlserver来说去掉timestamp
}
} else {
if (value == null) {
ColumnValue.append("null");
} else {
ColumnValue.append(value);
}
}
} else {
ColumnName.ColumnName(i) + ",");
if (Types.CHAR == ColumnType(i) || Types.VARCHAR == ColumnType(i)
|| Types.LONGVARCHAR == ColumnType(i)) {
if (value == null) {
ColumnValue.append("null,");
} else {
ColumnValue.append("'").append(value).append("',");
}
} else if (Types.SMALLINT == ColumnType(i) || Types.INTEGER == ColumnType(i)
|| Types.BIGINT == ColumnType(i) || Types.FLOAT == ColumnType(i)
|| Types.DOUBLE == ColumnType(i) || Types.NUMERIC == ColumnType(i)
|| Types.DECIMAL == ColumnType(i)) {
if (value == null) {
ColumnValue.append("null,");
} else {
ColumnValue.append(value).append(",");
}
} else if (Types.DATE == ColumnType(i) || Types.TIME == ColumnType(i)
|| Types.TIMESTAMP == ColumnType(i)) {
if (value == null) {
ColumnValue.append("null,");
} else {
// ColumnValue.append("timestamp'").append(value).append("',"); //对于mysql可能需要timestamp，未测试 ColumnValue.append("'").append(value).append("',"); //对于sqlserver来说去掉timestamp
}
} else {
if (value == null) {
ColumnValue.append("null,");
} else {
ColumnValue.append(value).append(",");
}
}
}
}
insertSQL( ColumnName, ColumnValue,j,rs.isLast());//j表⽰当前正在处理的表的顺序
}
}
return rs;
}
/**
* 拼装insertsql 放到全局list ⾥⾯
* @param ColumnName
* @param ColumnValue
*/
private static void insertSQL(StringBuffer ColumnName,StringBuffer ColumnValue,int order,Boolean isLast) {
StringBuffer insertSQL= new StringBuffer();
insertSQL.append( insert ).append( " " ).append( schema ).append( "." )
.append( table [order]).append( " (" ).String())
.append( ") " ).append( values ).append( " (" ).String()).append( ");" );
if(isOneFile){
insertList .String());
if(order ==table.length -1 ) createFile(); //最后⼀个表完成时，创建⽂件
}else {
insertList .String());
if(isLast){
createFile(table [order],insertList);
}
}
}
//⼊⼝
public static void executeSelectSQLFile() throws Exception {
List<String> listSQL= new ArrayList<String>();
connectSQL ( DRIVER , URL , NAME ,PASS ); // 连接数据库
listSQL= createSQL (); // 创建查询语句
executeSQL ( conn , sm,listSQL); // 执⾏sql 并拼装
System.out.println("^^^^^^^^^^^^^^^^^^^^^^创建完毕！");
}
public static void main(String[] args) throws Exception {
executeSelectSQLFile();
}
}
直接运⾏，即可⽣成txt⽂件，⾥⾯是insert语句。预览⼀下：
代码⽐较长，但逻辑还算清晰。原⽂作者已经不知道是谁了，在这⾥表⽰感谢！
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论