Spring之十整合Sqlite

最新推荐文章于 2024-06-03 07:02:28 发布

icanhaha

最新推荐文章于 2024-06-03 07:02:28 发布

阅读量9.3k

点赞数

分类专栏： Spring

本文链接：https://blog.csdn.net/icanhaha/article/details/51016477

版权

Spring 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

1.加入jar包

sqlite-jdbc-3.8.11.2.jar

2.applicationContext.xml配置文件中配置

<!-- sqlite内存数据库连接池-->  
    <bean id="sqliteDataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close">  
        <property name="poolProperties">  
            <bean class="org.apache.tomcat.jdbc.pool.PoolProperties">  
                <property name="driverClassName" value="org.sqlite.JDBC"/>  
                <property name="url" value="jdbc:sqlite:hello.db"/>  
                <property name="minIdle" value="10"/>  
                <property name="maxActive" value="100"/>  
                <!-- 数据库连接池配置 -->  
                <!-- 初始化连接数量 -->  
                <property name="initialSize" value="50"/>  
                <!-- 最大连接数量 -->  
                <!-- 最小空闲连接数量 -->  
                <property name="removeAbandoned" value="true" />  
                <property name="removeAbandonedTimeout" value="600" />  
                <!-- 验证连接是否有效 -->  
                <property name="validationQuery" value="select 1" />  
                <!-- 验证失败时，是否将连接从池中丢弃 -->  
                <property name="testWhileIdle" value="true" />  
                <!-- 把空闲时间超过minEvictableIdleTimeMillis毫秒的连接断开,直到连接池中的连接数到minIdle为止(毫秒，30分钟) -->  
                <property name="timeBetweenEvictionRunsMillis" value="1200000" />  
                <!-- 连接池中连接可空闲的时间（毫秒，5分钟） -->  
                <property name="minEvictableIdleTimeMillis" value="1800000" />  
                <!-- 在每次空闲连接回收器线程(如果有)运行时检查的连接数量 -->  
                <property name="numTestsPerEvictionRun" value="5" />  
            </bean>  
        </property>  
    </bean>

3.封装SqliteUtil对象

@Repository  
public class SqliteUtil {  
  
    private Logger logger = Logger.getLogger(this.getClass());  
    /**  
     * 数据源  
     */  
    @Resource(name = "sqliteDataSource")  
    private DataSource sqliteDataSource;  
  
    /**  
     * 获取数据库连接  
     * @return conn  
     */  
    public Connection getConnection() throws SQLException {  
        Connection conn = sqliteDataSource.getConnection();  
        conn.setAutoCommit(false);  
        return conn;  
    }  
  
    /**  
     * 关闭数据库连接  
     * @param conn  
     */  
    public void close(Connection conn, Statement stmt, ResultSet rs) {  
        if (null != rs) {  
            try {  
                rs.close();  
            } catch (SQLException ex) {  
                logger.error(null, ex);  
            }  
            rs = null;  
        }  
        if (null != stmt) {  
            try {  
                stmt.close();  
            } catch (SQLException ex) {  
                logger.error(null, ex);  
            }  
            stmt = null;  
        }  
        if (null != rs) {  
            try {  
                rs.close();  
            } catch (SQLException ex) {  
                logger.error(null, ex);  
            }  
            rs = null;  
        }  
    }  
}

4.封装Dao层

@Repository(value = "realtimeDao")  
public class RealtimeDaoImpl implements IRealtimeDao {  
  
    @Resource  
    private SqliteUtil sqliteUtil;  
  
    @Override  
    public void initRealtime() throws Exception {  
        Map<String, List<String>> tables = getFieldNamesByClass(Realtime.class);  
        Connection conn = sqliteUtil.getConnection();  
        Statement stmt = conn.createStatement();  
        StringBuilder sb = new StringBuilder();  
        for (String table : tables.keySet()) {  
            sb.append("CREATE TABLE IF NOT EXISTS ");  
            sb.append("'").append(table).append("'");  
            sb.append("(");  
            for (String column : tables.get(table)) {  
                sb.append(column).append(" string");  
                if (column.contains("termSn")) {  
                    sb.append(" PRIMARY KEY UNIQUE");  
                }  
                sb.append(",");  
            }  
            sb.delete(sb.lastIndexOf(","), sb.lastIndexOf(",") + 1);  
            sb.append(");");  
            stmt.addBatch(sb.toString());  
            sb.delete(0, sb.length());  
        }  
        stmt.executeBatch();  
        conn.commit();  
        sqliteUtil.close(conn, stmt, null);  
    }  
  
    @Override  
    public void saveObject(Object obj) throws Exception {  
        Map<String, List<String>> tablesMap = getFieldNamesByClass(Realtime.class);  
        Map<String, String> paramMap = getFieldValuesByClass(obj);  
        StringBuilder sql = new StringBuilder();  
        StringBuilder value = new StringBuilder();  
        Connection conn = sqliteUtil.getConnection();  
        Statement stmt = conn.createStatement();  
        int indexFlag;  
        //组装每个table的字段  
        for (String table : tablesMap.keySet()) {  
            String sqlStr = this.buildSaveSql(table, tablesMap, paramMap, sql, value);  
            stmt.addBatch(sqlStr);  
            sql.delete(0, sql.length());  
            value.delete(0, value.length());  
        }  
        stmt.executeBatch();  
        conn.commit();  
        sqliteUtil.close(conn, stmt, null);  
    }  
  
    @Override  
    public void saveObjectList(List<?> objList) throws Exception {  
        int listSize = objList.size() - 1;  
        int batchFlag = 0;  
        Map<String, List<String>> tablesMap = getFieldNamesByClass(objList.get(0).getClass());  
        Map<String, String> paramMap;  
        List<String> sqlList = new ArrayList<String>();  
        StringBuilder sql = new StringBuilder();  
        StringBuilder value = new StringBuilder();  
  
        Connection conn = sqliteUtil.getConnection();  
        Statement stmt = conn.createStatement();  
        for (int i = 0; i <= listSize; i++) {  
            Object obj = objList.remove(0);  
            paramMap = getFieldValuesByClass(obj);  
            //组装每个table的字段  
            for (String table : tablesMap.keySet()) {  
                String sqlStr = this.buildSaveSql(table, tablesMap, paramMap, sql, value);  
                stmt.addBatch(sqlStr);  
                sql.delete(0, sql.length());  
                value.delete(0, value.length());  
                if (batchFlag == 5000 || i == listSize) {  
                    stmt.executeBatch();  
                    batchFlag++;  
                }  
            }  
        }  
        conn.commit();  
        sqliteUtil.close(conn, stmt, null);  
    }  
  
    @Override  
    public Map<String, String> findOneObject(String termSn, Class clazz) throws Exception {  
        Map<String, List<String>> tablesMap = getFieldNamesByClass(clazz);  
        StringBuilder sql = new StringBuilder();  
        StringBuilder field = new StringBuilder();  
        int tableSize = tablesMap.keySet().size();  
  
        int index = 0;  
        for (String table : tablesMap.keySet()) {  
            if (index != 0) {  
                sql.append(" LEFT JOIN ").append("'").append(table).append("' t").append(index).append(" ON ").append(" t0.termSn=t").append(index).append(".termSn");  
            } else {  
                sql.append(" FROM ").append("'").append(table).append("' t").append(index);  
            }  
            field.append(" t").append(index).append(".*").append(",");  
            index++;  
        }  
        sql.append(" WHERE t0.termSn='").append(termSn).append("'");//.append("' LIMIT 0,1");  
        field.delete(field.lastIndexOf(","), field.lastIndexOf(",") + 1);  
        sql.insert(0, field);  
        sql.insert(0, "SELECT ");  
        field = null;  
  
        Connection conn = sqliteUtil.getConnection();  
        Statement stmt = conn.createStatement();  
        ResultSet rs = stmt.executeQuery(sql.toString());  
        Map<String, String> valueMap = new HashMap<String, String>();  
        if (rs.next()) {  
            ResultSetMetaData data = rs.getMetaData();  
            int columnSize = data.getColumnCount() - 1;  
            for (int i = 1; i <= columnSize; i++) {  
                valueMap.put(data.getColumnName(i), rs.getString(data.getColumnName(i)));  
            }  
        }  
        return valueMap;  
    }  
  
    /**  
     * 组装多个表的sql  
     */  
    private String buildSaveSql(String table, Map<String, List<String>> tablesMap, Map<String, String> paramMap,  
            StringBuilder sql, StringBuilder values) {  
        sql.append("REPLACE INTO ");  
        sql.append("'").append(table).append("'");  
        sql.append("(");  
        values.append(" VALUES(");  
        //组装字段值  
        for (String column : tablesMap.get(table)) {  
            if (paramMap.containsKey(column)) {  
                sql.append(column).append(",");  
                values.append("'").append(paramMap.get(column)).append("',");  
            }  
        }  
        int indexFlag = values.lastIndexOf(",");  
        values.delete(indexFlag, indexFlag + 1);  
        values.append(")");  
  
        indexFlag = sql.lastIndexOf(",");  
        sql.delete(indexFlag, indexFlag + 1);  
        sql.append(")").append(values).append(";");  
        return sql.toString();  
    }  
  
    /**  
     * 根据一个类，获取当前类及子类的字段列表  
     * @param clazz  
     * @return  
     * @throws Exception   
     */  
    private Map<String, List<String>> getFieldNamesByClass(Class clazz) throws Exception {  
        Map<String, List<String>> paramMap = new HashMap<String, List<String>>();  
        String className = clazz.getName();  
        paramMap.put(className, new ArrayList<String>());  
  
        Field[] fs = clazz.getDeclaredFields();  
        List<Field> list = new ArrayList<Field>();  
        list.addAll(Arrays.asList(fs));  
        for (Field field : list) {  
            Class classes = field.getType();  
            if (!classes.getName().contains("com.ithings.")) {  
                paramMap.get(className).add(field.getName());  
            } else {  
                //自定义类型  
                Map<String, List<String>> childMap = getFieldNamesByClass(classes);  
                paramMap.putAll(childMap);  
            }  
        }  
        return paramMap;  
    }  
  
    /**  
     * 获取一个对象及子对象的字段值(属性名/属性值)  
     */  
    private Map<String, String> getFieldValuesByClass(Object obj) throws Exception {  
        Map<String, String> paramMap = new HashMap<String, String>();  
        String className = obj.getClass().getName();  
  
        Field[] fs = obj.getClass().getDeclaredFields();  
        List<Field> list = new ArrayList<Field>();  
        list.addAll(Arrays.asList(fs));  
        for (Field field : list) {  
            field.setAccessible(true);  
            Object fdValue = field.get(obj);  
            if (null == fdValue) {  
                continue;  
            }  
            Class classes = field.getType();  
            if (!classes.getName().contains("com.ithings.")) {  
                paramMap.put(field.getName(), fdValue.toString());  
            } else {  
                //自定义类型  
                Map<String, String> childMap = getFieldValuesByClass(fdValue);  
                paramMap.putAll(childMap);  
            }  
        }  
        return paramMap;  
    }  
}

注： Sqlite教程