在Android中使用ListView（来自网络的数据）实现SQLite

Question

我有一个 ListView，它从 JSON 获取数据，并且应用程序通过 ImageLoader 类将下载的图像和文本从 Web 保存到外部存储。

我需要在没有互联网连接的情况下访问列表视图，该应用程序已经成功地将缓存数据保存到 SD 卡中。

我找到了一种使用 SQLite 来做到这一点的方法。当应用程序首次通过互联网连接运行时，它会下载 JSON 数据并将其保存到 sqlite 数据库中。当没有互联网连接时，应用程序不会从网络下载数据，而是从 sqlite 数据库调用数据。

但我仍然是一个新手，用我现有的代码实现 SQLite 时遇到困难。

以下是我的代码：

public class DBAdapter {

       public static String KEY_PROJECTTITLE = "project title";
       public static String KEY_ORGANIZATIONTITLE = "organization title";
       public static String KEY_KEYWORD = "keyword";
       public static String KEY_SHORTCODE = "short code";
       public static String KEY_PROJECTDESCRIPTION = "description";
       public static String KEY_SMALLIMAGE = "smallImageUrl";
       public static String KEY_BIGIMAGE = "bigImageUrl";
       public static String KEY_PRICE= "price";
       public static String KEY_COUNTRY= "country";


       private static final String DATABASE_NAME = "applicationdata";
       private static final String DATABASE_TABLE_PROJECT = "Project";

       private static final int DATABASE_VERSION = 17;


       private static final String DATABASE_CREATE = "CREATE TABLE "+DATABASE_TABLE_PROJECT+" (" +
        KEY_PROJECTTITLE +      " TEXT NOT NULL, " +
        KEY_ORGANIZATIONTITLE +         " TEXT NOT NULL, "+
        KEY_KEYWORD +   " TEXT NOT NULL PRIMARY KEY, " +
        KEY_SHORTCODE +         " TEXT NOT NULL, "+
        KEY_PROJECTDESCRIPTION +    " TEXT NOT NULL, "+
        KEY_SMALLIMAGE +            " TEXT NOT NULL, "+
        KEY_BIGIMAGE +      " TEXT NOT NULL, "+
        KEY_PRICE +         " TEXT NOT NULL, "+
        KEY_COUNTRY +       " TEXT NOT NULL)";

       private final Context context; 

        private DatabaseHelper dbhelper;
        private SQLiteDatabase db;

        public DBAdapter(Context ctx) 
        {
            this.context = ctx;
            dbhelper = new DatabaseHelper(context);
        }

        public static class DatabaseHelper extends SQLiteOpenHelper {


            public DatabaseHelper(Context context) {
                super(context, DATABASE_NAME, null, DATABASE_VERSION);
            }

            @Override
            public void onCreate(SQLiteDatabase db) {
                db.execSQL(DATABASE_CREATE);
            }

            @Override
            public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
                db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_PROJECT);
                onCreate(db);

            }
        }
        //---opens the database---
        public DBAdapter open() throws SQLException 
        {
            db = dbhelper.getWritableDatabase();
            return this;
        }

        //---closes the database---    
        public void close() 
        {
            dbhelper.close();
        }

        public long insertProject(String project_title, String organization_title, String keyword, String short_code, String project_description,String smallImageUrl,String bigImageUrl,String price, String country) {
            ContentValues initialValues = new ContentValues();
            initialValues.put(KEY_PROJECTTITLE, project_title);
            initialValues.put(KEY_ORGANIZATIONTITLE, organization_title);
            initialValues.put(KEY_KEYWORD, keyword);
            initialValues.put(KEY_SHORTCODE, short_code);
            initialValues.put(KEY_PROJECTDESCRIPTION, project_description);
            initialValues.put(KEY_SMALLIMAGE, smallImageUrl);
            initialValues.put(KEY_BIGIMAGE, bigImageUrl);
            initialValues.put(KEY_PRICE, price);
            initialValues.put(KEY_COUNTRY, country);
            return this.db.insert(DATABASE_TABLE_PROJECT, null, initialValues);
        }
}

ListView Adapter：

public class ProjectAdapter extends ArrayAdapter<Project> {

    int resource;
    String response;
    Context context;
    List<Project> items;
    private ImageLoaderCache imageLoader;
    LayoutInflater mInflater;
    Activity activity;

    private DBAdapter mDBHelper;
    // Initialize adapter
    public ProjectAdapter(Context context, int resource, List<Project> items,
            Activity activity) {
        super(context, resource, items);
        this.resource = resource;
        imageLoader = new ImageLoaderCache(context);
        this.items = items;
        mInflater = (LayoutInflater) context
                .getSystemService(Context.LAYOUT_INFLATER_SERVICE);
        this.activity = activity;
    }

    @Override
    public View getView(int position, View convertView, ViewGroup parent) {

        ViewHolder holder = null;

        // Inflate the view
        if (convertView == null) {

            convertView = mInflater.inflate(resource, null);
            holder = new ViewHolder();
            holder.image = (ImageView) convertView.findViewById(R.id.image);
            holder.textTitle = (TextView) convertView
                    .findViewById(R.id.txt_title);
            convertView.setTag(holder);

        } else {
            holder = (ViewHolder) convertView.getTag();
        }

        Project project = items.get(position);

        holder.textTitle.setText(project.project_title);

        String imageurl = project.smallImageUrl;
        holder.image.setTag(imageurl);
        imageLoader.displayImage(imageurl, activity, holder.image);
        return convertView;

    }

    static class ViewHolder {

        TextView textTitle;

        ImageView image;
    }
}

将 JSON 数据填充到 ListView 的类

public class ProjectsList extends Activity {
    /** Called when the activity is first created. */
    //ListView that will hold our items references back to main.xml
    ListView lstTest;

    //Array Adapter that will hold our ArrayList and display the items on the ListView
    ProjectAdapter arrayAdapter;


    //List that will  host our items and allow us to modify that array adapter
    ArrayList<Project> prjcts=null;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.projects_list);

        //Initialize ListView
        lstTest= (ListView)findViewById(R.id.lstText);

         //Initialize our ArrayList
        prjcts = new ArrayList<Project>();
        //Initialize our array adapter notice how it references the listitems.xml layout

        arrayAdapter = new ProjectAdapter(ProjectsList.this, R.layout.listitems,prjcts,ProjectsList.this);

        //Set the above adapter as the adapter of choice for our list
            lstTest.setAdapter(arrayAdapter);
            if (isOnline())
            {
        //Instantiate the Web Service Class with he URL of the web service not that you must pass
        WebService webService = new WebService("http://liebenwald.spendino.net/admanager/dev/android/projects.json");


        //Pass the parameters if needed , if not then pass dummy one as follows
        Map<String, String> params = new HashMap<String, String>();
        params.put("var", "");

        //Get JSON response from server the "" are where the method name would normally go if needed example
        // webService.webGet("getMoreAllerts", params);
        String response = webService.webGet("", params);

        try
        {
            //Parse Response into our object
            Type collectionType = new TypeToken<ArrayList<Project>>(){}.getType();

            //JSON expects an list so can't use our ArrayList from the lstart
            List<Project> lst= new Gson().fromJson(response, collectionType);


            //Now that we have that list lets add it to the ArrayList which will hold our items.
            for(Project l : lst)
            {
                prjcts.add(l);
                ConstantData.projectsList.add(l);
            }

            //Since we've modified the arrayList we now need to notify the adapter that
            //its data has changed so that it updates the UI
            arrayAdapter.notifyDataSetChanged();
        }
        catch(Exception e)
        {
            Log.d("Error: ", e.getMessage());
        }
       }


        lstTest.setOnItemClickListener(new OnItemClickListener() {
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {              

                Intent care = new Intent(ProjectsList.this, ProjectDetail.class);
                care.putExtra("spendino.de.ProjectDetail.position",position);
                startActivity(care);
            }
        });

    }


    protected boolean isOnline() {
        ConnectivityManager cm = (ConnectivityManager) getSystemService(Context.CONNECTIVITY_SERVICE);
        NetworkInfo netInfo = cm.getActiveNetworkInfo();
        if (netInfo != null && netInfo.isConnected()) {
            return true;
        } else {
             AlertDialog.Builder alertbox = new AlertDialog.Builder(this);
             alertbox.setTitle("spendino Helfomat");
             alertbox.setMessage ("Please check your internet connection");
             alertbox.setPositiveButton("OK", new DialogInterface.OnClickListener() {
                 public void onClick(DialogInterface dialog, int id) {
                      //Main.this.finish();
                 }
             });
             alertbox.show();
            return false;
        }
    }   
}

我对任何类型的解决方案持开放态度。

Answer 1

假设这

public static String KEY_PROJECTTITLE = "project title";

是 db 中的列名，它在 []

编辑中应该没有空格：
为了 FSM 的缘故，如果您使用 SQLite，请不要使用 ArrayAdapter，而是使用 (Simple)CursorAdapter 来

避免下一个问题 C:\android\android-sdk-windows\samples\android-8\ApiDemos\src\com\example\android \apis\view\List7.java <- (Simple)CursorAdapter 示例

第二次编辑：

这是程序的工作示例，需要 2 小时才能完成...是的，我也是 Android 新手（1 个月左右）

LooserSample.zip

Answer 2

尽量不要在列名称中使用空格，例如使用“short_code”而不是“short code”。

要将数据插入数据库，请使用 SQLiteStatement 首先编译查询：

private static final String INSERT_ITEM_TYPE = "insert into " + ITEM_TYPE_TABLE_NAME + "(recordGUID, code, item_type) values (?, ?, ?)";

然后创建插入语句：

public long insertItemType(int code, String itemName) {
      UUID guid = UUID.randomUUID();
      this.insertStmt = this.db.compileStatement(INSERT_ITEM_TYPE);
      this.insertStmt.bindString(1, guid.toString());
      this.insertStmt.bindLong(2, code);
      this.insertStmt.bindString(3, itemName);
      return this.insertStmt.executeInsert();
   }

要选择，只需使用 Cursor 和查询，如下所示：

public String[] getItemType(int code){
       Cursor cursor = db.query(ITEM_TYPE_TABLE_NAME, new String [] {"item_type, recordGUID"}, "code = " + code, null, null, null, null);
       String[] list = new String[2];
       if (cursor.moveToFirst()) {
             list[0] = cursor.getString(0);
             list[1] = cursor.getString(1);
          }
          if (cursor != null && !cursor.isClosed()) {
             cursor.close();
          }
          return list;
   }

返回值也可以是 List> ，具体取决于您期望返回的行数。

public List<List<String>> getItemAction(String itemGUID){
       List<List<String>> list = new ArrayList<List<String>>();
          Cursor cursor = this.db.query(ITEM_ACTION_TABLE_NAME, new String[] { "action, recordGUID" }, "ref_item = '" + itemGUID + "'", null, null, null, null);
          if (cursor.moveToFirst()) {
             do {
                 List<String> temp = new ArrayList<String>();
                 temp.add(cursor.getString(0));
                 temp.add(cursor.getString(1));
                 list.add(temp);
             } while (cursor.moveToNext());
          }
          if (cursor != null && !cursor.isClosed()) {
             cursor.close();
          }
          return list;
   }

我希望这可以帮助您朝着正确的方向前进。