Example usage for android.database.sqlite SQLiteDatabase rawQuery

List of usage examples for android.database.sqlite SQLiteDatabase rawQuery

  1. HOME
  2. Java
  3. android
  4. android.database.sqlite.*
  5. SQLiteDatabase
  6. rawQuery

Introduction

In this page you can find the example usage for android.database.sqlite SQLiteDatabase rawQuery.

Prototype

public Cursor rawQuery(String sql, String[] selectionArgs)

Source Link

Document

Runs the provided SQL and returns a Cursor over the result set.

Usage

From source file:bus_vn.gena.bus_vn.com.bus_vn.List_bus_stop.java

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.list_bus_stop);

    toolbar = (Toolbar) findViewById(R.id.toolbar);
    setSupportActionBar(toolbar);/* ww w  . ja va2 s.  co  m*/
    setTitle(" ?");
    getSupportActionBar().setDisplayHomeAsUpEnabled(true);

    Intent intent = getIntent();
    busPathId = intent.getStringExtra("busPathId");
    Context context = getApplicationContext();

    DbOpenHelper dbOpenHelper = new DbOpenHelper(context);
    SQLiteDatabase db;
    db = dbOpenHelper.getWritableDatabase();
    ContentValues cv = new ContentValues();
    // ? Type_day (,,)
    cv.clear();
    String st = "";
    st = "SELECT DISTINCT Type_day_id FROM bus_stop_path_table ";
    st = st + "INNER JOIN bus_path_table ON  bus_stop_path_table.Bus_path_id=bus_path_table.id ";
    st = st + " WHERE bus_path_table.id='" + busPathId + "'";
    Cursor c = db.rawQuery(st, null);
    kolTypeDay.clear();
    while (c.moveToNext()) {
        String st2 = "";
        st2 = c.getString(0);
        kolTypeDay.add(st2);
    }
    cv.clear();
    setupTablayout();
}

From source file:com.shalzz.attendance.DatabaseHandler.java

public ListFooter getListFooter() {
    SQLiteDatabase db = this.getReadableDatabase();
    ListFooter footer = null;//from ww w  . j ava 2 s  .com

    String selectQuery = "SELECT  sum(" + Subject.ATTENDED + ") as " + KEY_TOTAL_ATTEND + ",sum(" + Subject.HELD
            + ") as " + KEY_TOTAL_HELD + " FROM " + Subject.TABLE_NAME + ";";
    try (Cursor cursor = db.rawQuery(selectQuery, null)) {

        if (cursor.moveToFirst()) {
            footer = ListFooter.builder().setHeld(cursor.getFloat(cursor.getColumnIndexOrThrow(KEY_TOTAL_HELD)))
                    .setAttended(cursor.getFloat(cursor.getColumnIndexOrThrow(KEY_TOTAL_ATTEND))).build();
        }
        db.close();
        cursor.close();
    }

    return footer;
}

From source file:bus_vn.gena.bus_vn.com.bus_vn.tabs.Tab_time.java

@Override
public void onViewCreated(View view, Bundle savedInstanceState) {
    super.onViewCreated(view, savedInstanceState);
    Context context = getActivity();
    DbOpenHelper dbOpenHelper = new DbOpenHelper(context);
    SQLiteDatabase db;
    db = dbOpenHelper.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.clear();//from   w  ww.  jav  a 2s.c  o m

    String busStopPathId = "";
    String st = "";
    st = "SELECT bus_stop_path_table.id  FROM bus_stop_path_table ";
    st = st + " WHERE bus_stop_path_table.Bus_path_id='" + busPathId + "'";
    st = st + " AND bus_stop_path_table.Bus_stop_id='" + busStopId + "'";
    st = st + " AND bus_stop_path_table.Type_day_id='" + typeDay + "'";
    Cursor c = db.rawQuery(st, null);
    while (c.moveToNext()) {
        busStopPathId = c.getString(0);
    }
    st = "";
    st = "SELECT bus_time_table.Time  FROM bus_time_table ";
    st = st + " WHERE bus_time_table.Bus_path_stop_id='" + busStopPathId + "'";
    st = st + " AND bus_time_table.Type_day_id='" + typeDay + "'";
    c = db.rawQuery(st, null);
    ArrayList<String> busTimeArray = new ArrayList<String>();
    while (c.moveToNext()) {
        String str = c.getString(0).replaceAll(" ", "");
        if (str.equals("-")) {
        } else {
            busTimeArray.add(c.getString(0));
        }
    }
    String st3;
    String hour;
    for (int i = 0; i < busTimeArray.size(); i++) {
        st3 = busTimeArray.get(i);
        hour = st3.substring(0, st3.indexOf(':'));
        int k = 0;
        int number = 0;
        for (int j = 0; j < allTime.size(); j++) {
            if (hour.equals(allTime.get(j).Get_hour())) {
                k = 1;
                number = j;
            }
        }
        if (k == 0) {
            Time myTime = new Time();
            myTime.Set_hour(hour);
            myTime.Add_minutes(st3.substring(st3.indexOf(':') + 1, st3.length()));
            allTime.add(myTime);
        } else {
            allTime.get(number).Add_minutes(st3.substring(st3.indexOf(':') + 1, st3.length()));
        }
    }
    ArrayList<String> hours = new ArrayList<String>();
    for (int i = 0; i < allTime.size(); i++) {
        hours.add(allTime.get(i).Get_hour());
    }
    String[] resultsHours = hours.toArray(new String[hours.size()]);
    ListView listView = (ListView) getView().findViewById(R.id.lv);
    listView.setOnItemClickListener((android.widget.AdapterView.OnItemClickListener) this);
    ListviewArrayAdapterBusTime caa;
    caa = new ListviewArrayAdapterBusTime(getActivity(), resultsHours, allTime);
    listView.setAdapter(caa);
}

From source file:bus_vn.gena.bus_vn.com.bus_vn.List_bus_time.java

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.list_bus);//  w  ww.  j  a  v  a 2  s.com
    setTitle(" ?");

    Intent intent = getIntent();
    busPathId = intent.getStringExtra("busPathId");
    busStopId = intent.getStringExtra("busStopId");
    typeDay = intent.getStringExtra("typeDay");

    toolbar = (Toolbar) findViewById(R.id.toolbar);
    setSupportActionBar(toolbar);
    getSupportActionBar().setDisplayHomeAsUpEnabled(true);

    Context context = getApplicationContext();
    DbOpenHelper dbOpenHelper = new DbOpenHelper(context);
    SQLiteDatabase db;
    db = dbOpenHelper.getWritableDatabase();
    ContentValues cv = new ContentValues();
    // ? Type_day (,,)
    cv.clear();
    String st = "";
    st = "SELECT DISTINCT Type_day_id FROM bus_stop_path_table ";
    st = st + "INNER JOIN bus_path_table ON  bus_stop_path_table.Bus_path_id=bus_path_table.id ";
    st = st + " WHERE bus_path_table.id='" + busPathId + "'";
    Cursor c = db.rawQuery(st, null);
    kolTypeDay.clear();
    while (c.moveToNext()) {
        String st2 = "";
        st2 = c.getString(0);
        kolTypeDay.add(st2);
    }
    cv.clear();
    setupTablayout();
}

From source file:com.snt.bt.recon.database.DBHandler.java

public <T> int dbSyncCount(Class<T> cl) throws InstantiationException, IllegalAccessException {
    T inst = cl.newInstance();//from   w  w  w .  j  av a  2 s .c  o m
    String selectQuery = "";

    if (inst instanceof Trip)
        selectQuery = "SELECT * FROM " + TABLE_TRIPS + " where " + KEY_UPLOAD_STATUS + " = '" + "no" + "' or "
                + KEY_UPLOAD_STATUS + " = '" + "partial" + "'";

    else if (inst instanceof GPSLocation)
        selectQuery = "SELECT  * FROM " + TABLE_LOCATIONS + " where " + KEY_UPLOAD_STATUS + " = '" + "no" + "'";

    else if (inst instanceof BluetoothClassicEntry)
        selectQuery = "SELECT  * FROM " + TABLE_BC + " where " + KEY_UPLOAD_STATUS + " = '" + "no" + "'";

    else if (inst instanceof BluetoothLowEnergyEntry)
        selectQuery = "SELECT  * FROM " + TABLE_BLE + " where " + KEY_UPLOAD_STATUS + " = '" + "no" + "'";

    int count = 0;
    SQLiteDatabase database = this.getWritableDatabase();
    Cursor cursor = database.rawQuery(selectQuery, null);
    count = cursor.getCount();
    cursor.close();
    database.close();
    return count;
}

From source file:com.barcamppenang2014.tabfragment.ProfileFragment.java

License:asdf

public String check() {
    // Log.d("yc", "at check()");
    String isCreated = "false";

    MyDatabase database = new MyDatabase(getActivity());
    SQLiteDatabase sqliteDatabase = database.getReadableDatabase();
    Log.d("debug", "database.getReadableDatabase()");
    String sql = "SELECT ISPFOFILECREATED FROM USERPROFILE;";
    Cursor retrieved = sqliteDatabase.rawQuery(sql, null);
    Log.d("debug", "rawQuery");

    if (retrieved.moveToFirst()) {
        isCreated = retrieved.getString(retrieved.getColumnIndex("ISPFOFILECREATED"));
    }/* w  w  w . ja va  2 s  .c  om*/

    Log.d("debug", "checking isProfileCreated " + isCreated);
    retrieved.close();
    database.close();
    sqliteDatabase.close();
    return isCreated;
}

From source file:org.rapidandroid.activity.chart.form.FormDataBroker.java

private JSONGraphData loadBooleanPlot() {

    Date startDateToUse = getStartDate();
    DateDisplayTypes displayType = this.getDisplayType(startDateToUse, mEndDate);

    String selectionArg = getSelectionString(displayType);

    StringBuilder rawQuery = new StringBuilder();

    String fieldcol = RapidSmsDBConstants.FormData.COLUMN_PREFIX + fieldToPlot.getName();

    rawQuery.append("select time, " + fieldcol + ", count(*) from  ");
    rawQuery.append(RapidSmsDBConstants.FormData.TABLE_PREFIX + mForm.getPrefix());

    rawQuery.append(" join rapidandroid_message on (");
    rawQuery.append(RapidSmsDBConstants.FormData.TABLE_PREFIX + mForm.getPrefix());
    rawQuery.append(".message_id = rapidandroid_message._id");
    rawQuery.append(") ");
    if (startDateToUse.compareTo(Constants.NULLDATE) != 0 && mEndDate.compareTo(Constants.NULLDATE) != 0) {
        rawQuery.append(" WHERE rapidandroid_message.time > '" + Message.SQLDateFormatter.format(startDateToUse)
                + "' AND rapidandroid_message.time < '" + Message.SQLDateFormatter.format(mEndDate) + "' ");
    }/*from   ww w.ja va 2 s .c  om*/

    rawQuery.append(" group by ").append(selectionArg).append(", " + fieldcol);
    rawQuery.append(" order by ").append("time").append(" ASC");

    SQLiteDatabase db = rawDB.getReadableDatabase();
    // the string value is column 0
    // the magnitude is column 1
    Log.d("query", rawQuery.toString());
    Cursor cr = db.rawQuery(rawQuery.toString(), null);
    // TODO Auto-generated method stub
    int barCount = cr.getCount();
    Date[] allDates = new Date[barCount];
    if (barCount == 0) {
        db.close();
        cr.close();
    } else {
        List<Date> xValsTrue = new ArrayList<Date>();
        // Date[] xValsTrue = new Date[barCount];
        List<Integer> yValsTrue = new ArrayList<Integer>();
        List<Date> xValsFalse = new ArrayList<Date>();
        // Date[] xValsTrue = new Date[barCount];
        List<Integer> yValsFalse = new ArrayList<Integer>();
        cr.moveToFirst();
        int i = 0;
        do {
            String trueFalse = cr.getString(1);
            // int trueFalse2 = cr.getInt(fieldcol);
            // String trueFalseStr = cr.getString(1);

            Date thisDate = getDate(displayType, cr.getString(0));
            Log.d("FormDataBroker: ", cr.getString(0) + ", " + trueFalse + " , " + cr.getInt(2));

            if (trueFalse.equals("true")) {
                xValsFalse.add(thisDate);
                yValsFalse.add(new Integer(cr.getInt(2)));
            } else {
                xValsTrue.add(thisDate);
                yValsTrue.add(new Integer(cr.getInt(2)));
            }
            allDates[i] = thisDate;
            i++;
        } while (cr.moveToNext());

        try {
            //            String legend = this.getLegendString(displayType);
            int[] yVals = getIntsFromList(yValsTrue);
            JSONArray trueArray = getJSONArrayForValues(displayType, xValsTrue.toArray(new Date[0]), yVals);
            yVals = getIntsFromList(yValsFalse);
            JSONArray falseArray = getJSONArrayForValues(displayType, xValsFalse.toArray(new Date[0]), yVals);
            JSONArray finalValues = new JSONArray();
            JSONObject trueElem = new JSONObject();
            trueElem.put("data", trueArray);
            trueElem.put("label", "Yes");
            trueElem.put("lines", getShowTrue());
            finalValues.put(trueElem);
            JSONObject falseElem = new JSONObject();
            falseElem.put("data", falseArray);
            falseElem.put("label", "No");
            falseElem.put("lines", getShowTrue());
            finalValues.put(falseElem);
            return new JSONGraphData(finalValues, loadOptionsForDateGraph(allDates, true, displayType));

        } catch (Exception ex) {

        } finally {
            if (!cr.isClosed()) {

                cr.close();
            }
            if (db.isOpen()) {
                db.close();
            }
        }
    }
    // either there was no data or something bad happened
    return new JSONGraphData(getEmptyData(), new JSONObject());
}

From source file:com.uproot.trackme.LocationActivity.java

public void showData() throws IOException {

    SQLiteDatabase db = null;
    Cursor cursor = null;/*ww  w.  j  a  va 2s .  c om*/
    db = openOrCreateDatabase(DATABASE_NAME, SQLiteDatabase.OPEN_READWRITE, null);
    cursor = db.rawQuery("SELECT * " + " FROM " + POINTS_TABLE_NAME + " ORDER BY GMTTIMESTAMP ASC", null);

    StringBuffer str = new StringBuffer(
            "<session id=\"chetan123\" userid=\"" + userid + "\" passkey=\"" + passkey + "\">");

    int latitudeColumnIndex = cursor.getColumnIndexOrThrow("LATITUDE");
    int longitudeColumnIndex = cursor.getColumnIndexOrThrow("LONGITUDE");
    int TSColumnIndex = cursor.getColumnIndexOrThrow("GMTTIMESTAMP");
    int ACCColumnIndex = cursor.getColumnIndexOrThrow("ACCURACY");
    if (cursor.moveToFirst()) {
        do {
            double latitude = cursor.getDouble(latitudeColumnIndex);
            double longitude = cursor.getDouble(longitudeColumnIndex);
            long timestamp = (long) cursor.getDouble(TSColumnIndex);
            long accuracy = (long) cursor.getDouble(ACCColumnIndex);
            str.append("<location latitude=\"");
            str.append(latitude);
            str.append("\" longitude=\"");
            str.append(longitude);
            str.append("\" accuracy=\"");
            str.append(accuracy);
            str.append("\" timestamp=\"");
            str.append(timestamp);
            str.append("\" />");
        } while (cursor.moveToNext());
        str.append("</session>");
        fileContents = str.toString();
    }
    db.close();
}

From source file:com.dpcsoftware.mn.CategoryStats.java

public void renderGraph() {
    SQLiteDatabase db = DatabaseHelper.quickDb(this, 0);
    if (date == null)
        date = Calendar.getInstance().getTime();

    String queryModifier = "";
    if (isByMonth)
        queryModifier = " AND strftime('%Y-%m'," + Db.Table1.TABLE_NAME + "." + Db.Table1.COLUMN_DATAT + ") = '"
                + app.dateToDb("yyyy-MM", date) + "'";

    Cursor c = db.rawQuery("SELECT " + Db.Table2.TABLE_NAME + "." + Db.Table2._ID + "," + Db.Table2.TABLE_NAME
            + "." + Db.Table2.COLUMN_NCAT + "," + Db.Table2.TABLE_NAME + "." + Db.Table2.COLUMN_CORCAT + ","
            + "SUM(" + Db.Table1.TABLE_NAME + "." + Db.Table1.COLUMN_VALORT + ")" + " FROM "
            + Db.Table1.TABLE_NAME + "," + Db.Table2.TABLE_NAME + " WHERE " + Db.Table1.TABLE_NAME + "."
            + Db.Table1.COLUMN_IDCAT + " = " + Db.Table2.TABLE_NAME + "." + Db.Table2._ID + " AND "
            + Db.Table1.TABLE_NAME + "." + Db.Table1.COLUMN_IDGRUPO + " = " + app.activeGroupId + queryModifier
            + " GROUP BY " + Db.Table1.TABLE_NAME + "." + Db.Table1.COLUMN_IDCAT + " ORDER BY "
            + Db.Table2.COLUMN_NCAT, null);

    float[] values = new float[c.getCount()];
    int[] colors = new int[c.getCount()];
    float total = 0;

    while (c.moveToNext()) {
        values[c.getPosition()] = c.getFloat(3);
        colors[c.getPosition()] = c.getInt(2);
        total += c.getFloat(3);/*from  w w  w .  j  a  va2s  .c  o m*/
    }

    BarChart v = new BarChart(this, values, colors);
    v.setPadding(10, 10, 10, 10);
    FrameLayout graphLayout = ((FrameLayout) findViewById(R.id.FrameLayout1));
    if (graphLayout.getChildCount() == 1)
        graphLayout.removeViewAt(0);
    graphLayout.addView(v);

    ListView lv = ((ListView) findViewById(R.id.listView1));
    ((TextView) footer.findViewById(R.id.textView2)).setText(app.printMoney(total));

    int days = 1;
    if (!isByMonth) {
        SimpleDateFormat dateF = new SimpleDateFormat("yyyy-MM-dd");
        dateF.setTimeZone(TimeZone.getDefault());

        Cursor cTemp = db.rawQuery("SELECT " + Db.Table1.COLUMN_DATAT + " FROM " + Db.Table1.TABLE_NAME
                + " WHERE " + Db.Table1.COLUMN_IDGRUPO + " = " + app.activeGroupId + " ORDER BY "
                + Db.Table1.COLUMN_DATAT + " DESC", null);
        try {
            cTemp.moveToFirst();
            Date date2 = dateF.parse(cTemp.getString(0));
            cTemp.moveToLast();
            Date date1 = dateF.parse(cTemp.getString(0));

            days = (int) Math.ceil((date2.getTime() - date1.getTime()) / (1000.0 * 24 * 60 * 60)) + 1;

            App.Log("" + days);
        } catch (Exception e) {
            e.printStackTrace();
        }
    } else {
        Calendar cal = Calendar.getInstance();
        cal.setTime(date);
        Calendar now = Calendar.getInstance();
        if (cal.get(Calendar.MONTH) == now.get(Calendar.MONTH)
                && cal.get(Calendar.YEAR) == now.get(Calendar.YEAR))
            days = now.get(Calendar.DAY_OF_MONTH);
        else
            days = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
    }

    ((TextView) footer2.findViewById(R.id.textView2)).setText(app.printMoney(total / days));

    ((TextView) findViewById(R.id.textViewMonth)).setText(app.dateToUser("MMMM / yyyy", date));

    if (adapter == null) {
        adapter = new CategoryStatsAdapter(this, c, total);
        lv.setAdapter(adapter);
    } else {
        adapter.changeCursor(c, total);
        adapter.notifyDataSetChanged();
    }
}

From source file:bus_vn.gena.bus_vn.com.bus_vn.tabs.Tab_list_bus_stop.java

@Override
public void onViewCreated(View view, Bundle savedInstanceState) {
    super.onViewCreated(view, savedInstanceState);
    Context context = getActivity();
    DbOpenHelper dbOpenHelper = new DbOpenHelper(context);
    SQLiteDatabase db;
    db = dbOpenHelper.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.clear();/*from w w w .j av  a 2  s .  c om*/
    String st;
    st = "";
    st = "SELECT bus_stop_table.Name FROM bus_stop_path_table INNER JOIN bus_stop_table ON  bus_stop_path_table.Bus_stop_id=bus_stop_table.id ";
    st = st + "INNER JOIN bus_path_table ON  bus_stop_path_table.Bus_path_id=bus_path_table.id ";
    st = st + " WHERE bus_stop_path_table.Bus_path_id='" + busPathId + "'";
    st = st + " AND bus_stop_path_table.Type_day_id='" + typeDay + "'";
    Cursor c = db.rawQuery(st, null);

    while (c.moveToNext()) {
        String st2 = "";
        st2 = c.getString(0);
        results.add(st2);
    }
    //  ??
    ListView lvMain = (ListView) getView().findViewById(R.id.listView1);
    lvMain.setDivider(null);//   ? ListView
    //?? ?
    String[] values = results.toArray(new String[results.size()]);

    // custom adapter ? listview
    //? ClickListener ? listview
    lvMain.setOnItemClickListener(new AdapterView.OnItemClickListener() {
        // ?
        public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
            //?  ?
            String busStop = (String) parent.getItemAtPosition(position);
            String busStopId = "";
            Context context = getActivity();
            DbOpenHelper dbOpenHelper = new DbOpenHelper(context);
            SQLiteDatabase db;
            db = dbOpenHelper.getWritableDatabase();
            ContentValues cv = new ContentValues();
            cv.clear();

            String st = "";
            st = "SELECT bus_stop_table.id FROM bus_stop_table ";
            st = st + " WHERE bus_stop_table.Name='" + busStop + "'";
            Cursor c = db.rawQuery(st, null);
            while (c.moveToNext()) {
                busStopId = c.getString(0);
            }
            Intent intent = new Intent(getActivity(), List_bus_time.class);
            intent.putExtra("busPathId", busPathId);
            intent.putExtra("busStopId", busStopId);
            intent.putExtra("typeDay", typeDay);
            //?  ? ?  
            startActivity(intent);
            //     
            // overridePendingTransition(R.anim.slide_left_in, R.anim.slide_left_out);**/
        }
    });
    ListviewArrayAdapter adapter = new ListviewArrayAdapter(getActivity(), values, values.length - 1);
    //     listview
    lvMain.setAdapter(adapter);
}