android sqlite 数据库创建及增删改查的操作实现

在android 开发工程中，我们经常会把数据保存到本地数据库，然后进行增删该查的操作，为了提高工作效率，近期我有空的时候稍微整理了一下，下面分享下

第一步首先定义下表中的列名，我们要建一个类 implements BaseColumns ,下面给出源码

/**
 * Created by duanhongbo on 2016/11/28.
 */
public  class LocationData implements BaseColumns{
        public static final String LOCATION_TABLE_NAME = "location"; // 表名
        public static final String LOCATION_ID = "location_id";
        public final static String LONGITUDE = "longitude"; // varchar 20 not
        public final static String LATITUDE = "latitude"; // varchar 20 not null
        public final static String ELEVATION = "elevation";// varchar 20 not
        public final static String GsmSignalStrength = "gsmSignalStrength";// 信号强度
        public final static String BatteryCount = "batteryCount";// 电量
        public final static String GpsState = "gpsState";// gps开关
        public final static String Time = "time";// 时间
        public final static String UserId = "userid";// 用户idisNetwork
        public final static String isNetwork = "isNetwork";// 是否联网


}

2  建一个数据库管理类  继承SQLiteOpenHelper，下面给出代码

/**
 * Created by duanhongbo on 2016/11/28.
 */
public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "transition.db";
    private static final int DATABASE_VERSION = 1;
    private Context context;
    private static SQLiteDatabase mDb;
    public synchronized SQLiteDatabase getInstence() {

        if (null == mDb || (mDb != null && !mDb.isOpen())) {
            mDb = getReadableDatabase();
//       mDb = getWritableDatabase();
        }
        return mDb;

    }

    public static synchronized void closeDB() {
        if (null != mDb && mDb.isOpen()) {
            mDb.close();
        }
    }

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
        this.context = context;
    }
    @Override
    public void onCreate(SQLiteDatabase arg0) {
        // TODO Auto-generated method stub
        mDb = arg0;
      /*
       * 定位表
       */
        mDb.execSQL("CREATE TABLE IF NOT EXISTS "
                + LocationData.LOCATION_TABLE_NAME + "( " + LocationData.LOCATION_ID
                + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL  , "
                + LocationData.LONGITUDE + " DOUBLE , " + LocationData.LATITUDE
                + " DOUBLE , " + LocationData.ELEVATION + " DOUBLE , "
                + LocationData.Time + " TEXT ," + LocationData.UserId + " TEXT ," + LocationData.GsmSignalStrength + " INTEGER , " + LocationData.BatteryCount + " INTEGER , " + LocationData.GpsState + " INTEGER ," + LocationData.isNetwork +" INTEGER );");
        Log.i("dhb","success" + " -->  SUCCESS  ");
    }
    @Override
    public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
        // TODO Auto-generated method stub

    }

}

3第三部 我们要对数据库 进行增删该查的操作新建一个类LocationDao

/**
 * Created by duanhongbo on 2016/11/28.
 */
public class LocationDao {
    private DatabaseHelper dbHelper;

    /**
     *
     */
    public LocationDao(Context context) {
        // TODO Auto-generated constructor stub
        this.dbHelper = new DatabaseHelper(context);
    }

    /*
     * 插入某个用户的数据
     */
    public int insertInfo(Loaction l) {

        int success = 0;
        boolean flag = false;
        /*String sql = "SELECT  MAX(" + LocationData.Location.LOCATION_ID + ") from "
                + LocationData.Location.LOCATION_TABLE_NAME;*/
        Cursor cursor = null;
        SQLiteDatabase db = dbHelper.getInstence();
        db.beginTransaction();//

        try {
            ContentValues contentValues = new ContentValues();
            contentValues.put("elevation", l.getAltitude());//海拔
            contentValues.put("latitude", l.getLatitude());//纬度
            contentValues.put("longitude", l.getLongitude());//经度
            contentValues.put("gsmSignalStrength", l.getGsmSignalStrength());//信号强度
            contentValues.put("batteryCount", l.getBatteryCount());//电量
            contentValues.put("gpsState", l.getGpsState() + "");//gps状态
            contentValues.put("userid", l.getUserId());//用户id
            contentValues.put("time", l.getTime());//定位时间
            contentValues.put("isNetwork", l.getIsNetwork());//是否联网
            long ok = db.insert(LocationData.LOCATION_TABLE_NAME, null,
                    contentValues);
            if (0 < ok) {
                flag = true;
            } else {
                flag = false;
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            // TODO: handle exception
        } finally {
            db.endTransaction();
            // db.close();
        }
        return success;
    }

    /**
     * 删表某个用户的所有数据
     */
    public boolean delTable(String userId) {
        boolean isOk = false;
        SQLiteDatabase db = dbHelper.getInstence();
        db.beginTransaction();//
        try {
            int res = db.delete(LocationData.LOCATION_TABLE_NAME,
                    LocationData.UserId + "=?", new String[]{userId});
            if (res > 0) {
                isOk = true;
               
            }
            db.setTransactionSuccessful();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }
        return isOk;
    }

    /**
     * 查询某个用户的所有数据
     *
     * @param userId
     * @return
     */
    public List<Loaction> getAllLocation(int userId) {
        List<Loaction> allLoaction = new ArrayList<>();
        Loaction l = null;
        SQLiteDatabase db = dbHelper.getInstence();
        db.beginTransaction();//
        Cursor cursor = null;

        String sql = "select * from " + LocationData.LOCATION_TABLE_NAME
                + " where " + LocationData.UserId + " = " + userId;
        try {
            cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                l = new Loaction();
                String longtitude = cursor.getString(1);//经度
                String latitude = cursor.getString(2);//纬度
                String elevation = cursor.getString(3);//海拔
                String time = cursor.getString(4);//时间
                int userid = cursor.getInt(5);//用户id
                int gsmSignalStrength = cursor.getInt(6);//信号强度
                int batteryCount = cursor.getInt(7);//电量
                int gpsState = cursor.getInt(8);//gps状态
                int isNetwork= cursor.getInt(9);//是否联网

//............................................................................

                l.setAltitude(Double.parseDouble(elevation));//海拔
                l.setLongitude(Double.parseDouble(longtitude));//经度
                l.setLatitude(Double.parseDouble(latitude));//纬度
                l.setTime(time);//时间
                l.setBatteryCount(batteryCount);//电量
                l.setGsmSignalStrength(gsmSignalStrength);//强度
                l.setUserId(userid);//用户id
                l.setGpsState(gpsState);
                l.setIsNetwork(isNetwork);
                allLoaction.add(l);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }
        return allLoaction;
    }

}

如果其他项目用到的话，适当修改就好了，谢谢，周五到了，要下班了。。。。。。。美好的周末即将到来