自己搞的在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 = "";

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 = "";

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 = "";

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">

<settings

cacheModelsEnabled="false"

<property name="Pool.MaximumActiveConnections"

value="10"/>

<property name="Pool.MaximumCheckoutTime"

value="120000"/>

<property name="Pool.PingQuery" value="select 1 from

ACCOUNT"/>

<property name="Pool.PingConnectionsOlderThan"

value="1"/>

<property name="Pool.PingConnectionsNotUsedFor"

value="1"/>

</dataSource>

</transactionManager>

</sqlMapConfig>

jdbc.properties文件配置：

jdbc.driverClassName=oracle.jdbc.driver.OracleDriver

jdbc.url=jdbc:oracle:thin

IP:1521:SID

jdbc.username=

jdbc.password=

jdbc.maxActive=3

jdbc.maxIdle=1

jdbc.maxWait=5000