Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>I have rewrite your DatabaseHelpe and a Test Activity:</p> <pre><code>public class DataBaseHelper { private static final String DB_NAME = "DB_BusData"; public static final String TABLE_NAME = "Tbl_Driver"; private Context context; private String path; private SQLiteDatabase database; private boolean isInitializing = false; public DataBaseHelper(Context context) { this.context = context; this.path = context.getDatabasePath(DB_NAME).getAbsolutePath(); if (TextUtils.isEmpty(this.path)) { throw new IllegalArgumentException("database can't be null"); } } public SQLiteDatabase getReadableDatabase() { synchronized (this) { checkAndCopyDatabase(); return getDatabaseLocked(false); } } /** * Attention：just support readable database until now * * @return */ public SQLiteDatabase getWriteableDatabase() { synchronized (this) { checkAndCopyDatabase(); return getDatabaseLocked(true); } } private void checkAndCopyDatabase() { File file = new File(this.path); if (file.exists() &amp;&amp; file.length() &gt; 0) { Log.d("TAG", "db already exist"); } else { try { InputStream is = context.getAssets().open(DB_NAME); copyStreamToFile(is, new File(this.path)); } catch (IOException e) { e.printStackTrace(); } } } private static final void copyStreamToFile(InputStream inputStream, File file) { ensureDir(file); FileOutputStream fos = null; try { fos = new FileOutputStream(file); byte[] buffer = new byte[2048]; int read = 0; while ((read = inputStream.read(buffer)) &gt; 0) { fos.write(buffer, 0, read); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { quietClose(inputStream); quietClose(fos); } } private static final void ensureDir(File file) { if (file != null &amp;&amp; (file = file.getParentFile()) != null &amp;&amp; !file.exists()) { file.mkdirs(); } } private static final void quietClose(final Closeable closeable) { if (closeable != null) { try { closeable.close(); } catch (final IOException e) { } } } private SQLiteDatabase getDatabaseLocked(boolean writeable) { if (this.database != null) { if (!this.database.isOpen()) { database = null; } else if (!writeable || !database.isReadOnly()) { return database; } } if (isInitializing) { throw new IllegalArgumentException("getDatabase called recursively"); } SQLiteDatabase db = this.database; try { isInitializing = true; if (db != null &amp;&amp; writeable &amp;&amp; db.isReadOnly()) { if (db.isOpen()) { db.close(); } db = null; } try { db = SQLiteDatabase.openDatabase(this.path, null, writeable ? SQLiteDatabase.OPEN_READWRITE : SQLiteDatabase.OPEN_READONLY); } catch (SQLiteException e) { e.printStackTrace(); } this.database = db; return db; } finally { isInitializing = false; if (db != null &amp;&amp; db != database) { db.close(); } } } public static class Driver implements BaseColumns { long id; String code; String name; static final String CODE_CLOMN_NAME = "Driver_Code"; static final String NAME_CLOMN_Name = "Driver_Name"; @Override public String toString() { return name; } } public List&lt;Driver&gt; queryAllDriver() { List&lt;Driver&gt; drivers = null; SQLiteDatabase db = getReadableDatabase(); if (db != null) { Cursor cursor = null; try { cursor = db.query(TABLE_NAME, null, null, null, null, null, null); if(cursor != null &amp;&amp; cursor.moveToFirst()) { do { final long id = cursor.getLong(cursor.getColumnIndex(Driver._ID)); final String code = cursor.getString(cursor.getColumnIndex(Driver.CODE_CLOMN_NAME)); final String name = cursor.getString(cursor.getColumnIndex(Driver.NAME_CLOMN_Name)); Driver driver = new Driver(); driver.id = id; driver.code = code; driver.name = name; if(drivers == null) drivers = new ArrayList&lt;DataBaseHelper.Driver&gt;(); drivers.add(driver); } while(cursor.moveToNext()); } } catch (SQLiteException e) { e.printStackTrace(); } finally { if(cursor != null) cursor.close(); } db.close(); } return drivers; } </code></pre> <p>}</p> <p>The DB open and operation code is above, then I write a Test Activity:</p> <pre><code>public class MainActivity extends Activity implements OnItemSelectedListener { private List&lt;Driver&gt; drivers; private Spinner spinner; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); spinner = (Spinner) findViewById(R.id.spinner); spinner.setOnItemSelectedListener(this); new DBTask().execute(); } class DBTask extends AsyncTask&lt;Void, Void, List&lt;Driver&gt;&gt; { @Override protected List&lt;Driver&gt; doInBackground(Void... params) { DataBaseHelper dbHelper = new DataBaseHelper(MainActivity.this); return dbHelper.queryAllDriver(); } @Override protected void onPostExecute(List&lt;Driver&gt; result) { bindSpinner(result); } } private void bindSpinner(List&lt;Driver&gt; drivers) { this.drivers = drivers != null ? drivers : new ArrayList&lt;DataBaseHelper.Driver&gt;(0); ArrayAdapter&lt;Driver&gt; adapter = new ArrayAdapter&lt;DataBaseHelper.Driver&gt;(this, android.R.layout.simple_spinner_item, this.drivers); adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item); spinner.setAdapter(adapter); } @Override public void onItemSelected(AdapterView&lt;?&gt; parent, View view, int position, long id) { if (this.spinner != null &amp;&amp; position &gt;= 0 &amp;&amp; position &lt; this.drivers.size()) { Driver driver = drivers.get(position); Toast.makeText(this, "selected: driver=" + driver.name + ", code=" + driver.code, Toast.LENGTH_SHORT).show(); } } @Override public void onNothingSelected(AdapterView&lt;?&gt; parent) { Toast.makeText(this, "nothing selected", Toast.LENGTH_SHORT).show(); } </code></pre> <p>} The Test Activity is works fine for me, I use the database that I simulated. I think you can't store spinner item in database, how many item in spinner? 10? 100 ? 1000? </p> <p>You can move query data outside DataBaseHelper.java like this:</p> <pre><code>private List&lt;Driver&gt; queryAllDriver() { List&lt;Driver&gt; drivers = null; DataBaseHelper helper = new DataBaseHelper(this); SQLiteDatabase db = helper.getReadableDatabase(); if (db != null) { Cursor cursor = null; try { cursor = db.query(DataBaseHelper.TABLE_NAME, null, null, null, null, null, null); if(cursor != null &amp;&amp; cursor.moveToFirst()) { do { final long id = cursor.getLong(cursor.getColumnIndex(Driver._ID)); final String code = cursor.getString(cursor.getColumnIndex(Driver.CODE_CLOMN_NAME)); final String name = cursor.getString(cursor.getColumnIndex(Driver.NAME_CLOMN_Name)); Driver driver = new Driver(); driver.id = id; driver.code = code; driver.name = name; if(drivers == null) drivers = new ArrayList&lt;DataBaseHelper.Driver&gt;(); drivers.add(driver); } while(cursor.moveToNext()); } } catch (SQLiteException e) { e.printStackTrace(); } finally { if(cursor != null) cursor.close(); } db.close(); } return drivers; } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload