[Java知识库] Spring Boot + Mybatis

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> Java知识库 -> Spring Boot + Mybatis -> 正文阅读

[Java知识库]Spring Boot + Mybatis

使用SpringBoot整合Mybatis，主要介绍Mybatis的用法，主要就是增、删、改、查四种。

引入依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>版本号</version>
</dependency>

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>版本号</version>
</dependency>

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>版本号</version>
</dependency>

配置数据库以及在控制台打印SQL语句

server.port=8090

# 数据库配置
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/sell?characterEncoding=utf-8

# 打印sql日志
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

# pagehelper分页插件配置
pagehelper.helper-dialect=mysql
pagehelper.reasonable=true
pagehelper.support-methods-arguments=true
pagehelper.params=count=countSql

实体类

public class ProductInfo {

    private String productId;

    private String productName;

    private Integer productPrice;

    public String getProductId() {
        return productId;
    }

    public void setProductId(String productId) {
        this.productId = productId;
    }

    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    public Integer getProductPrice() {
        return productPrice;
    }

    public void setProductPrice(Integer productPrice) {
        this.productPrice = productPrice;
    }
}

Controller

@RestController
@RequestMapping("/text")
public class TextController {

    @Autowired
    ProductInfoServicesImpl productInfoServices;

    @GetMapping("/findProductInfoById")
    public String findProductInfoById(@PathParam("productId") String productId){
        return JSONObject.toJSONString(productInfoServices.findProductInfoById(productId));
    }

    @PostMapping("/insertProductInfo")
    public Integer insertProductInfo(@RequestBody String productInfo){
        return productInfoServices.insertProductInfo(JSONObject.parseObject(productInfo, ProductInfo.class));
    }

    @PostMapping("/updateProductInfo")
    public Integer updateProductInfo(@RequestBody String productInfo){
        return productInfoServices.updateProductInfo(JSONObject.parseObject(productInfo, ProductInfo.class));
    }

    @GetMapping("/deleteProductInfo")
    public Integer deleteProductInfo(@PathParam("productId") String productId){
        return productInfoServices.deleteProductInfo(productId);
    }

}

实现类

@Service
public class ProductInfoServicesImpl implements ProductInfoServices {

    @Autowired
    private ProductInfoMapper productInfoMapper;

    @Override
    public ProductInfo findProductInfoById(String productId) {
        return productInfoMapper.findProductInfoById(productId);
    }

    @Override
    public Integer insertProductInfo(ProductInfo productInfo) {
        return productInfoMapper.insertProductInfo(productInfo);
    }

    @Override
    public Integer updateProductInfo(ProductInfo productInfo) {
        return productInfoMapper.updateProductInfo(productInfo);
    }

    @Override
    public Integer deleteProductInfo(String productId) {
        return productInfoMapper.deleteProductInfo(productId);
    }
}

Mapper

1. 通过注解的形式实现

@Serlect

@Select("select * from product_info where product_id = #{productId}")
@Results({ //当数据库字段和实体类属性不一样时，使用该注解映射，一一对应
    @Result(column="product_id", property="productId", jdbcType= JdbcType.VARCHAR),
    @Result(column="product_name", property="productName", jdbcType= JdbcType.VARCHAR),
    @Result(column="product_price", property="productPrice", jdbcType= JdbcType.DECIMAL)
})
ProductInfo findProductInfoById(@Param("productId") String productId);

@Insert

@Insert("insert into product_info(product_id, product_name, product_price) values (#{productId},#{productName},#{productPrice})")
int insertProductInfo(ProductInfo productInfo);

@Update

@Update("update product_info set product_name=#{productName},product_price=#{productPrice} where product_id = #{productId}")
int updateProductInfo(ProductInfo productInfo);

@Delete

@Delete("delete from product_info where product_id = #{productId}")
int deleteProductInfo(@Param("productId") String productId);

2. 通过XML的形式实现?

标签：<select>、<insert>、<update>、<delete>、<resultMap>、<sql>

动态标签：<if>、<choose>、<when>、<otherwise>、<where>、<set>、<trim>、<foreach>、<bind>、<include>

select

<?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.example.text.mapper.ProductInfoMapper">
    <resultMap id="BaseResultMap" type="com.example.text.entity.ProductInfo">
        <id column="product_id" property="productId" jdbcType="VARCHAR" />
        <result column="product_name" property="productName" jdbcType="VARCHAR" />
        <result column="product_price" property="productPrice" jdbcType="DECIMAL" />
    </resultMap>
    <sql id="Base_Column_List">
        product_id, product_name, product_price
    </sql>
    <select id="findProductInfoById" parameterType="java.lang.String" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from product_info
        where product_id = #{productId,jdbcType=VARCHAR}
    </select>
</mapper>

?分页

a. limit关键字

@GetMapping("/findProductInfo")
public String findProductInfo(@RequestParam(value = "pageNo", defaultValue = "0") int pageNo, 
                              @RequestParam(value = "pageSize", defaultValue = "2") int pageSize){
    return JSONObject.toJSONString(productInfoServices.findProductInfo(pageNo, pageSize));
}

@Service
public class ProductInfoServicesImpl implements ProductInfoServices {

    @Autowired
    private ProductInfoMapper productInfoMapper;

    @Override
    public List<ProductInfo> findProductInfo(int pageNo, int pageSize) {
        return productInfoMapper.findProductInfo(pageNo, pageSize);
    }

}

@Mapper
public interface ProductInfoMapper {
    List<ProductInfo> findProductInfo(int pageNo, int pageSize);
}

<select id="findProductInfo" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from product_info limit #{pageNo,jdbcType=INTEGER}, #{pageSize,jdbcType=INTEGER}
</select>

b. RowBounds实现分页：有2个字段offset和limit。这种方式获取所有的ResultSet，从ResultSet中的offset位置开始获取limit个记录。但这并不意味着JDBC驱动器会将所有的ResultSet存放在内存，实际上只加载小部分数据到内存，如果需要，再加载部分数据到内存。

缺点：把结果暂时存放到了DB中，可能使DB压力过大。

@Service
public class ProductInfoServicesImpl implements ProductInfoServices {

    @Autowired
    private ProductInfoMapper productInfoMapper;

    @Override
    public List<ProductInfo> findProductInfo(int pageNo, int pageSize) {
        return productInfoMapper.findProductInfo(new RowBounds(pageNo, pageSize));
    }

}

@Mapper
public interface ProductInfoMapper {
    List<ProductInfo> findProductInfo(RowBounds rowBounds);
}

<select id="findProductInfo" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from product_info
</select>

c. PageHelper

@Service
public class ProductInfoServicesImpl implements ProductInfoServices {
    @Override
    public List<ProductInfo> findProductInfo(int pageNo, int pageSize) {
        PageHelper.startPage(pageNo, pageSize);
        List<ProductInfo> productInfos = productInfoMapper.findProductInfo();
        return productInfos;
    }
}

@Mapper
public interface ProductInfoMapper {
    List<ProductInfo> findProductInfo();
}

<select id="findProductInfo" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from product_info
</select>

insert

批量插入

a. 代码中for循环插入

@RestController
@RequestMapping("/text")
public class TextController {

    @PostMapping("/insertProductInfo")
    public Integer insertProductInfo(@RequestBody String productInfo){
        return productInfoServices.insertProductInfo(JSONObject.parseArray(productInfo, ProductInfo.class));
    }

}

@Service
public class ProductInfoServicesImpl implements ProductInfoServices {

    @Autowired
    private ProductInfoMapper productInfoMapper;

    @Override
    public Integer insertProductInfo(List<ProductInfo> productInfos) {
        for (ProductInfo productInfo: productInfos) {
            productInfoMapper.insertProductInfo(productInfo);
        }
        return 1;
    }
}

@Mapper
public interface ProductInfoMapper {
    int insertProductInfo(ProductInfo productInfo);
}

<insert id="insertProductInfo" parameterType="com.example.text.entity.ProductInfo" >
	insert into product_info
	<trim prefix="(" suffix=")" suffixOverrides="," >
		<if test="productId != null" >
			product_id,
		</if>
		<if test="productName != null" >
			product_name,
		</if>
		<if test="productPrice != null" >
			product_price
		</if>
	</trim>
	<trim prefix="values (" suffix=")" suffixOverrides="," >
		<if test="productId != null" >
			#{productId,jdbcType=VARCHAR},
		</if>
		<if test="productName != null" >
			#{productName,jdbcType=VARCHAR},
		</if>
		<if test="productPrice != null" >
			#{productPrice,jdbcType=DECIMAL},
		</if>
	</trim>
</insert>

?b. <foreach> 标签

@Service
public class ProductInfoServicesImpl implements ProductInfoServices {

    @Autowired
    private ProductInfoMapper productInfoMapper;

    @Override
    public Integer insertProductInfo(List<ProductInfo> productInfos) {
        return productInfoMapper.insertProductInfo(productInfos);
    }

}

@Mapper
public interface ProductInfoMapper {
    int insertProductInfo(@Param("productInfos") List<ProductInfo> productInfos);
}

<insert id="insertProductInfo" parameterType="java.util.List">
	insert into product_info (product_id,product_name,product_price) values
	<foreach collection="productInfos" item="productInfo" separator=",">
		<trim prefix="(" suffix=")" suffixOverrides=",">
			#{productInfo.productId,jdbcType=VARCHAR},
			#{productInfo.productName,jdbcType=VARCHAR},
			#{productInfo.productPrice,jdbcType=DECIMAL}
		</trim>
	</foreach>
</insert>

?b. 使用batch模式

@Service
public class ProductInfoServicesImpl implements ProductInfoServices {

    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;

    @Override
    public Integer insertProductInfo(List<ProductInfo> productInfos) {
        SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
        ProductInfoMapper productInfoMapper = sqlSession.getMapper(ProductInfoMapper.class);
        for (ProductInfo productInfo: productInfos) {
            productInfoMapper.insertProductInfo(productInfo);
        }
        return 1;
    }

}

@Mapper
public interface ProductInfoMapper {
    int insertProductInfo(ProductInfo productInfo);
}

<insert id="insertProductInfo" parameterType="com.example.text.entity.ProductInfo" >
	insert into product_info
	<trim prefix="(" suffix=")" suffixOverrides="," >
		<if test="productId != null" >
			product_id,
		</if>
		<if test="productName != null" >
			product_name,
		</if>
		<if test="productPrice != null" >
			product_price
		</if>
	</trim>
	<trim prefix="values (" suffix=")" suffixOverrides="," >
		<if test="productId != null" >
			#{productId,jdbcType=VARCHAR},
		</if>
		<if test="productName != null" >
			#{productName,jdbcType=VARCHAR},
		</if>
		<if test="productPrice != null" >
			#{productPrice,jdbcType=DECIMAL},
		</if>
	</trim>
</insert>

如果数据量较多建议采用batch模式

<foreach> 数据量建议10-100条

for循环是最不建议的方式，需要频繁的建立关闭数据库连接，比较耗时

update

<update id="updateProductInfo" parameterType="com.example.text.entity.ProductInfo" >
    update product_info
    <set>
        <if test="productName != null" >
            product_name = #{productName,jdbcType=VARCHAR},
        </if>
        <if test="productPrice != null" >
            product_price = #{productPrice,jdbcType=DECIMAL}
        </if>
    </set>
    where product_id = #{productId,jdbcType=VARCHAR}
</update>

delete

<delete id="deleteProductInfo" parameterType="java.lang.String" >
    delete from product_info where product_id = #{productId,jdbcType=VARCHAR}
</delete>

一对一

@RestController
@RequestMapping("/text")
public class TextController {

    @Autowired
    private OrderDetailServicesImpl orderDetailServices;

    @GetMapping("/findOrderDetailById")
    public String findOrderDetailById(@PathParam("detailId") String detailId){
        return JSONObject.toJSONString(orderDetailServices.findOrderDetailById(detailId));
    }

}

public class OrderDetail {

    private String detailId;

    private ProductInfo productInfo;

    public String getDetailId() {
        return detailId;
    }

    public void setDetailId(String detailId) {
        this.detailId = detailId;
    }

    public ProductInfo getProductInfo() {
        return productInfo;
    }

    public void setProductInfo(ProductInfo productInfo) {
        this.productInfo = productInfo;
    }
}

@Service
public class OrderDetailServicesImpl implements OrderDetailServices {

    @Autowired
    private OrderDetailMapper orderDetailMapper;

    @Override
    public OrderDetail findOrderDetailById(String detailId) {
        return orderDetailMapper.findOrderDetailById(detailId);
    }
}

@Mapper
public interface OrderDetailMapper {
    OrderDetail findOrderDetailById(String detailId);
}

<?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.example.text.mapper.OrderDetailMapper">
    <resultMap id="BaseResultMap" type="com.example.text.entity.OrderDetail">
        <id column="detail_id" property="detailId" jdbcType="VARCHAR"/>
        <association property="productInfo" javaType="com.example.text.entity.ProductInfo">
            <id column="product_id" property="productId" jdbcType="VARCHAR"/>
            <result column="product_name" property="productName" jdbcType="VARCHAR"/>
            <result column="product_price" property="productPrice" jdbcType="DECIMAL"/>
        </association>
    </resultMap>

    <select id="findOrderDetailById" parameterType="java.lang.String" resultMap="BaseResultMap">
        select o.detail_id, p.product_id, p.product_name, p.product_price
        from order_detail o
                 left join product_info p on o.product_id = p.product_id
        where o.detail_id = #{detailId,jdbcType=VARCHAR}
    </select>
</mapper>

一对多

public class ProductInfo {

    private String productId;

    private String productName;

    private Integer productPrice;

    private List<OrderDetail> orderDetails;

    public List<OrderDetail> getOrderDetails() {
        return orderDetails;
    }

    public void setOrderDetails(List<OrderDetail> orderDetails) {
        this.orderDetails = orderDetails;
    }

    public String getProductId() {
        return productId;
    }

    public void setProductId(String productId) {
        this.productId = productId;
    }

    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    public Integer getProductPrice() {
        return productPrice;
    }

    public void setProductPrice(Integer productPrice) {
        this.productPrice = productPrice;
    }
}

public class OrderDetail {

    private String detailId;

    public String getDetailId() {
        return detailId;
    }

    public void setDetailId(String detailId) {
        this.detailId = detailId;
    }
}

@Mapper
public interface ProductInfoMapper {
    ProductInfo findProductInfoById(String productId);
}

<?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.example.text.mapper.ProductInfoMapper">
    <resultMap id="BaseResultMap" type="com.example.text.entity.ProductInfo">
        <id column="product_id" property="productId" jdbcType="VARCHAR"/>
        <result column="product_name" property="productName" jdbcType="VARCHAR"/>
        <result column="product_price" property="productPrice" jdbcType="DECIMAL"/>
        <collection property="orderDetails" ofType="com.example.text.entity.OrderDetail">
            <id column="detail_id" property="detailId" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>
    <select id="findProductInfoById" parameterType="java.lang.String" resultMap="BaseResultMap">
        select o.detail_id, p.product_id, p.product_name, p.product_price
        from product_info p
                 left join order_detail o on o.product_id = p.product_id
        where o.product_id = #{productId,jdbcType=VARCHAR}
    </select>
</mapper>

多对多?

多对多写法和一对多的类似，但是需要关联表

public class ProductInfo {

    private String productId;

    private List<OrderDetail> orderDetails;

    public String getProductId() {
        return productId;
    }

    public void setProductId(String productId) {
        this.productId = productId;
    }

    public List<OrderDetail> getOrderDetails() {
        return orderDetails;
    }

    public void setOrderDetails(List<OrderDetail> orderDetails) {
        this.orderDetails = orderDetails;
    }
}

public class OrderDetail {

    private String detailId;

    public String getDetailId() {
        return detailId;
    }

    public void setDetailId(String detailId) {
        this.detailId = detailId;
    }
}

?关联表（可以不创类，光用表就行）

public class DetailList {
    
    private String detailListId;
    
    private String detailId;
    
    private String productId;

    public String getDetailListId() {
        return detailListId;
    }

    public void setDetailListId(String detailListId) {
        this.detailListId = detailListId;
    }

    public String getDetailId() {
        return detailId;
    }

    public void setDetailId(String detailId) {
        this.detailId = detailId;
    }

    public String getProductId() {
        return productId;
    }

    public void setProductId(String productId) {
        this.productId = productId;
    }
}

<?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.example.text.mapper.ProductInfoMapper">
    <resultMap id="BaseResultMap" type="com.example.text.entity.ProductInfo">
        <id column="product_id" property="productId" jdbcType="VARCHAR"/>
        <collection property="orderDetails" ofType="com.example.text.entity.OrderDetail">
            <id column="detail_id" property="detailId" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>
    <select id="findProductInfoById" parameterType="java.lang.String" resultMap="BaseResultMap">
        select d.detail_id, d.product_id
        from product_info p
                 left join detail_list d on d.product_id = p.product_id
                 left join order_detail o on o.detail_id = d.detail_id
        where d.product_id = #{productId,jdbcType=VARCHAR}
    </select>
</mapper>

Java知识库最新文章

计算距离春节还有多长时间

系统开发系列之WebService（spring框架+ma

springBoot+Cache(自定义有效时间配置)

SpringBoot整合mybatis实现增删改查、分页查

spring教程

SpringBoot+Vue实现美食交流网站的设计与实

加:2022-09-24 20:42:45 更:2022-09-24 20:46:58

360图书馆购物三丰科技阅读网日历万年历 2024年11日历

-2024/11/23 8:47:12-

图片自动播放器
↓图片自动播放器↓

TxT小说阅读器
↓语音阅读,小说下载,古典文学↓

一键清除垃圾
↓轻轻一点,清除系统垃圾↓

图片批量下载器
↓批量下载图片,美女图库↓

网站联系: qq:121756557 email:121756557@qq.com IT数码