Android系统使用SQlite数据库代码片段

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();