第一個mybatis程式，實現增刪改查CRUD

xiezhr發表於2023-05-05

MyBatis

mybatis 介紹

mybatis 本是apache的一個開源專案iBatis,
2010年這個專案由apache遷移到了googlecode，並且改名為MyBatis，2013年11月遷移到Github。
MyBatis 是一款優秀的持久層框架，它支援定製化 SQL、儲存過程以及高階對映。
MyBatis 避免了幾乎所有的 JDBC 程式碼和手動設定引數以及獲取結果集。
MyBatis 可以使用簡單的 XML 或註解來配置和對映原生資訊，將介面和 Java 的 POJOs(普通的 Java物件)對映成資料庫中的記錄

Mybatis官方檔案 :
http://www.mybatis.org/mybatis-3/zh/index.html

GitHub :https://github.com/mybatis/mybatis-3

程式碼演示

所需環境

jdk1.8.0_91
mysql-5.7.29
apache-maven-3.6.3

建立資料庫

CREATE DATABASE `mybatis`;

USE `mybatis`;

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
`id` int(20) NOT NULL,
`name` varchar(30) DEFAULT NULL,
`pwd` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `user`(`id`,`name`,`pwd`) values (1,'張三','123456'),(2,'李四','abcdef'),(3,'王五','987654');

使用idea建立專案並匯入匯入mybatis所需jar包

<dependencies>
        <!--mysql驅動-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <!--mybatis包-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
        <!--junit 測試包-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

    </dependencies>

編寫mybatis核心配置檔案

該配置檔案主要是配置連線mysql的基本資訊及註冊mapper(具體配置參考官方檔案)

<?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/mybatis?useUnicode=true&amp;characterEncoding=utf-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/xiezhr/Dao/UserMapper.xml"></mapper>
    </mappers>
</configuration>

編寫mybatis工具類

檢視官方檔案,我們這裡要封裝一個工具類生成SqlSession物件，SqlSession用於後面的執行sql

package com.xiezhr.util;

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

public class MysqlUtil {

    private static SqlSessionFactory sqlSessionFactory;
    static {
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    }

    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }


}

建立對應資料庫表的實體類

各個屬性得命名必須與資料庫欄位一一對應，具體如下所示，資料庫對應欄位為id,name,pwd

package com.xiezhr.pojo;

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

    public String getPwd() {
        return pwd;
    }

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

    public String getName() {
        return name;
    }

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

    public int getId() {
        return id;
    }

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

    public User() {
    }

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

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

編寫Mapper介面

該介面對應原來的dao,具體程式碼如下

package com.xiezhr.dao;

import com.xiezhr.pojo.User;

import java.util.List;

public interface UserMapper {
    List<User> getUserList();
}

編寫Mapper.xml 檔案

由於我們使用了mybatis,所以這的xml檔案相當於我們原來dao得實現類daoimpl。namespace屬性對應著介面，不能寫錯，標籤表示是個查詢語句。id 屬性對應著介面的方法，result Type代表返回得型別，即對應這pojo實體。具體程式碼如下

<?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.xiezhr.dao.UserMapper">
    <select id="getUserList" resultType="com.xiezhr.pojo.User">
     select * from mybatis.user;
    </select>
</mapper>

到這一步我們第一個mybatis實現查詢就基本大功告成了，接下來就要編寫測試類測試我們寫得程式碼

package com.xiezhr.dao;

import com.xiezhr.pojo.User;
import com.xiezhr.util.MysqlUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class TestUserDao {
    @Test
    public void selectUser(){
        SqlSession sqlSession = MysqlUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.getUserList();

        for (User user : userList) {
            System.out.println(user);
        }

    }
}

經過測試後輸出測試結果

D:\Java\jdk1.8.0_91\bin\java.exe -ea -Didea.test.cyclic.buffer.size=1048576 "-javaagent:D:\JetBrains\IntelliJ IDEA 2019.3.3\lib\idea_rt.jar=54576:D:\JetBrains\IntelliJ IDEA 2019.3.3\bin" -Dfile.encoding=UTF-8 -classpath "D:\JetBrains\IntelliJ IDEA 2019.3.3\lib\idea_rt.jar;D:\JetBrains\IntelliJ IDEA 2019.3.3\plugins\junit\lib\junit5-rt.jar;D:\JetBrains\IntelliJ IDEA 2019.3.3\plugins\junit\lib\junit-rt.jar;D:\Java\jdk1.8.0_91\jre\lib\charsets.jar;D:\Java\jdk1.8.0_91\jre\lib\deploy.jar;D:\Java\jdk1.8.0_91\jre\lib\ext\access-bridge-64.jar;D:\Java\jdk1.8.0_91\jre\lib\ext\cldrdata.jar;D:\Java\jdk1.8.0_91\jre\lib\ext\dnsns.jar;D:\Java\jdk1.8.0_91\jre\lib\ext\jaccess.jar;D:\Java\jdk1.8.0_91\jre\lib\ext\jfxrt.jar;D:\Java\jdk1.8.0_91\jre\lib\ext\localedata.jar;D:\Java\jdk1.8.0_91\jre\lib\ext\nashorn.jar;D:\Java\jdk1.8.0_91\jre\lib\ext\sunec.jar;D:\Java\jdk1.8.0_91\jre\lib\ext\sunjce_provider.jar;D:\Java\jdk1.8.0_91\jre\lib\ext\sunmscapi.jar;D:\Java\jdk1.8.0_91\jre\lib\ext\sunpkcs11.jar;D:\Java\jdk1.8.0_91\jre\lib\ext\zipfs.jar;D:\Java\jdk1.8.0_91\jre\lib\javaws.jar;D:\Java\jdk1.8.0_91\jre\lib\jce.jar;D:\Java\jdk1.8.0_91\jre\lib\jfr.jar;D:\Java\jdk1.8.0_91\jre\lib\jfxswt.jar;D:\Java\jdk1.8.0_91\jre\lib\jsse.jar;D:\Java\jdk1.8.0_91\jre\lib\management-agent.jar;D:\Java\jdk1.8.0_91\jre\lib\plugin.jar;D:\Java\jdk1.8.0_91\jre\lib\resources.jar;D:\Java\jdk1.8.0_91\jre\lib\rt.jar;F:\workspace_idea\Mybatis-test\mybatis-01\target\test-classes;F:\workspace_idea\Mybatis-test\mybatis-01\target\classes;D:\maven\repository\mysql\mysql-connector-java\5.1.46\mysql-connector-java-5.1.46.jar;D:\maven\repository\org\mybatis\mybatis\3.5.2\mybatis-3.5.2.jar;D:\maven\repository\junit\junit\4.12\junit-4.12.jar;D:\maven\repository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar" com.intellij.rt.junit.JUnitStarter -ideVersion5 -junit4 com.xiezhr.dao.TestUserDao,selectUser
Tue Apr 14 22:54:48 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
User{id=1, name='張三', pwd='322334'}
User{id=2, name='李四', pwd='123456'}
User{id=3, name='王五', pwd='123456'}

擴充套件

根據id查詢使用者
1.在UserMapper介面中新增相應的方法selectUserById(id)

public interface UserMapper {
    //根據ID查詢使用者
    User selectUserById(int id);
}

2.在UserMapper.xml 中新增相應select 語句

<mapper namespace="com.xiezhr.dao.UserMapper">
    
    <select id="selectUserById" parameterType="int" resultType="com.xiezhr.pojo.User">
        select * from mybatis.user where id = #{id}
    </select>
</mapper>

3.新增測試類

@Test
    public void selectUserByid(){
        SqlSession sqlSession = MysqlUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.selectUserById(1);
        System.out.println(user);
    }

測試透過

Wed Apr 15 23:08:00 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
User{id=1, name='張三', pwd='322334'}

根據使用者姓名密碼查詢使用者資訊
1.在UserMapper介面中新增方法

public interface UserMapper {
    //根據使用者名稱密碼查詢使用者資訊
    User selectUserByNP(@Param("name") String name,@Param("pwd") String pwd);
}

2.在UserMapper.xml 中新增select語句

<mapper namespace="com.xiezhr.dao.UserMapper">
    <select id="selectUserByNP" resultType="com.xiezhr.pojo.User">
        select * from mybatis.user where name=#{name} and pwd=#{pwd}
    </select>
</mapper>

3.新增測試類

    @Test
    public void selectUserByNP(){
        SqlSession sqlSession = MysqlUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.selectUserByNP("張三","322334");
        System.out.println(user);

    }

測試成功

Wed Apr 15 23:24:02 CST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
User{id=1, name='張三', pwd='322334'}

以上透過使用者名稱和密碼查詢使用者，傳參還可以透過萬能的map實現，具體程式碼如下

1.向UserMapper介面中新增方法

public interface UserMapper {
    //根據使用者名稱和密碼查詢使用者資訊
    User queryUserByNP(Map<String,Object> map);
}

2.向UserMapper.xml 中新增select語句，其中引數型別為map

<mapper namespace="com.xiezhr.dao.UserMapper">

    <select id="queryUserByNP" parameterType="map" resultType="com.xiezhr.pojo.User">
        select * from mybatis.user where name=#{name} and pwd=#{pwd}
    </select>
</mapper>

新增測試，在使用過程中，map的key對應著UserMapper.xml中取值,map在put值時候沒有先後順序

 @Test
    public void queryUserByNp(){
        SqlSession sqlSession = MysqlUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        Map<String,Object> map = new HashMap<String,Object>();
        map.put("name","張三");
        map.put("pwd","322334");
        User user = mapper.queryUserByNP(map);
        System.out.println(user);
    }

模糊查詢實現
1.在Java程式碼中新增sql萬用字元

string wildcardname = “%smi%”;
list<name> names = mapper.selectlike(wildcardname);

<select id=”selectlike”>
select * from foo where bar like #{value}
</select>

2.在sql語句中拼接萬用字元，會引起sql注入

string wildcardname = “smi”;
list<name> names = mapper.selectlike(wildcardname);

<select id=”selectlike”>
    select * from foo where bar like "%"#{value}"%"
</select>

華麗的分割線

接下來我們分別來實現insert、update、delete

insert

在之前編寫的UserMapper 介面中新增增加方法

public interface UserMapper {
    //新增一條使用者資訊
    int addUser(User user);
}

2.在UserMapper.xml 中寫insert 語句

<insert id="addUser" parameterType="com.xiezhr.pojo.User">
        insert into mybatis.user values(#{id},#{name},#{pwd})
</insert>

新增測試類
insert、update、delete 一定要提交事務，千萬千萬不能忘記了

@Test
    public void addUser(){
        SqlSession sqlSession = MysqlUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user = new User(4, "大頭兒子", "123456");
        mapper.addUser(user);
        sqlSession.commit();  //增刪改一定要提交事務
        sqlSession.close();
    }

update

在之前編寫的UserMapper 介面中新增update方法

public interface UserMapper {
    //修改一條記錄
    int updateUserById(int id);
}

2.在UserMapper.xml 中寫insert 語句

<update id="updateUserById" parameterType="int">
        update mybatis.user set name='小頭爸爸' where id=#{id}
</update>

新增測試類
insert、update、delete 一定要提交事務，千萬千萬不能忘記了

    @Test
    public void updateUserById(){
        SqlSession sqlSession = MysqlUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.updateUserById(4);
        sqlSession.commit();  //增刪改一定要提交事務
        sqlSession.close();
    }

delete

在之前編寫的UserMapper 介面中新增delete方法

public interface UserMapper {
    //根據ID刪除一條記錄
    int deleteUserById(int id);
}
}

2.在UserMapper.xml 中寫insert 語句

<delete id="deleteUserById" parameterType="int">
        delete from mybatis.user where id=#{id}
</delete>

新增測試類
insert、update、delete 一定要提交事務，千萬千萬不能忘記了

    @Test
    public void deletUserById(){
        SqlSession sqlSession = MysqlUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.deleteUserById(4);
        sqlSession.commit();  //增刪改一定要提交事務
        sqlSession.close();
    }

注意

所有的insert、update、delete 必須要提交事務
介面中所有的普通引數儘量寫上@Param 引數，尤其是多個引數的時候一定要寫上
有些時候由於業務需要需要可透過map傳值
為了規範在sql配置檔案中即本例的UserMapper.xml 中select inset delete update 儘量寫上Parameter引數和resultType

可能出現問題說明：Maven靜態資源過濾問題

<build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
    </build>

在靜態資源的過濾中，基本的元素有三種：

directory：指定資源所在的目錄，這個目錄的路徑是相對於pom.xml檔案；
includes：指定要包含哪些檔案，其中包括inlcude子節點來指定匹配的模式；
excludes：指定要排除哪些檔案，其中包括exclude子節點來指定匹配的模式；
filtering：指定哪些檔案需要過濾，這個過濾的目的是為了替換其中的佔位符${}，其中的佔位符屬性在pom.xml檔案中的中指定；

IDEA SpringBoot-Mybatis-plus 實現增刪改查(CRUD)
2022-04-22
IdeaSpring BootMyBatis
Mybatis-plus實現簡單增刪改查
2020-10-21
MyBatis
Python中CRUD增刪改查教程
2024-06-03
Python
mybatis的增刪改查
2024-12-05
MyBatis
mybatis實現MySQL資料庫的增刪改查
2020-12-29
MyBatisMySql資料庫
SpringBoot+Mybatis增刪改查實戰
2019-05-26
Spring BootMyBatis
一個系列搞定MyBatis：MyBatis快速上手增刪改查
2020-10-21
MyBatis
一起學Vue：CRUD(增刪改查)
2020-10-28
Vue
Spring+SpringMVC+Mybatis實現增刪改查--（七）總結篇
2018-06-15
SpringMVCMyBatis
MyBatis初級實戰之二：增刪改查
2021-01-18
MyBatis
第一天——Mybatis增刪改
2024-03-14
MyBatis
springboot整合mybatis增刪改查（三）：mybatis逆向工程
2019-01-29
Spring BootMyBatis
js實現表格的增刪改查
2019-04-12
JS
運用layui實現增刪改查
2019-07-11
UI
SSM-員工管理專案實戰-CRUD-增刪改查
2021-03-28
SSM
Mybatis 學習筆記（二）——原生DAO實現資料增刪改查
2018-10-13
MyBatis筆記
寫一個簡單的Linkedlist，實現增刪改查
2018-11-22
使用mybatis開發的增刪改查操作
2020-11-13
MyBatis
單連結串列實現增刪改查
2020-11-16
Go實現對MySQL的增刪改查
2021-09-09
GoMySql
SSM整合_年輕人的第一個增刪改查_查詢
2022-04-13
SSM
增刪改查
2024-03-23
Node.js+Express+Mysql 實現增刪改查
2019-04-02
Node.jsExpressMySql
Java實現簡單的增刪改查操作
2020-07-21
Java
jQuery實現購物車的增刪改查
2020-11-05
jQuery
Entity Framework使用DBContext實現增刪改查示例
2023-02-06
FrameworkContext
封裝模組實現商品增刪改查
2021-01-03
封裝
SSM整合_年輕人的第一個增刪改查_新增
2022-04-13
SSM
Laravel核心程式碼學習 -- Model增刪改查底層實現
2018-05-31
Laravel
Laravel核心程式碼學習 — Model增刪改查底層實現
2019-03-04
Laravel
無需程式設計，基於PostgreSQL零程式碼生成CRUD增刪改查RESTful API介面
2022-02-22
程式設計SQLRESTAPI
webpack4+express+mongodb+vue 實現增刪改查
2018-11-08
WebExpressMongoDBVue
JDBC連線mysql-8.0實現增刪改查
2018-06-24
JDBCMySql
python 連線mongodb實現增刪改查例項
2018-05-30
PythonMongoDB
SpringMVC+Spring Data JPA實現增刪改查操作
2018-03-17
SpringMVC
連線資料庫並實現增、刪、改、查
2024-11-05
資料庫
JDBC連線資料庫實現增刪改查
2024-12-03
JDBC資料庫
koa+mysql實現增刪改查-全棧之路
2021-09-09
MySql全棧

第一個mybatis程式，實現增刪改查CRUD

mybatis 介紹

程式碼演示

華麗的分割線

接下來我們分別來實現insert、update、delete

insert

update

delete

相關文章