用Sqlite数据库来对Android页面的数据进行增删改查

Sqlite是一款轻型的数据库，它包含在一个相对小的C库中，它的设计目标是嵌入式的，由于它占用资源非常少，所以在Android开发中经常使用。在Android中要想使用Sqlite数据库，首先应该创建一个类MySQLiteOpenHelper类继承SQLiteOpenHelper类，在这里面要重写四个方法：

package com.example.datastore.db;
import android.content.Context;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class MySQLiteOpenHelper extends SQLiteOpenHelper {
private static final String DB_NAME="test.db";
private static final int DB_VERSION=1;
public static final String NEWS_TAB="news";
public MySQLiteOpenHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, DB_NAME, factory, DB_VERSION);
}
public MySQLiteOpenHelper(Context context) {
this(context,DB_NAME,null,DB_VERSION);
}
//创建数据库
public void onCreate(SQLiteDatabase db) {
StringBuilder createNewsTable = new StringBuilder();
createNewsTable.append("create table ");
createNewsTable.append(NEWS_TAB);
createNewsTable.append("(");
createNewsTable.append(" id integer primary key autoincrement,");
createNewsTable.append(" title varchar(100) ,");
createNewsTable.append(" author varchar(100) ");
createNewsTable.append(")");
db.execSQL(createNewsTable.toString());
}
//版本更新
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//db.execSQL("drop table news");
}
}

在这个类里比较重要的方法是onCreate(SQLiteDatabase db)，它是用来创建数据库的，在每次增删改查之前首先要创建数据库。 onUpgrade()方法是当每次有新版本更新时会调用这个回调函数。

接下来我们就要创建一个实体类的Dao文件，假如要创建一个newsDao，那么在这里面我们可以SQLiteDatabase db=dbOpenHelper.getWritableDatabase();创建一个MySQLiteOpenHelper 对象 ，只有调用了MySQLiteOpenHelper 对象的getReadableDatabase()方法，或者是getWritableDatabase()方法之后，才会创建，或打开一个数据库 。然后对数据库进行增删改查

package com.example.datastore.db;
import java.util.ArrayList;
import com.example.datastore.entity.News;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class NewsDao {
private MySQLiteOpenHelper dbOpenHelper;
public NewsDao(Context context) {
dbOpenHelper=new MySQLiteOpenHelper(context);
}
//新增
public void addNews(String title,String author){
SQLiteDatabase db=dbOpenHelper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("title",title);
values.put("author",author);
db.insert(MySQLiteOpenHelper.NEWS_TAB, null, values);
db.close();
}
//更新
public void upadteNews(int id,String title,String author){
SQLiteDatabase db=dbOpenHelper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put("title", title);
values.put("author", author);
db.update(MySQLiteOpenHelper.NEWS_TAB, values, " id=?", new String[] {id+""});
db.close();
}
//删除
public void delete(int id){
SQLiteDatabase db=dbOpenHelper.getWritableDatabase();
//ContentValues values=new ContentValues();
db.delete(MySQLiteOpenHelper.NEWS_TAB, " id=?", new String[] {id+""});
}
//查询
public ArrayList<News> findNews(){
ArrayList<News> list=new ArrayList<News>();
SQLiteDatabase db=dbOpenHelper.getReadableDatabase();
Cursor cursor=db.query(MySQLiteOpenHelper.NEWS_TAB,new String[] {"id","title","author"}, null
, null, null,null, null);
while(cursor.moveToNext()){
int id=cursor.getInt(cursor.getColumnIndex("id"));
String title=cursor.getString(cursor.getColumnIndex("title"));
String author=cursor.getString(cursor.getColumnIndex("author"));
News n=new News();
n.setId(id);
n.setTitle(title);
n.setAuthor(author);
list.add(n);
}
cursor.close();
db.close();
return list;
}
}

最后，在Activity文件中，通过不同的操作然后调用Dao里的增删改查方法，从而实现对数据库数据的操作。

package com.example.datastore;
import java.util.ArrayList;
import com.example.datastore.db.NewsDao;
import com.example.datastore.entity.News;
import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Toast;
public class DBActivity extends Activity implements OnClickListener{
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.dblayout);
findViewById(R.id.db_creat).setOnClickListener(this);
findViewById(R.id.db_insert).setOnClickListener(this);
findViewById(R.id.db_delete).setOnClickListener(this);
findViewById(R.id.db_update).setOnClickListener(this);
findViewById(R.id.db_query).setOnClickListener(this);
}
@Override
public void onClick(View v) {
NewsDao newsDao=new NewsDao(this);
switch(v.getId()){
case R.id.db_creat:

break;
case R.id.db_insert:
newsDao.addNews("zszszszs","xmxm");
break;
case R.id.db_delete:
newsDao.delete(1);
break;
case R.id.db_update:
newsDao.upadteNews(1, "qqqqqqqqqqqqqqqqq", "xm");
break;
case R.id.db_query:
ArrayList<News> list=newsDao.findNews();
StringBuilder sb=new StringBuilder();
for(News n:list){
sb.append(n.getId()).append(n.getTitle()).append(n.getAuthor());
}
Toast.makeText(this, sb.toString(), Toast.LENGTH_LONG).show();
break;
default:
break;
}
}
}