Android之采用execSQL与rawQuery方法完成数据的添删改查操作详解

使用 SQLiteDatabase 操作 SQLite 数据库
复制代码 代码如下:
 

复制代码 代码如下:
public class DatabaseHelper extends SQLiteOpenHelper { 
//类没有实例化,是不能用作父类构造器的参数,必须声明为静态 
 private static final String name = "itcast"; //数据库名称 
 private static final int version = 1; //数据库版本 
 public DatabaseHelper(Context context) { 
//第三个参数CursorFactory指定在执行查询时获得一个游标实例的工厂类,设置为null,代表使用系统默认的工厂类 
super(context, name, null, version); 
 } 
@Override public void onCreate(SQLiteDatabase db) { 
  db.execSQL("CREATE TABLE IF NOT EXISTS person (personid integer primary key autoincrement, name varchar(20), age INTEGER)");
 } 
@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
   db.execSQL(" ALTER TABLE person ADD phone VARCHAr(12) NULL "); //往表中增加一列 
// DROp TABLE IF EXISTS person 删除表 
   } 
} 
//在实际项目开发中，当数据库表结构发生更新时，应该避免用户存放于数//据库中的数据丢失。 

复制代码 代码如下:
 

复制代码 代码如下:
 

复制代码 代码如下:
 

复制代码 代码如下:
 

复制代码 代码如下:
 

复制代码 代码如下:
package com.zyq.db; 
import android.app.Activity; 
import android.os.Bundle; 
public class MainActivity extends Activity  
{ 
@Override 
public void onCreate(Bundle savedInstanceState)  
{ 
super.onCreate(savedInstanceState); 
setContentView(R.layout.main); 
} 
} 

复制代码 代码如下:
package com.zyq.db; 
import java.util.List; 
import android.test.AndroidTestCase; 
import android.util.Log; 
import com.zyq.service.DBOpenHelper; 
import com.zyq.service.PersonService; 
import com.zyq.voo.Person; 

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

 
public void testCreateDB() throws Throwable 
{ 
DBOpenHelper dbOpenHelper=new DBOpenHelper(this.getContext()); 
dbOpenHelper.getReadableDatabase(); //Create and/or open a database. 
} 
 
public void testSave() throws Throwable 
{ 
PersonService personService=new PersonService(this.getContext()); 
personService.save(new Person("zhangsan","1360215320")); 
personService.save(new Person("lisi","1123")); 
personService.save(new Person("lili","232")); 
personService.save(new Person("wangda","123123")); 
personService.save(new Person("laozhu","234532")); 
} 
 
public void testFind() throws Throwable 
{ 
PersonService personService=new PersonService(this.getContext()); 
Person person=personService.find(1); 
Log.i(TAG,person.toString()); 
} 
 
public void testUpdate() throws Throwable 
{ 
PersonService personService=new PersonService(this.getContext()); 
Person person=personService.find(1); 
person.setName("lisi"); 
personService.update(person); 
} 
 
public void testGetCount() throws Throwable 
{ 
PersonService personService=new PersonService(this.getContext()); 
Log.i(TAG, personService.getCount()+"********"); 
} 
 
public void testScroll() throws Throwable 
{ 
PersonService personService=new PersonService(this.getContext()); 
List persons=personService.getScrollData(3, 3); 
for(Person person:persons) 
{ 
Log.i(TAG, person.toString()); 
} 
} 
 
public void testDelete() throws Throwable 
{ 
PersonService personService=new PersonService(this.getContext()); 
personService.delete(5); 
} 
} 

复制代码 代码如下:
package com.zyq.service; 
import android.content.Context; 
import android.database.sqlite.SQLiteDatabase; 
import android.database.sqlite.SQLiteOpenHelper; 
public class DBOpenHelper extends SQLiteOpenHelper 
{ 
 
public DBOpenHelper(Context context) 
{ 
super(context, "zyq.db", null, 2); 
} 
 
@Override 
public void onCreate(SQLiteDatabase db) 
{ 
db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))");//创建表 
} 
 
@Override 
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
{ 
db.execSQL("ALTER TABLE person ADD phone VARCHAr(12) NULL "); //往表中增加一列 
} 
} 

复制代码 代码如下:
 
  package="com.zyq.db" 
  android:versionCode="1" 
  android:versionName="1.0"> 
 
 
  android:label="@string/app_name"> 
 
 
 
 
 
 
 
android:targetPackage="com.zyq.db" android:label="Tests for My App" /> 
  

复制代码 代码如下:
package com.zyq.service; 
import java.util.ArrayList; 
import java.util.List; 
import android.content.Context; 
import android.database.Cursor; 
import android.database.sqlite.SQLiteDatabase; 
import com.zyq.voo.Person; 
public class PersonService 
{ 
private DBOpenHelper helper; 
public PersonService(Context context) 
{ 
helper=new DBOpenHelper(context); 
} 
 
public void save(Person person) 
{ 
SQLiteDatabase db=helper.getWritableDatabase();//Create and/or open a database that will be used for reading and writing 
db.execSQL("INSERT INTO person(name,phone) values(?,?)",new Object[]{person.getName().trim(),person.getPhone().trim()});//使用占位符进行转译 
//  db.close();  不关数据库连接 。可以提高性能 因为创建数据库的时候的操作模式是私有的。 
//代表此数据库，只能被本应用所访问 单用户的，可以维持长久的链接 
}  
 
public void update(Person person) 
{ 
SQLiteDatabase db=helper.getWritableDatabase(); 
db.execSQL("update person set phone=?,name=? where personid=?", 
new Object[]{person.getPhone().trim(),person.getName().trim(),person.getId()}); 
} 
 
public Person find(Integer id) 
{ 
SQLiteDatabase db=helper.getReadableDatabase(); 
Cursor cursor=db.rawQuery("select * from person where personid=?", new String[]{id.toString()});//Cursor 游标和 ResultSet 很像 
if(cursor.moveToFirst())//Move the cursor to the first row. This method will return false if the cursor is empty. 
{ 
int personid=cursor.getInt(cursor.getColumnIndex("personid")); 
String name=cursor.getString(cursor.getColumnIndex("name")); 
String phone=cursor.getString(cursor.getColumnIndex("phone")); 

return new Person(personid,name,phone); 
} 
return null; 
} 
 
public void delete(Integer id) 
{ 
SQLiteDatabase db=helper.getWritableDatabase(); 
db.execSQL("delete from person where personid=?", 
new Object[]{id}); 
} 

 
public long getCount() 
{ 
SQLiteDatabase db=helper.getReadableDatabase(); 
Cursor cursor=db.rawQuery("select count(*) from person", null); 
cursor.moveToFirst(); 
return cursor.getLong(0); 
} 
 
public List getScrollData(int offset,int maxResult) 
{ 
List persons=new ArrayList(); 
SQLiteDatabase db=helper.getReadableDatabase(); 
Cursor cursor=db.rawQuery("select * from person limit ?,?",  
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")); 

persons.add(new Person(personid,name,phone)); 
} 

return persons; 
} 
} 

复制代码 代码如下:
package com.zyq.voo; 
public class Person 
{ 
private Integer id; 
private String name; 
private String phone; 

public Person(int personid, String name, String phone) 
{ 
this.id=personid; 
this.name=name; 
this.phone=phone; 
} 

public Person(String name, String phone) 
{ 
this.name = name; 
this.phone = phone; 
} 
public String toString() 
{ 
return "Person [id=" + id + ", name=" + name + ", phone=" + phone + "]"; 
} 
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; 
} 

 

}