首页 > 代码库 > android中SQLite数据库的增删改查

android中SQLite数据库的增删改查

2024-07-14 04:44:26   224人阅读

1.数据库帮助类PersonSQLiteOpenHelper

package com.wzw.sqllitedemo.db;

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

/**
 * 
 * 数据库帮助类，用于创建管理数据库
 * @author Administrator
 *
 */
public final class PersonSQLiteOpenHelper extends SQLiteOpenHelper {

	private String tag="PersonSQLiteOpenHelper";

	/**
	 * 数据库的构造函数
	 * @param context
	 * @param name	数据库名称
	 * @param factory	游标工厂
	 * @param version	版本
	 */
	public PersonSQLiteOpenHelper(Context context) {
		super(context, "my.db", null, 1);
		
		
	}

	/**
	 * 数据库第一次创建时调用此方法
	 * 用于初始化表
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		//SQLiteDatabase对象已经连接数据库，直接进行性数据库操作
		String sql="create table person(_id integer primary key,name varchar(15),age integer);";
		db.execSQL(sql);		//创建person表
		
	}

	/**
	 * 数据库的版本号更新时调用
	 * 用于更新数据库的内容（删除表，更新表，删除表）
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int  oldVersion, int newVersion) {
	
	}

}
2.实体类person

package com.wzw.sqllitedemo.entities;

public class Person {

	private int id;
	private int age;
	private String name;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Person(int id, int age, String name) {
		super();
		this.id = id;
		this.age = age;
		this.name = name;
	}
	public Person() {
		super();
		// TODO Auto-generated constructor stub
	}
	@Override
	public String toString() {
		return "Person [id=" + id + ", age=" + age + ", name=" + name + "]";
	}
	
}

3.DAO数据库操作类PersonDao

package com.wzw.sqllitedemo.dao;

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

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

import com.wzw.sqllitedemo.db.PersonSQLiteOpenHelper;
import com.wzw.sqllitedemo.entities.Person;

public class PersonDao {

	private PersonSQLiteOpenHelper mOpenHelper;

	/**
	 *构造函数，谁调用需要传递一个context上下文对象
	 * @param context
	 */
	public PersonDao(Context context){
		mOpenHelper = new PersonSQLiteOpenHelper(context);
	}
	/**
	 * 添加person表一条数据
	 * @param person
	 */
	public void insert(Person person){
		
		SQLiteDatabase db=mOpenHelper.getWritableDatabase();
		if(db.isOpen()){//如果数据库打开，则进行插入操作
			//String sql="insert into person(name,age) values('李四',18);";
			db.execSQL("insert into person(name,age) values(?,?);", new Object[]{person.getName(),person.getAge()});
			db.close();
		}
	}
	
	public void delete(int id){
			
			SQLiteDatabase db=mOpenHelper.getWritableDatabase();//获取可写的数据库对象
			if(db.isOpen()){//如果数据库打开，则进行插入操作
				db.execSQL("delete from person where _id=?", new Integer[]{id});
				db.close();
			}
		}

	public void update(int id,String name){
		
		SQLiteDatabase db=mOpenHelper.getWritableDatabase();
		if(db.isOpen()){//如果数据库打开，则进行插入操作
			db.execSQL("update person set name=? where _id=?", new Object[]{name,id});
			
			db.close();
		}

	}
	
	public List<Person> queryAll(){
		SQLiteDatabase db=mOpenHelper.getReadableDatabase();//获得只读数据库对象
		if(db.isOpen()){
			Cursor cursor=db.rawQuery("select _id,name,age from person", null);
			if(cursor.getCount()>0	&&	cursor!=null){
				List<Person> personList=new ArrayList<Person>();
				int id;
				String name;
				int age;
				while(cursor.moveToNext()){
					id=cursor.getInt(0);//取第0列的ID
					name=cursor.getString(1);//取第1列的name
					age=cursor.getInt(2);//取第2列的age
					personList.add(new Person(id, age, name));
				}
				db.close();
				return personList;
			}
			db.close();
		}
		return null;
	}
	
	public Person queryItem(int id){

		SQLiteDatabase db=mOpenHelper.getReadableDatabase();
		if(db.isOpen()){
			Cursor cursor=db.rawQuery("select _id,name,age from person where id=?", new String[]{id+""});
			if(cursor!=null && cursor.moveToFirst()){
				int _id=cursor.getInt(0);
				String name=cursor.getString(1);
				int age=cursor.getInt(2);
				db.close();
				return new Person(_id, age, name);
			}
			db.close();
		}
		return null;
	}


}

4.测试类


package com.wzw.sqllitedemo.test;

import java.util.List;

import com.wzw.sqllitedemo.dao.PersonDao;
import com.wzw.sqllitedemo.db.PersonSQLiteOpenHelper;
import com.wzw.sqllitedemo.entities.Person;

import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;
import android.util.Log;

public class TestCase extends AndroidTestCase {

	private String tag="TestCase";

	public void test(){
		//数据库什么时候创建
		PersonSQLiteOpenHelper openHelp=new PersonSQLiteOpenHelper(getContext());
		//第一次连接数据库的时创建数据库，oncreate调用
		openHelp.getWritableDatabase();
	}
	public void testinsert(){
		PersonDao dao=new PersonDao(getContext());
		Person person=new Person(0,67,"zhang");
		dao.insert(person);
	}
	public void testDelete(){
		PersonDao dao=new PersonDao(getContext());
		dao.delete(1);
	}
	
	public void testUpdate(){
		PersonDao dao=new PersonDao(getContext());
		dao.update(2, "你妹");
	}
	
	public void testQueryAll(){
		PersonDao dao=new PersonDao(getContext());
		List<Person> personList=dao.queryAll();
		for (Person person : personList) {
			Log.i(tag,person.toString());
		}
	}
	
	public void testQueryItem(){
		PersonDao dao=new PersonDao(getContext());
		Person person=dao.queryItem(1);
		Log.i(tag, person.toString());
		
	}
}

需要在AndroidManifest.xml文件中声明测试

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

    <span style="color:#ff6666;"><instrumentation android:name="android.test.InstrumentationTestRunner" android:targetPackage="com.wzw.sqllitedemo"></instrumentation></span>
    <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" >
        <span style="color:#ff0000;"><uses-library android:name="android.test.runner"/></span>
        <activity
            android:name="com.wzw.sqllitedemo.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>
    </application>

</manifest>




联系
我们