android sqlite的一些简单的操作

吉佳

//MySQLHelper .java
public class MySQLHelper extends SQLiteOpenHelper
{
public static String DB_NAME = "Samyou";
public static String TABLE_NAME = "user_profile";
public static String COLUME_ID = "id";
public static String COLUME_NAME = "name";
public static String COLUME_AGE = "age";
public static String COLUME_LOCATION = "location";

public MySQLHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db)
{
System.out.println("db onCreate!!!!!!");
Log.e("sqltest", "onCreat table");
String sql = "create table if not exists "
+ TABLE_NAME+ " ("
+ COLUME_ID + " integer primary key,"
+ COLUME_NAME + " varchar,"
+ COLUME_AGE+ " integer,"
+ COLUME_LOCATION +" varchar);";
Log.e("sqltest", sql);
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
db.execSQL("drop table if exists "+TABLE_NAME);
onCreate(db);
}
}
//MainPage.java
public class MainPage extends Activity
{
MySQLHelper mySQLHelper = null;
public void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
mySQLHelper = new MySQLHelper(this,MySQLHelper.DB_NAME,null,8);
ContentValues contentValues = new ContentValues();
contentValues.put(MySQLHelper.COLUME_NAME, "samyou");
long now = System.currentTimeMillis();
contentValues.put(MySQLHelper.COLUME_AGE, now);
System.out.println(now);
contentValues.put(MySQLHelper.COLUME_LOCATION, "loc");
insertColume(contentValues);
ContentValues contentValues2 = new ContentValues();
contentValues2.put(MySQLHelper.COLUME_NAME, "samyou2");
contentValues2.put(MySQLHelper.COLUME_AGE, System.currentTimeMillis());
contentValues2.put(MySQLHelper.COLUME_LOCATION, "loc2");
insertColume(contentValues2);
Vector<ContentValues> v = getAll();
Log.e("sqltest", "v,size: "+v.size());
for(int i=0;i<v.size();i++)
{
ContentValues contentValues3 = v.elementAt(i);
System.out.println("person: "+i);
Log.e("sqltest", "COLUME_ID "+contentValues3.getAsInteger(MySQLHelper.COLUME_ID));
Log.e("sqltest", "COLUME_NAME "+contentValues3.getAsString(MySQLHelper.COLUME_NAME));
Log.e("sqltest", "COLUME_AGE "+contentValues3.getAsLong(MySQLHelper.COLUME_AGE));
Log.e("sqltest", "COLUME_LOCATION "+contentValues3.getAsString(MySQLHelper.COLUME_LOCATION));
}
}
private Vector<ContentValues> getAll()
{
Vector<ContentValues> v = new Vector<ContentValues>();
SQLiteDatabase db = mySQLHelper.getWritableDatabase();
Cursor cursor = db.query(MySQLHelper.TABLE_NAME, new String[]{MySQLHelper.COLUME_ID,
MySQLHelper.COLUME_NAME,
MySQLHelper.COLUME_AGE,
MySQLHelper.COLUME_LOCATION},
null, null, null, null,
MySQLHelper.COLUME_ID);
int idindex = cursor.getColumnIndex(MySQLHelper.COLUME_ID);
int nameindex = cursor.getColumnIndex(MySQLHelper.COLUME_NAME);
int ageindex = cursor.getColumnIndex(MySQLHelper.COLUME_AGE);
int locationindex = cursor.getColumnIndex(MySQLHelper.COLUME_LOCATION);
cursor.moveToFirst();
while(!cursor.isAfterLast())
{
Log.e("", "move cursor");
ContentValues contentValues = new ContentValues();
contentValues.put(MySQLHelper.COLUME_ID, cursor.getInt(idindex));
contentValues.put(MySQLHelper.COLUME_NAME, cursor.getString(nameindex));
contentValues.put(MySQLHelper.COLUME_AGE, cursor.getLong(ageindex));//cursor.getInt(ageindex));
contentValues.put(MySQLHelper.COLUME_LOCATION, cursor.getString(locationindex));
v.add(contentValues);
cursor.moveToNext();
}
cursor.close();
db.close();
return v;
}
private void updateColume(ContentValues contentValues,int id)
{
SQLiteDatabase db = mySQLHelper.getWritableDatabase();
db.update(MySQLHelper.TABLE_NAME, contentValues, MySQLHelper.COLUME_ID+"=?", new String[]{id+""});
db.close();
}
private void deleteColume(int id)
{
Log.e("", "deleteColume");
SQLiteDatabase db = mySQLHelper.getWritableDatabase();
int a = db.delete(MySQLHelper.TABLE_NAME, MySQLHelper.COLUME_ID+"=?", new String[]{id+""});
db.close();
Log.e("", "delete colume: "+ a);
}
private int insertColume(ContentValues contentValues)
{
int cnt = -1;
SQLiteDatabase db = mySQLHelper.getWritableDatabase();
cnt = (int)db.insert(MySQLHelper.TABLE_NAME, MySQLHelper.COLUME_ID, contentValues);
Log.e("sqltest", "insert cnt: "+cnt);
db.close();
return cnt;
}

}