spring-boot-starter-data-jpa + SQLite简单例子（含全部代码）

1、介绍

1.1 SQLite

SQLite官网：http://www.sqlite.org/
SQLite是比 Access 更优秀的文件型数据库，支持复杂的 SQL 语句，支持索引、触发器，速度很快，开源等。

1.2 spring-boot-starter-data-jpa

Spring Data JPA 是 Spring 基于 ORM 框架、JPA 规范的基础上封装的一套JPA应用框架，可使开发者用极简的代码即可实现对数据的访问和操作。它提供了包括增删改查等在内的常用功能，且易于扩展。spring-boot-starter-data-jpa是SpringBoot的进一步封装。

1.3 项目结构

新建一个springboot项目，编写相关代码，项目结构如下。

spring-boot-starter-data-jpa + SQLite简单例子（含全部代码）

; 2、全部代码

2.1 pom.xml


<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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0modelVersion>
    <parent>
        <groupId>org.springframework.bootgroupId>
        <artifactId>spring-boot-starter-parentartifactId>
        <version>2.5.6version>
        <relativePath/>
    parent>
    <groupId>com.examplegroupId>
    <artifactId>sqliteartifactId>
    <version>0.0.1-SNAPSHOTversion>
    <name>sqlitename>
    <description>Demo project for Spring Bootdescription>
    <properties>
        <java.version>1.8java.version>
    properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-data-jpaartifactId>
        dependency>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
        dependency>

        <dependency>
            <groupId>org.xerialgroupId>
            <artifactId>sqlite-jdbcartifactId>
            <version>3.36.0.3version>
        dependency>
        <dependency>
            <groupId>com.github.gwenngroupId>
            <artifactId>sqlite-dialectartifactId>
            <version>0.1.2version>
        dependency>

        <dependency>
            <groupId>org.projectlombokgroupId>
            <artifactId>lombokartifactId>
            <version>1.18.22version>
            <scope>providedscope>
        dependency>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-testartifactId>
            <scope>testscope>
        dependency>
    dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.bootgroupId>
                <artifactId>spring-boot-maven-pluginartifactId>
            plugin>
        plugins>
    build>

project>

2.2 application.properties

#&#x9A71;&#x52A8;&#x540D;&#x79F0;
spring.datasource.driver-class-name=org.sqlite.JDBC
#&#x6570;&#x636E;&#x5E93;&#x5730;&#x5740;
spring.datasource.url=jdbc:sqlite:test.db
#&#x663E;&#x793A;&#x6570;&#x636E;&#x5E93;&#x64CD;&#x4F5C;&#x8BB0;&#x5F55;
spring.jpa.show-sql=true
#&#x6BCF;&#x6B21;&#x542F;&#x52A8;&#x66F4;&#x6539;&#x6570;&#x636E;&#x8868;&#x7ED3;&#x6784;
spring.jpa.hibernate.ddl-auto=update
#&#x6570;&#x636E;&#x5E93;&#x7528;&#x6237;&#x540D;&#x548C;&#x5BC6;&#x7801;&#xFF0C;&#x7531;&#x4E8E;sqltie3&#x7684;&#x5F00;&#x6E90;&#x7248;&#x5E76;&#x6CA1;&#x6709;&#x6570;&#x636E;&#x5E93;&#x52A0;&#x5BC6;&#x529F;&#x80FD;&#xFF0C;&#x8FD9;&#x4E24;&#x4E2A;&#x914D;&#x7F6E;&#x65E0;&#x6548;
#spring.datasource.username=
#spring.datasource.password=

2.3 生成的Application

package com.example.sqlite;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SqliteApplication {

    public static void main(String[] args) {
        SpringApplication.run(SqliteApplication.class, args);
    }

}

2.4 bean层

package com.example.sqlite.bean;
import lombok.*;
import lombok.experimental.Accessors;
import javax.persistence.*;
import java.io.Serializable;
@Data
@NoArgsConstructor
@Accessors(chain = true)
@Entity
@Table(name = "users")
public class User implements Serializable{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
}

2.5 dao层

package com.example.sqlite.dao;
import com.example.sqlite.bean.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import javax.transaction.Transactional;

@Repository
public interface UserRepository extends JpaRepository<User, Long>{

    User findByName(String name);

    @Query("select u from User u where u.id )
    Page<User> findMore(Long maxId, Pageable pageable);

    @Modifying
    @Transactional
    @Query("update User u set u.name = ?1 where u.id = ?2")
    int updateById(String name, Long id);

}

2.6 service层

package com.example.sqlite.service;

import com.example.sqlite.bean.User;
import com.example.sqlite.dao.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

@Service("userService")
public class UserService {
    @Autowired
    private UserRepository userRepository;

    public User getUserByID(Long id){
        return userRepository.findById(id).get();
    }

    public User getByName(String name){
        return userRepository.findByName(name);
    }

    public Page<User> findPage(){
        Pageable pageable = PageRequest.of(0, 10);
        return userRepository.findAll(pageable);
    }

    public Page<User> find(Long maxId){
        Pageable pageable = PageRequest.of(0, 10);
        return userRepository.findMore(maxId,pageable);
    }

    public User save(User u){
        return userRepository.save(u);
    }

    public User update(Long id,String name){
        User user = userRepository.findById(id).get();
        user.setName(name+"_update");
        return userRepository.save(user);
    }

    public Boolean updateById(String  name, Long id){
        return userRepository.updateById(name,id)==1;
    }

}

2.7 controller层

package com.example.sqlite.web;

import com.example.sqlite.bean.User;
import com.example.sqlite.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Optional;

@RestController
@RequestMapping("/")
public class ApiController {

    @Autowired
    private UserService userService;

    @GetMapping("/init")
    public String init(){
        User user = null;
        for(int i=0;i<10;i++){
            user = new User();
            user.setName("test"+i);
            userService.save(user);
        }
        return "初始化完成。";
    }

    @GetMapping("/userByName/{username}")
    public User getUserByName(@PathVariable("username") String username){
        return userService.getByName(username);
    }

    @GetMapping("/userById/{userid}")
    public User getUserById(@PathVariable("userid") Long userid){
        return userService.getUserByID(userid);
    }

    @GetMapping("/page")
    public Page<User> getPage(){
        return userService.findPage();
    }

    @GetMapping("/page/{maxID}")
    public Page<User> getPageByMaxID(@PathVariable("maxID") Long maxID){
        return userService.find(maxID);
    }

    @RequestMapping("/update/{id}/{name}")
    public User update(@PathVariable Long id, @PathVariable String name){
        return userService.update(id,name);
    }

    @RequestMapping("/update/{id}")
    public Boolean updateById(@PathVariable Long id){
        return userService.updateById("newName",id);
    }
}