mysql+mybatis使用实例

一，给搜索结果添加序号

 

<select id="selectBigShotRankings" resultType="com.alibaba.fastjson.JSONObject"> 
    <![CDATA[ 
    	select (@i:=@i+1)i,s.* from (
         (select   ta.id,ta.name,ta.head_img,ta.mobile,ti.end_time,ti.memeber_type,ta.register_time
  from  ts_user_account ta
       JOIN ts_member_info ti  ON ta.id = ti.account_id 
       where 1=1 and big_shot=1 and ti.end_time  <now()
order by memeber_type desc,ta.register_time asc)
UNION
(select ta.id,ta.name,ta.head_img,ta.mobile ,ti.end_time,ti.memeber_type,ta.register_time
  from  ts_user_account ta
       JOIN ts_member_info ti  ON ta.id = ti.account_id 
       where 1=1 and big_shot=1 and (ti.end_time is null or  ti.end_time  > now() )
order by memeber_type desc,ta.register_time asc)
)s,(select  @i:=0)it 
    ]]>
</select>

二，添加返回主键

 

<insert id="insertSelective" parameterType="com.ssm.modules.entity.TsUserAccount"  useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    insert into ts_user_account
<trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="name != null">
        name,
     </if>
 </trim>
 <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=INTEGER},
      </if>
      <if test="name != null">
        #{name,jdbcType=VARCHAR},
      </if>
 </trim>
 </insert>

TsUserAccount record
int i = tsUserAccountMapper.insertSelective(record);
Integer id=record.getId();//主键Id

三，公共代码提取

<sql id="select">
   select  字段，字段... from 表
</sql>

<select >
   select * from (<include refid="select"/>)s3
</select >

四，mysql 查看表是否存在

   table_name  表名   no_worry  数据库名     如果没有，查询结果table_name 字段为null

SELECT table_name FROM information_schema.TABLES WHERE table_name ='ts_user' and table_schema="no_worry";