android SQLite 使用实例

首页 > 代码库 > android SQLite 使用实例

2024-07-05 04:25:06 229人阅读

Android作为目前主流的移动操作系统，完全符合SQLite占用资源少的优势，故在Android平台上，集成了一个嵌入式关系型数据库—SQLite。如果想要开发 Android 应用程序，需要在 Android 上存储数据，使用SQLite 数据库是一种非常好的选择。在一般程序中使用数据库的过程都可以框架化，套路化，实例如下：

表说明：

1.班级 classes：

class_id 主键 class_name

2.学生 students：

student_id 主键 student_name score class_id 外键

创建表：

CREATE TABLE classes(class_id varchar(10) primary key , class_name varchar(20))

CREATE TABLE students(student_id varchar(10) primary key ,

                                               student_name varchar(20) ,
                                             score varchar(4) ,
                                             class_id varchar(10),

foreign key (class_id) references classes(class_id) on delete cascade on update cascade )

1. 继承扩展 SQLiteOpenHelper 创建数据库和对应表

package com.tang.databasedemo;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper
{

	public DBHelper(Context context) {
		super(context, "info.db", null, 1);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) 
	{
		// TODO Auto-generated method stub
		String classesSQL = "CREATE TABLE classes(class_id varchar(10) primary key , " +
        		"class_name varchar(20))";
		
		String studentsSQL = "CREATE TABLE students(student_id varchar(10) primary key , " +
        		"student_name varchar(20) ,score varchar(4) ,class_id varchar(10), " +
        		"foreign key (class_id) references classes(class_id) " +
        		"on delete cascade on update cascade )";
		db.execSQL(classesSQL);
		Log.d("my", "create table classes:"+classesSQL);
		db.execSQL(studentsSQL);
		Log.d("my", "create table students:"+studentsSQL);
		
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
	{
		// TODO Auto-generated method stub
		
	}

}

2. 创建学生（Class）学生（Student）实体

package com.tang.databasedemo;
import android.util.Log;
public class Class 
{
	private String classId;
	private String className;
	
	public String getClassId() {
		return classId;
	}
	public void setClassId(String classId) {
		this.classId = classId;
	}
	public String getClassName() {
		return className;
	}
	public void setClassName(String className) {
		this.className = className;
	}
	public String toString() {
		return "Class--->"+"classId:"+classId+"  className:"+className;

	}
}

package com.tang.databasedemo;
public class Student 
{
	private String studentId;
	private String studentName;
	private String score;
	private String classId;
	
	public String getStudentId() {
		return studentId;
	}
	public void setStudentId(String studentId) {
		this.studentId = studentId;
	}
	public String getStudentName() {
		return studentName;
	}
	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}
	public String getScore() {
		return score;
	}
	public void setScore(String score) {
		this.score = score;
	}
	public String getClassId() {
		return classId;
	}
	public void setClassId(String classId) {
		this.classId = classId;
	}
	public String  toString() 
	{
		return "Student--->"+"studentId:"+studentId+" studentName:"+studentName+" score:"+score+" classId:"+classId;
		
		
	}

}

3. 创建DBServer类，在该类中定义增删改查等方法来操作数据库

package com.tang.databasedemo;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class DBServer 
{
	private DBHelper dbhelper;
	public DBServer(Context context)
	{
		 this.dbhelper = new DBHelper(context);
	}
	/**
	 * 添加班级
	 * @param entity
	 */
	public void addClass(Class entity)
	{
		  
		  SQLiteDatabase localSQLiteDatabase = this.dbhelper.getWritableDatabase();
		  Object[] arrayOfObject = new Object[2];
		  arrayOfObject[0] = entity.getClassId();
		  arrayOfObject[1] = entity.getClassName();
		  localSQLiteDatabase.execSQL("insert into classes(class_id,class_name) values(?,?)", arrayOfObject);
		  localSQLiteDatabase.close();
	}
	/**
	 * 添加学生
	 * @param entity
	 */
	public void addStudent(Student entity)
	{
		  
		  SQLiteDatabase localSQLiteDatabase = this.dbhelper.getWritableDatabase();
		  Object[] arrayOfObject = new Object[4];
		  arrayOfObject[0] = entity.getStudentId();
		  arrayOfObject[1] = entity.getStudentName();
		  arrayOfObject[2] = entity.getScore();
		  arrayOfObject[3] = entity.getClassId();
		  localSQLiteDatabase.execSQL("insert into students(student_id,student_name,score,class_id) values(?,?,?,?)", arrayOfObject);
		  localSQLiteDatabase.close();
	}
	
	/**
	 * 删除一个班级
	 * 同时会删除students中该班级的学生
	 * @param class_id
	 */
	public void deleteClass(String class_id)
	{
	    SQLiteDatabase localSQLiteDatabase = this.dbhelper.getWritableDatabase();
	    //设置了级联删除和级联更新
	    //在执行有级联关系的语句的时候必须先设置“PRAGMA foreign_keys=ON”
	    //否则级联关系默认失效
	    localSQLiteDatabase.execSQL("PRAGMA foreign_keys=ON");
	    Object[] arrayOfObject = new Object[1];
	    arrayOfObject[0] =class_id;
	    localSQLiteDatabase.execSQL("delete from classes where class_id=?", arrayOfObject);
	    localSQLiteDatabase.close();
	}
	
	/**
	 * 删除一个学生
	 * @param student_id
	 */
	public void deleteStudent(String student_id)
	{
	    SQLiteDatabase localSQLiteDatabase = this.dbhelper.getWritableDatabase();
	    Object[] arrayOfObject = new Object[1];
	    arrayOfObject[0] =student_id;
	    localSQLiteDatabase.execSQL("delete from students where student_id=?", arrayOfObject);
	    localSQLiteDatabase.close();
	}
	
	/**
	 * 修改学生信息
	* @param entity
	*/
   public void updateStudentInfo(Student entity)
   {
	   SQLiteDatabase localSQLiteDatabase = this.dbhelper.getWritableDatabase();
	   Object[] arrayOfObject = new Object[4];
	   
	   arrayOfObject[0] = entity.getStudentName();
	   arrayOfObject[1] = entity.getScore();
	   arrayOfObject[2] = entity.getClassId();
	   arrayOfObject[3] = entity.getStudentId();
   
	   localSQLiteDatabase.execSQL("update students set student_name=?,score=?,class_id=?  where student_id=?", arrayOfObject);
	   localSQLiteDatabase.close();
   }
	
	/**
	 * 使用班级编号查找该班级所有学生
	 * @param classId
	 * @return
	 */
	public List<Student> findStudentsByClassId(String classId)
	{
		  List<Student> localArrayList=new ArrayList<Student>();
		  SQLiteDatabase localSQLiteDatabase = this.dbhelper.getWritableDatabase();    
		  Cursor localCursor = localSQLiteDatabase.rawQuery("select student_id, student_name ,score  from students  " +
		  		"where class_id=?  order by score desc", new String[]{classId});
		 
		  while (localCursor.moveToNext())
		  {
			  Student temp=new Student();
			  temp.setStudentId(localCursor.getString(localCursor.getColumnIndex("student_id")));
			  temp.setStudentName(localCursor.getString(localCursor.getColumnIndex("student_name")));
			  temp.setScore(localCursor.getString(localCursor.getColumnIndex("score")));
			  temp.setClassId(classId);
		      localArrayList.add(temp);
		  }
		  localSQLiteDatabase.close();
		  return localArrayList;
	 }
	
	/**
	 * 使用班级名查找该班级所有学生
	 * @param className
	 * @return
	 */
	public List<Student> findStudentsByClassName(String className)
	{
		  List<Student> localArrayList=new ArrayList<Student>();
		  SQLiteDatabase localSQLiteDatabase = this.dbhelper.getWritableDatabase();    
		  Cursor localCursor = localSQLiteDatabase.rawQuery("select student_id, student_name,score,classes.class_id from students,classes" +
		  		" where students.class_id=classes.class_id and classes.class_name =?  order by score asc" , new String[]{className});
		 
		  while (localCursor.moveToNext())
		  {
			  Student temp=new Student();
			  temp.setStudentId(localCursor.getString(localCursor.getColumnIndex("student_id")));
			  temp.setStudentName(localCursor.getString(localCursor.getColumnIndex("student_name")));
			  temp.setScore(localCursor.getString(localCursor.getColumnIndex("score")));
			  temp.setClassId(localCursor.getString(3));
		      localArrayList.add(temp);
		  }
		  localSQLiteDatabase.close();
		  return localArrayList;
	 }
	/**
	 * 查找所有学生
	 * @param className
	 * @return
	 */
	public List<Student> findAllStudents()
	{
		  List<Student> localArrayList=new ArrayList<Student>();
		  SQLiteDatabase localSQLiteDatabase = this.dbhelper.getWritableDatabase();    
		  Cursor localCursor = localSQLiteDatabase.rawQuery("select * from students " +
		  		"where 1=1  order by score desc ", null);
		  while (localCursor.moveToNext())
		  {
			  Student temp=new Student();
			  temp.setStudentId(localCursor.getString(localCursor.getColumnIndex("student_id")));
			  temp.setStudentName(localCursor.getString(localCursor.getColumnIndex("student_name")));
			  temp.setScore(localCursor.getString(localCursor.getColumnIndex("score")));
			  temp.setClassId(localCursor.getString(localCursor.getColumnIndex("class_id")));
		      localArrayList.add(temp);
		  }
		  localSQLiteDatabase.close();
		  return localArrayList;
	 }
	
	
	/**
	 * 	取得所有班级
	 * @return
	 */
	public List<Class> findAllClasses()
	{
		  List<Class> localArrayList=new ArrayList<Class>();
		  SQLiteDatabase localSQLiteDatabase = this.dbhelper.getWritableDatabase();    
		  Cursor localCursor = localSQLiteDatabase.rawQuery("select * from classes " +
		  		"where 1=1", null);
		  while (localCursor.moveToNext())
		  {
			  Class temp=new Class();
			  temp.setClassId(localCursor.getString(localCursor.getColumnIndex("class_id")));
			  temp.setClassName(localCursor.getString(localCursor.getColumnIndex("class_name")));
		      localArrayList.add(temp);
		  }
		  localSQLiteDatabase.close();
		  return localArrayList;
	 }
	
	/**
	 * 成绩最好
	 * @return
	 */
	public Student findMaxScoreStudent()
	{
		Student temp =new Student();
		SQLiteDatabase localSQLiteDatabase = this.dbhelper.getWritableDatabase();    
		Cursor localCursor = localSQLiteDatabase.rawQuery("select student_id,student_name,class_id,max(score)  from students  " +
		  		"where 1=1",null );
		localCursor.moveToFirst();
		temp.setStudentId(localCursor.getString(0));
		temp.setStudentName(localCursor.getString(1));
		temp.setClassId(localCursor.getString(2));
		temp.setScore(localCursor.getString(3));
		return temp;
	}
	

	
	/**
	 * 查找是否有该学生
	 * @param studentId
	 * @return
	 */
	public boolean isStudentsExists(String studentId)
	{
		  SQLiteDatabase localSQLiteDatabase = this.dbhelper.getWritableDatabase();    
		  Cursor localCursor = localSQLiteDatabase.rawQuery("select count(*)  from students  " +
		  		"where student_id=?", new String[]{studentId});
		  localCursor.moveToFirst();
		  if(localCursor.getLong(0)>0)
			  return true;
		  else
			  return false;
	 }
	
	/**
	 * 确认该班级是否存在
	 * @param classId
	 * @return
	 */
	public boolean isClassExists(String s)
	{
		  SQLiteDatabase localSQLiteDatabase = this.dbhelper.getWritableDatabase();    
		  Cursor localCursor = localSQLiteDatabase.rawQuery("select count(*)  from classes  " +
		  		"where class_id=? or class_name=?", new String[]{s,s});
		  localCursor.moveToFirst();
		  if(localCursor.getLong(0)>0)
			  return true;
		  else
			  return false;
	 }

}

4.调用DBServer里的方法，操作数据

package com.tang.databasedemo;

import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import android.os.Bundle;
import android.os.Handler;
import android.os.Message;
import android.app.Activity;
import android.app.AlertDialog;
import android.app.Dialog;
import android.content.DialogInterface;
import android.content.SharedPreferences;
import android.text.AlteredCharSequence;
import android.util.Log;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;

public class MainActivity extends Activity implements OnClickListener {

	private List<Class> classData =http://www.mamicode.com/new ArrayList();>
附图一张:

注：
1 . 关于游标（Cursor）
    在查询返回的是一个Cursor类型的对象，它是一个指针，且永远都不会为空，所以，当查询某语句，并判断返回值是否为空时，切勿用cursor==null表示。而有个方法,cursor.getCount()==0就能判断其结果值是否为空了。
close() 
关闭游标，释放资源 
copyStringToBuffer(int columnIndex, CharArrayBuffer buffer) yB
在缓冲区中检索请求的列的文本，将将其存储 
getColumnCount() 
返回所有列的总数 
getColumnIndex(String columnName) 
返回指定列的名称，如果不存在返回-1 
getColumnIndexOrThrow(String columnName) 
从零开始返回指定列名称，如果不存在将抛出IllegalArgumentException 异常。 
getColumnName(int columnIndex) 
从给定的索引返回列名 
getColumnNames() 
返回一个字符串数组的列名 
getCount() 
返回Cursor 中的行数 
moveToFirst() 
移动光标到第一行 
moveToLast() 
移动光标到最后一行 
moveToNext() 
移动光标到下一行 
moveToPosition(int position) 
移动光标到一个绝对的位置 
moveToPrevious() 
移动光标到上一行
2. 关于on delete cascade on update cascade 级联更新和级联删除
   SQLite在3.6.19版本中才开始支持外键约束，但是为了兼容以前的程序，默认并没有启用该功能，如果要启用该功能每次都要需要使用如下语句：PRAGMA foreign_keys = ON来打开。也就是说，在执行删除一个班级的语句的时候需要执行db.execSQL("PRAGMA foreign_keys=ON")
可见：http://blog.csdn.net/tangnengwu/article/details/25980263
可执行代码：
http://download.csdn.net/detail/tangnengwu/7369503

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > android SQLite 使用实例

android SQLite 使用实例

看完仍有疑问？有类似问题直接问程序猿