SQlite數據庫增刪改查示例

1，創(chuàng)建DBHelper


package cn.net.outgoing.db.dbhepler;

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

public class qyDBHelper extends SQLiteOpenHelper {
    /**
     * VERSION = 1:初始建表結構
     */
    private static final int VERSION = 1;// 提交狀態(tài)增加一個生產功能

    /**
     * 數據庫名字
     */
    public final static String db_name = "Student.db";
    /**
     * 學生單
     */
    public static final String tb_student = "student";


    public qyDBHelper(Context context) {
        super(context, db_name, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        createProduct(db);//創(chuàng)建學生表
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }


    /**
     * name：姓名
     * sex：性別
     * age：年齡
     * phone：手機號
     * city：城市
     * @param db
     */
    private void createProduct(SQLiteDatabase db) {
        String sql = "CREATE TABLE " + tb_student
                + " (id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20),sex INTEGER,age INTEGER ,phone VARCHAR(20),city VARCHAR(20))";
        db.execSQL(sql);
    }

}

2,創(chuàng)建操作數據庫的方法接口


package cn.net.outgoing.db.inter;

import java.util.List;

import cn.net.outgoing.bean.StudentBean;

/**
 * @author zj
 * @date 2018/5/7 9:48
 * @desc
 */

public interface baseStudentInfoDal {

    /**
     * 插入學生信息
     * @param orderBean
     * @return
     */
    public void insertStudentInfo(StudentBean orderBean);

    /**
     * 刪除全部
     * @return
     */
    public void deleteStudentInfo();


    /**
     * 根據名稱刪除
     * @return
     */
    public void deleteStudentInfoByName(String name);

    /**
     * 修改時檢測數據庫中是否存在該單標
     * @param orderBean
     * @return
     */
    public void updateStudentInfo(StudentBean orderBean);



    /**
     * 查詢全部學生信息
     * @return
     */
    public List<StudentBean> queryStudentInfoToAll();


    /**
     * 根據姓名查詢學生信息
     * @return
     */
    public List<StudentBean> queryStudentInfoByName(String name);

    /**
     * 插入時檢測是否數據庫中是否存在
     * @param
     * @return
     */
    public boolean isHasExist(String account);

}

3.創(chuàng)建數據庫操作類


package cn.net.outgoing.db.dal;

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

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

import cn.net.outgoing.bean.LabelBean;
import cn.net.outgoing.bean.OrderBean;
import cn.net.outgoing.bean.StudentBean;
import cn.net.outgoing.db.dbhepler.FyswDBHelper;
import cn.net.outgoing.db.dbhepler.qyDBHelper;
import cn.net.outgoing.db.inter.baseProductInfoDal;
import cn.net.outgoing.db.inter.baseStudentInfoDal;

/**
 * @author zj
 * @date 2018/5/7 9:47
 * @desc
 */

public class StudentDal implements baseStudentInfoDal {
    private SQLiteDatabase db;
    private qyDBHelper helper;

    public StudentDal(Context context) {
        helper = new qyDBHelper(context);
    }


    @Override
    public void insertStudentInfo(StudentBean studentBean) {
        db = helper.getWritableDatabase();
        String sql = "insert into " + qyDBHelper.tb_student
                + " (name,sex,age,phone,city) values(?,?,?,?,?)";
        db.execSQL(sql, new Object[] { studentBean.getName(),studentBean.getSex(),studentBean.getAge(),studentBean.getPhone(),studentBean.getCity()});
        closeDB(db, null);
    }

    @Override
    public void deleteStudentInfo() {

        db = helper.getReadableDatabase();
        String sql = "delete from " + qyDBHelper.tb_student;
        db.execSQL(sql);
        closeDB(db, null);

    }

    @Override
    public void deleteStudentInfoByName(String name) {

        db = helper.getReadableDatabase();
        String sql = "delete from " + qyDBHelper.tb_student
                + " where name=?";
        db.execSQL(sql, new Object[] { name });
        closeDB(db, null);


    }

    @Override
    public void updateStudentInfo(StudentBean studentBean) {

        db = helper.getWritableDatabase();
        String sql = "update " + qyDBHelper.tb_student
                + " set name=?,sex=?,age=?,phone=?,city=?";
        db.execSQL(sql,
                new Object[] { studentBean.getName(),studentBean.getSex(),studentBean.getAge(),studentBean.getCity() });
        closeDB(db, null);

    }




    @Override
    public List<StudentBean> queryStudentInfoToAll() {
        db = helper.getReadableDatabase();
        List<StudentBean> beans = new ArrayList<>();
        String sql = "select * from " + qyDBHelper.tb_student+" order by id desc;";//倒序排序
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()) {
            beans.add(getStudentList(cursor));
        }

        closeDB(db, cursor);
        return beans;

    }


    @Override
    public List<StudentBean> queryStudentInfoByName(String name) {
        db = helper.getReadableDatabase();
        List<StudentBean> beans = new ArrayList<>();
        String sql = "select * from " + qyDBHelper.tb_student+" where account=? ";
        Cursor cursor = db.rawQuery(sql,new String[] { name });
        while (cursor.moveToNext()) {
            beans.add(getStudentList(cursor));
        }
        closeDB(db, cursor);
        return beans;
    }



    @Override
    public boolean isHasExist(String name) {
        db = helper.getReadableDatabase();
        boolean isExist = false;
        String sql = "select count(*) as count from " + qyDBHelper.tb_student + " where name=? ";
        Cursor cursor = db.rawQuery(sql, new String[] { name });
        if (cursor.moveToNext()) {
            int count = cursor.getInt(0);
            if (count > 0) {
                isExist = true;
            }
        }
        closeDB(db, cursor);
        return isExist;
    }


    private void closeDB(SQLiteDatabase db, Cursor cursor) {
        if (cursor != null) {
            cursor.close();
        }
        if (db != null) {
            db.close();
        }
    }

    private StudentBean getStudentList(Cursor cursor) {

        StudentBean bean = new StudentBean();
        bean.setName(cursor.getString(cursor.getColumnIndex("name")));
        bean.setSex(cursor.getString(cursor.getColumnIndex("sex")));
        bean.setAge(cursor.getInt(cursor.getColumnIndex("age")));
        bean.setPhone(cursor.getString(cursor.getColumnIndex("phone")));
        bean.setCity(cursor.getString(cursor.getColumnIndex("city")));

        return bean;
    }
}

4.調用


    StudentDal studentDal = new StudentDal(this);
        
        //增加一條
        StudentBean bean = new StudentBean();
        bean.setName("唐三");
        bean.setAge(12);
        bean.setSex("男");
        bean.setCity("諾丁城");
        bean.setPhone("18888888888");
        studentDal.insertStudentInfo(bean);




        //刪出全部
        studentDal.deleteStudentInfo();

        //刪除指定學生
        studentDal.deleteStudentInfoByName("唐三");

        //修改
        bean.setName("唐三");
        bean.setAge(18);
        bean.setSex("男");
        bean.setCity("史萊克");
        bean.setPhone("18888888888");
        studentDal.updateStudentInfo(bean);

        //查
        studentDal.queryStudentInfoToAll();//查詢全部學生
        studentDal.queryStudentInfoByName("唐三");//查詢表里所有叫唐三的學生
最后編輯于 ：
?著作權歸作者所有,轉載或內容合作請聯(lián)系作者
【社區(qū)內容提示】社區(qū)部分內容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內容（如有圖片或視頻亦包括在內）由作者上傳并發(fā)布，文章內容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內容

友情鏈接更多精彩內容