SQL数据库简单语句

leader让我给他写一个简单的数据库类demo

之前也没有写过相关的文档，为了以后使用方便，总结一下：

public class DataBaseHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "demo.db";// 数据库名
	private static final int DATABASE_VERSION = 1;// 数据版本号
	private SQLiteDatabase mSqLiteDatabase;
	private static final String TABLE_NAME = "table_name";// 表名
	public static final String ID = "id";
	public static final String NAME = "name";
	public static final String AGE = "age";
	public static final String GENDER = "gender";

	public DataBaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		mSqLiteDatabase = getWritableDatabase();
	}

	@Override
	public SQLiteDatabase getWritableDatabase() {
		// TODO Auto-generated method stub
		if (mSqLiteDatabase == null) {
			mSqLiteDatabase = super.getWritableDatabase();
		}
		return mSqLiteDatabase;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		mSqLiteDatabase = db;
		initDB();
	}

	  /**
     * 创建数据库表
     */
    private void initDB() {
        mSqLiteDatabase.execSQL("CREATE TABLE " + TABLE_NAME + "(" +
                ID + " INTEGER PRIMARY KEY," +
                NAME + " VARCHAR," +
                AGE + " INTEGER," +
                GENDER + " VARCHAR" +
                ");");
    }

	/**
	 * 插入数据库
	 * 
	 * @param list
	 */
	public void insert(List<PeopleInfo> list) {
		if (list == null || list.size() < 1) {
			return;
		}
		int length = list.size();
		for (int i = 0; i < length; i++) {
			insert(list.get(i));
		}
	}

	/**
	 * 插入数据库
	 * 
	 * @param peopleInfo
	 */
	private void insert(PeopleInfo peopleInfo) {
		// TODO Auto-generated method stub
		ContentValues values = new ContentValues();
		values.put(ID, peopleInfo.getID());
		values.put(NAME, peopleInfo.getName());
		values.put(AGE, peopleInfo.getAge());
		values.put(GENDER, peopleInfo.getGender());
		mSqLiteDatabase.insert(TABLE_NAME, null, values);
	}

	/**
	 * 删除数据库表
	 */
	public void dropTable() {
		String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
		mSqLiteDatabase.execSQL(sql);
	}

	/**
	 * 删除表中对应列
	 * 
	 * @param name
	 */
	public void deleteItem(int id) {
		String sql = "DELETE FROM " + TABLE_NAME + " WHERE " + ID + "=" + id;
		mSqLiteDatabase.execSQL(sql);
	}

	/**
	 * 删除表数据不删除表
	 */
	public void deleteTable() {
		String sql = "DELETE FROM " + TABLE_NAME;
		mSqLiteDatabase.execSQL(sql);
	}

	/**
	 * 更新数据库表数据
	 * 
	 * @param peopleInfo
	 */
	public void update(PeopleInfo peopleInfo) {

		ContentValues values = new ContentValues();
		values.put(NAME, peopleInfo.getName());
		values.put(AGE, peopleInfo.getAge());
		values.put(GENDER, peopleInfo.getGender());
		mSqLiteDatabase.update(TABLE_NAME, values, "id=?",
				new String[] { String.valueOf(peopleInfo.getID()) });
	}

	private Cursor getAll() {
		String sql = "SELECT * FROM " + TABLE_NAME;
		return mSqLiteDatabase.rawQuery(sql, null);
	}

	/**
	 * 获取表中所有数据
	 * 
	 * @return
	 */
	public List<PeopleInfo> getDBAllData() {
		List<PeopleInfo> list = new ArrayList<PeopleInfo>();
		Cursor mCursor = getAll();
		int idIndex = mCursor.getColumnIndexOrThrow(ID);
		int nameIndex = mCursor.getColumnIndexOrThrow(NAME);
		int ageIndex = mCursor.getColumnIndexOrThrow(AGE);
		int genderIndex = mCursor.getColumnIndexOrThrow(GENDER);

		while (mCursor.moveToNext()) {
			PeopleInfo info = new PeopleInfo();
			info.setID(mCursor.getInt(idIndex));
			info.setName(mCursor.getString(nameIndex));
			info.setAge(mCursor.getInt(ageIndex));
			info.setGender(mCursor.getString(genderIndex));
			list.add(info);
		}
		mCursor.close();
		return list;
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		mSqLiteDatabase = db;
		if (oldVersion != newVersion) {
			dropTable();
		}
		onCreate(db);
	}

}