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);
}
}
参考链接