当前位置：首页>后端>正文

Spring数据库MyBatis基本查询

后端2024-05-05 01:21:35

MyBatis

是支持定制化 SQL，存储过程以及高级映射的优秀的持久层框架。
MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。
MyBatis 可以对配置和原生Map使用简单的 XML 或注解。
将接口和 Java 的 POJOs(Plain Old Java Objects，普通的 Java对象)映射成数据库中的记录。

数据库的基本增删改查

select 按照主键查询

//select 按照主键查询
@Select("""
        select id,user_id,title,summary,read_count,create_time,update_time
        from article where id = #{ArticleID}
        """)
// 查询结果集和PO映射  列和PO结果映射
@Results(id = "BaseArticleMap", value = {
        @Result(id = true, column = "id", property = "id"),
        @Result(column = "user_id", property = "user_id"),
        @Result(column = "title", property = "title"),
        @Result(column = "summary", property = "summary"),
        @Result(column = "read_count", property = "read_count"),
        @Result(column = "create_time", property = "create_time"),
        @Result(column = "update_time", property = "update_time"),
})
ArticlePo selectById(@Param("ArticleID") Integer id);
        
 
// Spring Test       
        
@Autowired(required = false)
private ArticleMapper articleMapper;


@Test
void onTestSelect() {
    ArticlePo articlePo = articleMapper.selectById(1);
    System.out.println("articlePo = " + articlePo);
}

//打印日志
JDBC Connection [HikariProxyConnection@1971855969 wrapping com.mysql.cj.jdbc.ConnectionImpl@563ccd31] will not be managed by Spring
==>  Preparing: select id,user_id,title,summary,read_count,create_time,update_time from article where id = ?
==> Parameters: 1(Integer)
<==    Columns: id, user_id, title, summary, read_count, create_time, update_time
<==        Row: 1, 9527, Spring Boot 核心注解, 核心注解的主要作业作用, 20, 2023-07-12 11:34:36, 2023-07-12 11:34:50
<==      Total: 1

install 添加

//install
@Insert("""
         INSERT INTO article (user_id, title, summary, read_count, create_time, update_time) 
         VALUES 
         (#{user_id},#{title},#{summary},#{read_count},#{create_time},#{update_time})
        """)
int insertArticle(ArticlePo articlePo);
   
        
//Spring Test
     @Test
    void onInsertPO() {
        ArticlePo articlePo = new ArticlePo();
        articlePo.setUser_id(888);
        articlePo.setTitle("Android OS 系统开发");
        articlePo.setSummary("View,Hander,App");
        articlePo.setRead_count(50);
        articlePo.setCreate_time(LocalDateTime.now());
        articlePo.setUpdate_time(LocalDateTime.now());
        Integer rows = articleMapper.insertArticle(articlePo);
        System.out.println("rows = " + rows);
    }
        
//打印日志
==>  Preparing: INSERT INTO article (user_id, title, summary, read_count, create_time, update_time) VALUES (?,?,?,?,?,?)
==> Parameters: 888(Integer), Android OS 系统开发(String), View,Hander,App(String), 50(Integer), 2023-08-14T11:23:03.394367600(LocalDateTime), 2023-08-14T11:23:03.394367600(LocalDateTime)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4f59a516]
rows = 1

update更新

@Update("""
        UPDATE article set read_count = #{readCount} WHERE id = #{id}
        """)
int updateArticle(int id, int readCount);
        
        
//Spring Test
    @Test
    void onUpdateArticle() {
        Integer rows = articleMapper.updateArticle(3, 555);
        System.out.println("rows = " + rows);
    }
        
//打印日志
==>  Preparing: UPDATE article set read_count = WHERE id = ?
==> Parameters: 555(Integer), 3(Integer)
<==    Updates: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46fc522d]
rows = 0

delete删除

@Delete("""
        DELETE from  article where  id = #{id}
        """)
int deleteArticle(int id);
        
        
//Spring test 
    @Test
    void onDeleteArticle() {
        Integer rows = articleMapper.deleteArticle(3);
        System.out.println("rows = " + rows);
    }
        
//打印日志
==>  Preparing: DELETE from article where id = ?
==> Parameters: 8(Integer)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7e8279e5]
rows = 1

Result查询结果转换

List<*>

@Select("""
        select id,user_id,title,summary,read_count,create_time,update_time
        from article where user_id = #{userId}
                    """)
@Results(id = "BaseArticleMap", value = {
        @Result(id = true, column = "id", property = "id"),
        @Result(column = "user_id", property = "user_id"),
        @Result(column = "title", property = "title"),
        @Result(column = "summary", property = "summary"),
        @Result(column = "read_count", property = "read_count"),
        @Result(column = "create_time", property = "create_time"),
        @Result(column = "update_time", property = "update_time"),
})
List<ArticlePo> selectListArticle(Integer userId);

对象ArticlePo

@Select("""
        select * from article where id = #{article_Id}
        """)
@ResultMap(value = "BaseArticleMap")
ArticlePo selectById(Integer article_Id);

xml转换对象

resources - mappers - ArticleMapper.xml 新建mapper类型xml转换

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.alee.mybatis.springmybatis.mapper.ArticleDao">
    <!-- 定义ResultMap -->
    <resultMap id="ActionBaseMapper" type="com.example.alee.mybatis.springmybatis.po.ArticlePo">
        <id column="id" property="id"/>
        <result column="user_id" property="user_id"/>
        <result column="title" property="title"/>
        <result column="summary" property="summary"/>
        <result column="read_count" property="read_count"/>
        <result column="create_time" property="create_time"/>
        <result column="update_time" property="update_time"/>
    </resultMap>
</mapper>

@Select("""
        select * from article where id = #{article_Id}
        """)
@ResultMap("ActionBaseMapper")  //引用资源文件的id
ArticlePo selectMappersXmlById(Integer article_Id);

使用（SqlProvider）提供者操作

1. 新建一个提供者，对应操作sql静态函数

package com.example.alee.mybatis.springmybatis.provider;

public class SqlProvider {

    public static String selectArticle() {
        return "select * from article where id=#{id}";
    }

    public static String updateArticleTime() {
        return "update article set update_time=#{newTime} where id=#{id}";
    }


    public static String insertArticle() {
        return """
                   INSERT INTO article (user_id, title, summary, read_count, create_time, update_time) 
                   VALUES 
                   (#{user_id},#{title},#{summary},#{read_count},#{create_time},#{update_time})
                """;
    }


    public static String deleteArticleById() {
        return "DELETE from  article where  id = #{id}";
    }


}

2. 接口查询类 ArticleRepository

public interface ArticleRepository {


    //使用提供者查询
    @SelectProvider(type = SqlProvider.class, method = "selectArticle")
    ArticlePo selectByPrimary(Integer id);

    //提供者进行修改
    @SelectProvider(type = SqlProvider.class, method = "updateArticleTime")
    int updateTimePrimary(LocalDateTime newTime, Integer id);


    @InsertProvider(type = SqlProvider.class, method = "insertArticle")
    int insertArticle(ArticlePo articlePo);


    @DeleteProvider(type = SqlProvider.class, method = "deleteArticleById")
    int deleteArticleById(Integer id);
}

3. Spring Test 查询操作

@SpringBootTest
public class RepositoryTest {

    @Autowired(required = false)
    private ArticleRepository articleRepository;

    @Test
    void testSelectProvider() {
        ArticlePo articlePo = articleRepository.selectByPrimary(1);
        System.out.println("articlePo = " + articlePo);
    }

    @Test
    void testUpdateTimePrimary() {
        int result = articleRepository.updateTimePrimary(LocalDateTime.now(), 5);
        System.out.println("result = " + result);
    }

    @Test
    void testInsertArticle() {
        ArticlePo articlePo = new ArticlePo();
        articlePo.setUser_id(0125);
        articlePo.setTitle("Spring Boot xxx");
        articlePo.setSummary("核心讲解，难点剖析，源码分析");
        articlePo.setRead_count(15);
        articlePo.setCreate_time(LocalDateTime.now());
        articlePo.setUpdate_time(LocalDateTime.now());
        int result = articleRepository.insertArticle(articlePo);
        System.out.println("result = " + result);
    }

    @Test
    void testDeleteArticleById() {
        int result = articleRepository.deleteArticleById(7);
        System.out.println("result = " + result);
    }

}

End。

简书·Spring数据库MyBatis基本查询

git 项目仓库

查看全文

https://www.xamrdz.com/backend/3yu1942575.html

MyBatis

数据库的基本 增删改查

select 按照主键查询

install 添加

update更新

delete删除

Result查询结果转换

List<*>

对象ArticlePo

xml转换对象

使用（SqlProvider）提供者操作

1. 新建一个提供者， 对应操作sql静态函数

2. 接口查询类 ArticleRepository

3. Spring Test 查询操作

相关文章：

数据库的基本增删改查

1. 新建一个提供者，对应操作sql静态函数