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>

 

 

 

  • 3
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值