java中Mybatis下Mysql、Oracle分页等写法

-----Mysql分页写法

<select id="selectPage" resultMap="baseResult" parameterType="cn.gold.livesys.entity.customerEntity">
<!-- Mysql从0开始计数 -->
<bind name="page" value="(page-1) * pageSize">
select f_id,f_name,f_team as f_team_name from T_CUSTOMER
<include refid="selectConditionlike">
limit #{page},#{pageSize}
</select>

-----Oracle分页写法

<select id="selectPage" resultMap="baseResult" parameterType="cn.gold.livesys.entity.customerEntity">
select * from (
	select rownum rn,t.* from (
		select f_id,f_name,f_team as f_team_name from T_CUSTOMER
	) t
) where rn &gt; #{pageSize} * (#{page}-1) and rn &lt; #{pageSize} * #{page} + 1
</select>

知识点：resultMap中的column=“f_team_name” 对应的sql语句里的别名

<!-- page:当前页码 -->
<!-- pageSize: 每页显示数量 -->
<resultMap id="baseResult" type="cn.gold.livesys.entity.customerEntity">
	<!-- property: 需要映射到JavaBean 的属性名称（customerEntity中的属性名）-->
	<!-- column: 数据表的列名或者标签别名。 -->
	<id property="id" column="f_id"/>
	<result property="name" column="f_name"/>
	<result property="teamName" column="f_team_name"/>
</resultMap>

-----Mysql返回主键ID

f_id：AI（auto increment 自增）

<insert useGeneratedKeys="true" parameterType="cn.gold.livesys.entity.customerEntity" keyProperty="id">
insert into...
<selectKey keyProperty="id" resultType="java.lang.Integer" order="AFTER">
	select LAST_INSERT_ID() as id
</selectKey>
</insert>

-----Oracle返回主键ID

ORACLE🔧 PLSQL
数据库->Sequences->创建序列 Name=T_CUSTOMER_F_ID_SEQ

cn.gold.livesys.entity.customerEntity.java
	private Integer id;//<!-- 同定义的keProperty值 -->
	
Mapper.xml
<insert id="addCustomer" parameterType="cn.gold.livesys.entity.customerEntity">
	<selectKey keProperty="id" resultType="java.lang.Integer" order="BEFORE">
		select T_CUSTOMER_F_ID_SEQ.nextval as id from dual		
	</selectKey>
	insert into
		...
</insert>