Android Studio - ListView와 SQLite

2022-08-24

---

📄 ListView와 SQLite를 사용하는 예제

◾ 정답코드 파일

 

InputActivity.java

0.00MB

MainActivity.java

0.00MB

MySQLiteOpenHelper.java

0.00MB

activity_main.xml

0.00MB

atvitivy_item.xml

0.00MB

---

데이터베이스를 삭제하고 싶은 경우는 다음 경로를 따라 databases 안의 mydb.db파일을 삭제한다.

---

◾ ListView의 데이터를 수정할 경우

---

◾ 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"
    tools:context=".MainActivity"
    android:orientation="vertical">

    <Button
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:textSize="25sp"
        android:text="데이터 추가"
        android:onClick="onClick"/>

    <ListView
        android:id="@+id/list"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"/>

</LinearLayout>

---

📝atvitivy_item.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">
    <EditText
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/et_country"
        />
    <EditText
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/et_capital"
        />
    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:id="@+id/button"
        android:onClick="onInputClick"
        android:text="ok"
        />

</LinearLayout>

---

📝MySQLHelper.java

package com.example.listview;

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 world (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "country TEXT, capital TEXT);");

        db.execSQL("INSERT INTO world VALUES (null, '한국', '서울');");
        db.execSQL("INSERT INTO world VALUES (null, '중국', '베이징');");
        db.execSQL("INSERT INTO world VALUES (null, '일본', '도쿄');");
    }

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

    }
}

---

📝MainActivity.java

package com.example.listview;

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.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, "mydb2.db", null, 1);
        mdb = mydb.getWritableDatabase();
        cursor = mdb.rawQuery("SELECT * FROM world", null);

        String strCol[] = {"country", "capital"};
        lv = (ListView) findViewById(R.id.list);
        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);
    }

    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 str1 = cursor.getString(1);
            String str2 = cursor.getString(2);

            Intent i = new Intent(MainActivity.this, InputActivity.class);
            i.putExtra("_id", _id);
            i.putExtra("country", str1);
            i.putExtra("capital", str2);
            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) {
            int id = data.getIntExtra("_id", -1);
            // key값 (country, capital)을 가져온다.
            String str1 = data.getStringExtra("country");
            String str2 = data.getStringExtra("capital");

            // 2번째 액티비티에서 OK버튼이 눌리면 "수정완료" Log를 표시한다.
            if(requestCode == RQCODE_INSERT) {
                mdb.execSQL("INSERT INTO world VALUES (null, '" + str1 + "', '" + str2 + "');" );
                Log.i("jyeon", "수정완료");
            }
            // 2번째 액티비티에서 OK버튼이 눌리면 "추가완료" Log를 표시한다.
            else if(requestCode == RQCODE_UPDATE) {
                mdb.execSQL("UPDATE world SET country='" + str1 + "', capital = '" + str2 + "' WHERE _id=" + id + ";");
                Log.i("jyeon", "추가완료");
            }

            cursor.requery();
        }
    }
}

---

📝InputActivity.java

package com.example.listview;

import androidx.appcompat.app.AppCompatActivity;

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

public class InputActivity extends AppCompatActivity {

    EditText et_country;
    EditText et_capital;

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

        et_country = findViewById(R.id.et_country);
        et_capital = findViewById(R.id.et_capital);


        Intent i = getIntent();
        String str1 = i.getStringExtra("country");
        String str2 = i.getStringExtra("capital");
        if(str1 != null && str2 != null && str1.length() > 0 && str2.length() > 0) {
            et_country.setText(str1);
            et_capital.setText(str2);
        }

        findViewById(R.id.button).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                Intent i = getIntent();
                i.putExtra("country", et_country.getText().toString());
                i.putExtra("capital", et_capital.getText().toString());

                // OK버튼이 눌렸을 때, RESULT_OK로 인텐트를 세팅한다.
                setResult(RESULT_OK, i);
                finish();
            }
        });
    }
}

---