java spring sqlite,Spring Boot+MyBatis+SQLite配置

Spring Boot+MyBatis+SQLite配置例子参考下面

创建新项目

项目类型务必选择箭头指定的类型，否则不会自动生成代码模版

增加依赖项

junit

junit

4.13.1

test

org.xerial

sqlite-jdbc

3.32.3.2

org.mybatis

mybatis

3.5.6

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

junit

junit

4.13.1

test

org.xerial

sqlite-jdbc

3.32.3.2

org.mybatis

mybatis

3.5.6

如下图:

根据下图中的指示创建如下的几个文件

文件中的具体源代码如下：

DemoMapper.java

Java

package com.example.demo.mapper;

import com.example.demo.model.DemoModel;

import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper

public interface DemoMapper {

// 插入 并查询id 赋给传入的对象

@Insert("INSERT INTO tb_test(key, value) VALUES(#{key}, #{value})")

@SelectKey(statement = "SELECT seq id FROM sqlite_sequence WHERE (name = 'tb_test')", before = false, keyProperty = "id", resultType = int.class)

int insert(DemoModel model);

// 根据 ID 查询

@Select("SELECT * FROM tb_test WHERE id=#{id}")

DemoModel select(int id);

// 查询全部

@Select("SELECT * FROM tb_test")

List selectAll();

// 更新 value

@Update("UPDATE tb_test SET value=#{value} WHERE id=#{id}")

int updateValue(DemoModel model);

// 根据 ID 删除

@Delete("DELETE FROM tb_test WHERE id=#{id}")

int delete(Integer id);

int existTable(String tableName);

int dropTable(@Param("tableName") String tableName);

int createNewTable(@Param("tableName") String tableName);

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

packagecom.example.demo.mapper;

importcom.example.demo.model.DemoModel;

importorg.apache.ibatis.annotations.*;

importjava.util.List;

@Mapper

publicinterfaceDemoMapper{

// 插入 并查询id 赋给传入的对象

@Insert("INSERT INTO tb_test(key, value) VALUES(#{key}, #{value})")

@SelectKey(statement="SELECT seq id FROM sqlite_sequence WHERE (name = 'tb_test')",before=false,keyProperty="id",resultType=int.class)

intinsert(DemoModelmodel);

// 根据 ID 查询

@Select("SELECT * FROM tb_test WHERE id=#{id}")

DemoModelselect(intid);

// 查询全部

@Select("SELECT * FROM tb_test")

ListselectAll();

// 更新 value

@Update("UPDATE tb_test SET value=#{value} WHERE id=#{id}")

intupdateValue(DemoModelmodel);

// 根据 ID 删除

@Delete("DELETE FROM tb_test WHERE id=#{id}")

intdelete(Integerid);

intexistTable(StringtableName);

intdropTable(@Param("tableName")StringtableName);

intcreateNewTable(@Param("tableName")StringtableName);

}

DemoModel.java

Java

package com.example.demo.model;

public class DemoModel {

private Integer id;

private String key;

private String value;

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public String getKey() {

return key;

}

public void setKey(String key) {

this.key = key;

}

public String getValue() {

return value;

}

public void setValue(String value) {

this.value = value;

}

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

packagecom.example.demo.model;

publicclassDemoModel{

privateIntegerid;

privateStringkey;

privateStringvalue;

publicIntegergetId(){

returnid;

}

publicvoidsetId(Integerid){

this.id=id;

}

publicStringgetKey(){

returnkey;

}

publicvoidsetKey(Stringkey){

this.key=key;

}

publicStringgetValue(){

returnvalue;

}

publicvoidsetValue(Stringvalue){

this.value=value;

}

}

Mapper.xml

XHTML

/p>

"http://mybatis.org/dtd/mybatis-3-mapper.dtd" >

select count(*)

from information_schema.TABLES

where LCASE(table_name)=#{tableName}

DROP TABLE IF EXISTS ${tableName}

CREATE TABLE IF NOT EXISTS ${tableName} (

id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

key VARCHAR(20),

value VARCHAR(255)

)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

/p>

"http://mybatis.org/dtd/mybatis-3-mapper.dtd" >

select count(*)

from information_schema.TABLES

where LCASE(table_name)=#{tableName}

DROP TABLE IF EXISTS ${tableName}

CREATE TABLE IF NOT EXISTS ${tableName} (

id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

key VARCHAR(20),

value VARCHAR(255)

)

mybatis-config.xml

XHTML

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

配置数据库以及驱动信息(数据库扩展名务必是.sqlite这样可以保证IntelliJ IDEA可以直接查看数据库内容)：

application.properties

Vim

spring.datasource.driver-class-name=org.sqlite.JDBC

spring.datasource.url=jdbc:sqlite:sample.sqlite

spring.datasource.username=

spring.datasource.password=

1

2

3

4

spring.datasource.driver-class-name=org.sqlite.JDBC

spring.datasource.url=jdbc:sqlite:sample.sqlite

spring.datasource.username=

spring.datasource.password=

测试代码如下：

DemoApplicationTests.java

Java

package com.example.demo;

import com.example.demo.mapper.DemoMapper;

import com.example.demo.model.DemoModel;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.junit.Assert;

import org.junit.jupiter.api.Test;

import org.springframework.boot.test.context.SpringBootTest;

import java.io.Reader;

import java.util.List;

@SpringBootTest

class DemoApplicationTests {

@Test

void contextLoads() throws Exception {

String resource = "mybatis-config.xml";//配置文件

Reader reader = Resources.getResourceAsReader(resource);

SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();

SqlSessionFactory sqlMapper = builder.build(reader);

//设置为true 自动提交事务

SqlSession sqlSession = sqlMapper.openSession();

reader.close();//关闭读取流

DemoMapper mapper = sqlSession.getMapper(DemoMapper.class);//获取Mapper

mapper.createNewTable("tb_test");

List list = mapper.selectAll();//获取结果

sqlSession.close();

Assert.assertEquals(list.size(), 0);

}

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

packagecom.example.demo;

importcom.example.demo.mapper.DemoMapper;

importcom.example.demo.model.DemoModel;

importorg.apache.ibatis.io.Resources;

importorg.apache.ibatis.session.SqlSession;

importorg.apache.ibatis.session.SqlSessionFactory;

importorg.apache.ibatis.session.SqlSessionFactoryBuilder;

importorg.junit.Assert;

importorg.junit.jupiter.api.Test;

importorg.springframework.boot.test.context.SpringBootTest;

importjava.io.Reader;

importjava.util.List;

@SpringBootTest

classDemoApplicationTests{

@Test

voidcontextLoads()throwsException{

Stringresource="mybatis-config.xml";//配置文件

Readerreader=Resources.getResourceAsReader(resource);

SqlSessionFactoryBuilderbuilder=newSqlSessionFactoryBuilder();

SqlSessionFactorysqlMapper=builder.build(reader);

//设置为true 自动提交事务

SqlSessionsqlSession=sqlMapper.openSession();

reader.close();//关闭读取流

DemoMappermapper=sqlSession.getMapper(DemoMapper.class);//获取Mapper

mapper.createNewTable("tb_test");

Listlist=mapper.selectAll();//获取结果

sqlSession.close();

Assert.assertEquals(list.size(),0);

}

}

参考链接