Android之Sqlite資料庫

標籤：

資料庫訪問完畢後，遊標必須也記得關閉

import com.huangzhong.love_power_model.UserInfoDto;import java.util.ArrayList;import java.util.List;import android.app.AlertDialog;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.util.Log;public class UserInfoDao{        private static final int MODE_PRIVATE = 0x0;    private static final String DATABASE = "lovepower";    private static SQLiteDatabase db;        public static void insert(Context context,UserInfoDto model) {        //Id,UserName,Birthday,PhoneNumber,QQ,Address,CreateDate        Cursor cursor;        int count = 0;        try {              db = context.openOrCreateDatabase(DATABASE, MODE_PRIVATE, null);             //db.execSQL("drop table UserInfo ;");              String sqlCheckExsit = "select count(*) as c from Sqlite_master  where type =‘table‘ and name =‘UserInfo‘ ";                cursor = db.rawQuery(sqlCheckExsit, null);                             if(cursor.moveToNext()){                        count = cursor.getInt(0);                        if(count <= 0){                                db.execSQL("Create table UserInfo (Id text not null,UserName text,Birthday text,PhoneNumber text,QQ text,Address text,CreateDate text);");                                  Log.e("insertSql", "2222");                     }             }             String insertSql = "insert into UserInfo (Id,UserName,Birthday,PhoneNumber,QQ,Address,CreateDate) " +                        "values(‘" + model.getId() + "‘, ‘"                        + model.getUserName()+"‘,‘"                        + model.getBirthday()+"‘,‘"                        + model.getPhoneNumber()+"‘,‘"                        + model.getQQ()+"‘,‘"                        + model.getAddress()+"‘,‘"                        + model.getCreateDate() + "‘);";                db.execSQL(insertSql);                cursor.close();            Log.e("insertSql", "ok");        }        finally {                        db.close();        }    }            /**     * 查詢     * @param context 上下文     * @param sql       SQl查詢語句     * @param fields  欄位名集合     * @return    查詢結果集 List<String>類型     */    public static List<UserInfoDto> query(Context context, String sql) {        Log.e("UserInfoDto", "取資料");        List<UserInfoDto> dataList = new ArrayList<UserInfoDto>();        Cursor cursor;//Id,UserName,Birthday,PhoneNumber,QQ,Address,CreateDate        try {            db = context.openOrCreateDatabase(DATABASE, MODE_PRIVATE, null);            cursor = db.rawQuery(sql, null);            if (cursor != null) {                while (cursor.moveToNext()) {                    UserInfoDto model =new UserInfoDto();                    model.setId(cursor.getString(cursor.getColumnIndex("Id")));                    model.setUserName(cursor.getString(cursor.getColumnIndex("UserName")));                    model.setBirthday(cursor.getString(cursor.getColumnIndex("Birthday")));                    model.setPhoneNumber(cursor.getString(cursor.getColumnIndex("PhoneNumber")));                    model.setQQ(cursor.getString(cursor.getColumnIndex("QQ")));                    model.setAddress(cursor.getString(cursor.getColumnIndex("Address")));                    model.setCreateDate(cursor.getString(cursor.getColumnIndex("CreateDate")));                    dataList.add(model);                }                cursor.close();//遊標必須關閉            }        }catch(Exception e) {            new AlertDialog.Builder(context).setIcon(android.R.drawable.ic_dialog_alert).setTitle("資料庫連接錯誤：")                            .setMessage("資料訪問異常。").show();                                                    } finally {            db.close();        }        return dataList;    }        /**     * 資料記錄總條數     * @param context 上下文     * @param sql      SQL查詢語句     * @return          記錄條數     */    public static int getCount(Context context, String sql) {                int totalCounty = 0;        try {            db = context.openOrCreateDatabase(DATABASE, MODE_PRIVATE, null);            Cursor cursor = db.rawQuery(sql, null);            cursor.moveToFirst();            totalCounty = cursor.getInt(0);            cursor.close();//遊標必須關閉        } catch (Exception e) {            new AlertDialog.Builder(context)                    .setIcon(android.R.drawable.ic_dialog_alert)                    .setTitle("資料庫連接錯誤：").setMessage("資料訪問異常。").show();        } finally {            db.close();        }        return totalCounty;    }}

資料訪問層

public class UserInfoDto {    //Id,UserName,Birthday,PhoneNumber,QQ,Address,CreateDate    private String Id;    private String UserName;    private String Birthday;    private String PhoneNumber;    private String QQ;    private String Address;    private String CreateDate;    public String getId() {        return Id;    }    public void setId(String id) {        Id = id;    }    public String getUserName() {        return UserName;    }    public void setUserName(String userName) {        UserName = userName;    }    public String getBirthday() {        return Birthday;    }    public void setBirthday(String birthday) {        Birthday = birthday;    }    public String getPhoneNumber() {        return PhoneNumber;    }    public void setPhoneNumber(String phoneNumber) {        PhoneNumber = phoneNumber;    }    public String getQQ() {        return QQ;    }    public void setQQ(String qQ) {        QQ = qQ;    }    public String getAddress() {        return Address;    }    public void setAddress(String address) {        Address = address;    }    public String getCreateDate() {        return CreateDate;    }    public void setCreateDate(String createDate) {        CreateDate = createDate;    }}

實體層

 

Android之Sqlite資料庫