DBMNG数据库管理与应用

抓住自己最有兴趣的东西，由浅入深，循序渐进地学……

目录 / 分类

数据库基础
Access
SQLServer
MySQL
SQLite
Oracle
PostgreSQL
移动应用
经验分享
服务器配置
网文摘录
下载中心
CUBRID数据库
验证/二维/条形码
当前位置：首页 > SQLite > 应用案例

android数据库的常用方法

常使用的方法
SqliteSQLUtil.java
/** 
 * 2013-1-21 上午11:02:41 
 *  
 * @author sunji 数据库语句 
 */ 
public class SqliteSQLUtil { 
 
    private Context sqliteContext; 
    private DataBaseHelper dbHelper; //自定义helper继承至SQLiteOpenHelper
    private SQLiteDatabase mSqliteDB = null;//sqlite实例 
 
    public SqliteSQLUtilt(Context context) { 
        this.sqliteContext = context; 
    } 
 
    /** 
     * 打开数据库 
     */ 
    private void open() { 
        dbHelper = new DataBaseHelper(sqliteContext); 
        mSqliteDB = dbHelper.getWritableDatabase(); 
    } 
 
    /** 
     * 关闭数据库 
     */ 
    private void close() { 
        mSqliteDB.close(); 
    } 
 
    /** 
     * 将图片转换成byte[]以便能将图片存到数据库 
     *  
     * @param drawalbe 
     * @return 
     */ 
    public byte[] getBitmapByte(Drawable drawalbe) { 
        BitmapDrawable db = (BitmapDrawable) drawalbe; 
        Bitmap bitmap = db.getBitmap(); 
        ByteArrayOutputStream out = new ByteArrayOutputStream(); 
        bitmap.compress(Bitmap.CompressFormat.PNG, 100, out); 
        try { 
            out.flush(); 
            out.close(); 
        } catch (IOException e) { 
            e.printStackTrace(); 
 
        } 
        return out.toByteArray(); 
    } 
 
    /** 
     * 查询所有数据 
     *  
     * @return 
     */ 
    public Cursor queryAllData() { 
        open(); 
        Cursor cursor = mSqliteDB.query(DataBaseHelper.TABLE_NAME, 
                DBHelper.SQLITE_ALL_PROJECTION, null, null, null, null, null); 
        close(); 
        return cursor; 
    } 
 
    /** 
     * 通过页数分页 
     *  
     * @param pageID 
     * @return 
     */ 
    public Cursor queryPageById(int pageID) { 
        open(); 
        String sql = "select * from " + DataBaseHelper.TABLE_NAME + " Limit " 
                + String.valueOf(DBHelper.PAGESIZE) + " offset " 
                + String.valueOf(pageID * DBHelper.PAGESIZE) + " ;"; 
        Cursor cursor = mSqliteDB.rawQuery(sql, null); 
        cursor.getCount(); 
        close(); 
        return cursor; 
    } 
 
    /** 获取总页数 **/ 
    public int getTotalCount() { 
        open(); 
        String sql = "Select count(*) From  " + DataBaseHelper.TABLE_NAME 
                + " ;"; 
        Cursor cursor = mSqliteDB.rawQuery(sql, null); 
        cursor.moveToFirst(); 
        long recSize = cursor.getLong(0); 
        cursor.close(); 
        int pageNumber = (recSize % DBHelper.PAGESIZE == 0 ? (int) (recSize / DBHelper.PAGESIZE) 
                : (int) (recSize / DBHelper.PAGESIZE + 1)); 
        close(); 
        return pageNumber; 
    } 
 
    /** 
     * 从数据库分页读取数据 
     *  
     * @param page 
     *            索引页id，从0开始 
     * @return 满足条件的活动列表 
     * **/ 
    public List<Promotion> queryProByPageId(int pageID) throws Exception { 
        List<Promotion> promotions = new ArrayList<Promotion>(); 
        open(); 
        String sql = "select * from " + DataBaseHelper.TABLE_NAME + " Limit " 
                + String.valueOf(DBHelper.PAGESIZE) + " offset " 
                + String.valueOf(pageID * DBHelper.PAGESIZE) + " ;"; 
        Cursor cursor = mSqliteDB.rawQuery(sql, null); 
 
        Promotion pro = null; 
        CompanyData company = null; 
        for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) { 
            pro = new Promotion(); 
            company = new CompanyData(); 
            pro.setCity(cursor.getString(cursor 
                    .getColumnIndex(DBHelper.PROMOTION_CITY_CODE))); 
            company.setId(cursor.getInt(cursor 
                    .getColumnIndex(DBHelper.PROMOTION_COMPANY_ID))); 
            company.setName(cursor.getString(cursor 
                    .getColumnIndex(DBHelper.PROMOTION_COMPANY_NAME))); 
            pro.setCompany(company); 
            pro.setContent(cursor.getString(cursor 
                    .getColumnIndex(DBHelper.PROMOTION_CONTENT))); 
            Date endDate, startDate, timeDate = null; 
            String endString = cursor.getString(cursor 
                    .getColumnIndex(DBHelper.PROMOTION_END_TIME)); 
            if (endString != null && !endString.equals("")) { 
                endDate = Tools.getDate(endString); 
                pro.setEnd(endDate); 
            } 
            String startString = cursor.getString(cursor 
                    .getColumnIndex(DBHelper.PROMOTION_START_TIME)); 
            if (startString != null && !startString.equals("")) { 
                startDate = Tools.getDate(startString); 
                pro.setStart(startDate); 
            } 
            String timeString = cursor.getString(cursor 
                    .getColumnIndex(DBHelper.PROMOTION_TIME)); 
            if (timeString != null && !timeString.equals("")) { 
                timeDate = Tools.getDate(timeString); 
                pro.setTime(timeDate); 
            } 
            pro.setId(cursor.getInt(cursor 
                    .getColumnIndex(DBHelper.PROMOTION_ID))); 
            pro.setName(cursor.getString(cursor 
                    .getColumnIndex(DBHelper.PROMOTION_NAME))); 
            String[] pics = new String[3];  
            pics[0] = cursor.getString(cursor 
                    .getColumnIndex(DBHelper.PROMOTION_PIC_PATH_S)); 
            pics[1] = cursor.getString(cursor 
                    .getColumnIndex(DBHelper.PROMOTION_PIC_PATH_B1)); 
            pics[2] = cursor.getString(cursor 
                    .getColumnIndex(DBHelper.PROMOTION_PIC_PATH_B2)); 
            pro.setPics(pics); 
            pro.setStatus(cursor.getInt(cursor 
                    .getColumnIndex(DBHelper.PROMOTION_STATUS))); 
            pro.setType(cursor.getInt(cursor 
                    .getColumnIndex(DBHelper.PROMOTION_TYPE))); 
            promotions.add(pro); 
        } 
        close(); 
        return promotions; 
    } 
 
    /** 
     * 插入一条数据数据 
     *  
     * @param model 
     */ 
    public long insertPromotionInfo(Promotion promotion) { 
        open(); 
        ContentValues content = new ContentValues(); 
        content.put(DBHelper.PROMOTION_CITY_CODE, promotion.getCity()); 
        content.put(DBHelper.PROMOTION_COMPANY_ID, promotion.getCompany() 
                .getId()); 
        content.put(DBHelper.PROMOTION_COMPANY_NAME, promotion.getCompany() 
                .getName()); 
        content.put(DBHelper.PROMOTION_CONTENT, promotion.getContent()); 
        content.put(DBHelper.PROMOTION_END_TIME, Tools.formateDate(promotion.getEnd())); 
        content.put(DBHelper.PROMOTION_ID, promotion.getId()); 
        content.put(DBHelper.PROMOTION_NAME, promotion.getName()); 
        content.put(DBHelper.PROMOTION_PIC_PATH_B1, promotion.getPics()[1]); 
        content.put(DBHelper.PROMOTION_PIC_PATH_B2, promotion.getPics()[2]); 
        content.put(DBHelper.PROMOTION_PIC_PATH_S, promotion.getPics()[0]); 
        content.put(DBHelper.PROMOTION_START_TIME, Tools.formateDate(promotion.getStart())); 
        content.put(DBHelper.PROMOTION_STATUS, promotion.getStatus()); 
        content.put(DBHelper.PROMOTION_TIME,Tools.formateDate( promotion.getTime())); 
        content.put(DBHelper.PROMOTION_TYPE, promotion.getType()); 
        long i = mSqliteDB.insert(DataBaseHelper.TABLE_NAME, null, content); 
        close(); 
        return i; 
    } 
 
   
 
    /** 
     * 通过活动id删除对应的数据 
     *  
     * @param promotionID 
     *            活动id 
     * @return 返回受影响的行 
     */ 
    public int deleteOnePromotion(int promotionID) { 
        open(); 
        int i = mSqliteDB.delete(DataBaseHelper.TABLE_NAME, 
                DBHelper.PROMOTION_ID + " =?", 
                new String[] { String.valueOf(promotionID) }); 
        close(); 
        return i; 
    } 
 
    /** 
     * 判断数据库中是否存在此某条数据 
     *  
     * @param packageName 
     * @return 
     */ 
    public boolean isExistsByPromotionID(int promotionID) { 
        open(); 
        boolean isExist = true; 
        Cursor cursor = mSqliteDB.query(DataBaseHelper.TABLE_NAME, 
                DBHelper.SQLITE_ALL_PROJECTION, DBHelper.PROMOTION_ID + " =?", 
                new String[] { String.valueOf(promotionID) }, null, null, null); 
 
        if (!cursor.moveToFirst()) { 
            isExist = false; 
        } 
        cursor.close(); 
        close(); 
        return isExist; 
    } 
 
    /**
     * 得到存储在数据库中的图片
     * 
     * @param temp
     * @return
     */
    public Bitmap getBitmapFromByte(byte[] temp) {
 
        Bitmap bitmap = BitmapFactory.decodeByteArray(temp, 0, temp.length);
        return bitmap;
 
    }
DBHelper.java
**
 * 2013-1-21 上午10:56:20
 * @author 
 * 数据库
 */
public class DBHelper {
 
    /**
     * 
     * "promotions": [
                {
                    "status": "1",
                    "type": "4",
                    "city": "510100",
                    "id": 108,
                    "content": "今天心情不好",
                    "pics": [
                        "images/sales/201301/17.png",
                        "images/sales/201301/17.png",
                        null
                    ],
                    "time": "2013-01-09 11:31:12.0",
                    "start": "2013-01-04 00:00:00.0",
                    "company": {
                        "id": 111,
                        "name": "心里不爽"
                    },
                    "name": "超级不爽",
                    "end": "2013-01-17 00:00:00.0"
                }
     * **/
        
        public static final String PROMOTION_ID="pro_id";
        public static final String PROMOTION_TIME="pro_time";
        public static final String PROMOTION_NAME="pro_name";
        public static final String PROMOTION_STATUS="pro_status";   
        public static final String PROMOTION_TYPE="pro_type";
        public static final String PROMOTION_CONTENT="pro_content";
        public static final String PROMOTION_CITY_CODE="pro_city_code";
        public static final String PROMOTION_COMPANY_ID="pro_company_id";
        public static final String PROMOTION_COMPANY_NAME="pro_company_name";
        public static final String PROMOTION_PIC_PATH_S="pro_pic_path_s";
        public static final String PROMOTION_PIC_PATH_B1="pro_pic_path_b1";
        public static final String PROMOTION_PIC_PATH_B2="pro_pic_path_b2";
        public static final String PROMOTION_START_TIME="pro_start_time";
        public static final String PROMOTION_END_TIME="pro_start_end";
        public static final long PAGESIZE = 10;
 
        /**
         * 查询所有数据所有字段
         */
        public static final String[] SQLITE_ALL_PROJECTION = { PROMOTION_ID,
            PROMOTION_TIME, PROMOTION_NAME, PROMOTION_STATUS,PROMOTION_TYPE,PROMOTION_CONTENT,PROMOTION_CITY_CODE,
            PROMOTION_COMPANY_ID,PROMOTION_COMPANY_NAME,PROMOTION_PIC_PATH_S,PROMOTION_PIC_PATH_B1,PROMOTION_PIC_PATH_B2,
            PROMOTION_START_TIME,
            PROMOTION_END_TIME };
 
        /**
         * 查询收藏数据
         */
        public static final String[] SQLITE_COLLECT_PROJECTION={PROMOTION_ID,PROMOTION_NAME,PROMOTION_COMPANY_NAME,PROMOTION_START_TIME,PROMOTION_END_TIME,PROMOTION_PIC_PATH_S,PROMOTION_PIC_PATH_B1};
 
        /**查询活动id**/
        public static final String[] SQLITE_DELETE_ONE_PROJECTION={PROMOTION_ID};
 
        
 
    
}
DataBaseHelper.java
public class DataBaseHelper extends SQLiteOpenHelper {
 
    public static final String TAG = "SQLITE";
    public static final String DATABASENAME = "promotion.db";
    public static final String TABLE_NAME = "promotion_table";
    public static final int DATABASE_VERSION = 1;
 
    public DataBaseHelper(Context context) {
        super(context, DATABASENAME, null, DATABASE_VERSION);
    }
 
    
     public final static String
      CREATE_COLLECT_TABLE="CREATE TABLE IF NOT EXISTS "+ TABLE_NAME
      +" ( "+DBHelper.PROMOTION_ID+"  INTEGER  PRIMARY KEY  ,"
      +DBHelper.PROMOTION_CITY_CODE+"  TEXT,"  +DBHelper.PROMOTION_COMPANY_ID+"  INTEGER,"
      +DBHelper.PROMOTION_COMPANY_NAME+"  TEXT," +DBHelper.PROMOTION_CONTENT+"  TEXT," +DBHelper.PROMOTION_END_TIME+"  TEXT,"
      +DBHelper.PROMOTION_NAME+"  TEXT," +DBHelper.PROMOTION_PIC_PATH_B1+"  TEXT,"
      +DBHelper.PROMOTION_PIC_PATH_B2+"  TEXT," +DBHelper.PROMOTION_PIC_PATH_S+"  TEXT," +DBHelper.PROMOTION_START_TIME+"  TEXT,"
      +DBHelper.PROMOTION_STATUS+"  INTEGER," +DBHelper.PROMOTION_TIME+"  TEXT," +DBHelper.PROMOTION_TYPE+"  INTEGER " +")";
     
    @Override
    public void onCreate(SQLiteDatabase db) {
        
         db.execSQL(CREATE_COLLECT_TABLE);
        
        
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String sql = "DROP TABLE IF EXISTS  " + TABLE_NAME + "";
        db.execSQL(sql);
        onCreate(db);
    }
}

from:http://sunjilife.blog.51cto.com/3430901/1135669

上一篇：SQLite在线备份
下一篇：android:获取联系人原理（姓名和电话）详解
本站文章内容，部分来自于互联网，若侵犯了您的权益，请致邮件chuanghui423#sohu.com(请将#换为@)联系，我们会尽快核实后删除。
Copyright © 2006-2023 DBMNG.COM All Rights Reserved. Powered by DEVSOARTECH            豫ICP备11002312号-2

豫公网安备 41010502002439号