Sqlite常用操作

1.sqliteutil工具

package com.dist.common.utils;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.RandomAccessFile;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import java.sql.Statement;

import com.dist.common.log.LoggerProxy;

public class SqliteUtil {
    private final static int REPLACELENGTH = 100;

    private Connection conn = null;
    private Statement stat = null;
    public SqliteUtil(String dbPath) {
        if(conn!=null) {
            return ;
        }
        //Connection conn=null;
        try {
            Class.forName("org.sqlite.JDBC");
            conn=DriverManager.getConnection("jdbc:sqlite:"+dbPath);
            conn.setAutoCommit(false);
            stat = conn.createStatement();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    /***
     * 打开connection连接
     * @throws Exception 
     */
    public Connection getConnection(String dbPath)  {
        if(conn!=null) {
            return conn;
        }
        //Connection conn=null;
        try {
            Class.forName("org.sqlite.JDBC");
            conn=DriverManager.getConnection("jdbc:sqlite:"+dbPath);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }
    
    /***
     * 关闭connection连接
     * @throws Exception 
     */
    public  void closeConnection() {
        try {
        if(conn!=null) {
                stat.close();
                conn.close();
        }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    public void commit() {
        if(conn ==null) {
            return ;
        }
        
        try {
            conn.commit();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    public void rollBack() {
        if(conn ==null) {
            return ;
        }
        try {
            conn.rollback();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    /***
     * 执行sql语句
     * @throws SQLException 
     * @throws Exception 
     */
    private ResultSet selectDbData(String sql) throws SQLException{
        if(conn ==null) {
            return null;
        }
        PreparedStatement statement = null;
        ResultSet result = null;
        statement = conn.prepareStatement(sql);
        result = statement.executeQuery();
        statement.close();
        return result;
    }
    
    /***
     * 更新sql语句
     * @throws Exception 
     */
    public void updateDbData(String sql) {
        if(conn ==null) {
            return ;
        }
        try {
            stat.executeUpdate(sql);
            conn.commit();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    /***
     * 更新/插入sql语句
     * @throws Exception 
     *  Map<String, Object> param = new HashMap<String, Object>();
     *  map.put("0", "Jeff");
     *  map.put("1", Integer.MAX_VALUE);
     *  map.put("2", Long.MAX_VALUE);
     */
    public void instDbData(String sql,Map<String,Object> param ) throws SQLException {
        if(conn ==null) {
            return ;
        }
        
        if(conn ==null) {
            return ;
        }
        PreparedStatement statement = conn.prepareStatement(sql);
        
        
        param.forEach((key,value) ->{
            try {
            int parameterIndex =0;
            parameterIndex =Integer.parseInt(key);
            if (value instanceof String) {
                    statement.setString(parameterIndex, (String) value);
            } else if (value instanceof Integer) {
                statement.setInt(parameterIndex, (int) value);
            } else if (value instanceof Long) {
                statement.setLong(parameterIndex, (long) value);
            } else if (value instanceof Float) {
                statement.setFloat(parameterIndex, (float) value);
            } else if (value instanceof Double) {
                statement.setDouble(parameterIndex,  (double) value);
            } else if (value instanceof File) {
                FileInputStream fis  = new FileInputStream((File)value);
                statement.setBinaryStream(8,fis,((File)value).length()); 
            }
            
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        });
        
        statement.executeUpdate();
        conn.commit();
        statement.close();
    }

}

2.使用

String targetDbFilName = targetFilePath + File.separator + "zjgg.db";
SqliteUtil sqliteUtil = new SqliteUtil(targetDbFilName);
String sqlStr = "insert into ZJGG_XMJBQK(XMBH  ,  XMMC ,  PZWH ,  PZJG ,  SZSSX  ,  PZSJ ,  XMJHJSSJ,  ZZSSDW ,  ZZZBND )  values(?,?,?,?,?,?,?,?,?)";

Map<String, Object> param = new HashMap<String, Object>();
        param.put("1", xmjbqk.getXmbh() == null ? "" : xmjbqk.getXmbh().toString());
        param.put("2", xmjbqk.getXmmc() == null ? "" : xmjbqk.getXmmc().toString());
        param.put("3", xmjbqk.getPzwh() == null ? "" : xmjbqk.getPzwh().toString());
        param.put("4", xmjbqk.getPzjg() == null ? "" : xmjbqk.getPzjg().toString());
param.put("5", xmjbqk.getSzssx() == null ? "" : xmjbqk.getSzssx().toString());
        param.put("6", xmjbqk.getPzsj() == null ? "" : xmjbqk.getPzsj().toString());
        param.put("7", xmjbqk.getXmjhjssj() == null ? "" : xmjbqk.getXmjhjssj().toString());
        param.put("8", xmjbqk.getZzssdw() == null ? "" : xmjbqk.getZzssdw().toString());
        param.put("9", xmjbqk.getZzzbnd() == null ? "" : xmjbqk.getZzzbnd().toString());
        sqliteUtil.instDbData(sqlStr, param);
        sqliteUtil.closeConnection();