SQLite v 소개 ü SQLite 라이브러리를통해완전한관계형데이터베이스 (RDBMS) 기능제공 ü 오픈소스 ü 표준준수 ü 경량 ü 단일계층 2

Transcription

1 Android 데이터베이스 (SQLite) 모바일응용

2 SQLite v 소개 ü SQLite 라이브러리를통해완전한관계형데이터베이스 (RDBMS) 기능제공 ü 오픈소스 ü 표준준수 ü 경량 ü 단일계층 2

3 SQLite 데이터베이스라이브러리 v SQLiteDatabase ü 추가라이브러리 (android.database.sqlite.sqlitedatabase) ü 데이터베이스를다루는작업 ( 추가, 삭제, 수정, 질의 ) 를담당 v SQLiteOpenHelper ü 추가라이브러리 (android.database.sqlite.sqliteopenhelper) ü 데이터베이스의생성, 열기, 업그레이드를담당 3

4 SQLite 데이터베이스라이브러리 v 데이터베이스설계고려사항 ü 파일은보통데이터베이스테이블로저장하지않음 대신문자열을이용해해당파일의경로를저장한다. 정규화된콘텐트공급자 URI 이용 ü 거의모든테이블에자동증가키 (auto-increment key) 필드를포함시켜, 각행에대한인덱스값으로기능하도록하는것을적극권장. 4

5 SQLiteDatabase v SQLiteDatabase 이용하여데이터베이스열고생성하기 ü DDL구문을사용하기위해 SQLiteDatabase의 ExecSQL 메소드호출 ü SQLiteDatabase의 ExecSQL 메소드를이용한테이블생성 예제 private static final String DATABASE_NAME = "mydatabase.db"; private static final String TABLE_NAME = "maintable"; private static final String dbcreatestring = "create table " + TABLE_NAME + " ( _id integer primary key autoincrement," + "column_one text not null);"; 데이터베이스이름및테이블명전역변수선언 데이터베이스생성에대한쿼리문전역변수 SQLiteDatabase mydatabase; private void createdatabase() { mydatabase = openorcreatedatabase(database_name, Context.MODE_PRIVATE, null); mydatabase.execsql( dbcreatestring ); openorcreatedatabase 를호출하여새로운데이터베이스생성후인스턴스의 exesql 을호출하여 SQL 명령을실행한다. 5

6 SQLiteDatabase v 예제 : DB 및테이블생성 import java.util.locale; import android.app.activity; import android.os.bundle; import android.database.sqlite.sqlitedatabase; public class TestingData extends Activity { /** Called when the activity is first created. public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); setcontentview(r.layout.main); SQLiteDatabase db; db = openorcreatedatabase( "TestingData.db", SQLiteDatabase.CREATE_IF_NECESSARY, null ); db.setversion(1); db.setlocale(locale.getdefault()); db.setlockingenabled(true); " final String CREATE_TABLE_COUNTRIES = "CREATE TABLE tbl_countries (" + "id INTEGER PRIMARY KEY AUTOINCREMENT," + "country_name TEXT);"; final String CREATE_TABLE_STATES = "CREATE TABLE tbl_states (" + "id INTEGER PRIMARY KEY AUTOINCREMENT," + "state_name TEXT," + "country_id INTEGER NOT NULL CONSTRAINT " + "contry_id REFERENCES tbl_contries(id) " + "ON DELETE CASCADE);"; db.execsql(create_table_countries); db.execsql(create_table_states); final String CREATE_TRIGGER_STATES = "CREATE TRIGGER fk_insert_state BEFORE " + "INSERT on tbl_states" + "FOR EACH ROW " + "BEGIN " + "SELECT RAISE(ROLLBACK, 'insert on table " + ""tbl_states" voilates foreign key constraint " + ""fk_insert_state"') WHERE (SELECT id FROM " + "tbl_countries WHERE id = NEW.country_id) IS NULL; + "END;"; db.execsql(create_trigger_states); 6

7 SQLiteDatabase v 데이터삽입하기 ü ContentValues 객체를이용하여데이터베이스테이블에새로운행을삽입가능 ü ContentValues 의값에대한열이름과맵으로서하나의행을나타냄 ü ContentValues 객체에데이터베이스테이블에맞는자료를입력후 SQLiteDatabase 클래스의 insert() 메소드를사용하여새로운레코드추가 ü 예제 ContentValues values = new ContentValues(); values.put("country_name", "US"); long countryid = db.insert("tbl_countries", null, values); ContentValues statevalues = new ContentValues(); statevalues.put("state_name", "Texas"); statevalues.put("country_id", Long.toString(countryId)); try { db.insertorthrow("tbl_states", null, statevalues); catch (Exception e) { //catch code ContentValues 객체에 put 메소드를사용하여데이터입력 Insert 메소드를사용하여 ContentValues 객체데이터입력 7

8 v 테이블 update 하기 ContentValues updatecountry = new ContentValues(); updatecountry.put("country_name", "United States"); db.update("tbl_countries", updatecountry, "id=?", new String[] {Long.toString(countryId)); v 테이블에서 ROW 삭제하기 db.delete("tbl_states", "id=?", new String[] {Long.toString(countryId)); 8

9 데이터베이스쿼리 ( 질의 ) v Query( 질의 ) ü 질의를통해데이터베이스를접근 ü 질의결과는 Cursor 객체형태로반환 ü 메소드형태 public Cursor query (String table, String[] columns, String selection, String[] selectionargs, String groupby, String having, String orderby, String limit) Table - 질의를수행할테이블이름입니다. columns - 자료를받아올필드들입니다. null을입력하면모든필드를반환합니다. selection - SQL의 "where" 구문에해당되는조건을입력합니다. 조건이많을경우,? 로대체합니다. selectionargs - selection을? 로지정하였을경우, 그조건들을입력합니다. groupby - SQL의 "group by" 구문에해당합니다. Having - groupby를지정했을경우, 그조건을넣어줍니다. orderby - 결과값정렬방식을지정합니다. null을입력하면기본정렬을수행합니다. limit 결과값의개수를제한합니다. ü 예제 ( 위메소드와다른형태 ) // 모든레코드를반환하는쿼리를실행합니다. Cursor all = mydb.query("data", null, null, null, null, null, null, null); // 이름이 google 인레코드를반환하는쿼리를실행합니다. Cursor sel = mydb.query("data", "name = google", null, null, null, null, null, null); 9

10 Cursor 와 ContentValues v Cursor ü 질의 (Queries) 결과를 Cursor 객체로반환받음 ü Cursor 객체는결과값의사본이아닌실제데이터 ( 레코드 ) 를가리키는역활 ü Cursor 클래스는질의결과탐색을위한여러함수를포함함 movetofirst 커서를질의결과내의첫번째행으로옮긴다. movetonext 커서를다음행으로옮긴다. movetoprevious 커서를이전행으로옮긴다. getcount 결과셋에있는행의수를리턴 getcloumnindexorthrow 지정된이름을가진열에대한인덱스를리턴 getcloumnname 지정된열인덱스의이름을리턴 getcolumnnames 현재커서에있는모든열이름을가진 String 배열하나를리턴 movetoposition 현재커서위치를리턴 getposition 커서가현재가리키고있는위치를반환 get< 데이터타입 >( 필드인덱스 ) 커서에서데이터를받아올때사용예제 ) 레코드로부터이름을받아옵니다. String name = result.getstring(1); 다른데이터형식 10

11 SQLite 데이터베이스 v 데이터베이스질의하기 ü 일반적인질의예제 ) String[] result_columns = new String[] {KEY_ID, KEY_COL1, KEY_COL3; Cursor allrows = mydatabase.query(true, DATABASE_TABLE, result_columns, null, null, null, null, null, null); 모든행의 1 열과 3 열을중복없이리턴하는질의 // Return all columns for rows where column 3 equals a set value // and the rows are ordered by column 5. String where = KEY_COL3 + "=" + requiredvalue; String order = KEY_COL5; Cursor myresult = mydatabase.query(database_table, null, where, null, null, null, order); 3 열의값과설정된값과같은행의모든열을리턴하고행들을 5 열기준으로정렬하는질의 11

12 SQLite 데이터베이스 v 데이터베이스질의하기 ü 커서에서결과얻어오는예제 ) int GOLD_HOARDED_COLUMN = 2; Cursor mygold = mydatabase.query("goldhoards", null, null, null, null, null, null); float totalhoard = 0f; // Make sure there is at least one row. if (mygold.movetofirst()) { // Iterate over each cursor. do { float hoard = mygold.getfloat(gold_hoarded_column); totalhoard += hoard; while(mygold.movetonext()); Float averagehoard = totalhoard / mygold.getcount(); GlodHoards 란문자를가진행들을커서에서지원한메소드를통해커서를이동하며커서가위치한데이터를안전하게가져와부동소수열을추출하여더하는작업을검색된수만큼반복하고있다. 12

13 SQLite 데이터베이스 v 데이터베이스행추가, 업데이트, 제거 ü SQLiteDatabase 클래스 삽입삭제업데이트 SQL문을메서드로가지고있음. 캡슐화하도록특화된메서드를제공 insert() delete() update() ü execsql 메서드 유효한 SQL 이라면어떠한것이든데이터베이스테이블상에실행가능함 13

14 SQLite 데이터베이스 v 새로운행삽입하기 ü 예제 ) // Create a new row of values to insert. ContentValues newvalues = new ContentValues(); // Assign values for each row. newvalues.put(column_name, newvalue); [... 각열마다반복... ] // Insert the row into your table mydatabase.insert(database_table, null, newvalues); 1. contentvalues 객체를통해삽입할새로운행하나를생성 2. 각열에값을할당 3. 값을할당받은행을테이블에삽입 14

15 SQLite 데이터베이스 v 데이터베이스에있는행업데이트하기 ü 예제 ) // Define the updated row content. ContentValues updatedvalues = new ContentValues(); // Assign values for each row. newvalues.put(column_name, newvalue); [... 각열마다반복... ] String where = KEY_ID + "=" + rowid; // Update the row with the specified index with the new values. mydatabase.update(database_table, newvalues, where, null); v 행삭제하기 ü 예제 ) 1. contentvalues 객체를통해업데이드할행하나를생성 2. 각열에값을할당 3. 업데이트할행인덱스값지정 4. 지정된인덱스의행을새로운값으로업데이트 mydatabase.delete(database_table, KEY_ID + "=" + rowid, null); 1. 테이블이름과지우고자하는행을리턴하는 where 절을지정하여삭제 15

16 SQLite 데이터베이스 v 트랜잭션지원 db.begintransaction(); Cursor cur = null; try { cur = db.query("tbl_countries", null, null, null, null, null, null); cur.movetoposition(0); ContentValues values = new ContentValues(); values.put("state_name", "Georgia"); values.put("country_id", cur.getstring(0)); long stateid = db.insert("tbl_states", null, values); db.settransactionsuccessful(); view.append("n" + Long.toString(stateId)); catch (Exception e) { Log.e("Error in transaction", e.tostring()); finally { db.endtransaction(); cur.close(); 16

17 SQLite 데이터베이스 v 예 : DB insert 와 query public class DBTest extends Activity { public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); setcontentview(r.layout.main); SQLiteDatabase db = null; try { db = openorcreatedatabase("sample.db",sqlitedatabase.create_if_necessary, null); db.execsql("create TABLE IF NOT EXISTS fruit + " (id integer primary key autoincrement, name varchar(30));"); db.execsql("insert into fruit(name) values('grape');"); db.execsql("insert into fruit(name) values('apple');"); catch (Exception e) { // TODO: handle exception Log.e("ERROR","ERROR IN CODE:"+e.toString()); TextView tv = (TextView) findviewbyid(r.id.textview01); Cursor c = db.query("fruit", new String[] {"name", null, null, null, null, null); int rowcount = c.getcount(); c.movetofirst(); String str = ""; for (int i = 0; i < rowcount; i++) { str += c.getstring(0)+"\n"; c.movetonext(); tv.settext(str); if (db!= null) { db.close(); 17

18 DB Adapter 형태로만들어보기 v 전화번호부 DB Adapter 만들어보기 ü 메소드설계 Class PhoneDBAdapter open() close() public long createbook(string name, String phone) public boolean deletebook(long rowid) public Cursor fetchallbooks() public Cursor fetchbook(long rowid) public boolean updatebook(long rowid, String name, String phone) 18

19 DB Adapter 형태로만들어보기 v 전화번호부 DBAdapter 구현 public class DbAdapter { public static final String KEY_NAME = "name"; public static final String KEY_PHONE = "phone"; public static final String KEY_ROWID = "_id"; public static final int FIND_BY_NAME = 0; public static final int FIND_BY_PHONE = 1; private static final String TAG = "DbAdapter"; private DatabaseHelper mdbhelper; private SQLiteDatabase mdb; // 데이터베이스를저장 private static final String DATABASE_CREATE = "create table data (_id integer primary key autoincrement,"+ "name text not null, phone text not null);"; private static final String DATABASE_NAME = "datum.db"; private static final String DATABASE_TABLE = "data"; private static final int DATABASE_VERSION = 1; private final Context mctx; private class DatabaseHelper extends SQLiteOpenHelper{ public DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); // TODO Auto-generated constructor stub public void oncreate(sqlitedatabase db){ db.execsql(database_create); public void onupgrade(sqlitedatabase db, int oldversion, int newversion){ Log.w(TAG, "Upgrading db from version" + oldversion + " to" + newversion + ", which will destroy all old data"); db.execsql("drop TABLE IF EXISTS data"); oncreate(db); public DbAdapter(Context ctx){ this.mctx = ctx; public DbAdapter open() throws SQLException{ mdbhelper = new DatabaseHelper(mCtx); mdb = mdbhelper.getwritabledatabase(); return this; public void close(){ mdbhelper.close(); public long createbook(string name, String phone){ ContentValues initialvalues = new ContentValues(); initialvalues.put(key_name, name); initialvalues.put(key_phone, phone); return mdb.insert(database_table, null, initialvalues); public boolean deletebook(long rowid){ return mdb.delete(database_table, KEY_ROWID + "=" + rowid, null) > 0; public Cursor fetchbook(long rowid) throws SQLException{ Cursor mcursor = mdb.query(true, DATABASE_TABLE, new String[]{KEY_ROWID, KEY_NAME, KEY_PHONE, KEY_ROWID + "=" + rowid, null, null, null, null, null); if(mcursor!= null) mcursor.movetofirst(); return mcursor; public boolean updatebook(long rowid, String name, String phone){ ContentValues args = new ContentValues(); args.put(key_name, name); args.put(key_phone, phone); return mdb.update(database_table, args, KEY_ROWID + "=" + rowid, null) > 0; 19

20 SQLite 데이터베이스 v 예 : Android 폰의 Bookmark 보기 import android.app.activity; import android.os.bundle; import android.provider.browser; import android.widget.textview; import android.database.cursor; public class TestingData extends Activity { /** Called when the activity is first created. public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); setcontentview(r.layout.main); TextView view = (TextView) findviewbyid(r.id.hello); String[] projection = new String[] { Browser.BookmarkColumns.TITLE, Browser.BookmarkColumns.URL ; Cursor mcur = managedquery(android.provider.browser.bookmarks_uri, projection, null, null, null ); mcur.movetofirst(); int titleidx = mcur.getcolumnindex(browser.bookmarkcolumns.title); int urlidx = mcur.getcolumnindex(browser.bookmarkcolumns.url); while (mcur.isafterlast() == false) { view.append("n" + mcur.getstring(titleidx)); view.append("n" + mcur.getstring(urlidx)); mcur.movetonext(); 20

21 SQLite 데이터베이스 v 예 : Mediaplayer 파일정보보기 import android.app.activity; import android.os.bundle; import android.provider.mediastore; import android.provider.mediastore.audio.media; import android.widget.textview; import android.database.cursor; public class TestingData extends Activity { /** Called when the activity is first created. public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); setcontentview(r.layout.main); TextView view = (TextView) findviewbyid(r.id.hello); String[] projection = new String[] { MediaStore.MediaColumns.DISPLAY_NAME, MediaStore.MediaColumns.DATE_ADDED, MediaStore.MediaColumns.MIME_TYPE ; Cursor mcur = managedquery(media.external_content_uri, projection, null, null, null ); mcur.movetofirst(); while (mcur.isafterlast() == false) { for (int i=0; i<mcur.getcolumncount(); i++) { view.append("n" + mcur.getstring(i)); mcur.movetonext(); 21

22 SQLite 데이터베이스라이브러리 v SQLiteOpenHelper 확장하기 상속및데이터베이스생성 ü SQLiteOpenHelper 객체를사용하는게가장쉬운일반적인생성방법 SQLiteOpenHelper 클래스를사용할인스턴스생성 읽기 / 쓰기여부에따라 getreadabledatabase()/getwriteabledatabase() 메소드를호출 예제 private static class mydbhelper extends SQLIteOpenHelper { public mydbhelper(context context, String name, CursorFactory factory, imt version) { super(context, name, factory, version); /* 생성및업그레이드에대한내용 */.. dbhelper = new mydbhelper(context, DATABASE_NAME, null, DATABASEVERSION); SQLiterDatabase d; try { db = dbhelper.getwriteabledatabase(); Catch(SQLiteException ex){ db = dbhelper.getreadabledatabase(); 데이터베이스의쓰기가능한인스턴스얻기 데이터베이스의읽기가능한인스턴스얻기 22

23 과제 ü 게시물첨부 2 파일 (TodoList) 메뉴키를누르다. 2. 화면하단메뉴에 Add New Item 을선택 3. 텍스트를입력하고 4. 선택버큰을누르면리스트에추가 5. 리스트를오래누르거나방향키를움직이면리스트를선택가능 6. 선택후현재삭제기능만존재 7. 여기에수정기능추가 2 23