实现数据库中的数据(db data)转化成document文档 实现jdbc批量插入数据

package com.test;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.Date;
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;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.apache.log4j.Logger;

/**
 * 一.实现数据库中的数据(db data)转化成document文档
 *
 * 二。实现jdbc批量插入数据
 * */
public class DBDataToXmlDomTest {
 private static Logger log = Logger.getLogger(DBDataToXmlDomTest.class);
 private static BasicDataSource ds = null;

 public DBDataToXmlDomTest() {
 }

 public static void main(String[] args) throws Exception {
  init();
  // dbToDom();
  tableDataToXmlDom("tRoom");
 }

 /** jdbc 批量插入* */
 public void batchInsert(List sqllist) {
  Connection con = null;
  Statement st = null;
  try {
   con = getConnection();// 获得连接
   con.setAutoCommit(false);
   st = con.createStatement();
   for (int i = 0; i < sqllist.size(); i++) {
    String sql = sqllist.get(i).toString();
    st.addBatch(sql);
    // 51条sql执行一次批量插入
    if (i != 0 && i % 50 == 0) {
     st.executeBatch();
     con.commit();
    }
   }
   if (st != null) {
    st.executeBatch();
    con.commit();
   }
  } catch (Exception e) {
   log.error("批量导入异常！！", e);
   System.out.println("批量导入异常！！");
   System.out.println("batchInsert  " + e);
  } finally {
   try {
    st.close();
    con.close();
   } catch (SQLException e) {
   }
  }
 }

 /*
  * 把数据库中的数据转化成dom对象
  */
 public static void dbToDom() {
  try {
   StringBuffer dom = new StringBuffer();
   List tables = getTableList();
   for (int i = 0; i < tables.size(); i++) {
    String tableDom = tableDataToXmlDom((tables.get(i)).toString());
    dom.append(tableDom);
   }
   // System.out.println(dom.toString());
   // 写到文件中
   System.out.println("*****start write*****");
   File file = new File("d:/SystemData.xml");
   OutputStream os = new FileOutputStream(file);
   os.write(dom.toString().getBytes());
   os.close();
   System.out.println("*****finish*****");
  } catch (Exception e) {
   System.out.println(e);
  }
 }

 /* 获取指定数据库中所有表的名字 */
 public static List getTableList() throws SQLException {
  List tables = new ArrayList();
  Connection con = getConnection();// 获得连接
  ResultSet rs = con.getMetaData().getTables(null, null, "", null);
  while (rs.next()) {
   // System.out.println(rs.getString(3));
   tables.add(rs.getString(3));
  }
  return tables;
 }

 /* 清空表中的记录 */
 public static void truncateTable(String tableName) throws SQLException {
  Connection con = getConnection();// 获得连接
  Statement st = con.createStatement();
  String sql = "truncate table " + tableName;
  ResultSet rs = st.executeQuery(sql);
  rs.close();
  con.close();// 释放连接
 }

 /*
  * 功能： 把一个数据库表中的数据转化成dom对象
  *
  * param:tableName 表名
  */
 public static String tableDataToXmlDom(String tableName)
   throws SQLException {
  int blank1 = 1;// 空格符数
  int blank2 = 2;

  Connection con = getConnection();// 获得连接
  Statement st = con.createStatement();
  String sql = "select  *  from  " + tableName;
  ResultSet rs = st.executeQuery(sql);
  ResultSetMetaData metaData = rs.getMetaData();
  int columnCount = metaData.getColumnCount();
  StringBuffer tbDom = new StringBuffer();
  while (rs.next()) {
   tbDom.append(blank(blank1));
   tbDom.append("<" + tableName + ">").append(enter());
   for (int i = 1; i <= columnCount; i++) {
    String columnName = metaData.getColumnLabel(i);
    tbDom.append(blank(blank2)).append("<" + columnName + ">");
    int type = metaData.getColumnType(i);
    switch (type) {
    case Types.VARCHAR:
     tbDom.append(singleQuote(rs.getString(i)));
     break;
    case Types.TIMESTAMP:
     tbDom.append(dealDate(rs.getDate(i)));
     break;
    case Types.INTEGER:
     tbDom.append(rs.getInt(i));
     break;
    case Types.FLOAT:
     tbDom.append(rs.getFloat(i));
     break;
    case Types.BIGINT:
     tbDom.append(rs.getLong(i));
    default:
     tbDom.append(rs.getString(i));
    }
    tbDom.append("</" + columnName + ">").append(enter());
   }
   tbDom.append(blank(blank1));
   tbDom.append("</" + tableName + ">").append(enter());
  }
  System.out.println(tbDom.toString());
  rs.close();
  con.close();// 释放连接
  return tbDom.toString();
 }

 /*
  * 功能：返回指定长度的空格字符串
  *
  * param length 指定长度
  */
 public static String blank(int length) {
  StringBuffer sb = new StringBuffer();
  for (int i = 0; i < length; i++) {
   sb.append(" ");
  }
  return sb.toString();
 }

 /*
  * 功能：返回回车换行符号
  */
 public static String enter() {
  StringBuffer sb = new StringBuffer();
  sb.append("\r\n");
  return sb.toString();
 }

 /*
  * 功能：给字符串数据加上单引号
  *
  * 处理 varchar 类型数据
  */
 public static String singleQuote(String str) {
  if (str == null) {
   return null;
  } else if (str.equals("")) {
   return "''";
  } else {
   return "'" + str + "'";
  }
 }

 /*
  * 处理 datetime 类型数据
  */
 public static String dealDate(Date dt) {
  if (dt == null) {
   return null;
  } else {
   return "'" + dt + "'";
  }
 }

 public static void init() {
  if (ds != null) {
   try {
    ds.close();
   } catch (Exception e) {
    //
   }
   ds = null;
  }
  try {
   Properties p = new Properties();
   // �? .连接my sql 数据�?
   p.setProperty("driverClassName", "com.mysql.jdbc.Driver");
   p
     .setProperty(
       "url",
       "jdbc:mysql://localhost:3306/NetDetective?useUnicode=true&characterEncoding=UTF-8");
   p.setProperty("username", "xxxxxx");
   p.setProperty("password", "xxxxxx");
   p.setProperty("initialSize", "1");
   p.setProperty("minIdle", "1");
   p.setProperty("maxActive", "50");
   ds = (BasicDataSource) BasicDataSourceFactory.createDataSource(p);
  } catch (Exception e) {
   System.out.println(e);
  }
 }

 public static synchronized Connection getConnection() throws SQLException {
  Connection conn = null;
  if (ds != null) {
   conn = ds.getConnection();// 获得连接
  }
  return conn;
 }
}