title: Implementing SQLite with ListView (data from web) in Android
body: <p>I have an a ListView which fetches the data from JSON and the app saves the downloaded images and texts from the web to the external storage though an ImageLoader class.</p>
  
  <p>I need to make the listview accessible without an internet connection, the app already succesfully saves the cache data into the SD Card.</p>
  
  <p>I found a way to do it by using SQLite. When the app runs for the first time with internet connection, it downloads the JSON data and save them to the sqlite database. When there's no internet connection, instead of downloading the data from the web, the app calls the data from the sqlite database.</p>
  
  <p>But I'm still a newbie and I have difficulties in implementing the SQLite with the codes I have.</p>
  
  <p>Here are my codes:</p>
  
  <pre><code>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);
          }
  }
  </code></pre>
  
  <p>ListView Adapter:</p>
  
  <pre><code>public class ProjectAdapter extends ArrayAdapter&lt;Project&gt; {
  
      int resource;
      String response;
      Context context;
      List&lt;Project&gt; items;
      private ImageLoaderCache imageLoader;
      LayoutInflater mInflater;
      Activity activity;
  
      private DBAdapter mDBHelper;
      // Initialize adapter
      public ProjectAdapter(Context context, int resource, List&lt;Project&gt; 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;
      }
  }
  </code></pre>
  
  <p>the class where the JSON data is populated to the ListView</p>
  
  <pre><code>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&lt;Project&gt; 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&lt;Project&gt;();
          //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&lt;String, String&gt; params = new HashMap&lt;String, String&gt;();
          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&lt;ArrayList&lt;Project&gt;&gt;(){}.getType();
  
              //JSON expects an list so can't use our ArrayList from the lstart
              List&lt;Project&gt; 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&lt;?&gt; 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 &amp;&amp; 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;
          }
      }   
  }
  </code></pre>
  
  <p>I'm open to any kind of solution.</p>