java操作sqlite数据库工具代码及jar包

package com.decode.util; import java.sql.Blob; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class SQLiteUtil { public static final String TAG = "SQLiteUtil"; static{ try { Class.forName("org.sqlite.JDBC"); } catch (ClassNotFoundException e) { e.printStackTrace(); Log.D(TAG + " Load sqlite JDBC failed. " + e.getMessage()); } } /** * @param dbPath the database absolute path like 'C:\Test\xxx.db' if it not exist, will create a new database * */ public static Connection openSQLiteDB(String dbPath) { if(dbPath == null) { return null; } String str1 = dbPath.substring(0, 1).toLowerCase(); String str2 = dbPath.substring(1, dbPath.length()); dbPath = str1 + str2; String sql = "jdbc:sqlite://" + dbPath.replace("\\", "/"); Log.D(TAG + " (openSQLiteDB) open path:" + sql); try { Connection con = DriverManager.getConnection(sql); return con; } catch (SQLException e) { Log.D(TAG + " (openSQLiteDB) open db faied: " + e.getMessage()); return null; } } public static void closeSQLiteDB(Connection con) { if(con == null) { return; } try { con.close(); } catch (SQLException e) { Log.D(TAG + " (openSQLiteDB) close db faied: " + e.getMessage()); } } public static boolean exeSQL(Connection con, String sql) { if(con == null || sql == null) { return false; } Statement stmt = null; boolean ret = false; try { stmt = con.createStatement(); ret = stmt.execute(sql); } catch (SQLException e) { e.printStackTrace(); } finally { if(stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } return ret; } public static boolean createTable(Connection con, String sql) { if(con == null || sql == null) { return false; } Statement stmt = null; boolean ret = false; try { stmt = con.createStatement(); stmt.executeUpdate(sql); ret = true; } catch (SQLException e) { e.printStackTrace(); } finally { if(stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } return ret; } public static boolean insert(Connection con, String table, String[] values) { String sql = "insert into " + table + " values("; int lastParam = values.length - 1; for(int i=0; i < values.length; i++) { String value = values[i]; if(i == lastParam) { if(value == null || value.isEmpty()) { sql += "null);"; } else { sql += ("'"+ value + "');"); } } else { if(value == null || value.isEmpty()) { sql += "null,"; } else { sql += ("'" + value + "',"); } } } Log.D(TAG + " (insert) sql:" + sql); Statement stmt = null; boolean ret = false; try { stmt = con.createStatement(); stmt.executeUpdate(sql); ret = true; } catch (SQLException e) { e.printStackTrace(); } finally { if(stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } return ret; } public static boolean insert2(Connection con, String table, String[] params, Object[] values) { boolean ret = false; int paramsLength = params.length; if(paramsLength < values.length) { Log.D(TAG + " (insert2) values is little params."); return ret; } StringBuilder tmp = new StringBuilder("insert into " + table + "("); StringBuffer tmp2 = new StringBuffer(" values("); int lastParam = paramsLength - 1; for(int i = 0; i < paramsLength; i++) { if(i == lastParam) { tmp.append(params[i] + ")"); tmp2.append("?);"); } else { tmp.append(params[i] + ","); tmp2.append("?,"); } } String sql = tmp.append(tmp2).toString(); Log.D(TAG + " (insert2) sql : " + sql); try { PreparedStatement pstmt = con.prepareStatement(sql); for(int j = 1; j <= values.length; j++) { Object value = values[j - 1]; if(value instanceof String) { pstmt.setString(j, (String)value); } else if(value instanceof Integer) { pstmt.setInt(j, (Integer)value); } else if(value instanceof Boolean) { pstmt.setBoolean(j, (Boolean)value); } else if(value instanceof Double) { pstmt.setDouble(j, (Double)value); } else if(value instanceof Long) { pstmt.setLong(j, (Long)value); } else if(value instanceof Blob) { pstmt.setBlob(j, (Blob)value); } else { pstmt.setObject(j, value); } } pstmt.executeUpdate(); ret = true; } catch (SQLException e) { e.printStackTrace(); } return ret; } public static ResultSet selecte(Connection con, String sql) { if(con == null || sql == null) { return null; } Statement stmt = null; ResultSet ret = null; try { stmt = con.createStatement(); ret = stmt.executeQuery(sql); } catch (SQLException e) { // e.printStackTrace(); Log.D(TAG + " select happen error: " + e.getMessage()); } return ret; } public static boolean delete(Connection con, String table, String key, String keyValue) { Statement stmt = null; String sql = "delete from " + table + " where " + key + "='" + keyValue + "';"; boolean ret = false; Log.D(TAG + " (delete) sql:" + sql); try { stmt = con.createStatement(); stmt.executeUpdate(sql); ret = true; } catch (SQLException e) { e.printStackTrace(); } finally { if(stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } return ret; } private static void rollback(Connection con) { try { con.rollback(); } catch (SQLException e) { e.printStackTrace(); } } }