使用mybatis操作数据库动态SQL示例练习10

最新推荐文章于 2024-04-12 01:22:26 发布

虾米大王

最新推荐文章于 2024-04-12 01:22:26 发布

阅读量86

点赞数

分类专栏：感悟 Java 文章标签： mybatis 数据库 sql

本文链接：https://blog.csdn.net/modern358/article/details/130667686

版权

Java 同时被 2 个专栏收录

1301 篇文章 5 订阅

订阅专栏

感悟

15 篇文章 0 订阅

订阅专栏

文章展示了如何在Maven项目中配置MyBatis，包括mybatis-config.xml、db.properties文件，以及使用MyBatisUtils辅助类。同时，提到了Lombok库的@Data注解简化实体类代码，并提供了添加、查询和更新博客的Mapper接口及XML实现。文章还包含了数据库表结构和测试用例。

摘要由CSDN通过智能技术生成

还是延续之前的空maven工程，新建moudle，命名为mybatis-09-blog

项目总pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<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/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.shrimpking</groupId>
    <artifactId>mybatistest02</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>mybatis-01</module>
        <module>mybatis-01-self</module>
        <module>mybatis-02</module>
        <module>mybatis-03</module>
        <module>mybatis-04</module>
        <module>mybatis-05</module>
        <module>mybatis-05</module>
        <module>mybatis-06</module>
        <module>mybatis-07</module>
        <module>mybatis-08</module>
        <module>mybatis-09-blog</module>
    </modules>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

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

项目具体情况截图

下面依次贴出代码

mybatis-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>
    <properties resource="db.properties"/>

    <settings>
        <!--开启默认日志-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--开启mysql数据库字段下划线与实体类驼峰命名自动转换-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <typeAliases>
        <typeAlias type="com.shrimpking.pojo.Blog" alias="Blog"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper class="com.shrimpking.dao.BlogMapper"/>
    </mappers>
</configuration>

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
username=root
password=mysql123

MyBatisUtils.java

package com.shrimpking.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 java.io.InputStream;

/**
 * @author user1
 */
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 (Exception e)
        {
            e.printStackTrace();
        }
    }

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

IdUtils.java

package com.shrimpking.utils;

import org.junit.Test;

import java.util.UUID;

/**
 * @author user1
 */
public class IdUtils
{
    public static String getId()
    {
        return UUID.randomUUID().toString().replaceAll("-","");
    }

    @Test
    public void test()
    {
        for (int i = 0; i < 5; i++)
        {
            System.out.println(IdUtils.getId());
        }
    }
}

Blog.java

package com.shrimpking.pojo;

import lombok.Data;

import java.util.Date;

@Data
public class Blog
{
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
}

实体类中，使用的注解@Data，是lombok的功能，如果需要使用需要在子项目的pom.xml引入依赖，并且在idea工具中需要下载lombok插件。各位请自行百度如何下载lombok的idea插件，把插件安装了，代码才可以运行。

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<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/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>mybatistest02</artifactId>
        <groupId>com.shrimpking</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>mybatis-09-blog</artifactId>

    <dependencies>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
        </dependency>
    </dependencies>
</project>

数据库：

create table user(
	id int(20) not null,
	name varchar(30) default null,
	pwd varchar(30) default null,
	primary key(id)
);

insert into user values(1,'张三','123456');
insert into user values(2,'李四','123456');
insert into user values(3,'王五','123456');


create table teacher(
	id int(10) not null,
	name varchar(30) default null,
	primary key(id)
);

insert into teacher(id,name) values(1,"啥老师");

create table student(
	id int(10) not null,
	name varchar(30) default null,
	tid int(10) default null,
	primary key(id),
	key fktid(tid),
	constraint fktid foreign key(tid) references teacher(id)
);

insert into student(id,name,tid) values(1,"小明",1);
insert into student(id,name,tid) values(2,"小花",1);
insert into student(id,name,tid) values(3,"小妞",1);
insert into student(id,name,tid) values(4,"小宁",1);
insert into student(id,name,tid) values(5,"小王",1);

select 
s.id as sid,
s.name as sname,
s.tid as stid,
t.id as tid,
t.name as tname
from student s,teacher t where s.tid = t.id


select 
s.id as sid,
s.name as sname,
t.id as tid,
t.name as tname
from teacher t,student s
where t.id = s.tid

create table blog(
	id varchar(50) not null primary key,
	title varchar(100) not null comment '标题',
	author varchar(30) not null comment '作者',
	create_time datetime not null comment '创建时间',
	views int(30) not null comment '浏览量'
);

BlogMapper.java

package com.shrimpking.dao;

import com.shrimpking.pojo.Blog;

import java.util.List;
import java.util.Map;

public interface BlogMapper
{

    int addBlog(Blog blog);

    List<Blog> queryBlogIf(Map map);

    List<Blog> queryBlogWhere(Map map);

    int updateBlog(Blog blog);
}

BlogMapper.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">
<mapper namespace="com.shrimpking.dao.BlogMapper">
    <insert id="addBlog" parameterType="Blog">
        insert into blog(id,title,author,create_time,views)
        values (#{id},#{title},#{author},#{createTime},#{views});
    </insert>

    <select id="queryBlogIf" resultType="Blog" parameterType="map">
        select * from blog where 1=1
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </select>

    <select id="queryBlogWhere" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <if test="title != null">
                title = #{title}
            </if>
            <if test="author != null">
                and author = #{author}
            </if>
        </where>
    </select>

    <update id="updateBlog" parameterType="Blog">
        update blog
        <set>
            <if test="title != null">
                title = #{title},
            </if>
            <if test="author != null">
                author = #{author},
            </if>
            <if test="views != 0">
                views = #{views},
            </if>
        </set>
        where id = #{id}
    </update>
</mapper>

MyTest.java

package com.shrimpking;

import com.shrimpking.dao.BlogMapper;
import com.shrimpking.pojo.Blog;
import com.shrimpking.utils.IdUtils;
import com.shrimpking.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MyTest
{
    @Test
    public void testAddBlog()
    {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();
        blog.setId(IdUtils.getId());
        blog.setTitle("mybatis如此简单");
        blog.setAuthor("虾米大王");
        blog.setCreateTime(new Date());
        blog.setViews(9999);
        mapper.addBlog(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("spring如此简单");
        mapper.addBlog(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("java如此简单");
        mapper.addBlog(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("微服务如此简单");
        mapper.addBlog(blog);

        sqlSession.close();
    }

    @Test
    public void testQueryIf1()
    {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        List<Blog> blogs = mapper.queryBlogIf(map);
        for (Blog blog : blogs)
        {
            System.out.println(blog);
        }
        sqlSession.close();
    }

    @Test
    public void testQueryIf2()
    {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        map.put("title","java如此简单");
        List<Blog> blogs = mapper.queryBlogIf(map);
        for (Blog blog : blogs)
        {
            System.out.println(blog);
        }
        sqlSession.close();
    }

    @Test
    public void testQueryIf3()
    {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        map.put("title","java如此简单");
        map.put("author","虾米大王");
        List<Blog> blogs = mapper.queryBlogIf(map);
        for (Blog blog : blogs)
        {
            System.out.println(blog);
        }
        sqlSession.close();
    }

    @Test
    public void testQueryWhere()
    {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        List<Blog> blogs = mapper.queryBlogWhere(map);
        for (Blog blog : blogs)
        {
            System.out.println(blog);
        }
        sqlSession.close();
    }

    @Test
    public void testQueryWhere2()
    {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        map.put("title","java如此简单");
        List<Blog> blogs = mapper.queryBlogWhere(map);
        for (Blog blog : blogs)
        {
            System.out.println(blog);
        }
        sqlSession.close();
    }

    @Test
    public void testQueryWhere3()
    {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        map.put("author","虾米大王");
        List<Blog> blogs = mapper.queryBlogWhere(map);
        for (Blog blog : blogs)
        {
            System.out.println(blog);
        }
        sqlSession.close();
    }

    @Test
    public void testUpdate()
    {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();
        blog.setId("3004c40096bf476c824f0be1c4f62c51");
        //blog.setTitle("博客");
        blog.setAuthor("大王");
        mapper.updateBlog(blog);
        sqlSession.close();
    }
}