一、查询数据
android的查询数据库操作很复杂,SQLiteDatabase使用了query方法来进行查找数据,内含很多参数,我们来看一下
query()方法参数 对应SQL部分 描述 table from table_name 表名 colums select colum1,colum2 要查询的列名 selection where column = value 约束条件 selectionArgs - 为where中的占位符提供具体的值 groupBy group by column 需要分组的列 having having column = value 对分组后的结果进一步约束 orderBy order by column1,column2 排序方式
调用后会返回一个Cursor对象,查询到的所有对象都会从这里取出来。 修改activity_main.xml
< LinearLayout>
..........省略代码...........
< Button
android: id= " @+id/query_data"
android: layout_width= " match_parent"
android: layout_height= " wrap_content"
android: text= " Query data"
/>
</ LinearLayout>
添加了一个按钮用于查询数据,然后修改MainActivity.java
package com. example. databasetest;
import android. app. Activity;
import android. content. ContentValues;
import android. database. Cursor;
import android. database. sqlite. SQLiteDatabase;
import android. os. Bundle;
import android. util. Log;
import android. view. Menu;
import android. view. MenuItem;
import android. view. View;
import android. view. View. OnClickListener;
import android. widget. Button;
public class MainActivity extends Activity {
private MyDatabaseHelper dbHelper;
@Override
protected void onCreate ( Bundle savedInstanceState) {
super . onCreate ( savedInstanceState) ;
setContentView ( R. layout. activity_main) ;
dbHelper = new MyDatabaseHelper ( this , "BookStore.db" , null, 2 ) ;
. . . . . . . . . . . . . . . 省略代码. . . . . . . . . . . . . . . . . . . . . . . . . .
Button queryButton = ( Button) findViewById ( R. id. query_data) ;
queryButton. setOnClickListener ( new OnClickListener ( ) {
@Override
public void onClick ( View v) {
SQLiteDatabase db = dbHelper. getWritableDatabase ( ) ;
Cursor cursor = db. query ( "Book" , null, null, null, null, null, null) ;
if ( cursor. moveToFirst ( ) ) {
do {
String name = cursor. getString ( cursor. getColumnIndex ( "name" ) ) ;
String author = cursor. getString ( cursor. getColumnIndex ( "author" ) ) ;
int pages = cursor. getInt ( cursor. getColumnIndex ( "pages" ) ) ;
double price = cursor. getDouble ( cursor. getColumnIndex ( "price" ) ) ;
Log. d ( "MainActivity" , "book name is " + name) ;
Log. d ( "MainActivity" , "book author is " + author) ;
Log. d ( "MainActivity" , "book pages is " + pages) ;
Log. d ( "MainActivity" , "book price is " + price) ;
} while ( cursor. moveToNext ( ) ) ;
}
cursor. close ( ) ;
}
} ) ;
}
}
得到Cursor对象之后,调用moveToFirst()方法来把指针移到第一行的位置,然后进入循环,循环条件是moveToNext(),直到指针指到了空的位置才停止;循环内部就是cursor的getObject的方法,来取到对应Object类型的值,方法内传入的参数就是cursor.get+列名;最后关闭cursor
二、使用SQL操作数据库
如果不想使用android提供的方法,就像使用原生的SQL语言,那么也内置了方法来实现
SQLiteDatabase db = dbHelper. getWritableDatabase ( ) ;
db. execSQL ( "insert into Book(name,author,pages,prices) values(?,?,?,?)" , new String [ ] { "xiaoming" , "Dan Brown" , "454" , "16.69" ) ;
如上代码是插入数据的方法,对应的更新数据方法为:execSQL,删除数据:execSQL,查询数据:rawQuery
三、SQLite最佳实践
使用事务
删除旧数据,添加新数据,这两个操作都在一个事务中,我们修改activity_main.xml
< LinearLayout>
..........省略代码...........
< Button
android: id= " @+id/replace_data"
android: layout_width= " match_parent"
android: layout_height= " wrap_content"
android: text= " Replace data"
/>
</ LinearLayout>
修改MainActivity.java,在最后添加如下按钮绑定的事件
Button replaceData = ( Button) findViewById ( R. id. replace_data) ;
replaceData. setOnClickListener ( new OnClickListener ( ) {
@Override
public void onClick ( View v) {
SQLiteDatabase db = dbHelper. getWritableDatabase ( ) ;
db. beginTransaction ( ) ;
try {
db. delete ( "Book" , null, null) ;
if ( true ) {
throw new NullPointerException ( ) ;
}
ContentValues values = new ContentValues ( ) ;
values. put ( "name" , "Game of Thrones" ) ;
values. put ( "author" , "George Martin" ) ;
values. put ( "pages" , 720 ) ;
values. put ( "price" , 20.85 ) ;
db. insert ( "Book" , null, values) ;
db. setTransactionSuccessful ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
db. endTransaction ( ) ;
}
}
} ) ;
四、源码: