MyBatis基礎：MyBatis資料基本操作（2）

libingql發表於2017-08-23
MyBatis

1. MyBatis對映器

2. MyBatis資料基本操作

　　示例專案結構：

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>libing</groupId>
    <artifactId>com-helloworld-api</artifactId>
    <packaging>war</packaging>
    <version>0.0.1-SNAPSHOT</version>
    <name>com-helloworld-api Maven Webapp</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.43</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>4.0.0-b07</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>
    <build>
        <finalName>com-helloworld-api</finalName>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.6.2</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>
pom.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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/helloworld?characterEncoding=utf-8" />
                <property name="username" value="root" />
                <property name="password" value="root" />
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <mapper resource="mappers/RoleMapper.xml" />
    </mappers>
</configuration>
mybatis-config.xml
package com.libing.helloworld.model;

public class Role {

    private Integer id;

    private String roleName;

    public Integer getId() {
        return id;
    }

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

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

}
Role.java
<?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.libing.helloworld.dao.IRoleDao">
    <resultMap id="baseResultMap" type="com.libing.helloworld.model.Role">
        <id property="id" column="id" />
        <result property="roleName" column="role_name" />
    </resultMap>
    
    <select id="findAll" resultMap="baseResultMap">
        SELECT
            id,
            role_name
        FROM
            role
        ORDER BY id ASC
    </select>
    
    <select id="findBySearchText" resultMap="baseResultMap">
        SELECT
            id,
            role_name
        FROM
            role
        WHERE
            role_name LIKE CONCAT(CONCAT('%',#{searchText,jdbcType=VARCHAR}),'%')
        ORDER BY id ASC
    </select>
    
    <select id="findById" resultMap="baseResultMap">
        SELECT 
            id,
            role_name
        FROM 
            role 
        WHERE id = #{id}
    </select>
    
    <insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="com.libing.helloworld.model.Role">
        INSERT role
        (
            role_name
        )
        VALUES
        (
            #{roleName}
        )
    </insert>

    <update id="update" parameterType="com.libing.helloworld.model.Role">
        UPDATE role
        SET
            role_name=#{roleName}
        WHERE
            id = #{id}
    </update>
    
    <delete id="deleteById">
        DELETE FROM role
        WHERE
            id = #{id}
    </delete>
    
    <delete id="deleteByIds" parameterType="java.util.List">
        DELETE FROM role
        WHERE
            id IN
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </delete>
</mapper>
RoleMapper.xml
package com.libing.helloworld.test;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;

import com.libing.helloworld.dao.IRoleDao;
import com.libing.helloworld.model.Role;

public class RoleTest {
    SqlSession sqlSession = null;

    @Before
    public void init() {
        String resource = "mybatis-config.xml";
        InputStream inputStream = RoleTest.class.getClassLoader().getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession();
    }

    @Test
    public void findAll() {
        try {
            IRoleDao roleDao = sqlSession.getMapper(IRoleDao.class);
            List<Role> roles = roleDao.findAll();

            Assert.assertNotNull(roles);
            Assert.assertTrue(roles.size() > 0);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }

    @Test
    public void findById() {
        try {
            IRoleDao roleDao = sqlSession.getMapper(IRoleDao.class);
            Role role = roleDao.findById(1);

            Assert.assertNotNull(role);
            Assert.assertNotNull(role.getRoleName());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }

    @Test
    public void insert() {
        try {
            IRoleDao roleDao = sqlSession.getMapper(IRoleDao.class);

            Role role = new Role();
            role.setRoleName("測試");
            int result = roleDao.insert(role);

            // 只插入一條記錄
            Assert.assertEquals(1, result);
            // id不為null
            Assert.assertNotNull(role.getId());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.rollback();
            sqlSession.close();
        }
    }

    public void Update() {
        try {
            IRoleDao roleDao = sqlSession.getMapper(IRoleDao.class);

            Role role = roleDao.findById(1);
            Assert.assertNotNull(role);

            role.setRoleName("測試");
            int result = roleDao.update(role);

            // 只修改一條記錄
            Assert.assertEquals(1, result);

            // 修改後的值
            Assert.assertEquals("測試", roleDao.findById(1).getRoleName());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.rollback();
            sqlSession.close();
        }
    }

    @Test
    public void deleteById() {
        try {
            IRoleDao roleDao = sqlSession.getMapper(IRoleDao.class);

            Assert.assertNotNull(roleDao.findById(1));

            // 呼叫刪除方法
            Assert.assertEquals(1, roleDao.deleteById(1));
            // 再次查詢，判斷是否為null
            Assert.assertNull(roleDao.findById(1));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 由於sqlSessionFactory.openSession()是不自動提交的，不手動執行sqlSession.commit()不會提交到資料庫
            sqlSession.rollback();
            sqlSession.close();
        }
    }

    @Test
    public void deleteByIds() {
        try {
            List<Integer> ids = new ArrayList<Integer>();
            ids.add(1);
            ids.add(2);

            IRoleDao roleDao = sqlSession.getMapper(IRoleDao.class);
            int result = roleDao.deleteByIds(ids);

            Assert.assertTrue(result > 0);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.rollback();
            sqlSession.close();
        }
    }
}
RoleTest.java
package com.libing.helloworld.dao;

import java.util.List;

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

import com.libing.helloworld.model.Role;

public interface IRoleDao {

    @Select("SELECT id,role_name FROM role")
    @Results( value = {
        @Result(id = true, property = "id", column = "id"),
        @Result(property="roleName", column="role_name")
    })
    List<Role> findAll();
    
    @Select("SELECT id,role_name FROM role WHERE role_name LIKE CONCAT(CONCAT('%',#{searchText}),'%')")
    @Results( value = {
        @Result(id = true, property = "id", column = "id"),
        @Result(property="roleName", column="role_name")
    })
    List<Role> findBySearchText(@Param("searchText") String searchText);

    @Select("SELECT id,role_name FROM role WHERE id = #{id}")
    @Results( value = {
        @Result(id = true, property = "id", column = "id"),
        @Result(property="roleName", column="role_name")
    })
    Role findById(Integer id);

    @Insert({ 
        "INSERT INTO role(id,role_name)",
        " VALUES ",
        "(#{id}, #{roleName})" 
    })
    @Options(useGeneratedKeys = true, keyProperty = "id")
    @SelectKey(statement="select last_insert_id()", keyProperty = "id", before = false, resultType = int.class)
    int insert(Role role);

    @Update({
        "UPDATE role SET ",
        "role_name = #{roleName} ",
        "WHERE id = #{id}"
    })
    int update(Role role);

    @Delete("DELETE FROM role WHERE id = #{id}")
    int deleteById(Integer id);

    int deleteByIds(List<Integer> ids);

}
IRoleDao.java

2.1 select

　　基於XML方式：

<resultMap id="baseResultMap" type="com.libing.helloworld.model.Role">
    <id property="id" column="id" />
    <result property="roleName" column="role_name" />
</resultMap>

<select id="findAll" resultMap="baseResultMap">
    SELECT
        id,
        role_name
    FROM
        role
    ORDER BY id ASC
</select>

　　基於註解方式：

@Select("SELECT id,role_name FROM role")
@Results( value = {
    @Result(id = true, property = "id", column = "id"),
    @Result(property="roleName", column="role_name")
})
List<Role> findAll();

　　單元測試：

@Test
public void findAll() {
    try {
        IRoleDao roleDao = sqlSession.getMapper(IRoleDao.class);
        List<Role> roles = roleDao.findAll();

        Assert.assertNotNull(roles);
        Assert.assertTrue(roles.size() > 0);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        sqlSession.close();
    }
}

　　XML中大於符號與小於符號轉義：

　　>   &gt; 

　　<   &lt; 

　　或使用 <![CDATA[   ]]>

<select id="findAll" resultMap="baseResultMap">
    SELECT
        id,
        role_name
    FROM
        role
    WHERE id &gt;= 10
    ORDER BY id ASC
</select>
<select id="findAll" resultMap="baseResultMap">
    SELECT
        id,
        role_name
    FROM
        role
    WHERE id <![CDATA[>=]]> 10
    ORDER BY id ASC
</select>
Role findById(Integer id);
<select id="findById" resultMap="baseResultMap">
    SELECT
        id,
        role_name
    FROM
        role
    WHERE
        id = #{id}
</select>
@Test
public void findById() {
    try {
        IRoleDao roleDao = sqlSession.getMapper(IRoleDao.class);
        Role role = roleDao.findById(1);

        Assert.assertNotNull(role);
        Assert.assertNotNull(role.getRoleName());
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        sqlSession.close();
    }
}
@Select("SELECT id,role_name FROM role WHERE id = #{id}")
@Results( value = {
    @Result(id = true, property = "id", column = "id"),
    @Result(property="roleName", column="role_name")
})
Role findById(Integer id);

　　傳遞多個引數：

　　1>. 使用Map傳遞引數

List<Role> findByMap(Map<String, String> params);
<select id="findByMap" parameterType="map" resultMap="baseResultMap">
    SELECT 
        id,
        role_name
    FROM 
        role 
    WHERE 
        id != #{id}
    AND
        role_name = #{roleName}
</select>
@Test
public void findByMap() {
    try {
        Map<String, String> params = new HashMap<String, String>();
        params.put("id", "1");
        params.put("roleName", "管理員");
        IRoleDao roleDao = sqlSession.getMapper(IRoleDao.class);
        List<Role> roles = roleDao.findByMap(params);

        Assert.assertNotNull(roles);
        Assert.assertTrue(roles.size() > 0);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        sqlSession.close();
    }
}

　　2>. 使用註解方式傳遞引數

import org.apache.ibatis.annotations.Param;
List<Role> findByAnnotation(@Param("id") Integer id, @Param("roleName") String roleName);
<select id="findByAnnotation" resultMap="baseResultMap">
    SELECT
    　　id,
    　　role_name
    FROM
    　　role
    WHERE
    　　id != #{id}
    AND
    　　role_name = #{roleName}
</select>

　　3>. 使用JavaBean傳遞引數

package com.libing.helloworld.params;

public class RoleParam {

    private Integer id;

    private String roleName;

    public Integer getId() {
        return id;
    }

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

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

}
RoleParam.java
List<Role> findByRoleParam(RoleParam params);
<select id="findByRoleParam" parameterType="com.libing.helloworld.params.RoleParam" resultMap="baseResultMap">
    SELECT
        id,
        role_name
    FROM
        role
    WHERE
        id != #{id}
    AND
        role_name = #{roleName}
</select>

　　傳遞多個引數總結：

　　◊ 使用Map方式：Map擴充套件和維護困難，在實際應用中廢棄這種傳遞引數方式。

　　◊ 使用@Param註解方式：引數個數<=5時是最佳的傳參方式。

　　◊ JavaBean方式：當引數個數大於5個時，建議使用JavaBean方式。

2.2 insert

　　MyBatis在執行Insert語句之後返回一個整數，表示插入的記錄數。

int insert(Role role);
<insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="com.libing.helloworld.model.Role">
    INSERT role
    (
        role_name
    )
    VALUES
    (
        #{roleName}
    )
</insert>

　　其中，useGeneratedKeys：配置MyBatis使用JDBC的getGeneratedKeys()來獲取由資料庫內部生成的主鍵值，keyProperty：指定主鍵欄位。

@Test
public void insert() {
    try {
        IRoleDao roleDao = sqlSession.getMapper(IRoleDao.class);

        Role role = new Role();
        role.setRoleName("測試");
        int result = roleDao.insert(role);

        // 只插入一條記錄
        Assert.assertEquals(1, result);
        // id不為null
        Assert.assertNotNull(role.getId());
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        sqlSession.rollback();
        sqlSession.close();
    }
}
@Insert({ 
    "INSERT INTO role(id,role_name)",
    " VALUES ",
    "(#{id}, #{roleName})" 
})
@Options(useGeneratedKeys = true, keyProperty = "id")
@SelectKey(statement="select last_insert_id()", keyProperty = "id", before = false, resultType = int.class)
int insert(Role role);

2.3 update

　　MyBatis在執行update語句之後返回一個整數，表示更新的記錄數。

int update(Role role);
<update id="update" parameterType="com.libing.helloworld.model.Role">
    UPDATE role
    SET
        role_name = #{roleName}
    WHERE
        id = #{id}
</update>
@Test
public void Update() {
    try {
        IRoleDao roleDao = sqlSession.getMapper(IRoleDao.class);

        Role role = roleDao.findById(1);
        Assert.assertNotNull(role);

        role.setRoleName("測試");
        int result = roleDao.update(role);

        // 只修改一條記錄
        Assert.assertEquals(1, result);

        // 修改後的值
        Assert.assertEquals("測試", roleDao.findById(1).getRoleName());
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        sqlSession.rollback();
        sqlSession.close();
    }
}
@Update({
    "UPDATE role SET ",
    "role_name = #{roleName} ",
    "WHERE id = #{id}"
})
int update(Role role);

2.4 delete

　　MyBatis在執行delete語句之後返回一個整數，表示刪除的記錄數。

int deleteById(Integer id);
<delete id="deleteById">
    DELETE FROM role
    WHERE
        id = #{id}
</delete>
@Test
public void deleteById() {
    try {
        IRoleDao roleDao = sqlSession.getMapper(IRoleDao.class);

        Assert.assertNotNull(roleDao.findById(1));

        // 呼叫刪除方法
        Assert.assertEquals(1, roleDao.deleteById(1));
        // 再次查詢，判斷是否為null
        Assert.assertNull(roleDao.findById(1));
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 由於sqlSessionFactory.openSession()是不自動提交的，不手動執行sqlSession.commit()不會提交到資料庫
        sqlSession.rollback();
        sqlSession.close();
    }
}
@Delete("DELETE FROM role WHERE id = #{id}")
int deleteById(Integer id);

　　刪除多條記錄：

int deleteByIds(List<Integer> ids);
<delete id="deleteByIds" parameterType="java.util.List">
    DELETE FROM role
    WHERE
        id IN
    <foreach collection="list" index="index" item="item" open="("
        separator="," close=")">
        #{item}
    </foreach>
</delete>
@Test
public void deleteByIds() {
    try {
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);
        ids.add(2);

        IRoleDao roleDao = sqlSession.getMapper(IRoleDao.class);
        int result = roleDao.deleteByIds(ids);

        Assert.assertTrue(result > 0);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        sqlSession.rollback();
        sqlSession.close();
    }
}

相關文章