Android操作SQLite数据库（增、删、改、查、分页等）及ListView显
⽰数据的⽅法详解
本⽂实例讲述了Android操作SQLite数据库(增、删、改、查、分页等)及ListView显⽰数据的⽅法。分享给⼤家供⼤家参考，具体如下：
由于刚接触android开发，故此想把学到的基础知识记录⼀下，以备查询，故此写的⽐较啰嗦：
步骤如下：
⼀、介绍：
此⽂主要是介绍怎么使⽤android⾃带的数据库SQLite，以及把后台的数据⽤ListView控件显⽰
⼆、新建⼀个android⼯程——DBSQLiteOperate
⼯程⽬录：
三、清单列表l的配置为：
<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="schemas.android/apk/res/android"
package="ample.dboperate"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk android:minSdkVersion="8" />
<application
android:icon="@drawable/ic_launcher"
android:label="@string/app_name" >
<!--单元测试加这句-->
<uses-library android:name="st.runner" />
<activity
android:name=".DBSQLiteOperateActivity"
android:label="@string/app_name" >
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>
<instrumentation android:name="st.InstrumentationTestRunner"
android:targetPackage="ample.dboperate"
android:label="Test for my app"/>
</manifest>
四、l配置清单：
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="schemas.android/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="vertical" >
<LinearLayout android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:orientation="horizontal" >
<TextView
android:id="@+id/name"
android:layout_width="100dip"
android:layout_height="wrap_content"
android:text="@string/name"
android:gravity="center"/>
<TextView
android:id="@+id/phone"
android:layout_width="100dip"
android:layout_height="wrap_content"
android:text="@string/phone"
android:gravity="center"/>
<TextView
android:id="@+id/amount"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="@string/amount"
android:gravity="center"/>
</LinearLayout>
<ListView
android:id="@+id/listView"
android:layout_width="fill_parent"
android:layout_height="fill_parent" >
</ListView>
</LinearLayout>
五、l配置清单：
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="schemas.android/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="horizontal" >
<TextView
android:id="@+id/name"
android:layout_width="100dip"
android:layout_height="wrap_content"
android:text="@string/name"
android:gravity="center"/>
<TextView
android:id="@+id/phone"
android:layout_width="100dip"
android:layout_height="wrap_content"
android:text="@string/phone"
android:gravity="center"/>
<TextView
android:id="@+id/amount"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:text="@string/amount"
android:gravity="center"/>
</LinearLayout>
六、l配置清单：
<?xml version="1.0" encoding="utf-8"?>
<resources>
<string name="hello">Hello World, DBSQLiteOperateActivity!</string>
<string name="app_name">ExampleDBSQLiteOperate8</string>
<string name="name">姓名</string>
<string name="phone">电话</string>
<string name="amount">存款</string>
</resources>
七、DBSQLiteOperateActivity.java Activity类的源码：
ample.dboperate;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
ample.adapter.PersonAdapter;
ample.domain.Person;
ample.service.PersonService;
import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.SimpleCursorAdapter;
import android.widget.Toast;
public class DBSQLiteOperateActivity extends Activity {
ListView listView;
PersonService personService;
OnItemClickListener listViewListener;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
setContentView(R.layout.main);
listViewListener = new OnItemClickListener(){
@Override
public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
//得到listView控件
ListView listView = (ListView)parent;
//1、如果使⽤⾃定义适配器，返回的是Person对象
//得到该条⽬数据
//  Person person = (ItemAtPosition(position);
//  //⼴播出去
//  Toast.makeText(getApplicationContext(), String(), Toast.LENGTH_LONG).show();  //2、如果使⽤showList2()⽅法中的适配器时，则取得的值是不⼀样的，返回的是cursor
//  Cursor cursor = (ItemAtPosition(position);
//  int personid = ColumnIndex("_id"));
//  Toast.makeText(getApplicationContext(), personid+"", Toast.LENGTH_LONG).show();
//3、如果使⽤showList()⽅法中的适配器时，则取得的值是不⼀样的，返回的是map
@SuppressWarnings("unchecked")
Map<String,Object> map = (ItemAtPosition(position);
String name = ("name").toString();
String personid = ("personid").toString();
Toast.makeText(getApplicationContext(), personid +"-"+ name, Toast.LENGTH_LONG).show();  }
};
listView = (ListView) this.findViewById(R.id.listView);
listView.setOnItemClickListener(listViewListener);
personService = new PersonService(this);
showList();
}
private void showList() {
List<Person> persons = ScrollData(0, 50);
List<HashMap<String,Object>> data = new ArrayList<HashMap<String,Object>>();
for(Person person : persons){
HashMap<String,Object> item = new HashMap<String,Object>();
item.put("name", Name());
item.put("phone", Phone());
item.put("amount", Amount());
item.put("personid", Id());
data.add(item);
}
SimpleAdapter adapter = new SimpleAdapter(this,data,R.layout.item, new String[]{"name","phone","amount"}, new int[]{R.id.name,R.id.phone,R.id.amount});
listView.setAdapter(adapter);
}
public void showList2(){
Cursor cursor = CursorScrollData(0, 50);
//该适配器要求返回的结果集cursor必须包含_id字段，所以需要对取得结果集进⾏处理
SimpleCursorAdapter adapter = new SimpleCursorAdapter(this,R.layout.item,cursor,new String[]{"name","phone","amount"}, new int[]{R.id.name,R.id.phone,R.id.amount} ); listView.setAdapter(adapter);
}
/**
* ⾃定义适配器
*/
public void showList3(){
List<Person> persons = ScrollData(0, 50);
/**
* 第⼀个参数：上下⽂context，第⼆个参数：要显⽰的数据，第三个参数：绑定的条⽬界⾯
*/
PersonAdapter adapter = new PersonAdapter(this, persons, R.layout.item);
listView.setAdapter(adapter);
}
}
⼋、person.java 实体类源码：
ample.domain;
public class Person {
private Integer id;
private String name;
private String phone;
private Integer amount;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Person(String name, String phone) {
this.name = name;
this.phone = phone;
}
public Person(Integer id, String name, String phone,Integer amount) {
super();
this.id = id;
this.name = name;
this.phone = phone;
this.amount = amount;
}
public Person() {
super();
}
public Integer getAmount() {
return amount;
}
public void setAmount(Integer amount) {
this.amount = amount;
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", phone=" + phone
+ ", amount=" + amount + "]";
}
}
九、DBOperateHelper.java 业务类源码：
ample.service;
t.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DBOperateHelper extends SQLiteOpenHelper {
public DBOperateHelper(Context context) {//默认创建的数据库⽂件保存在<；包名>/database/
//第⼀个参数是上下⽂，第⼆个参数是数据库名称，第三个是游标⼯⼚为null时使⽤数据库默认的游标⼯⼚，第四个是数据库版本号但是不能为0，⼀般⼤于0
super(context, "smallpig", null, 4);
}
/**
* 数据库每⼀次被创建时被调⽤
*/
@Override
public void onCreate(SQLiteDatabase sqldb) {
}
/**
* 每⼀次数据库版本号发⽣变动时触发此⽅法
* ⽐如如果想往数据库中再插⼊⼀些表、字段或者其他信息时通过修改数据库版本号来触发此⽅法
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//db.execSQL("alter table person add phone varchar(12) null");\
}
}
⼗、PersonService.java 业务类源码：
ample.service;
import java.util.ArrayList;
import java.util.List;
t.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
ample.domain.Person;
public class PersonService {
private DBOperateHelper dbOperateHelper;
public PersonService(Context context) {
this.dbOperateHelper = new DBOperateHelper(context);
}
/**
* 保存记录
* @param person
*/
public void save(Person person){
/
/得到数据库实例，⾥⾯封装了数据库操作⽅法
SQLiteDatabase sqldb = WritableDatabase();
//SQL("insert into person(name,phone) values('"+Name()+"','"+Phone()+"')");
//利⽤占位符可以避免注⼊，但是注意数组参与与占位符对应的字段要⼀⼀对应
//关闭数据库
sqldb.close();
}
/**
* 删除记录
* @param id
*/
public void delete(Integer id){
SQLiteDatabase sqldb = WritableDatabase();
sqldb.close();
}
/**
* 更新记录
* @param person
*/
public void update(Person person){
SQLiteDatabase sqldb = WritableDatabase();
}
/**
* 通过ID查询记录
* @param id
* @return
*/
public Person find(Integer id){
/**
* getWritableDatabase 与 getReadableDatabase 的区别：
* getReadableDatabase会先返回getWritableDatabase(可写)，如果调⽤getWritableDatabase失败
* 则才会调⽤getReadableDatabase后续⽅法，使数据库只读
* 当写⼊的数据超过数据库⼤⼩则调⽤getWritableDatabase会失败
* 所以只读时则可以使⽤此⽅法，其它情况(只要不是超过数据库⼤⼩)也可以使⽤此⽅法
*/
SQLiteDatabase sqldb = ReadableDatabase();
Cursor cursor = sqldb.rawQuery("select * from person where personid=?", new String[]{String.valueOf(id)});
int personid;
String name;
String phone;
int amount;
Person person = null;
veToFirst()){
personid = ColumnIndex("personid"));
name = ColumnIndex("name"));
phone = ColumnIndex("phone"));
amount = ColumnIndex("amount"));
person = new Person(personid,name,phone,amount);
}
cursor.close();
return person;
}
/**
excel listview控件
* 返回指定长度记录，limit 3,5，适⽤于分页
* @param offset 起始
* @param maxResult 长度
* @return
*/
public List<Person> getScrollData(int offset,int maxResult){
SQLiteDatabase sqldb = ReadableDatabase();
Cursor cursor = sqldb.rawQuery("select * from person order by personid asc limit ?,?", new String[]{String.valueOf(offset),String.valueOf(maxResult)});
int personid;
String name;
String phone;
int amount;
Person person = null;
List<Person> persons = new ArrayList<Person>();
veToNext()){
personid = ColumnIndex("personid"));
name = ColumnIndex("name"));
phone = ColumnIndex("phone"));
amount = ColumnIndex("amount"));
person = new Person(personid,name,phone,amount);
persons.add(person);
}
cursor.close();
return persons;
}
/**
* 返回cursor
* @param offset 起始
* @param maxResult 长度
* @return
*/
public Cursor getCursorScrollData(int offset,int maxResult){
SQLiteDatabase sqldb = ReadableDatabase();
Cursor cursor = sqldb.rawQuery("select personid as _id,name,phone,amount from person order by personid asc limit ?,?", new String[]{String.valueOf(offset),String.valueOf(maxResult)}); return cursor;
}
/**
* 返回总记录数
* @return
*/
public long getCount(){
SQLiteDatabase sqldb = ReadableDatabase();
Cursor cursor = sqldb.rawQuery("select count(*) from person", null);
//该查询语句值返回⼀条语句
long result = Long(0);
cursor.close();
return result;
}
public void payment(){
SQLiteDatabase sqldb = WritableDatabase();
sqldb.beginTransaction();//开启事务
try{
sqldb.setTransactionSuccessful();//设置事务标志位true
} finally {
//结束事务：有两种情况：commit\rollback，事务提交或者回滚是由事务的标识决定的
//事务为ture则提交，事务为flase则回滚，默认为false
}
}
}
⼗⼀、OtherPersonService.java 业务类源码：
ample.service;
import java.util.ArrayList;
import java.util.List;
ample.domain.Person;
t.ContentValues;
t.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class OtherPersonService {
private DBOperateHelper dbOperateHelper;
public OtherPersonService(Context context) {
this.dbOperateHelper = new DBOperateHelper(context);
}
/**

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。