lazzyRabbit hello

最近的项目一直在使用Hibernate来作为数据持久化的框架,想着复习一下Mybatis,所以整理了一个SSM的demo。

首先在Spring配置文件中配置Mybatis部分

	<!-- 引入配置文件 -->
	<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">  
		<property name="location" value="classpath:config/jdbc.properties"></property>  
	</bean>
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">  
		<property name="driverClassName" value="${driver}"></property>  
		<property name="url" value="${url}"></property>  
		<property name="username" value="${username}"></property>  
		<property name="password" value="${password}"></property>  
		<!-- 初始化连接大小 -->  
		<property name="initialSize" value="${initialSize}"></property>  
		<!-- 连接池最大数量 -->  
		<property name="maxActive" value="${maxActive}"></property>  
		<!-- 连接池最大空闲 -->  
		<property name="maxIdle" value="${maxIdle}"></property>  
		<!-- 连接池最小空闲 -->  
		<property name="minIdle" value="${minIdle}"></property>  
		<!-- 获取连接最大等待时间 -->  
		<property name="maxWait" value="${maxWait}"></property>  
	</bean>

	<!-- spring和MyBatis完美整合,不需要mybatis的配置映射文件 -->  
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
		<property name="dataSource" ref="dataSource"></property>  
		<!-- 自动扫描mapping.xml文件 -->  
		<property name="mapperLocations" value="classpath:com/mlxp/mapping/*.xml"></property>  
	</bean>  
  
	<!-- DAO接口所在包名,Spring会自动查找其下的类 -->  
	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">  
		<property name="basePackage" value="com.mlxp.dao"></property>  
		<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>  
	</bean>  

	<!-- (事务管理)transaction manager, use JtaTransactionManager for global tx -->  
	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">  
		<property name="dataSource" ref="dataSource"></property>  
	</bean>
	<tx:annotation-driven transaction-manager="transactionManager"></tx:annotation-driven>

下面是jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/book_demo
username=root
password=root
#定义初始连接数
initialSize=0
#定义最大连接数
maxActive=20
#定义最大空闲
maxIdle=20
#定义最小空闲
minIdle=1
#定义最长等待时间
maxWait=30000

实体类(书和预定情况)

public class Book {
    private Long bookId;

    private String name;

    private Integer number;

    public Long getBookId() {
        return bookId;
    }
    //省略get set
}
public class Appointment {
    
    private Long bookId;

    private Long studentId;

    private Date appointTime;

    private Book book;

    //省略get set
}

Dao

<!--?xml version="1.0" encoding="UTF-8"?-->

<mapper namespace="com.mlxp.dao.BookMapper">
  <resultmap id="BaseResultMap" type="com.mlxp.model.Book">
    <id column="book_id" jdbctype="BIGINT" property="bookId"></id>
    <result column="name" jdbctype="VARCHAR" property="name"></result>
    <result column="number" jdbctype="INTEGER" property="number"></result>
  </resultmap>
  <sql id="Base_Column_List">
    book_id, name, number
  </sql>
  <select id="selectByPrimaryKey" parametertype="java.lang.Long" resultmap="BaseResultMap">
    select 
    
    from book
    where book_id = #{bookId,jdbcType=BIGINT}
  </select>
    <select id="listAll" parametertype="java.lang.Long" resultmap="BaseResultMap">
    select 
    
    from book
    order by book_id
    limit #{offset}, #{limit}
  </select>
  <select id="listBySelective" parametertype="com.mlxp.model.Book" resultmap="BaseResultMap">
    select 
    
    from book
    
        
        book_id like CONCAT('%',#{book.bookId,jdbcType=BIGINT},'%')
      
      
        and name like CONCAT('%',#{book.name,jdbcType=BIGINT},'%')
      
      
        and number like CONCAT('%',#{book.number,jdbcType=BIGINT},'%')
      
     
         order by book_id
    limit #{offset}, #{limit}
  </select>
  <update id="reduceNumber">
        UPDATE book
        SET number = number - 1
        WHERE
            book_id = #{bookId}
        AND number &gt; 0
  </update>
  <delete id="deleteByPrimaryKey" parametertype="java.lang.Long">
    delete from book
    where book_id = #{bookId,jdbcType=BIGINT}
  </delete>
  <insert id="insert" parametertype="com.mlxp.model.Book">
    insert into book (book_id, name, number
      )
    values (#{bookId,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}, #{number,jdbcType=INTEGER}
      )
  </insert>
  <insert id="insertSelective" parametertype="com.mlxp.model.Book">
    insert into book
    <trim prefix="(" suffix=")" suffixoverrides=",">
      <if test="bookId != null">
        book_id,
      </if>
      <if test="name != null">
        name,
      </if>
      <if test="number != null">
        number,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixoverrides=",">
      <if test="bookId != null">
        #{bookId,jdbcType=BIGINT},
      </if>
      <if test="name != null">
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="number != null">
        #{number,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parametertype="com.mlxp.model.Book">
    update book
    <set>
      <if test="name != null">
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="number != null">
        number = #{number,jdbcType=INTEGER},
      </if>
    </set>
    where book_id = #{bookId,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parametertype="com.mlxp.model.Book">
    update book
    set name = #{name,jdbcType=VARCHAR},
      number = #{number,jdbcType=INTEGER}
    where book_id = #{bookId,jdbcType=BIGINT}
  </update>
</mapper>
package com.mlxp.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.mlxp.model.Book;

public interface BookMapper {
    int deleteByPrimaryKey(Long bookId);

    int insert(Book record);

    int insertSelective(Book record);

    Book selectByPrimaryKey(Long bookId);

    int updateByPrimaryKeySelective(Book record);

    int updateByPrimaryKey(Book record);
    
    List<book> listAll(@Param("offset") int offset, @Param("limit") int limit);

    List<book> listBySelective(@Param("book")Book book,@Param("offset") int offset, @Param("limit") int limit);

    int reduceNumber(long bookId);
}</book></book>
<!--?xml version="1.0" encoding="UTF-8"?-->

<mapper namespace="com.mlxp.dao.AppointmentMapper">
  <resultmap id="BaseResultMap" type="com.mlxp.model.Appointment">
    <id column="book_id" jdbctype="BIGINT" property="bookId"></id>
    <id column="student_id" jdbctype="BIGINT" property="studentId"></id>
    <result column="appoint_time" jdbctype="TIMESTAMP" property="appointTime"></result>
  </resultmap>
  <sql id="Base_Column_List">
    book_id, student_id, appoint_time
  </sql>
  <select id="selectByPrimaryKey" parametertype="com.mlxp.model.Appointment" resulttype="com.mlxp.model.Appointment">
    select 
    
    from appointment
    where book_id = #{bookId,jdbcType=BIGINT}
      and student_id = #{studentId,jdbcType=BIGINT}
  </select>
  <select id="queryByKeyWithBook" resulttype="com.mlxp.model.Appointment">
      <!-- 如何告诉MyBatis把结果映射到Appointment同时映射book属性 -->
      <!-- 可以自由控制SQL -->
      SELECT
          a.book_id as bookId,
          a.student_id as studentId,
          a.appoint_time as appointTime,
          b.book_id "book.bookId",
          b.name "book.name",
          b.number "book.number"
      FROM
          appointment a
      INNER JOIN book b ON a.book_id = b.book_id
      WHERE
          a.book_id = #{bookId}
      AND a.student_id = #{studentId}
  </select>
    <select id="listAll" resulttype="com.mlxp.model.Appointment">
      <!-- 如何告诉MyBatis把结果映射到Appointment同时映射book属性 -->
      <!-- 可以自由控制SQL -->
      SELECT
          a.book_id as bookId,
          a.student_id as studentId,
          a.appoint_time as appointTime,
          b.book_id "book.bookId",
          b.name "book.name",
          b.number "book.number"
      FROM
          appointment a
      INNER JOIN book b ON a.book_id = b.book_id
      order by a.appoint_time
      limit #{offset}, #{limit}
  </select>
  <delete id="deleteByPrimaryKey" parametertype="com.mlxp.model.Appointment">
    delete from appointment
    where book_id = #{bookId,jdbcType=BIGINT}
      and student_id = #{studentId,jdbcType=BIGINT}
  </delete>
  <insert id="insert" parametertype="com.mlxp.model.Appointment">
    insert into appointment (book_id, student_id, appoint_time
      )
    values (#{bookId,jdbcType=BIGINT}, #{studentId,jdbcType=BIGINT}, #{appointTime,jdbcType=TIMESTAMP}
      )
  </insert>
  <insert id="insertAppointment">
        <!-- ignore 主键冲突,报错 -->
        INSERT ignore INTO appointment (book_id, student_id)
        VALUES (#{bookId}, #{studentId})
    </insert>
  <insert id="insertSelective" parametertype="com.mlxp.model.Appointment">
    insert ignore into appointment
    <trim prefix="(" suffix=")" suffixoverrides=",">
      <if test="bookId != null">
        book_id,
      </if>
      <if test="studentId != null">
        student_id,
      </if>
      <if test="appointTime != null">
        appoint_time,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixoverrides=",">
      <if test="bookId != null">
        #{bookId,jdbcType=BIGINT},
      </if>
      <if test="studentId != null">
        #{studentId,jdbcType=BIGINT},
      </if>
      <if test="appointTime != null">
        #{appointTime,jdbcType=TIMESTAMP},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parametertype="com.mlxp.model.Appointment">
    update appointment
    <set>
      <if test="appointTime != null">
        appoint_time = #{appointTime,jdbcType=TIMESTAMP},
      </if>
    </set>
    where book_id = #{bookId,jdbcType=BIGINT}
      and student_id = #{studentId,jdbcType=BIGINT}
  </update>
  <update id="updateByPrimaryKey" parametertype="com.mlxp.model.Appointment">
    update appointment
    set appoint_time = #{appointTime,jdbcType=TIMESTAMP}
    where book_id = #{bookId,jdbcType=BIGINT}
      and student_id = #{studentId,jdbcType=BIGINT}
  </update>
</mapper>
package com.mlxp.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.mlxp.model.Appointment;

public interface AppointmentMapper {
    int deleteByPrimaryKey(Appointment key);

    int insertAppointment(@Param("bookId") long bookId, @Param("studentId") long studentId);

    int insertSelective(Appointment record);

    Appointment selectByPrimaryKey(Appointment key);

    int updateByPrimaryKeySelective(Appointment record);

    Appointment queryByKeyWithBook(@Param("bookId") long bookId, @Param("studentId") long studentId);
    
    List<appointment> listAll(@Param("offset") int offset, @Param("limit") int limit);
//    Appointment queryByKeyWithBook(Appointment record);

}</appointment>

之后就是根据功能来实现service以及controller了,这里我就不贴代码了。

最终实现的效果如下图


#Point

1、在Dao方法中的参数为多个时,需要为参数加@Param注解

2、在不定义相对应resultMap使用关联查询时,要在sql语句中要为字段加别名,映射到实体类中

参考

http://blog.csdn.net/qq598535550/article/details/51703190

http://blog.csdn.net/wrp920227/article/details/54017835

本文demo

http://download.csdn.net/download/lazyrabbitlll/10242100