ibatis 参考

<!--FriendResult -->
<select id="getList" resultMap="FriendResult">
select f.userid1,f.userid2,f.state1,f.subdate,f.cardno,b.grade,f.nickname,f.gender,f.age,f.city,b.username,b.userid,b.city,b.state,b.gender,b.cardno,d.*from friend f,basicuserinfo b,detailuserinfo d whereb.userid = f.userid2 and f.userid2=d.userid and f.state1=#state1#
<dynamic prepend="and">
<isNotEqual prepend="and" property="userid1" comparevalue="0">
f.userid1=#userid1#
</isNotEqual>
<isNotEqual prepend="and" property="userid2" comparevalue="0">
f.userid2=#userid2#
</isNotEqual>

</dynamic>
</select>



结合IBATIS的LIST遍历实现模糊查询
如何结合IBATIS的LIST遍历实现模糊查询
实例如下:
<iterate prepend="AND" property="keyList" conjunction="AND">
(A.message like #keyList[]# OR A.title like #keyList[]#)
</iterate>

模糊查询如何实现:%#keyList[]# %,’%#keyList[]# %’,本人均试过,均不成功,望高手指点.


可以试试
<iterate prepend="AND" property="keyList" conjunction="AND">
(A.message like’%$keyList[]$%’ OR A.title like ’%$keyList[]$%’)
</iterate>
2004年3月29日 22:57


ibatis模糊查询的实现
ibatis有两种方式，一种是＃，一种是$。用preparestatment来实现的时候是这样的。凡是#的，都作为参数，用setobject方式。而$方式的，则直接替换字符串。
所以。
select col1,col2 from table1 where col1=#col1# and col2 like ’$col2$’
假如参数是col1=2 col2=2
最终的SQL就是 select col1,col2 from table1 where col1=? and col2 like ’2’

如果需要通符，则用 like ’%$col2$%’
2004年3月29日 22:55

select * from table
where name LIKE ’%’ || #username# || ’%’

-------------------------------------------------------------

import com.ibatis.sqlmap.client.SqlMapClient;
import com.entel.util.SqlMapManager;

public List getMessageList(String messageState) {
List list = null;
try {
SqlMapClient client = SqlMapManager.getClient();
Message message = new Message();
message.setMessageState(messageState);
list = client.queryForList("getMessageList", message);
}
catch (Exception e) {
e.printStackTrace();
}
return list;
}

public boolean delMessage(Message message) {
try {
SqlMapClient client = SqlMapManager.getClient();
int res = client.delete("delMessage", message);
System.out.println(res);
}
catch (Exception e) {
e.printStackTrace();
}
return true;
}

public Message getMessage(int messageId) {
Message message = new Message();
message.setMessageTitle("title");
message.setMessage("asdfasfdasfdasfdasf");
return message;
}
public boolean sendMessage(Message message ) throws Exception{

try {
SqlMapClient client = SqlMapManager.getClient();
Object res= client.insert("insertMessage", message);
System.out.println(res);
}
catch (Exception e) {
e.printStackTrace();
}
return true;
}
public boolean sendMessageByListUser(Message message) throws Exception{

String receiveUser = message.getReceiveUser();
String sendUser = message.getSendUser();
UserDAOuserDao = new UserDAO();
ArrayList receiveUserIdList = userDao.getUserIdListByUsername(receiveUser);
ArrayList sendUserIdList = userDao.getUserIdListByUsername(receiveUser);
int sendUserId = ( (Integer)receiveUserIdList.get(0)).intvalue();

int receiveUserId=0;
boolean success =true;
for(int i=0 ;i<receiveUserIdList.size();i++){
receiveUserId =( (Integer)receiveUserIdList.get(i)).intvalue();
message.setSendUserid(sendUserId);
message.setReceiveUserid(receiveUserId);
if(!sendMessage(message)) success = false;
}
return success;
}
public int getMessageStatics(String messageState){
int count =0 ;
try {
SqlMapClient client = SqlMapManager.getClient();
Object res= client.queryForObject("countMessage", messageState);
count = Integer.parseInt(res.toString());
}
catch (Exception e) {
e.printStackTrace();
count =0;
}
return count;
}

<resultMap id="AlbumPhoto" class="com.entel.home.domain.AlbumPhoto">
<result property="id" column="id" />
<result property="username"column="username" />
<result property="title" column="title" />
<result property="URL" column="url" />
<result property="intro"column="intro" />
<result property="uploadTime"column="uploadtime" />
<result property="point"column="point" />
</resultMap>

<insert id="insertAlbumPhoto">
<selectKey resultClass="int" keyProperty="id">
select photograph_id.nextval as id from dual
</selectKey>
insert into photograph(id, username ,title ,url,intro,uploadtime,point)
values(#id#,#username#,#title#,’URL’,#intro#,’#uploadTime#’,#point#)
</insert>



<select id="countMessage" parameterClass ="string" resultClass="int">
select count(messageid) as value from message where messagestate =#value#
</select>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sql-map
PUBLIC "-//iBATIS.com//DTD SQL Map 1.0//EN"
"http://www.ibatis.com/dtd/sql-map.dtd">
<sql-map name="attachment">
<!-- =============================================
mapped-statement insert
============================================= -->
<dynamic-mapped-statement name="insertattachmentDao">
insert into ATTACHMENT
( <dynamic prepend="">
<isPropertyAvailable prepend="," property="ATTACHID" >
<isNotNull prepend="" property="ATTACHID" >
ATTACHID
</isNotNull>
</isPropertyAvailable>
<isPropertyAvailable prepend="," property="ATTACHDESC" >
<isNotNull prepend="" property="ATTACHDESC" >
ATTACHDESC
</isNotNull>
</isPropertyAvailable>
</dynamic>
</dynamic-mapped-statement>
<dynamic prepend="and">
<isNotEqual prepend="and" property="userid1" comparevalue="0">
f.userid1=#userid1#
</isNotEqual>
<isNotEqual prepend="and" property="userid2" comparevalue="0">
f.userid2=#userid2#
</isNotEqual>
</dynamic>
<result property="cardno" column="cardno" nullvalue="-999"/>
<dynamic-mapped-statement name="getSqlLogStatistics" cache-model="sqllog-cache"
result-map="sqllog-hashmap-result" >
SELECT * FROM (SELECT ROWNUM count_row_num, w_o_l_f_w.* FROM (
select * from (
select PARSED_SQL,COUNT(*) CNT from SQL_STMT
<dynamic prepend="WHERE">
<isNotNull prepend="AND" property="exetimestart">
<![CDATA[EXE_TIME >= #exetimestart#]]>
</isNotNull>
<isNotNull prepend="AND" property="exetimeend">
<![CDATA[ EXE_TIME <= #exetimeend# ]]>
</isNotNull>
<isNotEmpty prepend="AND" property="sql">
(SQL like ’%’||#sql#||’%’ or PARSED_SQL like ’%’||#sql#||’%’ )
</isNotEmpty>
</dynamic>
<dynamic prepend="HAVING">
<isNotEmpty property="countfrom">
<![CDATA[ COUNT(*) >= #countfrom# ]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="countto">
<![CDATA[ COUNT(*) <= #countto# ]]>
</isNotEmpty>
</dynamic>
GROUP by PARSED_SQL

<![CDATA[
) order by cntdesc) w_o_l_f_w
WHERE ROWNUM < #__EndPoint#)
WHERE (count_row_num >= #__StartPoint#)
]]>
</dynamic-mapped-statement>
<parameter-map name="insert-params">
<property name="exeTime" />
<property name="sql"/>
<property name="parsedSql"/>
<property name="sqlvalues"/>
</parameter-map>
<mapped-statement name="insertSql" parameter-map="insert-params" >
insert into SQL_STMT (
ID,
EXE_TIME,
SQL,
PARSED_SQL,
SQL_valueS)
values (
<!--注意这里，可以使用数据库本身的功能，不受限制-->
seq_sql_stmt.nextval, ?, ?, ?,?
)
</mapped-statement>
<isNotNull prepend="AND" property="exetimeend">
<![CDATA[ EXE_TIME <= #exetimeend# ]]>
</isNotNull>
<isNotEmpty prepend="AND" property="sql">
(SQL like ’%’||#sql#||’%’ or PARSED_SQL like ’%’||#sql#||’%’ )
</isNotEmpty>
</dynamic>
<dynamic prepend="HAVING">
<isNotEmpty property="countfrom">
<![CDATA[ COUNT(*) >= #countfrom# ]]>
</isNotEmpty>
<isNotEmpty prepend="AND" property="countto">
<![CDATA[ COUNT(*) <= #countto# ]]>
</isNotEmpty>
</dynamic>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sql-map PUBLIC "-//iBATIS.com//DTD SQL Map 1.0//EN" "http://www.ibatis.com/dtd/sql-map.dtd">

<sql-map name="EmployeeSQL">

<cache-model name="employee_cache" reference-type="WEAK">
<flush-interval hours="24"/>
<flush-on-execute statement= "insertEmployee" />
<flush-on-execute statement= "updateEmployee" />
</cache-model>

<result-map name="employee_result"class="net.reumann.Employee">
<property name="id" column="emp_id"/>
<property name="name" column="name"/>
<property name="deptId" column="dept_id"/>
<property name="deptName" column="dept_name"/>
<property name="location" column="location_id" mapped-statement="getLocationById"/>
</result-map>

<mapped-statement name="insertEmployee">
INSERT INTO employee ( id, name, dept_id, location_id )
valueS( #id#, #name#, #deptId#, #locationId# )
</mapped-statement>

<mapped-statement name="updateEmployee">
UPDATE employee SET name = #name#, dept_id = #deptId#, location_id = #locationId#
WHERE id = #id#
</mapped-statement>

<mapped-statementname="getLocationById" result-class="net.reumann.Location">
SELECT id, name FROM location where id = #value#
</mapped-statement>

<dynamic-mapped-statement name="selectEmployeesFromSearch" result-map="employee_result" cache-model="employee_cache">
SELECT employee.id AS emp_id,
employee.name AS name,
employee.dept_id ASdept_id,
department.name AS dept_name,
employee.location_id AS location_id,
location.name AS location_name
FROM
employee, department, location
WHERE
employee.dept_id = department.id AND
employee.location_id = location.id
<dynamic>
<isNotEmpty prepend=" AND " property="id">
employee.id = #id#
</isNotEmpty>
<isNotEmpty prepend=" AND " property="name">
employee.name = #name#
</isNotEmpty>
<isNotEmpty prepend=" AND " property="deptId">
employee.dept_id = #deptId#
</isNotEmpty>
<isNotEmpty prepend=" AND " property="locationId">
employee.location_id = #locationId#
</isNotEmpty>
</dynamic>
</dynamic-mapped-statement>
</sql-map>
<cache-model name="employee_cache" reference-type="WEAK">
<flush-interval hours="24"/>
<flush-on-execute statement= "insertEmployee" />
<flush-on-execute statement= "updateEmployee" />
</cache-model>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sql-map PUBLIC "-//iBATIS.com//DTD SQL Map 1.0//EN" "http://www.ibatis.com/dtd/sql-map.dtd">

<sql-map name="LabelvalueSQL">

<cache-model name="label_value_cache" reference-type="STRONG">
<flush-intervalhours="48"/>
</cache-model>

<result-map name="label_value_result" class="net.reumann.Labelvalue">
<property name="value"column="id" />
<property name="label"column="name" />
</result-map>

<mapped-statement name="selectAllDepartments" result-map="label_value_result" cache-model="label_value_cache">
SELECTid, name FROM department ORDER BYname
</mapped-statement>

<mapped-statement name="selectAllLocations" result-map="label_value_result" cache-model="label_value_cache">
SELECTid, name FROM location ORDER BY name
</mapped-statement>

</sql-map>

<resultMap id="FriendResult" class="com.entel.home.domain.Friend">
<result property="userid1" column="USERID1"/>
<result property="userid2" column="USERID2"/>
<result property="state1" column="STATE1" />
<result property="subdate" column="SUBDATE"/>
<result property="userid" column="userid" nullvalue="-999"/>
<result property="username" column="username" nullvalue="-999"/>
<result property="gender" column="gender" nullvalue="-999"/>
<result property="cardno" column="cardno" nullvalue="-999"/>
<result property="nickname" column="nickname" nullvalue="-999"/>
<result property="city" column="city" nullvalue="-999"/>
<result property="grade" column="grade" nullvalue="-999"/>
<result property="state" column="state" nullvalue="-999"/>
</resultMap>
<insert id="add" parameterMap="AccountAddParam"> <![CDATA[

DECLARE

n_count NUMBER(1);

BEGIN

SELECT COUNT(*) INTO n_count

FROM T_ACCOUNT t

WHERE t.S_LOGINNAME=?;



IF n_count>0 THEN

RAISE_APPLICATION_ERROR(-20000,’用户帐号已存在’);

ELSE

INSERT INTO

T_ACCOUNT(ACCOUNT_ID,S_LOGINNAME,S_PASSWORD,N_ISACTIVE,

S_TRUENAME,S_EMAIL,N_REGTIME,S_DESC)

valueS(?,?,?,?,?,?,?,?);

END IF;

END;

]]> </insert>

ibatis中执行pl/sql语句块的测试
配置文件：

<?xml version="1.0" encoding="GBK"?>

<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"

"http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="Test">

<update id="update"><![CDATA[

declare

n_count number;

begin

select count(*) into n_count from t_account;

update t_auth set s_authdesc=’记录数：’||n_count;

end;

]]> </update>

</sqlMap>



测试代码：

public class Test

{



public static void main(String[] args)

{

SqlMapClient sqlMap = SqlMapConfig.getSqlMap();

try

{

sqlMap.startTransaction();

sqlMap.update("Test.update", null);

sqlMap.commitTransaction();

}

catch (SQLException e)

{

e.printStackTrace();

}

finally

{

try

{

sqlMap.endTransaction();

}

catch (SQLException e)

{

e.printStackTrace();

}

}

}

}

<!—Oracle SEQUENCE Example -->
<insert id="insertProduct-ORACLE" parameterClass="com.domain.Product">
<selectKey resultClass="int" keyProperty="id" >
SELECT STOCKIDSEQUENCE.NEXTVAL AS ID FROM DUAL
</selectKey>
insert into PRODUCT (PRD_ID,PRD_DESCRIPTION)
values (#id#,#description#)
</insert>


<!— Microsoft SQL Server IDENTITY Column Example -->
<insert id="insertProduct-MS-SQL" parameterClass="com.domain.Product">
insert into PRODUCT (PRD_DESCRIPTION)
values (#description#)
<selectKey resultClass="int" keyProperty="id" >
SELECT @@IDENTITY AS ID
</selectKey>
</insert>