Board index » Android Coding Problems » Networking & Database Problems

Skip to content

sqlite database problem about rawQuery()---need help

Problems with WiFi, SQLite ,Bluetooth, WiMax, Proxies, etc...
Post a reply

sqlite database problem about rawQuery()---need help

Postby Bruce.lntu » Fri Dec 12, 2008 2:43 am

Hi everyone,
My problem is how to find the related records from two or three talbes created in one database when type the EditText"start" and "end" then click "Busfind". :?:
Image

When I create a sqlite database with 3 tables with another sqlite tool,
Image
Image
Image
later,, I push it into the emulator,and it works well, I mean the data can be read and written by some Cursors like
Code: Select all
Cursor cst=myDB.rawQuery("SELECT node.n_name,ln.l_id" +
                                      " FROM " + MY_DATABASE_TABLE4 +", " + MY_DATABASE_TABLE3 +
                                      " WHERE node.n_name = " +
                                      "'" + busStart +"'", null);

this show me a list lines which contains the entry"start",look at the image below;
Image Image

or which Cursor shall I use?
Code: Select all
//               Cursor ced=myDB.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
//               Cursor ced=myDB.query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
//               Cursor ced=myDB.query(distinct, table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
//               Cursor ced=myDB.queryWithFactory(cursorFactory, distinct, table, columns, selection, selectionArgs, groupBy, having, orderBy, limit)
//               Cursor ced=myDB.rawQueryWithFactory(cursorFactory, sql, selectionArgs, editTable)


please Someone show me the way to this problem, Thank you very much.

the code is,
Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. package child.bf;
  2.  
  3.  
  4.  
  5. import java.util.ArrayList;
  6.  
  7. import android.app.ListActivity;
  8.  
  9. import android.content.Intent;
  10.  
  11. import android.database.Cursor;
  12.  
  13. import android.database.sqlite.SQLiteDatabase;
  14.  
  15. import android.os.Bundle;
  16.  
  17. import android.text.GetChars;
  18.  
  19. import android.widget.ArrayAdapter;
  20.  
  21. import android.widget.EditText;
  22.  
  23.  
  24.  
  25. public class DataBaseWork extends ListActivity {
  26.  
  27.  
  28.  
  29.      private final String MY_DATABASE_NAME = "bus.db";
  30.  
  31.  
  32.  
  33.      private final String MY_DATABASE_TABLE2 = "line";
  34.  
  35.      private final String MY_DATABASE_TABLE3 = "node";
  36.  
  37.      private final String MY_DATABASE_TABLE4 = "ln";
  38.  
  39.      
  40.  
  41.      public static final String BUS_START = "startInput";
  42.  
  43.          public static final String BUS_END = "endInput";
  44.  
  45.  
  46.  
  47.      /** Called when the activity is first created. */
  48.  
  49.      @Override
  50.  
  51.      public void onCreate(Bundle icicle) {
  52.  
  53.           super.onCreate(icicle);
  54.  
  55.          
  56.  
  57.           Intent intent = getIntent();
  58.  
  59.           String busStart = intent.getStringExtra(DataBaseWork.BUS_START);
  60.  
  61.           String busEnd = intent.getStringExtra(DataBaseWork.BUS_START);
  62.  
  63.            
  64.  
  65. //          EditText et = new EditText(this);
  66.  
  67. //          et.setSelection(et.getText().length());
  68.  
  69.           /* Will hold the 'Output' we want to display at the end. */
  70.  
  71.           ArrayList<String> results = new ArrayList<String>();
  72.  
  73.  
  74.  
  75.           SQLiteDatabase myDB = null;
  76.  
  77.           try {
  78.  
  79.                /* Create the Database (no Errors if it already exists) */
  80.  
  81.                   myDB = this.openOrCreateDatabase(MY_DATABASE_NAME, MODE_PRIVATE, null);
  82.  
  83.  
  84.  
  85.                /* Create a Table in the Database. */
  86.  
  87. //table line              
  88.  
  89.                myDB.execSQL("CREATE TABLE IF NOT EXISTS "
  90.  
  91.                        + MY_DATABASE_TABLE2
  92.  
  93.                        + " (l_id INT(3), l_name VARCHAR,"
  94.  
  95.                        + " n_ids VARCHAR);");
  96.  
  97. //talbe line_node as ln              
  98.  
  99.                myDB.execSQL("CREATE TABLE IF NOT EXISTS "
  100.  
  101.                        + MY_DATABASE_TABLE4
  102.  
  103.                        + " (l_id INT(3), "
  104.  
  105.                        + " n_id INT(3));");
  106.  
  107. //talbe node              
  108.  
  109.                myDB.execSQL("CREATE TABLE IF NOT EXISTS "
  110.  
  111.                        + MY_DATABASE_TABLE3
  112.  
  113.                        + " (n_id INT(3),n_name VARCHAR, "
  114.  
  115.                        + " n_lat INT(4),n_lon INT(4),"
  116.  
  117.                        + " l_ids VARCHAR );");  
  118.  
  119.                /* Query for some results with Selection and Projection. */
  120.  
  121.                Cursor cst=myDB.rawQuery("SELECT node.n_name,ln.l_id" +
  122.  
  123.                                             " FROM " + MY_DATABASE_TABLE4 +", " + MY_DATABASE_TABLE3 +
  124.  
  125.                                             " WHERE node.n_name = " +
  126.  
  127.                                             "'" + busStart +"'", null);
  128.  
  129.                /* Get the indices of the Columns we will need */
  130.  
  131.  
  132.  
  133.                int l_idColume=cst.getColumnIndexOrThrow("l_id");
  134.  
  135.                int n_nameColume=cst.getColumnIndexOrThrow("n_name");
  136.  
  137.      
  138.  
  139.                
  140.  
  141.                /* Check if our result was valid. */
  142.  
  143.                
  144.  
  145.              cst.moveToFirst();
  146.  
  147.              if (cst != null) {
  148.  
  149.                   /* Check if at least one Result was returned. */
  150.  
  151.                   if (cst.isFirst()) {
  152.  
  153.                        int i = 0;
  154.  
  155.                        /* Loop through all Results */
  156.  
  157.                        do {
  158.  
  159.                             i++;
  160.  
  161.                             /* Retrieve the values of the Entry
  162.  
  163.                              * the Cursor is pointing to. */
  164.  
  165.                             String nname = cst.getString(n_nameColume);
  166.  
  167.                             int lid=cst.getInt(l_idColume);
  168.  
  169. //                            int nid=cst.getInt(n_idColume);
  170.  
  171.                             /* We can also receive the Name
  172.  
  173.                              * of a Column by its Index.
  174.  
  175.                              * Makes no sense, as we already
  176.  
  177.                              * know the Name, but just to show we can  */
  178.  
  179. //                           String ageColumName = c.getColumnName(ageColumn);
  180.  
  181.                              
  182.  
  183.                             /* Add current Entry to results. */
  184.  
  185.                             results.add("" + i + "(line_id: " + lid + "node_name;" +nname);
  186.  
  187.                        } while(cst.moveToNext());
  188.  
  189.                    
  190.  
  191.                   }
  192.  
  193.              }
  194.  
  195.  
  196.  
  197.           } finally {
  198.  
  199.                if (myDB != null)
  200.  
  201.                     myDB.close();
  202.  
  203.           }
  204.  
  205.  
  206.  
  207.           this.setListAdapter(new ArrayAdapter<String>(this,
  208.  
  209.                     android.R.layout.simple_list_item_1, results));
  210.  
  211.      }
  212.  
  213.  
  214.  
  215. }
Parsed in 0.053 seconds, using GeSHi 1.0.8.4
User avatar
Bruce.lntu
Developer
Developer
 
Posts: 37
Joined: Thu Jan 03, 2008 11:36 am
Top
Top
Post a reply

Return to Networking & Database Problems

Who is online

Users browsing this forum: No registered users and 6 guests

Board index The team • Delete all board cookies • All times are UTC + 1 hour