student.java
public class Student {
//表名
public static final String TABLE="Student";
//表的各个域名
public static final String KEY_ID="id";
public static final String KEY_name="name";
public static final String KEY_email="email";
public static final String KEY_age="age";
//属性
public int student_ID;
public String name;
public String email;
public int age;
}
DBHelper.java
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DBHelper extends SQLiteOpenHelper {
//version number to upgrade database version
//each time if you Add, Edit table, you need to change the
//version number.
//每次你对数据表进行编辑,添加时候,你都需要对数据库的版本进行提升
//数据库版本
private static final int DATABASE_VERSION=3;
//数据库名称
private static final String DATABASE_NAME="sqlitestudy.db";
public DBHelper(Context context){
super(context,DATABASE_NAME,null,DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
//创建数据表
String CREATE_TABLE_STUDENT="CREATE TABLE "+ Student.TABLE+"("
+Student.KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT ,"
+Student.KEY_name+" TEXT, "
+Student.KEY_age+" INTEGER, "
+Student.KEY_email+" TEXT)";
db.execSQL(CREATE_TABLE_STUDENT);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//如果旧表存在,删除,所以数据将会消失
db.execSQL("DROP TABLE IF EXISTS "+ Student.TABLE);
//再次创建表
onCreate(db);
}
}
StudentRepo.java
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import java.util.ArrayList;
import java.util.HashMap;
public class StudentRepo {
private DBHelper dbHelper;
public StudentRepo(Context context){
dbHelper=new DBHelper(context);
}
public int insert(Student student){
//打开连接,写入数据
SQLiteDatabase db=dbHelper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put(Student.KEY_age,student.age);
values.put(Student.KEY_email,student.email);
values.put(Student.KEY_name,student.name);
//
long student_Id=db.insert(Student.TABLE,null,values);
db.close();
return (int)student_Id;
}
public void delete(int student_Id){
SQLiteDatabase db=dbHelper.getWritableDatabase();
db.delete(Student.TABLE,Student.KEY_ID+"=?", new String[]{String.valueOf(student_Id)});
db.close();
}
public void update(Student student){
SQLiteDatabase db=dbHelper.getWritableDatabase();
ContentValues values=new ContentValues();
values.put(Student.KEY_age,student.age);
values.put(Student.KEY_email,student.email);
values.put(Student.KEY_name,student.name);
db.update(Student.TABLE,values,Student.KEY_ID+"=?",new String[] { String.valueOf(student.student_ID) });
db.close();
}
public ArrayList<HashMap<String, String>> getStudentList(){
SQLiteDatabase db=dbHelper.getReadableDatabase();
String selectQuery="SELECT "+
Student.KEY_ID+","+
Student.KEY_name+","+
Student.KEY_email+","+
Student.KEY_age+" FROM "+Student.TABLE;
ArrayList<HashMap<String,String>> studentList=new ArrayList<HashMap<String, String>>();
Cursor cursor=db.rawQuery(selectQuery,null);
if(cursor.moveToFirst()){
do{
HashMap<String,String> student=new HashMap<String,String>();
student.put("id",cursor.getString(cursor.getColumnIndex(Student.KEY_ID)));
student.put("name",cursor.getString(cursor.getColumnIndex(Student.KEY_name)));
student.put("email",cursor.getString(cursor.getColumnIndex(Student.KEY_email)));
student.put("age",cursor.getString(cursor.getColumnIndex(Student.KEY_age)));
studentList.add(student);
}while(cursor.moveToNext());
}
cursor.close();
db.close();
return studentList;
}
public Student getStudentById(int Id){
SQLiteDatabase db=dbHelper.getReadableDatabase();
String selectQuery="SELECT "+
Student.KEY_ID + "," +
Student.KEY_name + "," +
Student.KEY_email + "," +
Student.KEY_age +
" FROM " + Student.TABLE
+ " WHERE " +
Student.KEY_ID + "=?";
int iCount=0;
Student student=new Student();
Cursor cursor=db.rawQuery(selectQuery,new String[]{String.valueOf(Id)});
if(cursor.moveToFirst()){
do{
student.student_ID =cursor.getInt(cursor.getColumnIndex(Student.KEY_ID));
student.name =cursor.getString(cursor.getColumnIndex(Student.KEY_name));
student.email =cursor.getString(cursor.getColumnIndex(Student.KEY_email));
student.age =cursor.getInt(cursor.getColumnIndex(Student.KEY_age));
}while(cursor.moveToNext());
}
cursor.close();
db.close();
return student;
}
}
主界面中使用
StudentRepo repo = new StudentRepo(this);
//插入一个学生
Student student = new Student();
student.age = 18;
student.email = "1006325356@qq.com";
student.name = "Mark";
student.student_ID = 0;
int x = repo.insert(student);
//插入一个学生
student.age = 28;
student.email = "130011171912@163.com";
student.name = "Bob";
int y = repo.insert(student);
//读取学生列表
ArrayList<HashMap<String, String>> hashMapArrayList1 = repo.getStudentList();
//删除一个学生
repo.delete(y);
//读取学生列表
ArrayList<HashMap<String, String>> hashMapArrayList2 = repo.getStudentList();
//修改一个学生
student.age = 19;
student.email = "10063253566@qq.com";
student.name = "MarkM";
student.student_ID = x;
repo.update(student);
//读取学生列表
ArrayList<HashMap<String, String>> hashMapArrayList3 = repo.getStudentList();