한국어

네트워킹

온누리070 플레이스토어 다운로드
    acrobits softphone
     온누리 070 카카오 프러스 친구추가온누리 070 카카오 프러스 친구추가친추
     카카오톡 채팅 상담 카카오톡 채팅 상담카톡
    
     라인상담
     라인으로 공유

     페북공유

   ◎위챗 : speedseoul


  
     PAYPAL
     
     PRICE
     

pixel.gif

    before pay call 0088 from app


http://hmkcode.com/android-simple-sqlite-database-tutorial/


http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/


http://chintankhetiya.wordpress.com/2013/06/01/sqlite-database-example/


http://www.techotopia.com/index.php/An_Android_SQLite_Database_Tutorial   <<== this is perfect


Objectives:

  • How to start using SQLite API?
  • How to create new database & database tables?
  • How to perform CRUD “Create, Read, Update and Delete” operations?

Environment & Tools:

  • Android Developer Tools (ADT) (or Eclipse + ADT plugin)
  • AVD Nexus S Android 4.3 “emulator”
  • Min SDK 8

What we are building here?

We will build an App that can store & retrieve books title and author name.

( 1 ) Create Android Application

  • File >> New >> Android Application
  • Enter App name: SQLite App
  • Enter Project name: android-sqlite
  • Pakcage: com.hmkcode.android
  • Keep other defualt selections, click Next until you reach Finish

( 2 ) Data Model Design “Table Structure”

We want to create the following:

  • One Database instance: “BookDB“.
  • One Table: “books” with three columns idtitle & author

android-sqlite-table-books

( 3 ) Object Model “Book.java”

  • Create one Java bean class: Book.java
  • /src/com/hmkcode/android/model/Book.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
package com.hmkcode.android.model;
 
public class Book {
 
    private int id;
    private String title;
    private String author;
 
    public Book(){}
 
    public Book(String title, String author) {
        super();
        this.title = title;
        this.author = author;
    }
 
    //getters & setters
 
    @Override
    public String toString() {
        return "Book [id=" + id + ", title=" + title + ", author=" + author
                + "]";
    }
}

( 4 ) extends SQLiteOpenHelper

The recommended method to create a new SQLite database is to create a subclass ofSQLiteOpenHelper and override the onCreate() method, in which you can execute a SQLite command to create tables in the database.

This is the main step.

  • Create a new class MySQLiteHelper extends SQLiteOpenHelper.
  • MySQLiteHelper constructor must call the super class constructor.
  • Override onCreate() method to create the table(s)
  • Override onUpgrade() to drop old tables and create new ones.
  • /src/com/hmkcode/android/sqlite/MySQLiteHelper.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
package com.hmkcode.android.sqlite;
 
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
 
public class MySQLiteHelper extends SQLiteOpenHelper {
 
    // Database Version
    private static final int DATABASE_VERSION = 1;
    // Database Name
    private static final String DATABASE_NAME = "BookDB";
 
    public MySQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION); 
    }
 
    @Override
    public void onCreate(SQLiteDatabase db) {
        // SQL statement to create book table
        String CREATE_BOOK_TABLE = "CREATE TABLE books ( " +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "title TEXT, "+
                "author TEXT )";
 
        // create books table
        db.execSQL(CREATE_BOOK_TABLE);
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older books table if existed
        db.execSQL("DROP TABLE IF EXISTS books");
 
        // create fresh books table
        this.onCreate(db);
    }
 
}
  • This class MySQLiteHelper will just create for us Database “BookDB” with one empty table “books“.
  • Next, we will create methods to help us populate “insert”, read “select”, update and delete book(s) from this table.

( 5 ) Add, Get, Update & Delete a Book

  • In the same file “MySQLiteHelper.java” we will add 5 methods
  1. addBook(Book book)
  2. getBook(int id)
  3. getAllBooks()
  4. update(Book book)
  5. delete(Book book)
  • Some static constants 

Define static constants for table & columns names;

    // Books table name
    private static final String TABLE_BOOKS = "books";

    // Books Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_TITLE = "title";
    private static final String KEY_AUTHOR = "author";

    private static final String[] COLUMNS = {KEY_ID,KEY_TITLE,KEY_AUTHOR};

1. addBook(Book book)

Notice:

  • ConentValues this class is used to store a set of values.
  • Log.d() just for logging, so we can see the result later on LogCat when we run the App.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public void addBook(Book book){
                //for logging
        Log.d("addBook", book.toString());
 
        // 1. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();
 
        // 2. create ContentValues to add key "column"/value
        ContentValues values = new ContentValues();
        values.put(KEY_TITLE, book.getTitle()); // get title
        values.put(KEY_AUTHOR, book.getAuthor()); // get author
 
        // 3. insert
        db.insert(TABLE_BOOKS, // table
                null, //nullColumnHack
                values); // key/value -> keys = column names/ values = column values
 
        // 4. close
        db.close();
    }

2. getBook(int id)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
public Book getBook(int id){
 
    // 1. get reference to readable DB
    SQLiteDatabase db = this.getReadableDatabase();
 
    // 2. build query
    Cursor cursor =
            db.query(TABLE_BOOKS, // a. table
            COLUMNS, // b. column names
            " id = ?", // c. selections
            new String[] { String.valueOf(id) }, // d. selections args
            null, // e. group by
            null, // f. having
            null, // g. order by
            null); // h. limit
 
    // 3. if we got results get the first one
    if (cursor != null)
        cursor.moveToFirst();
 
    // 4. build book object
    Book book = new Book();
    book.setId(Integer.parseInt(cursor.getString(0)));
    book.setTitle(cursor.getString(1));
    book.setAuthor(cursor.getString(2));
 
    //log
Log.d("getBook("+id+")", book.toString());
 
    // 5. return book
    return book;
}

3. getAllBooks()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
public List<Book> getAllBooks() {
       List<Book> books = new LinkedList<Book>();
 
       // 1. build the query
       String query = "SELECT  * FROM " + TABLE_BOOKS;
 
       // 2. get reference to writable DB
       SQLiteDatabase db = this.getWritableDatabase();
       Cursor cursor = db.rawQuery(query, null);
 
       // 3. go over each row, build book and add it to list
       Book book = null;
       if (cursor.moveToFirst()) {
           do {
               book = new Book();
               book.setId(Integer.parseInt(cursor.getString(0)));
               book.setTitle(cursor.getString(1));
               book.setAuthor(cursor.getString(2));
 
               // Add book to books
               books.add(book);
           } while (cursor.moveToNext());
       }
 
       Log.d("getAllBooks()", books.toString());
 
       // return books
       return books;
   }

4. update(Book book)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public int updateBook(Book book) {
 
    // 1. get reference to writable DB
    SQLiteDatabase db = this.getWritableDatabase();
 
    // 2. create ContentValues to add key "column"/value
    ContentValues values = new ContentValues();
    values.put("title", book.getTitle()); // get title
    values.put("author", book.getAuthor()); // get author
 
    // 3. updating row
    int i = db.update(TABLE_BOOKS, //table
            values, // column/value
            KEY_ID+" = ?", // selections
            new String[] { String.valueOf(book.getId()) }); //selection args
 
    // 4. close
    db.close();
 
    return i;
 
}

5. delete(Book book)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
public void deleteBook(Book book) {
 
        // 1. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();
 
        // 2. delete
        db.delete(TABLE_BOOKS, //table name
                KEY_ID+" = ?"// selections
                new String[] { String.valueOf(book.getId()) }); //selections args
 
        // 3. close
        db.close();
 
        //log
    Log.d("deleteBook", book.toString());
 
    }

Complete MySQLiteHelper.java Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
package com.hmkcode.android.sqlite;
 
import java.util.LinkedList;
import java.util.List;
 
import com.hmkcode.android.model.Book;
 
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
 
public class MySQLiteHelper extends SQLiteOpenHelper {
 
    // Database Version
    private static final int DATABASE_VERSION = 1;
    // Database Name
    private static final String DATABASE_NAME = "BookDB";
 
    public MySQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION); 
    }
 
    @Override
    public void onCreate(SQLiteDatabase db) {
        // SQL statement to create book table
        String CREATE_BOOK_TABLE = "CREATE TABLE books ( " +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "title TEXT, "+
                "author TEXT )";
 
        // create books table
        db.execSQL(CREATE_BOOK_TABLE);
    }
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older books table if existed
        db.execSQL("DROP TABLE IF EXISTS books");
 
        // create fresh books table
        this.onCreate(db);
    }
    //---------------------------------------------------------------------
 
    /**
     * CRUD operations (create "add", read "get", update, delete) book + get all books + delete all books
     */
 
    // Books table name
    private static final String TABLE_BOOKS = "books";
 
    // Books Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_TITLE = "title";
    private static final String KEY_AUTHOR = "author";
 
    private static final String[] COLUMNS = {KEY_ID,KEY_TITLE,KEY_AUTHOR};
 
    public void addBook(Book book){
        Log.d("addBook", book.toString());
        // 1. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();
 
        // 2. create ContentValues to add key "column"/value
        ContentValues values = new ContentValues();
        values.put(KEY_TITLE, book.getTitle()); // get title
        values.put(KEY_AUTHOR, book.getAuthor()); // get author
 
        // 3. insert
        db.insert(TABLE_BOOKS, // table
                null, //nullColumnHack
                values); // key/value -> keys = column names/ values = column values
 
        // 4. close
        db.close();
    }
 
    public Book getBook(int id){
 
        // 1. get reference to readable DB
        SQLiteDatabase db = this.getReadableDatabase();
 
        // 2. build query
        Cursor cursor =
                db.query(TABLE_BOOKS, // a. table
                COLUMNS, // b. column names
                " id = ?", // c. selections
                new String[] { String.valueOf(id) }, // d. selections args
                null, // e. group by
                null, // f. having
                null, // g. order by
                null); // h. limit
 
        // 3. if we got results get the first one
        if (cursor != null)
            cursor.moveToFirst();
 
        // 4. build book object
        Book book = new Book();
        book.setId(Integer.parseInt(cursor.getString(0)));
        book.setTitle(cursor.getString(1));
        book.setAuthor(cursor.getString(2));
 
        Log.d("getBook("+id+")", book.toString());
 
        // 5. return book
        return book;
    }
 
    // Get All Books
    public List<Book> getAllBooks() {
        List<Book> books = new LinkedList<Book>();
 
        // 1. build the query
        String query = "SELECT  * FROM " + TABLE_BOOKS;
 
        // 2. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(query, null);
 
        // 3. go over each row, build book and add it to list
        Book book = null;
        if (cursor.moveToFirst()) {
            do {
                book = new Book();
                book.setId(Integer.parseInt(cursor.getString(0)));
                book.setTitle(cursor.getString(1));
                book.setAuthor(cursor.getString(2));
 
                // Add book to books
                books.add(book);
            } while (cursor.moveToNext());
        }
 
        Log.d("getAllBooks()", books.toString());
 
        // return books
        return books;
    }
 
     // Updating single book
    public int updateBook(Book book) {
 
        // 1. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();
 
        // 2. create ContentValues to add key "column"/value
        ContentValues values = new ContentValues();
        values.put("title", book.getTitle()); // get title
        values.put("author", book.getAuthor()); // get author
 
        // 3. updating row
        int i = db.update(TABLE_BOOKS, //table
                values, // column/value
                KEY_ID+" = ?", // selections
                new String[] { String.valueOf(book.getId()) }); //selection args
 
        // 4. close
        db.close();
 
        return i;
 
    }
 
    // Deleting single book
    public void deleteBook(Book book) {
 
        // 1. get reference to writable DB
        SQLiteDatabase db = this.getWritableDatabase();
 
        // 2. delete
        db.delete(TABLE_BOOKS,
                KEY_ID+" = ?",
                new String[] { String.valueOf(book.getId()) });
 
        // 3. close
        db.close();
 
        Log.d("deleteBook", book.toString());
 
    }
}

( 6 ) Using MySQLiteHelper in Activity

  • /src/com/hmkcode/android/MainActivity.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
package com.hmkcode.android;
 
import java.util.List;
import com.hmkcode.android.model.Book;
import com.hmkcode.android.sqlite.MySQLiteHelper;
import android.os.Bundle;
import android.app.Activity;
 
public class MainActivity extends Activity {
 
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
 
        MySQLiteHelper db = new MySQLiteHelper(this);
 
        /**
         * CRUD Operations
         * */
        // add Books
        db.addBook(new Book("Android Application Development Cookbook", "Wei Meng Lee"));  
        db.addBook(new Book("Android Programming: The Big Nerd Ranch Guide", "Bill Phillips and Brian Hardy"));      
        db.addBook(new Book("Learn Android App Development", "Wallace Jackson"));
 
        // get all books
        List<Book> list = db.getAllBooks();
 
        // delete one book
        db.deleteBook(list.get(0));
 
        // get all books
        db.getAllBooks();
 
    }
 
}
  • Deploy the App on the emulator and run it.
  • Look for the results in LogCat!

( 7 ) Where to See the Results? LogCat!

  • Log function Log.d() used within the addBook(), getBook()…etc. will log database operations on LogCat Eclipse (ADT) window.
  • If you don’t see LogCat window, you need to add it.
  • go to Window >> Show View >> Others… (on Show View window) Android >> LogCat

android-logcat

LogCat view

wEyi5voHGCOCLo1mdckM++ckqiozFF4AML9QlzGzuKPNM8b3V8qASUDedQ39Z0KBABCXmnku9yXrVzJndMkQf0B1ZKDwAgAAAAAAmBJQeAEAAAAAAEwJKLwAAAAAAACmhP8F+levNjFHgaEAAAAASUVORK5CYII=

조회 수 :
33675
등록일 :
2014.02.13
09:43:04 (*.251.139.148)
엮인글 :
http://webs.co.kr/index.php?document_srl=38739&act=trackback&key=3bd
게시글 주소 :
http://webs.co.kr/index.php?document_srl=38739
List of Articles
번호 제목 글쓴이 날짜 조회 수
37 Android SQLite Database with Multiple Tables admin 2014-02-13 49920
» Android Simple Clean Good SQLite Database Tutorial 잘된 설명 admin 2014-02-13 33675
35 android sqlite 사용하기 admin 2014-02-10 85991
34 SQLite 개발가이드 데이터베이스의 성능 admin 2014-02-10 91409
33 android - 다수의 Insert 수행시 속도 향상을 위한 팁 sQlite admin 2014-02-10 80952
32 Oracle Linux 에 Oracle DB 설치하기 admin 2013-12-03 102865
31 PreparedStatement mysql java 깔끔한설명 admin 2013-10-26 103881
30 Connect Excel VBA to a MySQL database file admin 2013-09-05 44997
29 Configuring Oracle ASM disks in Oracle Enterprise Linux admin 2013-04-20 37785
28 OS에따른 Oracle 설치버전 admin 2013-04-08 31555
27 RHEL4 + 10g 설치 _silent mode admin 2013-04-08 79436
26 OLE5 + 11G 설치 _silent mode admin 2013-04-08 82608
25 WHERE 조건절 검색시 서브쿼리는 어떻게? admin 2013-04-01 36644
24 CDR 추출 저장 Inner Join 사용 Sql 문 admin 2013-02-05 37306
23 SUPER OCM 1.8club admin 2012-12-18 30588
22 MySQL Java tutorial admin 2012-09-30 48028
21 Oracle 10g Articles admin 2012-06-24 31562
20 기본 10g 설치의 리눅스 세팅에서 추가 해줘야하는 사항(윈도우) admin 2012-06-24 38952
19 SUSE Linux Enterprise Server 10 (Oracle 10g R2 (10.2.0.1)) file admin 2012-03-09 36939
18 Upgrade Oracle from 10.2.0.1 To 10.2.0.4 (Windows) admin 2012-03-06 161336
17 Upgrade Oracle 10g Release 2 from 10201 to 10204 admin 2012-03-05 108998
16 centos 6.2 oracle 10g 설치 admin 2012-03-05 106964
15 Oracle RHEL4+10G 10.2.0.1 설치 10.2.0.5 패치 admin 2012-03-03 93503
14 Oracle Backup & restore with RMAN 기본 admin 2012-02-12 41469
13 오라클 ACE가 해설하는 Oracle Backup & Recovery admin 2012-02-07 40813
12 Oracle Backup & Restore admin 2012-02-07 86208
11 http://www.hoons.kr/ admin 2011-12-19 34337
10 Java && Excel 연동 JAVA 자바로 엑셀을 핸들링 할 수 있는 방법 admin 2011-12-19 190184
9 (C#) ASP.NET MySQL Check Already Exists Add/Insert Record 프로그래밍 코드 admin 2011-12-19 34162
8 xhost and display admin 2011-12-16 34345