SQLite增刪改查

標籤：

package com.scme.db;import java.util.ArrayList;import java.util.List;import com.scme.bean.Students;import com.scme.bean.Tongxunlu;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;public class DBHelper extends SQLiteOpenHelper {    // 定義資料庫名稱    private static final String DBNAME = "txl.db";    // 定義資料庫版本    private static final int VERSION = 1;    public DBHelper(Context context, String name, CursorFactory factory,            int version) {        super(context, DBNAME, factory, VERSION);    }    /**     * 建立資料庫表     */    @Override    public void onCreate(SQLiteDatabase db) {        String sql1 = "create table user_info (_id integer primary key autoincrement,stuName text,stuPwd text,stuSex text,stuAge integer)";        String sql2 = "create table phone_info (_id integer primary key autoincrement,txlName text,txlPhone text,txlEmail text,txlAddr text)";        db.execSQL(sql1);        db.execSQL(sql2);    }    @Override    public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {    }    /**     * 註冊     */    public void addStu(Students stu) {        String sql = "insert into user_info(stuName,stuPwd,stuSex,stuAge) values (?,?,?,?)";        SQLiteDatabase db = getWritableDatabase();        db.execSQL(                sql,                new Object[] { stu.getStuName(), stu.getStuPwd(),                        stu.getStuSex(), stu.getStuAge() });        db.close();    }    /**     * 登入     *      * @param name     * @param pwd     * @return     */    public boolean login(String name, String pwd) {        String sql = "select * from user_info where stuName = " + name                + " and stuPwd =" + pwd;        SQLiteDatabase db = getReadableDatabase();        Cursor c = db.rawQuery(sql, null);        while (c.moveToNext()) {            if (c.getString(1) != null) {                return true;            }        }        c.close();        db.close();        return false;    }    /**     * 新增連絡人...     *      * @param txl     */    public void addTxl(Tongxunlu txl) {        String sql = "insert into phone_info(txlName,txlPhone,txlEmail,txlAddr) values (?,?,?,?)";        SQLiteDatabase db = getWritableDatabase();        db.execSQL(                sql,                new Object[] { txl.getTxlName(), txl.getTxlPhone(),                        txl.getTxlEmail(), txl.getTxlAddr() });        db.close();    }    /**     * 刪除連絡人     *      * @param id     */    public void delTxl(int id) {        String sql = "delete from phone_info where _id = " + id;        SQLiteDatabase db = getWritableDatabase();        db.execSQL(sql);        db.close();    }    /**     * 修改連絡人     *      * @param txl     */    public void updateTxl(Tongxunlu txl) {        String sql = "update phone_info set txlName=?,txlPhone=?,txlEmail=?,txlAddr=? where _id = ?";        SQLiteDatabase db = getWritableDatabase();        db.execSQL(                sql,                new Object[] { txl.getTxlName(), txl.getTxlPhone(),                        txl.getTxlEmail(), txl.getTxlAddr(), txl.get_id() });        db.close();    }    /**     * 查詢全部+根據姓名查詢     *      * @param name     * @return     */    public List<Tongxunlu> queryName(String name) {        ArrayList<Tongxunlu> list = new ArrayList<Tongxunlu>();        String sql = "select * from phone_info";        if (name != null && !name.equals("")) {            sql += " where txlName like ‘%" + name + "%‘";        }        SQLiteDatabase db = getReadableDatabase();        Cursor c = db.rawQuery(sql, null);        while (c.moveToNext()) {            Tongxunlu txl = new Tongxunlu();            txl.set_id(c.getInt(0));            txl.setTxlName(c.getString(1));            txl.setTxlPhone(c.getString(2));            txl.setTxlEmail(c.getString(3));            txl.setTxlAddr(c.getString(4));            list.add(txl);        }        c.close();        db.close();        return list;    }    /**     * 根據ID查詢     *      * @param id     * @return     */    public Tongxunlu qyeryId(int id) {        Tongxunlu txl = new Tongxunlu();        String sql = "select * from phone_info where _id =" + id;        SQLiteDatabase db = getReadableDatabase();        Cursor c = db.rawQuery(sql, null);        if (c.moveToNext()) {            txl.set_id(c.getInt(0));            txl.setTxlName(c.getString(1));            txl.setTxlPhone(c.getString(2));            txl.setTxlEmail(c.getString(3));            txl.setTxlAddr(c.getString(4));        }        c.close();        db.close();        return txl;    }}

 

SQLite增刪改查