android app 示例 SQLiteOpenHelper 实现数据操作

 

一个简单的入门 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;
	}
	

}

 

 

登录界面：

 

 

信息列表：
 

 

 

新增信息：