Spring Boot整合数据库

Spring Boot 整合 MyBatis

1. pom.xml

<parent>
        <groupId>org.springframework.boot</groupId>
    	<artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.1.RELEASE</version>
</parent>
<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
</dependencies>

2. application.yml

server:
  port: 9090
spring:
  thymeleaf:
    prefix: classpath:/templates/
    suffix: .html
    mode: HTML5
    encoding: UTF-8
  datasource:
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:
  mapper-locations: classpath:/mapping/*.xml
  type-aliases-package: com.haoxuan.entity

3. UserRepository

public interface UserRepository {
    public List<User> findAll(int index,int limit);
    public User findById(long id);
    public void save(User user);
    public void update(User user);
    public void deleteById(long id);
    public int count();
}

4. 在resource/mapping目录下创建UserRepository.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.southwind.mapper.UserRepository">

    <select id="findAll" resultType="User">
        select * from user limit #{param1},#{param2}
    </select>

    <select id="count" resultType="int">
        select count(id) from user
    </select>

    <select id="findById" parameterType="long" resultType="User">
        select * from user where id = #{id}
    </select>

    <insert id="save" parameterType="User">
        insert into user(name,score) values(#{name},#{score})
    </insert>

    <update id="update" parameterType="User">
        update user set name = #{name},score = #{score} where id = #{id}
    </update>

    <delete id="deleteById" parameterType="long">
        delete from user where id = #{id}
    </delete>
</mapper>

5. User

@Data
public class User {
    @NotNull(message = "id不能为空")
    private Long id;
    @NotEmpty(message = "姓名不能为空")
    @Length(min = 2,message = "姓名长度不能小于2位")
    private String name;
    @Min(value = 60,message = "成绩必须大于60分")
    private double score;
}

6. Handler

package com.southwind.controller;
import com.southwind.entity.User;
import com.southwind.mapper.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.ModelAndView;

@Controller
@RequestMapping("/mapper")
public class UserMapperHandler {

    @Autowired
    private UserRepository userRepository;
    private int limit = 10;

    @GetMapping("/findAll/{page}")
    public ModelAndView findAll(@PathVariable("page") int page){
        ModelAndView modelAndView = new ModelAndView();
        int index = (page-1)*limit;
        modelAndView.setViewName("show");
        modelAndView.addObject("list",userRepository.findAll(index,limit));
        modelAndView.addObject("page",page);
        //计算总页数
        int count = userRepository.count();
        int pages = 0;
        if(count%limit == 0){
            pages = count/limit;
        }else{
            pages = count/limit+1;
        }
        modelAndView.addObject("pages",pages);
        return modelAndView;
    }

    @GetMapping("/deleteById/{id}")
    public String deleteById(@PathVariable("id") long id){
        userRepository.deleteById(id);
        return "redirect:/mapper/findAll/1";
    }

    @GetMapping("/findById")
    public ModelAndView findById(@RequestParam("id") long id){
        ModelAndView modelAndView = new ModelAndView();
        modelAndView.addObject("user",userRepository.findById(id));
        modelAndView.setViewName("update");
        return modelAndView;
    }

    @PostMapping("/update")
    public String update(User user){
        userRepository.update(user);
        return "redirect:/mapper/findAll/1";
    }

    @PostMapping("/save")
    public String save(User user){
        userRepository.save(user);
        return "redirect:/mapper/findAll/1";
    }

    @GetMapping("/redirect/{name}")
    public String redirect(@PathVariable("name") String name){
        return name;
    }
}

Spring Boot 整合 Spring-data-JPA

虽然ORM框架都实现了JPA规范，但是在不同ORM框架之间切换是需要编写的代码有一些差异，而通过使用Spring Data Jpa能够方便大家在不同的ORM框架中间进行切换而不要更改代码

Spring Data JPA 是在JPA规范的基础下提供了Repository层的实现，但是使用那一款ORM(Hibernate)需要你自己去决定。

注意:mybatis不属于ORM框架,而且在spring boot中spring-data-jpa jar包自动整合了hibernate.

1. pom.xml

<parent>
        <groupId>org.springframework.boot</groupId>
    	<artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.1.RELEASE</version>
</parent>
<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>3.5.3</version>
        </dependency>
</dependencies>

2. 实体类User.java

@Data
@Entity
@Table(name="tableName")
public class users {
    @Id
    private Long id;
    
    @Column(name = "NAME",nullable = false)
    private String name;

@Tabel 和 @Column如果与数据库表名称相同可省略

3. 接口UserRepository.java

public interface accountRepository extends JpaRepository<Users, Long> {

    public users findById(Long id);

    @Modifying
    @Query(nativeQuery = true,value = "insert into USERS(id,name) values (?1,?2)")
    void addUser(Long id,String name);
}

增删改操作需要加@Modifying注解

4. UserController.java

@RestController
public UserController{
	@Autowired
    UserRepository userRepository;
    
	@GetMapping("/user")
    public User Users(@Requestparam("id") Long Id){
        return UserRepository.findById();
    }
}

5. 配置文件

spring:
  datasource:
    username: 
    password: 
    url: 
    driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true