深海游弋的鱼 – 默默的点滴

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

创建新项目

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

增加依赖项

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
            <scope>test</scope>
        </dependency>


        <!-- SQLite JDBC library -->
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.32.3.2</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.6</version>
        </dependency>

<groupId>junit</groupId>

<artifactId>junit</artifactId>

</dependency>

<groupId>org.xerial</groupId>

<artifactId>sqlite-jdbc</artifactId>

</dependency>

<groupId>org.mybatis</groupId>

<artifactId>mybatis</artifactId>

</dependency>

如下图:

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

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

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<DemoModel> 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);
}

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<DemoModel> 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);

}

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;
    }
}

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;

}

<?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.example.demo.mapper.DemoMapper">

    <select id="existTable" parameterType="String" resultType="Integer">
        select count(*)
        from information_schema.TABLES
        where LCASE(table_name)=#{tableName}
    </select>

    <update id="dropTable">
        DROP TABLE IF EXISTS ${tableName}
    </update>

    <update id="createNewTable" parameterType="String">
        CREATE TABLE IF NOT EXISTS ${tableName} (
          id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
          key VARCHAR(20),
          value VARCHAR(255)
          )
    </update>
</mapper>

<?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" >

select count(*)

from information_schema.TABLES

where LCASE(table_name)=#{tableName}

</select>

DROP TABLE IF EXISTS ${tableName}

</update>

CREATE TABLE IF NOT EXISTS ${tableName} (

id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

key VARCHAR(20),

value VARCHAR(255)

)

</update>

</mapper>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 引用"application.properties配置文件  -->
    <properties resource="application.properties"/>
    <!-- development : 开发模式 work : 工作模式 -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <!-- 配置数据库连接信息 -->
            <dataSource type="POOLED">
                <!-- value属性值引用db.properties配置文件中配置的值 -->
                <property name="driver" value="${spring.datasource.driver-class-name}"/>
                <property name="url" value="${spring.datasource.url}"/>
                <property name="username" value="${spring.datasource.username}"/>
                <property name="password" value="${spring.datasource.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!-- 自动扫描该包名下的所有Mapper -->
        <!-- <package name="com.mobibrw.apk.publishserv.db.sqlite"/> -->
        <mapper resource="mapper/Mapper.xml"></mapper>
    </mappers>
</configuration>

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE configuration

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

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

</dataSource>

</environment>

</environments>

</mappers>

</configuration>

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

spring.datasource.driver-class-name=org.sqlite.JDBC
spring.datasource.url=jdbc:sqlite:sample.sqlite
spring.datasource.username=
spring.datasource.password=

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

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

spring.datasource.username=

spring.datasource.password=

测试代码如下：

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<DemoModel> list = mapper.selectAll();//获取结果
        sqlSession.close();
        Assert.assertEquals(list.size(), 0);
    }
}

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<DemoModel> 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

Spring Boot+MyBatis+SQLite配置

参考链接

发布者

默默

发表回复取消回复

参考链接

发布者

默默

发表回复 取消回复

发表回复取消回复