java应用中使用轻量的Sqlite

java应用中使用轻量的Sqlite

使用场景

在有些java项目要使用数据库存储数据，但是并不是对数据库要求很高时，
可以选择使用本地化的sqlite。

使用方式

在java应用中初始化时创建数据库db，生成比如data.db文件，在执行建表，
建索引等操作。
下面就可以正常使用增删改查了。

代码依赖

	<dependency>
        <groupId>org.xerial</groupId>
        <artifactId>sqlite-jdbc</artifactId>
        <version>3.34.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-dbcp2</artifactId>
        <version>2.8.0</version>
    </dependency>

初始化基础增删改查代码

package com.icare.iot.db;

import com.icare.iot.dto.UserDataEntity;
import com.icare.iot.util.GsonUtil;
import com.sun.org.glassfish.gmbal.Description;
import org.apache.commons.dbcp2.BasicDataSource;
import org.jfree.data.json.impl.JSONValue;
import org.sqlite.date.FastDateFormat;

import javax.sql.DataSource;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

public class SqliteConfig {

//表基础字段
private static final String[] stableFields = {"id","userId","recMsg","createTime"};

private static final Map<Class,String[]> tbMapFields = new HashMap<>();
private static final Map<Class,String> tbMapName = new HashMap<>();

protected static BasicDataSource dataSource = null;
//db文件名
private static final String db_name ="iot.db";
private static final String TIME_FORMAT ="yyyy-MM-dd HH:mm:ss";

public static DataSource getDataSource() {
    synchronized (Thread.class) {
        if (null == dataSource) {
            dataSource = new BasicDataSource();
            dataSource.setUrl("jdbc:sqlite:"+db_name);
            dataSource.setDriverClassName("org.sqlite.JDBC");
        }
    }
    return dataSource;
}



//测试demo，
public static void main(String[] args) throws ParseException {
    initDB(UserDataEntity.class,new String[]{"userId","createTime","data1"});
    //插入数据
    UserDataEntity entity = new UserDataEntity();
    entity.setUserId(1);
    entity.setData1("data1");
    entity.setData2("data2");
    entity.setData3("data3");
    entity.setData4("data3");
    entity.setData5("data3");
    entity.setData6("data3");
    entity.setData7("data3");
    entity.setRecMsg("2342323");
    insert(entity);
    List<UserDataEntity> list = queryList(UserDataEntity.class, "select * from tb_user_data");
    System.out.println(GsonUtil.toJson(list));
    }
    
//查询指定userId的最近几条数据
 public static List<UserDataEntity> queryLastData(Integer userId,int size){
        String sql = "select * from tb_user_data where userId = "+userId+" order by createTime desc limit 0,"+size;
        List<UserDataEntity> list = queryList(UserDataEntity.class, sql);
        return list;
    }


public static void update(String sql) {
    DataSource data = getDataSource();
    Connection conn = null;
    try {
        conn = data.getConnection();
        PreparedStatement ps = conn.prepare