程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MyBatis實踐--Mapper與DAO

MyBatis實踐--Mapper與DAO

編輯：MySQL綜合教程

MyBatis實踐--Mapper與DAO

MyBatis簡介

MyBatis前身是iBatis,是一個基於Java的數據持久層/對象關系映射(ORM)框架.

MyBatis是對JDBC的封裝,使開發人員只需關注SQL本身,而不需花費過多的精力去處理如注冊驅動、設置參數、創建Connection/Statement、解析結果集等JDBC過程性代碼.MyBatis基於XML/注解的方式配置Statement,執行SQL,並將執行結果映射成Java對象, 大大降低了數據庫開發的難度.

MyBatis is a first class persistence framework with support for custom SQL, stored procedures and advanced mappings. MyBatis eliminates almost all of the JDBC code and manual setting of parameters and retrieval of results. MyBatis can use simple XML or Annotations for configuration and map primitives, Map interfaces and Java POJOs (Plain Old Java Objects) to database records.
– MyBatis項目地址/在線文檔.

初識MyBatis

使用MyBatis需要在pom.xml中添加如下依賴:

<code class="hljs xml"><dependency>
    <groupid>org.mybatis</groupid>
    <artifactid>mybatis</artifactid>
    <version>3.3.0</version>
</dependency>
<dependency>
    <groupid>mysql</groupid>
    <artifactid>mysql-connector-java</artifactid>
    <version>5.1.36</version>
</dependency></code>

`Select`

配置mybatis/mybatis-configuration.xml

作為MyBatis的全局配置文件,其配置了MyBatis的運行環境信息(如數據源/mapper文件等).

<code class="hljs xml"><code class="hljs xml"><!--{cke_protected}{C}%3C!%2D%2D%3Fxml%20version%3D%221.0%22%20encoding%3D%22UTF-8%22%20%3F%2D%2D%3E-->

<configuration>

    <environments default="development">
        <environment id="development">
            <!--{cke_protected}{C}%3C!%2D%2D%20%E9%85%8D%E7%BD%AEJDBC%E4%BA%8B%E5%8A%A1%E7%AE%A1%E7%90%86%2D%2D%3E-->
            <transactionmanager type="JDBC">
            <!--{cke_protected}{C}%3C!%2D%2D%20%E9%85%8D%E7%BD%AE%E6%95%B0%E6%8D%AE%E6%BA%90%2D%2D%3E-->
            <datasource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver">
                <property name="url" value="jdbc:mysql://host:port/db?characterEncoding=utf-8">
                <property name="username" value="username">
                <property name="password" value="password">
            </property></property></property></property></datasource>
        </transactionmanager></environment>
    </environments>

    <!--{cke_protected}{C}%3C!%2D%2D%20%E5%8A%A0%E8%BD%BDmapper%E6%98%A0%E5%B0%84%E6%96%87%E4%BB%B6%20%2D%2D%3E-->
    <mappers>
        <mapper resource="mybatis/mapper/UserDAO.xml">
    </mapper></mappers>
</configuration></code></code>

書寫UserDAO(mapper映射)

最為MyBatis最核心的部分,配置了操作數據庫的SQL語句:

<code class="hljs xml"><code class="hljs xml"><code class="hljs xml"><!--{cke_protected}{C}%3C!%2D%2D%3Fxml%20version%3D%221.0%22%20encoding%3D%22UTF-8%22%20%3F%2D%2D%3E-->

<mapper namespace="namespace">

    <select id="selectUserById" parametertype="java.lang.Integer" resulttype="com.fq.domain.User">
        SELECT * FROM user WHERE id = #{id};
    </select>

    <select id="selectUserByName" parametertype="java.lang.String" resulttype="com.fq.domain.User">
        SELECT * FROM user WHERE name LIKE '%${value}%';
    </select>

</mapper></code></code></code>

屬性 描述 namespace 命名空間,用於隔離SQL語句 parameterType 定義SQL輸入映射類型,MyBatis通過OGNL從輸入對象中獲取參數傳入SQL語句. resultType 定義SQL輸出映射類型,MyBatis將SQL查詢結果的一行記錄映射為resultType指定的類型.

mapper映射文件名有UserDAO.xml/UserMapper.xml/User.xml等幾種形式, 其一般存放在與mybatis-configuration.xml同級的mapper目錄下,由於其主要作用為定義SQL語句與映射關系, 因此一般統稱為mapper映射文件.

定義PO類

PO類主要作用為SQL(輸入/輸出)映射,通常與數據庫表對應:

/**
 * @author jifang
 * @since 15/12/31 下午2:27.
 */
public class User {

    private Integer id;

    private String name;

    private String password;

    public User() {
    }

    public User(Integer id, String name, String password) {
        this.id = id;
        this.name = name;
        this.password = password;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

UserDAO(Java對象)

獲得SqlSession,執行SQL語句, 得到映射結果:

/**
 * @author jifang
 * @since 16/2/24 下午6:15.
 */
public class UserDAO {

    private SqlSessionFactory factory;

    @Before
    public void setUp() throws IOException {
        String resource = "mybatis/mybatis-configuration.xml";
        factory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream(resource));
    }

    @Test
    public void selectUserById() {
        try (SqlSession session = factory.openSession()) {
            User user = session.selectOne("namespace.selectUserById", 1);
            System.out.println(user);
        }
    }

    @Test
    public void selectUserByName() {
        try (SqlSession session = factory.openSession()) {
            List users = session.selectList("namespace.selectUserByName", "student");
            for (User user : users) {
                System.out.println(user);
            }
        }
    }
}

`Insert`

mapper


    INSERT INTO user(name, password) VALUES(#{name}, #{password});

UserDAO

@Test
public void insertUser() {
    try (SqlSession session = factory.openSession()) {
        User user = new User();
        user.setName("new_name1");
        user.setPassword("new_password");
        session.insert("namespace.insertUser", user);
        session.commit();
    }
}

`自增主鍵返回`

修改mapper文件,添加,可以將MySQL的自增主鍵(即剛剛插入數據時生成的ID)返回:


    
        SELECT LAST_INSERT_ID();
    
    INSERT INTO user(name, password) VALUES(#{name}, #{password});

屬性 描述 keyProperty 指定存儲到DO中的哪個屬性; order selectKey執行順序(相對於insert語句),AFTER/BEFORE; resultType 主鍵返回類型(DO中對應屬性的類型); LAST_INSERT_ID() MySQL函數,返回auto_increment自增列新記錄值. UserDAO

@Test
public void insertUser() {
    try (SqlSession session = factory.openSession()) {
        System.out.println(session);
        User user = new User(null, "new_name", "new_password");
        session.insert("namespace.insertUser", user);
        // 需要在commit之後才能獲得自增主鍵
        session.commit();
        System.out.println(user.getId());
    }
}

該功能還可以通過的useGeneratedKeys/keyProperty兩個屬性合作完成, 詳見MyBatis文檔.

`Update`

mapper


    UPDATE user SET name = #{name}, password = #{password} WHERE id = #{id};

UserDAO

@Test
public void updateUserById() {
    try (SqlSession session = factory.openSession(true)) {
        session.update("namespace.updateUserById",
                new User(1, "feiqing", "ICy5YqxZB1uWSwcVLSNLcA=="));
    }
}

`Delete`

mapper


    DELETE FROM user WHERE id = #{id};

UserDAO

@Test
public void deleteUserById() {
    try (SqlSession session = factory.openSession(true)) {
        session.delete("namespace.deleteUserById", 51615);
    }
}

`小結`

#{}/${}

#{}: 表示一個占位符號,實現向PreparedStatement占位符中設置值(#{}表示一個占位符?),自動進行Java類型到JDBC類型的轉換(因此#{}可以有效防止SQL注入).#{}可以接收簡單類型或PO屬性值,如果parameterType傳輸的是單個簡單類型值,#{}花括號中可以是value或其它名稱. ${}: 表示拼接SQL串,通過${}可將parameterType內容拼接在SQL中而不進行JDBC類型轉換,${}可以接收簡單類型或PO屬性值,如果parameterType傳輸的是單個簡單類型值,${}花括號中只能是value.

雖然${}不能防止SQL注入,但有時${}會非常方便(如order by排序,需要將列名通過參數傳入SQL,則用ORDER BY ${column},使用#{}則無法實現此功能(詳見JDBC基礎關於PreparedStatement的討論).

SqlSession 提供操作數據庫的方法(如:selectOne/selectList).但SqlSession是線程不安全的,因此最好將其定義成局部變量使用.

MyBatis優點(與JDBC相比)

SQL寫在Java代碼中導致不易維護, 而MyBatis將SQL寫在mapper中,XML與Java代碼分離. 向SQL語句傳參繁瑣(如:SQL的where條件不一,SQL數據類型與Java不同),MyBatis通過parameterType自動將Java對象映射至SQL語句. 結果集解析麻煩(SQL變化導致解析代碼變化,SQL數據類型與Java不同),MyBatis通過resultType自動將SQL執行結果映射成Java對象.

附: 最好在pom.xml中添加一個日志系統實現(logback/log4j), 這樣會在調試程序時打印日志信息,便於查錯, 以logback為例:

pom.xml


    ch.qos.logback
    logback-classic
    1.1.2

logback.xml

<code class="hljs xml"><code class="hljs xml"><code class="hljs xml"><code class="hljs java"><code class="hljs java"><code class="hljs applescript"><code class="hljs java"><code class="hljs xml"><code class="hljs cs"><code class="hljs haml"><code class="hljs java"><code class="hljs livecodeserver"><code class="hljs java"><code class="hljs xml"><code class="hljs xml"><configuration>

    <property name="logRoot" value="/data/logs">
    <property name="pattern" value="%d{HH:mm:ss.SSS} [%thread] %-5level %logger{0} - %msg%n">

    <appender class="ch.qos.logback.core.ConsoleAppender" name="STDOUT">
        <encoder>
            <pattern>${pattern}</pattern>
        </encoder>
    </appender>
    <appender class="ch.qos.logback.core.rolling.RollingFileAppender" name="FILE">
        <rollingpolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <filenamepattern>${logRoot}/common-server.%d{yyyy-MM-dd}.log</filenamepattern>
            <maxhistory>7</maxhistory>
        </rollingpolicy>
        <encoder>
            <pattern>${pattern}</pattern>
        </encoder>
    </appender>

    <root level="DEBUG">
        <appender-ref ref="STDOUT">
        <appender-ref ref="FILE">
    </appender-ref></appender-ref></root>

</property></property></configuration></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

其他關於MyBatis日志的詳細信息可參考MyBatis文檔日志部分.

`DAO開發`

使用MyBatis開發DAO有兩個方法,原始DAO開發與Mapper映射DAO開發.

`原始DAO開發`

原始DAO開發需要開發人員編寫DAO接口與DAO實現,如根據ID查詢用戶信息:

mapper(同前)

<code class="hljs xml"><code class="hljs xml"><code class="hljs xml"><code class="hljs java"><code class="hljs java"><code class="hljs applescript"><code class="hljs java"><code class="hljs xml"><code class="hljs cs"><code class="hljs haml"><code class="hljs java"><code class="hljs livecodeserver"><code class="hljs java"><code class="hljs xml"><code class="hljs xml"><code class="hljs vbnet"><select id="selectUserById" parametertype="java.lang.Integer" resulttype="com.fq.domain.User">
    SELECT * FROM user WHERE id = #{id};
</select></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

UserDAO接口

/**
 * @author jifang
 * @since 16/2/22 上午10:20.
 */
public interface UserDAO {
    User selectUserById(Integer id) throws Exception;
}

UserDAO實現

public class UserDAOImpl implements UserDAO {

    private SqlSessionFactory factory;

    public UserDAOImpl(SqlSessionFactory factory) {
        this.factory = factory;
    }

    @Override
    public User selectUserById(Integer id) throws Exception {
        SqlSession session = factory.openSession();
        User user = session.selectOne("namespace.selectUserById", id);
        session.close();
        return user;
    }
}

Client

public class MyBatisClient {

    @Test
    public void originalClient() throws Exception {
        UserDAO dao = new UserDAOImpl(new SqlSessionFactoryBuilder().
                build(ClassLoader.getSystemResourceAsStream("mybatis/mybatis-configuration.xml")));
        User user = dao.selectUserById(1);
        System.out.println(user);
    }
}

原始DAO開發中存在的問題:

1) DAO實現方法體中存在很多過程性代碼.

2) 調用SqlSession的方法(select/insert/update)需要指定Statement的id,存在硬編碼,不利於代碼維護.

`Mapper映射開發`

mapper映射開發方法只需編寫DAO接口,MyBatis根據接口定義與mapper文件中的SQL語句動態創建接口實現.

mapper

注意: 此時namespace必須與UserDAO接口的全限定名相同.

UserDAO接口與前面相同, 但不再使用UserDAOImpl Client

/**
 * @author jifang
 * @since 16/2/22 下午2:57.
 */
public class MyBatisClient {

    private SqlSession session;

    private SqlSessionFactory factory;

    @Before
    public void setUp() {
        factory = new SqlSessionFactoryBuilder().
                build(ClassLoader.getSystemResourceAsStream("mybatis/mybatis-configuration.xml"));
        session = factory.openSession();
    }

    @Test
    public void mapperClient() throws Exception {
        UserDAO dao = session.getMapper(UserDAO.class);
        User user = dao.selectUserById(1);
        System.out.println(user);
    }

    @After
    public void tearDown() {
        session.close();
    }
}

mapper映射開發方法需要遵循以下規范: mapper文件中的namespace與DAO接口的全限定名相同; mapper文件中的Statement的id與DAO接口方法名相同; mapper文件中的Statement的parameterType/resultType與DAO方法的入參/回參類型相同.

`Mapper映射`

mapper映射文件(如UserDAO.xml)主要作用是定義SQL語句(每個SQL是一個Statement),是MyBatis的核心.

MyBatis官方推薦使用mapper映射的方法來開發DAO,因此我們以後就不再過多介紹原始DAO的開發.

`輸入映射`

`多個形參`

傳遞簡單類型前面示例已經使用過,在此就不再贅述.當需要傳遞多個形參時,不再需要設置parameterType參數:

mapper


    UPDATE user SET name = #{1}, password = #{2} WHERE id = #{0};

UserDAO

void updateUserById(Integer id, String name, String password) throws Exception;

`傳入PO`

MyBatis使用OGNL表達式解析對象屬性值:

mapper

<code class="hljs xml"><code class="hljs xml"><code class="hljs xml"><code class="hljs java"><code class="hljs java"><code class="hljs applescript"><code class="hljs java"><code class="hljs xml"><code class="hljs cs"><code class="hljs haml"><code class="hljs java"><code class="hljs livecodeserver"><code class="hljs java"><code class="hljs xml"><code class="hljs xml"><code class="hljs vbnet"><code class="hljs java"><code class="hljs java"><code class="hljs cs"><code class="hljs xml"><code class="hljs java"><code class="hljs haml"><code class="hljs lasso"><code class="hljs vbnet"><select id="selectUserByNamePassword" parametertype="com.fq.domain.User" resulttype="com.fq.domain.User">
    SELECT *
    FROM user
    WHERE name = #{name} AND password = #{password};
</select></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

UserDAO

User selectUserByNamePassword(User user) throws Exception;

`傳入Map`

mapper

<code class="hljs xml"><code class="hljs xml"><code class="hljs xml"><code class="hljs java"><code class="hljs java"><code class="hljs applescript"><code class="hljs java"><code class="hljs xml"><code class="hljs cs"><code class="hljs haml"><code class="hljs java"><code class="hljs livecodeserver"><code class="hljs java"><code class="hljs xml"><code class="hljs xml"><code class="hljs vbnet"><code class="hljs java"><code class="hljs java"><code class="hljs cs"><code class="hljs xml"><code class="hljs java"><code class="hljs haml"><code class="hljs lasso"><code class="hljs vbnet"><code class="hljs java"><code class="hljs vbnet"><select id="selectUserByMap" parametertype="java.util.Map" resulttype="com.fq.domain.User">
    SELECT *
    FROM user
    WHERE name = #{name} AND password = #{password};
</select></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

#{}花括號內對應Map的key.

UserDAO

User selectUserByMap(Map map) throws Exception;

`輸出映射`

`輸出簡單類型`

mapper

<code class="hljs xml"><code class="hljs xml"><code class="hljs xml"><code class="hljs java"><code class="hljs java"><code class="hljs applescript"><code class="hljs java"><code class="hljs xml"><code class="hljs cs"><code class="hljs haml"><code class="hljs java"><code class="hljs livecodeserver"><code class="hljs java"><code class="hljs xml"><code class="hljs xml"><code class="hljs vbnet"><code class="hljs java"><code class="hljs java"><code class="hljs cs"><code class="hljs xml"><code class="hljs java"><code class="hljs haml"><code class="hljs lasso"><code class="hljs vbnet"><code class="hljs java"><code class="hljs vbnet"><code class="hljs vhdl"><code class="hljs vbnet"><select id="selectUserCount" parametertype="java.lang.String" resulttype="java.lang.Integer">
    SELECT count(*)
    FROM user
    WHERE name LIKE '%${value}%';
</select></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

UserDAO

Integer selectUserCount(String name) throws Exception;

返回簡單類型必須保證查詢結果只有一行記錄,最終將第一個字段的值轉換為輸出類型.

`輸出PO對象/列表`

前面已經演示過輸出兩種類型(selectUserById/selectUserByName雖然當時使用的是原始DAO開發方法, 但mapper定義形式大同小異),因此在這兒只做簡單總結:

輸出單個PO對象和輸出PO列表在mapper中定義的resultType是一樣的; 輸出單個PO對象要保證SQL查詢結果為單條數據,其內部使用selectOne方法調用; 輸出PO列表表示查詢結果可能為多條,其內部使用selectList方法調用,接口返回值可用List/Set承載.

輸出Map
輸出PO對象完全可以改用Map輸出,字段名作key,字段值作value.
mapper
<code class="hljs vbnet"><select id="selectUserLikeName" resulttype="java.util.Map">
    SELECT *
    FROM user
    WHERE name LIKE '%${value}%';
</select></code>
UserDAO 
List> selectUserLikeName(String name) throws Exception;

resultMap
resultType可將查詢結果映射為PO,但前提是PO屬性名與SQL字段名必須一致,如不一致,則可通過resultMap作對應映射:
mapper 
<code class="hljs vbnet"><code class="hljs mathematica"><code class="hljs applescript"><resultmap id="userMap" type="com.fq.domain.User">
    <id column="user_id" property="id">
    <result column="user_name" property="name">
    <result column="user_password" property="password">
</result></result></id></resultmap>

<select id="selectUserByName" parametertype="java.lang.String" resultmap="userMap">
    SELECT
        id       user_id,
        name     user_name,
        password user_password
    FROM user
    WHERE name = #{name};
</select></code></code></code>


	
	
		屬性
	
		描述
	


	
	
	表示查詢結果集的唯一標識;
	
	
	
	表示普通結果,即PO屬性;
	
	
	column
	表示SQL查詢出來的字段名,
	
	
	property
	表示PO屬性.
	


UserDAO接口同前.