12-2. SQLite 연습

프로젝트1: SQLite 연습

프로젝트 1

package com.example.project12;

import androidx.appcompat.app.AppCompatActivity;

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

public class MainActivity extends AppCompatActivity {
    Button btn5;
    EditText editText;
    myDB myDbHelper;
    SQLiteDatabase sqlDB;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        setTitle("가수 그룹 관리 DB");

        btn5 = (Button)findViewById(R.id.btn5);
        editText = (EditText)findViewById(R.id.editResult);
        myDbHelper = new myDB(this);
        btn5.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                sqlDB = myDbHelper.getReadableDatabase();
                Cursor cursor = sqlDB.rawQuery("SELECT * FROM prodTable;", null);

                String str = "\n";

                while(cursor.moveToNext()){
                    str += cursor.getString(0) + ", "
                            + cursor.getString(1) + ","
                            + cursor.getString(2) + ","
                            + cursor.getString(3) + "\r\n";
                }

                editText.setText(str);
                cursor.close();
                sqlDB.close();
            }
        });

    }

    public class myDB extends SQLiteOpenHelper{

        myDB(Context context){
            super(context, "ex12_5DB", null, 1);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL("CREATE TABLE prodTable ( num INTEGER PRIMARY KEY AUTOINCREMENT, uName CHAR(20), product CHAR(20), count INTEGER);");
            db.execSQL("INSERT INTO prodTable(uName, product, count) VALUES ('장동건','운동화', 2);");
            db.execSQL("INSERT INTO prodTable(uName, product, count) VALUES ('원빈', '노트북', 1);");
            db.execSQL("INSERT INTO prodTable(uName, product, count) VALUES ('소지섭', '모니터', 1);");
            db.execSQL("INSERT INTO prodTable(uName, product, count) Values ('김제동', '모니터', 5);");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS prodTable");
            onCreate(db);
        }
    }
}

프로젝트2: 일기장 SQLite 사용하기

 

package com.example.file;

import androidx.appcompat.app.AppCompatActivity;
import androidx.core.app.ActivityCompat;

import android.Manifest;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.os.Environment;
import android.view.View;
import android.widget.Button;
import android.widget.DatePicker;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;

public class MainActivity extends AppCompatActivity {
    DatePicker datePicker;
    EditText editText;
    Button btn;
    String fileName;
    myDbHelper myDB;
    SQLiteDatabase sql;

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

        ActivityCompat.requestPermissions(this, new String[]{
                Manifest.permission.WRITE_EXTERNAL_STORAGE
        }, MODE_PRIVATE);

        datePicker = (DatePicker)findViewById(R.id.datePicker);
        editText =  (EditText)findViewById(R.id.getDiary);
        btn = (Button)findViewById(R.id.btn);
        myDB = new myDbHelper(this);

        Calendar cal = Calendar.getInstance();
        int cYear = cal.get(Calendar.YEAR);
        int cMonth = cal.get(Calendar.MONTH);
        int cDate = cal.get(Calendar.DATE);
        fileName = Integer.toString(cYear) + "_" + Integer.toString(cMonth+1) + "_" + Integer.toString(cDate);
        String initStr = readDiary(fileName);
        editText.setText(initStr);
        btn.setEnabled(true);

        datePicker.init(cYear, cMonth, cDate, new DatePicker.OnDateChangedListener() {
            @Override
            public void onDateChanged(DatePicker view, int year, int monthOfYear, int dayOfMonth) {
                fileName = Integer.toString(year) + "_" + Integer.toString(monthOfYear+1) + "_" +
                        Integer.toString(dayOfMonth);
                String str = readDiary(fileName);
                editText.setText(str);
                btn.setEnabled(true);
            }
        });

        btn.setOnClickListener(new View.OnClickListener(){
            @Override
            public void onClick(View v) {
                String str = editText.getText().toString();
                sql = myDB.getWritableDatabase();
                if(btn.getText().equals("새로 저장")) {
                    sql.execSQL("INSERT INTO myDiary VALUES(" + '"' + fileName + '"' + ", " +
                            '"' + str + '"' + ");");
                    btn.setText("수정하기");
                }
                else sql.execSQL("UPDATE myDiary SET content = " + '"' + str + '"');
                sql.close();
                Toast.makeText(getApplicationContext(), fileName + "이 저장됨", Toast.LENGTH_SHORT).show();
            }
        });

    }

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

        @Override
        public void onCreate(SQLiteDatabase db) {
            try{
                db.execSQL("CREATE TABLE myDiary (diaryDate char(10) primary key, content varchar(500));");
            }catch (Exception e){
                System.out.println(e);
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS myDiary");
            onCreate(db);
        }
    }

    String readDiary(String fName){
        String diaryStr = null;

        sql = myDB.getReadableDatabase();

        Cursor cursor = sql.rawQuery("SELECT * FROM myDiary WHERE diaryDate = " + '"' + fName + '"' + ";", null);
        while (cursor.moveToNext()) {
            diaryStr = cursor.getString(1);
        }
        if(diaryStr == null){
            editText.setHint("일기 없음");
            btn.setText("새로 저장");
        }
        else {
            btn.setText("수정하기");
        }

        return diaryStr;
    }
}

 

데이터베이스를 확인하면 이렇게 잘 저장된 걸 확인할 수 있다 ^^