需求
以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())) {// VARBINARY
sql.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迁库过程存在的不足而开发的。
1754
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
