@linux1s1s
2017-01-22T16:54:14.000000Z
字数 9819
阅读 2732
AndroidSQL
2016-01
在完成封装SQLite之前,先了解一下SQLite的基本知识,在后续博客中会进一步完成SQLite封装。
直接看Demo,首先看POJO类
public class Order {
public int id;
public String customName;
public int orderPrice;
public String country;
public Order() {
}
public Order(int id, String customName, int orderPrice, String country) {
this.id = id;
this.customName = customName;
this.orderPrice = orderPrice;
this.country = country;
}
}
紧接着定义一个SQLiteHelper类,这个类主要建数据库和建表用。
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);
}
}
然后建立DAO类,做数据转换和数据处理,这个类将数据库操作的所有对外方法封装了一层,对外公开的方法无外乎:
“增删查改”。对于“增删改”这类对表内容变换的操作,我们需先调用getWritableDatabase(),在执行的时候可以调用通用的execSQL(String sql)方法或对应的操作API:insert()、delete()、update()。而对“查”,需要调用getReadableDatabase(),这时就不能使用execSQL方法了,得使用query()或rawQuery()方法。
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 where Id=1 (注意其中的空格和不能空格的地方)
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的数据
delete from Orders where 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
update Orders set OrderPrice=800 where Id=6
*/
public boolean updateOrder(){
SQLiteDatabase db = null;
try {
db = ordersDBHelper.getWritableDatabase();
db.beginTransaction();
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"的信息提取出来
select from Oreders where CustomName='Bor'
*/
public List<Order> getBorOrder(){
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = ordersDBHelper.getReadableDatabase();
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的用户总数
select count(Id) from Orders where Country='China'
*/
public int getChinaCount(){
int count = 0;
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = ordersDBHelper.getReadableDatabase();
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最高的
select Id, CustomName, Max(OrderPrice) as OrderPrice, Country from Orders
*/
public Order getMaxOrderPrice(){
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = ordersDBHelper.getReadableDatabase();
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;
}
}
最后用个简单的Activity测试一下上面的DAO,界面的初始化操作,这里就省略了,给出单击回调的测试代码
public class SQLBtnOnclickListener implements View.OnClickListener{
@Override
public void onClick(View view) {
switch (view.getId()){
case R.id.executeButton:
showSQLMsg.setVisibility(View.GONE);
String sql = inputSqlMsg.getText().toString();
if (! TextUtils.isEmpty(sql)){
ordersDao.execSQL(sql);
}else {
Toast.makeText(OrderActivity.this, R.string.strInputSql, Toast.LENGTH_SHORT).show();
}
refreshOrderList();
break;
case R.id.insertButton:
showSQLMsg.setVisibility(View.VISIBLE);
showSQLMsg.setText("新增一条数据:\n添加数据(7, \"Jne\", 700, \"China\")\ninsert into Orders(Id, CustomName, OrderPrice, Country) values (7, \"Jne\", 700, \"China\")");
ordersDao.insertDate();
refreshOrderList();
break;
case R.id.deleteButton:
showSQLMsg.setVisibility(View.VISIBLE);
showSQLMsg.setText("删除一条数据:\n删除Id为7的数据\ndelete from Orders where Id = 7");
ordersDao.deleteOrder();
refreshOrderList();
break;
case R.id.updateButton:
showSQLMsg.setVisibility(View.VISIBLE);
showSQLMsg.setText("修改一条数据:\n将Id为6的数据的OrderPrice修改了800\nupdate Orders set OrderPrice = 800 where Id = 6");
ordersDao.updateOrder();
refreshOrderList();
break;
case R.id.query1Button:
showSQLMsg.setVisibility(View.VISIBLE);
StringBuilder msg = new StringBuilder();
msg.append("数据查询:\n此处将用户名为\"Bor\"的信息提取出来\nselect * from Orders where CustomName = 'Bor'");
List<Order> borOrders = ordersDao.getBorOrder();
for (Order order : borOrders){
msg.append("\n(" + order.id + ", " + order.customName + ", " + order.orderPrice + ", " + order.country + ")");
}
showSQLMsg.setText(msg);
break;
case R.id.query2Button:
showSQLMsg.setVisibility(View.VISIBLE);
int chinaCount = ordersDao.getChinaCount();
showSQLMsg.setText("统计查询:\n此处查询Country为China的用户总数\nselect count(Id) from Orders where Country = 'China'\ncount = " + chinaCount);
break;
case R.id.query3Button:
showSQLMsg.setVisibility(View.VISIBLE);
StringBuilder msg2 = new StringBuilder();
msg2.append("比较查询:\n此处查询单笔数据中OrderPrice最高的\nselect Id, CustomName, Max(OrderPrice) as OrderPrice, Country from Orders");
Order order = ordersDao.getMaxOrderPrice();
msg2.append("\n(" + order.id + ", " + order.customName + ", " + order.orderPrice + ", " + order.country + ")");
showSQLMsg.setText(msg2);
break;
default:
break;
}
}
}
TIPS 本文转载和修改自Android SQLite详解