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
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhgjajhf
系列文章
更多
同类精品
更多
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
excel打印预览压线压字怎么办
PHP中文网 06-22 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01 -
怎样阻止微信小程序自动打开
PHP中文网 06-13