一个简单的入门 Android app 示例, 学生管理系统, 单表查询, 实习增、删、改、查, 基于:SQLiteOpenHelper
部分代码:SQLiteOpenHelper.java
package com.lw.db;
import java.util.ArrayList;
import java.util.List;
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.text.TextUtils;
import com.lw.main.Student;
public class DBHelper extends SQLiteOpenHelper {
private static DBHelper myDBHelper = null;
public DBHelper(Context context, String name, CursorFactory factory, int version) {
super(context, name, factory, version);
}
public static DBHelper getDBHelper(Context context) {
if(myDBHelper == null) {
myDBHelper = new DBHelper(context, DBConfig.DB_NAME, null, DBConfig.DB_VERSION);
}
return myDBHelper;
}
@Override
public void onCreate(SQLiteDatabase db) {
String sql = "create table "
+ DBConfig.TABLE_STUDENT
+ "(_id integer primary key autoincrement, serialNo VARCHAR2(20) not null, name VARCHAR2(50) not null, sex VARCHAR2(4), age VARCHAR2(4), className VARCHAR2(50) not null)";
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
String sql = "DROP TABLE IF EXISTS " + DBConfig.TABLE_STUDENT;
db.execSQL(sql);
onCreate(db);
}
/***
* insert 学生信息
* @param serialNo
* @param name
* @param sex
* @param age
* @param className
* @return
*/
public long insertStudent(String serialNo, String name, String sex, String age, String className) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("serialNo", serialNo);
cv.put("name", name);
cv.put("sex", sex);
cv.put("age", age);
cv.put("className", className);
long rowId=db.insert(DBConfig.TABLE_STUDENT, null, cv);
System.out.println("-----rowId-------"+rowId);
return rowId;
}
/***
* 删除表的(所有)数据
* @param tableName
*/
public void clearData(String tableName) {
SQLiteDatabase db = this.getReadableDatabase();
String sql = "DELETE FROM " + tableName;
db.execSQL(sql);
}
/***
* 删除单条记录
* @param _id
*/
public void deleteStudent(int _id) {
SQLiteDatabase db = this.getReadableDatabase();
String sql = "DELETE FROM " + DBConfig.TABLE_STUDENT + " WHERE _id=" + _id;
db.execSQL(sql);
}
/***
* 编辑
* @param _id
*/
public void updateStudent(Student stu) {
SQLiteDatabase db = this.getReadableDatabase();
ContentValues cv = new ContentValues();
//cv.put("serialNo", stu.getSerialNo());
cv.put("name", stu.getName());
cv.put("sex", stu.getSex());
cv.put("age", stu.getAge());
cv.put("className", stu.getClassName());
int rowId=db.update(DBConfig.TABLE_STUDENT, cv, "_id =?", new String[]{ stu.get_id() + "" });
System.out.println("-----update RowId------"+rowId);
}
/***
* 查询全部学生信息
* @param stu
* @return
*/
public List<Student> queryStudents(Student stu) {
List<Student> list = new ArrayList<Student>();
SQLiteDatabase db = this.getReadableDatabase();
String sql = "select * from " + DBConfig.TABLE_STUDENT;
if(stu != null) {
sql += " where 1=1 ";
if(TextUtils.isEmpty(stu.getSerialNo()) == false) {
// TODO
}
if(TextUtils.isEmpty(stu.getName()) == false) {
sql += " and name like '%" + stu.getName() + "%'";
}
}
sql += " order by serialNo ";
Cursor cursor = db.rawQuery(sql, null);
Student info = null;
while(cursor.moveToNext()) {
info = new Student();
info.set_id(cursor.getInt(cursor.getColumnIndexOrThrow("_id")));
info.setSerialNo(cursor.getString(cursor.getColumnIndexOrThrow("serialNo")));
info.setName(cursor.getString(cursor.getColumnIndexOrThrow("name")));
info.setSex(cursor.getString(cursor.getColumnIndexOrThrow("sex")));
info.setAge(cursor.getString(cursor.getColumnIndexOrThrow("age")));
info.setClassName(cursor.getString(cursor.getColumnIndexOrThrow("className")));
list.add(info);
}
return list;
}
}
登录界面:
信息列表:
新增信息: