spring boot+sqlite+mybatis实现增删改查例子

主要是更换了下sqlite的数据源而已，其他代码不变。

我都贴一下吧，这个算是比较通用的基础增删改查代码。

 

 

1.创建test.db

可以使用Idea自带的Database插件配置，也可以命令行创建，具体我就不讲了，看下图

初始化数据：

create table tb_user
(
  id integer primary key  autoincrement not null,
  username  varchar(20),
  sex varchar(10),
  age integer
)
;


insert into tb_user(username,sex,age) values ('Jack','male',20);
insert into tb_user(username,sex,age) values ('Lucy','female',21);
insert into tb_user(username,sex,age) values ('Dylan','male',18);

 

 

2.配置application.yml

spring:
  datasource:
    driver-class-name: org.sqlite.JDBC
    url: jdbc:sqlite:D:/ideaprojects/sqlitedemo/src/main/resources/test.db
    username:
    password:

#mybatis
mybatis:
  mapperLocations: classpath*:mapper/*.xml

3.写实体类

package com.laoxu.test.sqlitedemo.model;

import lombok.Data;

/**
 * @Description:
 * @Author laoxu
 * @Date 2019/11/5 22:25
 **/
@Data
public class User {
    private Integer id;
    private String username;
    private String sex;
    private Integer age;
}

 

4.写mapper接口和xml文件

package com.laoxu.test.sqlitedemo.mapper;

import com.laoxu.test.sqlitedemo.model.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * @Description:
 * @Author laoxu
 * @Date 2019/11/5 22:27
 **/
@Mapper
@Component
public interface UserDao {
    int insert(User user);
    User select(Integer id);
    List<User> selectAll();
    int delete(Integer id);
    int update(User user);

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.laoxu.test.sqlitedemo.mapper.UserDao">
    <resultMap id="userMap" type="com.laoxu.test.sqlitedemo.model.User">
        <result property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="sex" column="sex"/>
        <result property="age" column="age"/>
    </resultMap>

    <insert id="insert" parameterType="com.laoxu.test.sqlitedemo.model.User">
      insert into tb_user (
             username,
             sex,
             age
              )
      VALUES (
            #{username},
            #{sex},
            #{age}
          )
    </insert>

    <update id="update" parameterType="com.laoxu.test.sqlitedemo.model.User">
        update tb_user
        set username=#{username},
            sex=#{sex},
            age = #{age}
        where id=#{id}
    </update>

    <delete id="delete">
        DELETE FROM tb_user
        WHERE id = #{id}
        <!--IN
        <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>-->
    </delete>

    <select id="select" parameterType="java.lang.Integer" resultMap="userMap">
        select id, username, sex, age
        from tb_user
        where id = #{id}
    </select>

    <select id="selectAll" resultMap="userMap">
        select id,username,sex,age
        from tb_user
    </select>

</mapper>

 

5.写service和controller

package com.laoxu.test.sqlitedemo.service;

import com.laoxu.test.sqlitedemo.mapper.UserDao;
import com.laoxu.test.sqlitedemo.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.jws.soap.SOAPBinding;
import java.util.List;

/**
 * @Description:
 * @Author laoxu
 * @Date 2019/11/5 23:10
 **/
@Service
public class UserService {
    @Autowired
    private UserDao userDao;

    public boolean add(User user) {
        return userDao.insert(user) > 0;
    }

    public User getOne(int id) {
        return userDao.select(id);
    }

    public List<User> getAll() {
        return userDao.selectAll();
    }

    public boolean modify(User user) {
        return userDao.update(user) > 0;
    }

    public boolean remove(Integer id) {
        return userDao.delete(id) > 0;
    }


}

 

package com.laoxu.test.sqlitedemo.controller;

import com.laoxu.test.sqlitedemo.model.User;
import com.laoxu.test.sqlitedemo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * @Description:
 * @Author laoxu
 * @Date 2019/11/5 23:13
 **/
@RestController
@RequestMapping("/api/user")
public class UserController {
    @Autowired
    private UserService userService;

    @GetMapping("/get/{id}")
    public User get(@PathVariable Integer id){
        return userService.getOne(id);
    }

    @GetMapping("/getAll")
    public List<User> getAll(){
        return userService.getAll();
    }

    @PostMapping("/remove")
    public Boolean remove(@RequestParam Integer id){
        return userService.remove(id);
    }

    @PostMapping("/add")
    public Boolean add(@RequestBody User user){
        return userService.add(user);
    }

    @PostMapping("/modify")
    public Boolean update(@RequestBody User user){
        return userService.modify(user);
    }

}

 

6.启动项目进行测试

 

增

---------------------

 

删

----------------------

改

-----------------------

查

 

源码地址：

https://gitee.com/indexman/sqlitedemo