功能描述:
在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