在已經建好數據庫的前提下，用代碼演示如何使用SQLiteDatabase實現對其中數據表中數據的增刪改查。
最簡單的寫法，最好還是自己封裝一個DAO
import org.apache.log4j.Logger;
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 java.util.Vector;
public class SQLiteUtils {
final static Logger logger = Logger.getLogger(SQLiteUtils.class);
private Connection connection;
private Statement stmt = null;
public Connection getConnection() {
return connection;
}
public Statement getStmt() {
return stmt;
}
public SQLiteUtils(Connection connection) {
this.connection = connection;
try {
if (this.connection != null)
this.stmt = this.connection.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 創建表。
*
* @param sql
* @return boolean
*/
public boolean createTable(String sql) throws Exception {
logger.debug(sql);
////Statement stmt = null;
try {
//stmt = this.connection.createStatement();
stmt.executeUpdate(sql);
return true;
} catch (Exception e) {
logger.error("創建指定表時異常 : " + e.getLocalizedMessage());
if (connection.getAutoCommit())
connectionRollback(connection);
return false;
}
}
/**
* 向指定表中插入一條數據。
*
* @param table 表名
* @param params 參數數組
* @return boolean
*/
public boolean insert(String table, Object[] params) throws Exception {
String sql = "insert into " + table + " values(";
for (int i = 0; i < params.length; i++) {
Object temp = params[i];
if (temp != null && temp.getClass() == String.class) {
temp = ("'" + temp + "'");
}
if (i == (params.length - 1)) {
sql += (temp + ");");
} else {
sql += (temp + ", ");
}
}
logger.debug(sql);
try {
stmt.executeUpdate(sql);
return true;
} catch (Exception e) {
logger.error("向表插入" + table + "數據時異常 : " + e.getLocalizedMessage());
if (connection.getAutoCommit())
connectionRollback(connection);
return false;
}
}
/**
* 修改表中一個元組的數據。
*
* @param table 表名
* @param keyParam 要修改的元組的主鍵值
* @param keyField 要修改的元組的主鍵字段名稱
* @param fields 要修改的元組的字段名稱數組
* @param params 要修改的元組的值數組
* @return boolean
*/
public boolean update(String table, String keyParam, String keyField, String[] fields, Object[] params) throws Exception {
//Statement stmt = null;
String sql = "update " + table + " set ";
for (int i = 0; i < fields.length; i++) {
if (i == (fields.length - 1)) {
sql += (fields[i] + "='" + params[i] + "' where " + keyField + "='" + keyParam + "';");
} else {
sql += (fields[i] + "='" + params[i] + "', ");
}
}
logger.debug(sql);
try {
//stmt = this.connection.createStatement();
int rows = stmt.executeUpdate(sql);
return true;
} catch (Exception e) {
logger.error("修改表" + table + "數據時異常 : " + e.getLocalizedMessage());
if (connection.getAutoCommit())
connectionRollback(connection);
return false;
}
}
/**
* 刪除指定表中指定鍵值的元組。
*
* @param table
* @param key
* @param keyValue
* @return boolean
*/
public boolean delete(String table, String key, String keyValue) throws Exception {
//Statement stmt = null;
String sql = "delete from " + table + " where " + key + "='" + keyValue + "';";
logger.debug(sql);
try {
//stmt = this.connection.createStatement();
stmt.executeUpdate(sql);
return true;
} catch (Exception e) {
logger.error("刪除表" + table + "數據時異常 : " + e.getLocalizedMessage());
if (connection.getAutoCommit())
connectionRollback(connection);
return false;
}
}
public boolean executeSql(String sql) throws Exception {
logger.debug(sql);
try {
//stmt = this.connection.createStatement();
int result = stmt.executeUpdate(sql);
return true;
} catch (Exception e) {
logger.error(sql + "執行失敗");
if (connection.getAutoCommit())
connectionRollback(connection);
return false;
}
}
/**
* 將一個表中滿足指定條件的所有元組以Vector<Vector<Object>>的形式返回
*
* @param table
* @param key
* @param keyValue
* @return Vector<Vector<Object>>
*/
public Vector<Vector<Object>> selectVector(String table, String key, String keyValue) throws Exception {
//Statement stmt = null;
ResultSet rs = null;
Vector<Vector<Object>> value = new Vector<Vector<Object>>();
String sql = "select * from " + table + " where " + key + "='" + keyValue + "';";
logger.debug(sql);
try {
//stmt = this.connection.createStatement();
rs = stmt.executeQuery(sql);
int columnCounts = getFieldsCounts(rs);
while (rs.next()) {
Vector<Object> valueVector = new Vector<Object>();
for (int i = 1; i <= columnCounts; i++) {
valueVector.addElement(rs.getObject(i));
}
value.addElement(valueVector);
}
return value;
} catch (Exception e) {
logger.error("查詢表" + table + "數據時異常 : " + e.getLocalizedMessage());
return value;
}
}
public Object selectColumnById(String table,String key,String keyValue,String column){
ResultSet rs = null;
String sql = "SELECT "+column+" from " + table + " where "+ key + "='" + keyValue +"'";
logger.debug(sql);
Object o = null;
try {
//stmt = this.connection.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
o = rs.getObject(1);
}
return o;
} catch (Exception e) {
logger.error("查詢表sql數據時異常 : " + e.getLocalizedMessage());
return o;
}
}
/**
* 返回制定sql語句查詢的Vector<Vector<Object>>結果集
*
* @param sql sql語句
* @return Vector<Vector<Object>>
*/
public Vector<Vector<Object>> selectVector(String sql) throws Exception {
//Statement stmt = null;
ResultSet rs = null;
Vector<Vector<Object>> value = new Vector<Vector<Object>>();
logger.debug(sql);
try {
//stmt = this.connection.createStatement();
rs = stmt.executeQuery(sql);
int columnCounts = getFieldsCounts(rs);
while (rs.next()) {
Vector<Object> valueVector = new Vector<Object>();
for (int i = 1; i <= columnCounts; i++) {
Object o = rs.getObject(i);
valueVector.addElement( o == null ?"null":o);
}
value.addElement(valueVector);
}
return value;
} catch (Exception e) {
logger.error("查詢表sql數據時異常 : " + e.getLocalizedMessage());
return value;
}
}
/**
* 將滿足一定條件的指定表中所有元組數據以Object[][]形式返回
*
* @param table
* @param key
* @param keyValue
* @return Object[][]
*/
public Object[][] selectObject(String table, String key, String keyValue) throws Exception {
//Statement stmt = null;
ResultSet rs = null;
int columns = getFieldsCounts(table);
int rows = getTableCount(table, key, keyValue);
Object[][] tableObject = new Object[rows][columns];
String sql = "select * from " + table + " where " + key + "='" + keyValue + "';";
logger.debug(sql);
try {
//stmt = this.connection.createStatement();
rs = stmt.executeQuery(sql);
int i = 0;
while (rs.next()) {
for (int j = 0; j < columns; j++) {
tableObject[i][j] = rs.getObject(j + 1);
}
i++;
}
return tableObject;
} catch (Exception e) {
logger.error("查詢表" + table + "數據時異常 : " + e.getLocalizedMessage());
return tableObject;
}
}
/**
* 將一個表中所有的元組以Vector<Vector<Object>>的形式返回
*
* @param table
* @return Vector<Vector<Object>>
*/
public Vector<Vector<Object>> select(String table) throws Exception {
//Statement stmt = null;
ResultSet rs = null;
Vector<Vector<Object>> value = new Vector<Vector<Object>>();
String sql = "select * from " + table + ";";
logger.debug(sql);
try {
//stmt = this.connection.createStatement();
rs = stmt.executeQuery(sql);
int columnCounts = getFieldsCounts(rs);
while (rs.next()) {
Vector<Object> valueVector = new Vector<Object>();
for (int i = 1; i <= columnCounts; i++) {
valueVector.addElement(rs.getObject(i));
}
value.addElement(valueVector);
}
return value;
} catch (Exception e) {
logger.error("查詢表" + table + "數據時異常 : " + e.getLocalizedMessage());
return value;
}
}
/**
* 將一個表中所有的元組以Object[][]的形式返回
*
* @param table
* @return Object[][]
*/
public Object[][] selectObject(String table) throws Exception {
//Statement stmt = null;
ResultSet rs = null;
int columns = getFieldsCounts(table);
int rows = getTableCount(table);
Object[][] tableObject = new Object[rows][columns];
String sql = "select * from " + table + ";";
logger.debug(sql);
try {
//stmt = this.connection.createStatement();
rs = stmt.executeQuery(sql);
int i = 0;
while (rs.next()) {
for (int j = 0; j < columns; j++) {
Object o = rs.getObject(j + 1);
tableObject[i][j] = (o == null? "null":o);
}
i++;
}
return tableObject;
} catch (Exception e) {
logger.error("查詢表" + table + "數據時異常 : " + e.getLocalizedMessage());
return tableObject;
}
}
/**
* 分頁查詢
* @param table
* @param key
* @param keyValue
* @param size
* @param offset
* @return
* @throws Exception
*/
public List<List<Object>> selectWithPager(String table, String[] key, String[] keyValue, String size, String offset,String orderBy) throws Exception {
//Statement stmt = null;
ResultSet rs = null;
String sql = "select * from " + table +" where ";
for(int i = 0 ; i < key.length ;i++){
if(keyValue[i] != null){
if("nickname".equals(key[i]))
sql += (key[i]+" like '%"+ keyValue[i] +"%' and ");
else
sql += (key[i]+"='"+ keyValue[i] +"' and ");
}
}
sql = sql.substring(0,sql.length()-4);
sql += " order by "+orderBy+" desc limit "+ size +" offset "+offset+";";
List<List<Object>> value = new ArrayList<List<Object>>();
logger.debug(sql);
try {
//stmt = this.connection.createStatement();
rs = stmt.executeQuery(sql);
int columnCounts = getFieldsCounts(rs);
while (rs.next()) {
List<Object> valueVector = new ArrayList<Object>();
for (int i = 1; i <= columnCounts; i++) {
valueVector.add(rs.getObject(i));
}
value.add(valueVector);
}
return value;
} catch (Exception e) {
logger.error("查詢表" + table + "數據時異常 : " + e.getLocalizedMessage());
return value;
}
}
/**
* 將一個ResultSet結果集中的所有字段以List形式返回
*
* @param resultSet
* @return List<String>
*/
public List<String> getFields(ResultSet resultSet) throws Exception {
List<String> fieldsList = new ArrayList<String>();
try {
int columnCounts = resultSet.getMetaData().getColumnCount();
for (int i = 1; i <= columnCounts; i++) {
fieldsList.add(resultSet.getMetaData().getColumnName(i));
}
} catch (SQLException e) {
logger.error("加載表中字段異常 ：" + e.getLocalizedMessage());
return null;
}
return fieldsList;
}
/**
* 將一個表中的所有字段以List形式返回
*
* @param table
* @return List<String>
*/
public List<String> getFields(String table) throws Exception {
List<String> fieldsList = new ArrayList<String>();
//Statement stmt = null;
ResultSet rs = null;
String sql = "select * from " + table + ";";
logger.debug(sql);
try {
//stmt = this.connection.createStatement();
rs = stmt.executeQuery(sql);
fieldsList = getFields(rs);
} catch (Exception e) {
logger.error("查詢表" + table + "數據時異常 : " + e.getLocalizedMessage());
}
return fieldsList;
}
/**
* 將一個ResultSet結果集中的所有字段的數目返回
*
* @param resultSet
* @return int
*/
public int getFieldsCounts(ResultSet resultSet) throws Exception {
try {
return resultSet.getMetaData().getColumnCount();
} catch (SQLException e) {
logger.error("加載表中字段異常 ：" + e.getLocalizedMessage());
return 0;
}
}
/**
* 返回一個表的所有字段數目
*
* @param table
* @return int
*/
public int getFieldsCounts(String table) throws Exception {
int counts = 0;
//Statement stmt = null;
ResultSet rs = null;
String sql = "select * from " + table + ";";
logger.debug(sql);
try {
//stmt = this.connection.createStatement();
rs = stmt.executeQuery(sql);
counts = getFieldsCounts(rs);
} catch (Exception e) {
logger.error("查詢表" + table + "數據時異常 : " + e.getLocalizedMessage());
}
return counts;
}
/**
* 查詢一個表中的所有元組數目
*
* @param table
* @return int
*/
public int getTableCount(String table) throws Exception {
String sql = "select count(*) from " + table + ";";
//Statement stmt = null;
ResultSet rs = null;
int counts = 0;
try {
//stmt = this.connection.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
counts = rs.getInt(1);
}
return counts;
} catch (Exception e) {
logger.error("查詢表" + table + "元組數時異常 : " + e.getLocalizedMessage());
return counts;
}
}
public int getTableCountViaSql(String sql) throws Exception {
//Statement stmt = null;
ResultSet rs = null;
int counts = 0;
try {
//stmt = this.connection.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
counts = rs.getInt(1);
}
return counts;
} catch (Exception e) {
logger.error(sql +"：" + e.getLocalizedMessage());
return counts;
}
}
/**
* 查詢一個表中的滿足一定條件的所有元組數目
*
* @param table 表名
* @param key 字段名稱
* @param keyValue 字段值
* @return int
*/
public int getTableCount(String table, String key, String keyValue) throws Exception {
String sql = "select count(*) from " + table + " where " + key + "='" + keyValue + "';";
//Statement stmt = null;
ResultSet rs = null;
int counts = 0;
try {
//stmt = this.connection.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
counts = rs.getInt(1);
}
return counts;
} catch (Exception e) {
logger.error("查詢表" + table + "元組數時異常 : " + e.getLocalizedMessage());
return counts;
}
}
public int getMaxCol(String table,String col){
String sql = "select max("+col+") from " + table + ";";
//Statement stmt = null;
ResultSet rs = null;
int counts = 0;
try {
//stmt = this.connection.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
counts = rs.getInt(1);
}
return counts;
} catch (Exception e) {
logger.error("查詢表" + table + "元組數時異常 : " + e.getLocalizedMessage());
return counts;
}
}
private void connectionRollback(Connection connection) {
try {
connection.rollback();
} catch (SQLException e) {
logger.error("異常時回滾錯誤 : " + e.getLocalizedMessage());
}
}
}
