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}// 建表@Overridepublic void onCreate(SQLiteDatabase db) {// TODO Auto-generated method stubthis.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;}}@Overridepublic 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;@Overrideprotected 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);}@Overridepublic void onClick(View v) {// TODO Auto-generated method stubswitch (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);}}// 删除某一行需要填写idprivate 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);}}// 修改操作需要按顺序填写各个字段的内容并用，隔开并且最后要填写修改某一行的idprivate 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);}@Overrideprotected void onDestroy() {// TODO Auto-generated method stubsuper.onDestroy();if (dbHelper != null) {dbHelper.close();dbHelper = null;}}}

最后效果图：

总结：

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

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

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

点击下载demo