Mybatis

文采杰出發表於2024-05-16
MyBatis
  • 新建maven工程,在resources中新建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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://192.168.1.41:3306/test?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="*********"/>
            </dataSource>
        </environment>
    </environments>
<!--  每一個Mapper.xml都需要在Mybatis核心檔案中註冊  -->
    <mappers>
        <mapper resource="com/kuang/dao/UserMapper.xml"/>
    </mappers>
</configuration>
  • 根據資料表建立pojo物件
package com.kuang.pojo;

public class User {
    private int id;
    private String name;
    private String pwd;

    /*public User() {
    }*/

    public User(int id, String name, String pwd) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
    }

    /*public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }*/

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}
  • 建立SqlSession
package com.kuang.utils;

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 javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;


    static {
        {
            try {
                String resource = "mybatis-config.xml";
                InputStream inputStream = Resources.getResourceAsStream(resource);
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    public  SqlSession getSqlSession(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        return sqlSession;
    }
}
  • 建立UserMapper介面
package com.kuang.dao;

import com.kuang.pojo.User;
import org.apache.ibatis.annotations.MapKey;
import java.util.List;
import java.util.Map;

public interface UserMapper {
    //查詢全部使用者
    List<User> getUserList();
    //根據ID查詢使用者
    User getUserById(int id);
    //以map方式查詢使用者
    User getUserById2(Map<String,Object> map);

    //插入一個使用者
    int addUser(User user);
    //以map方式插入使用者
    int addUser2(Map<String,Object> map);
    //修改使用者
    int updateUser(User user);
    //刪除使用者
    int deleteUser(int id);
}
  • 建立UserMapper.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">
<!--namespace繫結一個對應的Dao/Mapper介面-->
<mapper namespace="com.kuang.dao.UserMapper">

    <select id="getUserList" resultType="com.kuang.pojo.User">
        select * from test.user;
    </select>

    <select id="getUserById" parameterType="int" resultType="com.kuang.pojo.User" >
        select * from test.user where id = #{id}
    </select>

    <select id="getUserById2" parameterType="map" resultType="com.kuang.pojo.User" >
        select * from test.user where id = #{id} and name=#{name}
    </select>

    <insert id="addUser" parameterType="com.kuang.pojo.User">
        insert into test.user(id,name,pwd) values(#{id},#{name},#{pwd});
    </insert>
<!--    以map方式插入使用者-->
    <insert id="addUser2" parameterType="hashmap">
        insert into test.user(id,name,pwd) values(#{userid},#{name},#{password});
    </insert>


    <update id="updateUser" parameterType="com.kuang.pojo.User">
        update test.user set name=#{name},pwd=#{pwd}  where id = #{id};
    </update>

    <delete id="deleteUser" parameterType="int">
        delete from test.user where id = #{id};
    </delete>
</mapper>
  • 測試:
package com.kuang.dao;

import com.kuang.pojo.User;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.HashMap;
import java.util.List;
import java.util.TreeMap;

public class UserMapperTest {
    @Test
    public void test(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        //方式一,getMapper
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.getUserList();
        for (User user : userList) {
            System.out.println(user);
        }

        //方式二
      /*  List<User> userList1 = sqlSession.selectList("getUserList");
        for (User user : userList1) {
            System.out.println(user);
        }*/
        //關閉sqlSession
        sqlSession.close();

    }

    @Test
    public void getUserById(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.getUserById(1);
        System.out.println(user);
        sqlSession.close();
    }

    @Test
    public void getUserById2(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Object> map = new HashMap<>();
        map.put("id",5);
        map.put("name","單依純");
        User user2 = mapper.getUserById2(map);
        System.out.println(user2);
        sqlSession.close();
    }

    @Test
    public void addUser(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.addUser(new User(3,"哈哈","123"));
        //提交事務
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void updateUser(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.updateUser(new User(3,"章子怡","123123"));
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void deleteUser(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.deleteUser(3);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void addUser2(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Object> map = new HashMap<>();
        map.put("userid",6);
        map.put("name","李冰冰");
        map.put("password",123);

        mapper.addUser2(map);
        sqlSession.commit();
        sqlSession.close();
    }
}
  • 模糊查詢
    在UserMapper中新增
//模糊查詢
    List<User> getUserLike(String value);
  • 在UserMapper.xml中實現
<select id="getUserLike" resultType="com.kuang.pojo.User">
        select * from test.user where name like #{value}
</select>
  • 測試:
@Test
    public void getUserLike(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.getUserLike("%範%");
        for (User user : userList) {
            System.out.println(user);
        }
    }


也就是在select * from test.user where name like "%"#{value}"%"新增了模糊查詢,在測試執行的時候不用再新增%了,List<User> userList = mapper.getUserLike("範");,在測試執行程式碼時新增%安全.

  • 在mybatis-config.xml引入外部配置檔案,在mybatis-config.xml中有層級順序,如圖所示:

    properties標籤需要放在標籤中第一個

  • 在mybatis-config.xml中給實體類起別名,它僅用於 *.xml配置，意在降低冗餘的全限定類名書寫
<typeAliases>
        <typeAlias type="com.kuang.pojo.User" alias="User"></typeAlias>
    </typeAliases>

這樣在UserMapper.xml中的resultType填寫之前定義的別名.

    <select id="getUserList" resultType="User">
        select * from test.user;
    </select>

  • 對映器（mappers）
    在mybatis-config.xml配置檔案中新增對映器,告訴 MyBatis 到哪裡去找到這些語句
    方式一:

    方式二:使用對映器介面實現類的完全限定類名

    方式三:
<!-- 將包內的對映器介面實現全部註冊為對映器 -->
    <mappers>
<!--        <mapper resource="com/kuang/dao/UserMapper.xml"/>-->
        <package name="com.kuang.dao"/>
    </mappers>

其中注意點和方式二相同

  • resultMap結果對映
    顯式使用外部的 resultMap，解決列名不匹配的另外一種方式,當pojo物件中的欄位和列中欄位不一致時,

    透過結果集對映
    <!--結果集對映-->
    <resultMap id="UserMap" type="User">
<!--        <result column="id" property="id"/>-->
<!--        <result column="name" property="name"/>-->
        <result column="pwd" property="password"/>
    </resultMap>
    <select id="getUserById" resultMap="UserMap" >
        select * from test.user where id = #{id}
    </select>
  • Mybatis日誌

    在配置檔案mybatis-config.xml中新增設定,其中STDOUT_LOGGING為控制檯標準輸出.
  • 使用log4j
    <setting name="logImpl" value="LOG4J"/>
    在resource下新增log4j.properties
log4j.rootLogger = debug,console,file
#debug日誌輸出到控制檯
log4j.appender.console=org.apache.log4j.ConsoleAppender
#日誌格式
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.conversionPattern=[%-10p]%r %c%t%d{yyyy-MM-dd HH:mm:ss:SSS} %m%n


#appender到檔案
log4j.appender.file=org.apache.log4j.FileAppender
#檔案格式
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.conversionPattern=[%-10p]%r %c%t%d{ } %m%n
log4j.appender.file.file=D:/Feibao/log4j.log 
log4j.appender.file.encoding=UTF-8

#RollingFileAppender
log4j.appender.rollingFile=org.apache.log4j.RollingFileAppender
log4j.appender.rollingFile.layout=org.apache.log4j.PatternLayout
log4j.appender.rollingFile.layout.conversionPattern=[%-10p]%r %c%t%d{yyyy-MM-dd HH:mm:ss} %m%n
log4j.appender.rollingFile.file=D:/Feibao/log4j.log
log4j.appender.rollingFile.encoding=UTF-8
log4j.appender.rollingFile.maxFileSize=1MB
log4j.appender.rollingFile.maxBackupIndex=5

#DailyRollingFileAppender,用來建立每天滾動的日誌檔案
log4j.appender.dailyRollingFile=org.apache.log4j.DailyRollingFileAppender
#PatternLayout 允許你使用轉換模式（conversion pattern）來定義日誌輸出的格式。
log4j.appender.dailyRollingFile.layout=org.apache.log4j.PatternLayout
#[%-10p]左對齊並佔用至少 10 個字元的寬度。
#%r: 輸出自應用啟動到輸出該日誌資訊耗費的毫秒數。
#%c: 輸出日誌事件所屬的 logger 的名字。
#%t: 輸出產生該日誌事件的執行緒名。
#%d{yyyy-MM-dd HH:mm:ss}: 輸出日誌事件的日期或時間，日期或時間轉換格式由花括號內的內容指定。
#%m: 輸出日誌訊息。
#%n: 輸出一個平臺相關的行分隔符（如 Unix 系統是 \n，Windows 系統是 \r\n）。
log4j.appender.dailyRollingFile.layout.conversionPattern=[%-10p]%r %c%t%d{yyyy-MM-dd HH:mm:ss} %m%n
log4j.appender.dailyRollingFile.file=D:/Feibao/log4j.log
#日誌檔案的字元編碼為 UTF-8。
log4j.appender.dailyRollingFile.encoding=UTF-8
#DatePattern='.'yyyy-MM-dd 表示日誌檔案每天滾動一次，並且在檔名中插入日期，用 . 分隔。
log4j.appender.dailyRollingFile.datePattern = '.'yyyy-MM-dd HH-mm-ss

可以另外參考https://baike.baidu.com/item/log4j/480673?fr=ge_ala

Mybatis實現分頁

UserMapper介面

List<User> getUserByLimit(Map<String,Integer> map);

UserMapper.xml

<!--    分頁查詢-->
    <select id="getUserByLimit" parameterType="map" resultType="User">
        select * from test.user limit ${startIndex},#{pageSize};
    </select>

測試

public class UserDaoTest {
    @Test
    public void test(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Integer> map = new HashMap<>();
        map.put("startIndex",0);
        map.put("pageSize",2);
        List<User> userList = mapper.getUserByLimit(map);
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }
}
  • 透過RowBounds實現分頁,不使用sql關鍵字limit
    在UserMapper介面中定義方法List<User> getUserByRowBounds();
    在UserMapper.xml中實現List<User> getUserByRowBounds();
    測試:
@Test
    public void getUserByRowBounds(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        RowBounds rowBounds = new RowBounds(1, 2);
        List<Object> userList = sqlSession.selectList("com.kuang.dao.UserMapper.getUserByRowBounds", null, rowBounds);
        for (Object user : userList) {
            System.out.println(user);
        }
        sqlSession.close();

    }
  • 使用註解,複雜sql語句還是用xml方式.

    最後測試
public class UserMapperDu {
    @Test
    public void test(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.getUsers();
        for (User user : users) {
            System.out.println(user);
        }
        sqlSession.close();
    }
}
使用自動提交事務

在MybatisUtils.java中修改

字串替換

預設情況下，使用 #{} 引數語法時，MyBatis 會建立 PreparedStatement 引數佔位符，並透過佔位符安全地設定引數（就像使用 ? 一樣）。
當 SQL 語句中的後設資料（如表名或列名）是動態生成的時候，字串替換將會非常有用。 舉個例子，如果你想 select 一個表任意一列的資料時，不需要這樣寫：

@Select("select * from user where id = #{id}")
User findById(@Param("id") long id);

@Select("select * from user where name = #{name}")
User findByName(@Param("name") String name);

@Select("select * from user where email = #{email}")
User findByEmail(@Param("email") String email);
// 其它的 "findByXxx" 方法

而是可以只寫這樣一個方法：

@Select("select * from user where ${column} = #{value}")
User findByColumn(@Param("column") String column, @Param("value") String value);

 其中${column}會被直接替換，而 #{value} 會使用 ? 預處理。 這樣，就能完成同樣的任務,其中${column}和@Param("column")值要相同,都是column, String後面的的column可以自定義名稱; #{value}和 @Param("value")值要相同,都是value,String後面的vale可以自定義名稱.

User userOfId1 = userMapper.findByColumn("id", "1");或者返回int引數的字串表示形式。User userOfId1 = userMapper.findByColumn("id", String.valueOf(1));
User userOfNameKid = userMapper.findByColumn("name", "kid");
User userOfEmail = userMapper.findByColumn("email", "noone@nowhere.com");

練習在UserMapper.xml中新增增刪改查並使用註解

public interface UserMapper {

    @Select("select * from test.user")
    List<User> getUsers();

/*    @Select("select * from user where id =#{id1}")
    User getUserById(@Param("id1") int id);*/


    @Select("select * from user where ${column} = #{value}")
    User getUserById(@Param("column") String column1, @Param("value") String value1);

    @Insert("insert into test.user(id,name,pwd) value (#{id},#{name},#{password})")
    int addUser(User user);

    @Update("update test.user set name=#{name},pwd=#{password} where id=#{id}")
    int updateUser(User user);

    @Delete("delete from user where id = #{uid}")
    int deleteUser(@Param("uid") int id);
}

測試:

public class UserMapperDu {
    @Test
    public void test(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.getUsers();
        for (User user : users) {
            System.out.println(user);
        }
        sqlSession.close();
    }

     @Test
    public void test2(){
         SqlSession sqlSession = new MybatisUtils().getSqlSession();
         UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//         User userById = mapper.getUserById(1);
         User userById = mapper.getUserById("id", String.valueOf(1));
         System.out.println(userById);
         sqlSession.close();
     }

     @Test
    public void insertTest(){
         SqlSession sqlSession = new MybatisUtils().getSqlSession();
         UserMapper mapper = sqlSession.getMapper(UserMapper.class);
         mapper.addUser(new User(8,"張靚穎","123"));
         sqlSession.close();

     }

     @Test
    public void updateTest(){
         SqlSession sqlSession = new MybatisUtils().getSqlSession();
         UserMapper mapper = sqlSession.getMapper(UserMapper.class);
         mapper.updateUser(new User(5,"周筆暢","123"));
         sqlSession.close();
     }

     @Test
    public void deleteTest(){
         SqlSession sqlSession = new MybatisUtils().getSqlSession();
         UserMapper mapper = sqlSession.getMapper(UserMapper.class);
         mapper.deleteUser(8);
         sqlSession.close();
     }
}

多對一 association

  • 建老師表
  • 建學生表
CREATE TABLE `student2` (
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL,
    `tid` INT(10) DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
);
  • StudentMapper.xml
    association – 一個複雜型別的關聯；許多結果將包裝成這種型別
    巢狀結果對映 – 關聯可以是 resultMap 元素，或是對其它結果對映的引用
    巢狀查詢處理:
<select id="getStudent" resultMap="StudentTeacher">
    select * from test.student2;
</select>
<resultMap id="StudentTeacher" type="Student">

    <!--複雜的屬性,單獨處理
    物件用association; 集合用collection-->
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>

<select id="getTeacher" resultType="Teacher">
    select * from test.teacher where id = #{id}
</select>
```
  • Mybatis關聯的巢狀結果對映
  1. 建立兩個pojo類
import lombok.Data;

//多對一
@Data
public class Student {
    private int id;
    private String name;
    //學生關聯一個老師
    private Teacher teacher;
}
---
public class Teacher {
    private int id;
    private String name;

    public Teacher(int id, String name) {
        this.id = id;
        this.name = name;
    }

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

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

    public void setName(String name) {
        this.name = name;
    }
}
* 配置Mapper介面

public interface StudentMapper {
//查詢所有的學生資訊,以及對應的老師的資訊
// public List getStudent();
public List getStudent2();
}

* 在StudentMapper.xml檔案中定義一個<resultMap>來指定如何將查詢結果對映到這些Java物件

<resultMap id="StudentTeacherResultMap" type="Student">
    <id property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <id property="id" column="tid"/>
        <result property="name" column="tname"/>
    </association>
</resultMap>

<select id="getStudent2" resultMap="StudentTeacherResultMap">
    SELECT
        s.id AS sid,
        s.name AS sname,
        t.id AS tid,
        t.name AS tname
    FROM
        test.student2 s
            INNER JOIN
        test.teacher t ON s.tid = t.id
</select>

在這個<resultMap>中：
<id>和<result>標籤用於對映Student物件的屬性。
<association>標籤用於處理Student物件中的teacher屬性，它是一個Teacher型別的物件。
在<association>標籤內部，再次使用<id>和<result>標籤來對映Teacher物件的屬性。
最後一個<select>查詢來使用這個<resultMap>.

相關文章