环境jdk1.8;mysql5.7;IDEA2017
1、创建相关工程
2、引入相关的依赖
<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency>
3、创建application.yml并配置
spring: datasource: driver-class-name: com.mysql.jdbc.Driver username: username password: password url: jdbc:mysql://Ip:3306/pinyougoudb?characterEncoding=utf8&useSSL=false jpa: show-sql: true mybatis: mapper-locations: classpath*:mapper/*Mapper.xml
4、创建实体类,引入相关注解
packagecom.immoc.dataobject;
importlombok.Data;
importjavax.persistence.Entity;
importjavax.persistence.Id;
importjava.math.BigDecimal;
importjava.util.Date;
/**
*商品信息
*/
@Entity//实体与数据库对应
@Data//lombok注解,可省set、get等相关,提高代码简洁程度
publicclassProductInfo{
@Id//其为主键
Private String productId;
Private String productName;
Private BigDecimal productPrice;
Private Integer productStock;
Private Stringproduct Description;
Private String productIcon;
Private Integer categoryType;
Private Date createTime;
Private Date updateTime;
}
5、创建sell数据库和product_info表(sql例子)
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for product_info
-- ----------------------------
DROP TABLE IF EXISTS `product_info`;
CREATE TABLE `product_info` (
`product_id` varchar(32) NOT NULL,
`product_name` varchar(64) NOT NULL COMMENT '商品名称',
`product_price` decimal(8,2) NOT NULL COMMENT '单价',
`product_stock` int(11) NOT NULL COMMENT '库存',
`product_description` varchar(64) DEFAULT NULL COMMENT '描述',
`product_icon` varchar(512) DEFAULT NULL COMMENT '小图',
`product_status` tinyint(3) DEFAULT '0' COMMENT '商品状态,0正常1下架',
`category_type` int(11) NOT NULL COMMENT '类目编号',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
6、创建mapper映射的接口
packagecom.immoc.mapper;
importcom.immoc.dataobject.ProductInfo;
importorg.apache.ibatis.annotations.*;
importorg.apache.ibatis.annotations.Update;
@Mapper
publicinterfaceProductInfoMapper{
/**
*查找
*@paramproductId
*@return
*/
@Select("SELECTproduct_id,product_name,product_price,product_stock,product_description,product_icon,product_status,category_type,create_time,update_timeFROMproduct_infowhereproduct_id=#{productId}")
@Results({@Result(property="productId",column="product_id"),
@Result(property="productName",column="product_name"),
@Result(property="productPrice",column="product_price"),
@Result(property="productStock",column="product_stock"),
@Result(property="productDescription",column="product_description"),
@Result(property="productIcon",column="product_icon"),
@Result(property="productStatus",column="product_status"),
@Result(property="categoryType",column="category_type"),
@Result(property="createTime",column="create_time"),
})
ProductInfofindByProductId(StringproductId);
/**
*更新
*@paramproductInfo
*@return
*/
@Update("updateproduct_infosetproduct_id=#{productId},product_name=#{productName},product_price=#{productPrice},product_stock=#{productStock},product_description=#{productDescription},product_icon=#{productIcon},product_status=#{productStatus},category_type=#{categoryType}whereproduct_id=#{productId}")
intupdateById(ProductInfoproductInfo);
/**
*删除
*@paramproductId
*@return
*/
@Delete("deletefromproduct_infowhereproduct_id=#{productId}")
intdeleteById(StringproductId);
/**
*插入
*@paramproductInfo
*@return
*/
@Insert("insertintoproduct_info(product_id,product_name,product_price,product_stock,product_description,product_icon,product_status,category_type)values(#{productId},#{productName},#{productPrice},#{productStock},#{productDescription},#{productIcon},#{productStatus},#{categoryType})")
intinsert(ProductInfoproductInfo);
}
7、测试
packagecom.immoc.mapper;
importcom.immoc.dataobject.ProductInfo;
importlombok.extern.slf4j.Slf4j;
importorg.junit.Assert;
importorg.junit.Test;
importorg.junit.runner.RunWith;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.boot.test.context.SpringBootTest;
importorg.springframework.test.context.junit4.SpringRunner;
importjava.math.BigDecimal;
@Slf4j
@SpringBootTest
@RunWith(SpringRunner.class)
publicclassProductInfoMapperTest{
@Autowired
ProductInfoMapperinfoMapper;
@Test
publicvoidfindByProductId(){
StringproductId="1525103526600833270";
ProductInfoproductInfo=infoMapper.findByProductId(productId);
Assert.assertNotNull(productInfo);
}
@Test
publicvoidupdateById(){
ProductInfoproductInfo=newProductInfo();
productInfo.setProductId("1525103526600833270");
productInfo.setCategoryType(3);
productInfo.setProductStock(232);
productInfo.setProductStatus(1);
productInfo.setProductPrice(newBigDecimal(12123));
productInfo.setProductName("sfsd");
productInfo.setProductIcon("sfsdf");
productInfo.setProductDescription("sfs");
intupdate=infoMapper.updateById(productInfo);
Assert.assertEquals(1,update);
}
@Test
publicvoiddeleteById(){
StringproductId="1525103526600833270";
inti=infoMapper.deleteById(productId);
Assert.assertEquals(1,i);
}
@Test
publicvoidinsert(){
ProductInfoproductInfo=newProductInfo();
productInfo.setProductId("1525103526600833270");
productInfo.setCategoryType(3);
productInfo.setProductStock(232);
productInfo.setProductStatus(1);
productInfo.setProductPrice(newBigDecimal(12123));
productInfo.setProductName("sfsd");
productInfo.setProductIcon("sfsdf");
productInfo.setProductDescription("sfs");
inti=infoMapper.insert(productInfo);
Assert.assertEquals(1,i);
}
}
=============================================================
8、动态SQL
8.1查询
packagecom.immoc.mapper;
publicclassSqlProvider{
publicStringselectProductInfo(StringproductId){
StringBuffersql=newStringBuffer("select*fromproduct_infowhere1=1");
if(productId!=null){
sql.append("andproduct_id=#{productId}");
}
returnsql.toString();
}
}==============
packagecom.immoc.mapper;
importcom.immoc.dataobject.ProductInfo;
importorg.apache.ibatis.annotations.*;
@Mapper
publicinterfaceProductInfoMapperProvider{
@SelectProvider(type=SqlProvider.class,method="selectProductInfo")
//@ResultMap("ProductInfoMap")
@Results({@Result(property="productId",column="product_id"),
@Result(property="productName",column="product_name"),
@Result(property="productPrice",column="product_price"),
@Result(property="productStock",column="product_stock"),
@Result(property="productDescription",column="product_description"),
@Result(property="productIcon",column="product_icon"),
@Result(property="productStatus",column="product_status"),
@Result(property="categoryType",column="category_type"),
@Result(property="createTime",column="create_time"),
})
//@ResultType(value=ProductInfo.class)
ProductInfoselectProductInfo(StringproductId);
}
测试
packagecom.immoc.mapper;
importcom.immoc.dataobject.ProductInfo;
importorg.junit.Assert;
importorg.junit.Test;
importorg.junit.runner.RunWith;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.boot.test.context.SpringBootTest;
importorg.springframework.test.context.junit4.SpringRunner;
importstaticorg.junit.Assert.*;
@SpringBootTest
@RunWith(SpringRunner.class)
publicclassProductInfoMapperProviderTest{
@Autowired
ProductInfoMapperProviderinfoMapperProvider;
@Test
publicvoidselectProductInfo(){
StringproductId="1525103526600833270";
ProductInfoproductInfo=infoMapperProvider.selectProductInfo(productId);
Assert.assertEquals(productId,productInfo.getProductId());
}
}
====================
二、springboot与mybatis整合带xml文件
1、环境 IDEA;jdk1.8;mysql5.7;
2、创建相关工程
2.1引入依赖pom文件为
<?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.blog</groupId> <artifactId>demo</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>demo</name> <description>Demo project for Spring Boot</description> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.5.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!--热部署使用jar包--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <version>2.0.1.RELEASE</version> <optional>true</optional> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
2.2配置文件为(application.yml)
spring: datasource: driver-class-name: com.mysql.jdbc.Driver username: root password: xu0719wd url: jdbc:mysql://111.230.180.89:3306/pinyougoudb?characterEncoding=utf8&useSSL=false jpa: show-sql: true
#加载mybatis配置文件mybatis:
mybatis: mapper-locations: classpath*:mapper/*Mapper.xml
#数据源
3、mapper接口
Package com.immoc.mapper;
Import com.immoc.dataobject.ProductInfo;
Import org.apache.ibatis.annotations.Mapper;
@Mapper
Public interface ProductInfoMapper{
ProductInfo selectByPrimaryKey (StringproductId);
}
4、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.immoc.mapper.ProductInfoMapper"><!--接口路径-->
<resultMap id="BaseResultMap" type="com.immoc.dataobject.ProductInfo"><!--实体类的路径--><id column="product_id" jdbcType="VARCHAR" property="productId"/>
<result column="product_name" jdbcType="VARCHAR" property="productName"/>
<result column="product_price" jdbcType="DECIMAL" property="productPrice"/>
<result column="product_stock" jdbcType="INTEGER" property="productStock"/>
<result column="product_description" jdbcType="VARCHAR" property="productDescription"/>
<result column="product_icon" jdbcType="VARCHAR" property="productIcon"/>
<result column="product_status" jdbcType="TINYINT" property="productStatus"/>
<result column="category_type" jdbcType="INTEGER" property="categoryType"/>
<result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
<result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
</resultMap>
<select id="selectByPrimaryKey" parameterType="java.lang.String"resultMap="BaseResultMap">
select
*
from product_info
where product_id=#{productId,jdbcType=VARCHAR}
</select>
</mapper>
5、测试
packagecom.immoc.mapper;
importcom.immoc.dataobject.ProductInfo;
importlombok.extern.slf4j.Slf4j;
importorg.junit.Assert;
importorg.junit.Test;
importorg.junit.runner.RunWith;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.boot.test.context.SpringBootTest;
importorg.springframework.test.context.junit4.SpringRunner;
importjava.math.BigDecimal;
@Slf4j
@SpringBootTest
@RunWith(SpringRunner.class)
publicclassProductInfoMapperTest{
@Autowired
ProductInfoMapper infoMapper;
@Test
public void findByProductId(){
String productId="1525103526600833270";
ProductInfo info=infoMapper.selectByPrimaryKey(productId);
System.out.println(1);
System.out.println(info);
Assert.assertNotNull(info);
}
}