一、依赖引入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 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.site</groupId> <artifactId>sqlite_test</artifactId> <version>1.0-SNAPSHOT</version> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.3.RELEASE</version> <relativePath/> </parent> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-actuator</artifactId> </dependency> <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> <!-- https://mvnrepository.com/artifact/com.enigmabridge/hibernate4-sqlite-dialect --> <dependency> <groupId>com.enigmabridge</groupId> <artifactId>hibernate4-sqlite-dialect</artifactId> <version>0.1.2</version> </dependency> <dependency> <groupId>io.springfox</groupId> <artifactId>springfox-swagger2</artifactId> <version>2.8.0</version> </dependency> <dependency> <groupId>io.springfox</groupId> <artifactId>springfox-swagger-ui</artifactId> <version>2.8.0</version> </dependency> <!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc --> <dependency> <groupId>org.xerial</groupId> <artifactId>sqlite-jdbc</artifactId> <version>3.23.1</version> </dependency> </dependencies> </project>
二、数据源配置DataSourceConfig
package com.site.config; import org.springframework.boot.jdbc.DataSourceBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.sqlite.SQLiteDataSource; import javax.sql.DataSource; @Configuration public class DataSourceConfig { @Bean(destroyMethod = "", name = "EmbeddedDataSource") public DataSource dataSource() { DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create(); dataSourceBuilder.driverClassName("org.sqlite.JDBC"); //sqlite文件路径，可以是绝对路径也可以是相对路径 dataSourceBuilder.url("jdbc:sqlite:C:\\\\sqlite\\test.db"); dataSourceBuilder.type(SQLiteDataSource.class); return dataSourceBuilder.build(); } }
三、JPA配置JpaConfig
package com.site.config; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.data.jpa.repository.config.EnableJpaRepositories; import org.springframework.orm.jpa.JpaTransactionManager; import org.springframework.orm.jpa.JpaVendorAdapter; import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean; import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter; import org.springframework.transaction.annotation.EnableTransactionManagement; import javax.persistence.EntityManagerFactory; import javax.sql.DataSource; @Configuration @EnableJpaRepositories( basePackages = "com.site.repository", transactionManagerRef = "jpaTransactionManager", entityManagerFactoryRef = "localContainerEntityManagerFactoryBean" ) @EnableTransactionManagement public class JpaConfig { @Autowired @Bean public JpaTransactionManager jpaTransactionManager(@Qualifier(value = "EmbeddedDataSource") DataSource dataSource, EntityManagerFactory entityManagerFactory) { JpaTransactionManager jpaTransactionManager = new JpaTransactionManager(); jpaTransactionManager.setEntityManagerFactory(entityManagerFactory); jpaTransactionManager.setDataSource(dataSource); return jpaTransactionManager; } @Autowired @Bean LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean(@Qualifier(value = "EmbeddedDataSource") DataSource dataSource, JpaVendorAdapter jpaVendorAdapter) { LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean(); localContainerEntityManagerFactoryBean.setDataSource(dataSource); localContainerEntityManagerFactoryBean.setPackagesToScan("com.site.model"); localContainerEntityManagerFactoryBean.setJpaVendorAdapter(jpaVendorAdapter); return localContainerEntityManagerFactoryBean; } @Bean public JpaVendorAdapter jpaVendorAdapter() { HibernateJpaVendorAdapter hibernateJpaVendorAdapter = new HibernateJpaVendorAdapter(); hibernateJpaVendorAdapter.setGenerateDdl(true); hibernateJpaVendorAdapter.setShowSql(true); hibernateJpaVendorAdapter.setDatabasePlatform("com.enigmabridge.hibernate.dialect.SQLiteDialect"); return hibernateJpaVendorAdapter; } }
四、实体类User
package com.site.model; import org.springframework.boot.autoconfigure.data.jpa.JpaRepositoriesAutoConfiguration; import javax.persistence.*; @Entity @Table(name = "t_user") public class User extends JpaRepositoriesAutoConfiguration { @Id @GeneratedValue(strategy = GenerationType.AUTO) private Long id; private String name; private String password; private String id_no; private String birth; private String sex; private String address; private String email; private String mobile; private String datetime; // 省略get&set }
五、UserRepository
package com.site.repository; import com.site.model.User; import org.springframework.data.domain.Page; import org.springframework.data.domain.Pageable; import org.springframework.data.domain.Sort; import org.springframework.data.jpa.repository.Query; import org.springframework.data.repository.CrudRepository; import org.springframework.data.web.PageableDefault; import org.springframework.stereotype.Component; import java.util.List; @Component public interface UserRepository extends CrudRepository<User, Long> { User findById(Long id); @Query(value = "select * from t_user where name like %?1%", nativeQuery = true) List<User> findByNameLike(String name); Page<User> findAll(@PageableDefault(value = 15, sort = {"datetime"}, direction = Sort.Direction.DESC) Pageable pageable); }
六、 UserController
package com.site.controller; import com.site.model.User; import com.site.repository.UserRepository; import com.site.utils.PageUtil; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import org.springframework.data.domain.Page; import org.springframework.data.domain.Pageable; import org.springframework.web.bind.annotation.*; import javax.annotation.Resource; import java.util.List; @Api(value = "查询用户信息") @RestController @RequestMapping("user") public class UserController { @Resource UserRepository repository; //根据编号查询 @ApiOperation(value = "根据ID查询用户信息") @PostMapping(value = "/findById") User findById(@RequestParam(value = "id") Long id) { return repository.findById(id); } @ApiOperation(value = "根据姓名查询用户信息") @PostMapping(value = "findName") List<User> findName(@RequestParam(value = "name") String name) { return repository.findByNameLike(name); } @ApiOperation(value = "查询所有") @PostMapping(value = "findAll") //currentPage 当前页，默认为0，如果传1的话是查的第二页数据 //pageSize 每页数据条数 Page<User> findAll(@RequestParam(value = "currentPage") int currentPage, @RequestParam(value = "pageSize") int pageSize) { Pageable pageable = PageUtil.getPageable(currentPage, pageSize, "datetime"); return repository.findAll(pageable); } }
七、分页工具类PageUtil
package com.site.utils; import org.springframework.data.domain.PageRequest; import org.springframework.data.domain.Pageable; import org.springframework.data.domain.Sort; public class PageUtil { /** * 分页 * * @param page 当前页 默认是0 * @param size 每页条数 * @param keyword 排序字段 * @return */ public static Pageable getPageable(Integer page, Integer size, String keyword) { Sort sort = new Sort(Sort.Direction.DESC, keyword); return PageRequest.of(page, size, sort); } }
八、启动类Starter
@SpringBootApplication @EnableSwagger2 public class Starter { public static void main(String[] args) { SpringApplication.run(Starter.class); } @Bean public Docket swaggerApi() { return new Docket(DocumentationType.SWAGGER_2) .select() .apis(RequestHandlerSelectors.basePackage("com.site")) .paths(PathSelectors.any()) .build() .apiInfo(new ApiInfoBuilder().version("1.0").title("sqlite Test").description("sqlite Test").build()); } }
