SQLite实例

发布时间：2020-12-12 23:50:39 所属栏目：百科来源：网络整理

导读：因为使用到了单元测试，所以看一下AndroidManifest.xml ?xml version="1.0" encoding="utf-8"?manifest xmlns:android="http://schemas.android.com/apk/res/android" package="com.example.test_sqlite" android:versionCode="1" android:versionName="1.0"

因为使用到了单元测试，所以看一下AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.test_sqlite"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="8"
        android:targetSdkVersion="17" />

    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <activity
            android:name="com.example.test_sqlite.MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>

        <uses-library android:name="android.test.runner" />
    </application>

    <uses-permission android:name="android.permission.RUN_INSTRUMENTATION" />

    <instrumentation
        android:name="android.test.InstrumentationTestRunner"
        android:label="Test for my app"
        android:targetPackage="com.example.test_sqlite" />
</manifest>

主页布局
activity_main.xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    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="100dp"
            android:layout_height="wrap_content"
            android:text="用户名" />

        <TextView
            android:id="@+id/phone"
            android:layout_width="120dp"
            android:layout_height="wrap_content"
            android:text="手机号" />

        <TextView
            android:id="@+id/amount"
            android:layout_width="100dp"
            android:layout_height="wrap_content"
            android:text="余额" />
    </LinearLayout>

    <ListView
        android:id="@+id/listView"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" />

</LinearLayout>

ListView的Item布局文件

item.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="horizontal" >

    <TextView
        android:id="@+id/name"
        android:layout_width="100dp"
        android:layout_height="wrap_content"
        android:textSize="20dp"/>

    <TextView
        android:id="@+id/phone"
        android:layout_width="120dp"
        android:layout_height="wrap_content"
        android:textSize="20dp"/>

    <TextView
        android:id="@+id/amount"
        android:layout_width="100dp"
        android:layout_height="wrap_content"
        android:textSize="20dp"/>

</LinearLayout>

Person.java

package com.example.test_sqlite.domain;

public class Person {

	private Integer id;
	private String name;
	private String phone;
	private int amount;

	public Person() {
	}

	public Person(Integer id,String name,String phone,int amount) {
		this.id = id;
		this.name = name;
		this.phone = phone;
		this.amount = 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 int getAmount() {
		return amount;
	}

	public void setAmount(int amount) {
		this.amount = amount;
	}

	@Override
	public String toString() {
		return "Person [id=" + id + ",name=" + name + ",phone=" + phone
				+ ",amount=" + amount + "]";
	}
}

PersonService.java

package com.example.test_sqlite.service;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.example.test_sqlite.domain.Person;

public class PersonService {
	
	private SQLHelper sqlHelper;
	
	
	public PersonService(Context context) {
		this.sqlHelper = new SQLHelper(context);
	}

	/**
	 * 保存
	 * */
	public void save(Person person) {
		SQLiteDatabase db = sqlHelper.getWritableDatabase();	// 创建多个db的时候使用的依然是同一个对象
		String sql = "insert into person(name,phone,amount) values(?,?,?)";
		db.execSQL(sql,new Object[]{person.getName(),person.getPhone(),person.getAmount()});
	}
	
	/**
	 * 根据id删除对应记录
	 * */
	public void delete(int id){
		SQLiteDatabase db = sqlHelper.getWritableDatabase();
		String sql = "delete from person where personid=?";
		db.execSQL(sql,new Object[]{id});
	}
	
	/**
	 * 更新
	 * */
	public void update(Person person){
		SQLiteDatabase db = sqlHelper.getWritableDatabase();
		String sql = "update person set name=?,phone=?,amount=? where personid=?";
		db.execSQL(sql,person.getAmount(),person.getId()});
	}
	
	public Person findById(Integer id){
		SQLiteDatabase db = sqlHelper.getReadableDatabase();
		String sql = "select * from person where personid=?";
		Cursor cursor = db.rawQuery(sql,new String[]{id.toString()});
		Person person = new Person();
		if(cursor.moveToFirst()){
			int personid = cursor.getInt(cursor.getColumnIndex("personid"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			String phone = cursor.getString(cursor.getColumnIndex("phone"));
			int amount = cursor.getInt(cursor.getColumnIndex("amount"));
			person.setId(personid);
			person.setName(name);
			person.setPhone(phone);
			person.setAmount(amount);
		}
		return person;
	}
	
	/**
	 * 分页查询
	 * @param offset 跳过前面的几条记录
	 * @param maxResult 要查询几条记录
	 * @return
	 */
	public List<Person> getScrollData(int offset,int maxResult){
		List<Person> persons = new ArrayList<Person>();
		SQLiteDatabase db = sqlHelper.getReadableDatabase();
		String sql = "select * from person order by personid asc limit ?,?";
		Cursor cursor = db.rawQuery(sql,new String[]{String.valueOf(offset),String.valueOf(maxResult)});
		while(cursor.moveToNext()){
			int personid = cursor.getInt(cursor.getColumnIndex("personid"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			String phone = cursor.getString(cursor.getColumnIndex("phone"));
			int amount = cursor.getInt(cursor.getColumnIndex("amount"));
			persons.add(new Person(personid,name,amount));
		}
		db.close();
		return persons;
	}
	
	/**
	 * 返回Cursor的分页查询
	 * */
	public Cursor getCursorScrollData(int offset,int maxResult){
		SQLiteDatabase db = sqlHelper.getReadableDatabase();
		String sql = "select personid as _id,amount from person order by personid asc limit ?,String.valueOf(maxResult)});
		return cursor;
	}
	
	/**
	 * 获取总记录数
	 * */
	public long getCount(){
		SQLiteDatabase db = sqlHelper.getReadableDatabase();
		String sql = "select count(*) from person";
		Cursor cursor = db.rawQuery(sql,null);
		cursor.moveToFirst();
		long result = cursor.getLong(0);
		cursor.close();
		return result;
	}
	
	public void payment(){
		SQLiteDatabase db = sqlHelper.getWritableDatabase();
		db.beginTransaction();
		try {
			db.execSQL("update person set amount=amount-10 where personid=1");
			db.execSQL("update person set amount=amount+10 where personid=3");
			db.setTransactionSuccessful();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
		}
		/*
		 * 结束事务有两种情况：commit，rollback
		 * 事物的提交或者回滚是由事务的标志决定的，如果事务的标志位True，事务就会提交，默认情况下，事务的标志位False
		 * 
		 * 
		 * */
	}
	
	public SQLHelper getSqlHelper() {
		return sqlHelper;
	}
	
	public void setSqlHelper(SQLHelper sqlHelper) {
		this.sqlHelper = sqlHelper;
	}
}

SQLHelper.java

package com.example.test_sqlite.service;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class SQLHelper extends SQLiteOpenHelper{
	
	public static final int VERSION = 2;
	public static final String DBNAME = "lipeng.db";	// 默认保存在:/data/data/<包>/databases/
	
	String sql = "CREATE TABLE person(personid integer primary key autoincrement,name varchar(20),phone varchar(12))";
	String sql2 = "ALTER TABLE person ADD amount int NULL";

	public SQLHelper(Context context,CursorFactory factory,int version) {
		super(context,DBNAME,null,VERSION);
	}
	
	public SQLHelper(Context context) {
		super(context,VERSION);
	}

	/**
	 * 在数据库第一次被创建的时候使用
	 * */
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(sql);
	}

	/**
	 * 数据库文件版本号变更的时候调用
	 * */
	@Override
	public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion) {
		db.execSQL(sql2);
	}

}

MainActivity.java

package com.example.test_sqlite;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.example.adapter.PersonAdapter;
import com.example.test_sqlite.domain.Person;
import com.example.test_sqlite.service.PersonService;

import android.os.Bundle;
import android.app.Activity;
import android.database.Cursor;
import android.view.Menu;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.SimpleCursorAdapter;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends Activity {

	private ListView listView = null;
	PersonService service = null;
	
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);
		
		service = new PersonService(this);
		
		listView = (ListView) findViewById(R.id.listView);
		listView.setOnItemClickListener(new ItemClickListener());
		
//		show1();
		show2();
//		show3();
	}
	
	private final class ItemClickListener implements OnItemClickListener{

		@Override
		public void onItemClick(AdapterView<?> parent,View view,int position,long id) {
			ListView lView = (ListView) parent;
			/*类型一：这是相对于自定义适配器，只能用show3()
			Person person = (Person)lView.getItemAtPosition(position);
			Toast.makeText(getApplicationContext(),person.toString(),Toast.LENGTH_SHORT).show();*/
			/*  类型二：这是相对于SimpleCursorAdapter，只能用show2()的程序*/
			Cursor cursor = (Cursor) lView.getItemAtPosition(position);
			int _id = cursor.getInt(cursor.getColumnIndex("_id"));
			Toast.makeText(getApplicationContext(),String.valueOf(_id),Toast.LENGTH_SHORT).show();
		}
		
	}
	
	/**
	 * 自定义适配器显示
	 */
	private void show3() {
		List<Person> persons = service.getScrollData(0,20);
		PersonAdapter adapter = new PersonAdapter(this,persons,R.layout.item);
		listView.setAdapter(adapter);
	}

	private void show2() {
		Cursor cursor = service.getCursorScrollData(0,20);
		SimpleCursorAdapter simpleCursorAdapter = 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(simpleCursorAdapter);
	}

	/**
	 * 显示listView
	 * */
	private void show1() {
		List<Person> persons = service.getScrollData(0,5);
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		for(Person person : persons){
			Map<String,Object> map = new HashMap<String,Object>();
			map.put("name",person.getName());	// key：随便取名字
			map.put("phone",person.getPhone());
			map.put("amount",person.getAmount());
			map.put("id",person.getId());
			list.add(map);
		}
		SimpleAdapter adapter = new SimpleAdapter(this,list,R.id.amount});
		listView.setAdapter(adapter);
	}

	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		// Inflate the menu; this adds items to the action bar if it is present.
		getMenuInflater().inflate(R.menu.main,menu);
		return true;
	}

}

PersonAdapter.java

package com.example.adapter;

import java.util.List;

import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;

import com.example.test_sqlite.R;
import com.example.test_sqlite.domain.Person;

public class PersonAdapter extends BaseAdapter{

	private List<Person> persons; // 在绑定的数据
	private int resource;	//绑定的条目界面
	private LayoutInflater inflater = null;	// 使用xml文件生成一个对应的view对象
	
	public PersonAdapter(Context context,List<Person> persons,int resource) {
		this.persons = persons;
		this.resource = resource;
		inflater = (LayoutInflater)context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);	// 取得系统内置的布局填充服务
	}
	
	public final class ViewCache{
		public TextView nameView;
		public TextView phoneView;
		public TextView amountView;
	}

	@Override
	public int getCount() {
		return persons.size();
	}

	@Override
	public Object getItem(int position) {
		return persons.get(position);
	}

	@Override
	public long getItemId(int position) {
		return position;
	}

	@Override
	public View getView(int position,View convertView,ViewGroup parent) {
		/*
		 * 在listView中如果已经存在view，显示下一页的时候就不会再创建view了；如果不存在就创建
		 * */
		TextView nameView = null;
		TextView phoneView = null;
		TextView amountView = null;
		if(convertView == null){
			convertView = inflater.inflate(resource,null);// 生成条目对象
			nameView = (TextView) convertView.findViewById(R.id.name);
			phoneView = (TextView) convertView.findViewById(R.id.phone);
			amountView = (TextView) convertView.findViewById(R.id.amount);

			ViewCache viewCache = new ViewCache();
			viewCache.nameView = nameView;
			viewCache.phoneView = phoneView;
			viewCache.amountView = amountView;
			
			convertView.setTag(viewCache);
		} else {
			ViewCache cache = (ViewCache) convertView.getTag();
			nameView = cache.nameView;
			phoneView = cache.phoneView;
			amountView = cache.amountView;
		}
		// 实现数据绑定
		Person person = persons.get(position);
		nameView.setText(person.getName());
		phoneView.setText(person.getPhone());
		amountView.setText(String.valueOf(person.getAmount()));
		return convertView;
	}

}

PersonServiceTest.java

package com.example.test;

import java.util.List;

import android.test.AndroidTestCase;
import android.util.Log;

import com.example.test_sqlite.domain.Person;
import com.example.test_sqlite.service.PersonService;
import com.example.test_sqlite.service.SQLHelper;

public class PersonServiceTest extends AndroidTestCase {
	private static final String TAG = "PersonServiceTest";

	public void testCreateDB() {
		SQLHelper helper = new SQLHelper(getContext());
		helper.getWritableDatabase();
	}

	public void testSave() {
		// 虽然每个方法里面都有该语句，但是不能放在上面，但可以放在重写的setup方法里面
		PersonService service = new PersonService(getContext());
		Person person = new Person(null,"lipeng","15099144116",100);
		for (int i = 0; i < 20; i++) {
			service.save(person);
		}
	}

	public void testDelete() {
		PersonService service = new PersonService(getContext());
		service.delete(2);
	}

	public void testUpdate() {
		PersonService service = new PersonService(getContext());
		Person person = service.findById(2);
		person.setName("lipengsdfdsf");
		person.setPhone("132145646489");
		person.setAmount(20);
		service.update(person);
	}

	public void testFindById() {
		PersonService service = new PersonService(getContext());
		Person person = service.findById(2);
		Log.i(TAG,person.toString());
	}

	public void testFindByPage() {
		PersonService service = new PersonService(getContext());
		List<Person> persons = service.getScrollData(0,20);
		for(Person person : persons){
			Log.i(TAG,person.toString());
		}
	}

	public void testGetCount() {
		PersonService service = new PersonService(getContext());
		long count = service.getCount();
		Log.i(TAG,"count:" + count);
	}
	
	/**
	 * 模拟转账
	 * */
	public void testUpdateAmount(){
		PersonService service = new PersonService(getContext());
		Person person1 = service.findById(1);
		Person person2 = service.findById(3);
		person1.setAmount(100);
		person2.setAmount(50);
		service.update(person1);
		service.update(person2);
	}
	
	/**
	 * 测试转账
	 * */
	public void testPayment(){
		PersonService service = new PersonService(getContext());
		service.payment();
	}
	
}

OtherPersonServiceTest.java

package com.example.test;

import java.util.List;

import android.test.AndroidTestCase;
import android.util.Log;

import com.example.test_sqlite.domain.Person;
import com.example.test_sqlite.service.OtherPersonService;
import com.example.test_sqlite.service.SQLHelper;

public class OtherPersonServiceTest extends AndroidTestCase {
	private static final String TAG = "OtherPersonServiceTest";

	public void testCreateDB() {
		SQLHelper helper = new SQLHelper(getContext());
		helper.getWritableDatabase();
	}

	public void testSave() {
		OtherPersonService service = new OtherPersonService(getContext());
		Person person = new Person(null,"lipeng23","15099123213",100);
		service.save(person);
	}

	public void testDelete() {
		OtherPersonService service = new OtherPersonService(getContext());
		service.delete(10);
	}

	public void testUpdate() {
		OtherPersonService service = new OtherPersonService(getContext());
		Person person = service.findById(8);
		person.setName("lipengsdfdsf");
		person.setPhone("132145646489");
		person.setAmount(20);
		service.update(person);
	}

	public void testFindById() {
		OtherPersonService service = new OtherPersonService(getContext());
		Person person = service.findById(8);
		Log.i(TAG,person.toString());
	}

	public void testFindByPage() {
		OtherPersonService service = new OtherPersonService(getContext());
		List<Person> persons = service.getScrollData(2,3);
		for(Person person : persons){
			Log.i(TAG,person.toString());
		}
	}

	public void testGetCount() {
		OtherPersonService service = new OtherPersonService(getContext());
		long count = service.getCount();
		Log.i(TAG,"count:" + count);
	}
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!