很简单的过程, 直接贴代码, 以后要用的时候直接看代码就行.
Ctrl + Shift + F格式化代码. 非常好用.
数据库文件的位置在\data\data\应用包名\databases
1. SQLite和SQL语句的使用
package com.example.sqlitetest;
import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class DBOperateHelper extends SQLiteOpenHelper {
private final static String TAG = "DBOperateHelper";
public DBOperateHelper(Context context) {
// 默认创建的数据库文件保存在/data/data/<包名>/database/
// 第一个参数是上下文,第二个参数是数据库名称,第三个是游标工厂为null时使用
// 数据库默认的游标工厂,第四个是数据库版本号但是不能为0,一般大于0
super(context, "smallpig", null, 4);
}
/**
* 数据库第一次被创建时被调用
*/
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL("create table person(personid integer primary key autoincrement,name varchar(20),phone varchar(12) null)");
}
/**
* 每一次数据库版本号发生变动时触发此方法
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("alter table person add amount Integer null");
}
public int insertRecord(String name, String phone){
//得到数据库实例,里面封装了数据库操作方法
SQLiteDatabase sqldb = this.getWritableDatabase();
sqldb.execSQL("insert into person(name,phone) values(?,?)", new Object[]{name, phone});
sqldb.close();
return 1;
}
/**
* 删除记录
*
* @param name
*/
public void deleteRecord(String name) {
SQLiteDatabase sqldb = this.getWritableDatabase();
sqldb.execSQL("delete from person where name=?", new Object[] { name });
sqldb.close();
}
/**
* 更新记录
*
* @param person
*/
public void updateRecord(String name, String phone) {
SQLiteDatabase sqldb = this.getWritableDatabase();
sqldb.execSQL("update person set phone=? where name=?", new Object[] { phone, name});
sqldb.close();
}
/**
* 返回总记录数
*
* @return
*/
public long getCount() {
SQLiteDatabase sqldb = this.getReadableDatabase();
Cursor cursor = sqldb.rawQuery("select count(1) from person", null);
// 该查询语句值返回一条语句
cursor.moveToFirst();
long result = cursor.getLong(0);
cursor.close();
return result;
}
/**
* 返回指定长度记录,limit 2,8,适用于分页
*
* @param offset 起始
* @param maxResult 长度
* @return
*/
public List<Person> Traverse(int offset, int maxResult) {
SQLiteDatabase sqldb = this.getReadableDatabase();
Cursor cursor = sqldb.rawQuery(
"select * from person order by personid asc limit ?,?",
new String[] { String.valueOf(offset),
String.valueOf(maxResult) });
int personid;
String name;
String phone;
List<Person> persons = new ArrayList<Person>();
while (cursor.moveToNext()) {
personid = cursor.getInt(cursor.getColumnIndex("personid"));
name = cursor.getString(cursor.getColumnIndex("name"));
phone = cursor.getString(cursor.getColumnIndex("phone"));
Person p = new Person(name, phone);
persons.add(p);
}
cursor.close();
return persons;
}
}
2. 调用方法:
db = new DBOperateHelper(this);
View.OnClickListener onclick = new View.OnClickListener() {
public void onClick(View v) {
List<Person> p = null;
switch (v.getId()){
case R.id.button1:
db.insertRecord("lin" + (++index), "135" + (++phone));
break;
case R.id.button2:
db.deleteRecord("lin0");
break;
case R.id.button3:
db.updateRecord("lin1", "17");
break;
case R.id.button4:
p = db.Traverse(5, 10);
for (int i=0; i<p.size(); i++){
Person x = p.get(i);
Log.e(TAG, "result: " + "\t" + x.name + "\t" + x.phone);
}
break;
}
}
};