Android Studio - SQLite 활용하기

2022-08-24

---

📄상품추가 버튼을 클릭한 후 [상품명, 가격, 판매자, 판매자연락처] 데이터를 입력하고 OK버튼을 누르면,

ListView에 상품명과 판매자가 출력되게 하시오.

---

---

📝activity_main.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"
    android:padding="20dp"
    tools:context=".MainActivity">

    <Button
        android:id="@+id/btn_add"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:backgroundTint="#5b4fff"
        android:text="상품추가"
        android:textSize="25sp" />

    <ListView
        android:id="@+id/lv"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:layout_marginTop="30dp" />


</LinearLayout>

---

📝activity_input.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"
    android:padding="20dp"
    tools:context=".InputActivity">

    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="상품명"
            android:textSize="30sp" />

        <EditText
            android:id="@+id/et_name"
            android:layout_width="300dp"
            android:layout_height="wrap_content"
            android:textSize="20sp" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="가격"
            android:textSize="30sp" />

        <EditText
            android:id="@+id/et_price"
            android:layout_width="300dp"
            android:layout_height="wrap_content"
            android:textSize="20sp" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="판매자"
            android:textSize="30sp" />

        <EditText
            android:id="@+id/et_seller"
            android:layout_width="300dp"
            android:layout_height="wrap_content"
            android:textSize="20sp" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="판매자연락처"
            android:textSize="30sp" />

        <EditText
            android:id="@+id/et_phone"
            android:layout_width="200dp"
            android:layout_height="wrap_content"
            android:textSize="20sp" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <Button
            android:id="@+id/btn_ok"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_margin="10dp"
            android:text="OK"
            android:textSize="20sp" />

        <Button
            android:id="@+id/btn_cancel"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_margin="10dp"
            android:layout_marginLeft="15dp"
            android:text="Cancel"
            android:textSize="20sp" />

    </LinearLayout>

</LinearLayout>

---

📝MainActivity.java

package com.example.quiz;

import androidx.annotation.Nullable;
import androidx.appcompat.app.AppCompatActivity;

import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.preference.PreferenceManager;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

import java.util.Objects;

public class MainActivity extends AppCompatActivity {
    MySQLHelper mydb;
    SQLiteDatabase mdb;
    Cursor cursor;

    ListView lv;
    SimpleCursorAdapter ca;

    static final int RQCODE_UPDATE = 1;
    static final int RQCODE_INSERT = 2;

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

        mydb = new MySQLHelper(this, "product.db", null, 1);
        mdb = mydb.getWritableDatabase();
        cursor = mdb.rawQuery("SELECT * FROM product", null);

        String strCol[] = {"name", "seller"};
        lv = (ListView) findViewById(R.id.lv);
        ca = new SimpleCursorAdapter(this, android.R.layout.simple_expandable_list_item_2,
                cursor, strCol, new int[]{android.R.id.text1, android.R.id.text2}, 1);
        lv.setAdapter(ca);

        lv.setOnItemClickListener(lis);

        findViewById(R.id.btn_add).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Intent i = new Intent(MainActivity.this, InputActivity.class);
                startActivityForResult(i, RQCODE_INSERT);
            }
        });
    }

    AdapterView.OnItemClickListener lis = new AdapterView.OnItemClickListener() {
        @Override
        public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
            cursor.moveToPosition(position);
            int _id = cursor.getInt(0);

            String name = cursor.getString(1); // name
            String price = cursor.getString(2); // name
            String seller = cursor.getString(3); // seller
            String phone = cursor.getString(4); // phone

            Intent i = new Intent(MainActivity.this, InputActivity.class);
            i.putExtra("_id", _id);
            i.putExtra("name", name);
            i.putExtra("price", price);
            i.putExtra("seller", seller);
            i.putExtra("phone", phone);
            startActivityForResult(i, RQCODE_UPDATE);
        }
    };

    @Override
    protected void onActivityResult(int requestCode, int resultCode, @Nullable Intent data) {
        super.onActivityResult(requestCode, resultCode, data);

        if(resultCode == RESULT_OK && data != null) {
            String name = data.getStringExtra("name");
            String price = data.getStringExtra("price");
            String seller = data.getStringExtra("seller");
            String phone = data.getStringExtra("phone");

            if(requestCode == RQCODE_INSERT) {
                mdb.execSQL("INSERT INTO product VALUES (null, '" + name + "', '" + price + "', '" +
                        seller + "', '" + phone + "');" );
                Log.i("jyeon", "추가완료");
            }
        }
        cursor.requery();
    }
}

---

📝InputActivity.java

package com.example.quiz;

import androidx.appcompat.app.AppCompatActivity;

import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;

public class InputActivity extends AppCompatActivity {
    EditText et_name;
    EditText et_price;
    EditText et_seller;
    EditText et_phone;

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

        findViewById(R.id.btn_ok).setOnClickListener(lis);
        findViewById(R.id.btn_cancel).setOnClickListener(lis);

        et_name = findViewById(R.id.et_name);
        et_price = findViewById(R.id.et_price);
        et_seller = findViewById(R.id.et_seller);
        et_phone = findViewById(R.id.et_phone);

        Intent i = getIntent();
        String name = i.getStringExtra("name");
        String price = i.getStringExtra("price");
        String seller = i.getStringExtra("seller");
        String phone = i.getStringExtra("phone");

        if (name != null && price != null && seller != null && phone != null) {
            if (name.length() > 0 && seller.length() > 0) {
                et_name.setText(name);
                et_price.setText(price);
                et_seller.setText(seller);
                et_phone.setText(phone);
            }
        }
    }

    View.OnClickListener lis = new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            int id = v.getId();
            switch (id) {
                case R.id.btn_ok:
                    ok();
                    break;
                case R.id.btn_cancel:
                    cancel();
                    break;
            }
        }
    };

    public void ok() {
        Intent i = getIntent();
        i.putExtra("name", et_name.getText().toString());
        i.putExtra("price", et_price.getText().toString());
        i.putExtra("seller", et_seller.getText().toString());
        i.putExtra("phone", et_phone.getText().toString());

        setResult(RESULT_OK, i);
        finish();
    }

    public void cancel() {
        Intent i = new Intent(InputActivity.this, MainActivity.class);
        startActivity(i);
    }
}

 

---

📝MySQLHelper.java

package com.example.quiz;

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

import androidx.annotation.Nullable;

public class MySQLHelper extends SQLiteOpenHelper {
    public MySQLHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE product (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "name TEXT, price INTEGER, seller TEXT, phone TEXT);");

        db.execSQL("INSERT INTO product VALUES (null, '훈제연어', '12000원', '홍길동', '1234');");
    }

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

    }
}

---