项目中因为数据量不是很大，而且原有的代码中使用了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();
}
}
本文暂时没有评论，来添加一个吧(●'◡'●)