JavaPersistenceWithMyBatis3笔记-第4章SQL Mappers Using Annotations-001

一、

1.Mapper

/**
 * 
 */
package com.mybatis3.mappers;

import org.apache.ibatis.annotations.Select;

import com.mybatis3.domain.Address;

/**
 * @author Siva
 *
 */
public interface AddressMapper 
{
    @Select("select addr_id as addrId, street, city, state, zip, country from addresses where addr_id=#{id}")
    Address selectAddressById(int id);
}

package com.mybatis3.mappers;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.mybatis3.domain.Student;



/**
 * @author Siva
 *
 */
public interface StudentMapper
{
    
    @Select("select * from students")
    @Results({
            @Result(id=true, column="stud_id", property="studId"),
            @Result(column="name", property="name"),
            @Result(column="email", property="email"),
            @Result(column="addr_id", property="address.addrId")            
    })
    List<Student> findAllStudents();

    @Select("select stud_id as studId, name, email, addr_id as 'address.addrId', phone from students")
    List<Map<String,Object>> findAllStudentsMap();
    
    @Select("select stud_id as studId, name, email, addr_id as 'address.addrId', phone from students where stud_id=#{id}")
    Student findStudentById(Integer id);
    
    @Select("select stud_id as studId, name, email, addr_id as 'address.addrId', phone from students where stud_id=#{id}")
    Map<String,Object> findStudentMapById(Integer id);

    @Select("select stud_id, name, email, a.addr_id, street, city, state, zip, country"+
          " FROM students s left outer join addresses a on s.addr_id=a.addr_id"+
        " where stud_id=#{studId} ")
    @ResultMap("com.mybatis3.mappers.StudentMapper.StudentWithAddressResult")
    Student selectStudentWithAddress(int studId);
    
    @Insert("insert into students(name,email,addr_id, phone) values(#{name},#{email},#{address.addrId},#{phone})")
    @Options(useGeneratedKeys=true, keyProperty="studId")
    void insertStudent(Student student);
    
    @Insert("insert into students(name,email,addr_id, phone) values(#{name},#{email},#{address.addrId},#{phone})")
    @Options(useGeneratedKeys=true, keyProperty="studId")
    void insertStudentWithMap(Map<String, Object> map);

    @Update("update students set name=#{name}, email=#{email}, phone=#{phone} where stud_id=#{studId}")
    void updateStudent(Student student);
    
    @Delete("delete from students where stud_id=#{studId}")
    int deleteStudent(int studId);
    
}

/**
 * 
 */
package com.mybatis3.mappers;

import java.util.List;

import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;

import com.mybatis3.domain.Course;
import com.mybatis3.domain.Tutor;
import com.mybatis3.sqlproviders.TutorDynaSqlProvider;


/**
 * @author Siva
 *
 */

public interface TutorMapper 
{
    
    @Select("select * from courses where tutor_id=#{tutorId}")
    @ResultMap("com.mybatis3.mappers.TutorMapper.CourseResult")
    List<Course> selectCoursesByTutorId(int tutorId);
    
    @Select("SELECT tutor_id, t.name as tutor_name, email, addr_id FROM tutors t where t.tutor_id=#{tutorId}")
    @Results({
        @Result(id=true, column="tutor_id", property="tutorId"),
        @Result(column="tutor_name", property="name"),
        @Result(column="email", property="email"),
        @Result(property="address", column="addr_id",
                one=@One(select="com.mybatis3.mappers.AddressMapper.selectAddressById")),        
        @Result(property="courses", column="tutor_id",
                many=@Many(select="com.mybatis3.mappers.TutorMapper.selectCoursesByTutorId"))        
    })
    Tutor selectTutorWithCoursesById(int tutorId);
    
    @SelectProvider(type=TutorDynaSqlProvider.class, method="findAllTutorsSql")
    List<Tutor> findAllTutors();
    
    @SelectProvider(type=TutorDynaSqlProvider.class, method="findTutorByIdSql")
    Tutor findTutorById(int tutorId);
    
    @SelectProvider(type=TutorDynaSqlProvider.class, method="findTutorByNameAndEmailSql")
    Tutor findTutorByNameAndEmail(@Param("name")String name, @Param("email")String email);
    
    @InsertProvider(type=TutorDynaSqlProvider.class, method="insertTutor")
    @Options(useGeneratedKeys=true, keyProperty="tutorId")
    int insertTutor(Tutor tutor);
    
    @UpdateProvider(type=TutorDynaSqlProvider.class, method="updateTutor")
    int updateTutor(Tutor tutor);
    
    @DeleteProvider(type=TutorDynaSqlProvider.class, method="deleteTutor")
    int deleteTutor(int tutorId);
        
    @SelectProvider(type=TutorDynaSqlProvider.class, method="selectTutorById")
    @ResultMap("com.mybatis3.mappers.TutorMapper.TutorResult")
    Tutor selectTutorById(int tutorId);
    
    
}

2.Service

/**
 * 
 */
package com.mybatis3.services;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.mybatis3.domain.Tutor;
import com.mybatis3.mappers.TutorMapper;
import com.mybatis3.util.MyBatisUtil;


/**
 * @author Siva
 *
 */

public class TutorService 
{
    public List<Tutor> findAllTutors()
    {
        SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
        try {
            TutorMapper mapper = sqlSession.getMapper(TutorMapper.class);
            return mapper.findAllTutors();
        } finally {
            sqlSession.close();
        }
    }
    
    public Tutor findTutorById(int tutorId)
    {
        SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
        try {
            TutorMapper mapper = sqlSession.getMapper(TutorMapper.class);
            return mapper.findTutorById(tutorId);
        } finally {
            sqlSession.close();
        }
    }
    
    public Tutor findTutorByNameAndEmail(String name, String email)
    {
        SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
        try {
            TutorMapper mapper = sqlSession.getMapper(TutorMapper.class);
            return mapper.findTutorByNameAndEmail(name, email);
        } finally {
            sqlSession.close();
        }
    }
    
    public Tutor createTutor(Tutor tutor)
    {
        SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
        try {
            TutorMapper mapper = sqlSession.getMapper(TutorMapper.class);
            mapper.insertTutor(tutor);
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
        return tutor;
    }
    
    public Tutor updateTutor(Tutor tutor)
    {
        SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
        try {
            TutorMapper mapper = sqlSession.getMapper(TutorMapper.class);
            mapper.updateTutor(tutor);
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
        return tutor;
    }
    
    public boolean deleteTutor(int tutorId)
    {
        boolean deleted = false;
        SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
        try {
            TutorMapper mapper = sqlSession.getMapper(TutorMapper.class);
            int nor = mapper.deleteTutor(tutorId);
            deleted = (nor == 1);
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
        return deleted;
    }
    
    public Tutor selectTutorById(int tutorId)
    {
        SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
        try {
            TutorMapper mapper = sqlSession.getMapper(TutorMapper.class);
            return mapper.selectTutorById(tutorId);
        } finally {
            sqlSession.close();
        }
    }
    
    public Tutor selectTutorWithCoursesById(int tutorId) {
        SqlSession sqlSession = MyBatisUtil.getSqlSessionFactory().openSession();
        try {
            TutorMapper mapper = sqlSession.getMapper(TutorMapper.class);
            return mapper.selectTutorWithCoursesById(tutorId);
        } 
        
        finally {
            sqlSession.close();
        }
    }
}

3.Domain

4.辅助类

/**
 * 
 */
package com.mybatis3.sqlproviders;


import java.util.Map;

import org.apache.ibatis.jdbc.SQL;

import com.mybatis3.domain.Tutor;
/**
 * @author Siva
 *
 */
public class TutorDynaSqlProvider 
{

    public String findAllTutorsSql() 
    {
        return new SQL() {{
            SELECT("tutor_id as tutorId, name, email");
            FROM("tutors");
          }}.toString();
    }
    
    public String findTutorByIdSql(final int tutorId) 
    {
        /*return new SQL() {{
            SELECT("tutor_id as tutorId, name, email");
            FROM("tutors");
            WHERE("tutor_id = #{tutorId}");
        }}.toString();*/

        return new SQL() {{
            SELECT("tutor_id as tutorId, name, email");
            FROM("tutors");
            WHERE("tutor_id="+tutorId);
        }}.toString();
    }
    
    
    public String findTutorByNameAndEmailSql(Map<String, Object> map) 
    {
        String name = (String) map.get("name");
        String email = (String) map.get("email");
        System.err.println(name+":"+email);
        
        return new SQL() {{
            SELECT("tutor_id as tutorId, name, email");
            FROM("tutors");
            WHERE("name=#{name} AND email=#{email}");
        }}.toString();
    }
    
    public String insertTutor(final Tutor tutor) {
        
        return new SQL() {{
            INSERT_INTO("TUTORS");
            
            if (tutor.getName() != null) {
                VALUES("NAME", "#{name}");
            }
            
            if (tutor.getEmail() != null) {
                VALUES("EMAIL", "#{email}");
            }
        }}.toString();
        
    }
    
    public String updateTutor(final Tutor tutor) 
    {
        
        return new SQL() {{
            UPDATE("TUTORS");
            
            if (tutor.getName() != null) {
                SET("NAME = #{name}");
            }
            
            if (tutor.getEmail() != null) {
                SET("EMAIL = #{email}");
            }
            WHERE("TUTOR_ID = #{tutorId}");
        }}.toString();
    }
    
    public String deleteTutor(int tutorId) 
    {
        
        return new SQL() {{
            DELETE_FROM("TUTORS");
            WHERE("TUTOR_ID = #{tutorId}");
        }}.toString();
        
    }
    
    public String selectTutorById() 
    {    
        return new SQL() {{
            SELECT("t.tutor_id, t.name as tutor_name, email");
            SELECT("a.addr_id, street, city, state, zip, country");
            SELECT("course_id, c.name as course_name, description, start_date, end_date");
            FROM("TUTORS t");
            LEFT_OUTER_JOIN("addresses a on t.addr_id=a.addr_id");
            LEFT_OUTER_JOIN("courses c on t.tutor_id=c.tutor_id");
            WHERE("t.TUTOR_ID = #{id}");
        }}.toString();

        
    }
}

5.配置及资源文件

(1)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>

      <properties resource="application.properties"/>
        
      <typeAliases>
          <package name="com.mybatis3.domain"/>
      </typeAliases>
      <typeHandlers>
          <typeHandler handler="com.mybatis3.typehandlers.PhoneTypeHandler"/>
      </typeHandlers>
    
      <environments default="development">
        <environment id="development">
          <transactionManager type="JDBC"/>
          <dataSource type="POOLED">
            <property name="driver" value="${jdbc.driverClassName}"/>
            <property name="url" value="${jdbc.url}"/>
            <property name="username" value="${jdbc.username}"/>
            <property name="password" value="${jdbc.password}"/>
          </dataSource>
        </environment>
      </environments>
      
      <mappers>
          <package name="com.mybatis3.mappers"/>
      </mappers>      
      
</configuration>

(2)StudentMapper.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="com.mybatis3.mappers.StudentMapper">
    
    
    <resultMap type="Address" id="AddressResult">
          <id property="addrId" column="addr_id"/>
        <result property="street" column="street"/>
        <result property="city" column="city"/>
        <result property="state" column="state"/>
        <result property="zip" column="zip"/>
        <result property="country" column="country"/>
      </resultMap>
      
      <resultMap type="Student" id="StudentWithAddressResult">
        <id     property="studId" column="stud_id"/>
        <result property="name" column="name"/>
        <result property="email" column="email"/>
        <association property="address" resultMap="AddressResult"/>
    </resultMap>
    
    
</mapper>

(3)TutorMapper.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="com.mybatis3.mappers.TutorMapper">
              
      <resultMap type="Address" id="AddressResult">
          <id property="addrId" column="addr_id"/>
        <result property="street" column="street"/>
        <result property="city" column="city"/>
        <result property="state" column="state"/>
        <result property="zip" column="zip"/>
        <result property="country" column="country"/>
      </resultMap>
      
      <resultMap type="Course" id="CourseResult">
          <id     column="course_id" property="courseId"/>
          <result column="course_name" property="name"/>
          <result column="description" property="description"/>
          <result column="start_date" property="startDate"/>
          <result column="end_date" property="endDate"/>
      </resultMap>
      
      <resultMap type="Tutor" id="TutorResult">
          <id     column="tutor_id" property="tutorId"/>
          <result column="tutor_name" property="name"/>
          <result column="email" property="email"/>
          <association property="address" resultMap="AddressResult"/>
          <collection property="courses"  resultMap="CourseResult"></collection>
      </resultMap>
      
</mapper>

6.测试文件

package com.mybatis3.services;

import static org.junit.Assert.*;

import java.util.List;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import com.mybatis3.domain.Tutor;

public class TutorServiceTest 
{

    private static TutorService tutorService;
    
    @BeforeClass
    public static void setup() {
        tutorService = new TutorService();
        TestDataPopulator.initDatabase();
    }
    
    @AfterClass
    public static void teardown() {
        tutorService = null;
    }
    
    @Test
    public void testFindAllTutors() {
        List<Tutor> tutors = tutorService.findAllTutors();
        assertNotNull(tutors);
        for (Tutor tutor : tutors)
        {
            System.err.println(tutor);
        }
    }

    @Test
    public void testFindTutorById() {
        Tutor tutor = tutorService.findTutorById(1);
        assertNotNull(tutor);
        System.err.println(tutor);
    }

    @Test
    public void testFindTutorByNameAndEmail() {
        Tutor tutor = tutorService.findTutorByNameAndEmail("Paul", "paul@gmail.com");
        assertNotNull(tutor);
        System.err.println(tutor);
    }

    @Test
    public void testCreateTutor() {
        Tutor tutor = new Tutor();
        tutor.setName("siva");
        tutor.setEmail("siva@gmail.com");
        tutor = tutorService.createTutor(tutor);
        assertNotNull(tutor);
        System.err.println(tutor.getTutorId());
    }

    @Test
    public void testUpdateTutor() {
        Tutor tutor = new Tutor();
        tutor.setTutorId(1);
        tutor.setName("sivaprasad");
        tutor.setEmail("sivaprasad@gmail.com");
        tutor = tutorService.updateTutor(tutor);
        Tutor updTutor = tutorService.findTutorById(1);
        assertNotNull(updTutor);
        System.err.println(updTutor);
    }

    @Test
    public void testDeleteTutor() {
        boolean deleted = tutorService.deleteTutor(4);
           assertTrue(deleted);
    }

    @Test
    public void testSelectTutorById() {
        Tutor tutor = tutorService.selectTutorById(1);
        assertNotNull(tutor);
        System.err.println(tutor);
    }

    @Test
    public void testSelectTutorWithCoursesById() {
        Tutor tutor = tutorService.selectTutorWithCoursesById(1);
        assertNotNull(tutor);
        System.err.println(tutor);
    }

}