Android SQLite CRUD App

원문: http://code.google.com/p/openmobster/wiki/SQLiteCRUD

Introduction

Android Device의 Local Storage인 SQLite을 이용하여 CRUD을 구현하는 tutorial을 제공합니다.

Step 1: Database 초기화

Application 시작시 “crm.db”라는 Database을 호출하여 초기화 합니다. Database는 SQLite 내에 존재하지 않다면 한번만 Create됩니다.

db = context.openOrCreateDatabase("crm.db",SQLiteDatabase.CREATE_IF_NECESSARY, null);

“crm.db” Database에 “tickets” table 존재여부를 Check하고 없을 경우에는 Table을 생성합니다.

createTable(db, "tickets");

Table이 새로 생성되어 비어 있다면 Mock Data을 넣어 줍니다.

Mock Data는 3개의 Ticket이 Insert됩니다. 표준 Insert 문법이 사용됩니다.

Ticket local = new Ticket();

local.setTitle("Search is down");

local.setCustomer("Google");

local.setSpecialist("Larry Page");

local.setComments("Seach Index Error!!!");

String insert = "INSERT INTO tickets "+" (title,customer,specialist,comments) VALUES (?,?,?,?);";

initialize

public static void initialize(Context context)        {                //Open a SQLite Database                db = context.openOrCreateDatabase("crm.db", SQLiteDatabase.CREATE_IF_NECESSARY, null);                               //Check for the 'tickets' table and create it if it does not exist                createTable(db, "tickets");                               //Load mock data into 'tickets' table if it is empty                if(isTableEmpty("tickets"))                {                        for(int i=0; i<3; i++)                        {                                Ticket local = new Ticket();                                switch(i)                                {                                        case 0:                                                local.setTitle("Search is down");                                                local.setCustomer("Google");                                                local.setSpecialist("Larry Page");                                                local.setComments("Seach Index Error!!!");                                        break;                                                                               case 1:                                                local.setTitle("Windows is down");                                                local.setCustomer("Microsoft");                                                local.setSpecialist("Steve Ballmer");                                                local.setComments("Blue Screen of Death!!!");                                        break;                                                                               case 2:                                                local.setTitle("MobileMe is down");                                                local.setCustomer("Apple");                                                local.setSpecialist("Steve Jobs");                                                local.setComments("Cannot synchronize data!!!");                                        break;                                                                               default:                                        break;                                }                                insert(local);                        }                }        }

createTable

private static void createTable(SQLiteDatabase database, String tableName)        {                try                {                        //begin the transaction                        database.beginTransaction();                                               // Create a table                        String tableSql = "CREATE TABLE IF NOT EXISTS " + tableName + " ("                                        + "id INTEGER PRIMARY KEY AUTOINCREMENT," + "title TEXT," + "customer TEXT," + "specialist TEXT," +"comments TEXT"                                        + ");";                        database.execSQL(tableSql);                                               //this makes sure transaction is committed                        database.setTransactionSuccessful();                }                finally                {                        database.endTransaction();                }        }

isTableEmpty

private static boolean isTableEmpty(String table)        {                Cursor cursor = null;                try                {                        cursor = db.rawQuery("SELECT count(*) FROM "+table, null);                                               int countIndex = cursor.getColumnIndex("count(*)");                        cursor.moveToFirst();                        int rowCount = cursor.getInt(countIndex);                        if(rowCount > 0)                        {                                return false;                        }                                               return true;                }                finally                {                        if(cursor != null)                        {                                cursor.close();                        }                }        }

---

Step 2: Database 읽기

readAll() Method는 “Tickets” table의 모든 Row을 읽어 옵니다.

cursor = db.rawQuery("SELECT * FROM tickets", null);

Select 문장에 전달 할 파라미터가 없기 때문에 두 번째 Method 인자는 null 입니다.

public static List<Ticket> readAll()        {                Cursor cursor = null;                try                {                        List<Ticket> all = new ArrayList<Ticket>();                                               cursor = db.rawQuery("SELECT * FROM tickets", null);                                               if(cursor.getCount() > 0)                        {                                int idIndex = cursor.getColumnIndex("id");                                int titleIndex = cursor.getColumnIndex("title");                                int customerIndex = cursor.getColumnIndex("customer");                                int specialistIndex = cursor.getColumnIndex("specialist");                                int commentsIndex = cursor.getColumnIndex("comments");                                cursor.moveToFirst();                                do                                {                                        int id = cursor.getInt(idIndex);                                        String title = cursor.getString(titleIndex);                                        String customer = cursor.getString(customerIndex);                                        String specialist = cursor.getString(specialistIndex);                                        String comments = cursor.getString(commentsIndex);                                                                               Ticket ticket = new Ticket();                                        ticket.setId(id);                                        ticket.setTitle(title);                                        ticket.setCustomer(customer);                                        ticket.setSpecialist(specialist);                                        ticket.setComments(comments);                                                                               all.add(ticket);                                                                               cursor.moveToNext();                                }while(!cursor.isAfterLast());                        }                                               return all;                }                finally                {                        if(cursor != null)                        {                                cursor.close();                        }                }        }

---

Step 3: Insert into the database

Transaction 선언을 하여야 합니다.

“db.beginTransaction();” Transaction 시작 지점을 지정합니다.

“db.setTransactionSuccessful();” Transaction이 성공하였음을 의미하여 Commit을 기대합니다. 이 선언이 되어 있지 않은 상태에서 Transaction이 종료 될 경우 Rollback을 기대하게 됩니다.

“db.endTransaction();” Transaction 종료를 지점을 지정합니다.

public static void insert(Ticket ticket)        {                try                {                        db.beginTransaction();                                               //insert this row                        String title = ticket.getTitle();                        String customer = ticket.getCustomer();                        String specialist = ticket.getSpecialist();                        String comments = ticket.getComments();                        String insert = "INSERT INTO tickets "+" (title,customer,specialist,comments) VALUES (?,?,?,?);";                        db.execSQL(insert,new Object[]{title,customer,specialist,comments});                                               db.setTransactionSuccessful();                }                finally                {                        db.endTransaction();                }        }

Step 4: Delete from the database

Transaction 처리 방식은 Insert와 동일하게 사용됩니다.

public static void delete(Ticket ticket)        {                try                {                        db.beginTransaction();                                               //delete this record                        String delete = "DELETE FROM tickets WHERE id='"+ticket.getId()+"'";                        db.execSQL(delete);                                               db.setTransactionSuccessful();                }                finally                {                        db.endTransaction();                }        }

SQLite CRUP 쉽고 간단하다.