Mybatis基础入门一

数据准备：

CREATE TABLE MEMBER (
MEMBER_ID NUMBER(10, 0),
FIRST_NAME VARCHAR2(400) NOT NULL,
LAST_NAME VARCHAR2(400) NOT NULL,
ADDRESS VARCHAR2(1000),
PHONE VARCHAR2(400),
PRIMARY KEY (MEMBER_ID));

CREATE TABLE TASK (
TASK_ID NUMBER(10, 0),
MEMBER_ID NUMBER(10, 0),
CONTENT VARCHAR2(1000),
DUE_DATE DATE,
PRIMARY KEY (TASK_ID),
FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER (MEMBER_ID));

INSERT INTO MEMBER (MEMBER_ID, FIRST_NAME, LAST_NAME,PHONE) VALUES(1,'Alex', 'Brown', '635874');
INSERT INTO MEMBER (MEMBER_ID, FIRST_NAME, LAST_NAME,PHONE) VALUES(2,'Ada', 'Lee', '536952');
INSERT INTO MEMBER (MEMBER_ID, FIRST_NAME, LAST_NAME,PHONE) VALUES(3,'Candy', 'Law', '362548');
INSERT INTO MEMBER (MEMBER_ID, FIRST_NAME, LAST_NAME,PHONE) VALUES(4,'Vincent', 'Xu', '123698');

INSERT INTO TASK (TASK_ID,MEMBER_ID, CONTENT, DUE_DATE) VALUES (1, 1 ,'Kill Bill Gates.', TO_DATE('2012-12-31', 'YYYY-MM-DD'));
INSERT INTO TASK (TASK_ID,MEMBER_ID, CONTENT, DUE_DATE) VALUES (2, 1 ,'Earn USD 1 billion.', TO_DATE('2033-01-01', 'YYYY-MM-DD'));
INSERT INTO TASK (TASK_ID,MEMBER_ID, CONTENT, DUE_DATE) VALUES (3, 1 ,'Worldwide trip.', TO_DATE('2020-03-03', 'YYYY-MM-DD'));
INSERT INTO TASK (TASK_ID,MEMBER_ID, CONTENT, DUE_DATE) VALUES (4, 2 ,'Learn Java.', TO_DATE('2012-09-08', 'YYYY-MM-DD'));
INSERT INTO TASK (TASK_ID,MEMBER_ID, CONTENT, DUE_DATE) VALUES (5, 2 ,'Learn Ruby.', TO_DATE('2012-11-12', 'YYYY-MM-DD'));
INSERT INTO TASK (TASK_ID,MEMBER_ID, CONTENT, DUE_DATE) VALUES (6, 3 ,'Finish job 1.', TO_DATE('2013-04-01', 'YYYY-MM-DD'));
INSERT INTO TASK (TASK_ID,MEMBER_ID, CONTENT, DUE_DATE) VALUES (7, 3 ,'Finish job 2.', TO_DATE('2013-06-06', 'YYYY-MM-DD'));
INSERT INTO TASK (TASK_ID,MEMBER_ID, CONTENT, DUE_DATE) VALUES (8, 3 ,'Finish job 3.', TO_DATE('2014-02-28', 'YYYY-MM-DD'));

COMMIT;

目录结构：

[img]

 [/img]

 

mybatis-config.xml(mybatis的配置文件)

 

    <!-- db setting -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.driver.OracleDriver" />
                <property name="url" value="jdbc:oracle:thin:@192.168.2.136:1521:dev" />
                <property name="username" value="cnt5_training" />
                <property name="password" value="p" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!-- 关联mapper文件 -->
        <mapper resource="com/core/traininig/mybatisquiz/MemberMapper.xml" />
    </mappers>

 

 

MemberMapper.xml(POJO 与 sql 的映射文件)

 

<?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会调用该映射文件来进行CRUD -->
<mapper namespace="com.core.traininig.mybatisquiz.MemberMapper">

<resultMap id="memberWithTask" type="com.core.traininig.entity.Member">
        <result property="memberId" column="MEMBER_ID" />
        <result property="firstName" column="FIRST_NAME" />
        <result property="lastName" column="LAST_NAME" />
        <result property="address" column="ADDRESS" />
        <result property="phone" column="PNONE" />
        <collection property="tasks"
            ofType="com.core.traininig.entity.Task" column="MEMBER_ID" javaType="ArrayList">
            <result property="taskId" column="TASK_ID" />
            <result property="memberId" column="MEMBER_ID" />
            <result property="content" column="CONTENT" />
            <result property="dueDate" column="DUE_DATE" />
        </collection>
    </resultMap>

    <!-- id要与XXXMapper.java 的方法名一致-->
    <select id="getAllMembers" parameterType="map"
        resultMap="memberWithTask">
    SELECT m.MEMBER_ID AS ID, m.FIRST_NAME, m.LAST_NAME, m.ADDRESS, m.PHONE,t.TASK_ID,
        t.MEMBER_ID, t.CONTENT, t.DUE_DATE
           FROM MEMBER m LEFT JOIN TASK t ON m.MEMBER_ID = t.MEMBER_ID
    ORDER BY m.FIRST_NAME, t.DUE_DATE DESC
    </select>

</mapper>

 

 MemberMapper.java

import java.util.List;

import com.core.traininig.entity.Member;

/**
 * @author aron.tang
 *
 */
public interface MemberMapper {
   List<Member> getAllMembers();
}

 

Member.java

 

import java.util.List;

/**
 * @author aron.tang
 *
 */
public class Member {
    private int memberId;
    private String firstName;
    private String lastName;
    private String address;
    private String phone;
    private List<Task> tasks;
    /**
     * @return the memberId
     */
    public int getMemberId() {
        return memberId;
    }
    /**
     * @param memberId the memberId to set
     */
    public void setMemberId(final int memberId) {
        this.memberId = memberId;
    }
    /**
     * @return the firstName
     */
    public String getFirstName() {
        return firstName;
    }
    /**
     * @param firstName the firstName to set
     */
    public void setFirstName(final String firstName) {
        this.firstName = firstName;
    }
    /**
     * @return the lastName
     */
    public String getLastName() {
        return lastName;
    }
    /**
     * @param lastName the lastName to set
     */
    public void setLastName(final String lastName) {
        this.lastName = lastName;
    }
    /**
     * @return the address
     */
    public String getAddress() {
        return address;
    }
    /**
     * @param address the address to set
     */
    public void setAddress(final String address) {
        this.address = address;
    }
    /**
     * @return the phone
     */
    public String getPhone() {
        return phone;
    }
    /**
     * @param phone the phone to set
     */
    public void setPhone(final String phone) {
        this.phone = phone;
    }
    /**
     * @return the tasks
     */
    public List<Task> getTasks() {
        return tasks;
    }
    /**
     * @param tasks the tasks to set
     */
    public void setTasks(final List<Task> tasks) {
        this.tasks = tasks;
    }



}

 

 

Task.java

import java.util.Date;

/**
 * @author aron.tang
 *
 */
public class Task {
    private int taskId;
    private int memberId;
    private String content;
    private Date dueDate;
    /**
     * @return the taskId
     */
    public int getTaskId() {
        return taskId;
    }
    /**
     * @param taskId the taskId to set
     */
    public void setTaskId(final int taskId) {
        this.taskId = taskId;
    }
    /**
     * @return the memberId
     */
    public int getMemberId() {
        return memberId;
    }
    /**
     * @param memberId the memberId to set
     */
    public void setMemberId(final int memberId) {
        this.memberId = memberId;
    }
    /**
     * @return the content
     */
    public String getContent() {
        return content;
    }
    /**
     * @param content the content to set
     */
    public void setContent(final String content) {
        this.content = content;
    }
    /**
     * @return the dueDate
     */
    public Date getDueDate() {
        return dueDate;
    }
    /**
     * @param dueDate the dueDate to set
     */
    public void setDueDate(final Date dueDate) {
        this.dueDate = dueDate;
    }

}

 

 

测试类MemberManager.java

 

import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

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

import com.core.traininig.entity.Member;
import com.core.traininig.entity.Task;

/**
 * @author aron.tang
 */
public class MemberManager {
    private static SqlSessionFactory sqlSessionFactory;

    public static void main(final String[] args) throws IOException {
        // load the setting file of mybatis.
        final String resource = "com/core/traininig/mybatisquiz/mybatis-config.xml";
        final InputStream inputStream = Resources.getResourceAsStream(resource);
        // build the SqlSessionFactory instance which manage the session when access DB.
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        printMembers();
    }

    public static List<Member> getAllMembers() {
        // open the session then can access the assigned DB 
        final SqlSession session = sqlSessionFactory.openSession();
        // get the mapper which map the sql to POJO.
        final MemberMapper mapper = session.getMapper(MemberMapper.class);
        return mapper.getAllMembers();

    }

    public static void printMembers() {
        final MemberMapper mapper = getMapper();
        final List<Member> members = mapper.getAllMembers();
        System.out.println("There are total " + members.size() + " members");
        System.out.println("================================================");
        for (final Member m : members) {
            System.out.println(m.getFirstName() + " " + m.getLastName() + " has " + m.getTasks().size() + " tasks. ");
            for (final Task t : m.getTasks()) {
                System.out.println("{" + t.getContent() + "} to be done before " + parseDate(t.getDueDate()));
            }
            System.out.println();
        }
        closeSession();
    }

    public static String parseDate(final Date date) {
        final DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        final String result = df.format(date);
        return result;
    }
    
    private static MemberMapper getMapper() {
        final SqlSession session = sqlSessionFactory.openSession();
        final MemberMapper mapper = session.getMapper(MemberMapper.class);
        return mapper;
    }
    
    private static void closeSession() {
        final SqlSession session = sqlSessionFactory.openSession();
        if (session != null) {
            session.close();
        }
    }

}

 

 

测试结果:

 主要用到的jar包: mybatis-3.0.5.jar