boredbird 2010-09-19
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN" "http://www.ibatis.com/dtd/sql-map-config-2.dtd"> <sqlMapConfig> <settings cacheModelsEnabled="true" enhancementEnabled="true" lazyLoadingEnabled="true" errorTracingEnabled="true" maxRequests="32" maxSessions="10" maxTransactions="5" useStatementNamespaces="false" /> <transactionManager type="JDBC"> <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="oracle.jdbc.driver.OracleDriver" /> <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@localhost:1521:orcl" /> <property name="JDBC.Username" value="wind" /> <property name="JDBC.Password" value="rainbow" /> <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 sample" /> <property name="Pool.PingEnabled" value="false" /> <property name="Pool.PingConnectionsOlderThan" value="1" /> <property name="Pool.PingConnectionsNotUsedFor" value="1" /> </dataSource> </transactionManager> <sqlMap resource="com/wind/maps/User.xml" /> </sqlMapConfig>
1.like中%的配置异常;
2. 使用<isNotEmpty prepend=" AND " property="filedName"></isNotEmpty>时,后面必须有条件,如果不存在,可加 1=1 ;<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap namespace="User"> <typeAlias alias="user" type="com.wind.pojo.User" /> <select id="getAllUser" resultClass="user"> select username,password,rolename from t_user ,t_role where t_user.roleid=t_role.id </select> <select id="getLikeUser" parameterClass="user" resultClass="user"> select username,password,rolename from t_user ,t_role where t_user.roleid=t_role.id <isNotEmpty prepend=" AND " property="username"> username like '%'||#username#||'%' </isNotEmpty> <isNotEmpty prepend=" AND " property="password"> password = #password# </isNotEmpty> </select> <insert id="insertUser" parameterClass="user"> INSERT INTO t_user1 (id, username, password, roleid) VALUES (SEQ_USERID.NEXTVAL, #username#, #password#, #roleid# ) </insert> <delete id="deleteUser" parameterClass="java.lang.Integer"> delete from t_user1 where id=#value# </delete> </sqlMap>
reader = Resources.getResourceAsReader("com/maps/SqlMapConfig.xml"); sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
List<User> result = null; try { sqlMap.startTransaction(); result =sqlMap.queryForList("getAllUser", null); // sqlMap.insert("insertUser", user); // sqlMap.update("updateUser", user); sqlMap.commitTransaction(); } catch (SQLException e) { e.printStackTrace(); } finally { try { sqlMap.endTransaction(); } catch (SQLException e) { e.printStackTrace(); } } for(User u : result) { System.out.println(u.getUsername()+" "+u.getPassword()+" "+u.getRolename()); }
如:对于sql语句order by #user_id#,如果传入的值是111,那么解析成sql时的值为order by "111", 如果传入的值是id,则解析成的sql为order by "id"。