分类导航
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLOceanBase

SQLite实例

Sqlite   发布时间：2022-05-22  发布网站：大佬教程  code.js-code.com
大佬教程收集整理的这篇文章主要介绍了SQLite实例，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

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

<?xml version="1.0" enCoding="utf-8"?>
<manifest xmlns:androID="http://scheR_427_11845@as.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://scheR_427_11845@as.androID.com/apk/res/androID"
    xmlns:tools="http://scheR_427_11845@as.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://scheR_427_11845@as.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,@R_173_944@,?,?)";
		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);
	}
}

大佬总结

以上是大佬教程为你收集整理的SQLite实例全部内容，希望文章能够帮你解决SQLite实例所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。
标签：
猜你在找的Sqlite相关文章
其他相关热搜词更多
phpJavaPython程序员load中如何string使用参数jquery开发安装listlinuxiosandroid工具javascriptcap