android 数据库基本操作

package com.example.testandroid;


import java.util.Vector;


import android.Manifest.permission;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


public class DBTest 
{
public static final int VERSION = 2;
///
private final static String DB_NAME = "samdb";
private final static String TABLE_NAME = "person";
private final static String TABLE_COLUNM_ID = "id";
private final static String TABLE_COLUNM_NAME = "name";
private final static String TABLE_COLUNM_AGE = "age";
private final static String TABLE_COLUNM_ADDRESS = "address";

private Context context = null;
private SamSQLHelper samSQLHelper = null;


public DBTest(Context context)
{
this.context = context;
samSQLHelper = new SamSQLHelper(context,VERSION);

}

public synchronized int insertNew(Person person)
{
int id = -1;
SQLiteDatabase sqLiteDatabase = samSQLHelper.getWritableDatabase();
try {
ContentValues contentValues = new ContentValues();
contentValues.put(TABLE_COLUNM_NAME, person.name);
contentValues.put(TABLE_COLUNM_AGE, person.age);
contentValues.put(TABLE_COLUNM_ADDRESS, person.address);
id = (int)sqLiteDatabase.insert(TABLE_NAME, TABLE_COLUNM_NAME, contentValues);
sqLiteDatabase.close();
} catch (Exception e) {
try {
sqLiteDatabase.close();
} catch (Exception e2) {
}
}
System.out.println("insert id = "+id);

return id;
}

public boolean deleteOld(int id)
{
SQLiteDatabase sqLiteDatabase = samSQLHelper.getWritableDatabase();

try {
String deleteSqlString = String.format("delete from %s where %s = %d", TABLE_NAME,TABLE_COLUNM_ID,id);
System.out.println(deleteSqlString);
sqLiteDatabase.execSQL(deleteSqlString);
sqLiteDatabase.close();
} catch (Exception e) {
e.printStackTrace();
}
return false;
}

public Vector<Person> getAll()
{
Vector<Person> persons = new Vector<Person>();
try {
SQLiteDatabase sqLiteDatabase = samSQLHelper.getReadableDatabase();
String getallsqlString = String.format("select * from %s order by %s desc", TABLE_NAME,TABLE_COLUNM_AGE);
System.out.println(getallsqlString);
Cursor cursor =  sqLiteDatabase.rawQuery(getallsqlString, null);
while(cursor.moveToNext())
{
Person person = new Person();
person.id = cursor.getInt(cursor.getColumnIndex(TABLE_COLUNM_ID));
person.age = cursor.getInt(cursor.getColumnIndex(TABLE_COLUNM_AGE));
person.name = cursor.getString(cursor.getColumnIndex(TABLE_COLUNM_NAME));
person.address = cursor.getString(cursor.getColumnIndex(TABLE_COLUNM_ADDRESS));
persons.add(person);
}
cursor.close();
sqLiteDatabase.close();
} catch (Exception e) {
e.printStackTrace();
}

return persons;
}

public synchronized void updatePerson(Person person)
{
SQLiteDatabase sqLiteDatabase = samSQLHelper.getWritableDatabase();
try {
ContentValues contentValues = new ContentValues();
contentValues.put(TABLE_COLUNM_NAME, person.name);
contentValues.put(TABLE_COLUNM_AGE, person.age);
contentValues.put(TABLE_COLUNM_ADDRESS, person.address);
sqLiteDatabase.update(TABLE_NAME, contentValues, "id=?", new String[]{person.id+""});
sqLiteDatabase.close();
} catch (Exception e) {
e.printStackTrace();
}
}

public synchronized int getCount()
{
int count = 0;
SQLiteDatabase sqLiteDatabase = samSQLHelper.getWritableDatabase();
System.out.println("getVersion = "+sqLiteDatabase.getVersion());
try {
String queryString = String.format("select * from %s", TABLE_NAME);
System.out.println(queryString);
Cursor cursor = sqLiteDatabase.rawQuery(queryString, null);
count = cursor.getCount();
cursor.close();
sqLiteDatabase.close();
} catch (Exception e) {
e.printStackTrace();
}
return count;
}





class SamSQLHelper extends SQLiteOpenHelper
{
public void onCreate(SQLiteDatabase db) 
{
String sql = "create table if not exists "
+ TABLE_NAME + " ("
+ TABLE_COLUNM_ID + " integer primary key,"
+ TABLE_COLUNM_AGE + " integer,"
+ TABLE_COLUNM_NAME + " varchar);";
db.execSQL(sql);
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

String altertablesql = String.format("alter table %s add %s varchar;", TABLE_NAME,TABLE_COLUNM_ADDRESS);
System.out.println(altertablesql);
db.execSQL(altertablesql);


}

public SamSQLHelper(Context context,int version) {
super(context, DB_NAME, null, version);
}
}


}