Java：MyBatis+SQLite使用实例

$ tree
.
├── data.db       # 数据库文件
├── pom.xml
└── src
    └── main
        ├── java
        │   └── com
        │       └── mouday
        │           ├── App.java
        │           ├── dao
        │           │   ├── PersonDao.java
        │           │   └── impl
        │           │       └── PersonDaoImpl.java
        │           ├── pojo
        │           │   └── Person.java
        │           └── util
        │               └── MyBatisUtil.java
        └── resources
            ├── db.properties
            ├── mapper
            │   └── PersonMapper.xml
            └── mybatis-config.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>com.mouday</groupId>
    <artifactId>demo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <name>demo</name>
    <!-- FIXME change it to the project's website -->
    <url>http://www.example.com</url>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.32.3</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

driver=org.sqlite.JDBC
url=jdbc:sqlite:data.db

<?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>
    <!-- 加载数据库配置 -->
    <properties resource="db.properties" />
    <!-- 打印sql日志 -->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <!-- 指定整个包下的类都是别名 -->
    <typeAliases>
        <package name="com.mouday.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
            </dataSource>
        </environment>
    </environments>
    <!-- 映射文件 -->
    <mappers>
        <mapper resource="mapper/PersonMapper.xml"/>
    </mappers>
</configuration>

package com.mouday.util;
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 java.io.IOException;
import java.io.InputStream;
public class MyBatisUtil {
    private static SqlSessionFactory factory = null;
    // 使用static静态代码块，随着类的加载而加载，只执行一次
    static {
        try {
            String resource = "mybatis-config.xml";
            // 加载MyBatis的主配置文件
            InputStream inputStream = Resources.getResourceAsStream(resource);
            // 通过构建器（SqlSessionFactoryBuilder）构建一个SqlSessionFactory工厂对象
            factory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession() throws IOException {
        return factory.openSession();
    }
}

package com.mouday.pojo;
public class Person {
    private Integer id;
    private String name;
    private Integer age;
    public Person(String name, Integer age) {
        this.name = name;
        this.age = age;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    @Override
    public String toString() {
        return "Person{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

package com.mouday.dao;
import com.mouday.pojo.Person;
import java.util.List;
public interface PersonDao {
    Integer createTable();
    Integer dropTable();
    List<Person> selectAll();
    Person selectById(Integer id);
    Integer deleteById(Integer id);
    Integer update(Person person);
    Integer insert(Person person);
}

package com.mouday.dao.impl;
import com.mouday.dao.PersonDao;
import com.mouday.pojo.Person;
import com.mouday.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.util.List;
public class PersonDaoImpl implements PersonDao {
    public Integer createTable() {
        try (SqlSession session = MyBatisUtil.getSqlSession()) {
            PersonDao mapper = session.getMapper(PersonDao.class);
            Integer result = mapper.createTable();
            session.commit();
            return result;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return 0;
    }
    @Override
    public Integer dropTable() {
        try (SqlSession session = MyBatisUtil.getSqlSession()) {
            PersonDao mapper = session.getMapper(PersonDao.class);
            Integer result = mapper.dropTable();
            session.commit();
            return result;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return 0;
    }
    @Override
    public List<Person> selectAll() {
        List<Person> list = null;
        try (SqlSession session = MyBatisUtil.getSqlSession()) {
            // Person person = session.selectOne("com.mouday.dao.PersonDao.selectById", 1);
            // 等价于
            PersonDao mapper = session.getMapper(PersonDao.class);
            list = mapper.selectAll();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }
    public Person selectById(Integer id) {
        Person person = null;
        try (SqlSession session = MyBatisUtil.getSqlSession()) {
            // Person person = session.selectOne("com.mouday.dao.PersonDao.selectById", 1);
            // 等价于
            PersonDao mapper = session.getMapper(PersonDao.class);
            person = mapper.selectById(id);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return person;
    }
    public Integer deleteById(Integer id) {
        try (SqlSession session = MyBatisUtil.getSqlSession()) {
            PersonDao mapper = session.getMapper(PersonDao.class);
            Integer result = mapper.deleteById(id);
            session.commit();
            return result;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return 0;
    }
    public Integer insert(Person person) {
        try (SqlSession session = MyBatisUtil.getSqlSession()) {
            PersonDao mapper = session.getMapper(PersonDao.class);
            Integer result = mapper.insert(person);
            session.commit();
            return result;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return 0;
    }
    public Integer update(Person person) {
        try (SqlSession session = MyBatisUtil.getSqlSession()) {
            PersonDao mapper = session.getMapper(PersonDao.class);
            Integer result = mapper.update(person);
            session.commit();
            return result;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return 0;
    }
}

<?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.mouday.dao.PersonDao">
    <update id="dropTable">
        drop table if exists person;
    </update>
    <update id="createTable">
   CREATE TABLE IF NOT EXISTS `person` (
    `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `name` TEXT,
    `age` INTEGER
    )
    </update>
    <select id="selectAll" resultType="Person">
    select * from person
    </select>
    <select id="selectById" resultType="Person">
    select * from person where id = #{id} limit 1
    </select>
    <delete id="deleteById">
        delete from person where id = #{id}
    </delete>
    <insert id="insert" useGeneratedKeys="true">
        insert into person (name, age) values (#{name}, #{age})
    </insert>
    <update id="update">
        update person set name = #{name}, age = #{age} where id = #{id}
    </update>
</mapper>

package com.mouday;
import com.mouday.dao.PersonDao;
import com.mouday.dao.impl.PersonDaoImpl;
import com.mouday.pojo.Person;
import java.io.IOException;
import java.util.List;
public class App {
    public static void main(String[] args) throws IOException {
        PersonDao personDao = new PersonDaoImpl();
        // 建表
        personDao.dropTable();
        personDao.createTable();
        // 插入数据
        personDao.insert(new Person("曹操", 23));
        personDao.insert(new Person("刘备", 24));
        personDao.insert(new Person("孙权", 21));
        // 删除
        Integer deleteResult = personDao.deleteById(1);
        System.out.println(deleteResult);
        // 查询单个
        Person person = personDao.selectById(2);
        System.out.println(person);
        // 更新
        person.setAge(25);
        Integer updateResult = personDao.update(person);
        System.out.println(updateResult);
        // 查询所有
        List<Person> list = personDao.selectAll();
        for(Person p: list){
            System.out.println(p);
        }
    }
}