android sqlite的一些简单的操作

最新推荐文章于 2024-08-26 18:53:57 发布

samyou090

最新推荐文章于 2024-08-26 18:53:57 发布

阅读量79

点赞数

分类专栏： android 文章标签： SQLite Android SQL

本文链接：https://blog.csdn.net/samyou090/article/details/83887135

版权

android 专栏收录该内容

18 篇文章 0 订阅

订阅专栏


//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;
    }



}