java开发_mysql中获取数据库表描述_源码下载

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;

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='测试表';

  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 }

  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 }

  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 }

  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 }