Query for fetching records from SQLite database on Blackberry

javasqlite

I want to fetch data from SQLite database for Blackberry.

There is one table with four columns: ID, FROM, TO, TIME

I want to fetch record on the basis of FROM, and TO values.

Where condition 1:

FROM = [from value] AND TO = [to value]

2nd condition is:

FROM = TO AND TO = FROM

It should return the last 20 records.

Best Answer

It's not very clear what you are after. So, this guess may be wrong:

SELECT *
FROM Messages
WHERE ( fromUserID = @From AND toUserID = @To )
   OR ( fromUserID = @To AND toUserID = @From )
ORDER BY TimeSent DESC
  LIMIT 20 ;

Related Solutions

SQLite – How to Define Table Structure for Measured Data

Your second approach is not normalized: adding another sensor would require creating a new table, and querying multiple sensors would be horribly complex.

When all four sensors get a new value every second, and if you have multiple tables, then the database would have to update four tables. A single table is certainly more efficient.

For fast queries on time ranges, you need an index on the timestamp column. This implies that every INSERT also needs to update the index.
(If you want fast queries on time ranges for a single sensor, you need another index that contains both the sensor_id and timestamp columns.)

Please note that you do not need AUTOINCREMENT when you never delete rows. (Autoincrement In SQLite)

SQLite – How to Get Data According to Last Hours

Finally I found the solution to my own question

Following is the code which worked for me

DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date();
        Calendar calendar = Calendar.getInstance();
        String startDate = dateFormat.format(date);
        String endDate = "";
        for (int i = 1; i <= 24; i++) {
            System.out.println("Start Date:- " + startDate);
            calendar.add(Calendar.HOUR_OF_DAY, -1);
            date = calendar.getTime();
            endDate = dateFormat.format(date);
            System.out.println("End Date:- " + endDate);
            String data = dbAdapter.getOutDoorHourlyData(startDate, endDate);
            System.out.println("Hourly Average:- " + data);
            startDate = endDate;
            endDate = "";
        }

public String getOutDoorHourlyData(String startDate, String endDate) {
        double outdoorHourly = 0;

        Cursor cursor = _sqliteDB.rawQuery("Select AVG("
                + COLOUMN_NAME + ") from (Select * FROM "
                + TABLE_NAME + " where " + COLOUMN_NAME + " >= '"
                + endDate + "' and " + COLOUMN_NAME + " < '" + startDate
                + "')", null);

        try {

            if (cursor != null) {
                if (cursor.getCount() > 0) {
                    cursor.moveToFirst();
                    do {
                        outdoorHourly = cursor.getDouble(0);
                    } while (cursor.moveToNext());
                }
                cursor.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        String hourlyData = decimalFormat.format(outdoorHourly);
        hourlyData = hourlyData.replace(",", ".");
        return hourlyData;

    }

 }

I hope it will help someone in future

Best Answer

Related Solutions

SQLite – How to Define Table Structure for Measured Data

SQLite – How to Get Data According to Last Hours

Related Question