Android 第三章 SQLite 数据库
1,使用execSQL API 操作数据库。
步骤1,创建Class MyOpenHelper实现接口SQLiteOpenHelper,复写构造函数、onCreate、onUpgrade方法;
步骤2,在创建MyOpenHelper对象myOpenHelper后,使用 myOpenHelper 相关API进行操作数据库。
package com.xiaohui.createdb;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class MyOpenHelper extends SQLiteOpenHelper {
public MyOpenHelper(Context context) {
super(context,"xiaohui2.db", null, 1);
}
/**
* 当第一次创建库的时候执行
* Called when the database is created for the first time
*/
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "create table user(_id integer primary key autoincrement,name varchar(20),phone varchar(12))";
db.execSQL(sql);
}
/**
* 当数据库版本升级的时候调用
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
// System.out.println("onUpgrade。。。");
// String sql = "alter table user add phone varchar(20)";
// db.execSQL(sql );
}
}
package com.xiaohui.createdb;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;
public class MainActivity extends Activity {
private MyOpenHelper myOpenHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
myOpenHelper = new MyOpenHelper(getApplicationContext());
}
public void insert(View v){
SQLiteDatabase wdb = myOpenHelper.getWritableDatabase();
EditText etName = (EditText)findViewById(R.id.et_name);
EditText etPhone = (EditText)findViewById(R.id.et_phone);
wdb.execSQL("insert into user(name,phone) values(?,?)", new Object[]{etName.getText().toString(),etPhone.getText().toString()});
wdb.close();
}
public void delete(View v){
SQLiteDatabase wdb = myOpenHelper.getWritableDatabase();
EditText etName = (EditText)findViewById(R.id.et_name);
wdb.execSQL("delete from user where name=?", new Object[]{etName.getText().toString()});
wdb.close();
}
public void update(View v){
SQLiteDatabase wdb = myOpenHelper.getWritableDatabase();
EditText etName = (EditText)findViewById(R.id.et_name);
EditText etPhone = (EditText)findViewById(R.id.et_phone);
wdb.execSQL("update user set phone=? where name=?", new Object[]{etPhone.getText().toString(),etName.getText().toString()});
wdb.close();
}
public void select(View v){
SQLiteDatabase wdb = myOpenHelper.getWritableDatabase();
Cursor cursor = wdb.rawQuery("select * from user", null);
String list = "";
if(cursor != null && cursor.getCount() > 0){
while (cursor.moveToNext()) {
String id = cursor.getString(cursor.getColumnIndex("_id"));
int i = cursor.getColumnIndex("name");
String name = cursor.getString(i);
String phone = cursor.getString(cursor.getColumnIndex("phone"));
list += "{id="+id+",name="+name+",phone="+phone+"},";
}
}
TextView tVlist = (TextView)findViewById(R.id.tv_list);
tVlist.setText(list);
}
}
2,使用AndroidAPI操作数据库。
步骤同上面,所使用Android自带相关API
package com.xiaohui.createdb;
import com.xiaohui.createdb4API.R;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
public class MainActivity extends Activity {
private MyOpenHelper myOpenHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
myOpenHelper = new MyOpenHelper(getApplicationContext());
}
public void insert(View v){
SQLiteDatabase wdb = myOpenHelper.getWritableDatabase();
EditText etName = (EditText)findViewById(R.id.et_name);
EditText etPhone = (EditText)findViewById(R.id.et_phone);
// wdb.execSQL("insert into user(name,phone) values(?,?)", new Object[]{etName.getText().toString(),etPhone.getText().toString()});
ContentValues values = new ContentValues();
values.put("name", etName.getText().toString());
values.put("phone", etPhone.getText().toString());
long insert = wdb.insert("user", null, values );
wdb.close();
if(insert>0){
Toast.makeText(getApplicationContext(), "新增成功", 1).show();
}else{
Toast.makeText(getApplicationContext(), "新增失败", 1).show();
}
}
public void delete(View v){
SQLiteDatabase wdb = myOpenHelper.getWritableDatabase();
EditText etName = (EditText)findViewById(R.id.et_name);
// wdb.execSQL("delete from user where name=?", new Object[]{etName.getText().toString()});
int delete = wdb.delete("user", "name=?", new String[]{etName.getText().toString()});
wdb.close();
Toast.makeText(getApplicationContext(), "删除了"+delete+"条", 1).show();
}
public void update(View v){
SQLiteDatabase wdb = myOpenHelper.getWritableDatabase();
EditText etName = (EditText)findViewById(R.id.et_name);
EditText etPhone = (EditText)findViewById(R.id.et_phone);
// wdb.execSQL("update user set phone=? where name=?", new Object[]{etPhone.getText().toString(),etName.getText().toString()});
ContentValues values = new ContentValues();
values.put("phone", etPhone.getText().toString());
int update = wdb.update("user", values , "name=?", new String[]{etName.getText().toString()});
wdb.close();
Toast.makeText(getApplicationContext(), "修改了"+update+"条", 1).show();
}
public void select(View v){
SQLiteDatabase wdb = myOpenHelper.getWritableDatabase();
// Cursor cursor = wdb.rawQuery("select * from user", null);
EditText etName = (EditText)findViewById(R.id.et_name);
Cursor cursor = wdb.query("user", null,"name != ?", new String[]{etName.getText().toString()}, null, null, null);
String list = "";
if(cursor != null && cursor.getCount() > 0){
while (cursor.moveToNext()) {
String id = cursor.getString(cursor.getColumnIndex("_id"));
int i = cursor.getColumnIndex("name");
String name = cursor.getString(i);
String phone = cursor.getString(cursor.getColumnIndex("phone"));
list += "{id="+id+",name="+name+",phone="+phone+"},";
}
}
TextView tVlist = (TextView)findViewById(R.id.tv_list);
tVlist.setText(list);
}
}
3,SQLite数据库事务控制
文档中标准的格式描述如下
Here is the standard idiom for transactions:
db.beginTransaction();
try {
...
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
package com.xiaohui.transation;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class MyDBHelper extends SQLiteOpenHelper {
public MyDBHelper(Context context) {
super(context, "Account.db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table account(idCard carchar(20),name varchar(20),money varchar(15))");
db.execSQL("insert into account(idCard,name,money) values(?,?,?)",new String[]{"111","张三","2000"});
db.execSQL("insert into account(idCard,name,money) values(?,?,?)",new String[]{"222","李四","3000"});
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
package com.xiaohui.transation;
import android.app.Activity;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Toast;
public class MainActivity extends Activity {
SQLiteDatabase db;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
MyDBHelper dbHelper = new MyDBHelper(getApplicationContext());
db = dbHelper.getReadableDatabase();
}
public void trans(View v) {
db.beginTransaction();
try {
db.execSQL("update account set money=money-100 where idCard=?",new String[]{"1111"});
if(Math.random()*10 > 5){
throw new Exception();
}
db.execSQL("update account set money=money+100 where idCard=?",new String[]{"2222"});
db.setTransactionSuccessful();
Toast.makeText(getApplicationContext(), "转账成功", 1).show();
}catch(Exception e){
Toast.makeText(getApplicationContext(), "转账失败", 1).show();
} finally {
db.endTransaction();
}
}
}
4,使用命令行查看数据
在class配置了Android的SDK环境后使用相关命令:adb shell;sqlite3;以及标准SQL进行数据库操作。