Blog Archive

Android SQLITE query selection example

In this example we are going to learn how to create a SQLite database adapter that will perform the basic operations such as creating the table, upgrading the database as well as providing access to the data based on given input parameters. SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks.  To query a given table and return a Cursor over the result set you can use the following method.
  • public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)

Parameters
  • table 
    • The table name to compile the query against.
  • columns 
    • A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn't going to be used.
  • selection 
    • A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
  • selectionArgs 
    • You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
  • groupBy 
    • A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.
  • having 
    • A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.
  • orderBy 
    • How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.
  • limit 
    • Limits the number of rows returned by the query, formatted as LIMIT clause. Passing null denotes no LIMIT clause.
Returns
  • A Cursor object, which is positioned before the first entry. Note that Cursors are not synchronized

package com.as400samplecode.Util;

import java.io.StringReader;
import java.util.ArrayList;

import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class OrderDetailDbAdapter {

    public static final String KEY_ROWID = "_id";
    public static final String KEY_STATUS = "statusCode";
    public static final String KEY_COMPANY = "company";
    public static final String KEY_ORDER = "orderNumber";
    public static final String KEY_SEQ = "sequence";
    public static final String KEY_ITEM = "item";
    public static final String KEY_DESCRIPTION = "description";
    public static final String KEY_QUANTITY = "quantity";
    public static final String KEY_PRICE = "price";

    private static final String TAG = "OrderDetailDbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    private static final String DATABASE_NAME = "OrderDatabase";
    private static final String SQLITE_TABLE = "OrderDetail";
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE =
        "CREATE TABLE " + SQLITE_TABLE + " (" +
        KEY_ROWID + " integer PRIMARY KEY autoincrement," +
        KEY_STATUS + "," +
        KEY_COMPANY + "," +
        KEY_ORDER + "," +
        KEY_SEQ + "," +
        KEY_ITEM + "," +
        KEY_DESCRIPTION + "," +
        KEY_QUANTITY + "," +
        KEY_PRICE + "," +
        " UNIQUE (" + KEY_COMPANY + "," + KEY_ORDER + "," + KEY_SEQ  + "," + KEY_ITEM +"));";


    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }


        @Override
        public void onCreate(SQLiteDatabase db) {
            Log.w(TAG, DATABASE_CREATE);
            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + SQLITE_TABLE);
            onCreate(db);
        }
    }

    public OrderDetailDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    public OrderDetailDbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        if (mDbHelper != null) {
            mDbHelper.close();
        }
    }


    public long createOrderDetail() {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_STATUS, "A");
        initialValues.put(KEY_COMPANY, "1");
        initialValues.put(KEY_ORDER, "123");
        initialValues.put(KEY_SEQ, "1");
        initialValues.put(KEY_ITEM,"ABC");
        initialValues.put(KEY_DESCRIPTION, "Item ABC description");
        initialValues.put(KEY_QUANTITY, "100");
        initialValues.put(KEY_PRICE, "99.99");
        return mDb.insert(SQLITE_TABLE, null, initialValues);
    }

    public boolean deleteOrderDetail(long rowId) {

        return mDb.delete(SQLITE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }

    public ArrayList<OrderDetail> getOrderDetails(String company, String orderNumber) throws SQLException {


        ArrayList<OrderDetail> orderDetailList = new ArrayList<OrderDetail>();
        Cursor mCursor =


            mDb.query(true, SQLITE_TABLE, new String[] {
                    KEY_ROWID,
                    KEY_COMPANY,
                    KEY_ORDER,
                    KEY_SEQ,
                    KEY_ITEM,
                    KEY_DESCRIPTION,
                    KEY_QUANTITY,
                    KEY_PRICE,}, 
                    KEY_COMPANY + "=?" + " and "  +
                    KEY_ORDER + "=?", 
                    new String[] {company,orderNumber},
                    null, null, KEY_ITEM , null);


        if (mCursor.moveToFirst()) {
            do {
                OrderDetail orderDetail = new OrderDetail(); 
                orderDetail.setItem(mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_ITEM)));
                orderDetail.setDescription(mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_DESCRIPTION)));
                orderDetail.setQuantity(mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_QUANTITY)));
                orderDetail.setPrice(mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_PRICE)));
                orderDetailList.add(orderDetail);
            } while (mCursor.moveToNext());
        }
        if (mCursor != null && !mCursor.isClosed()) {
            mCursor.close();
        }
        return orderDetailList;


    }


    public boolean deleteAllOrderDetails() {

        int doneDelete = 0;
        doneDelete = mDb.delete(SQLITE_TABLE, null , null);
        Log.w(TAG, Integer.toString(doneDelete));
        return doneDelete > 0;

    }

}

16 comments :

  1. thanks for this example, saved me lots of time ,especially with the selection where part .

    ReplyDelete
  2. Thanks a lot! Very helpful

    ReplyDelete
  3. how to write search query ........

    ReplyDelete
  4. tnx :) it did help me to think...

    #GiftedHand11

    ReplyDelete
  5. giving error on orderDetail in array

    ReplyDelete
  6. put the search query....
    when we want to search any data from database and displayed on editbox....

    ReplyDelete
  7. thanks master , helped a lot

    ReplyDelete
  8. thankS for sharing your knowledge ....this tutorial helps a lot for beginners of android developers...

    ReplyDelete
  9. đồng tâm
    game mu
    cho thuê nhà trọ
    cho thuê phòng trọ
    nhac san cuc manh
    số điện thoại tư vấn pháp luật miễn phí
    văn phòng luật
    tổng đài tư vấn pháp luật
    dịch vụ thành lập công ty trọn gói

    Thạch Thiên Sơn biến sắc . Những lời này của Sở Dương chẳng khác gì là đâm vào nỗi đau của hắn! Không nhịn được nữa, Thạch Thiên Sơn ngẩng mạnh đầu lên nhìn Sở Dương, trầm giọng hỏi:
    - Ngươi nói lời này là có ý gì ?

    - Ý của ta, ngươi ăn cơm sẽ hiểu!
    Sở Dương nhìn hắn, ánh mắt dần trở nên lạnh lẽo, nói tà tà:
    - Để ta xem thuốc độc trong chén cơm này có thể làm chết người hay không? Nói thật, ta rất tò mò!

    Con tim Thạch Thiên Sơn như thắt lại. Hắn cười to:
    - Đệ, đệ đang nói đùa gì thế?

    Sở Dương cười khằng khặc, nói:
    - Trò đùa này không thú vị chút nào đúng không? Vậy chúng ta đổi trò vui hơn nha!
    Đột nhiên hắn bưng chén cơm lại gần , ngồi xuống đúng ngay đoạn xương đang bị gãy của Thạch Thiên Sơn , chỉ nghe thấy một tiếng “rắc” vang lên, đoạn xương bị gãy vừa nối lại bị rời ra một lần nữa.

    Thạch Thiên Sơn hét lên một tiếng thê thảm, cả người run lên vì đau nhức. Trên mặt hắn, từng giọt mồ hôi to như hạt đậu nành không ngừng toát ra, toàn thân cuộn lại như con tôm, co quắp liên hồi.

    Ngay lúc hắn vừa há miệng ra, một miếng vải bị Sở Dương xé ra từ trong chăn không biết từ bao giờ đã nhét thẳng vào miệng của hắn, hành động vô cùng nhanh nhẹn dứt khoát, chỉ sợ

    ReplyDelete
  10. android is now a day very popular. You should try this live blogging platform for all your problems.

    ReplyDelete
  11. This is very much essay writing service
    a work in progress whenever I find out about one who is more beautiful than any of these I will add her and kick out number ten Thanks for sharing the informative post.

    ReplyDelete
  12. I have found this example of how to create a SQLite database adapter that will perform the basic operations such as creating the table, upgrading the database as well as providing access to the data based on given input parameters to be very helpful. I will keep on visiting this site to learn more about how to create SQLite Database. Are you aware that you can get professional literature review rewriting help by simply clicking on this link: Literature Review Rewriting Websites

    ReplyDelete
  13. how to fetch data from the assets folder and display them into listview

    ReplyDelete