SqliteHelper

package com.mapgis.cloud.util;


import org.sqlite.SQLiteConfig;
import org.sqlite.SQLiteDataSource;
import org.sqlite.SQLiteOpenMode;


import java.io.File;
import java.sql.*;
import java.util.Objects;


public class SqliteStore {
private static String dbName = "virtualIGS.db";
   private static String dataDir = "data";
   private static int timeout = 10;
// private static int timeout = 1000;
static SQLiteConfig sqlConfig = new SQLiteConfig();


   private SqliteStore() {
dbName = dataDir + File.separator + dbName;
      try {
init();
} catch (Exception e) {
e.printStackTrace();
}
sqlConfig.setSynchronous(SQLiteConfig.SynchronousMode.OFF);
sqlConfig.setJournalMode(SQLiteConfig.JournalMode.WAL);
sqlConfig.setOpenMode(SQLiteOpenMode.READWRITE);
}


private static class DBStoreHolder {
static SqliteStore instance = new SqliteStore();
}


public static SqliteStore getInstance() {
return DBStoreHolder.instance;
}


public synchronized void init() {
File dbDir = new File(dataDir);
      if (!dbDir.exists()) {
dbDir.mkdirs();
}
try {
Class.forName("org.sqlite.JDBC");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
   }


public Connection getConnection() {
Connection conn = null;
      try {
conn = DriverManager.getConnection("jdbc:sqlite:" + dbName);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}


public static int execute(String sql) {
SqliteStore store = SqliteStore.getInstance();
Connection conn = store.getConnection();
      int ret = store.execute(sql, conn);
store.close(conn);
      return ret;
}
public static int getIntResult(String sql){
SqliteStore store = SqliteStore.getInstance();
Connection conn = store.getConnection();
      int ret =  store.getIntResult(sql,conn);
store.close(conn);
      return ret;
}




public int execute(String sql, Connection connection) {
Statement statement;
      int result = 0;
      try {
statement = connection.createStatement();
statement.setQueryTimeout(timeout);
result = statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}


/**
    * 执行SQL语句查询(手动关闭)
    *
    * @param sql
* @return
    */
public ResultSet query(String sql) {
ResultSet result = null;
      try {
SQLiteDataSource ds = new SQLiteDataSource(sqlConfig);
ds.setUrl("jdbc:sqlite:" + dbName);
Connection conn = ds.getConnection();
Statement statement = conn.createStatement();
statement.setQueryTimeout(timeout);
result = statement.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}


public ResultSet query(String sql, Connection connection) {
Statement statement;
ResultSet result = null;
      try {
statement = connection.createStatement();
statement.setQueryTimeout(timeout);
result = statement.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}


public Object getResult(String sql, Connection connection){
ResultSet set = query(sql,connection);
      try {
if(set.next()){
return set.getObject(1);
}
      } catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public int getIntResult(String sql, Connection connection){
ResultSet set = query(sql,connection);
      try {
if(set.next()){
return set.getInt(1);
}
      } catch (SQLException e) {
e.printStackTrace();
}
return 0;
}


public int delete(String sql, Connection connection) {
Statement statement;
      int result = 0;
      try {
statement = connection.createStatement();
statement.setQueryTimeout(timeout);
result = statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}


public void close(Connection conn) {
try {
if (conn != null && !conn.isClosed()) {
conn.close();
}
      } catch (SQLException e) {
e.printStackTrace();
}
   }


/**
    * 查询后关闭连接
*
    * @param rlt
*/
public void closeRltSet(ResultSet rlt) {
try {
if (rlt != null) {
rlt.close();
rlt.getStatement().close();
rlt.getStatement().getConnection().close();
}
      } catch (SQLException e) {
e.printStackTrace();
}
   }
}