自己搞的在oralce下动态生成IBATIS的sqlMap文件小工具

如果需要完整的代码或建议可以发到zhoubikui@eyou.com。

metadata类，可以在oralce下的取得相应的数据表信息,并生成文件sqlMap文件
* 目前可以生成IBATIS从数据库表到IBATIS sqlMAP类型主要有SELECT，INSERT，UPDATE
* 因项目太急，只是临时自己使用，没有开发RESULTMAP和PARAMETERMAP生成
* 下步计划开发这个工具，并把它图形化，先听一下各位的意见，希望与各位交流。
* 调用该程序，只需调用getMetaData（）方法即可
* 数据库配置见cn/com/mofit/demo/system/dao/maps/sqlMap-config.xml，
* 修改当前目录下的jdbc.properties数据库配置就可使用

其主类实现方法是：
package cn.com.mofit.util.jdbc;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import org.springframework.dao.DataAccessException;
import org.springframework.orm.ibatis.SqlMapClientTemplate;

import cn.com.mofit.util.spring.orm.ibatis.SqlMapDaoSupportPlus;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.engine.builder.xml.XmlSqlMapClientBuilder;

/**
*
* @author 周必奎
* 2004-10-15
* @email:zhoubikui@eyou.com
* @deprecated metadata类，可以在oralce下的取得相应的数据表信息,并生成文件sqlMap文件
* 目前可以生成IBATIS从数据库表到IBATIS sqlMAP类型主要有SELECT，INSERT，UPDATE
* 因项目太急，只是临时自己使用，没有开发RESULTMAP和PARAMETERMAP生成
* 下步计划开发这个工具，并把它图形化，先听一下各位的意见，希望与各位交流。
* 调用该程序，只需调用getMetaData（）方法即可
* 数据库配置见cn/com/mofit/demo/system/dao/maps/sqlMap-config.xml，
* 修改当前目录下的jdbc.properties数据库配置就可使用
*/
public class RsMetaDataOracle {
/*
* filePath SQLMAP文件生成的路径名，是绝对路径
*/
private String filePath = "c:/";

/*
* mapTablename 要映射的数据库的表名
*/
private String mapTablename = "BK_BILL";

//System.getProperty("user.dir") + "/config/sqlmap/";
private static SqlMapClientTemplate sqlTemp;
static {
try {
SqlMapDaoSupportPlus sqlsu = new SqlMapDaoSupportPlus();

String resource = "cn/com/mofit/demo/system/dao/maps/sqlMap-config.xml";
Reader read;
read = Resources.getResourceAsReader(resource);

XmlSqlMapClientBuilder xmlBuilder = new XmlSqlMapClientBuilder();
SqlMapClient sqlMap = xmlBuilder.buildSqlMap(read);

sqlsu.setSqlMapClient(sqlMap);
sqlsu.afterPropertiesSet();
sqlTemp = sqlsu.getSqlMapClientTemplate();

} catch (IOException e1) {
e1.printStackTrace();
} catch (Exception e) {

e.printStackTrace();
}

}

private static SqlMapClientTemplate getSqlMapTempInstance() {
return sqlTemp;
}

public void getMetaData() throws DataAccessException {
try {
//DaoCommon.startTransaction();
SqlMapClientTemplate sqlTemp = RsMetaDataOracle
.getSqlMapTempInstance();

//SqlMap sqlMap = DaoCommon.getSqlMap(this);
Connection conn = sqlTemp.getDataSource().getConnection();
Statement stmt = conn.createStatement();
List list = getTableNames();

for (Iterator iter = list.iterator(); iter.hasNext() {
String element = (String) iter.next();

ResultSet rs = stmt.executeQuery("select * from " + element);
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
if (element.startsWith(mapTablename.toUpperCase())) {

File file = new File(filePath);

if (!file.exists()) {
file.mkdir();
}

file = new File(filePath + element.toLowerCase() + ".xml");

String xml = "<?xml version=/"1.0/" encoding=/"GBK/" ?>/n";
xml += "<!DOCTYPE sql-map/n";
xml += "PUBLIC /"-//iBATIS.com//DTD SQL Map Config 2.0//EN/" /n";
xml += "/"http://www.ibatis.com/dtd/sql-map-2.dtd/">/n";
xml += ("<sql-map namespace=/"" + element.toLowerCase() + "/">/n");
xml += getXml(rsmd, numberOfColumns, element);
xml += "/n</sql-map>";

FileWriter writer = new FileWriter(file);
writer.write(xml);
writer.flush();
writer.close();
}
}
} catch (DataAccessException e) {
e.printStackTrace();
} catch (SQLException e) {

e.printStackTrace();
} catch (IOException e) {

e.printStackTrace();
}
}

private String getXml(ResultSetMetaData rsmd, int numberOfColumns,
String tableName) throws SQLException {
String result = "";
result += (createfindSql(rsmd, numberOfColumns, tableName));
result += (createInsertSql(rsmd, numberOfColumns, tableName));
result += (createUpdateSql(rsmd, numberOfColumns, tableName));
return result;
}

private String createfindSql(ResultSetMetaData rsmd, int numberOfColumns,
String tableName) throws SQLException {
String result;
result = "<!-- =============================================/n mapped-statement find /n============================================= -->";

result += ("/n<select id=/"find" + tableName.toLowerCase() + "Dao/" resultClass=/"java.util.HashMap/">");

result += ("/n select $listfield$ from " + tableName + "/n <dynamic prepend=/"where/">");

result += createWhereSql(rsmd, numberOfColumns, "and", 1);
result += "/n </dynamic>";
result += "/n</select>/n/n/n";
return result;
}

private String createColumnsString(ResultSetMetaData rsmd)
throws SQLException {
String result = "";
int numberOfColumns = rsmd.getColumnCount();
for (int i = 1; i <= numberOfColumns; i++) {
String colName = rsmd.getColumnName;
String name = rsmd.getColumnTypeName;
result += (((i == 1) ? "/n " : "/n ") + colName + ",");
}

return result.substring(1, result.length() - 1);
}

private String createWheremapSql(ResultSetMetaData rsmd, int numberOfColumns)
throws SQLException {
String result = "";

for (int i = 1; i <= numberOfColumns; i++) {
String colName = rsmd.getColumnName;
String name = rsmd.getColumnTypeName;
result += (((i == 1) ? "/n " : "/n and ") + colName
+ "=#" + colName + "#");
}

return result;
}

private String createInsertSql(ResultSetMetaData rsmd, int numberOfColumns,
String tableName) throws SQLException {
String result = "<!-- =============================================/n mapped-statement insert /n============================================= -->";
result += ("/n<insert id=/"insert" + tableName.toLowerCase() + "Dao/" parameterClass=/"java.util.HashMap/">");
result += ("/n insert into " + tableName + "( /n"
+ createColumnsString(rsmd) + ") "

+ "/n <dynamic prepend=/"values(/">");

result += createWhereSql(rsmd, numberOfColumns, ",", 3) + ")";
result += "/n </dynamic>";
result += "/n</insert>/n/n/n";
return result;

}

private String createUpdateSql(ResultSetMetaData rsmd, int numberOfColumns,
String tableName) throws SQLException {
String result = "<!-- =============================================/n mapped-statement update /n============================================= -->";
result += ("/n<update id=/"update" + tableName.toLowerCase() + "Dao/" parameterClass=/"java.util.HashMap/">");
result += ("/n update " + tableName
+ "/n <dynamic prepend=/"set/"> "
+ createWhereSql(rsmd, numberOfColumns, ",", 4)

+ "/n </dynamic> /n <dynamic prepend=/"where/">");

result += createWhereSql(rsmd, numberOfColumns, "and", 1);
result += "/n </dynamic>";
result += "/n</update>/n/n/n";
return result;
}

private String createWhereSql(ResultSetMetaData rsmd, int numberOfColumns,
String prepend, int detail) throws SQLException {
String result = "";

for (int i = 1; i <= numberOfColumns; i++) {
String colName = rsmd.getColumnName;

result += ("/n <isPropertyAvailable prepend=/"/" property=/""
+ colName.toLowerCase() + "/" >");

result += ("/n <isNotNull prepend=/"" + prepend
+ "/" property=/"" + colName.toLowerCase() + "/" >");

switch (detail) {
case 1: //where 语句
result += ("/n " + colName + "=#"
+ colName.toLowerCase() + "#");

break;

case 2: //insert的语句
result += ("/n " + colName.toLowerCase());

break;

case 3: //insert 准备的
result += ("/n #" + colName.toLowerCase() + "#");

break;

case 4: //修改的set语句

result += ("/n "
+ colName
+ "=#"
+ colName.toLowerCase()
+ (Types.VARCHAR == rsmd.getColumnType ? ":VARCHAR"
: "") + "#");

break;

default:
break;
}

result += ("/n </isNotNull>")
+ "/n </isPropertyAvailable>";
}

return result;
}

private void getType(ResultSetMetaData rsmd, int i, HashMap colMap)
throws SQLException {
switch (rsmd.getColumnType) {
case Types.VARCHAR:
colMap.put("COLUMNTYPE", rsmd.getColumnTypeName + "("
+ rsmd.getPrecision + ")");
break;
case 2:
colMap.put("COLUMNTYPE", rsmd.getColumnTypeName + "("
+ rsmd.getPrecision + "," + rsmd.getScale + ")");
break;
default:
colMap.put("COLUMNTYPE", rsmd.getColumnTypeName);
break;
}
}

private List getTableNames() throws DataAccessException {
List result = new Vector();

//SqlMap sqlMap = DaoCommon.getSqlMap(this);
Connection conn;
try {
conn = getSqlMapTempInstance().getDataSource().getConnection();
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getTableTypes();
String[] types = { "TABLE" };
rs = dbmd.getTables(null, dbmd.getUserName(), "%", types);

while (rs.next()) {
result.add(rs.getString("TABLE_NAME"));
}

rs.close();

} catch (SQLException e) {

e.printStackTrace();
}

return result;
}

public String getFilePath() {
return filePath;
}

public void setFilePath(String filePath) {
this.filePath = filePath;
}

public String getMapTablename() {
return mapTablename;
}

public void setMapTablename(String mapTablename) {
this.mapTablename = mapTablename;
}
}

SQL-CONFIG文件配置是：
<?xml version="1.0" encoding="GB2312" ?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
  <properties resource="cn/com/mofit/demo/system/dao/maps/jdbc.properties"/>
<!-- debug环境下，将其设为false. 正式运行时应设为true,启用缓存 -->
<settings
  cacheModelsEnabled="false"
  />
<transactionManager type="JDBC">
  <dataSource type="SIMPLE">
<property name="JDBC.Driver" value="${jdbc.driverClassName}"/>
<property name="JDBC.ConnectionURL" value="${jdbc.url}"/>
<property name="JDBC.Username" value="${jdbc.username}"/>
<property name="JDBC.Password" value="${jdbc.password}"/>
<property name="Pool.MaximumActiveConnections"
value="10"/>
<property name="Pool.MaximumIdleConnections" value="5"/>
<property name="Pool.MaximumCheckoutTime"
value="120000"/>
<property name="Pool.TimeToWait" value="500"/>
<property name="Pool.PingQuery" value="select 1 from
ACCOUNT"/>
<property name="Pool.PingEnabled" value="false"/>
<property name="Pool.PingConnectionsOlderThan"
value="1"/>
<property name="Pool.PingConnectionsNotUsedFor"
value="1"/>
</dataSource>
</transactionManager>
<!-- 非常简洁，将用到的sqlMap文件列到这儿就行了 -->
<sqlMap resource="cn/com/mofit/demo/system/dao/maps/User.xml" />
<sqlMap resource="cn/com/mofit/demo/bank/dao/maps/Bank.xml" />
</sqlMapConfig>

jdbc.properties文件配置：
jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thinIP:1521:SID
jdbc.username=
jdbc.password=
jdbc.maxActive=3
jdbc.maxIdle=1
jdbc.maxWait=5000