마카로닉스

379 No

[동영상 강좌 ] 블스 - 제 27강 데이터베이스 SQLITE , (insert, select, delete, update) - SQLiteOpenHelper 사용

2017-09-03 18:46:12 By 머나먼나라 0 1454 댓글수 4 포스트 하기

11월 2024
일	월	화	수	목	금	토
					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

 class MainActivity

package com.example.choi.mystudy27_1;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import static android.R.attr.id;


public class MainActivity extends AppCompatActivity {

    final static String TAG="MainActivity";

    MemberInfoHelper memberInfoHelper;
    Button btnInsert, btnDelete, btnUpdate, btnSelect;
    EditText tvResult;

    SQLiteDatabase db2;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        memberInfoHelper=new MemberInfoHelper(this);

        btnInsert=(Button)findViewById(R.id.bt_insert);
        btnDelete=(Button)findViewById(R.id.bt_delete);
        btnUpdate=(Button)findViewById(R.id.update);
        btnSelect=(Button)findViewById(R.id.bt_selet);
        tvResult=(EditText)findViewById(R.id.tv_result);


        btnInsert.setOnClickListener(listener);
        btnDelete.setOnClickListener(listener);
        btnUpdate.setOnClickListener(listener);
        btnSelect.setOnClickListener(listener);



    }



    View.OnClickListener listener =new View.OnClickListener() {

        SQLiteDatabase db;
        ContentValues values;


        @Override
        public void onClick(View v) {

            switch (v.getId()){

                case R.id.bt_insert:
                    try {
                        //데이터 베이스 오픈
                        db=memberInfoHelper.getWritableDatabase();
                       //1.첫번째 방법
                        //Content Value 를 이용한 데이터베이스 insert
                        values=new ContentValues();
                        values.put("uname", "홍길동");
                        values.put("uid", "abc");
                        values.put("upw", 123);
                        db.insert("member", null, values);


                        ///2.번째 방법  : 추천
                        String name="홍길순";
                        String uid="def";
                        int upw=456;
                        String query=
                                String.format("insert into member" +
                                        "(_id, uname, uid, upw ) " +
                                        " values ( null , '%s' , '%s', %d )" ,
                                        name, uid, upw );
                        db.execSQL(query);
                        memberInfoHelper.close();
                        Toast.makeText(MainActivity.this, "Insert OK!", Toast.LENGTH_SHORT).show();

                    }catch (Exception e){
                        e.printStackTrace();
                        Toast.makeText(MainActivity.this, "INSERT NG!", Toast.LENGTH_SHORT).show();
                    }
                break;

                case R.id.bt_delete:

                    try{

                        db  =memberInfoHelper.getWritableDatabase();
                        db.delete("member", null, null);

                        memberInfoHelper.close();
                        Toast.makeText(MainActivity.this, "DELETE OK!", Toast.LENGTH_SHORT).show();



                        SQLiteDatabase db=dbConn();
                        String sql =" delete from product where id="+id;
                        db.execSQL(sql);


                    }catch (Exception e){
                        e.printStackTrace();
                        Toast.makeText(MainActivity.this, "DELETE NG!", Toast.LENGTH_SHORT).show();
                    }

                 break;


                case R.id.bt_selet:
                    //1. 첫번째 방법

                   // sqlSelectOne(db);

                    //2.두번째 방법
                    sqlSelectTwo(db );

                    //edit 읽기 전용 모드
                    tvResult.setFocusableInTouchMode(false);
                    break;


                case R.id.update:
                    try{
                        db=memberInfoHelper.getWritableDatabase();

                        String uname="이순신";
                        String uid="def";
                        String query=String.format(" update member set uname ='%s'  where uid ='%s' "
                                , uname, uid);

                        db.execSQL(query);
                        Toast.makeText(MainActivity.this, "UDATE OK!", Toast.LENGTH_SHORT).show();

                    }catch (Exception e){
                        e.printStackTrace();
                        Toast.makeText(MainActivity.this, "UDATE NG!", Toast.LENGTH_SHORT).show();
                    }
                    break;


            }

        }



    };



    //1.첫번째 방법
    public void sqlSelectOne(SQLiteDatabase db){
        try{
            //데이터베이스 오픈
            db=memberInfoHelper.getReadableDatabase();

            String[] columns={"uname", "uid", "upw"};
            Cursor cursor=db.query("member", columns, null, null, null, null, null);

            StringBuffer sb =new StringBuffer();
            while (cursor.moveToNext()){
                String uname=cursor.getString(0);
                String uid=cursor.getColumnName(1);
                int upw=cursor.getInt(2);
                sb.append("uname :" +uname).append(", uid :" +uid)
                        .append(", upw :"+upw +"\n");
            }
            tvResult.setText(sb.toString());

            cursor.close();
            memberInfoHelper.close();

            Toast.makeText(MainActivity.this, "SELECT OK!", Toast.LENGTH_SHORT).show();


        }catch (Exception e){
            e.printStackTrace();
        }

    }


    //2.첫번째 방법
    public  void sqlSelectTwo(SQLiteDatabase db){


        db=memberInfoHelper.getReadableDatabase();
        Cursor cursor=null;

        try{

            // select * from product 로 * 를 쓰지말고 컬럼이름을 나열 해야 한다.
            // 왜냐 하면 cursor.getInt(0); 컬럼 이름은 사용할 수 없음 때문이다.
            String sql =" select uname, uid, upw from member order by uname";


            // select 쿼리를 실행하여 결과셋을 커서에 리턴함
            // execSQL()  - select  이외의 쿼리
            // rawQuery() - select 쿼리 전용
            // rawQuery(sql , null) - null 자리에 조건 검색 을 쓰면 된다.
            // 그러나 쿼리에 조건절을 써도 된다.
            cursor =db.rawQuery(sql, null);

            StringBuffer sb=new StringBuffer();
            //커서.moveToNext() 다음 레코드가 존재하면 true 리턴
            while(cursor.moveToNext()){
                //커서.get자료형(컬럼의 인텍스) 0 부터, 컬럼 이름은 사용할 수 없음
                String uname=cursor.getString(0);
                String uid=cursor.getColumnName(1);
                int upw=cursor.getInt(2);
                sb.append("uname :" +uname).append(", uid :" +uid)
                        .append(", upw :"+upw +"\n");

            }

            tvResult.setText(sb.toString());

            Toast.makeText(MainActivity.this, "SELECT OK!", Toast.LENGTH_SHORT).show();
        }catch (Exception e){
            e.printStackTrace();
            Toast.makeText(MainActivity.this, "SELECT NG!", Toast.LENGTH_SHORT).show();

        }finally {
            cursor.close();
            memberInfoHelper.close();

        }

    }




    //  Context.MODE_PRIVATE : 단독 사용 모드
    public SQLiteDatabase dbConn(){
        //데이터베이스를 오픈하거나 생성
        Context context =MainActivity.this;


        db2=context.openOrCreateDatabase("MemberInfo.db", Context.MODE_PRIVATE, null);

        //테이블이 존재하지 않으면  create
        // 자동증가컬럼은 자료형을 integer 로 하고 primary key 로 설정
        Log.v("메시지",  "dbConn() start");
        try{

            String sql=
                    "create table member (_id INTEGER PRIMARY KEY autoincrement,\n" +
                            "       uname TEXT,\n" +
                            "       uid TEXT,\n" +
                            "       upw INTEGER)";
            db2.execSQL(sql);  // select 이외의 쿼리

        }catch (Exception e){
            e.printStackTrace();
            Log.v("메시지",  "dbConn() execSQL : " + e.getMessage());
        }

        return db2;
    }


}

R.layout.activity_main

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/activity_main"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context="com.example.choi.mystudy27_1.MainActivity">

    <!--읽기전용-->
    <EditText
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:inputType="textMultiLine"
        android:ems="10"
        android:layout_alignParentTop="true"
        android:layout_centerHorizontal="true"
        android:id="@+id/tv_result"
        android:layout_weight="1"
        android:focusableInTouchMode="false"
        />

    <LinearLayout
        android:orientation="vertical"
        android:layout_width="match_parent"
        android:layout_height="0dp"
        android:layout_weight="1"
        >

        <Button
            android:text="INSERT"
            android:layout_width="match_parent"
            android:layout_height="0dp"
            android:id="@+id/bt_insert"
            android:background="@android:color/holo_blue_dark"
            android:textColor="@android:color/background_light"
            android:layout_weight="1"
             />

        <Button
            android:text="SELECT"
            android:layout_width="match_parent"
            android:layout_height="0dp"
            android:id="@+id/bt_selet"
            android:background="@android:color/holo_red_dark"
            android:textColor="@android:color/background_light"
            android:layout_weight="1"
             />


        <Button
            android:text="update"
            android:layout_width="match_parent"
            android:layout_height="0dp"
            android:id="@+id/update"
            android:background="@android:color/holo_orange_dark"
            android:textColor="@android:color/background_light"
            android:layout_weight="1"
            />

        <Button
            android:text="DELETE"
            android:layout_width="match_parent"
            android:layout_height="0dp"
            android:id="@+id/bt_delete"
            android:background="@android:color/holo_green_dark"
            android:textColor="@android:color/background_light"
            android:layout_weight="1"

             />


    </LinearLayout>


</LinearLayout>

class MemberInfoHelper

package com.example.choi.mystudy27_1;

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

/**
 * Created by choi on 2017-03-15.
 */

public class MemberInfoHelper extends SQLiteOpenHelper {

    final static String TAG="MemberInfoHelper";

    public MemberInfoHelper(Context context){
        // 1 - 버전명
        //데이터베이스 MemberInfo.db - DB 명
        super(context, "MemberInfo.db", null, 1);
    }

    public MemberInfoHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.i(TAG, "onCreate()");
        String query=
                "create table member (_id INTEGER PRIMARY KEY autoincrement,\n" +
                        "       uname TEXT,\n" +
                        "       uid TEXT,\n" +
                        "       upw INTEGER)";
        db.execSQL(query);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        Log.i(TAG, "onUpgrade()");

        //테이블이 존재하면 삭제
        String query ="DROP table if exits member ";
        db.execSQL(query);

        //호출
        onCreate(db);
    }




}