Spring之十 整合Sqlite

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教程