SpringBoot+SQLte3整合

创建一个SpringBoot项目

新建Spring Boot项目

在pom.xml文件中添加所需要的依赖jar

<!-- SQLite 驱动 -->
<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.21.0.1</version>
</dependency>

pom.xml文件依赖如下

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- SQLite 驱动 -->
<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.21.0.1</version>
</dependency>
    

创建sqlite数据库

在项目的根目录下创建文件 blog-db.sqlite (名字随便起)

然后在IDEA右边边上有一个Database，点击创建SQLite数据库

拷贝下面创建表语句，执行即可创建表

create table d_users
(
    id        int constraint "d-users_pk" primary key,
    username  text not null,
    password  text not null,
    start     int default 1 not null,
    foundTime text
);
insert into main.d_users (id, username, password, start, foundTime) values (1, 'xml', '12345', 123, '2021-04-28 01:12:45');
insert into main.d_users (id, username, password, start, foundTime) values (2, '小明', '123456', 321, '2021-04-28 01:12:37');
insert into main.d_users (id, username, password, start, foundTime) values (3, '小芳', '123456', 321, '2021-04-28 01:13:29');

or
手动创建

配置application.properties

#server.port=8081
#server.servlet.context-path=/first
#debug=true
# 粘贴此URL（和创建数据库面板URL保持一致）
spring.datasource.url=jdbc:sqlite:blog-db.sqlite
#spring.datasource.url=jdbc:mysql://localhost:3306/demo
spring.datasource.username=
spring.datasource.password=

#mybatis.typeAliasesPackage：为实体对象所在的包，跟数据库表一一对应
#mybatis.mapperLocations：mapper文件的位置

#mybatis.typeAliasesPackage=com.example.demo.entity
#mybatis.mapperLocations=classpath:mapper/*Dao.xml
#or 写法不同，不过结果一样
#mybatis.type-aliases-package=com.example.demo.entity
#mybatis.mapper-locations=classpath:mapper/*Dao.xml

一切准备就绪开始写业务

src/main/java/com/example/demo/entity/User.java

public class User {
    private Integer id;
    private String userName;
    private String password;
    private Integer start;
    private String 
    .........
}

com/example/demo/dao/UserDao.java

@Mapper
@Repository
public interface UserDao {
    @Select(" select * from d_users where id = #{id}")
    User findUserById(Integer id);

    @Select(" select * from d_users")
    List<User> findUserAll();
}

com/example/demo/service/UserService.java

public interface UserService {
    User findUserById(Integer id);

    List<User> findUserAll();
}

com/example/demo/service/impl/UserServiceImpl.java

@Service
public class UserServiceImpl implements UserService {
    @Resource
    private UserDao userDao;

    @Override
    public User findUserById(Integer id) {
        return userDao.findUserById(id);
    }

    @Override
    public List<User> findUserAll() {
        return userDao.findUserAll();
    }
}

/**
 * (D_Users)表控制层
 */
@RestController
@RequestMapping("/api")
public class UserController {
    /**
     * 服务对象
     */
    @Resource
    private UserService userService;

    @RequestMapping("/user/{id}")
    public Map getUser(@PathVariable int id) {
        HashMap<Object, Object> result = new HashMap<>();
        User user = userService.findUserById(id);
        result.put("date",user);
        return result;
    }

    @RequestMapping("/users")
    public Map getUsers() {
        Map<Object, Object> result = new HashMap<>();
        List<User> userAll = userService.findUserAll();
        result.put("date",userAll);
        return result;
    }
}

output

GET http://localhost:8080/api/users

HTTP/1.1 200 
Content-Type: application/json
Transfer-Encoding: chunked
Date: Wed, 28 Apr 2021 00:31:35 GMT

{
  "date": [
    {
      "id": 1,
      "userName": "xml",
      "password": "12345",
      "start": 123,
      "foundTime": "2021-04-28 01:12:45"
    },
    {
      "id": 2,
      "userName": "小明",
      "password": "123456",
      "start": 321,
      "foundTime": "2021-04-28 01:12:37"
    },
    {
      "id": 3,
      "userName": "小芳",
      "password": "123456",
      "start": 321,
      "foundTime": "2021-04-28 01:13:29"
    }
  ]
}