MyBatis学习笔记

1、工程搭建：

pom.xml:

    4.0.0

    com.emmaluo.mbatislearning
    mybatis
    1.0-SNAPSHOT
    
        
        
            junit
            junit
            4.12
            test
        
        
            org.slf4j
            slf4j-api
            1.7.5
        
        
            org.slf4j
            slf4j-log4j12
            1.7.5
        
        
            log4j
            log4j
            1.2.17
        
        
        
            org.mybatis
            mybatis
            3.4.0
        
        
            mysql
            mysql-connector-java
            5.1.30
        
        
        
            org.mybatis
            mybatis-spring
            1.3.0
        
        
            org.springframework
            spring-context-support
            4.3.0.RELEASE
        
        
            commons-logging
            commons-logging
            1.2
        
        
            org.springframework
            spring-jdbc
            4.3.0.RELEASE
        
        
            org.springframework
            spring-test
            4.3.0.RELEASE
            test
        
        
            org.aspectj
            aspectjrt
            1.7.2
        
        
            org.aspectj
            aspectjweaver
            1.7.2
        
        
            cglib
            cglib-nodep
            2.2
        
        
            commons-dbcp
            commons-dbcp
            1.4
        

    

mybatis-config.xml

知识点包括：environments default environment dataSource mappers mapper typeAliases                                  typeAliase typeHandlers typeHandler

jdbc.propertis

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/stock
jdbc.username=stock
jdbc.password=password

log4j.properties

log4j.rootLogger=INFO, stdout

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d [%-5p] %c - %m%n

log4j.logger.com.mybatis3=DEBUG

相关表的SQL文件

/*
MySQL Backup
Source Server Version: 5.7.12
Source Database: stock
Date: 2016/6/27 16:07:05
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
--  Table structure for `addresses`
-- ----------------------------
DROP TABLE IF EXISTS `addresses`;
CREATE TABLE `addresses` (
  `ADDR_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STREET` varchar(50) NOT NULL,
  `CITY` varchar(50) NOT NULL,
  `STATE` varchar(50) NOT NULL,
  `ZIP` varchar(10) DEFAULT NULL,
  `COUNTRY` varchar(50) NOT NULL,
  PRIMARY KEY (`ADDR_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Table structure for `courses`
-- ----------------------------
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
  `COURSE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(100) NOT NULL,
  `DESCRIPTION` varchar(512) DEFAULT NULL,
  `START_DATE` date DEFAULT NULL,
  `END_DATE` date DEFAULT NULL,
  `TUTOR_ID` int(11) NOT NULL,
  PRIMARY KEY (`COURSE_ID`),
  KEY `FK_COURSE_TUTOR` (`TUTOR_ID`),
  CONSTRAINT `FK_COURSE_TUTOR` FOREIGN KEY (`TUTOR_ID`) REFERENCES `tutors` (`TUTOR_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Table structure for `course_enrollment`
-- ----------------------------
DROP TABLE IF EXISTS `course_enrollment`;
CREATE TABLE `course_enrollment` (
  `COURSE_ID` int(11) NOT NULL,
  `STUD_ID` int(11) NOT NULL,
  PRIMARY KEY (`COURSE_ID`,`STUD_ID`),
  KEY `FK_ENROLLMENT_STUD` (`STUD_ID`),
  CONSTRAINT `FK_ENROLLMENT_COURSE` FOREIGN KEY (`COURSE_ID`) REFERENCES `courses` (`COURSE_ID`),
  CONSTRAINT `FK_ENROLLMENT_STUD` FOREIGN KEY (`STUD_ID`) REFERENCES `students` (`STUD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
--  Table structure for `students`
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `STUD_ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(50) NOT NULL,
  `EMAIL` varchar(50) NOT NULL,
  `PHONE` varchar(15) DEFAULT NULL,
  `DOB` date DEFAULT NULL,
  `BIO` longtext,
  `PIC` blob,
  `ADDR_ID` int(11) DEFAULT NULL,
  `GENDER` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`STUD_ID`),
  KEY `FK_STUDENTS_ADDR` (`ADDR_ID`),
  CONSTRAINT `FK_STUDENTS_ADDR` FOREIGN KEY (`ADDR_ID`) REFERENCES `addresses` (`ADDR_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=69 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Table structure for `tutors`
-- ----------------------------
DROP TABLE IF EXISTS `tutors`;
CREATE TABLE `tutors` (
  `TUTOR_ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(50) NOT NULL,
  `EMAIL` varchar(50) NOT NULL,
  `PHONE` varchar(15) DEFAULT NULL,
  `DOB` date DEFAULT NULL,
  `BIO` longtext,
  `PIC` blob,
  `ADDR_ID` int(11) DEFAULT NULL,
  PRIMARY KEY (`TUTOR_ID`),
  KEY `FK_TUTORS_ADDR` (`ADDR_ID`),
  CONSTRAINT `FK_TUTORS_ADDR` FOREIGN KEY (`ADDR_ID`) REFERENCES `addresses` (`ADDR_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Table structure for `user_pics`
-- ----------------------------
DROP TABLE IF EXISTS `user_pics`;
CREATE TABLE `user_pics` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(50) DEFAULT NULL,
  `PIC` blob,
  `BIO` longtext,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

-- ----------------------------
--  Records 
-- ----------------------------
INSERT INTO `addresses` VALUES ('1','4891 Pacific Hwy','San Diego','CA','92110','San Diego'), ('2','2400 N Jefferson St','Perry','FL','32347','Taylor'), ('3','710 N Cable Rd','Lima','OH','45825','Allen'), ('4','5108 W Gore Blvd','Lawton','OK','32365','Comanche');
INSERT INTO `courses` VALUES ('1','Quickstart Core Java','Core Java Programming','2013-03-01','2013-04-15','1'), ('2','Quickstart JavaEE6','Enterprise App Development using JavaEE6','2013-04-01','2013-08-30','1'), ('3','MyBatis3 Premier','MyBatis 3 framework','2013-06-01','2013-07-15','2');
INSERT INTO `course_enrollment` VALUES ('1','1'), ('1','2'), ('2','2');
INSERT INTO `students` VALUES ('1','Timothy','timothy@gmail.com','123-123-1234','1988-04-25',NULL,NULL,'3',NULL), ('2','Douglas','douglas@gmail.com','789-456-1234','1990-08-15',NULL,NULL,'4',NULL), ('42','student_3','student_3@gmail.com','123-123-1239',NULL,NULL,NULL,NULL,'0'), ('43','student_3','student_3@gmail.com','123-123-1239',NULL,NULL,NULL,NULL,'0'), ('44','student_3','student_3@gmail.com','123-123-1239',NULL,NULL,NULL,NULL,'0'), ('55','stud_1466947477396','stud_1466947477396@gmail.com',NULL,NULL,NULL,NULL,NULL,NULL), ('61','stud_1466947633475','stud_1466947633475@gmail.com',NULL,NULL,NULL,NULL,NULL,NULL), ('62','stud_1466949446311','stud_1466949446311@gmail.com',NULL,NULL,NULL,NULL,NULL,NULL), ('63','stud_1467014304497','stud_1467014304497@gmail.com',NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO `tutors` VALUES ('1','John','john@gmail.com','111-222-3333','1980-05-20',NULL,NULL,'1'), ('2','Paul','paul@gmail.com','123-321-4444','1981-03-15',NULL,NULL,'2');

2、包结构：

 

表映射的JAVA对象

 

Mapper接口

 

service包

 

typehandler与model包中的PhoneNumber关系

 

test包与service包关系

 

工程UML关系全图

 

3、查询、插入、更新mapper的xml文件配置

创建mapper文件

    
        
        
        
        
        
        
    

    
        
        
        
        
    
    
        
        
        
        
        
        
        
    
    
        SELECT STUD_ID, NAME, EMAIL, PHONE,dob, 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}
    
    
        SELECT * from students
    
    
        SELECT STUD_ID as studId,NAME,EMAIL,DOB,PHONE,gender
        FROM STUDENTS WHERE STUD_ID=#{id}
    
    
        SELECT STUD_ID,NAME,EMAIL,DOB,PHONE,gender
        FROM STUDENTS WHERE name = #{param1} and email=#{param2}
    
    
        insert into students(name,email,dob)
        values(#{name},#{email},#{dob})
    
    
        insert into students(name,email,dob,PHONE)
        values(#{name},#{email},#{dob},#{phone})
    
    
        insert into students(name,email,addr_id, phone,gender)
        values(#{name},#{email},#{address.addrId},#{phone},#{gender})
    
    
        DELETE FROM students
        WHERE stud_id=#{id}
    
    
        update students
        
            name=#{name},
            email=#{email},

        
        where stud_id=#{studId}
    

知识点包括：mapper namespace属性（值为mapper接口全限定名） select parameteType resultType resultMap insert useGenerateKeys keyProperty selectKey keyProperty resultType order update parameterType delete parameterType association collection property column select

4、数据查询接口层

Mapper接口

package com.emmaluo.mybatislearning.IDAO;

import com.emmaluo.mybatislearning.model.Student;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * Created by zcluo on 2016/6/23.
 */
@Component
public interface StudentMapper {
    List findAllStudents(RowBounds rowBounds);
    List findAllStudents();
    List findAllStudentsByNameEmail(String name,String email);
    Student findStudentById(Integer id);
    int insertStudent(Student student);
    int insertStudentWithPhone(Student student);
    int insertStudentWithGender(Student student);
    int deleteStudentById(Integer id);
    Student selectStudentWithAddress(Integer studId);
    int updateStudent(Student student);

}

5、服务层

服务实现

package com.emmaluo.mybatislearning.service;

import com.emmaluo.mybatislearning.IDAO.StudentMapper;
import com.emmaluo.mybatislearning.model.Student;

import com.emmaluo.mybatislearning.util.MyBatisSqlSessionFactory;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;

/**
 * Created by zcluo on 2016/6/23.
 */
public class StudentService {
    private Logger logger = LoggerFactory.getLogger(getClass());
    public List findAllStudents(){
        SqlSession sqlSession = MyBatisSqlSessionFactory.openSession();
        try {
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            return studentMapper.findAllStudents();
        }
        finally {
            sqlSession.close();
        }
    }

    public Student findStudentById(Integer studId){
        logger.debug("Select Student By ID :{}",studId);
        SqlSession sqlSession = MyBatisSqlSessionFactory.openSession();
        try{
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            return studentMapper.findStudentById(studId);
        }
        finally {
            sqlSession.close();
        }
    }

    public Student findStudentWithAddress(Integer studId){
        logger.debug("Select Student By ID :{}",studId);
        SqlSession sqlSession = MyBatisSqlSessionFactory.openSession();
        try{
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            return studentMapper.selectStudentWithAddress(studId);
        }
        finally {
            sqlSession.close();
        }
    }

    public List findStudentsWithNameAndEmail(String name,String email){
        logger.debug("Select Student by Name: {} and Email: {}",name,email);
        SqlSession sqlSession = MyBatisSqlSessionFactory.openSession();
        try{
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            return studentMapper.findAllStudentsByNameEmail(name,email);
        }
        finally {
            sqlSession.close();
        }
    }

    public List findStudentsAllWithPagenation(int offset,int limits){
        logger.debug("Select Student All with Pagenation!");
        SqlSession sqlSession = MyBatisSqlSessionFactory.openSession();
        try {
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            RowBounds rowBounds = new RowBounds(offset,limits);
            return studentMapper.findAllStudents(rowBounds);
        }
        finally {
            sqlSession.close();
        }
    }

    public int  createStudent(Student student) {
        SqlSession sqlSession = MyBatisSqlSessionFactory.openSession();
        int insertRows = 0;
        try {
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            insertRows = studentMapper.insertStudent(student);
            sqlSession.commit();

        } finally {
            sqlSession.close();
            return insertRows;
        }
    }

    public int insertStudentWithPhone(Student student){
        SqlSession sqlSession = MyBatisSqlSessionFactory.openSession();
        int insertRows = 0;
        try {
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            insertRows = studentMapper.insertStudentWithPhone(student);
            sqlSession.commit();

        } finally {
            sqlSession.close();
            return insertRows;
        }
    }

    public int insertStudentWithGender(Student student){
        SqlSession sqlSession = MyBatisSqlSessionFactory.openSession();
        int insertRows = 0;
        try {
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            insertRows = studentMapper.insertStudentWithGender(student);
            sqlSession.commit();

        } finally {
            sqlSession.close();
            return insertRows;
        }
    }

    public int deleteStudentById(Integer studId) {
        SqlSession sqlSession = MyBatisSqlSessionFactory.openSession();
        int deleteRows = 0;
        try {
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            deleteRows = studentMapper.deleteStudentById(studId);
            sqlSession.commit();
        } finally {
            sqlSession.close();
            return deleteRows;
        }
    }

    public int updateStudent(Student student) {
        SqlSession sqlSession = MyBatisSqlSessionFactory.openSession();
        int updateRows = 0;
        try{
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            updateRows = studentMapper.updateStudent(student);
            sqlSession.commit();
        }
        finally {
            sqlSession.close();
            return updateRows;
        }
    }
}

6、测试

测试案例

package com.emmaluo.mybatislearning.test;

import com.emmaluo.mybatislearning.model.Gender;
import com.emmaluo.mybatislearning.model.PhoneNumber;
import com.emmaluo.mybatislearning.model.Student;

import com.emmaluo.mybatislearning.service.StudentService;
import org.apache.ibatis.session.RowBounds;
import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;

import java.util.Date;
import java.util.List;

/**
 * Created by zcluo on 2016/6/23.
 */
public class StudentServiceTest {

    private static StudentService studentService;
    @Before
    public void setUp() throws Exception {

        studentService = new StudentService();

    }

    @After
    public void tearDown() throws Exception {

        studentService = null;
    }

    @Test
    public void findAllStudents() throws Exception {

        List students = studentService.findAllStudents();
        Assert.assertNotNull(students);
        for(Student student : students){
            System.out.println(student);
        }

    }

    @Test
    public void findStudentsAllWithPagenation() throws Exception {

        int offset=0;
        int limits=2;
        List students = studentService.findStudentsAllWithPagenation(offset,limits);
        Assert.assertNotNull(students);
        Assert.assertTrue(students.size()<=limits);
        for(Student student : students){
            System.out.println(student);
        }
    }

    @Test
    public void findStudentById() throws Exception {

        Student student = studentService.findStudentById(1);
        Assert.assertNotNull(student);
        System.out.println(student);
    }

    @Test
    public void findStudentsWithNameAndEmail() throws Exception {

        List students = studentService.findStudentsWithNameAndEmail("student_3","student_3@gmail.com");
        Assert.assertNotNull(students);
        System.out.println(students);

    }

    @Test
    public void findStudentWithAddress() throws Exception {

        Student student = studentService.findStudentWithAddress(1);
        Assert.assertNotNull(student);
        System.out.println(student);

    }

    @Test
    public void createStudent() throws Exception {
        Student student = new Student();
        int id = 3;
        //student.setStudId(id);
        student.setName("student_"+id);
        student.setEmail("student_"+id+"@gmail.com");
        student.setDob(new Date());
        int rowInserted = studentService.createStudent(student);
        id = student.getStudId();
        Student newStudent = studentService.findStudentById(id);
        Assert.assertNotNull(newStudent);
        Assert.assertEquals(1,rowInserted);
        System.out.println(newStudent);
        studentService.deleteStudentById(id);

    }

    @Test
    public void insertStudentWithPhone() throws Exception {

        Student student = new Student();
        int id = 3;
        //student.setStudId(id);
        student.setName("student_"+id);
        student.setEmail("student_"+id+"@gmail.com");
        student.setDob(new Date());
        PhoneNumber phone = new PhoneNumber("123-123-1239");
        student.setPhone(phone);
        int rowInserted = studentService.insertStudentWithPhone(student);
        id = student.getStudId();
        Student newStudent = studentService.findStudentById(id);
        Assert.assertNotNull(newStudent);
        Assert.assertEquals(1,rowInserted);
        System.out.println(newStudent);
        studentService.deleteStudentById(id);
    }

    @Test
    public void insertStudentWithGender() throws Exception {

        Student student = new Student();
        int id = 3;
        //student.setStudId(id);
        student.setName("student_"+id);
        student.setEmail("student_"+id+"@gmail.com");
        student.setDob(new Date());
        PhoneNumber phone = new PhoneNumber("123-123-1239");
        student.setPhone(phone);
        Gender gender = Gender.FEMALE;
        student.setGender(gender);
        int rowInserted = studentService.insertStudentWithGender(student);
        id = student.getStudId();
        Student newStudent = studentService.findStudentById(id);
        Assert.assertNotNull(newStudent);
        Assert.assertEquals(1,rowInserted);
        System.out.println(newStudent);
        studentService.deleteStudentById(id);
    }

    @Test
    public void updateStudent() throws Exception {

        Student student = new Student();
        int id = 0;
        //student.setStudId(id);
        student.setName("student_"+id);
        student.setEmail("student_"+id+"@gmail.com");
        student.setDob(new Date());
        int rowInserted = studentService.createStudent(student);
        id = student.getStudId();
        student.setName("student_" + id);
        student.setEmail("student_"+id+"@gmail.com");
        int rowUpdated = studentService.updateStudent(student);
        Assert.assertEquals(1,rowUpdated);
        student = studentService.findStudentById(id);
        System.out.println(student);
        int rowDeleted = studentService.deleteStudentById(id);
        Assert.assertEquals(1,rowDeleted);

    }

    @Test
    public void deleteStudentById() throws Exception {
        Student student = new Student();
        int id = 3;
        //student.setStudId(id);
        student.setName("student_"+id);
        student.setEmail("student_"+id+"@gmail.com");
        student.setDob(new Date());
        studentService.createStudent(student);
        int rowdeleted = studentService.deleteStudentById(student.getStudId());
        Student s = studentService.findStudentById(student.getStudId());
        Assert.assertNull(s);
        Assert.assertEquals(1,rowdeleted);

    }

}

7、spring集成

spring配置文件

查询接口代码注解

package com.emmaluo.mybatislearning.IDAO;

import com.emmaluo.mybatislearning.model.Student;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * Created by zcluo on 2016/6/23.
 */
@Component
public interface StudentMapper {
    List findAllStudents(RowBounds rowBounds);
    List findAllStudents();
    List findAllStudentsByNameEmail(String name,String email);
    Student findStudentById(Integer id);
    int insertStudent(Student student);
    int insertStudentWithPhone(Student student);
    int insertStudentWithGender(Student student);
    int deleteStudentById(Integer id);
    Student selectStudentWithAddress(Integer studId);
    int updateStudent(Student student);

}

服务层代码注解

package com.emmaluo.mybatislearning.service;

import com.emmaluo.mybatislearning.IDAO.StudentMapper;
import com.emmaluo.mybatislearning.model.Student;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;

/**
 * Created by zcluo on 2016/6/26.
 */
@Component
public class StudentServiceWithSpring {
    @Resource
    private StudentMapper studentMapper;

    public StudentMapper getStudentMapper() {
        return studentMapper;
    }

    public void setStudentMapper(StudentMapper studentMapper) {
        this.studentMapper = studentMapper;
    }

    public Student createStudent(Student student)
    {
        this.studentMapper.insertStudent(student);
        return student;
    }
}

测试案例

package com.emmaluo.mybatislearning.test;

import com.emmaluo.mybatislearning.model.Address;
import com.emmaluo.mybatislearning.model.Student;
import com.emmaluo.mybatislearning.service.StudentService;
import com.emmaluo.mybatislearning.service.StudentServiceWithSpring;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;

import static org.junit.Assert.*;

/**
 * Created by zcluo on 2016/6/26.
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:mybatis-spring.xml")
public class StudentServiceSpringTest {
    @Resource
    private StudentServiceWithSpring studentServiceWithSpring;

    @Test
    public void testCreateStudent(){
        Address address = new Address(0, "Quaker Ridge Rd.", "Bethel", "Brooklyn", "06801", "USA");
        Student stud = new Student();
        long ts = System.currentTimeMillis();
        stud.setName("stud_" + ts);
        stud.setEmail("stud_" + ts + "@gmail.com");
        stud.setAddress(address);
        Student student = studentServiceWithSpring.createStudent(stud);
        assertNotNull(student);
        assertEquals("stud_" + ts, student.getName());
        assertEquals("stud_" + ts + "@gmail.com", student.getEmail());
        System.err.println("CreatedStudent: " + student);
    }

}

所有代码：https://github.com/zcluo/mybatislearning.git

参考书籍：《Java Persistence with MyBatis 3》、http://www.mybatis.org/mybatis-3/zh/index.html