android 数据库操作

直奔主题，android要想使用数据库SQLite，那就得写一个工具类来继承自SQLiteOpenhelper,并实现onCreate方法。并且编写要用的数据库操作如增删改查。

直接上代码了

package com.icedcap.dbtest;

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;
import android.util.Log;

public class DbHelper extends SQLiteOpenHelper {

	private static final String TAG = "DBtest";
	private static final String TABLE1 = "person";
	private SQLiteDatabase db = null;
	private Cursor cursor = null;

	public DbHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}

	// 建表
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		this.db = db;
		db.execSQL("CREATE TABLE " + TABLE1 + " (" + "id" + " text not null, "
				+ "name" + " text not null, " + "age" + " text" + ");");
	}

	// 增
	public void insert(ContentValues values) {
		SQLiteDatabase db = getWritableDatabase();
		System.out.println("values" + values.get("id") + values.get("name")
				+ values.get("age"));
		db.insert(TABLE1, null, values);
		Log.i(TAG, "增加一行");
		db.close();
	}

	// 删除某一行
	public void delete(int id) {
//		if (db == null) {
		SQLiteDatabase db = getWritableDatabase();
//		}
		db.delete(TABLE1, "id=?", new String[] { String.valueOf(id) });
		Log.i(TAG, "删除一行");
	}

	// 更新某一行
	public void update(ContentValues values, int id) {
		SQLiteDatabase db = getWritableDatabase();
		db.update(TABLE1, values, "id=?", new String[] { String.valueOf(id) });
		db.close();
		Log.i(TAG, "更新一行");
	}

	// 按id查询
	public Cursor query(int id) {
		SQLiteDatabase db = getWritableDatabase();
		System.out.println("id---->" + id);
		cursor = db.query(TABLE1, null, "id=?",
				new String[] { String.valueOf(id) }, null, null, null);
		Log.i(TAG, "按id查询一行");
		return cursor;
	}

	public Cursor query() {
		SQLiteDatabase db = getWritableDatabase();
		cursor = db.query(TABLE1, null, null, null, "id", null, null);
		Log.i(TAG, "查询所有");
		return cursor;
	}

	// 按sql语句操作数据库
	public void handleBySql(String sql) {
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL(sql);
		Log.i(TAG, "执行sql语句");
	}

	// 关闭数据库
	public void close() {
		if (db != null) {
			db.close();
			db = null;
		}
		if (cursor != null) {
			cursor.close();
			cursor = null;
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}

}

在主activity中调用工具类

package com.icedcap.dbtest;

import android.annotation.SuppressLint;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.graphics.Color;
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;

@SuppressLint("NewApi")
public class MainActivity extends Activity implements OnClickListener {

	private Button add, delete, update, query, exec, display;
	private EditText edittext;
	private TextView textview;
	private static final String DB = "ddd";
	private DbHelper dbHelper = null;

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		dbHelper = new DbHelper(this, DB, null, 1);
		initView();
	}

	private void initView() {
		add = (Button) findViewById(R.id.add);
		add.setOnClickListener(this);
		delete = (Button) findViewById(R.id.delete);
		delete.setOnClickListener(this);
		update = (Button) findViewById(R.id.update);
		update.setOnClickListener(this);
		query = (Button) findViewById(R.id.query);
		query.setOnClickListener(this);
		exec = (Button) findViewById(R.id.exec);
		exec.setOnClickListener(this);
		display = (Button) findViewById(R.id.display);
		display.setOnClickListener(this);
		edittext = (EditText) findViewById(R.id.edittext);
		textview = (TextView) findViewById(R.id.textview);
	}

	@Override
	public void onClick(View v) {
		// TODO Auto-generated method stub
		switch (v.getId()) {
		case R.id.add:
			insert();
			break;
		case R.id.delete:
			delete();
			break;
		case R.id.update:
			update();
			break;
		case R.id.query:
			query();
			break;
		case R.id.exec:
			exec();
			break;
		case R.id.display:
			display();
			break;
		default:
			break;
		}
	}

	// 插入操作需要按顺序填写各个字段的内容并用，隔开
	@SuppressLint("NewApi")
	private void insert() {
		// dbHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		String[] text = edittext.getText().toString().split(",");
		String id = text[0];
		System.out.println("id+++>" + id);
		if (id != null && !id.isEmpty()) {
			String name = text[1];
			String age = text[2];
			values.put("id", id);
			values.put("name", name);
			values.put("age", age);
			dbHelper.insert(values);
		} else {
			textview.setText("请输入正确的执行条件！。。。");
			textview.setTextColor(Color.RED);
		}

	}

	// 删除某一行需要填写id
	private void delete() {
		String id = edittext.getText().toString();
		if (id != null && !id.isEmpty()) {
			dbHelper.delete(Integer.parseInt(id));
		} else {
			textview.setText("请输入正确的执行条件！。。。");
			textview.setTextColor(Color.RED);
		}

	}

	// 执行具体的sql语句
	private void exec() {
		String sql = edittext.getText().toString();
		if (sql != null && !sql.isEmpty()) {
			dbHelper.handleBySql(sql);
		} else {
			textview.setText("请输入正确的执行条件！。。。");
			textview.setTextColor(Color.RED);
		}

	}

	// 修改操作需要按顺序填写各个字段的内容并用，隔开并且最后要填写修改某一行的id
	private void update() {
		ContentValues values = new ContentValues();
		String[] text = edittext.getText().toString().split(",");
		String id = text[0];

		if (id != null && !id.isEmpty()) {
			String name = text[1];
			String age = text[2];
			int moId = Integer.parseInt(text[3]);
			values.put("id", id);
			values.put("name", name);
			values.put("age", age);
			dbHelper.update(values, moId);
		} else {
			textview.setText("请输入正确的执行条件！。。。");
			textview.setTextColor(Color.RED);
		}

	}

	// 按id查询某一行
	private void query() {
		String selectid = edittext.getText().toString();
		if (selectid != null && !selectid.isEmpty()) {
			Cursor cursor = dbHelper.query(Integer.parseInt(selectid));
			StringBuffer text = new StringBuffer();
			if (cursor.moveToFirst()) {
				while (!cursor.isAfterLast()) {
					// cursor.move(i);
					String id = cursor.getString(cursor.getColumnIndex("id"));
					String name = cursor.getString(cursor
							.getColumnIndex("name"));
					String age = cursor.getString(cursor.getColumnIndex("age"));
					String message = "id: " + id + " name: " + name + " age: "
							+ age;
					text.append(message + "\n");
					cursor.moveToNext();
				}
				// for (int i = 0; i < cursor.getCount(); i++) {}
			}
			textview.setText(text);
			textview.setTextColor(Color.BLACK);
		} else {
			textview.setText("请输入正确的执行条件！。。。");
			textview.setTextColor(Color.RED);
		}

	}

	// 显示所有数据
	private void display() {
		// 解析游标
		Cursor cursor = dbHelper.query();
		StringBuffer text = new StringBuffer();
		if (cursor.moveToFirst()) {
			while (!cursor.isAfterLast()) {
				// cursor.move(i);
				String id = cursor.getString(cursor.getColumnIndex("id"));
				String name = cursor.getString(cursor.getColumnIndex("name"));
				String age = cursor.getString(cursor.getColumnIndex("age"));
				String message = "id: " + id + " name: " + name + " age: "
						+ age;
				text.append(message + "\n");
				cursor.moveToNext();
			}
			// for (int i = 0; i < cursor.getCount(); i++) {}
		}
		textview.setText(text);
		textview.setTextColor(Color.BLACK);
	}

	@Override
	protected void onDestroy() {
		// TODO Auto-generated method stub
		super.onDestroy();
		if (dbHelper != null) {
			dbHelper.close();
			dbHelper = null;
		}
	}

}

最后效果图：

总结：

数据库操作有两种方法一是直接通过sql语句调用execSQL（sql）来操作。第二种就是直接调用API给出的具体操作方法。

数据库的通信是要有开关的本操作是具体情况而定的。

游标的解析要通过while (!cursor.isAfterLast())来遍历，否则会出现越界异常。

点击下载demo