一、实验目的
掌握数据库的创建。
掌握数据库增删改查。
二、实验内容
任务要求:自行设计个人记账本主界面,界面中设计3个输入框,分别为消费种类,如买铅笔,开销花费金额(如:100元),消费时间(当前系统时间),点击记账按钮将账目数据写入数据库表。点击查询按钮将表中账目数据显示在页面中。点击删除按钮可以根据消费种类删除某条数据,点击修改按钮可以根据消费种类修改开销花费金额。
实验效果要求:
实现数据库的增删改查即可。截图演示一条数据的新增、查询、更新、删除。
三、实验指导
- 创建类继承SQLiteOpenHelper,作为数据库使用帮助类。
- 初始化数据库帮助类,并使用数据库帮助类。SQLiteDatabase db = dbHelper.getWritableDatabase()。
- 使用sql语句对数据库进行增删改查。
- 将模拟器运行结果截图到word文档中,并将核心代码复制到word文档中。
四、模拟器效果截图
五、实验源代码
AccountsMain.java
package com.example.myapplication02;
import androidx.appcompat.app.AppCompatActivity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.Toast;
import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class AccountsMain extends AppCompatActivity {
AccountsOpen accountsOpen;
SQLiteDatabase db;//数据库的实例对象
EditText category,price;
Button btn_add,btn_select,btn_update,btn_delete;
ListView lv;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.accounts);
initView();
accountsOpen=new AccountsOpen(this);
//创建可读可写的数据库对象
db=accountsOpen.getWritableDatabase();
setonclick();
}
private void initView() {
category=findViewById(R.id.category);
price=findViewById(R.id.price);
btn_add=findViewById(R.id.btn_add);
btn_select=findViewById(R.id.btn_select);
btn_update=findViewById(R.id.btn_update);
btn_delete=findViewById(R.id.btn_delete);
lv=findViewById(R.id.lv);
}
private void setonclick() {
btn_add.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
//新增时判断一下有木有这个水果,如果有,提示用户有了,只能更新不能新增
Cursor cursor= db.rawQuery("select * from account",null);
List<String> namelist=new ArrayList<>();
while(cursor.moveToNext()){
String categoryname=cursor.getString(1);
namelist.add(categoryname);
}
//获取用户输入的名字
String exited=category.getText().toString();
if(namelist.contains(exited)){
Toast.makeText(AccountsMain.this,"数据表中已经有了,只能更新不能新增",Toast.LENGTH_LONG).show();
return;
}
ContentValues values=new ContentValues();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss");
Date date = new Date(System.currentTimeMillis());
String atime = simpleDateFormat.format(date);
values.put("category",category.getText().toString());
values.put("price",price.getText().toString());
values.put("time",atime);
long i= db.insert("account",null,values);
if(i>-1){
Toast.makeText(AccountsMain.this,"记账内容添加成功!",Toast.LENGTH_LONG).show();
}
}
});
btn_select.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
Cursor cursor= db.rawQuery("select * from account",null);
List<AccountsKeep> accountslist=new ArrayList<>();
if(cursor.getCount()!=0){
while(cursor.moveToNext()){
AccountsKeep accountsKeep=new AccountsKeep();
accountsKeep.setId(cursor.getInt(0));
accountsKeep.setCategory(cursor.getString(1));
accountsKeep.setPrice(cursor.getString(2));
accountsKeep.setTime(cursor.getString(3));
accountslist.add(accountsKeep);
accountsKeep=null;
}
cursor.close();
//数据已经放在集合中了,使用适配器绑定listview
ArrayAdapter<AccountsKeep> arrayAdapter=new ArrayAdapter<>(AccountsMain.this,
android.R.layout.simple_list_item_1,accountslist);
lv.setAdapter(arrayAdapter);
}
}
});
btn_update.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
ContentValues values=new ContentValues();
values.put("price",price.getText().toString());
int i= db.update("account",values,"category=?",new String[]{category.getText().toString()});
if(i>0){
Toast.makeText(AccountsMain.this,"记账内容更新成功!",Toast.LENGTH_LONG).show();
}
}
});
btn_delete.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
int i= db.delete("account","category=?",new String[]{category.getText().toString()});
if(i>0){
Toast.makeText(AccountsMain.this,"记账内容删除成功!",Toast.LENGTH_LONG).show();
}
}
});
}
}
AccountsKeep.java
package com.example.myapplication02;
public class AccountsKeep {
int id;
String category;
String price;
String time;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getCategory() {
return category;
}
public void setCategory(String category) {
this.category = category;
}
public String getPrice() {
return price;
}
public void setPrice(String price) {
this.price = price;
}
public String getTime() {
return time;
}
public void setTime(String time) {
this.time = time;
}
@Override
public String toString() {
return "AccountsKeep{" +
"id=" + id +
", category='" + category + '\'' +
", price='" + price + '\'' +
", time='" + time + '\'' +
'}';
}
}
AccountsOpen.java
package com.example.myapplication02;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import androidx.annotation.Nullable;
public class AccountsOpen extends SQLiteOpenHelper {
public AccountsOpen(@Nullable Context context) {
//第二个参数:name 数据库的名称 account.db
//第三个参数:游标工厂,默认的游标工厂null
//第四个参数:version版本号 从1开始
super(context, "account.db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
//数据库第一次被创建的时候调用,适合做一些数据表结构的初始化
sqLiteDatabase.execSQL("create table account (id INTEGER primary key autoincrement," +
"category varchar(30)," +
"price varchar(30)," +
"time varchar(30))");
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
}
accounts.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:background="@drawable/p01"
tools:context=".AccountsMain"
android:orientation="vertical">
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="记账本"
android:textSize="30sp"
android:textStyle="bold"
android:gravity="center"
android:layout_marginTop="50dp" />
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="50dp"
android:hint="请输入消费种类"
android:id="@+id/category"/>
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
android:hint="请输入花费金额"
android:id="@+id/price"/>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal">
<Button
android:layout_width="0dp"
android:layout_weight="1"
android:layout_height="wrap_content"
android:text="记账添加"
android:id="@+id/btn_add"/>
<Button
android:layout_width="0dp"
android:layout_weight="1"
android:layout_height="wrap_content"
android:text="记账查询"
android:id="@+id/btn_select"/>
<Button
android:layout_width="0dp"
android:layout_weight="1"
android:layout_height="wrap_content"
android:text="记账更新"
android:id="@+id/btn_update"/>
<Button
android:layout_width="0dp"
android:layout_weight="1"
android:layout_height="wrap_content"
android:text="记账删除"
android:id="@+id/btn_delete"/>
</LinearLayout>
<ListView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:id="@+id/lv" />
</LinearLayout>