SQLite详解
2018-12-21 本文已影响8人
Owen270
参考文章：https://www.jianshu.com/p/5c33be6ce89d
image.png
1.首先创建一个extends在SQLiteOpenhelper的类，并重写onCreate和onUpgrad方法
public class OrderDBHelper extends SQLiteOpenHelper{
private static final int DB_VERSION = 1;
private static final String DB_NAME = "myTest.db";
public static final String TABLE_NAME = "Orders";
public OrderDBHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
// create table Orders(Id integer primary key, CustomName text, OrderPrice integer, Country text);
String sql = "create table if not exists " + TABLE_NAME + " (Id integer primary key, CustomName text, OrderPrice integer, Country text)";
sqLiteDatabase.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
sqLiteDatabase.execSQL(sql);
onCreate(sqLiteDatabase);
}
}
注意：当表结构发生变化的时候，会自动触发onUpgrad()方法，删除原来的表，并重写创建新的表结构。
2.再创建一个OrderDao用于处理所有的数据操作方法。
public class OrderDao {
private static final String TAG = "OrdersDao";
// 列定义
private final String[] ORDER_COLUMNS = new String[] {"Id", "CustomName","OrderPrice","Country"};
private Context context;
private OrderDBHelper ordersDBHelper;
public OrderDao(Context context) {
this.context = context;
ordersDBHelper = new OrderDBHelper(context);
}
/**
* 判断表中是否有数据
*/
public boolean isDataExist(){
int count = 0;
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = ordersDBHelper.getReadableDatabase();
// select count(Id) from Orders
cursor = db.query(OrderDBHelper.TABLE_NAME, new String[]{"COUNT(Id)"}, null, null, null, null, null);
if (cursor.moveToFirst()) {
count = cursor.getInt(0);
}
if (count > 0) return true;
}
catch (Exception e) {
Log.e(TAG, "", e);
}
finally {
if (cursor != null) {
cursor.close();
}
if (db != null) {
db.close();
}
}
return false;
}
/**
* 初始化数据
*/
public void initTable(){
SQLiteDatabase db = null;
try {
db = ordersDBHelper.getWritableDatabase();
db.beginTransaction();
db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (1, 'Arc', 100, 'China')");
db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (2, 'Bor', 200, 'USA')");
db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (3, 'Cut', 500, 'Japan')");
db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (4, 'Bor', 300, 'USA')");
db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (5, 'Arc', 600, 'China')");
db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (6, 'Doom', 200, 'China')");
db.setTransactionSuccessful();
}catch (Exception e){
Log.e(TAG, "", e);
}finally {
if (db != null) {
db.endTransaction();
db.close();
}
}
}
/**
* 执行自定义SQL语句
*/
public void execSQL(String sql) {
SQLiteDatabase db = null;
try {
if (sql.contains("select")){
Toast.makeText(context, R.string.strUnableSql, Toast.LENGTH_SHORT).show();
}else if (sql.contains("insert") || sql.contains("update") || sql.contains("delete")){
db = ordersDBHelper.getWritableDatabase();
db.beginTransaction();
db.execSQL(sql);
db.setTransactionSuccessful();
Toast.makeText(context, R.string.strSuccessSql, Toast.LENGTH_SHORT).show();
}
} catch (Exception e) {
Toast.makeText(context, R.string.strErrorSql, Toast.LENGTH_SHORT).show();
Log.e(TAG, "", e);
} finally {
if (db != null) {
db.endTransaction();
db.close();
}
}
}
/**
* 查询数据库中所有数据
*/
public List<Order> getAllDate(){
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = ordersDBHelper.getReadableDatabase();
// select * from Orders
cursor = db.query(OrderDBHelper.TABLE_NAME, ORDER_COLUMNS, null, null, null, null, null);
if (cursor.getCount() > 0) {
List<Order> orderList = new ArrayList<Order>(cursor.getCount());
while (cursor.moveToNext()) {
orderList.add(parseOrder(cursor));
}
return orderList;
}
}
catch (Exception e) {
Log.e(TAG, "", e);
}
finally {
if (cursor != null) {
cursor.close();
}
if (db != null) {
db.close();
}
}
return null;
}
/**
* 新增一条数据
*/
public boolean insertDate(){
SQLiteDatabase db = null;
try {
db = ordersDBHelper.getWritableDatabase();
db.beginTransaction();
// insert into Orders(Id, CustomName, OrderPrice, Country) values (7, "Jne", 700, "China");
ContentValues contentValues = new ContentValues();
contentValues.put("Id", 7);
contentValues.put("CustomName", "Jne");
contentValues.put("OrderPrice", 700);
contentValues.put("Country", "China");
db.insertOrThrow(OrderDBHelper.TABLE_NAME, null, contentValues);
db.setTransactionSuccessful();
return true;
}catch (SQLiteConstraintException e){
Toast.makeText(context, "主键重复", Toast.LENGTH_SHORT).show();
}catch (Exception e){
Log.e(TAG, "", e);
}finally {
if (db != null) {
db.endTransaction();
db.close();
}
}
return false;
}
/**
* 删除一条数据 此处删除Id为7的数据
*/
public boolean deleteOrder() {
SQLiteDatabase db = null;
try {
db = ordersDBHelper.getWritableDatabase();
db.beginTransaction();
// delete from Orders where Id = 7
db.delete(OrderDBHelper.TABLE_NAME, "Id = ?", new String[]{String.valueOf(7)});
db.setTransactionSuccessful();
return true;
} catch (Exception e) {
Log.e(TAG, "", e);
} finally {
if (db != null) {
db.endTransaction();
db.close();
}
}
return false;
}
/**
* 修改一条数据 此处将Id为6的数据的OrderPrice修改了800
*/
public boolean updateOrder(){
SQLiteDatabase db = null;
try {
db = ordersDBHelper.getWritableDatabase();
db.beginTransaction();
// update Orders set OrderPrice = 800 where Id = 6
ContentValues cv = new ContentValues();
cv.put("OrderPrice", 800);
db.update(OrderDBHelper.TABLE_NAME,
cv,
"Id = ?",
new String[]{String.valueOf(6)});
db.setTransactionSuccessful();
return true;
}
catch (Exception e) {
Log.e(TAG, "", e);
}
finally {
if (db != null) {
db.endTransaction();
db.close();
}
}
return false;
}
/**
* 数据查询 此处将用户名为"Bor"的信息提取出来
*/
public List<Order> getBorOrder(){
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = ordersDBHelper.getReadableDatabase();
// select * from Orders where CustomName = 'Bor'
cursor = db.query(OrderDBHelper.TABLE_NAME,
ORDER_COLUMNS,
"CustomName = ?",
new String[] {"Bor"},
null, null, null);
if (cursor.getCount() > 0) {
List<Order> orderList = new ArrayList<Order>(cursor.getCount());
while (cursor.moveToNext()) {
Order order = parseOrder(cursor);
orderList.add(order);
}
return orderList;
}
}
catch (Exception e) {
Log.e(TAG, "", e);
}
finally {
if (cursor != null) {
cursor.close();
}
if (db != null) {
db.close();
}
}
return null;
}
/**
* 统计查询 此处查询Country为China的用户总数
*/
public int getChinaCount(){
int count = 0;
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = ordersDBHelper.getReadableDatabase();
// select count(Id) from Orders where Country = 'China'
cursor = db.query(OrderDBHelper.TABLE_NAME,
new String[]{"COUNT(Id)"},
"Country = ?",
new String[] {"China"},
null, null, null);
if (cursor.moveToFirst()) {
count = cursor.getInt(0);
}
}
catch (Exception e) {
Log.e(TAG, "", e);
}
finally {
if (cursor != null) {
cursor.close();
}
if (db != null) {
db.close();
}
}
return count;
}
/**
* 比较查询 此处查询单笔数据中OrderPrice最高的
*/
public Order getMaxOrderPrice(){
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = ordersDBHelper.getReadableDatabase();
// select Id, CustomName, Max(OrderPrice) as OrderPrice, Country from Orders
cursor = db.query(OrderDBHelper.TABLE_NAME, new String[]{"Id", "CustomName", "Max(OrderPrice) as OrderPrice", "Country"}, null, null, null, null, null);
if (cursor.getCount() > 0){
if (cursor.moveToFirst()) {
return parseOrder(cursor);
}
}
}
catch (Exception e) {
Log.e(TAG, "", e);
}
finally {
if (cursor != null) {
cursor.close();
}
if (db != null) {
db.close();
}
}
return null;
}
/**
* 将查找到的数据转换成Order类
*/
private Order parseOrder(Cursor cursor){
Order order = new Order();
order.id = (cursor.getInt(cursor.getColumnIndex("Id")));
order.customName = (cursor.getString(cursor.getColumnIndex("CustomName")));
order.orderPrice = (cursor.getInt(cursor.getColumnIndex("OrderPrice")));
order.country = (cursor.getString(cursor.getColumnIndex("Country")));
return order;
}
}
注意：对于增删改这类对表内容变换的操作，我们需要先调用getWritableDatabase,在执行的时候，调用通用改的execSQL(String sql)方法或者对应的insert() ，delete() , update() 。对于查，需要调用getReadableDatabase()，此时不能使用exexSQL方法，只能使用query()或者rawQuery()方法。
3.增加数据
(1).初始化数据，数据较多时，直接采用execSQL方法
db = ordersDBHelper.getWritableDatabase();
db.beginTransaction();
db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (1, 'Arc', 100, 'China')");
db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (2, 'Bor', 200, 'USA')");
db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (3, 'Cut', 500, 'Japan')");
db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (4, 'Bor', 300, 'USA')");
db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (5, 'Arc', 600, 'China')");
db.execSQL("insert into " + OrderDBHelper.TABLE_NAME + " (Id, CustomName, OrderPrice, Country) values (6, 'Doom', 200, 'China')");
db.setTransactionSuccessful();
(2).使用insert()方法插入数据
db = ordersDBHelper.getWritableDatabase();
db.beginTransaction();
// insert into Orders(Id, CustomName, OrderPrice, Country) values (7, "Jne", 700, "China");
ContentValues contentValues = new ContentValues();
contentValues.put("Id", 7);
contentValues.put("CustomName", "Jne");
contentValues.put("OrderPrice", 700);
contentValues.put("Country", "China");
db.insertOrThrow(OrderDBHelper.TABLE_NAME, null, contentValues);
db.setTransactionSuccessful();
(3).删除数据
db = ordersDBHelper.getWritableDatabase();
db.beginTransaction();
// delete from Orders where Id = 7
db.delete(OrderDBHelper.TABLE_NAME, "Id = ?", new String[]{String.valueOf(7)});
db.setTransactionSuccessful();
(4).修改数据
db = ordersDBHelper.getWritableDatabase();
db.beginTransaction();
// update Orders set OrderPrice = 800 where Id = 6
ContentValues cv = new ContentValues();
cv.put("OrderPrice", 800);
db.update(OrderDBHelper.TABLE_NAME,
cv,
"Id = ?",
new String[]{String.valueOf(6)});
db.setTransactionSuccessful();
(5).查找数据
db = ordersDBHelper.getReadableDatabase();
// select * from Orders
cursor = db.query(OrderDBHelper.TABLE_NAME, ORDER_COLUMNS, null, null, null, null, null);
if (cursor.getCount() > 0) {
List<Order> orderList = new ArrayList<Order>(cursor.getCount());
while (cursor.moveToNext()) {
orderList.add(parseOrder(cursor));
}
return orderList;
}
/**
* 将查找到的数据转换成Order类
*/
private Order parseOrder(Cursor cursor){
Order order = new Order();
order.id = (cursor.getInt(cursor.getColumnIndex("Id")));
order.customName = (cursor.getString(cursor.getColumnIndex("CustomName")));
order.orderPrice = (cursor.getInt(cursor.getColumnIndex("OrderPrice")));
order.country = (cursor.getString(cursor.getColumnIndex("Country")));
return order;
}
