- DataBase
1. 연다 2. 넣는다 3. 닫는다
1. 문연다. 2. 나간다. 3. 문 닫는다.
파일 입출력
1. 인풋스트림을 얻는다. 2. 읽는다. 3. 닫는다.
- 자바 JDBC - 1. 드라이버을 로딩한다.
2. 연결한다. (Connection)
3. 데이터를 주고 받는다.
- 안드로이드 SQLite - SQLiteOpenHelper class
SQLite는 Oracle이나 MS-SQL 처럼 무겁지 않으며, 아주 가볍고 빠르고, 간결한 db 엔진이다.
| | |
| DDMS -> File Explorer -> data -> data -> 응용 패키지 이름 -> databases -> 이 곳에 위치 예) data/data/com.android.email/databases/EmailProvider.db | |
| | |
1. ADB Shell 띄우고, SQLite3 실행 및 데이터베이스 연결하기
sqlite> 이 곳에서 테이블 생성, 데이타 수정, 삽입, 삭제 등이 가능하다.
| | |
| [ADB Shell 띄우기] C:\Documents and Settings\XNOTE> adb -s emulator-5554 shell
(에뮬레이터를 띄운 상태라면)adb shell
# sqlite3 /data/data/com.android.email/databases/EmailProvider.db
sqlite3 /data/data/com.android.email/databases/EmailProvider.db
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .help
.help
.bail ON|OFF Stop after hitting an error. Default OFF
.databases List names and files of attached databases
.dump ?TABLE? ... Dump the database in an SQL text format
.echo ON|OFF Turn command echo on or off
.................... (생략) [SQLite 명령 프롬프트 나가기] sqlite> .quit sqlite> .exit [사용가능한 DB 확인] sqlite> .databases
.databases
seq name file
--- --------------- ----------------------------------------------------------
0 main /data/data/com.android.email/databases/EmailProvider.db [사용가능한 테이블 확인] sqlite> .tables
.tables
Account HostAuth Message Message_Updates
Attachment Mailbox Message_Deletes android_metadata [DB 스키마와 자료 Export] sqlite> .output /data/local/tmp/dump.sql
.output /data/local/tmp/dump.sql -  Tip! local/temp 폴더는 읽고, 쓰기가 가능한 디렉토리이다. [SQL 스크립트 덤프하기] 테이블 이름을 지정하면 해당 테이블의 스크립트만 생성. 지정하지 않으면 전체 db sql 스크립트 생성 sqlite> .output /data/local/tmp/dump.sql
.output /data/local/tmp/dump.sql
sqlite> .dump Account
.dump Account
sqlite> .output stdout
.output stdout | |
| | |
- 아래 그림과 같이 local/tmp/dump.sql 파일에 SQL 스크립트가 생성
main.xml<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="vertical" >
<Button
android:id="@+id/button1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="right"
android:text="메모 추가하기" />
<ListView
android:id="@+id/listView1"
android:layout_width="match_parent"
android:layout_height="wrap_content" >
</ListView>
</LinearLayout>
sub.xml<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical" >
<Button
android:id="@+id/button1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="right"
android:text="뒤로가기" />
<EditText
android:id="@+id/editText1"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="1"
android:ems="10" >
<requestFocus />
</EditText>
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:orientation="horizontal">
<Button
android:id="@+id/button3"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="수정/입력" />
<Button
android:id="@+id/button2"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="취소" />
<Button
android:id="@+id/button4"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="1"
android:text="지우기" />
</LinearLayout>
</LinearLayout>
manifast.xml<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
package="com.gusfree.sqlite"
android:versionCode="1"
android:versionName="1.0" >
<uses-sdk android:minSdkVersion="8" />
<application
android:icon="@drawable/ic_launcher"
android:label="@string/app_name" >
<activity
android:name=".SQLiteActivity"
android:label="@string/app_name" >
<intent-filter>
<action android:name="android.intent.action.MAIN" />
<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
<!-- 등록하지 않으면 Activity not found error -->
<activity android:name=".SubActivity"></activity>
</application>
</manifest>
item.xml<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="horizontal" >
<TextView
android:id="@+id/textView1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="TextView" />
<TextView
android:id="@+id/textView2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="TextView" />
<TextView
android:id="@+id/textView3"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="TextView" />
</LinearLayout>
SQLiteActivity.javapackage com.gusfree.sqlite;
import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.Toast;
public class SQLiteActivity extends Activity {
ListView listView;
Helper helper;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
listView=(ListView)findViewById(R.id.listView1);
helper=new Helper(getApplicationContext(),"memojang.db", null, 1);
helper.open();
findViewById(R.id.button1).setOnClickListener(
new OnClickListener(){
@Override
public void onClick(View arg0) {
Intent intent=new Intent();
intent.setClass(getApplicationContext(),SubActivity.class);
startActivity(intent); //이동
}
});
}
@Override //다른 액티비티를 갖다오면 실행된다
protected void onResume() {
super.onResume();
//cursor안에 정보가 담겨있다.
Cursor cursor=helper.selectAll();
//커서안의 정보를 한줄씩 빼서 item.xml에 셋팅하고
//결국 listView전부 보여지도록
if(cursor!=null){
String[] from={"_id","memo","time"};
int[] to={R.id.textView1,R.id.textView2,R.id.textView3};
SimpleCursorAdapter adapter=
new SimpleCursorAdapter(this, R.layout.item, cursor, from, to);
listView.setAdapter(adapter);
listView.setOnItemClickListener(new OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> arg0, View arg1, int postion,
long arg3) {
Cursor cursor=helper.select(postion+1);
Intent intent = new Intent();
intent.setClass(SQLiteActivity.this, SubActivity.class);
if(cursor.moveToNext()){
String id =cursor.getString(0);
String memo =cursor.getString(1);
String time =cursor.getString(2);
intent.putExtra("id", id);
intent.putExtra("memo", memo);
intent.putExtra("time", time);
Log.w("커서 읽기","memo= "+memo);
}
cursor.close();
startActivity(intent);
}
});
}else Toast.makeText(this, "데이터가 없습니다.", 0).show();
}
}
SubActivity.java
package com.gusfree.sqlite;
import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.EditText;
public class SubActivity extends Activity implements android.view.View.OnClickListener{
EditText editText;
Helper helper;
Intent theIntent;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.sub);
editText =(EditText) findViewById(R.id.editText1);
findViewById(R.id.button1).setOnClickListener(this);
findViewById(R.id.button2).setOnClickListener(this);
findViewById(R.id.button3).setOnClickListener(this);
findViewById(R.id.button4).setOnClickListener(this);
helper =
new Helper(getApplicationContext(), "memojang.db", null, 1);
helper.open();
theIntent= getIntent();
String memo =theIntent.getStringExtra("memo");
if(memo!=null){
editText.setText(memo);
}
}
@Override
public void onClick(View v) {
switch(v.getId()){
case R.id.button1 ://뒤로가기
finish();//activity를 종료시켜라
break;
case R.id.button2 ://입력&수정
String id =theIntent.getStringExtra("id");
Log.e("id","id= "+id);
if(id==null){//새글쓰기
String memo=editText.getText().toString().trim();
helper.insert(memo);
}else{
//글수정 - 업데이트
String memo =editText.getText().toString().trim();
helper.update(id,memo);
}
break;
case R.id.button3 ://취소
finish();
break;
case R.id.button4 ://지우기
id=theIntent.getStringExtra("id");
helper.delete(id);
break;
}
}
}
Helper.javapackage com.gusfree.sqlite;
import java.text.SimpleDateFormat;
import java.util.Date;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class Helper extends SQLiteOpenHelper{
SQLiteDatabase myDb;
String table="memojang";
public Helper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
String sql=
"CREATE TABLE memojang (_id INTEGER PRIMARY KEY AUTOINCREMENT,memo TEXT, time TEXT); ";
db.execSQL(sql);
}
public void open(){
// 읽고 쓸 수 있는 db 얻기
myDb= this.getWritableDatabase();
//this.getReadableDatabase(); //읽을 수만 있는
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
public void insert(String memo) { //db에 insert시키기
SimpleDateFormat sdf = new SimpleDateFormat("hh:mm");
String time = sdf.format(new Date());
String sql="INSERT INTO memojang (memo, time) values (?, ?);";
// ? ,? 에 해당하는 값을 넣는다
Object[] bindArgs={memo, time } ;
myDb.execSQL(sql, bindArgs);
Log.i("insert","insertOK");
/*Calendar cal=Calendar.getInstance();
int month=cal.get(Calendar.DAY_OF_MONTH);
int hour=cal.get(Calendar.HOUR_OF_DAY);
int minute=cal.get(Calendar.MINUTE);*/
/* 비추천 */
/*String sql="INSERT INTO memojang "
+"(memo, time) values ("
+memo + ", "+ time
+") ;";
myDb.execSQL(sql);*/
}
public Cursor selectAll(){
//1. 테이블이름 2. 원하는 컬럼명 3. 원하는 조건
// 4. 조건에 해당하는 값 5. groupBy 6. Having 7. 정렬
/*myDb.query( "memojang", new String[]{"memo","time"},
"_id<?", new String[]{10},null,null,null); */
Cursor cursor=
myDb.query( "memojang", null,null,null,null,null,null);
return cursor;
}
public Cursor select(int no){
Cursor cursor =
myDb.query("memojang", null, "_id=?",
new String[]{String.valueOf(no)},//int->String
null,null,null);
return cursor;
}
public void update(String id, String newMemo) {
ContentValues values=new ContentValues();
// "memo"컬럼의 내용을 newMemo값으로 바꾸겠다
values.put("memo", newMemo);
String[] whereArgs={id};
int update=myDb.update("memojang", values,"_id=?", whereArgs);
Log.i("수정","수정된 row수는 ? "+ update);
}
public void delete(String id) {
//myDb.delete(table, "_id=?", new String[]{id});
//String sql="DELETE FROM memojang where _id=?"+id;
//myDb.execSQL(sql);
String sql="DELETE FROM memojang where _id=?";
myDb.execSQL(sql,new String[]{id});
}
}
실행하면 memojang.db가 생성된다.
