Java开发网 - 自己搞的在oralce下动态生成IBATIS的sqlMap文件小工具

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

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

Posted by: zhbk
Posted on: 2004-10-15 13:55

如果需要完整的或建议可以发到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">

<sqlMapConfig>
<properties resource="cn/com/mofit/demo/system/dao/maps/jdbc.properties"/>

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

2.Re:自己搞的在oralce下动态生成IBATIS的sqlMap文件小工具 [Re: zhbk]	Copy to clipboard
Posted by: gunrose Posted on: 2004-10-16 13:07 使用velocity保证你可以把你的代码变的更好看，更合理，更有效率。另：我已在项目中使用这种方式来生成源码，配置文件等。下面的目标是用它基于项目的特定样式生成相应的表现层，例如基于JSP的CRUD。

3.Re:自己搞的在oralce下动态生成IBATIS的sqlMap文件小工具 [Re: zhbk]	Copy to clipboard
Posted by: newnewworm Posted on: 2004-11-12 23:29 IBatis对于基本的单表操作还是很不错的！可惜多表操作，简直就是恶梦！

4.Re:自己搞的在oralce下动态生成IBATIS的sqlMap文件小工具 [Re: zhbk]	Copy to clipboard
Posted by: dingjunming Posted on: 2004-11-24 09:49 NEC公司都有一套自己用的图形界面生成存储过程的工具