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*/@Overridepublic void onCreate(SQLiteDatabase db) {String sql = "create table user(_id integer primary key autoincrement,name varchar(20),phone varchar(12))";db.execSQL(sql);}/*** 当数据库版本升级的时候调用*/@Overridepublic 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;@Overrideprotected 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;@Overrideprotected 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);}@Overridepublic 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"});}@Overridepublic 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;@Overrideprotected 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进行数据库操作。