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项目，编写相关代码，项目结构如下。

2、全部代码

2.1 pom.xml

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.6</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>sqlite</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sqlite</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.36.0.3</version>
        </dependency>
        <dependency>
            <groupId>com.github.gwenn</groupId>
            <artifactId>sqlite-dialect</artifactId>
            <version>0.1.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
            <scope>provided</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.properties

#驱动名称
spring.datasource.driver-class-name=org.sqlite.JDBC
#数据库地址
spring.datasource.url=jdbc:sqlite:test.db
#显示数据库操作记录
spring.jpa.show-sql=true
#每次启动更改数据表结构
spring.jpa.hibernate.ddl-auto=update
#数据库用户名和密码，由于sqltie3的开源版并没有数据库加密功能，这两个配置无效
#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>{

    //默认提供了Optional<User> findById(Long id);

    User findByName(String name);

    @Query("select u from User u where u.id <= ?1")
    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);
    }
}

3 运行测试

3.1 运行项目

等待一会，如下图所示，可以发现项目目录下多了一个test.db文件，也就是sqlite数据库文件。

3.2 测试初始化接口

http://localhost:8080/init

可以看到控制台输出的SQL预计

3.3 测试查询接口

（1）通过name查询
http://localhost:8080/userByName/test1

（2）通过id查询
http://localhost:8080/userById/1

3.4 测试分页查询接口

http://localhost:8080/page

3.5 条件查询

查询id值小于等于5的记录

http://localhost:8080/page/5

3.6 测试更新接口

（1）更新id=1的记录，name设置为newName
http://localhost:8080/update/1

（2）查询id=1的记录，可以看到name值已经更新

（3）更新id=1且name=newName的记录，设置name的值为newName_update

http://localhost:8080/update/1/newName

这篇好文章是转载于：学新通技术网