Java利用ormlite库直接操作sqlite数据库（javasqlite嵌入式数据库）

项目中因为数据量不是很大，而且原有的代码中使用了sqlite数据库，为了方便地利用Java来操作sqlite数据库，这里采用了ormlite库实现，当然有很多非常常用的框架，例如mybatis, hibernate等，项目没有使用springboot所有这里不介绍这些。

首先是在CentOS看是否已经安装了sqilte，大部分都自带的。如果没有这个比较简单，根据官网操作就可以了， 官网https://www.sqlite.org/index.html。

root@localhost:/# sqlite3
SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

项目中首先添加pom.xml文件：

    <dependency>
            <groupId>com.j256.ormlite</groupId>
            <artifactId>ormlite-core</artifactId>
            <version>5.0</version>
            <scope>provided</scope>
     </dependency>
     <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.21.0.1</version>
            <scope>provided</scope>
     </dependency>
     <dependency>
            <groupId>com.j256.ormlite</groupId>
            <artifactId>ormlite-jdbc</artifactId>
            <version>5.0</version>
            <scope>provided</scope>
     </dependency>

ormlite-core 源代码： https://github.com/j256/ormlite-core

对于数据库的操作来说，因为需要的操作不多，所以ormlite完全满足需求。 本项目中采用策略者模式，利用统一的接口来实现CRUD操作。

首先是定义接口：

public interface DatabaseOperation<M, R> {
    R perform(Dao<M, Long> dao) throws SQLException;
}

数据库具体操作：

import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.DaoManager;
import com.j256.ormlite.jdbc.JdbcConnectionSource;

public abstract class DataStore<M> {
    
    private static Lock connectLock = new ReentrantLock();
    private Class<M> clazz;
    private String connectionUrl = "jdbc:sqlite:/var/sqlite/" + getTableName() + ".sqlite";        // 数据库名称
    public DataStore(Class<M> clazz) {
        this.clazz = clazz;
    }

    // 查找
    public M find(final Long id) throws SQLException {
        return performDbOp(new DatabaseOperation<M, M>() {
            @Override
            public M perform(Dao<M, Long> dao) throws SQLException {
                return dao.queryForId(id);
            }
        });
    }
    public Collection<M> findAll() throws SQLException {
        return performDbOp(new DatabaseOperation<M, Collection<M>>() {
            @Override
            public Collection<M> perform(Dao<M, Long> dao) throws SQLException {
                return dao.queryForAll();
            }
        });
    }

    public Collection<M> findAllForEq(final String fieldName, final Object value) throws SQLException {
        HashMap<String, Object> singlePropertyMap = new HashMap<>();
        singlePropertyMap.put(fieldName, value);
        return this.findAllForEq(singlePropertyMap);
    }
    public Collection<M> findAllForEq(final Map<String, Object> properties) throws SQLException {
        return performDbOp(new DatabaseOperation<M, Collection<M>>() {
            @Override
            public Collection<M> perform(Dao<M, Long> dao) throws SQLException {
                return dao.queryForFieldValues(properties);
            }
        });
    }

       // 创建或更新。
    public synchronized void save(final M record) throws SQLException {
        performDbOp(new DatabaseOperation<M, Void>() {
            @Override
            public Void perform(Dao<M, Long> dao) throws SQLException {
                dao.createOrUpdate(record);
                return null;
            }
        });
    }
    
    /*
     * batching save data.
     */
    public synchronized void save(final Collection<M> records) throws SQLException {
        performDbOp(new DatabaseOperation<M, Void>() {
            @Override
            public Void perform(Dao<M, Long> dao) throws SQLException {
                for(M record : records) {
                    dao.createOrUpdate(record);
                }
                return null;
            }
        });
    }

       // 删除
    public synchronized void delete(final Long id) throws SQLException {
        if (id == null) {
            throw new NullPointerException();
        }
        performDbOp(new DatabaseOperation<M, Void>() {
            @Override
            public Void perform(Dao<M, Long> dao) throws SQLException {
                dao.deleteById(id);
                return null;
            }
        });
    }
    
    /**
     * delete bundle data according to ids.
     */
    public synchronized void delete(final Collection<Long> ids) throws SQLException {
        if (ids == null || ids.isEmpty()) {
            return ;
        }
        performDbOp(new DatabaseOperation<M, Void>() {
            @Override
            public Void perform(Dao<M, Long> dao) throws SQLException {
                dao.deleteIds(ids);
                return null;
            }
        });
    }
    
        // 数据库真正执行代码。
    public <R> R performDbOp(DatabaseOperation<M, R> operation) throws SQLException {
        Dao<M, Long> dao = null;
        connectLock.lock();
        try {
            dao = DaoManager.createDao(new JdbcConnectionSource(connectionUrl), clazz);
            return operation.perform(dao);
        } finally {
            if (dao != null && dao.getConnectionSource() != null) {
                try {
                    DaoManager.unregisterDao(dao.getConnectionSource(), dao);
                    dao.getConnectionSource().close();
                } catch (IOException e) {
                }
            }
            connectLock.unlock();
        }
    }
    protected abstract String getTableName();
}

只要继承于DataStore<>就可以实现数据库的直接操作。

import java.sql.SQLException;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.table.TableUtils;

public class DBVersionDataStore extends DataStore<DBVersion> {
    private static final DBVersionDataStore instance = new DBVersionDataStore();
    {
        // Create the table in database if it does not exist yet
        try {
            performDbOp(new DatabaseOperation<DBVersion, Void>() {
                @Override
                public Void perform(Dao<DBVersion, Long> dao) throws SQLException {
                    TableUtils.createTableIfNotExists(dao.getConnectionSource(), DBVersion.class);
                    return null;
                }
            });
        } catch (SQLException e) {
        }
    }
    public DBVersionDataStore() {
        super(DBVersion.class);
    }
    @Override
    protected String getTableName() {
        return DBVersion.DBVERSION_TABLE_NAME;
    }
    public static DBVersionDataStore getInstance() {
        return instance;
    }
}

可以写简单的例子就可以测试下。

public class DBTest {
    public static void main(String[] args) {
        DataStore<DBVersion> sDBVersionDataStore = DBVersionDataStore.getInstance();
        Collection<DBVersion> dbVersions = sDBVersionDataStore.findAll();
    }
}