一,创建数据库
1,继承SQLiteOpenHelper类:
public class XxDatabaseHelper extends SQLiteOpenHelper {}
2,创建XxDatabaseHelper的实例:
XxDatabaseHelper xxDatabaseHelper = new XxDatabaseHelper(this, “xxx.db”, null, 1);
3,调用XxDatabaseHelper实例的getReadableDatabase()方法或者getWritableDatabase()方法得到SQLiteDatabase的对象:
SQLiteDatabase sqLiteDatabase = xxDatabaseHelper.getWritableDatabase();
或者SQLiteDatabase sqLiteDatabase =xxDatabaseHelper.getReadableDatabase();
package com.jackie.app_test;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Created by Administrator on 2016/11/9.
*/
public class MyDatabaseHelpler extends SQLiteOpenHelper {
public static final String CREATE_BOOK = "create table Book (\n" +
"id integer primary key autoincrement,\n" +
"author text,\n" +
"price real,\n" +
"pages integer,\n" +
"name text\n" +
")";
public MyDatabaseHelpler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_BOOK);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
package com.jackie.app_test;
import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
/**
* Created by Administrator on 2016/11/9.
*/
public class SQLiteActivity extends Activity {
private Button btn_create;
private MyDatabaseHelpler helpler;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_sqlite);
btn_create = (Button)findViewById(R.id.btn_create);
helpler = new MyDatabaseHelpler(this,"BookStore.db",null,1);
btn_create.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
helpler.getWritableDatabase();
}
});
}
}
二,升级数据库(也就是更新数据库中的表)
1,在onCreate()方法中执行要升级的表
2,重写onUpgrade()方法
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
switch (oldVersion) {
case 1:
db.execSQL(xxx);
case 2:
db.execSQL(“xxx”);
default:
}
}
注:每一个数据库版本都会对应一个版本号, 当指定的数据库版本号大于当前数据库版本号的时候,
就会进入到 onUpgrade()方法中去执行更新操作。switch 中每一个 case 的最后都是没有使用 break 的,为什么要这么做呢?这是为了保证在跨版本升级的时候,每一次的数据库修改都能被全部执行到。
3,创建XxDatabaseHelper的实例
XxDatabaseHelper xxDatabaseHelper = new XxDatabaseHelper(this, “xxx.db”, null, 2);
4,调用XxDatabaseHelper实例的getReadableDatabase()方法或者getWritableDatabase()方法得到SQLiteDatabase的对象:
SQLiteDatabase sqLiteDatabase = xxDatabaseHelper.getWritableDatabase();
或者SQLiteDatabase sqLiteDatabase =xxDatabaseHelper.getReadableDatabase();
package com.jackie.app_test;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* Created by Administrator on 2016/11/9.
*/
public class MyDatabaseHelpler extends SQLiteOpenHelper {
public static final String CREATE_BOOK = "create table Book (\n" +
"id integer primary key autoincrement,\n" +
"author text,\n" +
"price real,\n" +
"pages integer,\n" +
"name text\n" +
")";
public static final String CREATE_CATEGORY = "create table category (\n" +
"id integer primary key autoincrement,\n" +
"category_name text,\n" +
"category_code text\n" +
")";
public MyDatabaseHelpler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_BOOK);
db.execSQL(CREATE_CATEGORY);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
switch (oldVersion) {
case 1:
db.execSQL(CREATE_CATEGORY);
case 2:
db.execSQL("alter table Book add column category_id integer");
default:
}
}
}
package com.jackie.app_test;
import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
/**
* Created by Administrator on 2016/11/9.
*/
public class SQLiteActivity extends Activity {
private Button btn_create;
private MyDatabaseHelpler helpler;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_sqlite);
btn_create = (Button)findViewById(R.id.btn_create);
helpler = new MyDatabaseHelpler(this,"BookStore.db",null,2);
btn_create.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
helpler.getWritableDatabase();
}
});
}
}
三,对数据库的CRUD操作
a,添加数据
1,使用ContentValues 来对要添加的数据进行组装
ContentValues values = new ContentValues();
调用values.put()方法
2,插入数据
调用SQLiteDatabase对象的insert()方法
package com.jackie.app_test;
import android.app.Activity;
import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
public class SQLiteActivity extends Activity {
private Button btn_add;
private MyDatabaseHelpler helpler;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_sqlite);
btn_add = (Button) findViewById(R.id.btn_add);
helpler = new MyDatabaseHelpler(this, "BookStore.db", null, 2);
btn_add.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
SQLiteDatabase db = helpler.getWritableDatabase();
ContentValues values = new ContentValues();
// 开始组装第一条数据
values.put("name", "The Da Vinci Code");
values.put("author", "Dan Brown");
values.put("pages", 454);
values.put("price", 16.96);
db.insert("Book", null, values); // 插入第一条数据
values.clear();
// 开始组装第二条数据
values.put("name", "The Lost Symbol");
values.put("author", "Dan Brown");
values.put("pages", 510);
values.put("price", 19.95);
db.insert("Book", null, values); // 插入第二条数据
}
});
}
}
b,更新数据
1,使用ContentValues 来对要添加的数据进行组装
ContentValues values = new ContentValues();
调用values.put()方法
2,插入数据
调用SQLiteDatabase对象的update()方法。update()方法中的第三、第四个参数用于去约束更新某一行或某几行中的数据,不指定的话默认就是更新所有行。
//核心代码
btn_update.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
SQLiteDatabase db = helpler.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("price", 10.99);
db.update("Book", values, "name = ?", new String[]{"The Da Vinci Code" });
}
});
c,删除数据
调用SQLiteDatabase对象的delete()方法。delete()方法中的第二、第三个参数又是用于去约束删除某一行或某几行的数据,不指定的话默认就是删除所有行。
//核心代码
btn_delete.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.delete("Book", "pages > ?", new String[] { "500" });
}
});
d,查询数据
1,调用SQLiteDatabase对象的query()方法,返回一个Cursor对象。query()方法中至少有七个参数:
2,通过Cursor对象的 getColumnIndex()方法获取到某一列在表中对应的位置索引
3,查询完之后就得到了一个Cursor对象,接着我们利用while循环调用它的moveToNext()方法。通过Cursor对象的getXxx()方法得到相应的数据,并执行相关业务逻辑。
4,最后调用Cursor对象的close()方法关闭游标。
//简单运用,核心代码
btn_query.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
// 查询Book表中所有的数据
Cursor cursor = db.query("Book", null, null, null, null, null, null);
if (cursor.moveToFirst()) {
do {
// 遍历Cursor对象,取出数据并打印
String name = cursor.getString(cursor.
getColumnIndex("name"));
String author = cursor.getString(cursor.
getColumnIndex("author"));
int pages = cursor.getInt(cursor.getColumnIndex
("pages"));
double price = cursor.getDouble(cursor.
getColumnIndex("price"));
Log.d("MainActivity", "book name is " + name);
Log.d("MainActivity", "book author is " + author);
Log.d("MainActivity", "book pages is " + pages);
Log.d("MainActivity", "book price is " + price);
} while (cursor.moveToNext());
}
cursor.close();
}
});
四,使用事务
replaceData.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
SQLiteDatabase db = dbHelper.getWritableDatabase();
db.beginTransaction(); // 开启事务
try {
db.delete("Book", null, null);
if (true) {
// 在这里手动抛出一个异常,让事务失败。开启事务与事务执行成功之间,如果有异常,就会中断事务。
throw new NullPointerException();
}
ContentValues values = new ContentValues();
values.put("name", "Game of Thrones");
values.put("author", "George Martin");
values.put("pages", 720);
values.put("price", 20.85);
db.insert("Book", null, values);
db.setTransactionSuccessful(); // 事务已经执行成功
} catch (Exception e) {
e.printStackTrace();
} finally {
db.endTransaction(); // 结束事务
}
}
});