Android(安卓)数据库SQLite的使用简单Demo

在手机开发中SQLite的使用demo

简单的bean类

Java代码

packagecom.easyway.android.sql;
/**
*普通JavaBean
*@authorlonggangbai
*
*/
publicclassCityBean{
publicstaticfinalStringID="_id";
publicstaticfinalStringCITY="city";
publicstaticfinalStringCODE="code";
privateStringid;
privateStringcity;
privateStringcode;
publicStringgetId(){
returnid;
}
publicvoidsetId(Stringid){
this.id=id;
}
publicStringgetCity(){
returncity;
}
publicvoidsetCity(Stringcity){
this.city=city;
}
publicStringgetCode(){
returncode;
}
publicvoidsetCode(Stringcode){
this.code=code;
}
}

界面展现类：

Java代码

packagecom.easyway.android.sql;
importjava.util.ArrayList;
importjava.util.List;
importandroid.app.Activity;
importandroid.content.ContentValues;
importandroid.database.Cursor;
importandroid.database.sqlite.SQLiteDatabase;
importandroid.os.Bundle;
importandroid.view.View;
importandroid.view.ViewGroup;
importandroid.widget.AdapterView;
importandroid.widget.BaseAdapter;
importandroid.widget.Button;
importandroid.widget.EditText;
importandroid.widget.ListView;
importandroid.widget.TextView;
/**
*
*
*本例实现SQLite数据库增加、删除、修改、模糊查询操作。这里不是最好的实现方法,
*如想研究SQL如何封装，请详细查看SQLiteDatebase类.
*查看SQL语句：Stringsql=SQLiteQueryBuilder.buildQueryString();
*
*
*@authorlonggangbai
*/
publicclassAndroidSQLextendsActivity{
privatestaticStringDB_NAME="mycity.db";
privatestaticintDB_VERSION=1;
privatestaticintPOSTION;
privateListViewlistview;
privateCursorcursor;
privateSQLiteDatabasedb;
privateSQLiteHelperdbHelper;
privateListAdapterlistAdapter;
privateEditTextetCity;
privateEditTextetCode;
privateButtonbt_add;
privateButtonbt_modify;
privateButtonbt_query;
privateList<CityBean>cityList=newArrayList<CityBean>();
/**Calledwhentheactivityisfirstcreated.*/
@Override
publicvoidonCreate(BundlesavedInstanceState){
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
etCity=(EditText)findViewById(R.id.etCity);
etCode=(EditText)findViewById(R.id.etCode);
bt_add=(Button)findViewById(R.id.bt_add);
bt_modify=(Button)findViewById(R.id.bt_modify);
bt_query=(Button)findViewById(R.id.bt_query);
try{
/*初始化并创建数据库*/
dbHelper=newSQLiteHelper(this,DB_NAME,null,DB_VERSION);
/*创建表*/
db=dbHelper.getWritableDatabase();//调用SQLiteHelper.OnCreate()
/*查询表，得到cursor对象*/
cursor=db.query(SQLiteHelper.TB_NAME,null,null,null,null,null,CityBean.CODE+"DESC");
cursor.moveToFirst();
while(!cursor.isAfterLast()&&(cursor.getString(1)!=null)){
CityBeancity=newCityBean();
city.setId(cursor.getString(0));
city.setCity(cursor.getString(1));
city.setCode(cursor.getString(2));
cityList.add(city);
cursor.moveToNext();
}
}catch(IllegalArgumentExceptione){
//当用SimpleCursorAdapter装载数据时，表ID列必须是_id，否则报错column'_id'doesnotexist
e.printStackTrace();
//当版本变更时会调用SQLiteHelper.onUpgrade()方法重建表注：表以前数据将丢失
++DB_VERSION;
dbHelper.onUpgrade(db,--DB_VERSION,DB_VERSION);
//dbHelper.updateColumn(db,SQLiteHelper.ID,"_"+SQLiteHelper.ID,"integer");
}
listview=(ListView)findViewById(R.id.listView);
listAdapter=newListAdapter();
listview.setAdapter(listAdapter);
listview.setOnItemClickListener(newListView.OnItemClickListener(){
@Override
publicvoidonItemClick(AdapterView<?>parent,Viewview,intpostion,
longarg3){
setSelectedValues(postion);
}
});
/*插入表数据并ListView显示更新*/
bt_add.setOnClickListener(newButton.OnClickListener(){
@Override
publicvoidonClick(Viewarg0){
if(etCity.getText().length()>1&&etCode.getText().length()>1){
ContentValuesvalues=newContentValues();
values.put(CityBean.CITY,etCity.getText().toString().trim());
values.put(CityBean.CODE,etCode.getText().toString().trim());
//插入数据用ContentValues对象也即HashMap操作,并返回ID号
LongcityID=db.insert(SQLiteHelper.TB_NAME,CityBean.ID,values);
CityBeancity=newCityBean();
city.setId(""+cityID);
city.setCity(etCity.getText().toString().trim());
city.setCode(etCode.getText().toString().trim());
cityList.add(city);
listview.setAdapter(newListAdapter());
resetForm();
}
}
});
/*查询表，模糊条件查询*/
bt_query.setOnClickListener(newButton.OnClickListener(){
@Override
publicvoidonClick(Viewview){
cityList.removeAll(cityList);
Stringsql=null;
StringsqlCity=etCity.getText().length()>0?CityBean.CITY+"like'%"+etCity.getText().toString().trim()+"%'":"";
StringsqlCode=etCode.getText().length()>0?CityBean.CITY+"like'%"+etCity.getText().toString().trim()+"%'":"";
if((!"".equals(sqlCity))&&(!"".equals(sqlCode))){
sql=sqlCity+"and"+sqlCode;
}elseif(!"".equals(sqlCity)){
sql=sqlCity;
}elseif(!"".equals(sqlCode)){
sql=sqlCode;
}
cursor=db.query(true,SQLiteHelper.TB_NAME,
newString[]{CityBean.ID,CityBean.CITY,CityBean.CODE},
sql,
null,null,null,null,null);
cursor.moveToFirst();
while(!cursor.isAfterLast()&&(cursor.getString(1)!=null)){
CityBeancity=newCityBean();
city.setId(cursor.getString(0));
city.setCity(cursor.getString(1));
city.setCode(cursor.getString(2));
cityList.add(city);
cursor.moveToNext();
}
listview.setAdapter(newListAdapter());
resetForm();
}
});
/*修改表数据*/
bt_modify.setOnClickListener(newButton.OnClickListener(){
@Override
publicvoidonClick(Viewarg0){
ContentValuesvalues=newContentValues();
values.put(CityBean.CITY,etCity.getText().toString().trim());
values.put(CityBean.CODE,etCode.getText().toString().trim());
db.update(SQLiteHelper.TB_NAME,values,CityBean.ID+"="+cityList.get(POSTION).getId(),null);
cityList.get(POSTION).setCity(etCity.getText().toString().trim());
cityList.get(POSTION).setCode(etCode.getText().toString().trim());
listview.setAdapter(newListAdapter());
resetForm();
}
});
}
/*设置选中ListView的值*/
publicvoidsetSelectedValues(intpostion){
POSTION=postion;
etCity.setText(cityList.get(postion).getCity());
etCode.setText(cityList.get(postion).getCode());
}
/*重值form*/
publicvoidresetForm(){
etCity.setText("");
etCode.setText("");
}
@Override
protectedvoidonDestroy(){
db.delete(SQLiteHelper.TB_NAME,null,null);
super.onDestroy();
}
privateclassListAdapterextendsBaseAdapter{
publicListAdapter(){
super();
}
@Override
publicintgetCount(){
returncityList.size();
}
@Override
publicObjectgetItem(intpostion){
returnpostion;
}
@Override
publiclonggetItemId(intpostion){
returnpostion;
}
@Override
publicViewgetView(finalintpostion,Viewview,ViewGroupparent){
view=getLayoutInflater().inflate(R.layout.listview,null);
TextViewtv=(TextView)view.findViewById(R.id.tvCity);
tv.setText(""+cityList.get(postion).getCity());
TextViewbu=(TextView)view.findViewById(R.id.btRemove);
bu.setText(R.string.delete);
bu.setId(Integer.parseInt(cityList.get(postion).getId()));
/*删除表数据*/
bu.setOnClickListener(newButton.OnClickListener(){
@Override
publicvoidonClick(Viewview){
try{
db.delete(SQLiteHelper.TB_NAME,CityBean.ID+"="+view.getId(),null);
cityList.remove(postion);
listview.setAdapter(newListAdapter());
}catch(Exceptione){
e.printStackTrace();
}
}
});
returnview;
}
}
}

SQLite实现类：

Java代码

packagecom.easyway.android.sql;
importandroid.content.Context;
importandroid.database.sqlite.SQLiteDatabase;
importandroid.database.sqlite.SQLiteOpenHelper;
importandroid.database.sqlite.SQLiteDatabase.CursorFactory;
/**
*实现对表的创建、更新、变更列名操作
*
*在Android中针对少量数据的操作在SQLite操作实现相关功能功能
*，但是必须继承SQLiteOpenHelper，实现相关的功能。
*
*
*@authorlonggangbai
*
*/
publicclassSQLiteHelperextendsSQLiteOpenHelper{
publicstaticfinalStringTB_NAME="citys";
publicSQLiteHelper(Contextcontext,Stringname,CursorFactoryfactory,
intversion){
super(context,name,factory,version);
}
/**
*创建新表
*/
@Override
publicvoidonCreate(SQLiteDatabasedb){
db.execSQL("CREATETABLEIFNOTEXISTS"+
TB_NAME+"("+
CityBean.ID+"integerprimarykey,"+
CityBean.CITY+"varchar,"+
CityBean.CODE+"integer"+
")");
}
/**
*当检测与前一次创建数据库版本不一样时，先删除表再创建新表
*/
@Override
publicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion){
db.execSQL("DROPTABLEIFEXISTS"+TB_NAME);
onCreate(db);
}
/**
*变更列名
*@paramdb
*@paramoldColumn
*@paramnewColumn
*@paramtypeColumn
*/
publicvoidupdateColumn(SQLiteDatabasedb,StringoldColumn,StringnewColumn,StringtypeColumn){
try{
db.execSQL("ALTERTABLE"+
TB_NAME+"CHANGE"+
oldColumn+""+newColumn+
""+typeColumn
);
}catch(Exceptione){
e.printStackTrace();
}
}
}

AndroidSQL.rar (90.7 KB)

更多相关文章

随机推荐