1. 為什麼要記憶體資料庫
在常見的Java應用項目中,通常會有需求将資料儲存到DB中。但實際環境中,受到伺服器資源限制或者網絡限制等因素,無法為項目提供DB資源。比如:
- 項目研發初期本地調試運作階段,無法連接配接公司有網絡通路控制的DB;
- 項目單元測試階段,單測資料與正常測試資料污染隔離,清理測試庫效率較低;
- 開發初學者用自己電腦運作程式,但又不想在本地安裝MySQL導緻電腦運作較慢;
- 雲ECS伺服器資源受限;
以上場景下需要DB又不能很好擷取DB資源,是以會優先考慮記憶體型資料庫。記憶體資料庫一方面能滿足應用對DB的需求,另一方面依賴資源(CPU和記憶體)簡單,能及時打掃清理現場,認可度很高。
2. 有哪些常見的記憶體資料庫
DB類型 | 優點 | 缺點 | 其它 |
sqlite | 1. C語言開源,輕量級,通過jar方式引用; 2. 嵌入式模式運作,支援Java/go/python/php等多語言; 3. 嵌入式運作支援記憶體和磁盤模式,存儲檔案可以跨平台使用; 4. 支援實物隔離和索引; | 1. 多線程并發讀寫能力弱; 2. Mysql相容性較弱; 3. 無使用者管理; | 官網位址:SQLite Home Page |
H2 | 1. 純Java編寫,通過Jar方式引用; 2.支援client/server多線程模式; 3. 支援記憶體和磁盤存儲資料; 4.支援索引和事務隔離,支援全文索引; 5. 相比于Sqlite對mysql文法支援更多; 6. 相同資料量級記憶體模式比較性能最好; | 與SQLite類似 | 官網位址:H2 Database Engine |
derby | 1. 開源Java語言編寫,核心部分derby.jar隻有2M; 2. 支援主從模式,支援授權使用者; | 1. derby 對很多 mysql 的關鍵字并不支援,同時 derby 不支援插入空值; 2. 隻支援Java語言; 3. 不支援記憶體模式和全文本搜尋; | 官網位址:Apache Derby |
mariaDb4j | 1. 号稱能相容MySQL的記憶體資料庫; 2. 支援sql 索引; | 1. 運作會依賴外部os的動态lib; | github:MariaDB4j/mariaDB4j-pom-lite at master · vorburger/MariaDB4j · GitHub |
embeded mysql | 1. 支援client/server模式; 2. 号稱對mysql文法相容性最強; | 1. 隻支援記憶體模式; 2. 5.8以後就被移除了,開源維護性較差; 3. 支援資料量級和性能受限,10萬資料量級可能有問題; | github: GitHub - wix/wix-embedded-mysql: embedded mysql based on https://github.com/flapdoodle-oss/de.flapdoodle.embed.process |
3. 如何選擇
進考慮單機嵌入式情況,推薦使用sqlite,具體原因如上;
考慮事務支援以及記憶體情況下運作效率,選擇H2;
如果考慮對MySQL文法支援,優先選擇使用mariaDb4j,LZ親測對sql文法相容性強;
4. 示例代碼
為了屏蔽底層DB引擎的差異性以及對查詢的多線程使用,樓主選用了Druid作為資料庫連接配接池,DAO層直接用Spring JDBC做封裝。
公共依賴包:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
抽象公共的RbdUtil:
package com.book.xw.common.dal;
import com.alibaba.druid.pool.DruidDataSource;
import lombok.Data;
import org.springframework.jdbc.core.JdbcTemplate;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
public abstract class RdbDaoUtil {
protected static String DB_LOCAL_PATH = "./mydb";
private JdbcTemplate jdbcTemplate;
private DruidDataSource dataSource;
protected String dbName;
private volatile Integer dataSourceStatus = 1;
public RdbDaoUtil(String dbName) {
this.dbName = dbName;
this.dataSource = buildRdbDataSource(dbName);
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
protected abstract DruidDataSource buildRdbDataSource(String ... args);
// 保障不同資料庫名字對應的執行個體隻有一個
protected DruidDataSource buildDataSource(String dbUrl, String user, String pwd, String driverClass){
synchronized (dbName){
if(dataSourceStatus == 1){
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(dbUrl);
dataSource.setUsername(user);
dataSource.setPassword(pwd);
dataSource.setInitialSize(1);
dataSource.setMinIdle(1);
dataSource.setMaxWait(30000);
dataSource.setMinEvictableIdleTimeMillis(30000);
dataSource.setTestWhileIdle(true);
dataSource.setValidationQuery("select 1");
dataSource.setTestOnBorrow(true);
dataSource.setTestOnReturn(false);
dataSource.setLogAbandoned(true);
try {
dataSource.init();
}catch (Exception e){
throw new RuntimeException(e);
}
}else{
return this.dataSource;
}
}
return dataSource;
}
public void destroyDataSource(){
if(this.dataSource != null && !dataSource.isClosed()){
this.dataSource.close();
dataSourceStatus = 1;
}
}
public void createTable(String table, List<ColumnType> columns ){
String sql = "";
jdbcTemplate.execute(sql);
}
public void deleteTable(String table){
String sql = "delete table if exists ` "+table+"` ;";
jdbcTemplate.update(sql);
}
public void executeSql(String sql){
jdbcTemplate.execute(sql);
}
public List<Map<String, Object>> queryForMap(String sql){
return jdbcTemplate.queryForList(sql);
}
public MyData queryDbData(String sql, boolean needColName){
MyData data = new MyData();
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = this.dataSource.getConnection();
conn.setAutoCommit(false);
stmt = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery();
if(needColName){
getColNameAndType(rs, data);
}
int row = 0;
while (rs.next()){
getColData(rs, data);
row++;
}
data.setRows(row);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
try {
if(rs != null){
rs.close();
}
if(stmt != null){
stmt.close();
}
if(conn != null){
conn.setAutoCommit(true);
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return data;
}
private void getColData(ResultSet rs, MyData data){
int size = data.getColNames().size();
if(rs != null){
List<Object> list = new ArrayList<>(size);
try {
for(int i = 0; i< size; i++){
list.add(rs.getObject(i + 1));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
data.getColData().add(list);
}
}
private void getColNameAndType(ResultSet rs, MyData data){
List<String> names = new ArrayList<>();
List<Integer> types = new ArrayList<>();
if(rs != null){
try {
ResultSetMetaData rms = rs.getMetaData();
for(int i = 1; i<=rms.getColumnCount(); i++){
types.add(rms.getColumnType(i));
names.add(rms.getColumnLabel(i));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
data.setColNames(names);
data.setColTypes(types);
}
@Data
public class ColumnType{
// 字段名字
private String name;
// 字段類型
private String type;
// 限制條件,主鍵、普通索引
private String constraint;
}
@Data
public class MyData{
private List<String> colNames;
private List<Integer> colTypes;
private List<List<Object>> colData = new ArrayList<>();
private int rows ;
}
}
Embedded Msql需要引入的包為:
<dependency>
<groupId>com.wix</groupId>
<artifactId>wix-embedded-mysql</artifactId>
<version>4.6.2</version>
</dependency>
引擎代碼:
import com.alibaba.druid.pool.DruidDataSource;
import com.book.xw.common.dal.RdbDaoUtil;
import com.wix.mysql.EmbeddedMysql;
import com.wix.mysql.config.Charset;
import com.wix.mysql.config.MysqldConfig;
import com.wix.mysql.distribution.Version;
import lombok.SneakyThrows;
import java.net.ServerSocket;
import java.util.TimeZone;
public class EmbeddedMysqlUtil extends RdbDaoUtil {
private EmbeddedMysql embeddedMysql;
public EmbeddedMysqlUtil(String dbName) {
super(dbName);
}
@Override
protected DruidDataSource buildRdbDataSource(String... args) {
MysqldConfig config = mysqldConfig();
embeddedMysql = EmbeddedMysql
.anEmbeddedMysql(mysqldConfig())
.addSchema(dbName)
.start();
String url = "jdbc:mysql://localhost:"+config.getPort()+"/"+dbName+"?useUnicode=true&characterEncoding=UTF-8";
String driverClass = "com.mysql.jdbc.driver";
return buildDataSource(url, mysqldConfig().getUsername(), mysqldConfig().getPassword(), driverClass);
}
@Override
public void destroyDataSource() {
super.destroyDataSource();
if(embeddedMysql != null){
embeddedMysql.stop();
}
}
private MysqldConfig mysqldConfig(){
return MysqldConfig.aMysqldConfig(Version.v5_7_latest)
.withCharset(Charset.UTF8)
.withPort(randomPort())
.withTimeZone(TimeZone.getDefault())
.withTempDir(DB_LOCAL_PATH)
.build();
}
@SneakyThrows
private int randomPort(){
try(ServerSocket serverSocket = new ServerSocket(0)){
return serverSocket.getLocalPort();
}
}
}
MariaDB4j引擎jar:
// 核心包
<dependency>
<groupId>ch.vorburger.mariaDB4j</groupId>
<artifactId>mariaDB4j-core</artifactId>
<version>2.4.0</version>
</dependency>
// linux os依賴包
<dependency>
<groupId>ch.vorburger.mariaDB4j</groupId>
<artifactId>mariaDB4j-db-linux64</artifactId>
<version>10.2.11</version>
</dependency>
// mac os 依賴包
<dependency>
<groupId>ch.vorburger.mariaDB4j</groupId>
<artifactId>mariaDB4j-db-mac64</artifactId>
<version>10.2.11</version>
</dependency>
引擎代碼:
import ch.vorburger.mariadb4j.DB;
import ch.vorburger.mariadb4j.DBConfigurationBuilder;
import com.alibaba.druid.pool.DruidDataSource;
import com.book.xw.common.dal.RdbDaoUtil;
import lombok.SneakyThrows;
public class MariaDb4jUtil extends RdbDaoUtil {
private DB mariaDb;
public MariaDb4jUtil(String dbName) {
super(dbName);
}
@SneakyThrows
@Override
protected DruidDataSource buildRdbDataSource(String... args) {
buildDb();
mariaDb.createDB(dbName);
String url = "jdbc:mysql://localhost:"+mariaDb.getConfiguration().getPort()+"/"+dbName+"?useUnicode=true&characterEncoding=UTF-8";
String driverClass = "com.mysql.jdbc.driver";
return buildDataSource(url, "root", "", driverClass);
}
@SneakyThrows
private void buildDb(){
DBConfigurationBuilder builder = DBConfigurationBuilder.newBuilder();
// 0 -> auto detect free port
builder.setPort(0);
builder.setBaseDir(DB_LOCAL_PATH);
mariaDb = DB.newEmbeddedDB(builder.build());
mariaDb.start();
}
@SneakyThrows
@Override
public void destroyDataSource() {
super.destroyDataSource();
if(mariaDb != null){
mariaDb.stop();
}
}
}