Android笔记（四十一） Android中的数据存储——SQLite（三）select
SQLite 通过query实现查询，它通过一系列参数来定义查询条件。
各参数说明：
| query()方法参数 | 对应sql部分 | 描述 |
| table | from table_name | 表名称 |
| colums | select column1,column2 | 列名称数组 |
| selection | where column = value | 条件子句，相当于where |
| selectionArgs | - | 条件语句的参数数组 |
| groupBy | group by column | 分组 |
| having | having column = value | 分组条件 |
| orderBy | order by column,column | 排序类 |
| limit | 分页查询的限制 | |
| Cursor | 返回值，相当于结果集ResultSet |
针对游标（Cursor）也提供了不少方法
| 方法名称 | 方法描述 |
| getCount() | 总记录条数 |
| isFirst() | 判断是否第一条记录 |
| isLast() | 判断是否最后一条记录 |
| moveToFirst() | 移动到第一条记录 |
| moveToLast() | 移动到最后一条记录 |
| move(int offset) | 移动到指定的记录 |
| moveToNext() | 移动到下一条记录 |
| moveToPrevious() | 移动到上一条记录 |
| getColumnIndex(String columnName) | 获得指定列索引的int类型值 |
下面我们通过例子来演示一下SQLite中的查询：
不带参数查询
MainActivity.java
package cn.lixyz.sqlite; import android.app.Activity; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteOpenHelper; import android.os.Bundle; import android.util.Log; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.Toast; public class MainActivity extends Activity { private EditText name, age; private Button insertButton, selectButton; private SQLiteDatabase database; private MySQLiteOpenHelper msop; public String inputSex; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); findView(); msop = new MySQLiteOpenHelper(this, "user.db", null, 1); database = msop.getReadableDatabase(); } private void findView() { name = (EditText) findViewById(R.id.name); age = (EditText) findViewById(R.id.age); insertButton = (Button) findViewById(R.id.insertButton); selectButton = (Button) findViewById(R.id.selectButton); } public void clickButton(View view) { switch (view.getId()) { case R.id.selectButton: selectData(); break; case R.id.insertButton: insertData(); break; } } private void insertData() { String inputAge = age.getText().toString(); String inputName = name.getText().toString(); ContentValues cv = new ContentValues(); cv.put("name", inputName); cv.put("age", inputAge); database.insert("user", null, cv); Toast.makeText(MainActivity.this, "插入成功", Toast.LENGTH_SHORT).show(); age.setText(""); name.setText(""); } private void selectData() { Cursor c = database.query("user", null, null, null, null, null, null); if (c.moveToFirst()) { do { int id = c.getInt(c.getColumnIndex("id")); String name = c.getString(c.getColumnIndex("name")); String age = c.getString(c.getColumnIndex("age")); Log.d("TTTT", "id=" + id + ",姓名=" + name + ",年龄=" + age); } while (c.moveToNext()); } c.close(); } class MySQLiteOpenHelper extends SQLiteOpenHelper { private static final String CREATE_USER = "create table user(id integer primary key autoincrement,name text,age text)"; private Context mContext; public MySQLiteOpenHelper(Context context, String name, CursorFactory factory, int version) { super(context, name, factory, version); mContext = context; } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_USER); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub } } }
activity_main.xml
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" tools:context=".MainActivity" > <EditText android:id="@+id/name" android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="输入姓名" /> <EditText android:id="@+id/age" android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="输入年龄" /> <Button android:id="@+id/insertButton" android:layout_width="match_parent" android:layout_height="wrap_content" android:onClick="clickButton" android:text="点击插入" /> <Button android:id="@+id/selectButton" android:layout_width="match_parent" android:layout_height="wrap_content" android:onClick="clickButton" android:text="点击查询" /> </LinearLayout>
先插入几条数据，然后点击查询按钮：
带参数查询
通过rawQuery实现的带参数查询
修改一下代码
MainActivity.java
package cn.lixyz.sqlite; import android.app.Activity; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteOpenHelper; import android.os.Bundle; import android.util.Log; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.Toast; public class MainActivity extends Activity { private EditText name, age, paramter; private Button insertButton, selectButton, paramterSelect; private SQLiteDatabase database; private MySQLiteOpenHelper msop; public String inputSex; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); findView(); msop = new MySQLiteOpenHelper(this, "user.db", null, 1); database = msop.getReadableDatabase(); } private void findView() { name = (EditText) findViewById(R.id.name); age = (EditText) findViewById(R.id.age); insertButton = (Button) findViewById(R.id.insertButton); selectButton = (Button) findViewById(R.id.selectButton); paramter = (EditText) findViewById(R.id.paramter); paramterSelect = (Button) findViewById(R.id.paramterSelect); } public void clickButton(View view) { switch (view.getId()) { case R.id.selectButton: selectData(); break; case R.id.insertButton: insertData(); break; case R.id.paramterSelect: paramterSelect(); } } private void paramterSelect() { String inputAge = paramter.getText().toString(); Cursor c = database.rawQuery("select * from user where age>?", new String[] { inputAge }); if (c.moveToFirst()) { do { int id = c.getInt(c.getColumnIndex("id")); String name = c.getString(c.getColumnIndex("name")); String age = c.getString(c.getColumnIndex("age")); Log.d("TTTT", "id=" + id + ",name=" + name + ",age=" + age); } while (c.moveToNext()); } c.close(); } private void insertData() { String inputAge = age.getText().toString(); String inputName = name.getText().toString(); ContentValues cv = new ContentValues(); cv.put("name", inputName); cv.put("age", inputAge); database.insert("user", null, cv); Toast.makeText(MainActivity.this, "插入成功", Toast.LENGTH_SHORT).show(); age.setText(""); name.setText(""); } private void selectData() { Cursor c = database.query("user", null, null, null, null, null, null); if (c.moveToFirst()) { do { int id = c.getInt(c.getColumnIndex("id")); String name = c.getString(c.getColumnIndex("name")); String age = c.getString(c.getColumnIndex("age")); Log.d("TTTT", "id=" + id + ",姓名=" + name + ",年龄=" + age); } while (c.moveToNext()); } c.close(); } class MySQLiteOpenHelper extends SQLiteOpenHelper { private static final String CREATE_USER = "create table user(id integer primary key autoincrement,name text,age text)"; private Context mContext; public MySQLiteOpenHelper(Context context, String name, CursorFactory factory, int version) { super(context, name, factory, version); mContext = context; } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_USER); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub } } }
activity_main.xml
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" tools:context=".MainActivity" > <EditText android:id="@+id/name" android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="输入姓名" /> <EditText android:id="@+id/age" android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="输入年龄" /> <Button android:id="@+id/insertButton" android:layout_width="match_parent" android:layout_height="wrap_content" android:onClick="clickButton" android:text="点击插入" /> <Button android:id="@+id/selectButton" android:layout_width="match_parent" android:layout_height="wrap_content" android:onClick="clickButton" android:text="点击查询" /> <TextView android:id="@+id/text" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="条件搜索" /> <EditText android:id="@+id/paramter" android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="您要搜多少岁以上的？" /> <Button android:id="@+id/paramterSelect" android:layout_width="match_parent" android:layout_height="wrap_content" android:onClick="clickButton" android:text="点击搜索" /> </LinearLayout>
运行结果：
