|
[list=1] <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd"> <sqlMap namespace="Account"> <typeAlias alias="Account" type="test.Account"/> <!--列表查询,返回Account Object的List--> <resultMap id="AccountResult" class="Account"> <result property="id" column="ID"/> <result property="firstName" column="FIRST_NAME"/> <result property="lastName" column="LAST_NAME"/> <result property="emailAddress" column="EMAIL"/> </resultMap> <select id="selectAllAccounts" resultMap="AccountResult"> select * from ACCOUNT </select> <!--列表查询,返回HashMap的List --> <!--resultMap无需再通过JDBC ResultSetMetaData 来动态获取字段信息，性能有提高--> <resultMap id="mapResult" class="java.util.HashMap"> <result property="roleid" column="ROLEID"/> <result property="rolename" column="ROLENAME"/> <result property="id" column="ID"/> <result property="firstName" column="FIRST_NAME"/> <result property="lastName" column="LAST_NAME"/> <result property="emailAddress" column="EMAIL"/> <result property="dt" column="DT"/> </resultMap> <select id="selectAllAccountsWithMap" resultMap="mapResult"> select B.ROLEID, B.ROLENAME, A.ID, A.FIRST_NAME,A.LAST_NAME,A.EMAIL,A.DT from ACCOUNT A left outer join ROLE B on A.ROLEID = B.ROLEID ORDER BY A.ID DESC </select> <!--动态构造查询条件--> <select id="getUsers" parameterClass="user" resultMap="get-user-result"> Select id,name,sex from t_user <dynamic prepend="WHERE"> <isNotEmpty prepend="AND" property="name"> (name like #name#) </isNotEmpty> <isNotEmpty prepend="AND" property="address"> (address like #address#) </isNotEmpty> </dynamic> </select> <isNotEmpty prepend="AND" property="name"> ( name=#name# <isNotEmpty prepend="AND" property="address"> address=#address# </isNotEmpty> ) </isNotEmpty> <select id="dynamicGetAccountList" resultMap="account-result" > select * from ACCOUNT <dynamic prepend="WHERE"> <isNotNull prepend="AND" property="firstName" open=”(“ close=”)”> ACC_FIRST_NAME = #firstName# <isNotNull prepend="OR" property="lastName"> ACC_LAST_NAME = #lastName# </isNotNull> </isNotNull> <isNotNull prepend="AND" property="emailAddress"> ACC_EMAIL like #emailAddress# </isNotNull> <isGreaterThan prepend="AND" property="id" compareValue="0"> ACC_ID = #id# </isGreaterThan> </dynamic> order by ACC_LAST_NAME </select> <isParameterPresent> <isNotParameterPresent> <isNull> <isNotNull> <isEmpty> <isNotEmpty> <isEqual> <isNotEqual> <isGreaterThan> <isGreaterEqual> <isLessThan> <isLessEqual> <!-- Sql片段的是用--> <sql id="selectItem_fragment"> FROM items WHERE parentid = 6 </sql> <select id="selectItemCount" resultClass="int"> SELECT COUNT(*) AS total <include refid="selectItem_fragment"/> </select> <select id="selectItems" resultClass="Item"> SELECT id, name <include refid="selectItem_fragment"/> </select> <!--缓存--> <cacheModel id="product-cache" type="LRU"> <flushInterval hours="24"/> <flushOnExecute statement="insertProduct"/> <flushOnExecute statement="updateProduct"/> <flushOnExecute statement="deleteProduct"/> <property name=”size” value=”1000” /> </cacheModel> <select id=”getProductList” parameterClass=”int” cacheModel=”product-cache”> select * from PRODUCT where PRD_CAT_ID = #value# </select> <!—对XML支持 --> <select id="getPerson" parameterClass=”int” resultClass="xml" xmlResultName=”person”> SELECT PER_ID as id, PER_FIRST_NAME as firstName, PER_LAST_NAME as lastName, PER_BIRTH_DATE as birthDate, PER_WEIGHT_KG as weightInKilograms, PER_HEIGHT_M as heightInMeters FROM PERSON WHERE PER_ID = #value# </select> <person> <id>1</id> <firstName>Clinton</firstName> <lastName>Begin</lastName> <birthDate>1900-01-01</birthDate> <weightInKilograms>89</weightInKilograms> <heightInMeters>1.77</heightInMeters> </person> <!—字符串替换 select * from $tableName$ Important Note 1: This support will only substitute Strings, so it is not appropriate for complex data types like Date or Timestamp. Important Note 2: If you use this support to alter a table name, or a column list, in an SQL select statement,then you should always specify remapResults=“true” -->. <!—关联查询方式，有1/N问题--> <sqlMap namespace="User"> <typeAlias alias="user" type="com.ibatis.sample.User"/> <typeAlias alias="address" type="com.ibatis.sample.Address"/> <resultMap id="get-user-result" class="user"> <result property="id" column="id"/> <result property="name" column="name"/> <result property="sex" column="sex"/> <result property="addresses" column="id" select="User.getAddressByUserId"/> </resultMap> <select id="getUsers" parameterClass="java.lang.String" resultMap="get-user-result"> <![CDATA[Select id,name,sex from t_user where id = #id#]]> </select> <select id="getAddressByUserId" parameterClass="int" resultClass="address"> <![CDATA[select address,zipcode from t_address where user_id = #userid# ]]> </select> </sqlMap> <resultMap id="get-user-result" class="user"> <result property="id" column="id"/> <result property="name" column="name"/> <result property="sex" column="sex"/> <result property="address" column="t_address.address"/> <result property="zipCode" column="t_address.zipcode"/> </resultMap> <select id="getUsers" parameterClass="java.lang.String" resultMap="get-user-result"> <![CDATA[select* from t_user,t_address where t_user.id=t_address.user_id]]> </select> 保证User 类中包含address和zipCode两个String型属性。 <!—关联查询，无1/N问题--> <resultMap id="AccountResultWithRole" class="Account" groupBy="id"> <result property="id" column="ID"/> <result property="firstName" column="FIRST_NAME"/> <result property="lastName" column="LAST_NAME"/> <result property="emailAddress" column="EMAIL"/> <result property="role" resultMap="Account.roleResult"/> </resultMap> <resultMap id="roleResult" class="test.Role"> <result property="roleid" column="ROLEID"/> <result property="rolename" column="ROLENAME"/> </resultMap> <select id="selectAccountByIdWithRole" parameterClass="int" resultMap="AccountResultWithRole"> select B.ROLEID, B.ROLENAME, A.ID, A.FIRST_NAME,A.LAST_NAME,A.EMAIL from ACCOUNT A left outer join ROLE B on A.ROLEID = B.ROLEID where A.ID = #id# </select> <!--查询--> <select id="selectAccountById" parameterClass="int" resultClass="Account"> select ID as id,FIRST_NAME as firstName,LAST_NAME as lastName, EMAIL as emailAddress from ACCOUNT where ID = #id# </select> <!--新增--> <insert id="insertAccount" parameterClass="Account"> insert into ACCOUNT (FIRST_NAME,LAST_NAME,EMAIL,PID,DT) values (#firstName:VARCHAR#, #lastName:VARCHAR#, #emailAddress:VARCHAR#,#pid:INTEGER:0#,#dt:TIME#) <selectKey resultClass="int" type="post" keyProperty="id"> SELECT @@IDENTITY AS ID </selectKey> </insert> <!--更新--> <update id="updateAccount" parameterClass="Account"> update ACCOUNT set FIRST_NAME = #firstName:VARCHAR#, LAST_NAME = #lastName:VARCHAR#, EMAIL = #emailAddress:VARCHAR# where ID = #id# </update> <!--删除--> <delete id="deleteAccountById" parameterClass="int"> delete from ACCOUNT where ID = #id# </delete> <!--存储过程，如果没有返回列表，procTest的resultMap可以省略--> <parameterMap id="procParamMap" class="java.util.HashMap" > <parameter property="id" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/> <parameter property="outid" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/> <parameter property="errMsg" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/> </parameterMap> <resultMap id="procResultMap" class="java.util.HashMap" > <result property="a" column="AAA"/> <result property="b" column="BBB"/> <result property="c" column="CCC"/> </resultMap> <procedure id="procTest" parameterMap="procParamMap" resultMap="procResultMap"> {call test_sp_1 (?,?,?)} </procedure> </sqlMap>
//JAVA代码：
public class TestDao extends SqlMapClientDaoSupport { public List selectAllAccounts() throws SQLException { return getSqlMapClientTemplate().queryForList("selectAllAccounts"); } public List selectAllAccountsWithMap() throws SQLException { return getSqlMapClientTemplate().queryForList( "selectAllAccountsWithMap"); } public Account selectAccountById(int id) throws SQLException { return (Account) getSqlMapClientTemplate().queryForObject( "selectAccountById", new Integer(id)); } public List procTest(Map params) throws Exception { List ret = (List) getSqlMapClientTemplate().queryForList("procTest",params); return ret; } public Account selectAccountByIdWithRole(int id) throws SQLException { return (Account) getSqlMapClientTemplate().queryForObject( "selectAccountByIdWithRole", new Integer(id)); } public void insertAccount(Account account) throws SQLException { getSqlMapClientTemplate().insert("insertAccount", account); } public int updateAccount(Account account) throws SQLException { return getSqlMapClientTemplate().update("updateAccount", account); } public int deleteAccount(int id) throws SQLException { return getSqlMapClientTemplate().delete("deleteAccountById", new Integer(id)); } }
//存储过程
CREATE procedure test_sp_1 @id int,@outid int output,@errMsg varchar(255) output AS update ACCOUNT set pid=@id select @outid=isnull(@id,0)+1 select @errMsg='测试' select AAA='AAAAAAA',BBB='BBBBBBB',CCC='CCCCCCC' GO
|
一共有 116 条评论