Android SQlite数据库常规操作

jopen 9年前
1.Sqlite简介

       Sqlite是一款轻型的数据库,它包含在一个相对小的C库中,它的设计目标是嵌入式的,由于它占用资源非常少,可能只需要几百K的内存就可以了,并且支持Windows/Linux/Unix等等主流的操作系统,同时可以和很多种程序语言相结合,比如:C#/Java/php等,所以在嵌入式设备中特别受欢迎,这一点也正好符合android的开发要求,所以在Android开发中经常要用到该数据库。

2. 运用Sqlite,一般要有一个SQLiteOpenHelper来辅助创建数据库,连接数据库。

eg:

    public class DatabaseHelper extends SQLiteOpenHelper  {            public static final String DBNAME = "cbg_download.db";            public static final int VERSION = 1;                                //    public DatabaseHelper(Context context) {        //      //必须通过super调用父类当中的构造函数         //      super(context,DBNAME,null,VERSION);         //  }            //创建多项数据库连接            public DatabaseHelper(Context context,String name){                 this(context,name,VERSION);             }             public DatabaseHelper(Context context,String name,int version){                 this(context, name,null,version);             }             //在SQLiteOepnHelper的子类当中,必须有该构造函数             public DatabaseHelper(Context context, String name, CursorFactory factory, int version) {                 //必须通过super调用父类当中的构造函数                 super(context, name, factory, version);             }                                     @Override            public void onCreate(SQLiteDatabase db) {                //存在数据库则不会调用。进行初始操作                db.execSQL("CREATE TABLE IF NOT EXISTS priaseTable(id integer primary key autoincrement,clickId varchar(100),clickType varchar(100),clickCount INTEGER)");            }            @Override            public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {                // TODO Auto-generated method stub                db.execSQL("DROP TABLE IF EXISTS filedownlog");                onCreate(db);            }                }  
 注意:

 (1)DatabaseHelper 继承SQLiteOpenHelper。必须实现3点,

构造函数:用于初始化数据库,创建数据库

public void onCreate(SQLiteDatabase db) :用于创建表。在调getReadableDatabase或getWritableDatabase时,会判断指定的数据库是否存在,不存在则调 SQLiteDatabase.create创建, onCreate只在数据库第一次创建时才执行

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion):用于数据库更新操作

3.//单例模式  Dao 类,做一个对数据表进行操作的类

eg:

    package cdv.cq.mobilestation.Activity.praise;                import android.content.Context;        import android.database.Cursor;        import android.database.sqlite.SQLiteDatabase;        import android.widget.Toast;                //单例模式        public class Dao {            private static Dao dao = null;            private Context context;                    private Dao(Context contex) {                this.context = contex;            }                    public static Dao getInstance(Context context) {                if (dao == null) {                    dao = new Dao(context);                }                return dao;            }                    // 连接数据库            public SQLiteDatabase getConnection() {                SQLiteDatabase sqLiteDatabase = null;                try {                    sqLiteDatabase = new DatabaseHelper(context, DatabaseHelper.DBNAME)                            .getReadableDatabase();                } catch (Exception e) {                    e.printStackTrace();                }                        return sqLiteDatabase;            }                        public void createTable(SQLiteDatabase sqLiteDatabase){                sqLiteDatabase.execSQL("CREATE TABLE IF NOT EXISTS priaseTable(id integer primary key autoincrement,clickId varchar(100),clickType varchar(100),clickCount INTEGER)");            }                    public synchronized int isHasInfors(String clickId, String clickType) {                SQLiteDatabase database = getConnection();                int id = -1;                Cursor cursor = null;                try {                    cursor = database.query("priaseTable", null,                            "clickId=? AND clickType=?", new String[] { clickId,                                    clickType }, null, null, null);                    if (cursor.moveToFirst()) {                        id = cursor.getInt(0);                    }                } catch (Exception e) {                    e.printStackTrace();                } finally {                    if (null != database) {                        database.close();                    }                    if (null != cursor) {                        cursor.close();                    }                }                return id;            }                    //增            public synchronized void insert(String clickId, String clickType,                    int count) {                SQLiteDatabase database = getConnection();                // int id = dao.isHasInfors(clickId,clickType);                // if(id == 0){                // ContentValues cv = new ContentValues();                // cv.put("clickId", clickId);                // cv.put("clickType", clickType);                // cv.put("clickCount", 1);                // database.insert("priaseTable", null, cv);                // String sql =                // "insert into priaseTable(clickId,clickType,clickCount) values(?,?,?)";                // Object[] info ={clickId,clickType,"1"};                // database.execSQL(sql,info);                // }                // else{                // //更改数据操作                //                // }                try {                    String sql = "insert into priaseTable(clickId,clickType,clickCount) values(?,?,?)";                    Object[] info = { clickId, clickType, count};                    database.execSQL(sql, info);                } catch (Exception e) {                    e.printStackTrace();                } finally {                    if (null != database) {                        database.close();                    }                }                    }                    // 改            public synchronized void update(int Id, boolean isAdd) {                SQLiteDatabase database = getConnection();                int count = 0;                try{                                        Cursor curor = database.rawQuery("select * from priaseTable where id =?",new String[]{String.valueOf(Id)});                    if(curor.moveToFirst())                         count = curor.getInt(3);                    if(isAdd){                        String sql = "update priaseTable set clickCount=? where id = ?";                        Object[] info = {++count,Id};                        database.execSQL(sql,info);                        //这是在新的线程打开的,Toast只能在主线程修改UI界面                        //Toast.makeText(context, "点赞加1成功",2*1000).show();                    }                    else{                        String sql = "update priaseTable set clickCount=? where id = ?";                        Object[] info = {--count,Id};                        database.execSQL(sql,info);                        //Toast.makeText(context, "点赞减1成功",2*1000).show();                    }                }catch(Exception e){                    e.printStackTrace();                }finally{                    if (null != database) {                        database.close();                    }                }            }                        //查 点赞数            public synchronized int queryPraise(String id){                SQLiteDatabase database = getConnection();                int count = 0;                try{                    Cursor curor = database.rawQuery("select from priaseTable where id =?", new String[]{id});                    while(curor.moveToFirst())                         count = curor.getInt(4);                }catch(Exception e){                    e.printStackTrace();                }finally{                    if (null != database) {                        database.close();                    }                }                return count;                            }                }  
注意:

(1)sqlite 数据库中的表 列号是从0开始的,
eg:Cursor curor = database.rawQuery("select * from priaseTable where id =?",new String[]{String.valueOf(Id)});
while(curor.moveToFirst())
count = curor.getInt(3); //一共4列

(2)做成一个DAO类单例模式可以很好的对数据库中的表进行操作

(3)增删改查功能跟SQLserver语句一样的但是要注意:不必记太多的android自带的方法

增删改::database.execSQL(sql, info); 直接对数据库进行操作

查询用:  database.query(sql, infoquery()方法可以直接返回一个游标进行相关操作