9일차 SQLite 연동하기(naver 메모장)

- 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.java

package 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.java

package 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가 생성된다.