摘要：
1.在注射期间初始化这两个模板。1/**2*获取表结构信息3*@paramtableName表名4*@return5*@throwsException6*Methodcreator：yanwei7*MethodcreatedateTime:2011-12-21 01:01:178 PM*Methodupdateauthor:9*MethodupdatedateTime:10*/11publicListgetDsTableColumnInfothrows DataAccessFailureException｛1213ResultSetresultSet＝null；14Connectionconnection＝null；15java.util.List＜DsClientColumnInfo＞clientTableInfos＝newArrayList＜DsClientColumnInfo＞（）；16try｛17connection=this.jdbcTemplate.getDataSource（）.getConnection（）；18//获取列信息19resultSet＝connection.getMetaData（）.Get-Columns；20while｛21//获取字段名称22Stringname=resultSet.getString；23//获取字段类型名称24Stringtype=resultSet.Get-String；25//获取字段大小26intsize=resultSet/getInt；27//获取字段备注28Stringremark=resultSet.getString；29DsClientColumnInfoinfo=newDsClientColumnInfo；30clientTableInfos.add；31}3233//获取主键信息34resultSet=connection。获取元数据（）。getPrimaryKeys；35while｛36StringprimaryKey=47resultSet.getString；37//设置它是否是｛39if（primaryKey！=null&&primaryKey.equals）40dsClientColumnInfo.setIsParmaryKey；41else42dsClientColumnInfo.setSparmaryKey；43｝44｝4546//获取外键信息47 resultSet=连接。获取元数据（）。获取导入密钥；48while｛49StringexportedKey=resultSet.getString；50//设置它是否是｛52if（exportedKey！=null&&exportedKey.equals）53dsClientColumnInfo.setIsImportedKey；54else55dsClientColumnInfo.setIsImportedKey；56｝57｝585960｝catch｛61e.printStackTrace（）；62thrownewRuntimeException（“无法获取字段信息，请向维护人员报告问题。=null）65try｛66resultSet.close（）；67｝catch｛68e.printStackTrace（）；69thrownewDataAccessFailureException（“未能关闭resultsSet.=null）72try{73connection.close）；74｝catch{75e.printStackTrace（））；76thrownewData AccessFailureException（”未能关闭连接。
1，在注入时初始化这两个模板。
/**
* 注入数据源， 该数据源在Spring配置文件中配置
* 在注入时初始化这两个模板
* @paramdataSource
* Method create author: yanwei
* Method create dateTime: 2011-11-2 下午03:43:13
* Method update author:
* Method update dateTime:
*/
@Resource
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
jdbcTemplate = new JdbcTemplate(dataSource);
simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
}
2，获取表结构信息。
1 /**2 * 获取表结构信息
3 * @paramtableName 表名
4 * @return5 * @throwsException
6 * Method create author: yanwei
7 * Method create dateTime: 2011-12-21 下午01:01:17
8 * Method update author:
9 * Method update dateTime:
10 */
11 public List<DsClientColumnInfo> getDsTableColumnInfo(String tableName) throws DataAccessFailureException{
12
13 ResultSet resultSet = null;
14 Connection connection = null;
15 java.util.List<DsClientColumnInfo> clientTableInfos = new ArrayList<DsClientColumnInfo>();
16 try {
17 connection = this.jdbcTemplate.getDataSource().getConnection();
18 //获得列的信息19 resultSet = connection.getMetaData().getColumns(null, null, tableName, null);
20 while (resultSet.next()) {
21 //获得字段名称22 String name = resultSet.getString("COLUMN_NAME");
23 //获得字段类型名称24 String type = resultSet.getString("TYPE_NAME");
25 //获得字段大小26 int size = resultSet.getInt("COLUMN_SIZE");
27 //获得字段备注28 String remark = resultSet.getString("REMARKS");
29 DsClientColumnInfo info = new DsClientColumnInfo(null, null, null, name, remark, size, type, "false");
30 clientTableInfos.add(info);
31 }
32
33 //获得主键的信息34 resultSet = connection.getMetaData().getPrimaryKeys(null, null, tableName);
35 while(resultSet.next()){
36 String primaryKey = resultSet.getString("COLUMN_NAME");
37 //设置是否为主键38 for (DsClientColumnInfo dsClientColumnInfo : clientTableInfos) {
39 if(primaryKey != null && primaryKey.equals(dsClientColumnInfo.getClientColumnCode()))
40 dsClientColumnInfo.setIsParmaryKey("true");
41 else
42 dsClientColumnInfo.setIsParmaryKey("false");
43 }
44 }
45
46 //获得外键信息47 resultSet = connection.getMetaData().getImportedKeys(null, null, tableName);
48 while(resultSet.next()){
49 String exportedKey = resultSet.getString("FKCOLUMN_NAME");
50 //设置是否是外键51 for (DsClientColumnInfo dsClientColumnInfo : clientTableInfos) {
52 if(exportedKey != null && exportedKey.equals(dsClientColumnInfo.getClientColumnCode()))
53 dsClientColumnInfo.setIsImportedKey("true");
54 else
55 dsClientColumnInfo.setIsImportedKey("false");
56 }
57 }
58
59
60 } catch (Exception e) {
61 e.printStackTrace();
62 throw new RuntimeException("获取字段信息的时候失败，请将问题反映到维护人员。" + e.getMessage(), e);
63 } finally{
64 if(resultSet != null)
65 try {
66 resultSet.close();
67 } catch (SQLException e) {
68 e.printStackTrace();
69 throw new DataAccessFailureException("关闭结果集resultSet失败。",e);
70 }finally{
71 if(connection != null)
72 try {
73 connection.close();
74 } catch (SQLException e) {
75 e.printStackTrace();
76 throw new DataAccessFailureException("关闭连接connection失败。",e);
77 }
78 }
79 }
80
81 Set set = new HashSet();
82 set.addAll(clientTableInfos);
83 clientTableInfos.clear();
84 clientTableInfos.addAll(set);
85 return clientTableInfos;
86 }
3，获得数据库中所有的表。
1 /**2 * 获得数据库中所有的表
3 * @return4 * Method create author: yanwei
5 * Method create dateTime: 2012-1-5 上午11:23:54
6 * Method update author:
7 * Method update dateTime:
8 * @throwsSQLException
9 */
10 public Map<String, String> getDatabaseTables() throws DataAccessFailureException{
11 ResultSet resultSet = null;
12 Connection connection = null;
13 Map<String, String> map = new HashMap<String, String>();
14 try {
15 String[] types = {"TABLE"};
16 connection = this.jdbcTemplate.getDataSource().getConnection();
17 String databaseName = SynXmlAnalysis.getElementValueByName(DATABASE_NAME);
18 resultSet = connection.getMetaData().getTables(null, databaseName, null, types);
19 while(resultSet.next()){
20 String tableName = resultSet.getString("TABLE_NAME");
21 String remark = resultSet.getString("REMARKS");
22 map.put(tableName, remark);
23 }
24 } catch (SQLException e) {
25 e.printStackTrace();
26 throw new DataAccessFailureException(e);
27 }catch (Exception e) {
28 e.printStackTrace();
29 }finally{
30 if(resultSet != null)
31 try {
32 resultSet.close();
33 } catch (SQLException e) {
34 e.printStackTrace();
35 throw new DataAccessFailureException("关闭结果集resultSet失败。",e);
36 }finally{
37 if(connection != null)
38 try {
39 connection.close();
40 } catch (SQLException e) {
41 e.printStackTrace();
42 throw new DataAccessFailureException("关闭连接connection失败。",e);
43 }
44 }
45
46 }
47 return map;
48 }