Kotlin 데이터 저장,관리 : SQLite 성적관라
- 이름과 영어 성적을 입력하고, 저장을 클릭하면 성적이 저장된다.
- 이름을 입력하고, 읽기 버튼을 클릭하면 하면 성적을 알 수 있다.
- 모두 보기 버튼을 클릭하면 하면 모든 데이터를 보여준다.
- 이름을 입력하고, 특정데이터삭제 버튼을 클릭하면 하면 이름에 해당하는 데이터가 삭제 된다.
(확인은 모두 보기 버튼을 클릭한다.)
- 모두 삭제하면 저장한 입력한 모든 내용을 삭제한다.
-------------------------------------------------------------------------------------
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"
tools:context=".MainActivity">
<LinearLayout
android:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="wrap_content">
<TextView
android:id="@+id/textVIEW"
android:hint="상태 메시지"
android:layout_width="match_parent"
android:layout_height="50dp"
android:layout_margin="10dp"/>
</LinearLayout>
<LinearLayout
android:orientation="horizontal"
android:layout_width="match_parent"
android:layout_height="wrap_content">
<Button
android:id="@+id/writeBT"
android:text="저장"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="@drawable/confirm_botton"
android:layout_weight="1"
android:layout_marginTop="10dp"
android:layout_marginLeft="10dp" />
<Button
android:id="@+id/readBT"
android:text="읽기"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="@drawable/confirm_botton"
android:layout_weight="1"
android:layout_marginTop="10dp"
android:layout_marginLeft="10dp" />
<Button
android:id="@+id/deleteDataBT"
android:text="특정데이터삭제"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="@drawable/confirm_botton"
android:layout_weight="1"
android:layout_marginTop="10dp"
android:layout_marginLeft="10dp"
android:layout_marginRight="10dp" />
</LinearLayout>
<LinearLayout
android:orientation="horizontal"
android:layout_width="match_parent"
android:layout_height="wrap_content">
<EditText
android:id="@+id/nameET"
android:hint="Name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="1"
android:layout_marginTop="10dp"
android:layout_marginLeft="10dp"/>
<EditText
android:id="@+id/scoreET"
android:hint="Score"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="1"
android:layout_marginTop="10dp"
android:layout_marginLeft="10dp"
android:layout_marginRight="10dp"/>
</LinearLayout>
<LinearLayout
android:orientation="horizontal"
android:layout_width="match_parent"
android:layout_height="wrap_content">
<Button
android:id="@+id/showallBT"
android:text="모두 보기"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="@drawable/confirm_botton"
android:layout_weight="1"
android:layout_marginTop="20dp"
android:layout_marginLeft="30dp" />
<Button
android:id="@+id/deleteAllBT"
android:text="모두 삭제"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="@drawable/confirm_botton"
android:layout_weight="1"
android:layout_marginTop="20dp"
android:layout_marginLeft="20dp"
android:layout_marginRight="30dp" />
</LinearLayout>
<TextView
android:id="@+id/allDataTV"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_marginLeft="10dp"
android:layout_marginRight="10dp"
android:layout_marginTop="10dp"
android:background="#FFFCE1" />
</LinearLayout>
------------------------------------------------------------------------------------
botton_true.xml
-- res/drawable
-- New --> Drawable Resource File
-- File Name : button_true , Root Element : shape
-----------------------------------------------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<shape xmlns:android="http://schemas.android.com/apk/res/android"
android:padding="10dp"
android:shape="rectangle" >
<solid android:color="#8EC7F4" />
<stroke android:color="#ff0000ff" android:width="5dp" />
<padding android:left="7dp" android:top="7dp"
android:right="7dp" android:bottom="7dp" />
<corners
android:radius="30dp"
android:bottomLeftRadius="100dp"
android:bottomRightRadius="100dp"
android:topLeftRadius="100dp"
android:topRightRadius="100dp" />
</shape>
------------------------------------------------------------------------------------
botton_false.xml
-- res/drawable
-- New --> Drawable Resource File
-- File Name : button_farse , Root Element : shape
------------------------------------------------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<shape xmlns:android="http://schemas.android.com/apk/res/android"
android:padding="10dp"
android:shape="rectangle" >
<solid android:color="#F4DB8E" />
<stroke android:color="#ff0000ff" android:width="5dp" />
<padding android:left="7dp" android:top="7dp"
android:right="7dp" android:bottom="7dp" />
<corners
android:radius="30dp"
android:bottomLeftRadius="100dp"
android:bottomRightRadius="100dp"
android:topLeftRadius="100dp"
android:topRightRadius="100dp" />
</shape>
------------------------------------------------------------------------------------
confirm_botton.xml
-- res/drawable
-- New --> Drawable Resource File
-- File Name : confirm_botton , Root Element : selector
------------------------------------------------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<selector xmlns:android="http://schemas.android.com/apk/res/android">
<item android:state_pressed="true" android:drawable="@drawable/button_farse"></item>
<item android:state_pressed="false" android:drawable="@drawable/botton_true"></item>
</selector>
------------------------------------------------------------------------------------
MainActivity.kt
------------------------------------------------------------------------------------
private lateinit var statusTV : TextView
class MainActivity : AppCompatActivity() {
val TAG = MainActivity::class.java.simpleName
private lateinit var myEngScoreDatabase : EKownDbHelper
private lateinit var readEngDb: SQLiteDatabase
private lateinit var writeEngDb: SQLiteDatabase
private lateinit var editName : EditText
private lateinit var editScore : EditText
private lateinit var AllDataShowTV : TextView
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
title = "영어 점수 관리하기"
myEngScoreDatabase = EKownDbHelper(this)
readEngDb = myEngScoreDatabase.readableDatabase
writeEngDb = myEngScoreDatabase.writableDatabase
editName = findViewById<EditText>(R.id.nameET)
editScore = findViewById<EditText>(R.id.scoreET)
statusTV = findViewById(R.id.textVIEW)
AllDataShowTV = findViewById(R.id.allDataTV)
val btnReadDb = findViewById<Button>(R.id.readBT)
val btnWriteDb = findViewById<Button>(R.id.writeBT)
val btnDeleteData = findViewById<Button>(R.id.deleteDataBT)
val btnShowAll = findViewById<Button>(R.id.showallBT)
val btnDeleteAllColumns = findViewById<Button>(R.id.deleteAllBT)
btnReadDb.setOnClickListener {
Log.d(TAG,"read database")
statusTV.text = " read database"
var strName = editName.text
val projection = arrayOf(
BaseColumns._ID,
EKownDbHelper.MyContract.MyEntry.COLUMN_NAME_TITLE,
EKownDbHelper.MyContract.MyEntry.COLUMN_NAME_SCORE )
val selection = "${EKownDbHelper.MyContract.MyEntry.COLUMN_NAME_TITLE} = ?"
val selectionArgs = arrayOf("${strName.toString()}")
val cursor = readEngDb.query(
EKownDbHelper.MyContract.MyEntry.TABLE_NAME, // The table to query
projection, // The array of columns to return (pass null to get all)
selection, // The columns for the WHERE clause
selectionArgs, // The values for the WHERE clause
null, // don't group the rows
null, // don't filter by row groups
null // The sort order
)
with(cursor) {
while (moveToNext()) {
val score = getInt(getColumnIndexOrThrow(EKownDbHelper.MyContract.MyEntry.COLUMN_NAME_SCORE))
Log.d(TAG,"score = $score")
editScore.setText(score.toString())
}
}
}
btnWriteDb.setOnClickListener {
Log.d(TAG,"write database")
statusTV.text = " write database"
var strName = editName.text
val strScore = editScore.text
// Create a new map of values , where column names are the keys
val values = ContentValues().apply{
put(EKownDbHelper.MyContract.MyEntry.COLUMN_NAME_TITLE, strName.toString())
put(EKownDbHelper.MyContract.MyEntry.COLUMN_NAME_SCORE, strScore.toString().toInt())
}
// Insert the new row, returning the primary key value of the new row
val newRowId = writeEngDb.insert(EKownDbHelper.MyContract.MyEntry.TABLE_NAME, null,values)
Log.d(TAG,"id = $newRowId")
editName.setText(null)
editScore.setText(null)
}
btnShowAll.setOnClickListener {
AllDataShowTV.text = " Show all datas in the database.\n\n"
val cursor: Cursor = readEngDb.rawQuery("select * from " + EKownDbHelper.MyContract.MyEntry.TABLE_NAME + "", null)
with(cursor){
while (moveToNext()){
val score = getInt(getColumnIndexOrThrow(EKownDbHelper.MyContract.MyEntry.COLUMN_NAME_SCORE))
val name = getString(getColumnIndexOrThrow(EKownDbHelper.MyContract.MyEntry.COLUMN_NAME_TITLE))
val id = getInt(getColumnIndexOrThrow(BaseColumns._ID))
AllDataShowTV.append("id =$id , name = $name , score =$score \n")
}
}
AllDataShowTV.append("\n")
}
btnDeleteData.setOnClickListener {
Log.d(TAG,"onClick() for btnDeleteColumn")
var strName = editName.text
val projection = arrayOf(BaseColumns._ID,
EKownDbHelper.MyContract.MyEntry.COLUMN_NAME_TITLE,
EKownDbHelper.MyContract.MyEntry.COLUMN_NAME_SCORE )
val selection = "${EKownDbHelper.MyContract.MyEntry.COLUMN_NAME_TITLE} = ?"
val selectionArgs = arrayOf("${strName.toString()}")
val cursor = readEngDb.query(
EKownDbHelper.MyContract.MyEntry.TABLE_NAME, // The table to query
projection, // The array of columns to return (pass null to get all)
selection, // The columns for the WHERE clause
selectionArgs, // The values for the WHERE clause
null, // don't group the rows
null, // don't filter by row groups
null // The sort order
)
with(cursor) {
if(cursor.count > 0) {
while (moveToNext()) {
val id = getInt(getColumnIndexOrThrow(BaseColumns._ID))
Log.d(TAG, "id = $id")
writeEngDb.delete( EKownDbHelper.MyContract.MyEntry.TABLE_NAME, "_id=$id",null)
statusTV.text = " Delete column of id =$id"
}
}else {
Log.d(TAG, "$strName is not existed.")
statusTV.text = "Failed to find " + strName.toString() +"."
}
}
}
btnDeleteAllColumns.setOnClickListener {
statusTV.text = statusTV.text.toString() + "\n Delete all columns."
writeEngDb.delete( EKownDbHelper.MyContract.MyEntry.TABLE_NAME, null,null)
editName.setText(null)
editScore.setText(null)
}
editName.setOnClickListener {
editName.setText(null)
}
editScore.setOnClickListener {
editScore.setText(null)
}
}
override fun onDestroy() {
myEngScoreDatabase.close()
super.onDestroy()
}
}
class EKownDbHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME,null,DATABASE_VERSION) {
private val TAG = EKownDbHelper::class.java.simpleName
private val SQL_CREATE_ENTRIES =
"CREATE TABLE ${MyContract.MyEntry.TABLE_NAME} (" +
"${BaseColumns._ID} INTEGER PRIMARY KEY," +
"${MyContract.MyEntry.COLUMN_NAME_TITLE} TEXT," +
"${MyContract.MyEntry.COLUMN_NAME_SCORE} INT)"
private val SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS ${MyContract.MyEntry.TABLE_NAME}"
object MyContract {
object MyEntry : BaseColumns {
const val TABLE_NAME = "english"
const val COLUMN_NAME_TITLE = "name"
const val COLUMN_NAME_SCORE = "score"
}
}
companion object{
val DATABASE_NAME = "MyClassEnglishDb.db"
val DATABASE_VERSION =1
}
override fun onCreate(p0: SQLiteDatabase?) {
// CREATE TABLE tells the system to create a new database table keyword.
Log.d(TAG,"onCreate() +")
p0?.execSQL(SQL_CREATE_ENTRIES)
}
override fun onUpgrade(p0: SQLiteDatabase?, p1: Int, p2: Int) {
// This database is only a cache for online data, so its upgrade policy is
// to simply to discard the data and start over
Log.d(TAG,"onUpgrade() +")
p0?.execSQL(SQL_DELETE_ENTRIES)
onCreate(p0)
}
}
------------------------------------------------------------------------------------
themes.xml
------------------------------------------------------------------------------------
<resources xmlns:tools="http://schemas.android.com/tools">
<!-- Base application theme. -->
<style name="Base.Theme.SQLiteScoreShowALL" parent="Theme.AppCompat.Light">
<item name="colorPrimary">#00BCD4</item>
<item name="colorPrimaryDark">#3F51B5</item>
</style>
<style name="Theme.SQLiteScoreShowALL" parent="Base.Theme.SQLiteScoreShowALL" />
</resources>
SQLiteScoreShowALL.zip
(314.5K)
22회 다운로드
DATE : 2023-11-12 01:59:26