springboot(2) 数据库操作

一。 JPA

1.pom文件引入jpa和mysql依赖

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>

2.application.yml文件增加数据库配置

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true

3.与表girl对应的实体类

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class Girl {
    @Id
    @GeneratedValue
    private Integer id;
    private String cupSize;
    private String age;

JPA定义的标签：@Entity：表明该类是个实体类对应数据库中girl表。

@Id：主键，@GeneratedValue：主键生成策略

4.新建数据库操作respository

public interface GirlRespository extends JpaRepository<Girl, Integer> {
　　// 自定义按照age条件查询，格式固定
    public Girl findByAge(String age);
}

5.controller层操作数据库

@RestController
public class GirlController {
    @Autowired
    private GirlRespository girlRespository;
    
    @Autowired
    private GirlService girlService;
    
    @RequestMapping(value = "/girls", method = RequestMethod.GET)
    public List<Girl> getList(){
        return girlRespository.findAll();
    }
    @PostMapping(value = "/girls/insertTwo")
    public void insertTwo() {
        girlService.insertTwo();
    }
    @RequestMapping(value = "/girls", method = RequestMethod.POST)
    public void addOne(@RequestParam(value = "cupSize")String cupSize,
            @RequestParam(value = "age")String age){
        Girl girl = new Girl();
        girl.setAge(age);
        girl.setCupSize(cupSize);
        girlRespository.save(girl);
    }
    
    @GetMapping(value = "/girls/{id}")
    public Girl getOne(@PathVariable(value = "id")Integer id) {
        return girlRespository.getOne(id);
    }
    
    @PutMapping(value = "/girls/{id}")
    public Girl updateOne(@PathVariable(value = "id")Integer id,@RequestParam(value = "cupSize")String cupSize,
            @RequestParam(value = "age")String age) {
        Girl girl = new Girl();
        girl.setId(id);
        girl.setAge(age);
        girl.setCupSize(cupSize);
        return girlRespository.save(girl);
    }
    
    @DeleteMapping(value = "/girls/{id}")
    public void delOne(@PathVariable(value = "id")Integer id) {
        girlRespository.deleteById(id);
    }
    
    @GetMapping(value = "/girls/age/{age}")
    public Girl getOne(@PathVariable(value = "age")String age) {
        return girlRespository.findByAge(age);
    }
}

6.数据库事务操作，只需在方法上使用标签@Transactional

二。 mybatis

2.1 application.yml中配置

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/ordering_food?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
    type: com.alibaba.druid.pool.DruidDataSource
mybatis:
  mapperLocations: classpath:mapping/*.xml

2.2 pom文件引入mysql和mybatis依赖

　　　　<dependency>
              <groupId>mysql</groupId>
              <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
          <groupId>com.alibaba</groupId>
              <artifactId>druid</artifactId>
              <version>1.1.3</version>
        </dependency>
        <dependency>
              <groupId>org.mybatis.spring.boot</groupId>
              <artifactId>mybatis-spring-boot-starter</artifactId>
              <version>1.3.1</version>
        </dependency>

2.3 启动类增加mapper扫描注解 

@MapperScan("com.yjm.sell.dao")

@SpringBootApplication
@MapperScan("com.yjm.sell.dao")
public class SellApplication {
    public static void main(String[] args) {
        SpringApplication.run(SellApplication.class, args);
    }
}

2.4 增加src/main/resources/mapper.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.yjm.sell.dao.ProductCategoryDOMapper">
  <resultMap id="BaseResultMap" type="com.yjm.sell.dataobject.ProductCategoryDO">
    <id column="category_id" jdbcType="INTEGER" property="categoryId" />
    <result column="category_name" jdbcType="VARCHAR" property="categoryName" />
    <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>
  <sql id="Base_Column_List">
    category_id, category_name, category_type, create_time, update_time
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from product_category
    where category_id = #{categoryId,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from product_category
    where category_id = #{categoryId,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.yjm.sell.dataobject.ProductCategoryDO">
    insert into product_category (category_id, category_name, category_type, 
      create_time, update_time)
    values (#{categoryId,jdbcType=INTEGER}, #{categoryName,jdbcType=VARCHAR}, #{categoryType,jdbcType=INTEGER}, 
      #{createTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP})
  </insert>

</mapper>

2.5 增加 com.yjm.sell.dao/mapper.java

public interface ProductCategoryDOMapper {

    int deleteByPrimaryKey(Integer categoryId);

    int insert(ProductCategoryDO record);

    int insertSelective(ProductCategoryDO record);

    ProductCategoryDO selectByPrimaryKey(Integer categoryId);

}

2.6 可以在service中使用mapper了

@SpringBootTest
public class ProductCategoryDOMapperTest {
    @Autowired
    ProductCategoryDOMapper productCategoryDOMapper;
    
    @Test
    public void findOneTest() {
        ProductCategoryDO prdCategory = productCategoryDOMapper.selectByPrimaryKey(1);
        System.out.println(prdCategory.toString());
    }
}

2.7 mybatis-generator自动生成代码插件使用

 2.7.1 在pom中增加插件的引用

<plugin>
              <groupId>org.mybatis.generator</groupId>
              <artifactId>mybatis-generator-maven-plugin</artifactId>
              <version>1.3.5</version>
              <dependencies>
                <dependency>
                  <groupId>org.mybatis.generator</groupId>
                  <artifactId>mybatis-generator-core</artifactId>
                  <version>1.3.5</version>
                </dependency>
                <dependency>
                  <groupId>mysql</groupId>
                  <artifactId>mysql-connector-java</artifactId>
                  <version>8.0.11</version>
                </dependency>
              </dependencies>
              <executions>
                    <execution>
                      <id>mybatis generator</id>
                      <phase>package</phase>
                      <goals>
                            <goal>generate</goal>
                      </goals>
                    </execution>
              </executions>
              <configuration>
                    <!--允许移动生成的文件-->
                    <verbose>true</verbose>
                     <!--允许自动覆盖文件-->
                     <overwrite>false</overwrite>
                     <configurationFile>
                          src/main/resources/mybatis-generator.xml
                     </configurationFile>
              </configuration>
        </plugin>

2.7.2 新增srcmain esourcesmybatis-generator.xml 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>

    <context id="DB2Tables"    targetRuntime="MyBatis3">
        <!--数据库链接地址账号密码-->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://127.0.0.1:3306/test?serverTimezone=GMT%2B8" userId="root" password="root">
        </jdbcConnection>
        <!--生成DataObject类存放位置-->
        <javaModelGenerator targetPackage="com.yjm.sell.dataobject" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>
        <!--生成映射文件存放位置-->
        <sqlMapGenerator targetPackage="mapping" targetProject="src/main/resources">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>
        <!--生成Dao类存放位置-->
        <!-- 客户端代码，生成易于使用的针对Model对象和XML配置文件 的代码
                type="ANNOTATEDMAPPER",生成Java Model 和基于注解的Mapper对象
                type="MIXEDMAPPER",生成基于注解的Java Model 和相应的Mapper对象
                type="XMLMAPPER",生成SQLMap XML文件和独立的Mapper接口
        -->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.yjm.sell.dao" targetProject="src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>

        <!--生成对应表及类名-->
        <!--
        <table tableName="user_info"  domainObjectName="UserDO" enableCountByExample="false"
        enableUpdateByExample="false" enableDeleteByExample="false"
        enableSelectByExample="false" selectByExampleQueryId="false"></table>
        <table tableName="user_password"  domainObjectName="UserPasswordDO" enableCountByExample="false"
               enableUpdateByExample="false" enableDeleteByExample="false"
               enableSelectByExample="false" selectByExampleQueryId="false"></table>
        -->
        <table tableName="product_category"  domainObjectName="ProductCategoryDO" enableCountByExample="false"
               enableUpdateByExample="false" enableDeleteByExample="false"
               enableSelectByExample="false" selectByExampleQueryId="false"></table>
    </context>
</generatorConfiguration>

2.7.3 运行 右键maven build增加命令mybatis-generator:generate

DO, mapper.xml,mapper自动生成了，很方便

2.8 mybatis获取自增ID

在xml的语句增加 useGeneratedKeys="true" keyProperty="categoryId"， keyProperty是DO中的字段

<insert id="insertSelective" parameterType="com.yjm.sell.dataobject.ProductCategoryDO" useGeneratedKeys="true" keyProperty="categoryId">

然后就可以从prdCategory中获取了

productCategoryDOMapper.insertSelective(prdCategory);
        assertEquals(3, prdCategory.getCategoryId());

2.9 mybatis 中 sql语句

2.9.1 in 查询，使用<foreach>标签

如果参数是list类型 collection属性必须是"list"，如果是数组，该属性为array

List<ProductCategoryyDO> selectByTypeSet(List<Integer> list);

<select id="selectByTypeSet" resultMap="BaseResultMap">
   select
   <include refid="Base_Column_List" />
   from product_category
   where category_type in   
   <foreach collection="list" item="categoryType" open="(" close=")" separator="," >
    #{categoryType,jdbcType=INTEGER}
   </foreach>
  </select>

2.9.2 参数有多个时，使用@param

List<ProductCategoryyDO> selectByTypeSet(@param("id")String ctId, @param("typeList")List<Integer> list);

<select id="selectByTypeSet" resultMap="BaseResultMap">
   select
   <include refid="Base_Column_List" />
   from product_category
   where category_id = #{id,jdbcType=INTEGER}
   AND category_type in   
   <foreach collection="typeList" item="categoryType" open="(" close=")" separator="," > 
    #{categoryType,jdbcType=INTEGER}
   </foreach>
</select>

或者用map

Map<String,Object> map = new HashMap<String,Object>();
map.put("id", 1);
map.put("typeList", list);
List<ProductCategoryyDO> selectByTypeSet(map);

<select id="selectByTypeSet" resultMap="BaseResultMap">
   select
   <include refid="Base_Column_List" />
   from product_category
   where category_id = #{id,jdbcType=INTEGER}
   AND category_type in   
   <foreach collection="typeList" item="categoryType" open="(" close=")" separator="," > 
    #{categoryType,jdbcType=INTEGER}
   </foreach>
</select>

 2.9.3 递归查询

DataBankWithChildren 中的 <collection property="children" 实行递归

<resultMap id="BaseResultMap" type="com.migu.cms.model.DataBank">
    <id column="ID" jdbcType="VARCHAR" property="id" />
    <result column="DATABASE_TITLE" jdbcType="VARCHAR" property="databaseTitle" />
    <result column="PARENT_ID" jdbcType="VARCHAR" property="parentId" />
    <result column="LAYER_ID" jdbcType="DECIMAL" property="layerId" />
    <result column="SORT" jdbcType="DECIMAL" property="sort" />
    <result column="ARTICLE_ID" jdbcType="DECIMAL" property="articleId" />
    <result column="CREATE_TIME" jdbcType="TIMESTAMP" property="createTime" />
    <result column="CREATE_USER" jdbcType="DECIMAL" property="createUser" />
    <result column="REVIEW_USER" jdbcType="DECIMAL" property="reviewUser" />
    <result column="REVIEW_TIME" jdbcType="TIMESTAMP" property="reviewTime" />
    <result column="REALSE_STATE" jdbcType="DECIMAL" property="realseState" />
    <result column="STATE" jdbcType="DECIMAL" property="state" />
  </resultMap>
  <resultMap id="DataBankWithChildren" type="com.migu.cms.model.DataBank" extends="BaseResultMap">
    <collection property="children" ofType="com.migu.cms.model.DataBank"
                select="com.migu.cms.mapper.DataBankMapper.selectByParentId" column="id"></collection>
  </resultMap>
<select id="selectByParentId" parameterType="java.lang.String" resultMap="DataBankWithChildren">
    select
    <include refid="Base_Column_List" />
    from t_database_info
    where PARENT_ID = #{id,jdbcType=VARCHAR}
    and state = 0
  </select>

 2.10 pageHelper分页

<!--MyBatis分页插件-->
    <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper-spring-boot-starter</artifactId>
      <version>1.2.10</version>
    </dependency>

　　　　PageHelper.startPage(pageNum, pageSize);
        List<PmsBrand> list = brandMapper.selectByExample(new PmsBrandExample());
        PageInfo<PmsBrand> pageInfo = new PageInfo<>(list);