[모바일프로그래밍] 11-4 SQLite를 활용한 파일 처리

activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <Button
        android:id="@+id/button"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="44dp"
        android:layout_marginTop="48dp"
        android:text="초기화"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/editText2" />

    <Button
        android:id="@+id/button2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="16dp"
        android:layout_marginTop="48dp"
        android:text="레코드 입력"
        app:layout_constraintStart_toEndOf="@+id/button"
        app:layout_constraintTop_toBottomOf="@+id/editText2" />

    <TextView
        android:id="@+id/textView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="76dp"
        android:layout_marginTop="60dp"
        android:text="이름 :"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <EditText
        android:id="@+id/editText"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="40dp"
        android:layout_marginTop="48dp"
        android:ems="10"
        android:inputType="textPersonName"
        android:text="name"
        app:layout_constraintStart_toEndOf="@+id/textView"
        app:layout_constraintTop_toTopOf="parent" />

    <TextView
        android:id="@+id/textView2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="64dp"
        android:layout_marginTop="48dp"
        android:text="전화번호 :"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/textView" />

    <EditText
        android:id="@+id/editText2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="32dp"
        android:layout_marginTop="24dp"
        android:ems="10"
        android:inputType="phone"
        app:layout_constraintStart_toEndOf="@+id/textView2"
        app:layout_constraintTop_toBottomOf="@+id/editText" />

    <Button
        android:id="@+id/button3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginStart="16dp"
        android:layout_marginTop="48dp"
        android:text="조회"
        app:layout_constraintStart_toEndOf="@+id/button2"
        app:layout_constraintTop_toBottomOf="@+id/editText2" />

    <EditText
        android:id="@+id/editText3"
        android:layout_width="127dp"
        android:layout_height="270dp"
        android:layout_marginStart="44dp"
        android:layout_marginTop="20dp"
        android:ems="10"
        android:gravity="start|top"
        android:inputType="textMultiLine"
        android:text="이름"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/button2" />

    <EditText
        android:id="@+id/editText4"
        android:layout_width="201dp"
        android:layout_height="272dp"
        android:layout_marginStart="20dp"
        android:layout_marginTop="20dp"
        android:ems="10"
        android:gravity="start|top"
        android:inputType="textMultiLine"
        android:text="전화번호"
        app:layout_constraintStart_toEndOf="@+id/editText3"
        app:layout_constraintTop_toBottomOf="@+id/button2" />

</androidx.constraintlayout.widget.ConstraintLayout>

 

MainActivity.java

package com.example.myapplication;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;

import androidx.appcompat.app.AppCompatActivity;

public class MainActivity extends AppCompatActivity {

    DBHelper myDBHelper;
    EditText edtName, edtPhone, edtRes1, edtRes2;
    Button initButton, insButton, searchButton;
    SQLiteDatabase mySQLDB;

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

        @Override
        public void onCreate(SQLiteDatabase DB) {
            DB.execSQL("CREATE TABLE contacts(gName CHAR(20) PRIMARY KEY, gphone CHAR(20))");
        }
        @Override
        public void onUpgrade(SQLiteDatabase DB, int oldVersion, int newVersion) {
            DB.execSQL("DROP TABLE IF EXISTS contacts");
            onCreate(DB);
        }
    }

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

        setTitle("데이터베이스 실습");
        edtName = (EditText) findViewById(R.id.editText);
        edtPhone = (EditText) findViewById(R.id.editText2);
        edtRes1 = (EditText) findViewById(R.id.editText3);
        edtRes2 = (EditText) findViewById(R.id.editText4);
        initButton = (Button) findViewById(R.id.button);
        insButton = (Button) findViewById(R.id.button2);
        searchButton = (Button) findViewById(R.id.button3);

        myDBHelper = new DBHelper(this);
        initButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                mySQLDB = myDBHelper.getWritableDatabase();
                myDBHelper.onUpgrade(mySQLDB, 1, 2);
                mySQLDB.close();
            }
        });

        insButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                mySQLDB = myDBHelper.getWritableDatabase();
                mySQLDB.execSQL(" INSERT INTO contacts VALUES ('" + edtName.getText().toString() + "', '" + edtPhone.getText().toString() + "');");
                mySQLDB.close();
            }
        });

        searchButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                mySQLDB = myDBHelper.getReadableDatabase();
                Cursor cursor;
                cursor = mySQLDB.rawQuery(" SELECT * FROM contacts;", null);
                String strname = "name \r\n" + "-------------" + "\r\n";
                String strphone = "phone number" + "\r\n" + "-----------" + "\r\n";
                while(cursor.moveToNext()) {
                    strname += cursor.getString(0) + "\r\n";
                    strphone += cursor.getString(1) + "\r\n";
                }
                edtRes1.setText(strname);
                edtRes2.setText(strphone);
                cursor.close();
                mySQLDB.close();
            }
        });
    }
}

SQL문을 입력하는 부분에서 작은 따옴표(')에 주의해야 한다. 처음에 작은 따옴표를 쓰지 않고 실행했더니 SQL문에서 에러가 발생했다.

왜 저렇게 써야하는지는 모르겠다..

 

이름과 전화번호를 입력한 후 레코드 입력 버튼을 누르면 데이터베이스에 정보가 저장되고 조회 버튼을 누르면 현재 저장되어 있는 정보가 출력된다. 초기화 버튼을 누른 후 조회 버튼을 누르면 레코드가 모두 삭제된 것을 볼 수 있다.