功能描述：
在mysql数据库中，有两张表：
data_element_config , test_table
我们需要获取表：test_table表的描述信息，然后把描述信息插入到表：data_element_config中记录结果
项目结构：
运行效果：
控制台输出效果：
数据库表：data_element_config情况
================================================
代码部分：
================================================
data_element_config表情况：
CREATE TABLE `data_element_config` (
`de_name` varchar(75) NOT NULL,
`de_group` varchar(15) NOT NULL,
`memo` varchar(300) NOT NULL,
`data_type` int(11) NOT NULL,
`value_check` varchar(10) NOT NULL,
`yx_bj` char(1) NOT NULL,
PRIMARY KEY (`de_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
test_table表情况：
CREATE TABLE `test_table` (
`Test_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键(自增长)',
`Test_Key` varchar(10) COLLATE utf8_bin NOT NULL COMMENT '种类',
`Test_Value` varchar(20) COLLATE utf8_bin NOT NULL COMMENT '数值',
`Test_Type` int(11) NOT NULL COMMENT '内部类型',
`Test_BelongTo` int(11) DEFAULT NULL COMMENT '从属关系',
`Test_Grade` int(11) DEFAULT '' COMMENT '等级',
`Test_Remark` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
`Test_Visible` bit(1) DEFAULT b'' COMMENT '是否可见',
PRIMARY KEY (`Test_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='测试表';
/DataElementConfigTool/src/com/b510/data/element/config/tool/DataBaseBO.java
package com.b510.data.element.config.tool; import java.io.Serializable;
import java.util.List; /**
* 数据库配置信息
*
* @author Hongten
* @mail hongtenzone@foxmail.com
* @create 2013-8-3
*/
public class DataBaseBO implements Serializable {
private static final long serialVersionUID = 171777003280248377L;
private final String SELECT_SQL_FIELD = " column_name as field,";
private final String SELECT_SQL_TYPE = " data_type as type,";
private final String SELECT_SQL_MEMO = " column_comment as memo,";
private final String SELECT_SQL_MUNERIC_LENGTH = " numeric_precision as munericLength,";
private final String SELECT_SQL_NUMERIC_SCALE = " numeric_scale as numericScale, ";
private final String SELECT_SQL_ISNULLABLE = " is_nullable as isNullable,";
private final String SELECT_SQL_EXTRA = " CASE WHEN extra = 'auto_increment' THEN 1 ELSE 0 END as extra,";
private final String SELECT_SQL_ISDEFAULT = " column_default as isDefault,";
private final String SELECT_SQL_CHARACTER_LENGTH = " character_maximum_length AS characterLength ";
/**
* 查询表结构sql
*/
private String selectSQL = "SELECT " + SELECT_SQL_FIELD + SELECT_SQL_TYPE + SELECT_SQL_MEMO + SELECT_SQL_MUNERIC_LENGTH + SELECT_SQL_NUMERIC_SCALE + SELECT_SQL_ISNULLABLE + SELECT_SQL_EXTRA + SELECT_SQL_ISDEFAULT + SELECT_SQL_CHARACTER_LENGTH + " FROM Information_schema.columns WHERE table_Name = ";
/**
* 驱动名称
*/
private String driver;
/**
* 数据库名称
*/
private String dbName;
/**
* 数据库密码
*/
private String passwrod;
/**
* 数据库用户名
*/
private String userName;
/**
* 访问数据库的url
*/
private String url;
/**
* 端口号
*/
private String port;
/**
* ip地址
*/
private String ip;
/**
* 数据类型：mysql， oracle等等
*/
private String dbType; /**
* 根据sql:show tables;查询出的数据库表名称
*/
private List<String> tables;
/**
* 数据库表名称
*/
private String tableName;
/**
* sql语句
*/
private String sql; public String getDriver() {
return driver;
} public void setDriver(String driver) {
this.driver = driver;
} public String getDbName() {
return dbName;
} public void setDbName(String dbName) {
this.dbName = dbName;
} public String getPasswrod() {
return passwrod;
} public void setPasswrod(String passwrod) {
this.passwrod = passwrod;
} public String getUserName() {
return userName;
} public void setUserName(String userName) {
this.userName = userName;
} public String getUrl() {
return url;
} public void setUrl(String url) {
this.url = url;
} public String getSql() {
return sql;
} public void setSql(String sql) {
this.sql = sql;
} public String getPort() {
return port;
} public void setPort(String port) {
this.port = port;
} public String getIp() {
return ip;
} public void setIp(String ip) {
this.ip = ip;
} public String getDbType() {
return dbType;
} public void setDbType(String dbType) {
this.dbType = dbType;
} public static long getSerialversionuid() {
return serialVersionUID;
} public List<String> getTables() {
return tables;
} public void setTables(List<String> tables) {
this.tables = tables;
} public String getTableName() {
return tableName;
} public void setTableName(String tableName) {
this.tableName = tableName;
} public String getSelectSQL() {
return selectSQL;
} public void setSelectSQL(String selectSQL) {
this.selectSQL = selectSQL;
} }
/DataElementConfigTool/src/com/b510/data/element/config/tool/DataElementConfigBO.java
/**
*
*/
package com.b510.data.element.config.tool; import java.io.Serializable; /**
* data_element_config这张表的BO类
*
* @author Hongten
* @mail hongtenzone@foxmail.com
* @create 2013-8-3
*/
public class DataElementConfigBO implements Serializable {
private static final long serialVersionUID = -5951470192914621265L;
/**
* 数据库表的字段名称：TableDescBO - field
*/
private String deName;
/**
* 数据库表的分组，这里主要是在却别不同的字段名称<br>
* 如：有同一个字段名为<code>name</code>,那么在生成DE的过程中系统不知道<br>
* 是哪一个组或者哪一个用例的<code>name</code>字段，，如果一个字段是<code>TEST</code><br>
* 一个字段是<code>DEMO</code>的，那么在生成DE的时候，就很容易区分了<br>
* 则分别生成的DE是：<code>DE_TEST_NAME</code>和<code>DE_DEMO_NAME</code><br>
*/
private String deGroup;
/**
* 数据库表字段的描述
*/
private String memo;
/**
* 数据库表字段对应的数据类型
*/
private int dataType;
/**
* 该属性默认为：<code>true</code>,不用去修改
*/
private String valueCheck;
/**
* 有效标记，这里统一设置为:<code>1</code>,表示有效的<br>
* 如果设置为：<code>0</code>,则在生成DE的时候，该类会被标记为：<code>@Deprecated</code>
*/
private String yxBj;
/**
* 插入数据库表：<code>data_element_config</code>的sql语句
*/
private String insertIntoSQL = "INSERT INTO DATA_ELEMENT_CONFIG(DE_NAME,DE_GROUP,MEMO,DATA_TYPE,VALUE_CHECK,YX_BJ) VALUES ("; public String getDeName() {
return deName;
} public void setDeName(String deName) {
this.deName = deName;
} public String getDeGroup() {
return deGroup;
} public void setDeGroup(String deGroup) {
this.deGroup = deGroup;
} public String getMemo() {
return memo;
} public void setMemo(String memo) {
this.memo = memo;
} public int getDataType() {
return dataType;
} public void setDataType(int dataType) {
this.dataType = dataType;
} public String getValueCheck() {
return valueCheck;
} public void setValueCheck(String valueCheck) {
this.valueCheck = valueCheck;
} public String getYxBj() {
return yxBj;
} public void setYxBj(String yxBj) {
this.yxBj = yxBj;
} public String getInsertIntoSQL() {
return insertIntoSQL;
} public void setInsertIntoSQL(String insertIntoSQL) {
this.insertIntoSQL = insertIntoSQL;
} }
/DataElementConfigTool/src/com/b510/data/element/config/tool/DataElementConfigTool.java
package com.b510.data.element.config.tool; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List; /**
* DE数据插入工具
*
* @author Hongten
* @mail hongtenzone@foxmail.com
* @create 2013-8-3
*/
public class DataElementConfigTool { public static void main(String[] args) {
// 设置数据库链接信息
DataBaseBO dataBaseBO = new DataBaseBO();
dataBaseBO.setDbName("sworddemo");
dataBaseBO.setDriver("com.mysql.jdbc.Driver");
dataBaseBO.setUserName("root");
dataBaseBO.setPasswrod("gzcss");
dataBaseBO.setTableName("'test_table'");
dataBaseBO.setSql(dataBaseBO.getSelectSQL() + dataBaseBO.getTableName());
System.out.println(dataBaseBO.getSql());
// 初始化数据库链接的相关信息
DataElementConfigTool tool = new DataElementConfigTool(dataBaseBO);
// 数据库表结构情况
List<TableDescBO> list = tool.getTableDescBOList(dataBaseBO);
System.out.println(" Field Type Null Key Default Extra memo");
if (list != null) {
for (TableDescBO bo : list) {
System.out.println(bo.toString());
// 对数据库表描述进行封装成DataElementConfigBO对象
DataElementConfigBO decBo = tool.getDataElementConfigBO(bo, "gnzy");
// 向数据库表：data_element_config中插入数据
int result = tool.insertIntoDECTable(dataBaseBO, decBo);
System.out.println("插入数据：" + (result == 1 ? "成功" : "失败"));
}
}
} /**
* 初始化数据库链接的相关信息
*
* @param dataBaseBO
* 数据库配置信息
*/
public DataElementConfigTool(DataBaseBO dataBaseBO) {
super();
dataBaseBO.setIp(dataBaseBO.getIp() == null ? "localhost" : dataBaseBO.getIp());
dataBaseBO.setPort(dataBaseBO.getPort() == null ? "3306" : dataBaseBO.getPort());
dataBaseBO.setUrl("jdbc:mysql://" + dataBaseBO.getIp() + ":" + dataBaseBO.getPort() + "/" + dataBaseBO.getDbName());
} /**
* 数据库表结构情况
*
* @param dataBaseBO
* 数据库配置信息
* @return 所需查询的数据表的字段信息
*/
public List<TableDescBO> getTableDescBOList(DataBaseBO dataBaseBO) {
List<TableDescBO> list = new ArrayList<TableDescBO>();
TableDescBO tableDescBO = null;
try {
Class.forName(dataBaseBO.getDriver());
Connection conn = DriverManager.getConnection(dataBaseBO.getUrl(), dataBaseBO.getUserName(), dataBaseBO.getPasswrod());
PreparedStatement ps = conn.prepareStatement(dataBaseBO.getSql());
ResultSet rs = ps.executeQuery();
while (rs.next()) {
tableDescBO = new TableDescBO();
tableDescBO.setField(rs.getString(1));
tableDescBO.setType(rs.getString(2));
tableDescBO.setMemo(rs.getString(3));
tableDescBO.setMunericLength(rs.getString(4));
tableDescBO.setNumericScale(rs.getString(5));
tableDescBO.setIsNullable(rs.getString(6));
tableDescBO.setExtra(rs.getString(7));
tableDescBO.setIsDefault(rs.getString(8));
tableDescBO.setCharacterLength(rs.getString(9));
list.add(tableDescBO);
}
close(rs, ps, conn);
} catch (Exception e) {
e.printStackTrace();
}
return list;
} /**
* 执行向数据库表:<code>data_element_config</code>中插入数据
*
* @param dataBaseBO
* 数据库配置信息
* @param decBo
* data_element_config这张表的BO类
* @return 返回:<code>-1</code>, 表示插入数据失败，否则成功
*/
public int insertIntoDECTable(DataBaseBO dataBaseBO, DataElementConfigBO decBo) {
int result = -1;
if (decBo != null) {
String sql = decBo.getInsertIntoSQL() + decBo.getDeName() + "," + decBo.getDeGroup() + "," + decBo.getMemo() + "," + decBo.getDataType() + "," + decBo.getValueCheck() + "," + decBo.getYxBj() + ")";
try {
Class.forName(dataBaseBO.getDriver());
Connection conn = DriverManager.getConnection(dataBaseBO.getUrl(), dataBaseBO.getUserName(), dataBaseBO.getPasswrod());
PreparedStatement ps = conn.prepareStatement(sql);
result = ps.executeUpdate();
close(null, ps, conn);
} catch (Exception e) {
e.printStackTrace();
}
}
return result;
} /**
* 去除括号，如："int(11)",去除括号了以后，为："int"
*
* @param oldType
* @return
*/
public static String getType(String oldType) {
if (oldType != null && !oldType.equals("")) {
return oldType.substring(0, oldType.indexOf("("));
}
return null;
} /**
* 对数据库表描述进行封装成DataElementConfigBO对象
*
* @param tableDescBO
* 数据库表的描述
* @param group
* 字段的分组名称，在表：<code>data_element_config</code>中对应的
* <code>de_group</code>字段
* @return dataElementConfig对象的一个实例
*/
public DataElementConfigBO getDataElementConfigBO(TableDescBO tableDescBO, String group) {
DataElementConfigBO bo = null;
if (tableDescBO != null) {
bo = new DataElementConfigBO();
bo.setDeName("'" + tableDescBO.getField() + "'");
bo.setDeGroup("'" + group + "'");
bo.setValueCheck("'true'");
bo.setYxBj("'1'");
bo.setMemo("'" + tableDescBO.getMemo() + "'");
bo.setDataType(1);
}
return bo;
} /**
* 关闭数据库的相关链接
*
* @param rs
* 记录集
* @param ps
* 声明
* @param conn
* 链接对象
*/
public void close(ResultSet rs, PreparedStatement ps, Connection conn) {
// 关闭记录集
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭声明
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭链接对象
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/DataElementConfigTool/src/com/b510/data/element/config/tool/TableDescBO.java
/**
*
*/
package com.b510.data.element.config.tool; import java.io.Serializable; /**
* 数据库表结构情况BO
*
* @author Hongten
* @mail hongtenzone@foxmail.com
* @create 2013-8-3
*/
public class TableDescBO implements Serializable {
private static final long serialVersionUID = 6450523501528806316L;
/**
* 数据库表中对应的字段名称
*/
private String field;
/**
* 数据库表中对应字段的类型
*/
private String type;
/**
* 数据库表中字段是否为空：YES/NO
*/
private String isNullable;
/**
* 是否为主键：KEY，不是，则为空，null
*/
private String key;
/**
* 字段的默认值
*/
private String isDefault;
/**
* 额外的属性，如：auto_increment
*/
private String extra;
/**
* 小数位数
*/
private String numericScale;
/**
* 数字长度
*/
private String munericLength; /**
* 字符长度
*/
private String characterLength;
/**
* 备注
*/
private String memo; /**
* 重写toStirng方法 主要是为了控制台输出
*/
public String toString() {
return " " + field + " " + type + " " + isNullable + " " + key + " " + isDefault + " " + extra + " "+ memo;
} public String getField() {
return field;
} public void setField(String field) {
this.field = field;
} public String getType() {
return type;
} public void setType(String type) {
this.type = type;
} public String getIsNullable() {
return isNullable;
} public void setIsNullable(String isNullable) {
this.isNullable = isNullable;
} public String getKey() {
return key;
} public void setKey(String key) {
this.key = key;
} public String getIsDefault() {
return isDefault;
} public void setIsDefault(String isDefault) {
this.isDefault = isDefault;
} public String getExtra() {
return extra;
} public void setExtra(String extra) {
this.extra = extra;
} public String getNumericScale() {
return numericScale;
} public void setNumericScale(String numericScale) {
this.numericScale = numericScale;
} public String getMunericLength() {
return munericLength;
} public void setMunericLength(String munericLength) {
this.munericLength = munericLength;
} public String getCharacterLength() {
return characterLength;
} public void setCharacterLength(String characterLength) {
this.characterLength = characterLength;
} public String getMemo() {
return memo;
} public void setMemo(String memo) {
this.memo = memo;
} }
项目源码：http://files.cnblogs.com/hongten/DataElementConfigTool.zip
jar包下载：http://files.cnblogs.com/hongten/DECTool_needParams.jar.zip