一、实验目的
掌握数据库的创建。
掌握数据库增删改查。
二、实验内容
任务要求:自行设计个人记账本主界面,界面中设计3个输入框,分别为消费种类,如买铅笔,开销花费金额(如:100元),消费时间(当前系统时间),点击记账按钮将账目数据写入数据库表。点击查询按钮将表中账目数据显示在页面中。点击删除按钮可以根据消费种类删除某条数据,点击修改按钮可以根据消费种类修改开销花费金额。
实验效果要求:
实现数据库的增删改查即可。截图演示一条数据的新增、查询、更新、删除。
三、实验指导
- 创建类继承SQLiteOpenHelper,作为数据库使用帮助类。
- 初始化数据库帮助类,并使用数据库帮助类。SQLiteDatabase db = dbHelper.getWritableDatabase()。
- 使用sql语句对数据库进行增删改查。
四、模拟器效果截图
五、源代码(可复制可截图)
MainActivity
package cs.qqq.sqlite;
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.TextView;
import android.widget.Toast;
import androidx.appcompat.app.AppCompatActivity;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class MainActivity extends AppCompatActivity {
Myopenhelper myopenhelper;
SQLiteDatabase sqLiteDatabase;
EditText editTextTextPersonName1,editTextTextPersonName2,editTextTextPersonName3;
Button btn_add, btn_select, btn_update, btn_delete;
ListView listView;
TextView textView1,textView2,textView3;
SimpleDateFormat simpleDateFormat;
String date;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
initView();
myopenhelper = new Myopenhelper(this);
//创建可读可写的数据库
sqLiteDatabase = myopenhelper.getWritableDatabase();
simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
date=simpleDateFormat.format(new Date());
editTextTextPersonName3.setText(date);
setonclick();
}
private void setonclick() {
btn_add.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
//新增时判断一下有没有这个水果,若有则只可更新
ContentValues contentValues=new ContentValues();
contentValues.put("type",editTextTextPersonName1.getText().toString());
contentValues.put("total",editTextTextPersonName2.getText().toString());
contentValues.put("date",date);
long i = sqLiteDatabase.insert("ledger", null,contentValues);
if(i>0){
Toast.makeText(MainActivity.this, "已添加", Toast.LENGTH_SHORT).show();
}
}
});
btn_select.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
//Cursor cursor = sqLiteDatabase.rawQuery("select * from goods_info", null);
Cursor cursor = sqLiteDatabase.query("ledger", null, null, null,null,null,null);
List<Ledger> ledgerList = new ArrayList<>();
if (cursor.getCount() != 0) {
while (cursor.moveToNext()) {
Ledger ledger = new Ledger();
ledger.setId(cursor.getInt(0));
ledger.setDate(cursor.getString(1));
ledger.setTotal(cursor.getString(2));
ledger.setType(cursor.getString(3));
ledgerList.add(ledger);
ledger = null;
}
Toast.makeText(MainActivity.this, "已查询", Toast.LENGTH_SHORT).show();
cursor.close();
ArrayAdapter<Ledger> arrayAdapter = new ArrayAdapter<>(MainActivity.this, android.R.layout.simple_list_item_activated_1, ledgerList);
listView.setAdapter(arrayAdapter);
}
}
});
btn_update.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
ContentValues contentValues=new ContentValues();
contentValues.put("type",editTextTextPersonName1.getText().toString());
contentValues.put("total",editTextTextPersonName2.getText().toString());
SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
contentValues.put("date",simpleDateFormat.format(new Date()));
int i=sqLiteDatabase.update("ledger",contentValues,"type=?",new String[]{editTextTextPersonName1.getText().toString()});
if(i>0){
Toast.makeText(MainActivity.this, "已更新", Toast.LENGTH_SHORT).show();
}
}
});
btn_delete.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
int i=sqLiteDatabase.delete("ledger","type=?",new String[]{
editTextTextPersonName1.getText().toString()
});
if (i>0){
Toast.makeText(MainActivity.this, "已删除", Toast.LENGTH_SHORT).show();
}
}
});
}
private void initView() {
textView1= findViewById(R.id.textView1);
textView2 = findViewById(R.id.textView2);
textView3 = findViewById(R.id.textView3);
editTextTextPersonName1=findViewById(R.id.editTextTextPersonName1);
editTextTextPersonName2=findViewById(R.id.editTextTextPersonName2);
editTextTextPersonName3=findViewById(R.id.editTextTextPersonName3);
btn_delete=findViewById(R.id.btn_delete);
btn_add=findViewById(R.id.btn_add);
btn_select=findViewById(R.id.btn_select);
btn_update=findViewById(R.id.btn_update);
listView=findViewById(R.id.listView);
}
}
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">
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="50dp"
android:gravity="center"
android:text="记账本"
android:textSize="30sp"
android:textStyle="bold"
/>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="50dp"
android:orientation="horizontal"
android:layout_marginTop="50dp">
<TextView
android:id="@+id/textView1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="0"
android:layout_marginLeft="15dp"
android:layout_marginRight="15dp"
android:textSize="20dp"
android:textColor="@color/black"
android:text="消费种类:" />
<EditText
android:id="@+id/editTextTextPersonName1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:ems="10"
android:inputType="textPersonName"
android:layout_marginRight="15dp"
android:text="" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="50dp"
android:orientation="horizontal">
<TextView
android:id="@+id/textView2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="0"
android:layout_marginLeft="15dp"
android:layout_marginRight="15dp"
android:textSize="20dp"
android:textColor="@color/black"
android:text="花费金额:" />
<EditText
android:id="@+id/editTextTextPersonName2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:ems="10"
android:inputType="textPersonName"
android:layout_marginRight="15dp"
android:text="" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="50dp"
android:orientation="horizontal">
<TextView
android:id="@+id/textView3"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="0"
android:layout_marginLeft="15dp"
android:layout_marginRight="15dp"
android:textSize="20dp"
android:textColor="@color/black"
android:text="消费时间:" />
<EditText
android:id="@+id/editTextTextPersonName3"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_weight="1"
android:ems="10"
android:inputType="textPersonName"
android:layout_marginRight="15dp"
android:text="" />
</LinearLayout>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal">
<Button
android:id="@+id/btn_add"
android:layout_width="0dp"
android:layout_height="match_parent"
android:layout_weight="1"
android:layout_marginRight="2dp"
android:layout_marginLeft="15dp"
android:text="记账" />
<Button
android:id="@+id/btn_select"
android:layout_width="0dp"
android:layout_height="match_parent"
android:layout_weight="1"
android:layout_marginRight="2dp"
android:layout_marginLeft="2dp"
android:text="查询" />
<Button
android:id="@+id/btn_delete"
android:layout_width="0dp"
android:layout_height="match_parent"
android:layout_weight="1"
android:layout_marginRight="2dp"
android:layout_marginLeft="2dp"
android:text="删除" />
<Button
android:id="@+id/btn_update"
android:layout_width="0dp"
android:layout_height="match_parent"
android:layout_weight="1"
android:layout_marginLeft="2dp"
android:layout_marginRight="15dp"
android:text="修改" />
/>
</LinearLayout>
<ListView
android:id="@+id/listView"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="10dp"
/>
</LinearLayout>
Myopenhelper
package cs.qqq.sqlite;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import androidx.annotation.Nullable;
public class Myopenhelper extends SQLiteOpenHelper {
public Myopenhelper(@Nullable Context context) {
super(context, "ledger.db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase){
//数据库第一次被创建的时候调用,适合走一些数据库表的初始化
System.out.println("onCreate...");
sqLiteDatabase.execSQL("create table ledger (_id INTEGER primary key autoincrement," +
"date varchar(20)," +
"total varchar(20)," +
"type varchar(20)" +
")");
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
//数据库升级时会被调用,数据库只能升级不能降级
}
}
Goodsinfo
package cs.qqq.sqlite;
public class Goodsinfo {
int _id;
String goods_name;
String goods_price;
@Override
public String toString() {
return "Goodsinfo{" +
"_id=" + _id +
", goods_name='" + goods_name + '\'' +
", goods_price='" + goods_price + '\'' +
'}';
}
public int get_id() {
return _id;
}
public void set_id(int _id) {
this._id = _id;
}
public String getGoods_name() {
return goods_name;
}
public void setGoods_name(String goods_name) {
this.goods_name = goods_name;
}
public String getGoods_price() {
return goods_price;
}
public void setGoods_price(String goods_price) {
this.goods_price = goods_price;
}
}
Ledger
package cs.qqq.sqlite;
public class Ledger {
private int id;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
private String date;
private String total;
private String type;
public String getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
public String getTotal() {
return total;
}
public void setTotal(String total) {
this.total = total;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
@Override
public String toString() {
return "Ledger{" +
"id=" + id +
", date='" + date + '\'' +
", total='" + total + '\'' +
", type='" + type + '\'' +
'}';
}
}