안드로이드 Android DB SQLite example

안드로이드 Android DB SQLite example

SQLiteOpenHelper 이용한 예제입니다.

XML 을 보시면 open , insert, select, update, delete 가있습니다. 각 버튼을 클릭 시 예제를 간단하게 들었습니다. 보시고 쉽게 응용하시면 될 거 같습니다.


MainActivity.java

package com.example.bj.test05;

import android.database.Cursor;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;
import android.widget.Button;

public class MainActivity extends AppCompatActivity implements View.OnClickListener {
    GGARI_DBAdapter ggari_db;
    Button btnOpen, btnInsert, btnSelect, btnUpdate, btnDelete;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        btnOpen = (Button) findViewById(R.id.btnOpen);

        btnInsert = (Button) findViewById(R.id.btnInsert);
        btnSelect = (Button) findViewById(R.id.btnSelect);
        btnUpdate = (Button) findViewById(R.id.btnUpdate);
        btnDelete = (Button) findViewById(R.id.btnDelete);

        btnOpen.setOnClickListener(this);
        btnInsert.setOnClickListener(this);
        btnSelect.setOnClickListener(this);
        btnUpdate.setOnClickListener(this);
        btnDelete.setOnClickListener(this);

        ggari_db = new GGARI_DBAdapter(this);

    }

    @Override
    public void onClick(View v) {
        switch (v.getId()) {
            //오픈
            case R.id.btnOpen:
                ggari_db.open();
                break;
            //삽입
            case R.id.btnInsert:
                ggari_db.insert(20,"01012345678","seoul","ggari");
                break;
            //조회
            case R.id.btnSelect:
                Cursor all_cursor = ggari_db.AllRows();
                all_cursor.moveToFirst();
                if(all_cursor.getCount() ==0) {
                    Log.d("TEST","아무것도없다.");
                    return;
                }else{
                    Log.d("TEST", "id=" + all_cursor.getString(all_cursor.getColumnIndex("ID")));
                    Log.d("TEST", "AGE=" + all_cursor.getString(all_cursor.getColumnIndex("AGE")));
                    Log.d("TEST", "PHONE=" + all_cursor.getString(all_cursor.getColumnIndex("PHONE")));
                    Log.d("TEST", "ADDR=" + all_cursor.getString(all_cursor.getColumnIndex("ADDR")));
                    Log.d("TEST", "NAME=" + all_cursor.getString(all_cursor.getColumnIndex("NAME")));
                }
                break;
            //업데이트
            case R.id.btnUpdate:
                ggari_db.update("1",30,"01011112222","busan","rigga");
                break;
            //삭제
            case R.id.btnDelete:
                ggari_db.deleteAll();
                break;


        }
    }
}

GGARI_DBAdapter.java

package com.example.bj.test05;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Created by bj on 2015-09-08.
 */
public class GGARI_DBAdapter {


    private static final String DATABASE_NAME = "ggariDB";
    private static final String DATABASE_TABLE = "TB_GGARI";
    private static final int DATABASE_VERSION = 1;
    private final Context mCtx;

    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    //생성테이블
    private static final String DATABASE_CREATE="CREATE TABLE " +DATABASE_TABLE+ "" +
            " (    ID    INTEGER PRIMARY    KEY AUTOINCREMENT, AGE INTEGER,   PHONE TEXT,    ADDR TEXT,    NAME TEXT)";

    //테이블  드랍
/*
   private static final String SQL_DELETE_TABLE =
            "DROP TABLE IF EXISTS " + DATABASE_NAME;
*/

    public static class DatabaseHelper extends SQLiteOpenHelper {

         DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            //데이터베이스 최초 생성될때 실행 디비가 생성될때 실행된다
            Log.d("TEST","onCreate DATABSE_CREATE");
            db.execSQL(DATABASE_CREATE);
        }

        /**
         *
         * @param db         The database.
         * @param oldVersion The old database version.
         * @param newVersion The new database version.
         */
        @Override
        //데이터베이스가 업그레이드가 필요할때
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
           // db.execSQL( SQL_DELETE_TABLE);
        }





    }

    //
    public void open() throws SQLException {

        mDbHelper = new DatabaseHelper(mCtx);
/*
        DB가 없다면 onCreate가 호출 후 생성, version이 바뀌었다면 onUpgrade 메소드 호출
        mDb = mDbHelper.getWritableDatabase();
*/
        // 권한부여 읽고 쓰기를 위해
        mDb = mDbHelper.getWritableDatabase();
        Log.d("TEST","open");
    }

    public GGARI_DBAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    //닫기
    public void close() {
        mDbHelper.close();
    }


    //넣기
    public long insert(int age,  String phone, String addr, String name ) {
        ContentValues insertValues = new ContentValues();
        Log.d("TEST",phone);
        Log.d("TEST",addr);
        Log.d("TEST",name);



        insertValues.put("AGE", age);
        insertValues.put("PHONE", phone);
        insertValues.put("ADDR", addr);
        insertValues.put("NAME", name);
        Log.d("TEST","insert suc");
        return mDb.insert(DATABASE_TABLE, null, insertValues);
    }
    //업데이트~
    public long update(String id, int age,  String phone, String addr, String name) {
        ContentValues updateValues = new ContentValues();
        updateValues.put("AGE", age);
        updateValues.put("PHONE", phone);
        updateValues.put("ADDR", addr);
        updateValues.put("NAME", name);
        return mDb.update(DATABASE_TABLE, updateValues, "ID" + "=?", new String[]{id});
    }

    //한개씩삭제
    public boolean deleteRow(String id) {
        return mDb.delete(DATABASE_TABLE, "ID" + "=?", new String[]{id}) > 0;

    }

    //다삭제
    public boolean deleteAll() {
        return mDb.delete(DATABASE_TABLE, null, null) > 0;
    }

    public Cursor AllRows() {
        return mDb.query(DATABASE_TABLE, null, null, null, null, null, null);

    }


}

layout- activity_main.xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"

              xmlns:tools="http://schemas.android.com/tools"
              android:layout_width="match_parent"
              android:layout_height="match_parent"
              tools:context=".MainActivity">

    <Button
        android:layout_weight="1"
        android:id="@+id/btnOpen"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="open"/>

    <Button
        android:layout_weight="1"
        android:id="@+id/btnInsert"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="insert"/>

    <Button
        android:layout_weight="1"
        android:id="@+id/btnSelect"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="select"/>

    <Button
        android:layout_weight="1"
        android:id="@+id/btnUpdate"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="update"/>

    <Button
        android:layout_weight="1"
        android:id="@+id/btnDelete"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="delete"/>
</LinearLayout>

LOG CAT 화면 스크린샷 입니다~

open

insert


select

update

delete -> select

끝~