android 存储方式（三）SQLiteDatabase

数据库是Android 中常用的功能，这里直接贴出代码：

activit_main.xml：

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity" >

    <EditText
        android:id="@+id/name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />
    
    <EditText
        android:id="@+id/address"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />
    
    <EditText
        android:id="@+id/sex"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" />

    <TextView
        android:id="@+id/show"
        android:gravity="center"
        android:layout_width="match_parent"
        android:layout_height="70sp" />

    <Button
        android:id="@+id/insert"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="插入" />

    <Button
        android:id="@+id/update"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="更新" />

    <Button
        android:id="@+id/select"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="查询" />

    <Button
        android:id="@+id/delete"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="删除" />

</LinearLayout>


MainActivity.java:
<pre name="code" class="java">package com.example.database;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
import android.app.Activity;

public class MainActivity extends Activity {

	private TextView show;
	private Button insertBtn, updateBtn, selectBtn, deleteBtn;
	private EditText editName, editAddress, editSex;
	SqliteDatabaseControl database;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);

		database = new SqliteDatabaseControl(this);
		initView();
		insertBtn.setOnClickListener(new OnClickListener() {

			@Override
			public void onClick(View v) {

				database.insertData(editName.getText().toString(), editAddress
						.getText().toString());
			}
		});
		updateBtn.setOnClickListener(new OnClickListener() {

			@Override
			public void onClick(View v) {

				database.updateData(editAddress.getText().toString(), editName
						.getText().toString());
			}
		});

		deleteBtn.setOnClickListener(new OnClickListener() {

			@Override
			public void onClick(View v) {

				database.deleteData(editName.getText().toString());
			}
		});

		selectBtn.setOnClickListener(new OnClickListener() {

			@Override
			public void onClick(View v) {

				List<String> list = new ArrayList<String>();
				list = database.listPersonString();

				if (list.size() != 0) {
					show.setText(list.toString());
				}

				//这里需要将name 的edittext填写出来，不然出错
				Toast.makeText(
						MainActivity.this,
						database.listPerson(
								new String[] { editName.getText().toString() })
								.toString(), Toast.LENGTH_LONG).show();
			}
		});
	}

	private void initView() {
		show = (TextView) findViewById(R.id.show);
		editName = (EditText) findViewById(R.id.name);
		editAddress = (EditText) findViewById(R.id.address);
		editSex = (EditText) findViewById(R.id.sex);
		insertBtn = (Button) findViewById(R.id.insert);
		updateBtn = (Button) findViewById(R.id.update);
		selectBtn = (Button) findViewById(R.id.select);
		deleteBtn = (Button) findViewById(R.id.delete);
	}

}

当然最主要的还是对数据库操作的类：

package com.example.database;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Address;
import android.util.Log;

/**
 * 数据库存在本应用的包里面（databases文件夹里面） tools:Sqlite export professional
 */
public class SqliteDatabaseControl extends SQLiteOpenHelper {

	private static String name = "test.db";
	private static CursorFactory factory = null;
	private static int version = 1;
	
	public SqliteDatabaseControl(Context context) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		// 支持整形数据类型，字符串类型，日期类型，二进制类型
		String sql = "create table person(id integer primary key autoincrement,name text,address text)";
		db.execSQL(sql);
	}

	// 版本更新的时候使用该方法
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// 向表里添加一个字段
		String sql = "alter table myTable add sex varchar(8)";
		db.execSQL(sql);
	}

	// 插入数据
	public void insertData(String name, String address) {

		SQLiteDatabase db = null;
		String sql = "insert into person(name,address) values(?,?)";
		try {
			db = this.getWritableDatabase();//如果要对数据库进行操作，需要通过getWritableDatabase或者getReadableDatabase进行实例化SQLiteDatabase;
			Object[] bindArgs = new Object[] { name, address };
			db.execSQL(sql, bindArgs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (db != null) {
				db.close();
			}
		}
	}

	public void deleteData(String name) {

		SQLiteDatabase db = null;
		try {
			String sql = "delete from person where name = ?";
			Object[] bindArgs = new Object[] { name };
			db = this.getWritableDatabase();
			db.execSQL(sql, bindArgs);
		} catch (Exception e) {

		} finally {
			if (db != null) {
				db.close();
			}
		}
	}

	public void updateData(String address, String name) {

		SQLiteDatabase db = null;
		try {
			String sql = "update person set address = ? where name = ? ";
			Object[] bindArgs = new Object[] { address, name };
			db = this.getWritableDatabase();
			db.execSQL(sql, bindArgs);
		} catch (Exception e) {

		} finally {
			if (db != null) {
				db.close();
			}
		}
	}

	public String viewDatabaseAddress(String[] bindArgs) {
		SQLiteDatabase db = null;
		String address = "";
		try {
			String sql = "select * from person where name = ?";
			db = this.getReadableDatabase();
			Cursor cursor = db.rawQuery(sql, bindArgs);
			for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor
					.moveToNext()) {
				address = cursor.getInt(cursor.getColumnIndex("address"))+"";
			}
			return address;
		} catch (Exception e) {

		} finally {
			if (db != null) {
				db.close();
			}
		}
		return null;
	}

	public List<String> listPersonString() {

		List<String> list = new ArrayList<String>();
		SQLiteDatabase db = null;

		String sql = "select * from person";
		db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		Log.i("test", cursor.getCount()+"");
		try {
			for(cursor.moveToFirst();!cursor.isAfterLast();cursor.moveToNext()){
				String a = cursor.getString(cursor.getColumnIndex("address"));
				list.add(a);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (db != null) {
			db.close();
		}
		return list;
	}
	
	public List<String> listPerson(String[] name) {

		List<String> list = new ArrayList<String>();
		SQLiteDatabase db = null;

		String sql = "select * from person where name = ?";
		db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, name);
		Log.i("test", cursor.getCount()+"");
		try {
			for(cursor.moveToFirst();!cursor.isAfterLast();cursor.moveToNext()){
				String n = cursor.getString(cursor.getColumnIndex("name"));
				String a = cursor.getString(cursor.getColumnIndex("address"));
				list.add(n);
				list.add(a);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (db != null) {
			db.close();
		}
		return list;
	}

}

当然如果你不想用复杂的数据库操作语句，可以这么写：

package com.example.databasetest;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class OperateDatabase extends SQLiteOpenHelper {

	private static String name = "test.db";
	private static CursorFactory factory = null;
	private static int version = 1;

	public OperateDatabase(Context context) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		String sql = "create table person(id integer primary key autoincrement,name text,address text)";
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	public void insertData(String name, String address) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("name", name);
		values.put("address", address);
		db.insert("person", null, values);
	}

	public void deleteData(String name) {
		SQLiteDatabase db = this.getWritableDatabase();
		String whereClause = "name = ?";
		db.delete("person", whereClause, new String[]{name});
	}

	public void updateData(String name, String address) {
		SQLiteDatabase db = this.getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put("address", address);
		String whereClause = "name = ?";
		db.update("person", values, whereClause, new String[]{name});
	}

	public List<String> listPersonString() {

		List<String> list = new ArrayList<String>();
		SQLiteDatabase db = null;

		String sql = "select * from person";
		db = this.getReadableDatabase();
		Cursor cursor = db.query("person", null, null, null, null, null, "name");
		try {
			for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
				String a = cursor.getString(cursor.getColumnIndex("address"));
				String n = cursor.getString(cursor.getColumnIndex("name"));
				list.add("<"+n);
				list.add(a+">");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (db != null) {
			db.close();
		}
		return list;
	}

}

这里仅仅是学习所用，所以代码上的设计并不严密