这里主要讲安卓SQLite数据库的帮助类实现简单的数据库操作。
效果图:
界面用到两个TextView和一个Spinner和一个ListView。
1.我们先定义一些常量,等下会用到
private static final String DBNAME = "myclass.db";//数据库名
private static final String STABLENAME = "student";//数据库中的表1
private static final String CTABLENAME = "course";//表2(创建了两个表,在于增加难度)
private static final int VERSION = 1;//数据库版本(目的用于更新)
2.定义一些模拟数据
public static final String[][] STUDENTS = { { "A123", "rose" },
{ "B456", "jack" }, { "C789", "karl" }, { "D012", "如花" },
{ "E345", "小白" },
};
public static final String[][] COURSE = { { "A123", "java" },
{ "A123", "android" }, { "B456", "android" }, { "B456", "swift" },
{ "C789", "java web" }, { "C789", "gnu c" }, { "D012", "html5" },
{ "D012", "vc++" }, { "E345", "swift" }, { "E345", "linux c" },
{ "E345", "vc" }, { "E345", "oc" }, };
3.自定义一个DBHelper类,继承自SQLiteOpenHelper。
public class DBHelper extends SQLiteOpenHelper {
private Context context;//上下文对象(Activity.this)
public DBHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
this.context = context;
}
@Override //数据库表的创建和初始化
public void onCreate(SQLiteDatabase db) {
SQLiteStatement stmt;
// 涉及多个表和数据用事务保证完整一致
db.beginTransaction();
try {
//创建学生表
String studentSQL="create table student(sid text primary key,sname text not null)";
db.execSQL(studentSQL);//执行SQL语句
//添加学生记录
stmt=db.compileStatement("insert into student values(?,?)");
for(String[] record:MainActivity.STUDENTS){
stmt.bindString(1, record[0]);
stmt.bindString(2, record[1]);
stmt.executeInsert();
}
//创建课程表
String courseSQL="create table course(sid text not null,cname text not null)";
db.execSQL(courseSQL);
//添加课程记录
stmt=db.compileStatement("insert into course values(?,?)");
for(String[] record:MainActivity.COURSE){
stmt.bindString(1, record[0]);
stmt.bindString(2, record[1]);
stmt.executeInsert();
}
//成功提交
db.setTransactionSuccessful();
} catch (Exception e) {
Toast.makeText(context, "初始化数据库失败!!",Toast.LENGTH_SHORT).show();
}finally{
db.endTransaction();
}
}
@Override //用于版本更新
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
4.在活动onCrate方法中
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
txtName = (TextView) findViewById(R.id.txtName);
sp = (Spinner) findViewById(R.id.spinner1);
listview = (ListView) findViewById(R.id.listView);
DBHelper helper = new DBHelper(this, DBNAME, null, VERSION);
db = helper.getWritableDatabase(); // 通知可以数据库可以执行操作了
// 读取数据并存入数组
Cursor cursor = db.query(STABLENAME, new String[] { "sid", "sname" },
null, null, null, null, null);//'sid','sname'>>数据库中的字段
String[] idStrings = null;
if (cursor != null) {
cursor.moveToFirst();
idStrings = new String[cursor.getCount()];
for (int i = 0; i < idStrings.length; i++) {
idStrings[i] = cursor.getString(0);
cursor.moveToNext();
}
cursor.close();
}
sp.setAdapter(new ArrayAdapter<String>(this,
android.R.layout.simple_spinner_item, idStrings));//数据绑定
sp.setOnItemSelectedListener(this);//item项设置监听器
}
刚才设置了sp的项监听器,现在在重写的onItemSelected方法中,写选择后的操作
这里我们把它封装出来
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position,
long id) {
doQuery(sp.getSelectedItem().toString());
}
private void doQuery(String id) {
// 根据学号找姓名
Cursor cursor = db.query(STABLENAME, new String[] { "sname" }, "sid=?",
new String[] { id }, null, null, null);//使用cursor对象,可以获取查询返回的数据
if (cursor != null) {
cursor.moveToFirst();
String name = cursor.getString(0);
String s = "你的姓名是:" + name;
txtName.setText(s);
cursor.close();
}
// 根据学号找选修课程
cursor = db.query(CTABLENAME, new String[] { "cname" }, "sid=?",
new String[] { id }, null, null, null);
String[] courselist = null;
if (cursor != null) {
cursor.moveToFirst();
courselist = new String[cursor.getCount()];
for (int i = 0; i < courselist.length; i++) {
courselist[i] = cursor.getString(0);
cursor.moveToNext();
}
cursor.close();
}
//绑定数据
listview.setAdapter(new ArrayAdapter<String>(this,
android.R.layout.simple_list_item_1, courselist));
}
最后记得把数据库给关了
@Override
protected void onDestroy() {
super.onDestroy();
db.close();
}
**注:**查询方法query的参数
Cursor android.database.sqlite.SQLiteDatabase.query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
“`
返回类型为Cursor,括号内相对于查询语句:select columns from table where selection = selectionArgs group by groupBy having having order by orderBy;
其中groupBy为<列名列序>,having为<组条件表达式>,orderBy为<列名[asc|desc]>