package com.yc.database.manager;
import android.annotation.SuppressLint;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import java.io.Serializable;
import java.util.Date;
import com.yc.database.bean.BindSQL;
import com.yc.database.utils.DBLog;
import com.yc.database.utils.DateUtil;
/**
*
* @author yangchong
* email : yangchong211@163.com
* time : 2017/8/6
* desc : SQL语句执行器
* revise:
*
*/
public class SQLExecuteManager implements Serializable {
/**
* uid
*/
private static final long serialVersionUID = 1L;
/**
* SQLite中的关键字
*/
public static final String[] SQLITE_KEYWORDS = { "ABORT", " ACTION ",
" ADD ", "AFTER", "ALL", "ALTER", "ANALYZE", "AND", "AS", "ASC",
"ATTACH", "AUTOINCREMENT", "BEFORE", "BEGIN", "BETWEEN", "BY",
"CASCADE", "CASE", "CAST", "CHECK", "COLLATE", "COLUMN", "COMMIT",
"CONFLICT", "CONSTRAINT", "CREATE", "CROSS", "CURRENT_DATE",
"CURRENT_TIME", "CURRENT_TIMESTAMP", "DATABASE", "DEFAULT",
"DEFERRABLE", "DEFERRED", "DELETE", "DESC", "DETACH", "DISTINCT",
"DROP", "EACH", "ELSE", "END", "ESCAPE", "EXCEPT", "EXCLUSIVE",
"EXISTS", "EXPLAIN", "FAIL", "FOR", "FOREIGN", "FROM", "FULL",
"GLOB", "GROUP", "HAVING", "IF", "IGNORE", "IMMEDIATE", "IN",
"INDEX", "INDEXED", "INITIALLY", "INNER", "INSERT", "INSTEAD",
"INTERSECT", "INTO", "IS", "ISNULL", "JOIN", "KEY", "LEFT", "LIKE",
"LIMIT", "MATCH", "NATURAL", "NO", "NOT", "NOTNULL", "NULL", "OF",
"OFFSET", "ON", "OR", "ORDER", "OUTER", "PLAN", "PRAGMA",
"PRIMARY", "QUERY", "RAISE", "REFERENCES", "REGEXP", "REINDEX",
"RELEASE", "RENAME", "REPLACE", "RESTRICT", "RIGHT", "ROLLBACK",
"ROW", "SAVEPOINT", "SELECT", "SET", "TABLE", "TEMP", "TEMPORARY",
"THEN", "TO", "TRANSACTION", "TRIGGER", "UNION", "UNIQUE",
"UPDATE", "USING", "VACUUM", "VALUES", "VIEW", "VIRTUAL", "WHEN",
"WHERE" };
/**
* 数据库操作类
*/
private SQLiteDatabase mSQLiteDataBase;
public SQLExecuteManager(SQLiteDatabase mSQLiteDataBase) {
super();
this.mSQLiteDataBase = mSQLiteDataBase;
}
/**
* 开启一个事务(事务开始)
* 在事务代码执行完成后，必须要执行successTransaction()将事务标记为成功
* 在代码的最后必须要执行endTransaction()来结束当前事务，如果事务成功则提交事务，否则回滚事务
*
* db.beginTransaction();
* try {
* ...
* db.setTransactionSuccessful();
* } finally {
* db.endTransaction();
* }
*
*/
public void beginTransaction() {
this.mSQLiteDataBase.beginTransaction();
}
/**
* 标记当前事务成功
*/
public void successTransaction() {
this.mSQLiteDataBase.setTransactionSuccessful();
}
/**
* 结束当前事务，当事物被标记成功后，此操作会提交事务，否则会回滚事务
*/
public void endTransaction() {
this.mSQLiteDataBase.endTransaction();
}
/**
* 执行指定无返回值的单条SQL语句，如建表、创建数据库等
* @param sql 执行sql
*/
public void execSQL(String sql) {
DBLog.debug(sql);
this.mSQLiteDataBase.execSQL(sql);
}
/**
* 插入一条记录，返回该记录的rowId
* @param sql
* @param args
* @return 插入失败返回-1，成功返回rowId
*/
public long insert(String sql, Object[] args) {
long rowId = -1;
SQLiteStatement statement = this.mSQLiteDataBase.compileStatement(sql);
try {
if(args != null) {
for(int i = 0; i < args.length; i++) {
bindArgs(statement, i + 1, args[i]);
}
}
rowId = statement.executeInsert();
DBLog.debug(sql, args);
} finally {
statement.close();
}
return rowId;
}
/**
* 根据BindSQL进行插入数据
* @param bindSQL
* @return
* @throws Exception
*/
public long insert(BindSQL bindSQL) {
if (bindSQL==null){
return -1;
}
String sql = bindSQL.getSql();
Object[] bindArgs = bindSQL.getBindArgs();
return insert(sql, bindArgs);
}
/**
* 绑定参数
* @param statement
* @param position
* @param args
*/
private void bindArgs(SQLiteStatement statement, int position, Object args) {
int type = FieldTypeManager.getValueType(args);
switch(type) {
case FieldTypeManager.VALUE_TYPE_NULL:
statement.bindNull(position);
break;
case FieldTypeManager.BASE_TYPE_BYTE_ARRAY:
statement.bindBlob(position, (byte[])args);
break;
case FieldTypeManager.BASE_TYPE_CHAR:
case FieldTypeManager.BASE_TYPE_STRING:
statement.bindString(position, args.toString());
break;
case FieldTypeManager.BASE_TYPE_DATE:
statement.bindString(position, DateUtil.formatDatetime((Date) args));
break;
case FieldTypeManager.BASE_TYPE_DOUBLE:
case FieldTypeManager.BASE_TYPE_FLOAT:
statement.bindDouble(position, Double.parseDouble(args.toString()));
break;
case FieldTypeManager.BASE_TYPE_INT:
case FieldTypeManager.BASE_TYPE_LONG:
case FieldTypeManager.BASE_TYPE_SHORT:
statement.bindLong(position, Long.parseLong(args.toString()));
break;
case FieldTypeManager.NOT_BASE_TYPE:
throw new IllegalArgumentException("未知参数类型，请检查绑定参数");
}
}
/**
* 删除指定表
* @param tableName
* @throws Exception
*/
public void dropTable(String tableName) {
String sql = "DROP TABLE IF EXISTS " + tableName;
execSQL(sql);
}
/**
* 删除,表名不能使用占位符
* @param bindSQL
*/
public void delete(BindSQL bindSQL) {
updateOrDelete(bindSQL.getSql(), bindSQL.getBindArgs());
}
/**
* 删除,表名不能使用占位符
* @param sql 删除语句(参数使用占位符)
* @param args 占位符参数
*/
@SuppressLint("NewApi")
public void updateOrDelete(String sql, Object[] args) {
SQLiteStatement statement = mSQLiteDataBase.compileStatement(sql);
try {
if(args != null) {
for(int i = 0; i < args.length; i++) {
bindArgs(statement, i + 1, args[i]);
}
}
DBLog.debug(sql, args);
statement.executeUpdateDelete();
} finally {
statement.close();
}
}
/**
* 删除(对于表名需要动态获取的，此方法非常适合)
* @param tableName 要删除的数据表
* @param whereClause where后面的条件句(delete from XXX where XXX)，参数使用占位符
* @param whereArgs where子句后面的占位符参数
*/
public void delete(String tableName, String whereClause, String[] whereArgs) {
DBLog.debug("{SQL：DELETE FROM " + tableName + " WHERE " + whereClause + "，PARAMS：" + whereArgs + "}");
mSQLiteDataBase.delete(tableName, whereClause, whereArgs);
}
/**
* 更新
* @param bindSQL
*/
public void update(BindSQL bindSQL) {
updateOrDelete(bindSQL.getSql(), bindSQL.getBindArgs());
}
/**
* 根据SQL进行查询
* @param sql
* @return
*/
public Cursor query(String sql) {
return query(sql, null);
}
/**
* 执行绑定语句
* 运行一个预置的SQL语句，返回带游标的数据集（与query的语句最大的区别 = 防止SQL注入）
* @param sql sql语句
* @param whereArgs 搜索条件
* @return
*/
public Cursor query(String sql, String[] whereArgs) {
DBLog.debug("{SQL：" + sql + "，PARAMS：" + whereArgs + "}");
return this.mSQLiteDataBase.rawQuery(sql, whereArgs);
}
/**
* 根据BindSQL查询
* @param bindSQL
* @return
*/
public Cursor query(BindSQL bindSQL) {
return query(bindSQL.getSql(), (String[])bindSQL.getBindArgs());
}
@Deprecated
public Cursor query(boolean distinct, String table, String[] columns,
String selection, String[] selectionArgs, String groupBy,
String having, String orderBy, String limit) {
// 查询指定的数据表返回一个带游标的数据集。
// 各参数说明：
// table：表名称
// colums：列名称数组
// selection：条件子句，相当于where
// selectionArgs：条件语句的参数数组
// groupBy：分组
// having：分组条件
// orderBy：排序类
// limit：分页查询的限制
return this.mSQLiteDataBase.query(distinct, table, columns, selection,
selectionArgs, groupBy, having, orderBy, limit);
}
}