sqlite的使用

1.建立一个SqlLiteHelper类，用来创建表

public class SqlLiteHelper extends SQLiteOpenHelper {
   private static final String name = "testdb.db";

   private static class SingletonClass {
      private static final SqlLiteHelper instance = new SqlLiteHelper();
   }

   public static SqlLiteHelper getInstance() {
      return SingletonClass.instance;
   }

   /**
    * 在SQLiteOpenHelper的子类当中，必须有这个构造函数
    *
    *            当前的Activity
    *            表的名字（而不是数据库的名字，这个类是用来操作数据库的）
    *            用来在查询数据库的时候返回Cursor的子类，传空值
    *            当前的数据库的版本，整数且为递增的数
    */
   public SqlLiteHelper() {
      super(App.app, name, null, 1);
   }

   /**
    * 该函数是在第一次创建数据库时执行，只有当其调用getreadabledatebase()
    * 或者getwrittleabledatebase()而且是第一创建数据库是才会执行该函数
    */
   @Override
   public void onCreate(SQLiteDatabase db) {

      db.beginTransaction();
      try {
         db.execSQL("CREATE TABLE IF NOT EXISTS "
               + SysMessDao.TABLE_NAME + " ("
               + SysMessDao.COLUMN_ID
               + " INTEGER PRIMARY KEY AUTOINCREMENT,"
               + SysMessDao.COLUMN_CONTENT + " TEXT,"
               + SysMessDao.COLUMN_USERID + " TEXT,"
               + SysMessDao.COLUMN_TIME + " TEXT)");

         db.setTransactionSuccessful();
      } catch (SQLException e) {
         e.printStackTrace();
      } finally {

         db.endTransaction();
      }

   }

   /**
    * 更新版本
    * @param db
    * @param oldVersion
    * @param newVersion
     */
   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      String sql = "DROP TABLE IF EXISTS " + SysMessDao.TABLE_NAME;
      db.execSQL(sql);
      this.onCreate(db);
   }

}

app类：(记得配置文件加android:name=".App")

public class App extends Application{

    public static App app;
    @Override
    public void onCreate() {
        app = this;
        super.onCreate();
    }
}

2新建SysMessDao类对数据的增删改查。

public class SysMessDao {
    public static final String TABLE_NAME = "sysmess";// 表名
    public static final String COLUMN_ID = "_id";// 主键自动增长
    public static final String COLUMN_TIME = "time";// id标示
    public static final String COLUMN_CONTENT = "content";// 类型名
    public static final String COLUMN_USERID = "userid";// 用户id，查询时候可以查出不同用户对应的不同数据

    /**
     * 插入一条数据
     * @param type
     * @return
     */
    public static boolean insertType(SystemMessageBean type,String userid) {

        SQLiteDatabase db = SqlLiteHelper.getInstance().getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(SysMessDao.COLUMN_TIME, type.getTime());
        values.put(SysMessDao.COLUMN_CONTENT, type.getContent());
        values.put(SysMessDao.COLUMN_USERID, userid);

        return db.insert(SysMessDao.TABLE_NAME, null, values) != -1;
    }

    /**
     * 以集合形式插入
     * @param types
     */
    public static void insertTypes(List<SystemMessageBean> types,String userid) {

        SQLiteDatabase db = SqlLiteHelper.getInstance().getWritableDatabase();

        db.beginTransaction();

        try {
            for (int i = 0; i < types.size(); i++) {
                SystemMessageBean type = types.get(i);
                ContentValues values = new ContentValues();
                values.put(SysMessDao.COLUMN_CONTENT, type.getContent());
                values.put(SysMessDao.COLUMN_TIME, type.getTime());
                values.put(SysMessDao.COLUMN_USERID, userid);

                db.insert(SysMessDao.TABLE_NAME, null, values);
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }
    }

    /**
     *
     *
     * @return
     */
    public static void clearTable() {

        SQLiteDatabase db = SqlLiteHelper.getInstance().getWritableDatabase();

        db.beginTransaction();

        try {
            db.execSQL("DELETE FROM " + SysMessDao.TABLE_NAME);
            db.execSQL("DELETE FROM " + "sqlite_sequence");
            // db.execSQL("UPDATE sqlite_sequence SET seq = 0 WHERE name = " +
            // TABLE_NAME);
            db.setTransactionSuccessful();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }
    }

    /**
     * 清除表并插入新数据
     *
     * @param types
     * @return
     */
    public static void clearAndInsert(List<SystemMessageBean> types,String userid) {

        clearTable();
        insertTypes(types,userid);
    }

    public static List<SystemMessageBean> queryAllType(String userid) {

        SQLiteDatabase db = SqlLiteHelper.getInstance().getReadableDatabase();

        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME + " WHERE userid="+userid, null);

        List<SystemMessageBean> types = new ArrayList<>();
        while (cursor.moveToNext()) {

            SystemMessageBean type = new SystemMessageBean();
            type.setContent(cursor.getString(cursor
                    .getColumnIndex(SysMessDao.COLUMN_CONTENT)));
            type.setTime(cursor.getString(cursor
                    .getColumnIndex(SysMessDao.COLUMN_TIME)));
            types.add(type);
        }
        return types;
    }
}

3.Activity类中使用
 1).单条数据插入：
SystemMessageBean messageBean = new SystemMessageBean();
messageBean.setContent("张三");
messageBean.setTime(getCurrentDay());
SysMessDao.insertType(messageBean,userid);//不需要区分id就不要userid字段
集合插入
SysMessDao.insertTypes(list,userid);
2）.查询：
SysMessDao.queryAllType("3");//userid=3
3).删除
SysMessDao.clearTable()

OK,一些简单的sqlite操作。