java 鏈接 sqlite數據庫

本次采用的sqlite的包是sqlite-jdbc-3.6.0.jar

package com.***.app.mappcore.impl.util;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ygsoft.ecp.app.mappcore.impl.ext.config.BaseConfig;
import com.ygsoft.ecp.service.log.EcpLogFactory;
import com.ygsoft.ecp.service.log.IEcpLog;
import com.ygsoft.ecp.service.tool.StringUtil;

public class SQLiteUtil {
    /**
     * 日志
     */
    private static final IEcpLog LOG = EcpLogFactory.getLog(SQLiteUtil.class);
    
    public SQLiteUtil(){
        
    }
    /**
     * 內部
     * 內部懶加載類.<br>
     * @author mapengfei <br>
     * @version 1.0.0 2016年5月31日<br>
     * @see
     * @since JDK 1.5.0
     */
    private static class  LazyHolder {
        private static final SQLiteUtil INSTANCE = new SQLiteUtil();
    }
    /**
     * 單例新實現
     * @return
     */
    public static final SQLiteUtil getInstance(){
        return LazyHolder.INSTANCE;
    }

    public static void main(final String[] args) {
        // try {

        // Class.forName("org.sqlite.JDBC");
        // Connection conn =
        // DriverManager.getConnection("jdbc:sqlite:abc.db");
        // 初始化線程池
        // ConnectionPool pool = initPool();
        // // 從連接池中取得鏈接
        // Connection conn = pool.getCurrentConnecton();
        // Statement stat = conn.createStatement();

        // stat.executeUpdate("create table tbl1(name varchar(20), salary
        // int);");// ?
        // stat.executeUpdate("insert into tbl1 values('ZhangSan',8000);");
        // //
        // stat.executeUpdate("insert into tbl1 values('LiSi',7800);");
        // stat.executeUpdate("insert into tbl1 values('WangWu',5800);");
        // stat.executeUpdate("insert into tbl1 values('我',9100);");

        // ResultSet rs = stat.executeQuery("select * from tbl1;"); //
        // 初始化數據
        SQLiteUtil util = new SQLiteUtil();
        //util.initParametersTable();
        for(int i=0;i<10;i++){
            List<ParametesVO> list =util.findAll(true);
            System.out.println(list.size()+"******************"+i);
        }
//        List<String> sqls =BaseConfig.getSQL();
//        for(String sql: sqls){
//            
//            System.out.println(sql);
//        }
//        String rs = util.findParamValueByName("FTPPUFFERSIZE","FTP");
//        System.out.println(rs);
//        List<ParametesVO> list = util.findAll();
//        System.out.println(list.get(0).getNo());

//        ParametesVO model = new ParametesVO();
//        model.setNo("100");
//        model.setParamName("test1");
//        model.setParamValue("002");
//        model.setParamDescription("描述");
//        model.setParamType("MPF");
//        model.setParamStatus("0");
//        add(model); // 添加
//        String t = findByName("test1");
//        System.out.println("add新添加的記錄查詢結果是:" + t);
        
//        updateByNO("001",model);
//        String t1 = findByName("FTPPUFFERSIZE");
//        System.out.println("updateByNO修改的記錄查詢結果是:" + t1);
//        
//        util.delByNO("002");
//        List<ParametesVO> list1 = util.findAll();
//        System.out.println(list1.size());
        // while (rs.next()) { //
        //
        // System.out.print("name = " + rs.getString("name") + " "); //
        //
        // System.out.println("salary = " + rs.getString("salary")); //
        //
        // }
        // rs.close();
        // closeConnection(conn); //

        // } catch (Exception e) {
        // e.printStackTrace();
        // }
    }
    /**
     * 刪除表
     * @param tableName
     */
    public void dropParametersTable(final String tableName){
        String sql =" DROP TABLE PARAMETERS_TABLE ";
        Connection conn=null;
        try {
            // 取得SQL查詢結果聲明
            conn = getCurrentConnecton();
            Statement stat = conn.createStatement();
            // 創建參數表
            stat.executeUpdate(sql);
            stat.close();
        } catch (SQLException e) {
            if(LOG.isDebugEnabled()){
                LOG.info(" 初始化參數表有誤。");
            }
            e.printStackTrace();
        }finally {
            closeConnection(conn);
        }
    }

    /**
     * 初始化參數表
     */
    public void initParametersTable() {
        final String initTableSql = " CREATE TABLE PARAMETERS_TABLE(NO VARCHAR(36), PARAMENAME VARCHAR(36),PARAMEVALUE VARCHAR(36),PARAMESTATUS VARCHAR(4),PARAMETYPE VARCHAR(12),PARAMEDESCRIPTION VARCHAR(2000))";
        Connection conn=null;
        try {
            // 取得SQL查詢結果聲明
            conn = getCurrentConnecton();
            Statement stat = conn.createStatement();
            // 創建參數表
            stat.executeUpdate(initTableSql);
            // 初始化參數表數據    
            List<String> sqls =BaseConfig.getSQL();
            if(sqls!=null){
                for(String sql : sqls){
                    if(StringUtil.isNotEmptyString(sql)){
                        stat.executeUpdate(sql);
                    }
                }
            }
            stat.close();
        } catch (SQLException e) {
            if(LOG.isDebugEnabled()){
                LOG.info(" 初始化參數表有誤。");
            }
            e.printStackTrace();
        }finally {
            closeConnection(conn);
        }
    }
    /**
     * 專供java內部調用
     * @param key 參數名
     * @param type 參數類型
     * @return 參數值
     */
    public static String get(final String key,final String type){
        return SQLiteUtil.getInstance().findParamValueByName(key,type);
        
    }

    /**
     * 根據參數名稱查詢參數值
     *
     * @param parameName
     *            參數名稱
     * @return 參數值
     */
    public  String findParamValueByName(final String parameName , final String paramType) {
        String parameValue = null;
        Connection conn=null;
        try {
            // 取得SQL查詢結果聲明
            conn = getCurrentConnecton();
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("SELECT * FROM PARAMETERS_TABLE WHERE PARAMENAME='" + parameName + "' AND PARAMETYPE='"+paramType+"'");
            while (rs.next()) { // 取得鏈接
                parameValue = rs.getString(3);
            }
            rs.close();
            stat.close();
        } catch (SQLException e) {
            if(LOG.isDebugEnabled()){
                LOG.info("獲取查詢配置結果有誤");
            }
            e.printStackTrace();
        }finally {
            closeConnection(conn);
        }
        return parameValue;
    }
    /**
     * 根據parameName查詢相應的配置參數信息
     * @param parameName 參數名稱
     * @return 配置參數VO
     */
    public List<ParametesVO> findByName(final String parameName){
        List<ParametesVO> list = new ArrayList<ParametesVO>();
        Connection conn=null;
        try {
            // 取得SQL查詢結果聲明
            conn = getCurrentConnecton();
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("SELECT * FROM PARAMETERS_TABLE WHERE PARAMENAME='" + parameName + "'");
            while (rs.next()) { // 取得鏈接
                ParametesVO vo = new ParametesVO();
                vo.setNo(rs.getString(1));
                vo.setParamName(rs.getString(2));
                vo.setParamValue(rs.getString(3));
                vo.setParamStatus(rs.getString(4));
                vo.setParamDescription(rs.getString(5));
                list.add(vo);
            }
            rs.close();
            stat.close();
        } catch (SQLException e) {
            if(LOG.isDebugEnabled()){
                LOG.info("findALL查詢配置參數列表有誤");
            }
            e.printStackTrace();
        }finally {
            closeConnection(conn);
        }
        return list;
        
    }
    /**
     * 根據parameType查詢系統配置參數信息
     * @param parameType
     * @return
     */
    public List<ParametesVO> findByParameteType(final String parameType){
        List<ParametesVO> list = new ArrayList<ParametesVO>();
        Connection conn=null;
        try {
            // 取得SQL查詢結果聲明
            conn = getCurrentConnecton();
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("select * from PARAMETERS_TABLE WHERE PARAMETYPE='" + parameType + "'");
            while (rs.next()) { // 取得鏈接
                ParametesVO vo = new ParametesVO();
                vo.setNo(rs.getString(1));
                vo.setParamName(rs.getString(2));
                vo.setParamValue(rs.getString(3));
                vo.setParamStatus(rs.getString(4));
                vo.setParamType(rs.getString(5));
                vo.setParamDescription(rs.getString(6));
                list.add(vo);
            }
            rs.close();
            stat.close();
        } catch (SQLException e) {
            if(LOG.isDebugEnabled()){
                LOG.info("findALL查詢配置參數列表有誤");
            }
            e.printStackTrace();
        }finally {
            closeConnection(conn);
        }
        return list;
        
    }

    /**
     * 根究NO修改
     *
     * @param NO
     * @param model
     */
    public void updateByNO(final String NO, final ParametesVO model) {
        // 取得SQL查詢結果聲明
        String update_sql = " UPDATE PARAMETERS_TABLE SET PARAMENAME = '" + model.getParamName() + "',PARAMEVALUE='"
                + model.getParamValue() + "',PARAMESTATUS='" + model.getParamStatus() + "',PARAMETYPE='"
                + model.getParamType() + "',PARAMEDESCRIPTION='" + model.getParamDescription() + "' WHERE NO = '" + NO
                + "'";
        if(LOG.isDebugEnabled()){
            LOG.info("updateByNO de sql:" + update_sql);
        }
        Connection conn = null;
        try {
            conn = getCurrentConnecton();
            Statement stat = conn.createStatement();
            stat.executeUpdate(update_sql);
            stat.close();
        } catch (SQLException e) {
            if(LOG.isDebugEnabled()){
                LOG.info("根據" + NO + "修改PARAMETERS_TABLE對應記錄的sql:" + update_sql);
            }
            e.printStackTrace();
        }finally {
            closeConnection(conn);
        }
    }

    /**
     * 添加配置信息
     *
     * @param model
     */
    public void add(final ParametesVO model) {
        Connection conn=null;
        try {
            conn = getCurrentConnecton();
            Statement stat = conn.createStatement();
            String add_sql = " INSERT INTO PARAMETERS_TABLE VALUES('" + model.getNo() + "','" + model.getParamName()
                    + "','" + model.getParamValue() + "','" + model.getParamStatus() + "','" + model.getParamType()
                    + "','" + model.getParamDescription() + "')";
            stat.executeUpdate(add_sql);
            if(LOG.isDebugEnabled()){
                LOG.info("添加配置信息的sql:" + add_sql);
            }
            stat.close();
        } catch (SQLException e) {
            if(LOG.isDebugEnabled()){
                LOG.info("添加時");
            }
            e.printStackTrace();
        }finally {
            closeConnection(conn);
        }
    }
    
    /**
     * 根據 NO查詢參數信息
     * @param NO
     * @return
     */
    public ParametesVO findByNO(final String NO){
        ParametesVO vo=null;
        Connection conn=null;
        try {
            // 取得SQL查詢結果聲明
            conn = getCurrentConnecton();
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(" SELECT * FROM PARAMETERS_TABLE WHERE NO = '" + NO + "'");
            while (rs.next()) { // 取得鏈接
                vo = new ParametesVO();
                vo.setNo(rs.getString(1));
                vo.setParamName(rs.getString(2));
                vo.setParamValue(rs.getString(3));
                vo.setParamStatus(rs.getString(4));
                vo.setParamType(rs.getString(5));
                vo.setParamDescription(rs.getString(6));
            }
            rs.close();
            stat.close();
        } catch (SQLException e) {
            if(LOG.isDebugEnabled()){
                LOG.info("findALL查詢配置參數列表有誤");
            }
            e.printStackTrace();
        }finally {
            closeConnection(conn);
        }
        return vo;
    }

    /**
     * 根據NO刪除PARAMETERS_TABLE表中的信息
     *
     * @param NO
     */
    public void delByNO(final String no) {
        Connection conn=null;
        try {
            conn = getCurrentConnecton();
            Statement stat = conn.createStatement();
            stat.executeUpdate("DELETE FROM PARAMETERS_TABLE WHERE NO = '" + no + "'");
            stat.close();
        } catch (SQLException e) {
            if(LOG.isDebugEnabled()){
                LOG.info("刪除失敗,NO:" + no);
            }
            e.printStackTrace();
        }finally {
            closeConnection(conn);
        }
    }
    
    /**
     * 根究NO修改
     *
     * @param NO
     * @param model
     */
    public void updateByNameAndType(final String paramname,final String paramtype, final ParametesVO model) {
        // 取得SQL查詢結果聲明
        String update_sql = " UPDATE PARAMETERS_TABLE SET PARAMENAME = '" + model.getParamName() + "',PARAMEVALUE='"
                + model.getParamValue() + "',PARAMESTATUS='" + model.getParamStatus() + "',PARAMETYPE='"
                + model.getParamType() + "',PARAMEDESCRIPTION='" + model.getParamDescription() + "' WHERE PARAMENAME = '" + paramname
                + "' and PARAMETYPE='" +paramtype + "'";
        if(LOG.isDebugEnabled()){
            LOG.info("updateByNameAndType de sql:" + update_sql);
        }
        try {
            Connection conn = getCurrentConnecton();
            Statement stat = conn.createStatement();
            stat.executeUpdate(update_sql);
            closeConnection(conn);
        } catch (SQLException e) {
            if(LOG.isDebugEnabled()){
                LOG.info("根據" + paramname+" 和 "+paramtype+ "修改PARAMETERS_TABLE對應記錄的sql:" + update_sql);
            }
            e.printStackTrace();
        }
    }
    
     /**
     * 根據name和type查詢參數信息
     * @param paramname
     * @param paramtype
     * @return
     */
    public ParametesVO findByNameAndType(final String paramname,final String paramtype){
        ParametesVO vo=null;
        Connection conn =null;
        try {
            // 取得SQL查詢結果聲明
            conn = getCurrentConnecton();
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery(" SELECT * FROM PARAMETERS_TABLE WHERE PARAMENAME = '" + paramname + "' and PARAMETYPE='" + paramtype + "'");
            while (rs.next()) { // 取得鏈接
                vo = new ParametesVO();
                vo.setNo(rs.getString(1));
                vo.setParamName(rs.getString(2));
                vo.setParamValue(rs.getString(3));
                vo.setParamStatus(rs.getString(4));
                vo.setParamType(rs.getString(5));
                vo.setParamDescription(rs.getString(6));
            }
            rs.close();
            stat.close();
        } catch (SQLException e) {
            if(LOG.isDebugEnabled()){
                LOG.info("findByNameAndType查詢配置參數列表有誤");
            }
            e.printStackTrace();
        }finally {
            closeConnection(conn);
        }
        return vo;
    }
    /**
     * 校驗數據庫文件是否存在
     * @return
     */
    private boolean isDbexists(){
        if(LOG.isDebugEnabled()){
            LOG.info("開始校驗數據庫文件是否存在");
        }
        File f = new File(BaseConfig.get("JdbcUrl"));
        if (f.exists()) {
            if(LOG.isDebugEnabled()){
                LOG.info("數據庫文件存在于"+BaseConfig.get("JdbcUrl"));
            }
            return true;
        }
        return false;
    }
    /**
     * 查詢前的邏輯處理
     */
    private void findBefor(){
        if(!isDbexists()){ // 不存在
            if(LOG.isDebugEnabled()){
                LOG.info("由于數據庫文件不存在，故初始化");
            }
            initParametersTable();
        }
    }

    /**
     * 查詢所有配置信息
     * @param isCheckDB 是否校驗數據庫文件是否存在
     * @return
     */
    public List<ParametesVO> findAll(final boolean isCheckDB) {
        if(isCheckDB){            
            findBefor();
        }
        List<ParametesVO> list = new ArrayList<ParametesVO>();
        Connection conn=null;
        try {
            // 取得SQL查詢結果聲明
            conn = getCurrentConnecton();
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("SELECT * FROM PARAMETERS_TABLE");
            while (rs.next()) { // 取得鏈接
                ParametesVO vo = new ParametesVO();
                vo.setNo(rs.getString(1));
                vo.setParamName(rs.getString(2));
                vo.setParamValue(rs.getString(3));
                vo.setParamStatus(rs.getString(4));
                vo.setParamType(rs.getString(5));
                vo.setParamDescription(rs.getString(6));
                list.add(vo);
            }
            rs.close();
            stat.close();
        } catch (SQLException e) {
            if(LOG.isDebugEnabled()){
                LOG.info("findALL查詢配置參數列表有誤");
            }
            e.printStackTrace();
        }finally {
            closeConnection(conn);
        }
        return list;

    }

    /**
     * 獲取數據庫鏈接
     *
     * @return
     */
    private static Connection getCurrentConnecton() {
        // 初始化連接池
        ConnectionPool pool = getPool();
        // 取得當前鏈接
        Connection conn = pool.getCurrentConnecton();
        if (conn != null) {
            return conn;
        }
        return null;
    }

    /**
     * 單例模式初始化一個連接池
     *
     * @return
     */
    public static ConnectionPool getPool() {
        return ConnectionPoolManager.getInstance().getPool("sqllitePool");
    }
    /**
     * 刪除一個連接池
     * @param conn
     */
    public static void closeConnection(final Connection conn){
        ConnectionPoolManager.getInstance().closeConnection("sqllitePool", conn);
    }
    /**
     * 清空連接池
     */
    public static void destroyPool(){
        ConnectionPoolManager.getInstance().destroy("sqllitePool");
    }

}

注：本類涉及到的一些數據庫鏈接池的問題或類可參考作者的相關文章