移动开发技术（Android）——实验8 SQLite数据库的使用
一、实验目的
- 掌握创建SQLite数据库的方法；
- 掌握实现对SQLite数据增删改查的方法；
- 学会使用调试工具查看数据库和数据表；
二、实验内容
创建一个Android项目，项目名称为“shiyan0801_专业_×××（学生姓名）”，要求：
（1）创建2个Activity
创建2个Activity，名称分别为MainActivity、ManageActivity，对应的布局文件名称分别为activity_main.xml、activity_manage。
（2）界面构成
① 主界面activity_main.xml
实现用户登录，包括以下控件：
a）2个文本框，分别用来显示“管理员”、“密码”；
b）2个编辑框，分别用来接收用户输入的用户名、密码；
c）1个按钮，文本显示为“登录”。
<TableLayout 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:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
tools:context=".MainActivity" >
<TableRow>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="用户名"
android:textSize="17sp" />
<EditText
android:id="@+id/ET_username"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="1"
android:textSize="17sp" />
</TableRow>
<TableRow>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="密 码"
android:textSize="17sp" />
<EditText
android:id="@+id/ET_password"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="1"
android:inputType="textPassword"
android:textSize="17sp" />
</TableRow>
<Button
android:id="@+id/BT_login"
android:text="登录" />
<TextView
android:id="@+id/TV_show"
android:gravity="center"
android:text="用户名：AAA \n 密码：180000"
android:textSize="20sp" />
</TableLayout>
② activity_manage.xml
实现图书信息设置，包括以下控件：
(a)5个文本框用于显示提示信息“图书名称：”、“图书价格”、“图书版次”“图书类别”、“出版社”；
(b)2个编辑框分别用于接收图书的名称和价格；
©4个单选按钮用于版次选择（1版、2版、3版、3版以上）；
(d)n个复选框用于类别选择（教育、计算机、小说、科技、励志、文学等）；
(e)1个下拉列表用于出版社选择（人民邮电出版社、科学出版社、高等教育出版社、清华大学出版社，其他等）；
(f)4个普通按钮，文本分别显示为“添加”、“修改”、“删除”、“查询”；
(g)1个ListView控件。
<TableLayout 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:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
tools:context=".ManageActivity" >
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="图书名称" />
<EditText android:id="@+id/ED_name" />
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="图书价格" />
<EditText android:id="@+id/ED_prize" />
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="图书版次" />
<RadioGroup
android:id="@+id/RadioGroup1"
android:orientation="horizontal" >
<RadioButton
android:id="@+id/radio_1"
android:text="1版" />
<RadioButton
android:id="@+id/radio_2"
android:text="2版" />
<RadioButton
android:id="@+id/radio_3"
android:text="3版" />
<RadioButton
android:id="@+id/radio_3p"
android:text="3版以上" />
</RadioGroup>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="图书类别" />
<TableRow android:id="@+id/CB_TR" >
<CheckBox android:text="教育" />
<CheckBox android:text="计算机" />
<CheckBox android:text="小说" />
</TableRow>
<TableRow android:id="@+id/CB_TR2" >
<CheckBox android:text="励志" />
<CheckBox android:text="科技" />
<CheckBox android:text="文学" />
</TableRow>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="出版社" />
<Spinner
android:id="@+id/spinner"
android:entries="@array/publisher" />
<TableRow>
<Button
android:id="@+id/submit_BT"
android:layout_weight="1"
android:text="添加" />
<Button
android:id="@+id/update_BT"
android:layout_weight="1"
android:text="修改" />
<Button
android:id="@+id/delete_BT"
android:layout_weight="1"
android:text="删除" />
<Button
android:id="@+id/select_BT"
android:layout_weight="1"
android:text="查询" />
</TableRow>
<ListView
android:id="@+id/listview"
android:layout_width="match_parent"
android:layout_height="wrap_content" >
</ListView>
</TableLayout>
③ 适配器布局adapter2.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="horizontal" >
<TextView
android:id="@+id/no1"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginRight="10px" />
<TextView
android:id="@+id/no2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginRight="10px" />
<TextView
android:id="@+id/no3"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginRight="10px" />
<TextView
android:id="@+id/no4"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginRight="10px" />
<TextView
android:id="@+id/no5"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_marginRight="10px" />
</LinearLayout>
（3）程序功能
① DBOpenHelper.java（数据库辅助类）
package com.example.shiyan0801; //(此处换成自己的包名)
import android.content.Context;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
public class DBOpenHelper extends SQLiteOpenHelper{
public DBOpenHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, name, factory, version);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
db.execSQL("create table BOOK_INFORMATION(_id integer primary key,"+
"bookName varchar(20), "+
"bookPrice varchar(10),"+
"bookVersion varchar(10),"+
"bookKind varchar(20),"+
"bookPublisher varchar(20))");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
db.execSQL("drop table if exists BOOK_INFORMATION");
onCreate(db);
}
}
② MainActivity.java
点击MainActivity中的“登录”按钮，若管理员和密码分别为学生的姓名和学号，则弹出提示对话框，显示“***登录成功！”，然后跳转到ManageActivity，否则，弹出Toast提示“管理员或密码错误，请重新输入”。
package com.example.shiyan0801; //(此处换成自己的包名)
import android.os.Bundle;
import android.app.Activity;
import android.app.AlertDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
public class MainActivity extends Activity {
EditText ET_username ,ET_passweord;
Button BT_login;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
ET_username=(EditText) findViewById(R.id.ET_username);
ET_passweord=(EditText) findViewById(R.id.ET_password);
BT_login=(Button) findViewById(R.id.BT_login);
BT_login.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
String Str_username = ET_username.getText().toString();
String str_password = ET_passweord.getText().toString();
if(Str_username.equals("AAA")&&str_password.equals("180000")){
AlertDialog alert = new AlertDialog.Builder(MainActivity.this)
.setTitle("提示").setMessage("登陆成功").setPositiveButton("确定", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
// TODO Auto-generated method stub
Intent intent = new Intent(MainActivity.this, ManageActivity.class);
startActivity(intent);
}
}).create();
alert.show();
}else{
Toast.makeText(MainActivity.this,"管理员或密码错误，请重新输入" , Toast.LENGTH_LONG).show();
}
}
});
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
}
③ ManageActivity.java
ManageActivity中的操作：
a)单击“添加”按钮，将用户输入和选择的各项信息写入数据库；
b)单击“修改”按钮，将_id为3的记录的价格改为100，出版社改为“清华大学出版社”；
c)单击“删除”按钮，将所有版次为1版的记录删除；
d)单击“查询”按钮，将出版社为“人民邮电出版社”的记录显示在ListView控件中。
package com.example.shiyan0801; //(此处换成自己的包名)
import java.util.ArrayList;
import android.os.Bundle;
import android.support.v4.widget.SimpleCursorAdapter;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.view.Menu;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.CheckBox;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.Spinner;
import android.widget.TableRow;
import android.widget.TextView;
import android.widget.Toast;
public class ManageActivity extends Activity {
private DBOpenHelper dbOpenHelper;
private static final int VERSION = 2;
private static final String DBNAME = "BOOK_INFORMATION.db";
SQLiteDatabase db;
EditText ET_bookName,ET_bookPrice;
RadioGroup RG_version;
TableRow TR_category,TR_category2;
Spinner sp;
Button BT_insert,BT_update,BT_delete,BT_select;
ListView listView;
ArrayList<String> allmess;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_manage);
dbOpenHelper = new DBOpenHelper(ManageActivity.this,DBNAME,null,VERSION);
db=dbOpenHelper.getWritableDatabase();
ET_bookName=(EditText) findViewById(R.id.ED_name);
ET_bookPrice=(EditText) findViewById(R.id.ED_prize);
RG_version=(RadioGroup) findViewById(R.id.RadioGroup1);
TR_category=(TableRow) findViewById(R.id.CB_TR);
TR_category2=(TableRow) findViewById(R.id.CB_TR2);
sp=(Spinner) findViewById(R.id.spinner);
listView=(ListView) findViewById(R.id.listview);
BT_insert=(Button) findViewById(R.id.submit_BT);
BT_insert.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
String Str_version="";
String Str_kind="";
for(int i=0;i<RG_version.getChildCount();i++){
RadioButton r = (RadioButton) RG_version.getChildAt(i);
if(r.isChecked()){
Str_version=r.getText().toString();
}
}
for(int i=0;i<TR_category.getChildCount();i++){
CheckBox c = (CheckBox) TR_category.getChildAt(i);
if(c.isChecked()){
Str_kind+=c.getText().toString()+" ";
}
}
for(int i=0;i<TR_category2.getChildCount();i++){
CheckBox c = (CheckBox) TR_category2.getChildAt(i);
if(c.isChecked()){
Str_kind+=c.getText().toString()+" ";
}
}
/*db.execSQL("insert into BOOK_INFORMATION(bookName,bookPrice,bookVersion,bookKind,bookPublisher)"+
" values(\'"+ET_bookName.getText().toString()+"\',"+
"\'"+ET_bookPrice.getText().toString()+"\',"+
"\'"+Str_version+"\',"+
"\'"+Str_kind+"\',"+
"\'"+sp.getSelectedItem().toString()+"\')");*/
ContentValues values = new ContentValues();
values.put("bookName", ET_bookName.getText().toString());
values.put("bookPrice", ET_bookPrice.getText().toString());
values.put("bookVersion", Str_version);
values.put("bookKind", Str_kind);
values.put("bookPublisher", sp.getSelectedItem().toString());
long rt=db.insert("BOOK_INFORMATION", null, values);
if(rt==-1){
Toast.makeText(ManageActivity.this, "插入失败", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(ManageActivity.this, "插入成功", Toast.LENGTH_SHORT).show();
}
}
});
BT_update=(Button) findViewById(R.id.update_BT);
BT_update.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
//db.execSQL("update BOOK_INFORMATION set bookPrice = '100',bookPublisher = '清华大学出版社' where _id=3");
ContentValues values = new ContentValues();
values.put("bookPrice", "100");
values.put("bookPublisher", "清华大学出版社");
int res = db.update("BOOK_INFORMATION", values, "_id=3", null);
if(res==0){
Toast.makeText(ManageActivity.this, "更新失败", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(ManageActivity.this, "更新成功", Toast.LENGTH_SHORT).show();
}
}
});
BT_delete=(Button) findViewById(R.id.delete_BT);
BT_delete.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
//db.execSQL("delete from BOOK_INFORMATION where bookVersion='1版'");
int res=db.delete("BOOK_INFORMATION", "bookVersion=?", new String[]{"1版"});
if(res==0){
Toast.makeText(ManageActivity.this, "删除失败", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(ManageActivity.this, "删除成功", Toast.LENGTH_SHORT).show();
}
}
});
BT_select=(Button) findViewById(R.id.select_BT);
BT_select.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
allmess = new ArrayList<String>();
//Cursor cursor = db.rawQuery("select * from BOOK_INFORMATION", null);
Cursor cursor = db.query("BOOK_INFORMATION", null, null, null, null, null, null,null);
// if(cursor.moveToFirst()){
// while(!cursor.isAfterLast()){
// allmess.add(" 书名:"+cursor.getString(1)+
// " 价格:"+cursor.getString(2)+
// " 版本:"+cursor.getString(3)+
// " 类别:"+cursor.getString(4)+
// " 出版社:"+cursor.getString(5));
// cursor.moveToNext();
// }
// }
// ArrayAdapter<String> adapter = new ArrayAdapter<String>(ManageActivity.this,
// R.layout.adapter,
// allmess);
// listView.setAdapter(adapter);
SimpleCursorAdapter adapter2 = new SimpleCursorAdapter(ManageActivity.this,
R.layout.adapter2,
cursor,
new String[] {"bookName","bookPrice","bookVersion","bookKind","bookPublisher"},
new int[] {R.id.no1,R.id.no2,R.id.no3,R.id.no4,R.id.no5});
listView.setAdapter(adapter2);
//db.execSQL("select * from BOOK_INFORMATION where bookPublisher='人民邮电出版社'");
}
});
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.manage, menu);
return true;
}
}
扫一扫
195
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
