Android学习_17_SQLite数据库存储方式

SQLite最大特点：无数据类型数据库。

SQLite可以解析大部分标准SQL语句。

编写一个数据库应用：

1 创建数据库，自动创建数据库功能（第一次使用时创建）创建的路径：

<包>/databases/

SQLiteOpenHelper     .getReadableDatabase()或.getWritableDatabase()方法。

SQL语句 创建数据表语句

CREATE TABLE person(personid integer primary key autoincrement,name varchar(20))

表名                         id                                                组件 类型为整形                               自增长                                               字段类型     

执行SQL语句：

db.exeSQL();  db 为SQLiteDatabase类的对象

SQL语句 增加数据

ALTER TABLE person ADD phone VARCHAR(12) NULL

2 jdbc connection 在android中自动加载

实现增删改查代码：

	/**
	 * 添加记录
	 * @param person
	 */
	public void save(Person person) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.<span style="color:#ff6666;">execSQL</span>("insert into person(name,phone) values(?,?)",
				new Object[]{person.getName(),person.getPhone()});
	}
	/**
	 * 删除记录
	 * @param id
	 */
	public void delete(Integer id) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("delete from person where personid=?",new Object[]{id});
	}
	/**
	 * 更新记录
	 * @param person
	 */
	public void update(Person person) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.execSQL("update person set name=?,phone=? where personid=?",
				new Object[]{person.getName(),person.getPhone(),person.getId()});
	}
	/**
	 * 查找记录
	 * @param id
	 * @return
	 */
	public Person find(Integer id) {
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.<span style="color:#ff6666;">rawQuery</span>("select*from person where personid=?", new String[]{id.toString()});
		if(cursor.moveToFirst()){
			int personid = cursor.getInt(cursor.getColumnIndex("personid"));
			String name =  cursor.getString(cursor.getColumnIndex("name"));
			String phone =  cursor.getString(cursor.getColumnIndex("phone"));
			return new Person(personid,name,phone);
		}
				return null;
	}
	/**
	 * 分页获取记录
	 * @param offset 跳过前面多少条记录
	 * @param maxResult 每获取多少条记录
	 * @return
	 */
	public List<Person> getScrollData(int offset,int maxResult){
		List<Person> persons = new ArrayList<Person>();
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select*from person order by personid asc limit ?,?",
				new String[]{String.valueOf(offset),String.valueOf(maxResult)});
		while(cursor.moveToNext())
		{
			int personid = cursor.getInt(cursor.getColumnIndex("personid"));
			String name =  cursor.getString(cursor.getColumnIndex("name"));
			String phone =  cursor.getString(cursor.getColumnIndex("phone"));
			persons.add(new Person(personid,name,phone));
		}
		cursor.close();
		return persons;
	}
	/**
	 * 获取记录总数
	 * @return
	 */
	public long getCount(){//查询记录  统计
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery("select count(*) from person",null);
		cursor.moveToFirst();
		long result = cursor.getLong(0);
		cursor.close();
		return result;
	}

由SQLiteDatabase提供的增删改查方法

insert()、delete()、update()、query()

/**
	 * 添加记录
	 * @param person
	 */
	public void save(Person person) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("name", person.getName());
		values.put("phone", person.getPhone());
		db.insert("person", null, values);
	}
	/**
	 * 删除记录
	 * @param id
	 */
	public void delete(Integer id) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		db.delete("person", "personid=?", new String[]{id.toString()});
	}
	/**
	 * 更新记录
	 * @param person
	 */
	public void update(Person person) {
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("name", person.getName());
		values.put("phone", person.getPhone());
		db.update("person", values, "personid", new String[]{person.getId().toString()});
	}
	/**
	 * 查找记录
	 * @param id
	 * @return
	 */
	public Person find(Integer id) {
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.query("person", null, "personid=?", new String[]{id.toString()}, null, null, null);
		if(cursor.moveToFirst()){
			int personid = cursor.getInt(cursor.getColumnIndex("personid"));
			String name =  cursor.getString(cursor.getColumnIndex("name"));
			String phone =  cursor.getString(cursor.getColumnIndex("phone"));
			return new Person(personid,name,phone);
		}
				return null;
	}
	/**
	 * 分页获取记录
	 * @param offset 跳过前面多少条记录
	 * @param maxResult 每获取多少条记录
	 * @return
	 */
	public List<Person> getScrollData(int offset,int maxResult){
		List<Person> persons = new ArrayList<Person>();
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.query("person", null, null, null, null, null, "personid asc", offset+","+maxResult);
		
		while(cursor.moveToNext())
		{
			int personid = cursor.getInt(cursor.getColumnIndex("personid"));
			String name =  cursor.getString(cursor.getColumnIndex("name"));
			String phone =  cursor.getString(cursor.getColumnIndex("phone"));
			persons.add(new Person(personid,name,phone));
		}
		cursor.close();
		return persons;
	}
	/**
	 * 获取记录总数
	 * @return
	 */
	public long getCount(){//查询记录  统计
		SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
		Cursor cursor = db.query("person", new String[]{"count(*)"}, null, null, null, null, null);
		//Cursor cursor = db.rawQuery("select count(*) from person",null);
		cursor.moveToFirst();
		long result = cursor.getLong(0);
		cursor.close();
		return result;
	}