功能描述:
在mysql数据库中,有两张表:
data_element_config , test_table
我们需要获取表:test_table表的描述信息,然后把描述信息插入到表:data_element_config中记录结果
项目结构:
运行效果:
控制台输出效果:
数据库表:data_element_config情况
================================================
代码部分:
================================================
data_element_config表情况:
1 CREATE TABLE `data_element_config` (
2 `de_name` varchar(75) NOT NULL,
3 `de_group` varchar(15) NOT NULL,
4 `memo` varchar(300) NOT NULL,
5 `data_type` int(11) NOT NULL,
6 `value_check` varchar(10) NOT NULL,
7 `yx_bj` char(1) NOT NULL,
8 PRIMARY KEY (`de_name`)
9 ) 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 '1' COMMENT '等级',
`Test_Remark` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
`Test_Visible` bit(1) DEFAULT b'1' COMMENT '是否可见',
PRIMARY KEY (`Test_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='测试表';
/DataElementConfigTool/src/com/b510/data/element/config/tool/DataBaseBO.java
1 package com.b510.data.element.config.tool;
2
3 import java.io.Serializable;
4 import java.util.List;
5
6 /**
7 * 数据库配置信息
8 *
9 * @author Hongten
10 * @mail hongtenzone@foxmail.com
11 * @create 2013-8-3
12 */
13 public class DataBaseBO implements Serializable {
14 private static final long serialVersionUID = 171777003280248377L;
15 private final String SELECT_SQL_FIELD = " column_name as field,";
16 private final String SELECT_SQL_TYPE = " data_type as type,";
17 private final String SELECT_SQL_MEMO = " column_comment as memo,";
18 private final String SELECT_SQL_MUNERIC_LENGTH = " numeric_precision as munericLength,";
19 private final String SELECT_SQL_NUMERIC_SCALE = " numeric_scale as numericScale, ";
20 private final String SELECT_SQL_ISNULLABLE = " is_nullable as isNullable,";
21 private final String SELECT_SQL_EXTRA = " CASE WHEN extra = 'auto_increment' THEN 1 ELSE 0 END as extra,";
22 private final String SELECT_SQL_ISDEFAULT = " column_default as isDefault,";
23 private final String SELECT_SQL_CHARACTER_LENGTH = " character_maximum_length AS characterLength ";
24 /**
25 * 查询表结构sql
26 */
27 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 = ";
28 /**
29 * 驱动名称
30 */
31 private String driver;
32 /**
33 * 数据库名称
34 */
35 private String dbName;
36 /**
37 * 数据库密码
38 */
39 private String passwrod;
40 /**
41 * 数据库用户名
42 */
43 private String userName;
44 /**
45 * 访问数据库的url
46 */
47 private String url;
48 /**
49 * 端口号
50 */
51 private String port;
52 /**
53 * ip地址
54 */
55 private String ip;
56 /**
57 * 数据类型:mysql, oracle等等
58 */
59 private String dbType;
60
61 /**
62 * 根据sql:show tables;查询出的数据库表名称
63 */
64 private List<String> tables;
65 /**
66 * 数据库表名称
67 */
68 private String tableName;
69 /**
70 * sql语句
71 */
72 private String sql;
73
74 public String getDriver() {
75 return driver;
76 }
77
78 public void setDriver(String driver) {
79 this.driver = driver;
80 }
81
82 public String getDbName() {
83 return dbName;
84 }
85
86 public void setDbName(String dbName) {
87 this.dbName = dbName;
88 }
89
90 public String getPasswrod() {
91 return passwrod;
92 }
93
94 public void setPasswrod(String passwrod) {
95 this.passwrod = passwrod;
96 }
97
98 public String getUserName() {
99 return userName;
100 }
101
102 public void setUserName(String userName) {
103 this.userName = userName;
104 }
105
106 public String getUrl() {
107 return url;
108 }
109
110 public void setUrl(String url) {
111 this.url = url;
112 }
113
114 public String getSql() {
115 return sql;
116 }
117
118 public void setSql(String sql) {
119 this.sql = sql;
120 }
121
122 public String getPort() {
123 return port;
124 }
125
126 public void setPort(String port) {
127 this.port = port;
128 }
129
130 public String getIp() {
131 return ip;
132 }
133
134 public void setIp(String ip) {
135 this.ip = ip;
136 }
137
138 public String getDbType() {
139 return dbType;
140 }
141
142 public void setDbType(String dbType) {
143 this.dbType = dbType;
144 }
145
146 public static long getSerialversionuid() {
147 return serialVersionUID;
148 }
149
150 public List<String> getTables() {
151 return tables;
152 }
153
154 public void setTables(List<String> tables) {
155 this.tables = tables;
156 }
157
158 public String getTableName() {
159 return tableName;
160 }
161
162 public void setTableName(String tableName) {
163 this.tableName = tableName;
164 }
165
166 public String getSelectSQL() {
167 return selectSQL;
168 }
169
170 public void setSelectSQL(String selectSQL) {
171 this.selectSQL = selectSQL;
172 }
173
174 }
/DataElementConfigTool/src/com/b510/data/element/config/tool/DataElementConfigBO.java
1 /**
2 *
3 */
4 package com.b510.data.element.config.tool;
5
6 import java.io.Serializable;
7
8 /**
9 * data_element_config这张表的BO类
10 *
11 * @author Hongten
12 * @mail hongtenzone@foxmail.com
13 * @create 2013-8-3
14 */
15 public class DataElementConfigBO implements Serializable {
16 private static final long serialVersionUID = -5951470192914621265L;
17 /**
18 * 数据库表的字段名称:TableDescBO - field
19 */
20 private String deName;
21 /**
22 * 数据库表的分组,这里主要是在却别不同的字段名称<br>
23 * 如:有同一个字段名为<code>name</code>,那么在生成DE的过程中系统不知道<br>
24 * 是哪一个组或者哪一个用例的<code>name</code>字段,,如果一个字段是<code>TEST</code><br>
25 * 一个字段是<code>DEMO</code>的,那么在生成DE的时候,就很容易区分了<br>
26 * 则分别生成的DE是:<code>DE_TEST_NAME</code>和<code>DE_DEMO_NAME</code><br>
27 */
28 private String deGroup;
29 /**
30 * 数据库表字段的描述
31 */
32 private String memo;
33 /**
34 * 数据库表字段对应的数据类型
35 */
36 private int dataType;
37 /**
38 * 该属性默认为:<code>true</code>,不用去修改
39 */
40 private String valueCheck;
41 /**
42 * 有效标记,这里统一设置为:<code>1</code>,表示有效的<br>
43 * 如果设置为:<code>0</code>,则在生成DE的时候,该类会被标记为:<code>@Deprecated</code>
44 */
45 private String yxBj;
46 /**
47 * 插入数据库表:<code>data_element_config</code>的sql语句
48 */
49 private String insertIntoSQL = "INSERT INTO DATA_ELEMENT_CONFIG(DE_NAME,DE_GROUP,MEMO,DATA_TYPE,VALUE_CHECK,YX_BJ) VALUES (";
50
51 public String getDeName() {
52 return deName;
53 }
54
55 public void setDeName(String deName) {
56 this.deName = deName;
57 }
58
59 public String getDeGroup() {
60 return deGroup;
61 }
62
63 public void setDeGroup(String deGroup) {
64 this.deGroup = deGroup;
65 }
66
67 public String getMemo() {
68 return memo;
69 }
70
71 public void setMemo(String memo) {
72 this.memo = memo;
73 }
74
75 public int getDataType() {
76 return dataType;
77 }
78
79 public void setDataType(int dataType) {
80 this.dataType = dataType;
81 }
82
83 public String getValueCheck() {
84 return valueCheck;
85 }
86
87 public void setValueCheck(String valueCheck) {
88 this.valueCheck = valueCheck;
89 }
90
91 public String getYxBj() {
92 return yxBj;
93 }
94
95 public void setYxBj(String yxBj) {
96 this.yxBj = yxBj;
97 }
98
99 public String getInsertIntoSQL() {
100 return insertIntoSQL;
101 }
102
103 public void setInsertIntoSQL(String insertIntoSQL) {
104 this.insertIntoSQL = insertIntoSQL;
105 }
106
107 }
/DataElementConfigTool/src/com/b510/data/element/config/tool/DataElementConfigTool.java
1 package com.b510.data.element.config.tool;
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.PreparedStatement;
6 import java.sql.ResultSet;
7 import java.sql.SQLException;
8 import java.util.ArrayList;
9 import java.util.List;
10
11 /**
12 * DE数据插入工具
13 *
14 * @author Hongten
15 * @mail hongtenzone@foxmail.com
16 * @create 2013-8-3
17 */
18 public class DataElementConfigTool {
19
20 public static void main(String[] args) {
21 // 设置数据库链接信息
22 DataBaseBO dataBaseBO = new DataBaseBO();
23 dataBaseBO.setDbName("sworddemo");
24 dataBaseBO.setDriver("com.mysql.jdbc.Driver");
25 dataBaseBO.setUserName("root");
26 dataBaseBO.setPasswrod("gzcss");
27 dataBaseBO.setTableName("'test_table'");
28 dataBaseBO.setSql(dataBaseBO.getSelectSQL() + dataBaseBO.getTableName());
29 System.out.println(dataBaseBO.getSql());
30 // 初始化数据库链接的相关信息
31 DataElementConfigTool tool = new DataElementConfigTool(dataBaseBO);
32 // 数据库表结构情况
33 List<TableDescBO> list = tool.getTableDescBOList(dataBaseBO);
34 System.out.println(" Field Type Null Key Default Extra memo");
35 if (list != null) {
36 for (TableDescBO bo : list) {
37 System.out.println(bo.toString());
38 // 对数据库表描述进行封装成DataElementConfigBO对象
39 DataElementConfigBO decBo = tool.getDataElementConfigBO(bo, "gnzy");
40 // 向数据库表:data_element_config中插入数据
41 int result = tool.insertIntoDECTable(dataBaseBO, decBo);
42 System.out.println("插入数据:" + (result == 1 ? "成功" : "失败"));
43 }
44 }
45 }
46
47 /**
48 * 初始化数据库链接的相关信息
49 *
50 * @param dataBaseBO
51 * 数据库配置信息
52 */
53 public DataElementConfigTool(DataBaseBO dataBaseBO) {
54 super();
55 dataBaseBO.setIp(dataBaseBO.getIp() == null ? "localhost" : dataBaseBO.getIp());
56 dataBaseBO.setPort(dataBaseBO.getPort() == null ? "3306" : dataBaseBO.getPort());
57 dataBaseBO.setUrl("jdbc:mysql://" + dataBaseBO.getIp() + ":" + dataBaseBO.getPort() + "/" + dataBaseBO.getDbName());
58 }
59
60 /**
61 * 数据库表结构情况
62 *
63 * @param dataBaseBO
64 * 数据库配置信息
65 * @return 所需查询的数据表的字段信息
66 */
67 public List<TableDescBO> getTableDescBOList(DataBaseBO dataBaseBO) {
68 List<TableDescBO> list = new ArrayList<TableDescBO>();
69 TableDescBO tableDescBO = null;
70 try {
71 Class.forName(dataBaseBO.getDriver());
72 Connection conn = DriverManager.getConnection(dataBaseBO.getUrl(), dataBaseBO.getUserName(), dataBaseBO.getPasswrod());
73 PreparedStatement ps = conn.prepareStatement(dataBaseBO.getSql());
74 ResultSet rs = ps.executeQuery();
75 while (rs.next()) {
76 tableDescBO = new TableDescBO();
77 tableDescBO.setField(rs.getString(1));
78 tableDescBO.setType(rs.getString(2));
79 tableDescBO.setMemo(rs.getString(3));
80 tableDescBO.setMunericLength(rs.getString(4));
81 tableDescBO.setNumericScale(rs.getString(5));
82 tableDescBO.setIsNullable(rs.getString(6));
83 tableDescBO.setExtra(rs.getString(7));
84 tableDescBO.setIsDefault(rs.getString(8));
85 tableDescBO.setCharacterLength(rs.getString(9));
86 list.add(tableDescBO);
87 }
88 close(rs, ps, conn);
89 } catch (Exception e) {
90 e.printStackTrace();
91 }
92 return list;
93 }
94
95 /**
96 * 执行向数据库表:<code>data_element_config</code>中插入数据
97 *
98 * @param dataBaseBO
99 * 数据库配置信息
100 * @param decBo
101 * data_element_config这张表的BO类
102 * @return 返回:<code>-1</code>, 表示插入数据失败,否则成功
103 */
104 public int insertIntoDECTable(DataBaseBO dataBaseBO, DataElementConfigBO decBo) {
105 int result = -1;
106 if (decBo != null) {
107 String sql = decBo.getInsertIntoSQL() + decBo.getDeName() + "," + decBo.getDeGroup() + "," + decBo.getMemo() + "," + decBo.getDataType() + "," + decBo.getValueCheck() + "," + decBo.getYxBj() + ")";
108 try {
109 Class.forName(dataBaseBO.getDriver());
110 Connection conn = DriverManager.getConnection(dataBaseBO.getUrl(), dataBaseBO.getUserName(), dataBaseBO.getPasswrod());
111 PreparedStatement ps = conn.prepareStatement(sql);
112 result = ps.executeUpdate();
113 close(null, ps, conn);
114 } catch (Exception e) {
115 e.printStackTrace();
116 }
117 }
118 return result;
119 }
120
121 /**
122 * 去除括号,如:"int(11)",去除括号了以后,为:"int"
123 *
124 * @param oldType
125 * @return
126 */
127 public static String getType(String oldType) {
128 if (oldType != null && !oldType.equals("")) {
129 return oldType.substring(0, oldType.indexOf("("));
130 }
131 return null;
132 }
133
134 /**
135 * 对数据库表描述进行封装成DataElementConfigBO对象
136 *
137 * @param tableDescBO
138 * 数据库表的描述
139 * @param group
140 * 字段的分组名称,在表:<code>data_element_config</code>中对应的
141 * <code>de_group</code>字段
142 * @return dataElementConfig对象的一个实例
143 */
144 public DataElementConfigBO getDataElementConfigBO(TableDescBO tableDescBO, String group) {
145 DataElementConfigBO bo = null;
146 if (tableDescBO != null) {
147 bo = new DataElementConfigBO();
148 bo.setDeName("'" + tableDescBO.getField() + "'");
149 bo.setDeGroup("'" + group + "'");
150 bo.setValueCheck("'true'");
151 bo.setYxBj("'1'");
152 bo.setMemo("'" + tableDescBO.getMemo() + "'");
153 bo.setDataType(1);
154 }
155 return bo;
156 }
157
158 /**
159 * 关闭数据库的相关链接
160 *
161 * @param rs
162 * 记录集
163 * @param ps
164 * 声明
165 * @param conn
166 * 链接对象
167 */
168 public void close(ResultSet rs, PreparedStatement ps, Connection conn) {
169 // 关闭记录集
170 if (rs != null) {
171 try {
172 rs.close();
173 } catch (SQLException e) {
174 e.printStackTrace();
175 }
176 }
177 // 关闭声明
178 if (ps != null) {
179 try {
180 ps.close();
181 } catch (SQLException e) {
182 e.printStackTrace();
183 }
184 }
185 // 关闭链接对象
186 if (conn != null) {
187 try {
188 conn.close();
189 } catch (SQLException e) {
190 e.printStackTrace();
191 }
192 }
193 }
194 }
/DataElementConfigTool/src/com/b510/data/element/config/tool/TableDescBO.java
1 /**
2 *
3 */
4 package com.b510.data.element.config.tool;
5
6 import java.io.Serializable;
7
8 /**
9 * 数据库表结构情况BO
10 *
11 * @author Hongten
12 * @mail hongtenzone@foxmail.com
13 * @create 2013-8-3
14 */
15 public class TableDescBO implements Serializable {
16 private static final long serialVersionUID = 6450523501528806316L;
17 /**
18 * 数据库表中对应的字段名称
19 */
20 private String field;
21 /**
22 * 数据库表中对应字段的类型
23 */
24 private String type;
25 /**
26 * 数据库表中字段是否为空:YES/NO
27 */
28 private String isNullable;
29 /**
30 * 是否为主键:KEY,不是,则为空,null
31 */
32 private String key;
33 /**
34 * 字段的默认值
35 */
36 private String isDefault;
37 /**
38 * 额外的属性,如:auto_increment
39 */
40 private String extra;
41 /**
42 * 小数位数
43 */
44 private String numericScale;
45 /**
46 * 数字长度
47 */
48 private String munericLength;
49
50 /**
51 * 字符长度
52 */
53 private String characterLength;
54 /**
55 * 备注
56 */
57 private String memo;
58
59 /**
60 * 重写toStirng方法 主要是为了控制台输出
61 */
62 public String toString() {
63 return " " + field + " " + type + " " + isNullable + " " + key + " " + isDefault + " " + extra + " "+ memo;
64 }
65
66 public String getField() {
67 return field;
68 }
69
70 public void setField(String field) {
71 this.field = field;
72 }
73
74 public String getType() {
75 return type;
76 }
77
78 public void setType(String type) {
79 this.type = type;
80 }
81
82 public String getIsNullable() {
83 return isNullable;
84 }
85
86 public void setIsNullable(String isNullable) {
87 this.isNullable = isNullable;
88 }
89
90 public String getKey() {
91 return key;
92 }
93
94 public void setKey(String key) {
95 this.key = key;
96 }
97
98 public String getIsDefault() {
99 return isDefault;
100 }
101
102 public void setIsDefault(String isDefault) {
103 this.isDefault = isDefault;
104 }
105
106 public String getExtra() {
107 return extra;
108 }
109
110 public void setExtra(String extra) {
111 this.extra = extra;
112 }
113
114 public String getNumericScale() {
115 return numericScale;
116 }
117
118 public void setNumericScale(String numericScale) {
119 this.numericScale = numericScale;
120 }
121
122 public String getMunericLength() {
123 return munericLength;
124 }
125
126 public void setMunericLength(String munericLength) {
127 this.munericLength = munericLength;
128 }
129
130 public String getCharacterLength() {
131 return characterLength;
132 }
133
134 public void setCharacterLength(String characterLength) {
135 this.characterLength = characterLength;
136 }
137
138 public String getMemo() {
139 return memo;
140 }
141
142 public void setMemo(String memo) {
143 this.memo = memo;
144 }
145
146 }
项目源码:http://files.cnblogs.com/hongten/DataElementConfigTool.zip
jar包下载:http://files.cnblogs.com/hongten/DECTool_needParams.jar.zip