JavaPersistenceWithMyBatis3笔记-第1章-001

一、介绍

1.项目结构

2.数据库结构

二、代码

1.Mapper

package com.mybatis3.mappers;

import java.util.List;

import com.mybatis3.domain.Student;


/**
 * @author Siva
 *
 */
public interface StudentMapper
{

    List<Student> findAllStudents();

    Student findStudentById(Integer id);

    void insertStudent(Student student);

    void updateStudent(Student student);

}

 

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="Student" id="StudentResult">
        <id     property="studId" column="stud_id"/>
        <result property="name" column="name"/>
        <result property="email" column="email"/>
        <result property="dob" column="dob"/>
    </resultMap>
  
      <select id="findAllStudents" resultMap="StudentResult">
        select * from Students
      </select>
      
      <select id="findStudentById" parameterType="int" resultType="Student">
        select stud_id as studId, name, email, dob from Students where stud_id=#{studId}
      </select>
      
      <insert id="insertStudent" parameterType="Student">
          INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL,DOB) VALUES(#{studId},#{name},#{email},#{dob})
      </insert>
      
      <update id="updateStudent" parameterType="Student">
          UPDATE STUDENTS SET NAME=#{name}, EMAIL=#{email}, DOB=#{dob} WHERE STUD_ID=#{studId}
      </update>
      
</mapper>

 

2.Service

package com.mybatis3.services;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.mybatis3.domain.Student;
import com.mybatis3.mappers.StudentMapper;
import com.mybatis3.util.MyBatisSqlSessionFactory;


/**
 * @author Siva
 *
 */
public class StudentService 
{
    private Logger logger = LoggerFactory.getLogger(getClass());
    
    public List<Student> findAllStudents()
    {
        SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSession();
        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.getSqlSession();
        try {
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            return studentMapper.findStudentById(studId);
            //return sqlSession.selectOne("com.mybatis3.StudentMapper.findStudentById", studId);
        } finally {
            sqlSession.close();
        }
    }
    
    public void createStudent(Student student)
    {
        SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSession();
        try {
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            studentMapper.insertStudent(student);
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }
    
    public void updateStudent(Student student)
    {
        SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSession();
        try {
            StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
            studentMapper.updateStudent(student);
            sqlSession.commit();
        } finally {
            sqlSession.close();
        }
    }
}

 

老式的JDBC

package com.mybatis3.services;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import com.mybatis3.domain.Student;


/**
 * @author Siva
 *
 */

public class JdbcStudentService
{
    
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/elearning";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "admin";

    public static void main(String[] args)
    {
        
        JdbcStudentService service = new JdbcStudentService();
        
        Student existingStudent = service.findStudentById(1);
        System.out.println(existingStudent);
        
        
        long ts = System.currentTimeMillis();//For creating unique student names
        Student newStudent = new Student(0,"student_"+ts,"student_"+ts+"@gmail.com",new Date());
        service.createStudent(newStudent);
        System.out.println(newStudent);
        
        int updateStudId = 3;
        Student updateStudent = service.findStudentById(updateStudId);
        ts = System.currentTimeMillis();//For creating unique student email
        updateStudent.setEmail("student_"+ts+"@gmail.com");
        service.updateStudent(updateStudent);
        
    }
    
    public Student findStudentById(int studId)
    {
        Student student = null;
        Connection conn = null;
        try
        {
            conn = getDatabaseConnection();
            String sql = "select * from students where stud_id=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, studId);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next())
            {
                student = new Student();
                student.setStudId(rs.getInt("stud_id"));
                student.setName(rs.getString("name"));
                student.setEmail(rs.getString("email"));
                student.setDob(rs.getDate("dob"));
            }
            
        } catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            if(conn!= null){
                try {
                    conn.close();
                } catch (SQLException e){ }
            }
        }
        return student;
    }
    
    public void createStudent(Student student)
    {
        Connection conn = null;
        try
        {
            conn = getDatabaseConnection();
            String sql = "INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL,DOB) VALUES(?,?,?,?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, student.getStudId());
            pstmt.setString(2, student.getName());
            pstmt.setString(3, student.getEmail());
            pstmt.setDate(4, new java.sql.Date(student.getDob().getTime()));
            pstmt.executeUpdate();
            
        } catch (SQLException e)
        {
            throw new RuntimeException(e);
        }
        finally
        {
            if(conn!= null){
                try {
                    conn.close();
                } catch (SQLException e){ }
            }
        }
    }
    
    public void updateStudent(Student student)
    {
        Connection conn = null;
        try
        {
            conn = getDatabaseConnection();
            conn = getDatabaseConnection();
            String sql = "UPDATE STUDENTS SET NAME=?,EMAIL=?,DOB=? WHERE STUD_ID=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, student.getName());
            pstmt.setString(2, student.getEmail());
            pstmt.setDate(3, new java.sql.Date(student.getDob().getTime()));
            pstmt.setInt(4, student.getStudId());
            pstmt.executeUpdate();
            
        } catch (SQLException e)
        {
            throw new RuntimeException(e.getCause());
        }
        finally
        {
            if(conn!= null){
                try {
                    conn.close();
                } catch (SQLException e){ }
            }
        }
    }
    
    protected Connection getDatabaseConnection() throws SQLException
    {
        try
        {
            Class.forName(JdbcStudentService.DRIVER);
            return DriverManager.getConnection(JdbcStudentService.URL, 
                                                JdbcStudentService.USERNAME, 
                                                JdbcStudentService.PASSWORD);
        } catch (SQLException e)
        {
            throw e;
        } catch (Exception e)
        {
            throw new RuntimeException(e.getCause());
        } 
    }

}

 

3.Domain

package com.mybatis3.domain;

import java.util.Date;


/**
 * @author Siva
 *
 */
public class Student
{
    private Integer studId;
    private String name;
    private String email;
    private Date dob;
    
    public Student() {
        
    }
    
    public Student(Integer studId) {
        this.studId = studId;
    }
    
    public Student(Integer studId, String name, String email, Date dob) {
        this.studId = studId;
        this.name = name;
        this.email = email;
        this.dob = dob;
    }

    @Override
    public String toString() {
        return "Student [studId=" + studId + ", name=" + name + ", email="
                + email + ", dob=" + dob + "]";
    }
    
    public Integer getStudId() {
        return studId;
    }
    public void setStudId(Integer studId) {
        this.studId = studId;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public Date getDob() {
        return dob;
    }
    public void setDob(Date dob) {
        this.dob = dob;
    }
    
    
}

 

4.辅助类

package com.mybatis3.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

import org.apache.ibatis.datasource.DataSourceFactory;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

/**
 * @author Siva
 *
 */
public class MyBatisSqlSessionFactory
{
    private static SqlSessionFactory sqlSessionFactory;
    
    private static final Properties PROPERTIES = new Properties();
    
    static
    {
        try {
            InputStream is = DataSourceFactory.class.getResourceAsStream("/application.properties");
            PROPERTIES.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    public static SqlSessionFactory getSqlSessionFactory()
    {
        if(sqlSessionFactory==null) 
        {
            InputStream inputStream = null;
            try
            {
                inputStream = Resources.getResourceAsStream("mybatis-config.xml");
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            }catch (IOException e)
            {
                throw new RuntimeException(e.getCause());
            }finally {
                if(inputStream != null){
                    try {
                        inputStream.close();
                    } catch (IOException e) {
                    }
                }
            }
        }
        return sqlSessionFactory;
    }
    
    public static SqlSession getSqlSession() 
    {
        return getSqlSessionFactory().openSession();
    }
    
    public static Connection getConnection() 
    {
        String driver = PROPERTIES.getProperty("jdbc.driverClassName");
        String url = PROPERTIES.getProperty("jdbc.url");
        String username = PROPERTIES.getProperty("jdbc.username");
        String password = PROPERTIES.getProperty("jdbc.password");
        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } 
        return connection;
    }
}

 

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>
    
      <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>
        <mapper resource="com/mybatis3/mappers/StudentMapper.xml"/>
      </mappers>
      
      
</configuration>

 

(2)application.properties

################### DataSource Configuration ##########################

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/elearning
jdbc.username=root
jdbc.password=1234

 

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=1 DEFAULT CHARSET=LATIN1;

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 DEFAULT NULL,
  PIC BLOB DEFAULT NULL,
  ADDR_ID INT(11) DEFAULT NULL,  
  PRIMARY KEY (STUD_ID),
  CONSTRAINT FK_STUDENTS_ADDR FOREIGN KEY (ADDR_ID) REFERENCES ADDRESSES (ADDR_ID)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1;

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 DEFAULT NULL,
  PIC BLOB DEFAULT NULL,
  ADDR_ID INT(11) DEFAULT NULL,
  PRIMARY KEY (TUTOR_ID),
  CONSTRAINT FK_TUTORS_ADDR FOREIGN KEY (ADDR_ID) REFERENCES ADDRESSES (ADDR_ID)  
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1;


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),
  CONSTRAINT FK_COURSE_TUTOR FOREIGN KEY (TUTOR_ID) REFERENCES TUTORS (TUTOR_ID)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=LATIN1;


CREATE TABLE COURSE_ENROLLMENT
(
  COURSE_ID INT(11) NOT NULL,
  STUD_ID INT(11) NOT NULL,
  PRIMARY KEY (COURSE_ID,STUD_ID),
  CONSTRAINT FK_ENROLLMENT_STUD FOREIGN KEY (STUD_ID) REFERENCES STUDENTS (STUD_ID),
  CONSTRAINT FK_ENROLLMENT_COURSE FOREIGN KEY (COURSE_ID) REFERENCES COURSES (COURSE_ID)
) ENGINE=INNODB DEFAULT CHARSET=LATIN1;



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=1 DEFAULT CHARSET=LATIN1;

 

DROP TABLE IF EXISTS USER_PICS;
DROP TABLE IF EXISTS COURSE_ENROLLMENT;
DROP TABLE IF EXISTS COURSES;
DROP TABLE IF EXISTS TUTORS;
DROP TABLE IF EXISTS STUDENTS;
DROP TABLE IF EXISTS ADDRESSES;

 

--Sample data for table ADDRESSES

INSERT INTO ADDRESSES (ADDR_ID,STREET,CITY,STATE,ZIP,COUNTRY) 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');

-- Sample data for table STUDENTS

INSERT INTO STUDENTS (STUD_ID,NAME,EMAIL,PHONE,DOB,BIO,PIC,ADDR_ID) VALUES 
 (1,'Timothy','timothy@gmail.com','123-123-1234','1988-04-25',NULL,NULL,3),
 (2,'Douglas','douglas@gmail.com','789-456-1234','1990-08-15',NULL,NULL,4);

-- Sample data for table TUTORS

INSERT INTO TUTORS (TUTOR_ID,NAME,EMAIL,PHONE,DOB,BIO,PIC,ADDR_ID) 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);

-- Sample data for table courses

INSERT INTO COURSES (COURSE_ID,NAME,DESCRIPTION,START_DATE,END_DATE,TUTOR_ID) 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);

-- Sample data for table COURSE_ENROLLMENT

INSERT INTO COURSE_ENROLLMENT (COURSE_ID,STUD_ID) VALUES 
 (1,1),
 (1,2),
 (2,2);

 

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

 

6.测试文件

package com.mybatis3.services;

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

import org.junit.AfterClass;
import static org.junit.Assert.*;
import org.junit.BeforeClass;
import org.junit.Test;

import com.mybatis3.domain.Student;



/**
 * @author Siva
 *
 */
public class StudentServiceTest 
{
    private static StudentService studentService;
    
    @BeforeClass
    public static void setup()
    {
        studentService = new StudentService();
        TestDataPopulator.initDatabase();
    }
    @AfterClass
    public static void teardown()
    {
        studentService = null;
    }
    
    @Test
    public void testFindAllStudents() 
    {
        List<Student> students = studentService.findAllStudents();
        assertNotNull(students);
        for (Student student : students)
        {
            assertNotNull(student);
            System.out.println(student);
        }
        
    }
    
    @Test
    public void testFindStudentById() 
    {
        Student student = studentService.findStudentById(1);
        assertNotNull(student);
    }
    
    @Test
    public void testCreateUStudent() 
    {
        Student student = new Student();
        int id = 4;
        student.setStudId(id);
        student.setName("student_"+id);
        student.setEmail("student_"+id+"gmail.com");
        student.setDob(new Date());
        studentService.createStudent(student);
        Student newStudent = studentService.findStudentById(id);
        assertNotNull(newStudent);
        assertEquals("student_"+id, newStudent.getName());
        assertEquals("student_"+id+"gmail.com", newStudent.getEmail());
    }
    
    @Test    
    public void testUpdateStudent() 
    {
        int id = 2;
        Student student =studentService.findStudentById(id);
        student.setStudId(id);
        student.setName("student_"+id);
        student.setEmail("student_"+id+"gmail.com");
        Date now = new Date();
        student.setDob(now);
        studentService.updateStudent(student);
        Student updatedStudent = studentService.findStudentById(id);
        assertNotNull(updatedStudent);
        assertEquals("student_"+id, updatedStudent.getName());
        assertEquals("student_"+id+"gmail.com", updatedStudent.getEmail());
        
    }
}

 

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

import java.io.Reader;
import java.sql.Connection;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.mybatis3.util.MyBatisSqlSessionFactory;

/**
 * @author Siva
 *
 */
public class TestDataPopulator 
{
    private static Logger logger = LoggerFactory.getLogger(TestDataPopulator.class);
    
    public static void main(String[] args) {
        initDatabase();
    }
    
    public static void initDatabase()
    {
        Connection connection = null;
        Reader reader = null;
        try {
            connection = MyBatisSqlSessionFactory.getConnection();
            ScriptRunner scriptRunner = new ScriptRunner(connection);
            reader = Resources.getResourceAsReader("sql/drop_tables.sql");
            scriptRunner.runScript(reader);
            logger.info("drop_tables.sql executed successfully");
            reader = Resources.getResourceAsReader("sql/create_tables.sql");
            scriptRunner.runScript(reader );
            logger.info("create_tables.sql executed successfully");
            reader = Resources.getResourceAsReader("sql/sample_data.sql");
            scriptRunner.runScript(reader );
            logger.info("sample_data.sql executed successfully");
            connection.commit();
            reader.close();
            scriptRunner.closeConnection();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        
    }
}