package
com.
example.
wpj.
myapplication.
sql;
import
android.
content.
ContentValues;
import
android.
content.
Context;
import
android.
database.
Cursor;
import
android.
database.
sqlite.
SQLiteDatabase;
import
android.
database.
sqlite.
SQLiteOpenHelper;
import
android.
database.
sqlite.
SQLiteStatement;
import
android.
util.
Log;
import
java.
util.
List;
/**
* Created by wpj on 2017/2/27.
* 数据库工具类
*/
public
class
DBUtil
extends
SQLiteOpenHelper {
private
static
final
String
DB_NAME
=
"TVBank.db";
private
static
final
int
VERSION
=
1;
//创建表SQL语句,可以创建多个
private
static
final
String
Create_Table
=
"create table login_info \n"
+
"(\n"
+
" log_id varchar2(50) not null,\n"
+
" account_name varchar2(20) null,\n"
+
" account_password varchar2(20) null,\n"
+
" state int null,\n"
+
" device_id varchar2(50) null,\n"
+
" login_time date null,\n"
+
" constraint PK_LOGIN_INFO primary key (log_id)\n"
+
");";
private
SQLiteDatabase
db;
public
DBUtil(
Context
context) {
super(
context,
DB_NAME,
null,
VERSION);
}
/**
* 数据库版本更新执行方法
* 版本号改变则执行此方法
*
@param
sqLiteDatabase
*
@param
oldVersion
*
@param
newVersion
*/
@
Override
public
void
onUpgrade(
SQLiteDatabase
sqLiteDatabase,
int
oldVersion,
int
newVersion) {
Log.
e(
"onUpgrade",
"数据库升级更新,"
+
oldVersion
+
"--->"
+
newVersion);
//db.execSQL("alter table info add money varchar(20)");
}
/**
* 创建数据库
* 工具类第一次执行时运行此方法
*
@param
sqLiteDatabase
*/
@
Override
public
void
onCreate(
SQLiteDatabase
sqLiteDatabase) {
this.
db
=
sqLiteDatabase;
sqLiteDatabase.
execSQL(
Create_Table);
Log.
e(
"CreateDB",
"Create "
+
DB_NAME);
}
/**
* 获取DB实例
* 读写权限的实例
*
@return
*/
private
SQLiteDatabase
getDb() {
if (
db
==
null)
db
=
getWritableDatabase();
return
db;
}
/**
* 关闭DB实例
*/
public
void
close() {
if (
db
!=
null)
db.
close();
}
/**
* 插入
* 单条插入
*
@param
table
*
@param
values
*
@return
*/
public
long
insert(
String
table,
ContentValues
values) {
db
=
getDb();
return
db.
insert(
table,
null,
values);
}
/**
* 批量插入数据
* 使用事务提交的方式,大大缩短执行时间
*
@param
table
*
@param
columns
*
@param
values
*
@return
*/
public
boolean
insertMulti(
String
table,
String[]
columns,
List
<
String[]
>
values) {
db
=
getDb();
try {
String
columnSql
=
"";
String
valuesSql
=
"";
for (
String
column :
columns) {
columnSql
=
columnSql
+
column
+
",";
valuesSql
=
valuesSql
+
"?,";
}
StringBuilder
sb
=
new
StringBuilder();
sb.
append(
"insert into ");
sb.
append(
table);
sb.
append(
"(");
sb.
append(
columnSql.
substring(
0,
columnSql.
length()
-
1));
sb.
append(
") values (");
sb.
append(
valuesSql.
substring(
0,
valuesSql.
length()
-
1));
sb.
append(
")");
String
sql
=
sb.
toString();
//-----------------------设置事务----------------------
SQLiteStatement
stat
=
db.
compileStatement(
sql);
db.
beginTransaction();
for (
String[]
value :
values) {
for (
int
i
=
0;
i
<
value.
length;
i
++) {
stat.
bindString(
i
+
1,
value[
i]);
}
long
result
=
stat.
executeInsert();
if (
result
<
0) {
return
false;
}
}
//设置事务处理成功,不设置会自动回滚不提交。
db.
setTransactionSuccessful();
//-----------------------事务结束----------------------
}
catch (
Exception
e) {
e.
printStackTrace();
return
false;
}
finally {
db.
endTransaction();
}
return
true;
}
/**
* 查询
*
*
@param
distinct
*
@param
table
*
@param
columns
*
@param
selection-查询条件子句,相当于select语句where关键字后面的部分
*
@param
selectionArgs-对应于selection语句中占位符的值
*
@param
groupBy
*
@param
having
*
@param
orderBy
*
@param
limit-指定偏移量和获取的记录数,用作分页
*
@return
*/
public
Cursor
query(
boolean
distinct,
String
table,
String[]
columns,
String
selection,
String[]
selectionArgs,
String
groupBy,
String
having,
String
orderBy,
String
limit) {
db
=
getDb();
return
db.
query(
distinct,
table,
columns,
selection,
selectionArgs,
groupBy,
having,
orderBy,
limit);
}
/**
* 查询
*
*
@param
sql
*
@param
args
*
@return
*/
public
Cursor
query(
String
sql,
String[]
args) {
db
=
getDb();
return
db.
rawQuery(
sql,
args);
}
/**
* 更新
*
*
@param
table
*
@param
values
*
@param
whereClause
*
@param
whereArgs
*
@return
*/
public
int
update(
String
table,
ContentValues
values,
String
whereClause,
String[]
whereArgs) {
db
=
getDb();
return
db.
update(
table,
values,
whereClause,
whereArgs);
}
/**
* 删除
*
*
@param
table
*
@param
whereClause
*
@param
whereArgs
*
@return
*/
public
int
delete(
String
table,
String
whereClause,
String[]
whereArgs) {
db
=
getDb();
return
db.
delete(
table,
whereClause,
whereArgs);
}
/**
* 执行sql
*
*
@param
sql
*/
public
void
excSQL(
String
sql) {
db
=
getDb();
db.
execSQL(
sql);
}
/**
* 执行sql
*
*
@param
sql
*
@param
bindArgs
*/
public
void
excSQL(
String
sql,
Object[]
bindArgs) {
db
=
getDb();
db.
execSQL(
sql,
bindArgs);
}
}