mybatis整合sqlite详解

1、pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>cn.suncsf.sc.sf.wfilesearch</groupId>
    <artifactId>win-file-search</artifactId>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <maven.compiler.target>1.8</maven.compiler.target>
        <maven.compiler.source>1.8</maven.compiler.source>
    </properties>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.28.0</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.0</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>2.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.1.4.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>cn.suncsf.framework.core</groupId>
            <artifactId>suncsf-core</artifactId>
            <version>1.0-SNAPSHOT</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.1.4.RELEASE</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.6.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

 

2、配置applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:util="http://www.springframework.org/schema/util"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd
       http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd
       http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.3.xsd
       http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
       http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.3.xsd">
    <context:component-scan base-package="cn.suncsf.sc.sf.wfilesearch" />
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="name" value="test" />
        <property name="password" value="" />
        <property name="username" value="" />
        <property name="driverClassName" value="org.sqlite.JDBC" />
        <property name="url" value="jdbc:sqlite:src/main/resources/data/dt.db" />
        <property name="dbType" value="sqlite" />
        <property name="validationQuery" value="SELECT 'X'" />
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="mapperLocations" value="classpath:mapper/*.xml" />
        <property name="configLocation" value="classpath:mybatis/SqlMapConfig.xml" />
    </bean>

    <bean id="sessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory" />
    </bean>

    <bean id="mapperScanner" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="cn.suncsf.sc.sf.wfilesearch.mapper" />
    </bean>
</beans>

3、配置SqlMapConfig.xml

<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <typeAliases>
        <package name="cn.suncsf.sc.sf.wfilesearch.dt" />
    </typeAliases>
</configuration>

4、配置FileMapper.xml文件

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.suncsf.sc.sf.wfilesearch.mapper.FileMapper">
    <select id="findAll" resultType="T_File">
        select * from t_file;
    </select>
    <insert id="save" parameterType="T_File">
        insert into t_file (id, name, path) values (#{id},#{name},#{path});
    </insert>
    
    <select id="findByName" parameterType="String" resultType="T_File">
        
    </select>
</mapper>

5、resources/data/dt.db 库建立

--
-- 由SQLiteStudio v3.2.1 产生的文件 周一 8月 26 16:35:28 2019
--
-- 文本编码：System
--
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

-- 表：t_file
CREATE TABLE t_file (id BIGINT (11) PRIMARY KEY, name CHAR (200) UNIQUE, path CHAR (1000) NOT NULL);

COMMIT TRANSACTION;
PRAGMA foreign_keys = on;

6、实体文件 T_File.java

package cn.suncsf.sc.sf.wfilesearch.dt;

import cn.suncsf.framework.core.entity.EntityDatabase;

/**
 * @author sunchao
 * @version 1.0.0
 * @date 2019/8/26 15:18
 * @create 2019/8/26 15:18
 * @description
 */
public class T_File extends EntityDatabase {

    private String id = generateId("FL");

    private String name;

    private String path;


    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPath() {
        return path;
    }

    public void setPath(String path) {
        this.path = path;
    }
}

7、目录结构

 8、注意点

db路径jdbc连接url：jdbc:sqlite:src/main/resources/data/dt.db

driverClassName：org.sqlite.JDBC

cn.suncsf.framework.core(可移除不适用)：GitHub(https://github.com/aaaa4444ss22/suncsf-framework.git)