关闭

一般的数据库SQLite操作

53人阅读 评论(0) 收藏 举报
分类:


一般的数据库SQLite操作

package com.map.database;


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


public class DatabaseHelper extends SQLiteOpenHelper {


    private static final String TAG = "DatabaseHelper";


    public static final String DB_NAME = "DB_MAP";
    public static final int DB_VERSION = 1;


    /**
     * 数据库表的名字
     */
    public static final String DB_TABLE = "RECORD";


    public final static String DB_START_LAT = "start_lat";
    public final static String DB_START_LNG = "start_lng";
    public final static String DB_START_PLACE = "location_place_start";
    public final static String DB_END_LAT = "end_lat";
    public final static String DB_END_LNG = "end_lng";
    public final static String DB_END_PLACE = "location_place_end";


    public DatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,
                          int version) {
        super(context, name, factory, version);
        // TODO Auto-generated constructor stub
    }


    /**
     * 调用此方法,首次会创建数据库和数据库版本
     * @param context
     */
    public DatabaseHelper(Context context){
        super(context, DB_NAME, null, DB_VERSION);
        // TODO Auto-generated constructor stub
        Log.d("LOG_TAG----->", "DatabaseHelper");
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub


        //创建数据表
        StringBuffer sBuffer = new StringBuffer();
        sBuffer.append("CREATE TABLE IF NOT EXISTS " + DB_TABLE + " (");
        sBuffer.append("ID INTEGER PRIMARY KEY AUTOINCREMENT, ");//ID是必须要有的,作为数据库表中的唯一标识。
        //sBuffer.append("record_id VARCHAR(255) NULL,");
        sBuffer.append("start_lat DOUBLE NULL,");
        sBuffer.append("start_lng DOUBLE NULL,");
        sBuffer.append("location_place_start VARCHAR(255) NULL,");
        sBuffer.append("end_lat DOUBLE NULL,");
        sBuffer.append("end_lng DOUBLE NULL,");
        sBuffer.append("location_place_end VARCHAR(255) NULL)");
        db.execSQL(sBuffer.toString());


    }


    public void onOpen(SQLiteDatabase db){
        super.onOpen(db);
        Log.d(TAG, "DatabaseHelper onOpen");
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);
        onCreate(db);
    }


    @Override
    public synchronized void close() {
        super.close();
    }


    public void close(SQLiteDatabase db){
        db.close();
    }




}
_____________________________________________________________________________


package com.map.database;


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


import java.util.LinkedList;
import java.util.List;


public class DatabaseOperator {


    private static final String TAG = "DatabaseOperator";


    private static DatabaseOperator instance;
    private Context context;
    private DatabaseHelper databaseHelper;




    private DatabaseOperator(Context context){
       this.context = context;
        setInitDatabase(context);
    }


   public static DatabaseOperator getInstance(Context context){
       if (instance == null){
           synchronized(DatabaseOperator.class){
               if(instance == null){
                   instance = new DatabaseOperator(context);
               }
           }
       }
       return instance;
   }


    private synchronized void setInitDatabase(Context context){
        databaseHelper = new DatabaseHelper(context);
    }


    private synchronized SQLiteDatabase getSQLiteDatabaseWriteInstance(){
        if (databaseHelper == null){
            setInitDatabase(context);
        }
        SQLiteDatabase database = databaseHelper.getWritableDatabase();//增删改
        return database;
    }


    private SQLiteDatabase getSQLiteDatabaseReadInstance(){
        if (databaseHelper == null){
            setInitDatabase(context);
        }
        SQLiteDatabase database = databaseHelper.getReadableDatabase();//查
        return database;
    }


    public synchronized void insertDbRecordBeanListToDB(List<DbRecoedBean> beans){


        List<DbRecoedBean> beanList = beans;
        SQLiteDatabase database = getSQLiteDatabaseWriteInstance();


        for (int i = 0; i < beanList.size(); i++){
            String SQL = "insert into "+ DatabaseHelper.DB_TABLE
                    + "(" + DatabaseHelper.DB_START_LAT + "," + DatabaseHelper.DB_START_LNG + ","
                    + DatabaseHelper.DB_START_PLACE + "," + DatabaseHelper.DB_END_LAT + ","
                    + DatabaseHelper.DB_END_LNG + "," + DatabaseHelper.DB_END_PLACE + ")"
                    + "values(?, ?, ?, ?, ?, ?)";
            database.execSQL(SQL, new Object[]{
                    beanList.get(i).getStart_lat(),
                    beanList.get(i).getStart_lng(),
                    beanList.get(i).getLocation_start_place(),
                    beanList.get(i).getEnd_lat(),
                    beanList.get(i).getEnd_lng(),
                    beanList.get(i).getLocation_end_place()});
        }


    }


    public synchronized void insertDbResordBeanToDB(DbRecoedBean bean){
        SQLiteDatabase database = getSQLiteDatabaseWriteInstance();


        String SQL = "insert into "+ DatabaseHelper.DB_TABLE
                    + "(" + DatabaseHelper.DB_START_LAT + "," + DatabaseHelper.DB_START_LNG + ","
                    + DatabaseHelper.DB_START_PLACE + "," + DatabaseHelper.DB_END_LAT + ","
                    + DatabaseHelper.DB_END_LNG + "," + DatabaseHelper.DB_END_PLACE + ")"
                    + "values(?, ?, ?, ?, ?, ?)";
        database.execSQL(SQL, new Object[]{
                    bean.getStart_lat(),
                    bean.getStart_lng(),
                    bean.getLocation_start_place(),
                    bean.getEnd_lat(),
                    bean.getEnd_lng(),
                    bean.getLocation_end_place()});
    }


    public List<DbRecoedBean> selectDbRecordData(){


        SQLiteDatabase database = getSQLiteDatabaseReadInstance();
        String SQL = "select * from " + DatabaseHelper.DB_TABLE;
        List<DbRecoedBean> beanList = new LinkedList<>();


        Cursor mCursor = database.rawQuery(SQL, new String[]{});
        int count = mCursor.getCount();
        Log.d(TAG, "数据集条数 = " + count);
        while(mCursor.moveToNext()){
            DbRecoedBean bean = new DbRecoedBean();


            int indexStLat = mCursor.getColumnIndex(DatabaseHelper.DB_START_LAT);
            Double stLat = mCursor.getDouble(indexStLat);
            bean.setStart_lat(stLat);


            int indexStLng = mCursor.getColumnIndex(DatabaseHelper.DB_START_LNG);
            Double stLng = mCursor.getDouble(indexStLng);
            bean.setStart_lng(stLng);


            int indexEndLat = mCursor.getColumnIndex(DatabaseHelper.DB_END_LAT);
            Double endLat = mCursor.getDouble(indexEndLat);
            bean.setEnd_lat(endLat);


            int indexEndLng = mCursor.getColumnIndex(DatabaseHelper.DB_END_LNG);
            Double endLng = mCursor.getDouble(indexEndLng);
            bean.setEnd_lng(endLng);


            int indexStPlace = mCursor.getColumnIndex(DatabaseHelper.DB_START_PLACE);
            String stPlace = mCursor.getString(indexStPlace);
            bean.setLocation_start_place(stPlace);


            int indexEndPlace = mCursor.getColumnIndex(DatabaseHelper.DB_END_PLACE);
            String endPlace = mCursor.getString(indexEndPlace);
            bean.setLocation_end_place(endPlace);


            beanList.add(bean);
        }
        mCursor.close();
        return beanList;
    }




    public synchronized void deleteAllData(){
        String SQL = "delete from "+ DatabaseHelper.DB_TABLE;
        SQLiteDatabase database = getSQLiteDatabaseWriteInstance();
        database.execSQL(SQL);
    }




}
______

______________________________________________________________________
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:25704次
    • 积分:815
    • 等级:
    • 排名:千里之外
    • 原创:59篇
    • 转载:1篇
    • 译文:0篇
    • 评论:0条
    文章分类