最近做了一个帐套管理的一个功能,需要进行帐套初始化,在初始化的过程中,需要新建一个数据库,同时把上一个帐套的数据库里面的表的表结构全部拿出,在新数据库中全部新建,同时,有些信息在前一个帐套中是需要保留的,如一些基本信息和账目信息,这个时候就会出现表数据的拷贝和更新操作了。逻辑很简单,就是先获取到这些建数据库和建表的语句,以及更新和插入的语句,然后再按照一定的逻辑有顺序的执行它们。在做了这一系列的操作之后就是更改配置文件了,那是属于xml的操作,就不赘述了!
以下代码中用到的db是一个对Spring封装的jdbc的封装
主逻辑
List<String> tableNames = this.getTableNames(db);
List<String> sqls = this.getCreateTableSql(db, dataBaseName, tableNames);
String createDdSql = "create database "+dataBaseName;
sqls.add(0, createDdSql);
sqls.add(0, "set character_set_results=gbk;");
sqls.add(0, "set character_set_connection=gbk;");
sqls.add(0, "set character_set_client=gbk;");
List<String> insertTableNames = getInsertTableNames();
List<String> insertSqls = this.getInsertSql(dataBaseName, insertTableNames, db);
List<String> updateSqls = this.getUpdateSql(dataBaseName, db, cover);
sqls.addAll(insertSqls);
sqls.addAll(updateSqls);
this.executeSql(db, sqls);
/**
* 判断数据库是否存在,如果已经存在,就表示该帐套已经初始化了,需要给用户一个提示
* @param db
* @param dbName 数据库名
* @return
*/
public static boolean existDatabase(DB db, String dbName) {
boolean flag = false;
String sql = "show databases";
SqlRowSet srs = db.executeQuery(sql);
while (srs.next()) {
String databaseName = srs.getString(1);
if (databaseName.equals(dbName)) {
flag = true;
break;
}
}
return flag;
}
/**
* 获取当前数据库的名字,有了当前数据库的名字可以更精确的从当前数据库中拿数据
* @param tableName
* @param db
* @return
*/
public static String getDatabaseName(String tableName, DB db) {
String sql = "select * from "+tableName+" limit 0,1";
SqlRowSet srs = db.executeQuery(sql);
return srs.getMetaData().getCatalogName(1);
}
/**
* 获得所有的表名
* @param db
* @return
*/
private List<String> getTableNames(DB db) {
List<String> tableNames = new ArrayList<String>();
String sql = "show tables";
SqlRowSet srs = db.executeQuery(sql);
while (srs.next())
tableNames.add(srs.getString(1));
return tableNames;
}
/**
* 获取所有的需要插入数据的表
*
* @return
*/
private List<String> getInsertTableNames() {
List<String> tableNames = new ArrayList<String>();
String tables = "t1,t2,t3";
String[] tnames = tables.split(",");
for (String tableName:tnames)
tableNames.add(tableName);
return tableNames;
}
/**
* 获取建表语句
* @param dbName新建数据库的名称
* @param tableName 表的名字
* @param db
* @return
*/
public static String createTable(String dbName,String tableName,DB db) {
StringBuffer buffer = new StringBuffer("create table ").append(dbName+"."+tableName).append("(");
String sql = "select * from " + tableName + " limit 0,1";
SqlRowSet srs = db.executeQuery(sql);
SqlRowSetMetaData metaData = srs.getMetaData();
for (int i=0;i<metaData.getColumnCount();i++) {
String columnName = metaData.getColumnName((i+1));//字段名
String columnType = metaData.getColumnTypeName((i+1));//字段类型
int displaySize = metaData.getColumnDisplaySize((i+1));//长度
int precision = metaData.getPrecision((i+1));//整数位
int scale = metaData.getScale((i+1));//小数位
if (displaySize > 300)
columnType = "text";
if (columnName.equals("s_id")) {
buffer.append("s_id int primary key,");
} else if (scale > 0 && "decimal".equalsIgnoreCase(columnType)) {
buffer.append(columnName+" "+columnType+"("+precision+","+scale+"),");
} else if ("double".equalsIgnoreCase(columnType) || columnType.toLowerCase().startsWith("date") || columnType.toLowerCase().startsWith("int")) {
buffer.append(columnName+" "+columnType+",");
} else {
buffer.append(columnName+" "+columnType+"("+displaySize+"),");
}
// System.out.println("columnName:"+columnName+", columnType:"+columnType);
}
buffer.deleteCharAt(buffer.length()-1);
buffer.append(");");
return buffer.toString();
}
/**
* 通过表名获取一系列的建表语句
* @param db
* @param tableNames
* @return
*/
private List<String> getCreateTableSql(DB db, String dbName, List<String> tableNames) {
List<String> createSqls = new ArrayList<String>();
for (String tableName:tableNames) {
createSqls.add(Util.createTable(dbName, tableName, db));
}
return createSqls;
}
/**
* 获取复制数据的语句
* @param tableNames
* @param db
* @return
*/
private List<String> getInsertSql(String databaseName,List<String> tableNames, DB db) {
List<String> sqls = new ArrayList<String>();
String dbName = Util.getDatabaseName("t_area", db);
sqls.add("insert into "+databaseName+".t_auto_increment select * from "+dbName+".t_auto_increment;");
// sqls.add("update t_auto_increment set current_value = 1;");
for (String tableName:tableNames) {
String tempName = dbName+"."+tableName;
sqls.add("insert into "+databaseName+"."+tableName+" select * from "+tempName+";");
}
for (String sql:sqls)
System.out.println(sql);
return sqls;
}
/**
* 执行SQL语句
* @param db
* @param sqls
*/
private void executeSql(DB db, List<String> sqls) {
for (String sql:sqls) {
db.executeUpdate(sql);
}
}