SQLite TEST
문제
activity1.xml
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout 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">
<TextView
android:id="@+id/textView"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentStart="true"
android:layout_alignParentTop="true"
android:layout_marginStart="23dp"
android:layout_marginTop="20dp"
android:text="수업명 : " />
<TextView
android:id="@+id/textView2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignParentStart="true"
android:layout_alignParentTop="true"
android:layout_marginStart="23dp"
android:layout_marginTop="65dp"
android:text="인원 : " />
<EditText
android:id="@+id/editclass"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_alignParentStart="true"
android:layout_alignParentTop="true"
android:layout_marginStart="89dp"
android:layout_marginTop="6dp"
android:ems="10"
android:inputType="text"/>
<EditText
android:id="@+id/editnum"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_alignParentStart="true"
android:layout_alignParentTop="true"
android:layout_marginStart="89dp"
android:layout_marginTop="52dp"
android:ems="10"
android:inputType="number" />
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_alignParentStart="true"
android:layout_alignParentTop="true"
android:layout_marginTop="113dp"
android:orientation="horizontal">
<Button
android:id="@+id/init"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:layout_margin="5dp"
android:text="초기" />
<Button
android:id="@+id/insert"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:layout_margin="5dp"
android:text="입력" />
<Button
android:id="@+id/update"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:layout_margin="5dp"
android:text="수정" />
<Button
android:id="@+id/delete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:layout_margin="5dp"
android:text="삭제" />
<Button
android:id="@+id/search"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:layout_margin="5dp"
android:text="조회" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_alignParentStart="true"
android:layout_alignParentTop="true"
android:layout_marginTop="172dp"
android:gravity="center"
android:background="@color/teal_700"
android:orientation="horizontal">
<TextView
android:id="@+id/result_class"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:textColor="@color/white"
android:text="TextView" />
<TextView
android:id="@+id/result_num"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:textColor="@color/white"
android:text="TextView" />
</LinearLayout>
</RelativeLayout>
MainActivity.java
package kr.ac.skuniv.a1103practice_1;
import androidx.annotation.Nullable;
import androidx.appcompat.app.AppCompatActivity;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
class DBHelper extends SQLiteOpenHelper{
private static final String DATABASE_NAME = "class.db";
private static final int DATABASE_VERSION=2;
public DBHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE class (name TEXT PRIMARY KEY, num INTEGER)");
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
db.execSQL("DROP TABLE IF EXISTS class");
onCreate(db);
}
}
public class MainActivity extends AppCompatActivity {
EditText edt1,edt2;
Button init, insert,update, delete, search;
TextView result1,result2;
DBHelper helper;
SQLiteDatabase db;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
edt1 = (EditText) findViewById(R.id.editclass);
edt2 = (EditText) findViewById(R.id.editnum);
init = (Button) findViewById(R.id.init);
insert = (Button) findViewById(R.id.insert);
update = (Button) findViewById(R.id.update);
delete = (Button) findViewById(R.id.delete);
search = (Button) findViewById(R.id.search);
result1 = (TextView) findViewById(R.id.result_class);
result2 = (TextView) findViewById(R.id.result_num);
helper = new DBHelper(this);
try {
db = helper.getWritableDatabase();
}catch (SQLiteException e){
db = helper.getReadableDatabase();
}
init.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
helper.onUpgrade(db,1,2);
result1.setText("");
result2.setText("");
}
});
insert.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String a = edt1.getText().toString();
String b = edt2.getText().toString();
db.execSQL("INSERT INTO class VALUES ('"+a+"',"+b+")");
show();
}
});
update.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String a = edt1.getText().toString();
String b = edt2.getText().toString();
db.execSQL("UPDATE class SET num="+b+" WHERE name='"+a+"'");
show();
}
});
delete.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
String a = edt1.getText().toString();
db.execSQL("DELETE FROM class WHERE name='"+a+"'");
show();
}
});
search.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
show();
}
});
}
public void show(){
db = helper.getReadableDatabase();
Cursor cursor;
cursor = db.rawQuery("SELECT * FROM class",null);
String name = "수업명" + '\n' + "-----------------" + '\n';
String num = "인원" + '\n' + "-----------------" + '\n';
while (cursor.moveToNext()){
name += cursor.getString(0) + '\n';
num += cursor.getString(1) + '\n';
}
result1.setText(name);
result2.setText(num);
}
}
