功能描述：

在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
     
       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
      
        getTables() {151         return tables;152     }153 154     public void setTables(List
       
         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      * 数据库表的分组，这里主要是在却别不同的字段名称
 23      * 如：有同一个字段名为name,那么在生成DE的过程中系统不知道
 24      * 是哪一个组或者哪一个用例的name字段，，如果一个字段是TEST
 25      * 一个字段是DEMO的，那么在生成DE的时候，就很容易区分了
 26      * 则分别生成的DE是：DE_TEST_NAME和DE_DEMO_NAME
 27      */ 28     private String deGroup; 29     /** 30      * 数据库表字段的描述 31      */ 32     private String memo; 33     /** 34      * 数据库表字段对应的数据类型 35      */ 36     private int dataType; 37     /** 38      * 该属性默认为：true,不用去修改 39      */ 40     private String valueCheck; 41     /** 42      * 有效标记，这里统一设置为:1,表示有效的
 43      * 如果设置为：0,则在生成DE的时候，该类会被标记为：@Deprecated 44      */ 45     private String yxBj; 46     /** 47      * 插入数据库表：data_element_config的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
     
       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
      
        getTableDescBOList(DataBaseBO dataBaseBO) { 68         List
       
         list = new ArrayList
        
         (); 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      * 执行向数据库表:
         data_element_config中插入数据 97      *  98      * @param dataBaseBO 99      *            数据库配置信息100      * @param decBo101      *            data_element_config这张表的BO类102      * @return 返回:
         -1, 表示插入数据失败，否则成功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 oldType125      * @return126      */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 tableDescBO138      *            数据库表的描述139      * @param group140      *            字段的分组名称，在表：
         data_element_config中对应的141      *            
         de_group字段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 rs162      *            记录集163      * @param ps164      *            声明165      * @param conn166      *            链接对象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 }

 项目源码：

jar包下载：