异库批处理建表小工具

需求

以SQLServer为数据源，MySQL为目标数据库，通过读取文件中的表名，在目标库创建表。

代码

其他不多描述，直接贴代码：

JDBC代码

一：JDBC连接的常量：

public class Constants {public static final String SQL_JDBC_DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";public static final String SQL_JDBC_URL = "jdbc:sqlserver://localhost:1433;DatabaseName=CMSDB";public static final String SQL_JDBC_USERNAME = "sa";public static final String SQL_JDBC_PASSWORD = "sa";public static final String MYSQL_JDBC_DRIVER = "com.mysql.jdbc.Driver";public static final String MYSQL_JDBC_URL = "jdbc:mysql://localhost:3306/cmsdb_test";public static final String MYSQL_JDBC_USERNAME = "root";public static final String MYSQL_JDBC_PASSWORD = "mysqladmin";}

二：MySQL连接：

import java.sql.Connection;
import java.sql.DriverManager;/*** MySQL的JDBC连接（在记录表数据库为空的操作中正式使用）.* */
public class MySQLConnection {public static MySQLConnection instance = null;public static Connection conn = null;private MySQLConnection(){}public static MySQLConnection getInstance(){if(instance == null){instance = new MySQLConnection();}return instance;}public static Connection getConnection() {try {Class.forName(Constants.MYSQL_JDBC_DRIVER);String url = Constants.MYSQL_JDBC_URL;String user = Constants.MYSQL_JDBC_USERNAME;String password = Constants.MYSQL_JDBC_PASSWORD;conn = DriverManager.getConnection(url, user, password);} catch (Exception e) {e.printStackTrace();}return conn;}}

三：SQLServer连接：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;/*** SQLServer的JDBC连接（在记录表数据库为空的操作中正式使用）.* */
public class SQLServerConnection {public static SQLServerConnection instance = null;public static Connection conn = null;private SQLServerConnection() {}public static SQLServerConnection getInstance() {if (instance == null) {instance = new SQLServerConnection();}return instance;}public static Connection getConnection() {try {Class.forName(Constants.SQL_JDBC_DRIVER);String url = Constants.SQL_JDBC_URL;String user = Constants.SQL_JDBC_USERNAME;String password = Constants.SQL_JDBC_PASSWORD;conn = DriverManager.getConnection(url, user, password);} catch (Exception e) {e.printStackTrace();}return conn;}//	public static void main(String[] args) throws Exception {
//		PreparedStatement stmt;
//		SQLServerConnection con = new SQLServerConnection();
//		Connection conn = con.getConnection();
//
//		ResultSet rst = conn.getMetaData().getPrimaryKeys(null, null,
//				"sms_send_info");
//
//		// 主键字段名称
//		String pkName = null;
//
//		while (rst.next()) {
//			pkName = rst.getString("COLUMN_NAME");
//			System.out.println("主键:" + pkName);
//		}
//
//		String sql = "select * from app_version_info";
//		stmt = conn.prepareStatement(sql);
//		ResultSet rs = stmt.executeQuery();
//		ResultSetMetaData data = rs.getMetaData();
//
//		for (int i = 1; i < data.getColumnCount(); i++) {
//			// 获得指定列的列名
//			String columnName = data.getColumnName(i);
//			// if (pkName != null && pkName.equalsIgnoreCase(columnName)) {
//			// 获得指定列的数据类型名
//			String columnTypeName = data.getColumnTypeName(i);
//			System.out.println("主键字段名：" + columnName + "，类型：" + columnTypeName);
//			// }
//		}
//
//	}}

实体代码

一：建表的DDL的entity：

import java.util.ArrayList;
import java.util.List;public class TableDDL {/*** 表名.*/private String tableName;/*** 主键名称(可能存在复合主键).*/private List<String> pkNames;/*** 主键类型.*/private String pkType;/*** 字段信息.*/private List<FieldParams> fieldParams = new ArrayList<FieldParams>();public String getTableName() {return tableName;}public void setTableName(String tableName) {this.tableName = tableName;}public List<FieldParams> getFieldParams() {return fieldParams;}public void setFieldParams(List<FieldParams> fieldParams) {this.fieldParams = fieldParams;}public List<String> getPkNames() {return pkNames;}public void setPkNames(List<String> pkNames) {this.pkNames = pkNames;}public String getPkType() {return pkType;}public void setPkType(String pkType) {this.pkType = pkType;}}

二：字段属性entity:

/*** 字段属性实体.* * @author yangwenxue**/
public class FieldParams {private String fieldName;private String fieldType;private int fieldLength;public String getFieldName() {return fieldName;}public void setFieldName(String fieldName) {this.fieldName = fieldName;}public String getFieldType() {return fieldType;}public void setFieldType(String fieldType) {this.fieldType = fieldType;}public int getFieldLength() {return fieldLength;}public void setFieldLength(int fieldLength) {this.fieldLength = fieldLength;}}

工具类及其他

一：DB操作的工具类：

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;//import org.pentaho.di.core.util.StringUtil;import com.db.connection.MySQLConnection;
import com.db.connection.SQLServerConnection;
import com.entity.FieldParams;
import com.entity.TableDDL;/*** 创建表的操作类.* * @author yangwenxue**/
public class DbOperator {private static final String CL = "(";private static final String CR = ")";private static final String COMMA = ",";private static final String BLANK = " ";private static final String PK_LAST_SETING = "PRIMARY KEY";private static final String PK_SETING = "NOT NULL PRIMARY KEY";private static final String PK_AOTO_INCREMENT = "NOT NULL AUTO_INCREMENT";/********* 字段类型常量 **********/private static final String PK_INT = "int";private static final String PK_BIGINT = "bigint";private static final String FIELD_TYPE_BLOB = "blob";private static final String FIELD_TYPE_VARBINARY = "VARBINARY";private static final String FIELD_TYPE_IMAGE = "image";private static final String FIELD_TYPE_VARCHAR = "VARCHAR";private static final String FIELD_TYPE_NVARCHAR = "nvarchar";private static final String FIELD_TYPE_SMALLDATETIME = "smalldatetime";private static final String FIELD_TYPE_DATETIME = "datetime";/*** 获取sqlserver的数据库连接.*/private Connection conn = SQLServerConnection.getInstance().getConnection();/*** 准备数据.* * @param tableName* @throws SQLException*/private TableDDL prepareCreasteTableData(String tableName)throws SQLException {PreparedStatement stmt;ResultSet rst = conn.getMetaData().getPrimaryKeys(null, null, tableName);TableDDL tableDDL = new TableDDL();// 主键字段名称String pkName = null;List<String> pkNames = new ArrayList<String>();while (rst.next()) {pkName = rst.getString("COLUMN_NAME");pkNames.add(pkName);// 设置主键}tableDDL.setPkNames(pkNames);String sql = "select * from " + tableName;stmt = conn.prepareStatement(sql);ResultSet rs = stmt.executeQuery();ResultSetMetaData data = rs.getMetaData();for (int i = 1; i <= data.getColumnCount(); i++) {FieldParams fieldParams = new FieldParams();// 获得指定列的列名String columnName = data.getColumnName(i);// 获得指定列的数据类型名String columnTypeName = data.getColumnTypeName(i);// 字段长度int columnSize = data.getPrecision(i);fieldParams.setFieldName(columnName);if ("uniqueidentifier".equalsIgnoreCase(columnTypeName)) {fieldParams.setFieldType(FIELD_TYPE_VARCHAR);} else {fieldParams.setFieldType(columnTypeName);}fieldParams.setFieldLength(columnSize);tableDDL.getFieldParams().add(fieldParams);System.out.println("主键字段名：" + columnName + "，类型：" + columnTypeName+ "，长度：" + columnSize);}return tableDDL;}/*** 获取建表语句.* * @param tableName* @param tableDDL* @return* @throws SQLException*/private String getCreateTableDDL(String tableName) throws SQLException {TableDDL tableDDL = prepareCreasteTableData(tableName);// 主键名称List<String> pkNames = tableDDL.getPkNames();// 如果是复合主键的if (pkNames.size() > 1) {return getMultiplePkCreateTableSQL(tableName, tableDDL);} else {return getSinglePkCreateTableSQL(tableName, tableDDL);}}/*** 获取复合主键的建表SQL.* * @param tableName* @param tableDDL* @return*/private String getMultiplePkCreateTableSQL(String tableName,TableDDL tableDDL) {StringBuffer sql = new StringBuffer("create table " + tableName);sql.append(CL);List<FieldParams> fileList = tableDDL.getFieldParams();for (FieldParams field : fileList) {sql.append(field.getFieldName());sql.append(BLANK);if (FIELD_TYPE_IMAGE.equalsIgnoreCase(field.getFieldType())) {sql.append(FIELD_TYPE_BLOB);} else if(FIELD_TYPE_SMALLDATETIME.equalsIgnoreCase(field.getFieldType())){sql.append(FIELD_TYPE_DATETIME);} else {sql.append(field.getFieldType());if (FIELD_TYPE_VARCHAR.equalsIgnoreCase(field.getFieldType())|| FIELD_TYPE_NVARCHAR.equalsIgnoreCase(field.getFieldType())|| FIELD_TYPE_VARBINARY.equalsIgnoreCase(field.getFieldType())) {sql.append(CL);sql.append(field.getFieldLength());sql.append(CR);}}sql.append(COMMA);}// 设置复合主键sql.append(PK_LAST_SETING);sql.append(CL);for (String pk : tableDDL.getPkNames()) {sql.append(pk);sql.append(COMMA);}sql.deleteCharAt(sql.length() - 1);sql.append(CR);sql.append(CR);return sql.toString();}/*** 获取只有一个主键的建表SQL.* * @param tableName* @param tableDDL* @return*/public String getSinglePkCreateTableSQL(String tableName, TableDDL tableDDL) {StringBuffer sql = new StringBuffer("create table " + tableName);sql.append(CL);List<FieldParams> fileList = tableDDL.getFieldParams();for (FieldParams field : fileList) {sql.append(field.getFieldName());sql.append(BLANK);if (FIELD_TYPE_IMAGE.equalsIgnoreCase(field.getFieldType())) {sql.append(FIELD_TYPE_BLOB);} else if(FIELD_TYPE_SMALLDATETIME.equalsIgnoreCase(field.getFieldType())) {sql.append(FIELD_TYPE_DATETIME);} else {sql.append(field.getFieldType());if (FIELD_TYPE_VARCHAR.equalsIgnoreCase(field.getFieldType())|| FIELD_TYPE_NVARCHAR.equalsIgnoreCase(field.getFieldType())|| FIELD_TYPE_VARBINARY.equalsIgnoreCase(field.getFieldType())) {sql.append(CL);sql.append(field.getFieldLength());sql.append(CR);}}// 如果该字段是主键if (tableDDL.getPkNames().size() != 0&& field.getFieldName().equalsIgnoreCase(tableDDL.getPkNames().get(0))) {// 如果主键是自增长的if (PK_INT.equalsIgnoreCase(field.getFieldType())|| PK_BIGINT.equalsIgnoreCase(field.getFieldType())) {return autoIncrement(tableName, tableDDL);} else {sql.append(BLANK);// 设置主键sql.append(PK_SETING);}}sql.append(COMMA);}sql.deleteCharAt(sql.length() - 1);sql.append(CR);return sql.toString();}/*** 存在自增长主键的建表方法.* * @param tableName* @param tableDDL* @return*/private String autoIncrement(String tableName, TableDDL tableDDL) {StringBuffer sql = new StringBuffer("create table " + tableName);sql.append(CL);List<FieldParams> fileList = tableDDL.getFieldParams();for (FieldParams field : fileList) {sql.append(field.getFieldName());sql.append(BLANK);if (FIELD_TYPE_IMAGE.equalsIgnoreCase(field.getFieldType())) {sql.append(FIELD_TYPE_BLOB);} else if(FIELD_TYPE_SMALLDATETIME.equalsIgnoreCase(field.getFieldType())){sql.append(FIELD_TYPE_DATETIME);} else {sql.append(field.getFieldType());if (FIELD_TYPE_VARCHAR.equalsIgnoreCase(field.getFieldType())|| FIELD_TYPE_NVARCHAR.equalsIgnoreCase(field.getFieldType())|| FIELD_TYPE_VARBINARY.equalsIgnoreCase(field.getFieldType())) {// VARBINARYsql.append(CL);sql.append(field.getFieldLength());sql.append(CR);}}// 设置自增长主键if (field.getFieldName().equalsIgnoreCase(tableDDL.getPkNames().get(0))) {sql.append(BLANK);sql.append(PK_AOTO_INCREMENT);}sql.append(COMMA);}sql.append(PK_LAST_SETING);sql.append(CL);sql.append(tableDDL.getPkNames().get(0));sql.append(CR);sql.append(CR);return sql.toString();}/*** 执行建表语句.* * @param sql* @throws SQLException*/public void excuteCreateSql(String sql) {PreparedStatement stmt = null;try {Connection conn = MySQLConnection.getInstance().getConnection();stmt = conn.prepareStatement(sql);boolean flag = stmt.execute();if (!flag) {System.out.println("表创建成功，执行的SQL为：" + sql);} else {System.out.println("表创建失败，执行的SQL为：" + sql);}} catch (Exception e) {e.printStackTrace();} finally {if (stmt != null) {try {stmt.close();} catch (SQLException e) {e.printStackTrace();}}}}/*** 读取文件并创建表.* * @param filePath*/public void readFiletoCreateTable(String filePath) {BufferedReader br = null;String tableName = "";String line = "";try {br = new BufferedReader(new FileReader(filePath));while ((line = br.readLine()) != null) {tableName = line;// 获取建表语句
//				if(!StringUtil.isEmpty(tableName)){if(tableName != null && !"".equals(tableName)){String createTableSql = getCreateTableDDL(tableName);// 执行建表语句excuteCreateSql(createTableSql);}}} catch (Exception e) {e.printStackTrace();}}public static void main(String[] args) {
//		DbOperator dbOperator = new DbOperator();
//		String filePath = "F:\\123.txt";// String filePath = Spoon.FILE_PATH_NULL_DATA;
//		dbOperator.readFiletoCreateTable(filePath);// String str = dbOperator.getPkName("xj_testdata_location");// System.out.println("主键："+str);// try {// String str = dbOperator.getCreateTableDDL("app_version_info");// System.out.println("****************" + str);// } catch (SQLException e) {// e.printStackTrace();// }}
}

二：主函数

/*** 批量创建空表程序.* @author yangwenxue**/
public class BatchCreateTable {public static void main(String[] args) {DbOperator dbOperator = new DbOperator();
//		String filePath = "F:\\123.txt";
//		String filePath = args[0];String filePath = "f:\\nullDataOfTable.txt";dbOperator.readFiletoCreateTable(filePath);}}

工程结构如下图：

                                   

将以上代码打成jar包，并准备SQLServer和MySQL的jdbc驱动jar，将他们放在一起，如下：

                                  

编写bat文件为：

java -cp create-table.jar;sqljdbc4.jar;mysql-connector-java-5.1.23-bin.jar test.BatchCreateTable
pause; 

并将bat文件和jar包放在一个文件夹下：

                 

测试

运行CreateTable.bat文件，程序会读取我们指定路径下的文件（可以做出配置的，目前为粗糙版，后续升级），文件内容如下：

运行CreateTable.bat文件执行批量建表：

再看看目标库，已经创建好的表如下：

                                       

备注：

1、此工具目前有点粗糙，后续有待优化，后续也可以将数据抽取功能加上，让使用者更方便。

2、以上工具的出现是由于kettle迁库过程存在的不足而开发的。