mybatis 一些总结

娟斌心

　　最近用mybatis开发，一些总结：

• 
  结合spring框架，需要在spring配置文件中加入sessionFactory定义：　　        
  
  <!-- 创建SqlSessionFactory，同时指定数据源 -->
  <bean id="sqlSessionFactory"
  class="org.mybatis.spring.SqlSessionFactoryBean">
  <property name="configLocation" value="classpath:mybatis-config.xml"/>
  <property name="dataSource" ref="dataSource" />
  </bean>
  
• 
  定义mybatis-config.xml文件
  <?xml version="1.0" encoding="UTF-8" ?>  
  <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">  
  <configuration>  
  <typeAliases>  
  <typeAlias alias="App" type="com.cyou.appserver.entity.App"/>  
  <typeAlias alias="Feedback" type="com.cyou.appserver.entity.Feedback"/>   
  <typeAlias alias="RecommendApp" type="com.cyou.appserver.entity.RecommendApp"/>
  <typeAlias alias="UpdateInfo" type="com.cyou.appserver.entity.UpdateInfo"/>
  <typeAlias alias="DeviceToken" type="com.cyou.appserver.entity.DeviceToken"/>
  <typeAlias alias="Cards" type="com.cyou.appserver.entity.Cards"/>
  <typeAlias alias="UnableVideoLog" type="com.cyou.appserver.entity.UnableVideoLog"/>
  <typeAlias alias="Battle" type="com.cyou.appserver.entity.Battle"/>
  <typeAlias alias="Card" type="com.cyou.appserver.entity.Card"/>
  <typeAlias alias="Occupational" type="com.cyou.appserver.entity.Occupational"/>
  <typeAlias alias="Licensing" type="com.cyou.appserver.entity.Licensing"/>
  <typeAlias alias="Pictures" type="com.cyou.appserver.entity.Pictures"/>
  <typeAlias alias="Timetable" type="com.cyou.appserver.entity.Timetable"/>
  <typeAlias alias="IndexImage" type="com.cyou.appserver.entity.IndexImage"/>
  <typeAlias alias="CardStat" type="com.cyou.appserver.entity.CardStat"/>
  <typeAlias alias="AppPush" type="com.cyou.appserver.entity.AppPush" />
  <typeAlias alias="Formation" type="com.cyou.appserver.entity.Formation" />
  <typeAlias alias="FormationParise" type="com.cyou.appserver.entity.FormationParise" />
  <!--        <typeAlias alias="AppConfig" type="com.cyou.appserver.entity.AppConfig"/>
  <typeAlias alias="AppMenus" type="com.cyou.appserver.entity.AppMenus"/>
  <typeAlias alias="AppClick" type="com.cyou.appserver.entity.AppClick"/>-->
  </typeAliases>
  <mappers>
  <mapper resource="mybatis/Ad.xml" />
  <mapper resource="mybatis/App.xml" />
  <mapper resource="mybatis/Feedback.xml" />
  <mapper resource="mybatis/RecommendApp.xml" />
  <mapper resource="mybatis/UpdateInfo.xml" />
  <mapper resource="mybatis/DeviceToken.xml" />
  <mapper resource="mybatis/Cards.xml" />
  <mapper resource="mybatis/UnableVideoLog.xml" />
  <mapper resource="mybatis/Battle.xml" />
  <mapper resource="mybatis/Card.xml" />
  <mapper resource="mybatis/Occupational.xml" />
  <mapper resource="mybatis/Licensing.xml" />
  <mapper resource="mybatis/Pictures.xml" />
  <mapper resource="mybatis/Timetable.xml" />
  <mapper resource="mybatis/IndexImage.xml" />
  <mapper resource="mybatis/CardStat.xml" />
  <mapper resource="mybatis/AppPush.xml" />
  <!-- <mapper resource="mybatis/AppConfig.xml" />
  <mapper resource="mybatis/AppMenus.xml" />
  <mapper resource="mybatis/AppClick.xml" /> -->
  <mapper resource="mybatis/Formation.xml" />
  <mapper resource="mybatis/FormationParise.xml" />
  </mappers>
  </configuration>
  
• 
  定义具体的域对象xml文件，比如Formation.xml
  <?xml version="1.0" encoding="UTF-8"?>
  <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace="Formation">
  <select id="list" resultType="Formation" parameterType="map">
  SELECT f.*,count(p.formationId) as praiseCount
  FROM
  formation f LEFT JOIN formation_parise p ON f.id = p.formationId
  WHERE
  f.appId=#{appId}
  <if test="level != null">  
  and f.level = #{level}  
  </if>
  <if test="query2 != null">
  and f.createTime &gt; #{query2}
  </if>
  GROUP BY
  f.id
  <if test="query1 != null">
  ORDER BY
  f.${query1}
  </if>
  LIMIT #{offset},#{limit}
  </select>
  <select id="countByParams" resultType="int" parameterType="map">
  SELECT count(f.id)
  FROM
  formation f
  WHERE
  f.appId=#{appId}
  <if test="level != null">  
  and f.level = #{level}  
  </if>
  <if test="query2 != null">
  and f.createTime &gt; #{query2}
  </if>
  </select>
  <select id="totalCount" resultType="int" parameterType="int">
  select count(*) from formation where appId=#{id}
  </select>
  <insert id="insert" parameterType="Formation" keyProperty="id" useGeneratedKeys="true">
  <!-- <selectKey keyProperty="id" resultType="int">select nextval('formation')</selectKey> -->
  insert into formation(appId, name, remark, imageUrl,createTime,contentUrl,commentCount,imei,level,deviceType)
  values (#{appId}, #{name}, #{remark}, #{imageUrl},#{createTime},#{contentUrl},#{commentCount},#{imei},#{level},#{deviceType})
  </insert>
  <update id="update" parameterType="Formation">
  update formation set
  <!--    appId=#{appId}, name=#{name}, remark=#{remark}, image=#{image}, -->
  topicId=#{topicId} where id=#{id}
  </update>
  <delete id="delete" parameterType="int">
  delete from formation where id=#{id}
  </delete>
  <select id="findById" resultType="Formation" parameterType="int">
  select f.*,count(p.id) as praiseCount from formation f,formation_parise p where p.id=#{id} and f.id = p.formationId
  </select>
  <update id="updateCommentCount" parameterType="int">
  update formation set commentCount = commentCount + 1 where id=#{id}
  </update>
  <update id="updatePraiseCount" parameterType="map">
  update formation set praiseCount = praiseCount+1 where id=#{id}
  </update>
  </mapper>
   
  
  
• 　　在java中调用：首先需要在bean中引用factory对象：
  
  @Resource
  private SqlSessionTemplate sqlSessionTemplate;
   
  
• 
  然后使用名称调用
  Map<String,Object> queryMap = new HashMap<String, Object>();
  queryMap.put("appId", appId);
  queryMap.put("offset", (pageNum-1) * pageSize);
  queryMap.put("limit", pageSize);
  List<Formation> list = (List<Formation>) sqlSessionTemplate.selectList("Formation.list", queryMap);
   
  
• 需要注意的是，传递的参数在mybatis里可以使用#{变量名}，标示带引号的，比如appId=#{appId},和${变量名},不带引号的，比如order by f.${query}.这一点要灵活运用。