spring-boot-starter-data-jpa + SQLite简单例子（含全部代码）_程裕强_jpa配置sqlite

irpas 02-07 7321

1、介绍 1.1 SQLite

SQLite官网：http://·/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>  <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