Android SQLite数据库增删改查

推荐一个查看SQLite数据库的工具

SQLite Developer 3.9.2下载地址：

http://www.sqlitedeveloper.com/

DBOpenHelper.java

[java] view plain copy print ?

1. package cn.itcast.service; 
2.  
3. import android.content.Context; 
4. import android.database.sqlite.SQLiteDatabase; 
5. import android.database.sqlite.SQLiteOpenHelper; 
6.  
7. public class DBOpenHelper extends SQLiteOpenHelper { 
8.     private static final String DATABASENAME = "itcast.db"; //数据库名称 
9.     private static final int DATABASEVERSION = 2;//数据库版本 
10.  
11.     public DBOpenHelper(Context context) { 
12.         super(context, DATABASENAME, null, DATABASEVERSION); 
13.     } 
14.  
15.     @Override 
16.     public void onCreate(SQLiteDatabase db) { 
17.         db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20), amount integer)");//执行有更改的sql语句 
18.     } 
19.  
20.     @Override 
21.     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
22.         db.execSQL("DROP TABLE IF EXISTS person"); 
23.         onCreate(db); 
24.     } 
25.  
26. } 

package cn.itcast.service;

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

public class DBOpenHelper extends SQLiteOpenHelper {
	private static final String DATABASENAME = "itcast.db"; //数据库名称
	private static final int DATABASEVERSION = 2;//数据库版本

	public DBOpenHelper(Context context) {
		super(context, DATABASENAME, null, DATABASEVERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20), amount integer)");//执行有更改的sql语句
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS person");
		onCreate(db);
	}

}

PersonService.java

[java] view plain copy print ?

1. package cn.itcast.service; 
2.  
3. import java.util.ArrayList; 
4. import java.util.List; 
5.  
6. import android.content.Context; 
7. import android.database.Cursor; 
8. import android.database.sqlite.SQLiteDatabase; 
9.  
10. import cn.itcast.domain.Person; 
11.  
12. public class PersonService { 
13.     private DBOpenHelper dbOpenHelper; 
14.      
15.     public PersonService(Context context) { 
16.         this.dbOpenHelper = new DBOpenHelper(context); 
17.     } 
18.  
19.     public void save(Person person){ 
20.         //如果要对数据进行更改，就调用此方法得到用于操作数据库的实例,该方法以读和写方式打开数据库 
21.         SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 
22.         db.execSQL("insert into person (name) values(?)", new Object[]{person.getName()}); 
23.     } 
24.      
25.     public void update(Person person){ 
26.         SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 
27.         db.execSQL("update person set name=? where personid=?",  
28.                 new Object[]{person.getName(),person.getId()}); 
29.     } 
30.      
31.     public void delete(Integer id){ 
32.         SQLiteDatabase db = dbOpenHelper.getWritableDatabase(); 
33.         db.execSQL("delete from person where personid=?", new Object[]{id.toString()}); 
34.     } 
35.      
36.     public Person find(Integer id){ 
37.         //如果只对数据进行读取，建议使用此方法 
38.         SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); 
39.         Cursor cursor = db.rawQuery("select * from person where personid=?", new String[]{id.toString()}); 
40.         if(cursor.moveToFirst()){ 
41.             int personid = cursor.getInt(cursor.getColumnIndex("personid")); 
42.             String name = cursor.getString(cursor.getColumnIndex("name")); 
43.             return new Person(personid, name); 
44.         } 
45.         return null; 
46.     } 
47.      
48.     public List<Person> getScrollData(Integer offset, Integer maxResult){ 
49.         List<Person> persons = new ArrayList<Person>(); 
50.         SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); 
51.         Cursor cursor = db.rawQuery("select * from person limit ?,?", 
52.                 new String[]{offset.toString(), maxResult.toString()}); 
53.         while(cursor.moveToNext()){ 
54.             int personid = cursor.getInt(cursor.getColumnIndex("personid")); 
55.             String name = cursor.getString(cursor.getColumnIndex("name")); 
56.             Person person = new Person(personid, name); 
57.             persons.add(person); 
58.         } 
59.         cursor.close(); 
60.         return persons; 
61.     } 
62.      
63.     public long getCount() { 
64.         SQLiteDatabase db = dbOpenHelper.getReadableDatabase(); 
65.         Cursor cursor = db.rawQuery("select count(*) from person", null); 
66.         cursor.moveToFirst(); 
67.         return cursor.getLong(0); 
68.     } 
69. } 

package cn.itcast.service;

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

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

import cn.itcast.domain.Person;

public class PersonService {
	private DBOpenHelper dbOpenHelper;
	
	public PersonService(Context context) {
		this.dbOpenHelper = new DBOpenHelper(context);
	}

	public void save(Person person){
		//如果要对数据进行更改，就调用此方法得到用于操作数据库的实例,该方法以读和写方式打开数据库
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("insert into person (name) values(?)", new Object[]{person.getName()});
	}
	
	public void update(Person person){
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("update person set name=? where personid=?", 
				new Object[]{person.getName(),person.getId()});
	}
	
	public void delete(Integer id){
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("delete from person where personid=?", new Object[]{id.toString()});
	}
	
	public Person find(Integer id){
		//如果只对数据进行读取，建议使用此方法
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select * from person where personid=?", new String[]{id.toString()});
		if(cursor.moveToFirst()){
			int personid = cursor.getInt(cursor.getColumnIndex("personid"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			return new Person(personid, name);
		}
		return null;
	}
	
	public List<Person> getScrollData(Integer offset, Integer maxResult){
		List<Person> persons = new ArrayList<Person>();
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select * from person limit ?,?",
				new String[]{offset.toString(), maxResult.toString()});
		while(cursor.moveToNext()){
			int personid = cursor.getInt(cursor.getColumnIndex("personid"));
			String name = cursor.getString(cursor.getColumnIndex("name"));
			Person person = new Person(personid, name);
			persons.add(person);
		}
		cursor.close();
		return persons;
	}
	
	public long getCount() {
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select count(*) from person", null);
		cursor.moveToFirst();
		return cursor.getLong(0);
	}
}

测试类：PersonServiceTest.java

[java] view plain copy print ?

1. package cn.itcast.db; 
2.  
3. import java.util.List; 
4.  
5. import cn.itcast.domain.Person; 
6. import cn.itcast.service.DBOpenHelper; 
7. import cn.itcast.service.PersonService; 
8. import android.test.AndroidTestCase; 
9. import android.util.Log; 
10.  
11. public class PersonServiceTest extends AndroidTestCase { 
12.     private static final String TAG = "PersonServiceTest"; 
13.  
14.     public void testCreateDB() throws Throwable{ 
15.         DBOpenHelper dbOpenHelper = new DBOpenHelper(this.getContext()); 
16.         dbOpenHelper.getWritableDatabase();//第一次调用该方法就会创建数据库 
17.     } 
18.      
19.     public void testSave() throws Throwable{ 
20.         PersonService personService = new PersonService(this.getContext()); 
21.         Person person = new Person(); 
22.         person.setName("xiaoxiao"); 
23.         personService.save(person); 
24.          
25.         person = new Person(); 
26.         person.setName("zhangliming"); 
27.         personService.save(person); 
28.          
29.         person = new Person(); 
30.         person.setName("libaobao"); 
31.         personService.save(person); 
32.          
33.         person = new Person(); 
34.         person.setName("taobao"); 
35.         personService.save(person); 
36.     } 
37.      
38.     public void testUpate() throws Throwable{ 
39.         PersonService personService = new PersonService(this.getContext()); 
40.         Person person = personService.find(1);   
41.         person.setName("lili"); 
42.         personService.update(person); 
43.     } 
44.      
45.     public void testDelete() throws Throwable{ 
46.         PersonService personService = new PersonService(this.getContext()); 
47.         personService.delete(1); 
48.     } 
49.      
50.     public void testFind() throws Throwable{ 
51.         PersonService personService = new PersonService(this.getContext()); 
52.         Person person = personService.find(1); 
53.         Log.i(TAG, person.toString()); 
54.     } 
55.      
56.     public void testGetScrollData() throws Throwable{ 
57.         PersonService personService = new PersonService(this.getContext()); 
58.         List<Person> persons = personService.getScrollData(0, 3); 
59.         for(Person person : persons){ 
60.             Log.i(TAG, person.toString()); 
61.         } 
62.     } 
63.      
64.     public void testGetCount() throws Throwable{ 
65.         PersonService personService = new PersonService(this.getContext()); 
66.         Log.i(TAG, personService.getCount()+""); 
67.     } 
68. }