sqlite 사용(CRUD)

1. 개요

    * sqlite 사용기

    * SQLiteOpenHelper를 이용함.

    * activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout 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"
    android:orientation="vertical"
    tools:context=".MainActivity" >

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">
        <TextView
            android:id="@+id/lblDBnm"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="DB명" />
        <EditText
            android:id="@+id/edtDBnm"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:ems="10"
            android:inputType="textPersonName"
            android:text="" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">
        <TextView
            android:id="@+id/lbltablenm"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="테이블명" />
        <EditText
            android:id="@+id/edttablenm"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:ems="10"
            android:inputType="textPersonName"
            android:text="" />
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">
        <TextView
            android:id="@+id/lblin0"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="ID" />
        <EditText
            android:id="@+id/edtin0"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:ems="10"
            android:inputType="textPersonName"
            android:text="" />
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">
        <TextView
            android:id="@+id/lblin1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="입력1" />
        <EditText
            android:id="@+id/edtin1"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:ems="10"
            android:inputType="textPersonName"
            android:text="" />
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">
        <TextView
            android:id="@+id/lblin2"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="입력2" />
        <EditText
            android:id="@+id/edtin2"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:ems="10"
            android:inputType="textPersonName"
            android:text="" />
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">
        <TextView
            android:id="@+id/lblin3"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:text="입력3" />

        <EditText
            android:id="@+id/edtin3"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:ems="10"
            android:inputType="textPersonName"
            android:text="" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="234dp"
        android:orientation="vertical">
        <Button
            android:id="@+id/btnmkdb"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="DB 생성" />
        <Button
            android:id="@+id/btnmkTable"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Table 생성" />
        <Button
            android:id="@+id/btninput"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="입력" />
        <Button
            android:id="@+id/btnget"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="가져오기" />
        <Button
            android:id="@+id/btnmod"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="수정" />
        <Button
            android:id="@+id/btndel"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="삭제" />
    </LinearLayout>

    <EditText
        android:id="@+id/edtml1"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:ems="10"
        android:gravity="start|top"
        android:inputType="textMultiLine"
        android:textSize="20sp" />
</LinearLayout>

    * sqliteCTR.java

package com.example.sqlite;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import androidx.annotation.NonNull;
import static java.sql.DriverManager.println;

public class sqliteCTR extends SQLiteOpenHelper {
    //public static final String DATABASE_NAME = "doransoft.db";
    public static final String TABLE_NAME = "test_table";

    // table column
    public static final String guid = "id";
    public static final String input1 = "input1";
    public static final String input2 = "input2";
    public static final String input3 = "input3";
    // 생정자
    public sqliteCTR(Context context, int version, String DBname){
        super(context, DBname,null,version);
    }
    // table 생성
    @Override
    public void onCreate(@NonNull SQLiteDatabase db) {
        println("onCreate!!!");
        String sql = "CREATE TABLE ";
        sql +=  TABLE_NAME + "(guid INTEGER PRIMARY KEY AUTOINCREMENT, input1 TEXT, input2 TEXT, input3 TEXT)";
        println(sql);
        db.execSQL(sql);
    }
    // table 삭제 후 재생성,
    @Override
    public void onUpgrade(@NonNull SQLiteDatabase db, int oldVersion, int newVersion){
        println("onUpgrade!!!");
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }
    // insert
    public boolean insertData(String input1prm, String input2prm, String input3prm){
        println("insertData!!!");
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(input1, input1prm);
        contentValues.put(input2, input2prm);
        contentValues.put(input3, input3prm);
        long result = db.insert(TABLE_NAME, null, contentValues);
        if(result==-1)
            return false;
        else
            return true;
    }
    // select
    public Cursor getAllData(){
        println("getAllData!!!");
        SQLiteDatabase db = this.getWritableDatabase();
        String sql  = "Select * From " + TABLE_NAME;
        Cursor res = db.rawQuery(sql, null);
        return res;
    }
    // update
    public boolean updateData(String input0prm, String input1prm, String input2prm, String input3prm){
        println("udpateData!!!");
        SQLiteDatabase db= this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(input1,input1prm);
        contentValues.put(input2, input2prm);
        contentValues.put(input3, input3prm);
        int rs = db.update(TABLE_NAME, contentValues, "guid=?", new String[]{input0prm});
        if (rs >0)
            return true;
        else
            return false;
    }
    // delete
    public int deleteDate(String id){
        println("deleteDate!!!");
        SQLiteDatabase db=this.getWritableDatabase();
        return db.delete(TABLE_NAME,"guid=?", new String[]{id});
    }
}

    * MainActivity.java

package com.example.sqlite;

import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

import androidx.appcompat.app.AppCompatActivity;

public class MainActivity extends AppCompatActivity {

    sqliteCTR sqlCTR;
    EditText edtin0, edtin1,edtin2,edtin3,edtDBnm,edttablenm, edtml1;
    Button  btnmkdb, btninput, btnget, btnmod, btndel;

    String DBnm = "";

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

        edtin0 = findViewById(R.id.edtin0);
        edtin1 = findViewById(R.id.edtin1);
        edtin2 = findViewById(R.id.edtin2);
        edtin3 = findViewById(R.id.edtin3);
        edtml1 = findViewById(R.id.edtml1);
        edtDBnm = findViewById(R.id.edtDBnm);
        edttablenm = findViewById(R.id.edttablenm);


        btnmkdb = findViewById(R.id.btnmkdb);
        btnmkdb.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                sqlCTR = new sqliteCTR(v.getContext(),2, edtDBnm.getText().toString());
            }
        });

        btninput = findViewById(R.id.btninput);
        btninput.setOnClickListener(new View.OnClickListener(){
            @Override
            public void onClick(View V){
                boolean isInserted = sqlCTR.insertData(edtin1.getText().toString(),
                        edtin2.getText().toString(), edtin3.getText().toString());

                if(isInserted == true)
                    Toast.makeText(MainActivity.this,"데이터추가 성공", Toast.LENGTH_LONG).show();
                else
                    Toast.makeText(MainActivity.this, "데이터 추가 실패", Toast.LENGTH_LONG).show();
            }
        });

        btnget = findViewById(R.id.btnget);
        btnget.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Cursor res = sqlCTR.getAllData();
                if(res.getCount() ==0){
                    Toast.makeText(MainActivity.this, "데이터를 찾을수 없습니다.", Toast.LENGTH_LONG).show();
                    return;
                }
                StringBuffer buffer = new StringBuffer();
                while(res.moveToNext()){
                    buffer.append("id" + res.getString(0)+ "\n");
                    buffer.append("input1" + res.getString(1)+"\n");
                    buffer.append("input2" + res.getString(2)+ "\n");
                    buffer.append("input3" + res.getString(3) + "\n");
                }
                edtml1.append(buffer.toString());
            }
        });

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

                boolean isUpdated = sqlCTR.updateData(edtin0.getText().toString(),
                        edtin1.getText().toString(),
                        edtin2.getText().toString(),
                        edtin3.getText().toString());
                if(isUpdated == true)
                    Toast.makeText(MainActivity.this, "데이터 수정 성공", Toast.LENGTH_LONG).show();
                else
                    Toast.makeText(MainActivity.this, "데이터 수정 실패", Toast.LENGTH_LONG).show();
            }
        });

        btndel = findViewById(R.id.btndel);
        btndel.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Integer deleteRows = sqlCTR.deleteDate(edtin0.getText().toString());
                if(deleteRows>0)
                    Toast.makeText(MainActivity.this, "데이터 삭제 성공", Toast.LENGTH_LONG).show();
                else
                    Toast.makeText(MainActivity.this, "데이터 삭제 실패", Toast.LENGTH_LONG).show();
            }
        });
    }
}

 

 

ps> DB명 입력 받아 생성하는 것으로 수정했습니다. 테이블 명입력 받는 것은 참고하셔서 만드시면 될것 같습니다.