sqlMap文件
代码
- <?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));
- }
- }
SqlServer存储过程
代码
- 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