SQLite和SQLiteDatabase应用
一、实验目的
1、本次实验的目的是让大家熟悉Android中对数据库进行操作的相关的接口、类等。SQLiteDatabase这个是在android中数据库操作使用最频繁的一个类。通过它可以实现数据库的创建或打开、创建表、插入数据、删除数据、查询数据、修改数据等操作。
2、实现添加用户名，爱好小例程。
二、实验要求
1、完成Android开发平台的搭建及相关配置
2、创建项目并熟悉文件目录结构
3、实现例程添加用户名，爱好实验步骤
三、实验步骤
1.创建项目
2.编写代码
逻辑代码：
|
package com.example.costbook;
import android.content.DialogInterface;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import com.google.android.material.floatingactionbutton.FloatingActionButton;
import com.google.android.material.snackbar.Snackbar;
import androidx.appcompat.app.AlertDialog;
import androidx.appcompat.app.AppCompatActivity;
import androidx.appcompat.widget.Toolbar;
import android.os.Parcelable;
import android.view.LayoutInflater;
import android.view.View;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.DatePicker;
import android.widget.EditText;
import android.widget.ListView;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
public class MainActivity extends AppCompatActivity {
private List<CostBean> mCostBeanList;
private DatabaseHelper mdatabaseHelper;
private CostListAdapter mAdapter;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
Toolbar toolbar = findViewById(R.id.toolbar);
setSupportActionBar(toolbar);
mdatabaseHelper = new DatabaseHelper(this);
mCostBeanList = new ArrayList<>();
ListView costList = (ListView) findViewById(R.id.lv_main);
initCostData();
mAdapter = new CostListAdapter(this, mCostBeanList);
costList.setAdapter(mAdapter);
FloatingActionButton fab = findViewById(R.id.fab);
fab.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
AlertDialog.Builder builder = new AlertDialog.Builder(MainActivity.this);
LayoutInflater inflater = LayoutInflater.from(MainActivity.this);
View viewDialog = inflater.inflate(R.layout.new_cost_data, null);
final EditText title = (EditText) viewDialog.findViewById(R.id.et_cost_title);
final EditText money = (EditText) viewDialog.findViewById(R.id.et_cost_money);
final DatePicker date = (DatePicker) viewDialog.findViewById(R.id.dp_cost_data);
builder.setView(viewDialog);
builder.setTitle("添加爱好");
builder.setPositiveButton("OK", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
CostBean costBean = new CostBean();
costBean.costTitle = title.getText().toString();
costBean.costMoney = money.getText().toString();
costBean.costDate = date.getYear() + "-" + (date.getMonth() + 1) + "-" + date.getDayOfMonth();
mdatabaseHelper.insertCost(costBean);
mCostBeanList.add(costBean);
mAdapter.notifyDataSetChanged();
}
});
builder.setNegativeButton("Cancel", null);//点击取消
builder.create().show();//显示dialog的布局
}
});
}
private void initCostData() {
// mdatabaseHelper.deleteAllData();
/* CostBean costBean=new CostBean();
for (int i=0;i<6;i++) {
costBean.costTitle=i+"imooc";
costBean.costDate="11-11";
costBean.costMoney="20";
mdatabaseHelper.insertCost(costBean);
}*/
Cursor cursor = mdatabaseHelper.getAllCostData();
if (cursor != null) {
while (cursor.moveToNext()) {
CostBean costBean1 = new CostBean();
costBean1.costTitle = cursor.getString(cursor.getColumnIndex("cost_title"));
costBean1.costDate = cursor.getString(cursor.getColumnIndex("cost_date"));
costBean1.costMoney = cursor.getString(cursor.getColumnIndex("cost_money"));
mCostBeanList.add(costBean1);
}
cursor.close();
}
}
}
|
|
package com.example.costbook;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import androidx.annotation.Nullable;
public class DatabaseHelper extends SQLiteOpenHelper {
public static final String COST_TITLE = "cost_title";
public static final String COST_DATE = "cost_date";
public static final String COST_MONEY = "cost_money";
public static final String IMOOC_COST = "imooc_cost";
public DatabaseHelper(@Nullable Context context) {
super(context, "imooc_daily", null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table if not exists IMOOC_COST("+
"id integer primary key, "+
"cost_title varchar, "+
"cost_date varchar, "+
"cost_money varchar)");
}
public void insertCost(CostBean costBean)
{
SQLiteDatabase database=getWritableDatabase();
ContentValues cv=new ContentValues();
cv.put(COST_TITLE,costBean.costTitle);
cv.put(COST_DATE,costBean.costDate);
cv.put(COST_MONEY,costBean.costMoney);
database.insert(IMOOC_COST,null,cv);
}
public Cursor getAllCostData(){
SQLiteDatabase database=getWritableDatabase();
return database.query("IMOOC_COST",null,null,null,null,null,"COST_DATE "+" ASC");
}
public void deleteAllData(){
SQLiteDatabase database=getWritableDatabase();
database.delete("IMOOC_COST",null,null);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
|
|
package com.example.costbook;
import android.content.Context;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.TextView;
import java.util.List;
public class CostListAdapter extends BaseAdapter {
private List<CostBean> mlist;
private Context mContext;
private LayoutInflater mlayoutInflater;
public CostListAdapter(Context context,List<CostBean> list){
mContext=context;
mlist=list;
mlayoutInflater=LayoutInflater.from(context);
}
@Override
public int getCount() {
return mlist.size();
}
@Override
public Object getItem(int position) {
return mlist.get(position);
}
@Override
public long getItemId(int position) {
return position;
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
ViewHolder viewHolder;
if(convertView==null)
{
viewHolder=new ViewHolder();
convertView=mlayoutInflater.inflate(R.layout.list_item,null);
viewHolder.mTvCostTitle=convertView.findViewById(R.id.tv_title);
viewHolder.mTvCostDate=convertView.findViewById(R.id.tv_date);
viewHolder.mTvCostMoney=convertView.findViewById(R.id.tv_cost);
convertView.setTag(viewHolder);
}else{
viewHolder=(ViewHolder) convertView.getTag();
}
CostBean bean=mlist.get(position);
viewHolder.mTvCostTitle.setText(bean.costTitle);
viewHolder.mTvCostDate.setText(bean.costDate);
viewHolder.mTvCostMoney.setText(bean.costMoney);
return convertView;
}
private static class ViewHolder{
public TextView mTvCostTitle;
public TextView mTvCostDate;
public TextView mTvCostMoney;
}
}
|
|
package com.example.costbook;
import java.io.Serializable;
public class CostBean implements Serializable {
public String costTitle;
public String costDate;
public String costMoney;
@Override
public String toString() {
return "CostBean{" +
"costTitle='" + costTitle + '\'' +
", costDate='" + costDate + '\'' +
", costMoney='" + costMoney + '\'' +
'}';
}
public String getCostDate() {
return costDate;
}
public void setCostDate(String costDate) {
this.costDate = costDate;
}
public String getCostMoney() {
return costMoney;
}
public void setCostMoney(String costMoney) {
this.costMoney = costMoney;
}
public String getCostTitle() {
return costTitle;
}
public void setCostTitle(String costTitle) {
this.costTitle = costTitle;
}
}
|
布局代码：
|
<?xml version="1.0" encoding="utf-8"?>
<androidx.coordinatorlayout.widget.CoordinatorLayout 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">
<com.google.android.material.appbar.AppBarLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:theme="@style/AppTheme.AppBarOverlay">
<androidx.appcompat.widget.Toolbar
android:id="@+id/toolbar"
android:layout_width="match_parent"
android:layout_height="?attr/actionBarSize"
android:background="?attr/colorPrimary"
app:popupTheme="@style/AppTheme.PopupOverlay" />
</com.google.android.material.appbar.AppBarLayout>
<include layout="@layout/content_main" />
<com.google.android.material.floatingactionbutton.FloatingActionButton
android:id="@+id/fab"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="bottom|end"
android:layout_margin="16dp"
app:srcCompat="@mipmap/tianjia" />
</androidx.coordinatorlayout.widget.CoordinatorLayout>
|
|
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="80dp"
android:orientation="vertical">
<TextView
android:id="@+id/tv_title"
android:layout_width="150dp"
android:layout_height="80dp"
android:layout_marginLeft="10dp"
android:layout_alignParentLeft="true"
android:gravity="center"
android:singleLine="true"
android:textSize="35sp"
android:text="costTitle"
android:ellipsize="marquee" />
<TextView
android:id="@+id/tv_date"
android:layout_width="wrap_content"
android:layout_height="80dp"
android:layout_toRightOf="@+id/tv_title"
android:layout_marginLeft="15dp"
android:textSize="20sp"
android:gravity="center"
android:text="costDate"/>
<TextView
android:id="@+id/tv_cost"
android:layout_width="wrap_content"
android:layout_height="80dp"
android:text="30"
android:textSize="30sp"
android:layout_marginRight="20dp"
android:layout_alignParentRight="true"
android:gravity="center"/>
</RelativeLayout>
|
|
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical" android:layout_width="match_parent"
android:layout_height="match_parent"
android:gravity="center">
<EditText
android:id="@+id/et_cost_title"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_margin="4dp"
android:hint="用户名"/>
<EditText
android:id="@+id/et_cost_money"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_margin="4dp"
android:hint="爱好"/>
<DatePicker
android:id="@+id/dp_cost_data"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_margin="4dp"
android:datePickerMode="spinner"
android:calendarViewShown="false"/>
</LinearLayout>
|
- 运行效果：
