mybatis 查询 动态sql语句怎么写

mybatis的sql和你在数据库客户端执行的sql是一样的，但是在mybatis中调用的sql一般都是动态的，所以用到了参数传递。这个mybatis有对应的标签以及相应的变量来实现。你可以搜索下mybatis标签。同时给你一个参考的你看看，这个是一个查询用户的

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

<select id="queryUsers" parameterType="map" resultType="xx.xx.xx.bean.UserBean">

<![CDATA[

select

ID,

LOGIN_NAME AS loginName,

PASSWORD,

REAL_NAME AS realName,

POSITION,

(SELECT D.POSITION_NAME FROM UNIT_POSITION D WHERE D.POSITION_CODE=T.POSITION) POSITIONNAME,

USER_TYPE AS userType,

SEX,

PID,

TO_CHAR(T.BIRTHDAY,'YYYY-MM-DD') BIRTHDAY,

EMAIL,

CONTACT_TEL AS contactTel,

CONTACT_MOBILE AS contactMobile,

CONTACT_FAX AS contactFax,

CONTACT_ZIP AS contactZip,

CONTACT_ADDR AS contactAddr,

STATUS,

EDUCATION,

(SELECT D.EDUCATION_NAME FROM UNIT_EDUCATION D WHERE D.EDUCATION_CODE=T.EDUCATION AND D.STATUS=0) EDUCATIONNAME,

NATION,

POLITICAL,

REMARK,

TO_CHAR(T.CREATE_DATE,'YYYY-MM-DD HH24:MI:SS') createDate,

(SELECT D.REAL_NAME FROM UNIT_USER D WHERE D.ID= T.CREATE_USER_ID) createUserId,

TO_CHAR(T.UPDATE_DATE,'YYYY-MM-DD HH24:MI:SS') updateDate,

(SELECT D.REAL_NAME FROM UNIT_USER D WHERE D.ID= T.UPDATE_USER_ID) updateUserId

from UNIT_USER T

]]>

<where>

T.STATUS='1'

<if test="realName !=null and realName !=''">

and T.REAL_NAME like '%${realName}%'

</if>

<if test="nexusDpartment !=null">

AND T.ID IN (SELECT DISTINCT D.USER_ID FROM UNIT_USER_DEPT D WHERE D.DEPT_CODE IN (${nexusDpartment}))

</if>

<if test="deptCode !=null and deptCode !=''">

AND T.ID IN (SELECT DISTINCT D.USER_ID FROM UNIT_USER_DEPT D WHERE D.DEPT_CODE = #{deptCode})

</if>

</where>

<if test="sort != null and sort != ''">

order by ${sort}

<if test="direction != null and direction != ''">

${direction}

</if>

</if>

</select>

1、亲Mybatis是自己写Sql语句啊，和Hibernate不一样。

2、如何知道上面的，你还要知道MySql有一个分页语句叫limit，如：limit(1,10)前面一个参数是起始未知，后面一个是查询多少个。

3、Oracle的分页方法是嵌套子查询，需要用到rownum这个属性

Sql Server是Top。

分页例子：

Oracleselect * from (select emp.*,rownum rn from emp where rownum<9) where rn>3

MySql select * from emp limit startIndex,maxNum

---