2018_05_25 sqlite 연동, 초기화, 입력, 조회

xml소스

<?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:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="0dip"
        android:layout_weight="1"
        android:orientation="horizontal" >

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="이름 : "
            android:textSize="20dp" />

        <EditText
            android:id="@+id/edtName"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="0dip"
        android:layout_weight="1"
        android:orientation="horizontal" >

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="인원 : "
            android:textSize="20dp" />

        <EditText
            android:id="@+id/edtNumber"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1" >
        </EditText>
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="0dip"
        android:layout_weight="1"
        android:orientation="horizontal" >

        <Button
            android:id="@+id/btnInit"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="초기화" />

        <Button
            android:id="@+id/btnInsert"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="입력" />

        <Button
            android:id="@+id/btnSelect"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="조회" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="0dip"
        android:layout_weight="8"
        android:orientation="horizontal" >

        <EditText
            android:id="@+id/edtNameResult"
            android:layout_width="wrap_content"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:background="#00FF00"
            android:padding="20dp" />

        <EditText
            android:id="@+id/edtNumberResult"
            android:layout_width="wrap_content"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:background="#00FF00"
            android:padding="20dp" />
    </LinearLayout>

</LinearLayout>

java소스

package com.cookandroid.day13_ryan;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabaseLockedException;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.PersistableBundle;
import android.support.annotation.Nullable;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class MainActivity extends AppCompatActivity {

    EditText edtName, edtNumber, edtNmaeResult,edtNumberResult;
    Button btn1, btn2, btn3;
    MyDBHelper myDBHelper;
    SQLiteDatabase sqlLiteDatabase;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        edtName =(EditText)findViewById(R.id.edtName);
        edtNumber=(EditText)findViewById(R.id.edtNumber);
        edtNmaeResult=(EditText)findViewById(R.id.edtNameResult);
        edtNumberResult=(EditText)findViewById(R.id.edtNumberResult);
        btn1 = (Button)findViewById(R.id.btnInit);
        btn2 = (Button)findViewById(R.id.btnInsert);
        btn3 = (Button)findViewById(R.id.btnSelect);

        myDBHelper = new MyDBHelper(this);

        //초기화
        btn1.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                sqlLiteDatabase =myDBHelper.getWritableDatabase();
                myDBHelper.onUpgrade(sqlLiteDatabase,1,2);
                Cursor cursor;
                cursor =sqlLiteDatabase.rawQuery("select * from groupTB;",null);
                String strNames ="";
                String strNumbers ="";
                edtNmaeResult.setText(strNames);
                edtNumberResult.setText(strNumbers);

                cursor.close();
                sqlLiteDatabase.close();
            }
        });


        //입력
        btn2.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                sqlLiteDatabase=myDBHelper.getWritableDatabase();
                sqlLiteDatabase.execSQL("insert into groupTB values('"+edtName.getText().toString()+"',"+edtNumber.getText().toString()+");");
                Cursor cursor;
                cursor =sqlLiteDatabase.rawQuery("select * from groupTB;",null);

                String strNames ="그룹이름"+ "\r\n" + "-------"+"\r\n";
                String strNumbers ="인원"+ "\r\n" + "-------"+"\r\n";

                while (cursor.moveToNext()){
                    strNames += cursor.getString(0)+"\r\n";
                    strNumbers += cursor.getString(1)+"\r\n";
                }

                edtNmaeResult.setText(strNames);
                edtNumberResult.setText(strNumbers);

                cursor.close();
                sqlLiteDatabase.close();
                Toast.makeText(getApplicationContext(),"입력됨",Toast.LENGTH_LONG).show();
            }
        });


        //조회
        btn3.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                sqlLiteDatabase=myDBHelper.getReadableDatabase();
                Cursor cursor;
                cursor =sqlLiteDatabase.rawQuery("select * from groupTB;",null);

                String strNames ="그룹이름"+ "\r\n" + "-------"+"\r\n";
                String strNumbers ="인원"+ "\r\n" + "-------"+"\r\n";

                while (cursor.moveToNext()){
                    strNames += cursor.getString(0)+"\r\n";
                    strNumbers += cursor.getString(1)+"\r\n";
                }

                edtNmaeResult.setText(strNames);
                edtNumberResult.setText(strNumbers);

                cursor.close();
                sqlLiteDatabase.close();
            }
        });

    }
    class MyDBHelper extends SQLiteOpenHelper{
        public MyDBHelper(Context context){
            super(context, "groupDB", null,1);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL("create table groupTB(game char(20), number integer);");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("drop table if exists groupTB");
            onCreate(db);
        }

    }

}