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
findStudentsByClassId(String classId)
{
List localArrayList=new ArrayList();
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 findStudentsByClassName(String className)
{
List localArrayList=new ArrayList();
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 findAllStudents()
{
List localArrayList=new ArrayList();
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 findAllClasses()
{
List localArrayList=new ArrayList();
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 classData =new ArrayList();
private List studentsData =new ArrayList();
private static final String className ="A/B/C/D/E";
private static final String studentName ="彭大/黄二/张三/李四/王五/郑六/田七/周八/叶九/孔十/萧十一";
private DBServer db;
private SharedPreferences share;
private SharedPreferences.Editor editor;
private String info ="";
private EditText editText;
private Button b,b1,b2,b3,b4,b5,b6;
private EditText sId,sName,score,cId,cName;
private Handler hander =new Handler()
{
@Override
public void handleMessage(Message msg)
{
// TODO Auto-generated method stub
if(msg.what==0)
{
sId.setText("");
sName.setText("");
score.setText("");
cName.setText("");
cId.setText("");
}
else if(msg.what==1)
{
db.deleteClass((String)msg.obj);
info += "删除一个班级及班级里面的学生：班级Id:"+(String)msg.obj;
editText.setText(info);
}
}
};
@Override
protected void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
initView();
share = getSharedPreferences("DatabaseDamo", 0);
editor =share.edit();
db=new DBServer(this);
if(share.getInt("times", 0)==0)
{
initDatabase();
editor.putInt("times", 1);
editor.commit();
}
}
private void initView()
{
editText = (EditText) findViewById(R.id.info);
sId = (EditText) findViewById(R.id.studentId);
sName = (EditText) findViewById(R.id.studentName);
score = (EditText) findViewById(R.id.score);
cId = (EditText) findViewById(R.id.classId);
cName = (EditText) findViewById(R.id.className);
b =(Button) findViewById(R.id.button);
b1 =(Button) findViewById(R.id.button1);
b2 =(Button) findViewById(R.id.button2);
b3 =(Button) findViewById(R.id.button3);
b4 =(Button) findViewById(R.id.button4);
b5 =(Button) findViewById(R.id.button5);
b6 =(Button) findViewById(R.id.button6);
b.setOnClickListener(this);
b1.setOnClickListener(this);
b2.setOnClickListener(this);
b3.setOnClickListener(this);
b4.setOnClickListener(this);
b5.setOnClickListener(this);
b6.setOnClickListener(this);
}
private void initDatabase()
{
info="";
editText.setText("");
String []classTemp = className.split("/");
Class c;
for(int i=0;iOnce opened successfully, the database is cached, so you can
* call this method every time you need to write to the database.
* (Make sure to call {@link #close} when you no longer need the database.)
* Errors such as bad permissions or a full disk may cause this method
* to fail, but future attempts may succeed if the problem is fixed.
*
* Database upgrade may take a long time, you
* should not call this method from the application main thread, including
* from {@link android.content.ContentProvider#onCreate ContentProvider.onCreate()}.
*
* @throws SQLiteException if the database cannot be opened for writing
* @return a read/write database object valid until {@link #close} is called
*/
public SQLiteDatabase getWritableDatabase() {
synchronized (this) {
return getDatabaseLocked(true);
}
}
/**
* Create and/or open a database. This will be the same object returned by
* {@link #getWritableDatabase} unless some problem, such as a full disk,
* requires the database to be opened read-only. In that case, a read-only
* database object will be returned. If the problem is fixed, a future call
* to {@link #getWritableDatabase} may succeed, in which case the read-only
* database object will be closed and the read/write object will be returned
* in the future.
*
*
Like {@link #getWritableDatabase}, this method may
* take a long time to return, so you should not call it from the
* application main thread, including from
* {@link android.content.ContentProvider#onCreate ContentProvider.onCreate()}.
*
* @throws SQLiteException if the database cannot be opened
* @return a database object valid until {@link #getWritableDatabase}
* or {@link #close} is called.
*/
public SQLiteDatabase getReadableDatabase() {
synchronized (this) {
return getDatabaseLocked(false);
}
}
private SQLiteDatabase getDatabaseLocked(boolean writable) {
if (mDatabase != null) {
if (!mDatabase.isOpen()) {
// Darn! The user closed the database by calling mDatabase.close().
mDatabase = null;
} else if (!writable || !mDatabase.isReadOnly()) {
// The database is already open for business.
return mDatabase;
}
}
if (mIsInitializing) {
throw new IllegalStateException("getDatabase called recursively");
}
SQLiteDatabase db = mDatabase;
try {
mIsInitializing = true;
if (db != null) {
if (writable && db.isReadOnly()) {
db.reopenReadWrite();
}
} else if (mName == null) {
db = SQLiteDatabase.create(null);
} else {
try {
if (DEBUG_STRICT_READONLY && !writable) {
final String path = mContext.getDatabasePath(mName).getPath();
db = SQLiteDatabase.openDatabase(path, mFactory,
SQLiteDatabase.OPEN_READONLY, mErrorHandler);
} else {
db = mContext.openOrCreateDatabase(mName, mEnableWriteAheadLogging ?
Context.MODE_ENABLE_WRITE_AHEAD_LOGGING : 0,
mFactory, mErrorHandler);
}
} catch (SQLiteException ex) {
if (writable) {
throw ex;
}
Log.e(TAG, "Couldn't open " + mName
+ " for writing (will try read-only):", ex);
final String path = mContext.getDatabasePath(mName).getPath();
db = SQLiteDatabase.openDatabase(path, mFactory,
SQLiteDatabase.OPEN_READONLY, mErrorHandler);
}
}
onConfigure(db);
final int version = db.getVersion();
if (version != mNewVersion) {
if (db.isReadOnly()) {
throw new SQLiteException("Can't upgrade read-only database from version " +
db.getVersion() + " to " + mNewVersion + ": " + mName);
}
db.beginTransaction();
try {
if (version == 0) {
onCreate(db);
} else {
if (version > mNewVersion) {
onDowngrade(db, version, mNewVersion);
} else {
onUpgrade(db, version, mNewVersion);
}
}
db.setVersion(mNewVersion);
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
}
onOpen(db);
if (db.isReadOnly()) {
Log.w(TAG, "Opened " + mName + " in read-only mode");
}
mDatabase = db;
return db;
} finally {
mIsInitializing = false;
if (db != null && db != mDatabase) {
db.close();
}
}
}
~~~
对于getReadableDatabase（）的注释，大致意思也就是：
getWritableDatabase()和getReadableDatabase()会返回相同的对象，除非出现了一些如空间已满的问题，这时就会返回一个只读的对象。当问题解决了之后，只读对象将会被关闭，这时就会返回一个可读写的对象。
SQL实例可执行代码：
[http://download.csdn.net/detail/tangnengwu/7369503](http://download.csdn.net/detail/tangnengwu/7369503)