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进行数据库操作。