SQLITE最基本操作

SQLite数据库的基本操作

main.xml(主布局)

android:layout_width="fill_parent"

android:layout_height="fill_parent"

android:orientation="vertical">

android:layout_width="fill_parent"

android:layout_height="wrap_content"

android:orientation="horizontal"

android:padding="5dp"

>

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="姓名"

/>

android:id="@+id/edName"

android:layout_width="fill_parent"

android:layout_height="wrap_content"

/>

android:layout_width="fill_parent"

android:layout_height="wrap_content"

android:orientation="horizontal"

android:padding="5dp"

>

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="年龄"

/>

android:id="@+id/edAge"

android:layout_width="fill_parent"

android:layout_height="wrap_content"

/>

android:layout_width="fill_parent"

android:layout_height="wrap_content"

android:orientation="horizontal"

android:padding="5dp"

android:gravity="center"

>

android:id="@+id/btnInsert"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="添加"

/>

android:id="@+id/btnDelete"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="删除"

/>

android:id="@+id/btnUpdate"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="修改"

/>

android:id="@+id/btnSelect"

android:layout_width="wrap_content"

android:layout_height="wrap_content"

android:text="查询"

/>

android:id="@+id/listView"

android:layout_width="fill_parent"

android:layout_height="wrap_content"

android:padding="5dp"

/>

listitem.xml(行布局)

android:layout_width="match_parent"

android:layout_height="match_parent"

android:orientation="horizontal"

android:padding="5dp">

android:id="@+id/id"

android:layout_width="80dp"

android:layout_height="wrap_content"

/>

android:id="@+id/name"

android:layout_width="80dp"

android:layout_height="wrap_content"

/>

android:id="@+id/age"

android:layout_width="80dp"

android:layout_height="wrap_content"

/>

DBHelper.java(辅助类)

package spl.db.sqlite;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper{

//创建构造器

public DBHelper(Context context){

super(context,"contactinfo.db",null,1);

//contactinfo.db-数据库名,null-游标工厂,1-版本...下课休息...

}

@Override

public void onCreate(SQLiteDatabase db){

//建表

db.execSQL("create table person(_id integer primary key autoincrement,name varchar,age integer)");

}

@Override

public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){ //版本升级

}

}

DBDemoActivity.java(主界面)

package spl.db.sqlite;

import java.util.ArrayList;

import java.util.HashMap;

import android.app.Activity;

import android.content.ContentValues;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.os.Bundle;

import android.util.Log;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.EditText;

import android.widget.ListView;

import android.widget.SimpleAdapter;

public class DBDemoActivity extends Activity{

/**Called when the activity is first created.*/

//声明控件对象

EditText edName,edAge;

Button btnInsert,btnDelete,btnUpdate,btnSelect;

ListView listView;

@Override

public void onCreate(Bundle savedInstanceState){ super.onCreate(savedInstanceState);

setContentView(https://www.360docs.net/doc/116441555.html,yout.main);

//Fragment

//初始化控件对象

edName=(EditText)findViewById(R.id.edName);

edAge=(EditText)findViewById(R.id.edAge);

btnInsert=(Button)findViewById(R.id.btnInsert);

btnDelete=(Button)findViewById(R.id.btnDelete);

btnUpdate=(Button)findViewById(R.id.btnUpdate);

btnSelect=(Button)findViewById(R.id.btnSelect);

listView=(ListView)findViewById(R.id.listView);

//给按钮添加监听事件

btnInsert.setOnClickListener(btnListener);

btnDelete.setOnClickListener(btnListener);

btnUpdate.setOnClickListener(btnListener);

btnSelect.setOnClickListener(btnListener);

selectAll();//全查询

}

private OnClickListener btnListener=new OnClickListener(){

@Override

public void onClick(View v){

//判断是哪一个按钮被点击

switch(v.getId()){

case R.id.btnInsert:

insert();

selectAll();

break;

case R.id.btnDelete:

delete();

selectAll();

break;

case R.id.btnUpdate:

update();

selectAll();

break;

case R.id.btnSelect:

select();

break;

default:

break;

}

}

};

//增

protected void insert(){

DBHelper helper=new DBHelper(this);

SQLiteDatabase db=helper.getReadableDatabase();

ContentValues values=new ContentValues();//HashMap

String name=edName.getText().toString().trim();

String age=edAge.getText().toString().trim();

values.put("name",name);

values.put("age",age);

db.insert("person",null,values);//"person"-表名,null-空值列,values

db.close();//关闭连接

}

//删

protected void delete(){

DBHelper helper=new DBHelper(this);

SQLiteDatabase db=helper.getReadableDatabase();

String name=edName.getText().toString();

int row=db.delete("person","name=?",new String[]{name});

//"person"-表名,"name=?"-where模板,new String[]{name}-占位符的值

Log.i("spl","delete row="+row);

db.close();

}

//改

protected void update(){

DBHelper helper=new DBHelper(this);

SQLiteDatabase db=helper.getReadableDatabase();

ContentValues values=new ContentValues();

String name=edName.getText().toString();

String age=edAge.getText().toString();

values.put("age",age);

int row=db.update("person",

values,

"name=?",//where模板

new String[]{name});//-占位符的值

Log.i("spl","update row="+row);

db.close();//关闭连接

}

//查询

protected void select(){

ArrayList>list=new

ArrayList>();

DBHelper helper=new DBHelper(this);

SQLiteDatabase db=helper.getReadableDatabase();

String age=edAge.getText().toString();

Cursor cursor=db.query(

"person",//表名

new String[]{"_id","name","age"},//列名

"age=?"//where模板

,new String[]{age},//-占位符的值

null,//分组

null,//having

null);//排序"age desc"

//解析结果集

while(cursor.moveToNext()){//移到下一行

HashMapmap=new HashMap();

map.put("id",cursor.getString(0));

map.put("name",cursor.getString(1));

map.put("age",cursor.getString(2));

list.add(map);

}

cursor.close();

db.close();

//UI更新

listView.setAdapter(new SimpleAdapter(

this,

list,https://www.360docs.net/doc/116441555.html,yout.listitem,

new String[]{"id","name","age"},

new int[]{R.id.id,https://www.360docs.net/doc/116441555.html,,R.id.age}

));

}

//全查询

protected void selectAll(){

ArrayList>list=new

ArrayList>();

DBHelper helper=new DBHelper(this);

SQLiteDatabase db=helper.getReadableDatabase();

Cursor cursor=db.rawQuery("select*from person",null);

while(cursor.moveToNext()){

HashMapmap=new HashMap();

map.put("id",cursor.getString(0));

map.put("name",cursor.getString(1));

map.put("age",cursor.getString(2));

list.add(map);

}

cursor.close();

db.close();

listView.setAdapter(new SimpleAdapter(

this,

list,https://www.360docs.net/doc/116441555.html,yout.listitem,

new String[]{"id","name","age"},

new int[]{R.id.id,https://www.360docs.net/doc/116441555.html,,R.id.age}

));

}

}