Java：MyBatis+SQLite使用实例

MyBatis： https://mybatis.org/mybatis-3/zh/index.html

项目结构

$ 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

配置文件

pom.xml

    4.0.0

    com.mouday
    demo
    1.0-SNAPSHOT

    demo
    
    http://www.example.com

    
        UTF-8
        1.8
        1.8
    

    
        
            org.xerial
            sqlite-jdbc
            3.32.3
        

        
            org.mybatis
            mybatis
            3.5.5
        

        
            junit
            junit
            4.11
            test
        
    

db.properties

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

mybatis-config.xml

工具类

MyBatisUtil.java

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

POJO

Plain Ordinary Java Object

Person.java

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

DAO

Data Access Object

PersonDao.java

package com.mouday.dao;

import com.mouday.pojo.Person;

import java.util.List;


public interface PersonDao {
    Integer createTable();
    Integer dropTable();
    List selectAll();
    Person selectById(Integer id);
    Integer deleteById(Integer id);
    Integer update(Person person);
    Integer insert(Person person);

}

PersonDaoImpl.java

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 selectAll() {
        List 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;
    }
}

PersonMapper.xml

    
        drop table if exists person;
    

    
   CREATE TABLE IF NOT EXISTS `person` (
    `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `name` TEXT,
    `age` INTEGER
    )

    

    
    select * from person
    

    
    select * from person where id = #{id} limit 1
    

    
        delete from person where id = #{id}
    

    
        insert into person (name, age) values (#{name}, #{age})
    

    
        update person set name = #{name}, age = #{age} where id = #{id}
    

接口使用

App.java

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 list = personDao.selectAll();
        for(Person p: list){
            System.out.println(p);
        }
    }
}

完整代码:

https://github.com/mouday/MyBatis-demo