SQLite,是一款轻量级的关系型数据库。由于它占用的资源非常少,所以在很多嵌入式设备都是用SQLite来存储数据。并且它目前支持Windows/Linux/Unix等等主流的操作系统,兼容性还不错。我们也可以用多种开发语言如C#、Java、PHP等来通过ODBC接口操作SQLite,十分方便。
Android作为目前主流的移动操作系统,完全符合SQLite占用资源少的优势,故在Android平台上,集成了一个嵌入式关系型数据库—SQLite。
一、本文中需要在数据库存储的数据结构为:
/**
* 个人信息类。
*/
public class PersonData
{
private int _id = -1;
private String number = null;
private String name = null;
private int age = -1;
public PersonData()
{
}
public PersonData(String numberString, String nameString, int age_)
{
this.number = numberString;
this.name = nameString;
this.age = age_;
}
public int get_id()
{
return _id;
}
public void set_id(int _id)
{
this._id = _id;
}
public String getNumber()
{
return number;
}
public void setNumber(String number)
{
this.number = number;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
public int getAge()
{
return age;
}
public void setAge(int age)
{
this.age = age;
}
}
二、数据库表的结构为:
public class TestTable
{
public static String TABLE_NAME = "test_table";
/**
* 所有列的名称
*/
public static interface ColumsName
{
public static final String ID = "Id";
public static final String NUMBER = "Number";
public static final String NAME = "Name";
public static final String AGE = "Age";
}
}
把表名和列名做成静态常量。
因为如果每次都在代码语句上自己写表名和列名,很难不保证哪天手抖写错,我相信大部分的人都有同感。而且,如果某天要修改某一列的名称,这就是非常头疼和麻烦的事情。
三、通过SQLiteOpenHelper来管理数据库的生成和升级:
public class DBHelper extends SQLiteOpenHelper
{
private static int VERSION = 1;
private static final String DIRECTORY = Environment.getExternalStorageDirectory().getPath()
+ File.separator + "test";
private static final String DB_NAME = Environment.getExternalStorageDirectory().getPath()
+ File.separator + "test" + File.separator
+ "test.db";
//android 应用建数据库时,默认建立在系统内 /data/data/'应用包名'/
//在SD卡的优点:
//1、当系统恢复出厂设置等操作后,重新安装应用。数据不会丢失。
//2、方便备份、恢复。只要复制到PC上,或从PC复制到Android设备的SD卡中即可。
static
{
while(!Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED))// 检查SD卡的状态
{
try
{
Thread.sleep(500);
}
catch(InterruptedException e)
{
e.printStackTrace();
break;
}
}
File file = new File(DIRECTORY);
if(!file.exists())
file.mkdir();// 创建目录
}
public DBHelper(Context context)
{
super(context, DB_NAME, null, VERSION);
}
@Override
public void onCreate(SQLiteDatabase db)
{
db.execSQL("create table " + TestTable.TABLE_NAME + "( " + TestTable.ColumsName.ID
+ " integer primary key autoincrement, " + TestTable.ColumsName.NUMBER
+ " varchar(31), " + TestTable.ColumsName.NAME + " varchar(127), "
+ TestTable.ColumsName.AGE + " integer)");
}
@Override
/**
* 数据升级,如果检测到数据库版本升级了,则会执行这个方法
*/
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
db.beginTransaction();// 开启事务
renameTable(db);
createNewTable(db);
copyData(db);
dropOldTable(db);
db.setTransactionSuccessful();
db.endTransaction();
}
/**
* 第一,重命名旧表
*
* @param db
*/
private void renameTable(SQLiteDatabase db)
{
String tempTableName = TestTable.TABLE_NAME + "_temp";
String sql = "ALTER TABLE " + TestTable.TABLE_NAME + " RENAME TO " + tempTableName;
db.execSQL(sql);
} // -------------------------------------------------------
/**
* 第二,建新表
*
* @param db
*/
private void createNewTable(SQLiteDatabase db)
{
db.execSQL("create table " + TestTable.TABLE_NAME + "( " + TestTable.ColumsName.ID
+ " integer primary key autoincrement, " + TestTable.ColumsName.NUMBER + " varchar, "
+ TestTable.ColumsName.NAME + " varchar, " + TestTable.ColumsName.AGE
+ " integer, Test text)");
} // -------------------------------------------------------
/**
* 第三,把旧表需要的数据复制到新表
*
* @param db
*/
private void copyData(SQLiteDatabase db)
{
String sql = "INSERT INTO " + TestTable.TABLE_NAME + " (" + TestTable.ColumsName.ID + ", "
+ TestTable.ColumsName.NUMBER + ", " + TestTable.ColumsName.NAME + ", "
+ TestTable.ColumsName.AGE + ") SELECT " + TestTable.ColumsName.ID + ", "
+ TestTable.ColumsName.NUMBER + ", " + TestTable.ColumsName.NAME + ", "
+ TestTable.ColumsName.AGE + " FROM " + TestTable.TABLE_NAME + "_temp";
db.execSQL(sql);
} // -------------------------------------------------------
/**
* 第四,删除临时表
*
* @param db
*/
private void dropOldTable(SQLiteDatabase db)
{
db.execSQL("DROP TABLE IF EXISTS " + TestTable.TABLE_NAME + "_temp");
} // -------------------------------------------------------
}
SQLiteOpenHelper里面需要重载两个方法:onCreate(SQLiteDatabase db)和onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)。
1、onCreate(SQLiteDatabase db)方法在数据库创建的时候自动调用,在里面通过execSQL()运行数据库语句来生成表。
2、onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)方法是用作数据库升级,每次打开数据库,系统都会检查数据库的版本号,当发现版本号不一致,则会执行该方法。在上述例子中,只要把版本号VERSION改为2,就会为表添加一个列,并保留以前的数据。
四、数据库的具体操作:插入、修改、删除和查询:
/**
* 数据库操作类,采用单例模式操作。
*/
public class DBOperator
{
private DBHelper helper = null;
private static DBOperator operator = null;
private DBOperator(Context context)
{
helper = new DBHelper(context);
}
public static DBOperator getInstance(Context context)
{
if(operator == null)
{
synchronized(DBOperator.class)
{
if(operator == null)
operator = new DBOperator(context);
}
}
return operator;
}
/**
* 向数据库插入一条数据
*
* @param personData 具体数据
* @return 插入的结果
*/
public synchronized boolean insertData(PersonData personData)
{
boolean result = true;
try
{
SQLiteDatabase db = helper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(TestTable.ColumsName.NUMBER, personData.getNumber());
values.put(TestTable.ColumsName.NAME, personData.getName());
values.put(TestTable.ColumsName.AGE, personData.getAge());
db.insert(TestTable.TABLE_NAME, null, values);
// 也可以通过数据库语句来插入数据库
// String sqlString = "INSERT INTO " + TestTable.TABLE_NAME + "('"
// + TestTable.ColumsName.NUMBER + "', '" + TestTable.ColumsName.NAME + "', '"
// + TestTable.ColumsName.AGE + "') VALUES('" + personData.getNumber() + "', '"
// + personData.getName() + "', " + personData.getAge() + ")";
// db.execSQL(sqlString);
db.close();
}
catch(Exception e)
{
e.printStackTrace();
result = false;
}
return result;
}
/**
* 根据编号把数据删除
*
* @param number 人员的编号
* @return 删除的结果
*/
public synchronized boolean delete(String number)
{
boolean result = true;
try
{
SQLiteDatabase db = helper.getWritableDatabase();
db.delete(TestTable.TABLE_NAME, TestTable.ColumsName.NUMBER + "=?",
new String[] { number });
// db.execSQL("DELETE FROM " + TestTable.TABLE_NAME + " WHERE " +
// TestTable.ColumsName.NUMBER + "='" + number + "'");
db.close();
}
catch(Exception e)
{
e.printStackTrace();
result = false;
}
return result;
}
/**
* 更新,根据唯一的编号来更新
*
* @param personData 人员的数据
* @return 更新的结构
*/
public synchronized boolean update(PersonData personData)
{
boolean result = true;
try
{
SQLiteDatabase db = helper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(TestTable.ColumsName.NUMBER, personData.getNumber());
values.put(TestTable.ColumsName.NAME, personData.getName());
values.put(TestTable.ColumsName.AGE, personData.getAge());
db.update(TestTable.TABLE_NAME, values, TestTable.ColumsName.NUMBER + "=?",
new String[] { personData.getNumber() });
// String sqlString = "UPDATE " + TestTable.TABLE_NAME + " SET "
// + TestTable.ColumsName.NUMBER + "='" + personData.getNumber() + "', "
// + TestTable.ColumsName.NAME + "='" + personData.getName() + "',"
// + TestTable.ColumsName.AGE + "=" + personData.getAge() + " WHERE "
// + TestTable.ColumsName.NUMBER + "='" + personData.getNumber() + "'";
// db.execSQL(sqlString);
db.close();
}
catch(Exception e)
{
e.printStackTrace();
result = false;
}
return result;
}
/**
* 根据编号获得人员的信息
*
* @param number 人员编号
* @return 人员信息
*/
public synchronized PersonData getPersonData(String number)
{
PersonData personData = null;
try
{
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.query(TestTable.TABLE_NAME, null, TestTable.ColumsName.NUMBER + "=?",
new String[] { number }, null, null, null);
// String sqlString = "SELECT * FROM " + TestTable.TABLE_NAME + " WHERE "
// + TestTable.ColumsName.NUMBER + "='" + number + "'GROUP BY "
// + TestTable.ColumsName.NUMBER;
// Cursor cursor = db.rawQuery(sqlString, null);
if(cursor != null)
{
while(cursor.moveToNext())
{
int columsCount = cursor.getColumnCount();
personData = new PersonData();
for(int i = 0; i < columsCount; i++)
{
if(TestTable.ColumsName.ID.equals(cursor.getColumnName(i)))
personData.set_id(cursor.getInt(i));
if(TestTable.ColumsName.NUMBER.equals(cursor.getColumnName(i)))
personData.setNumber(cursor.getString(i));
if(TestTable.ColumsName.NAME.equals(cursor.getColumnName(i)))
personData.setName(cursor.getString(i));
if(TestTable.ColumsName.AGE.equals(cursor.getColumnName(i)))
personData.setAge(cursor.getInt(i));
}
}
}
}
catch(Exception e)
{
e.printStackTrace();
}
return personData;
}
/**
* 获取所有的数据
*
* @return
*/
public synchronized List<PersonData> getAllPersonData()
{
List<PersonData> personList = null;
try
{
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.query(TestTable.TABLE_NAME, null, null, null, null, null,
TestTable.ColumsName.NUMBER);
// String sqlString = "SELECT * FROM " + TestTable.TABLE_NAME + " GROUP BY "
// + TestTable.ColumsName.NUMBER;
// Cursor cursor = db.rawQuery(sqlString, null);
if(cursor != null)
{
int columsCount = cursor.getColumnCount();
personList = new ArrayList<PersonData>();
while(cursor.moveToNext())
{
PersonData personData = new PersonData();
for(int i = 0; i < columsCount; i++)
{
if(TestTable.ColumsName.ID.equals(cursor.getColumnName(i)))
personData.set_id(cursor.getInt(i));
if(TestTable.ColumsName.NUMBER.equals(cursor.getColumnName(i)))
personData.setNumber(cursor.getString(i));
if(TestTable.ColumsName.NAME.equals(cursor.getColumnName(i)))
personData.setName(cursor.getString(i));
if(TestTable.ColumsName.AGE.equals(cursor.getColumnName(i)))
personData.setAge(cursor.getInt(i));
}
personList.add(personData);
}
}
}
catch(Exception e)
{
e.printStackTrace();
}
return personList;
}
}
对数据库的操作很灵活,既可以使用Android框架本身提供的方式,也可以运行原生的数据库语句。