1, Experimental purpose
- Master the method of creating SQLite database;
- Master the methods of adding, deleting, modifying and querying SQLite data;
- Learn to use debugging tools to view databases and data tables;
2, Experimental content
Create an Android project named "shiyan0801_ professional"_ ××× (name of student) requirements:
(1) Create 2 activities
Create two activities named MainActivity and ManageActivity, and the corresponding layout file names are activity_main.xml,activity_manage.
(2) Interface composition
① Main interface activity_main.xml
Implement user login, including the following controls:
a) Two text boxes are used to display "Administrator" and "password" respectively;
b) Two edit boxes are used to receive the user name and password entered by the user;
c) 1 button with text "login".
<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="user name"
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="dense code"
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="Sign in" />
<TextView
android:id="@+id/TV_show"
android:gravity="center"
android:text="user name: AAA \n Password: 180000"
android:textSize="20sp" />
</TableLayout>
② activity_manage.xml
Implement book information settings, including the following controls:
(a) Five text boxes are used to display the prompt information "Book Name:", "book price", "Book revision", "book category" and "publishing house";
(b) Two edit boxes are used to receive the name and price of books respectively;
© Four radio buttons are used for version selection (version 1, version 2, version 3 and above);
(d)n check boxes for category selection (education, computer, novel, science and technology, inspirational, literature, etc.);
(e) One drop-down list is used for publishing house selection (people's Posts and Telecommunications Publishing House, science publishing house, higher education publishing house, Tsinghua University Publishing House, others, etc.);
(f) There are 4 common buttons, and the text is displayed as add, modify, delete and query respectively;
(g)1 ListView control.
<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="Book name" />
<EditText android:id="@+id/ED_name" />
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Book price" />
<EditText android:id="@+id/ED_prize" />
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Book revision" />
<RadioGroup
android:id="@+id/RadioGroup1"
android:orientation="horizontal" >
<RadioButton
android:id="@+id/radio_1"
android:text="1 edition" />
<RadioButton
android:id="@+id/radio_2"
android:text="2 edition" />
<RadioButton
android:id="@+id/radio_3"
android:text="3 edition" />
<RadioButton
android:id="@+id/radio_3p"
android:text="3 Version above" />
</RadioGroup>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Book category" />
<TableRow android:id="@+id/CB_TR" >
<CheckBox android:text="education" />
<CheckBox android:text="computer" />
<CheckBox android:text="novel" />
</TableRow>
<TableRow android:id="@+id/CB_TR2" >
<CheckBox android:text="Self-Improvement" />
<CheckBox android:text="science and technology" />
<CheckBox android:text="literature" />
</TableRow>
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="press" />
<Spinner
android:id="@+id/spinner"
android:entries="@array/publisher" />
<TableRow>
<Button
android:id="@+id/submit_BT"
android:layout_weight="1"
android:text="add to" />
<Button
android:id="@+id/update_BT"
android:layout_weight="1"
android:text="modify" />
<Button
android:id="@+id/delete_BT"
android:layout_weight="1"
android:text="delete" />
<Button
android:id="@+id/select_BT"
android:layout_weight="1"
android:text="query" />
</TableRow>
<ListView
android:id="@+id/listview"
android:layout_width="match_parent"
android:layout_height="wrap_content" >
</ListView>
</TableLayout>
③ Adapter layout 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) Program function
① DBOpenHelper.java (database helper class)
package com.example.shiyan0801; //(replace this with your own package name)
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
Click the "login" button in MainActivity. If the administrator and password are the student's name and student number respectively, a prompt dialog box will pop up, showing "* * * login succeeded!", and then jump to ManageActivity. Otherwise, a Toast prompt "administrator or password error, please re-enter" will pop up.
package com.example.shiyan0801; //(replace this with your own package name)
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("Tips").setMessage("Login successful").setPositiveButton("determine", 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,"Administrator or password error, please re-enter" , 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
Actions in ManageActivity:
a) Click the "add" button to write the information entered and selected by the user into the database;
b) Click the Modify button to modify the_ The price of the record with id 3 is changed to 100, and the publishing house is changed to "Tsinghua University Publishing House";
c) Click Delete to delete all records with version 1;
d) Click the "query" button to display the records of the publishing house as "people's Posts and Telecommunications Publishing House" in the ListView control.
package com.example.shiyan0801; //(replace this with your own package name)
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, "Insert failed", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(ManageActivity.this, "Insert successful", 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 = 'Tsinghua University Press' where _id=3");
ContentValues values = new ContentValues();
values.put("bookPrice", "100");
values.put("bookPublisher", "tsinghua university press ");
int res = db.update("BOOK_INFORMATION", values, "_id=3", null);
if(res==0){
Toast.makeText(ManageActivity.this, "Update failed", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(ManageActivity.this, "Update succeeded", 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 ='version 1 '");
int res=db.delete("BOOK_INFORMATION", "bookVersion=?", new String[]{"1 edition"});
if(res==0){
Toast.makeText(ManageActivity.this, "Deletion failed", Toast.LENGTH_SHORT).show();
}else{
Toast.makeText(ManageActivity.this, "Delete succeeded", 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()){
// Allmes. Add ("book title:" + cursor.getString(1)+
// "Price:" + cursor.getString(2)+
// "Version:" + cursor.getString(3)+
// "Category:" + cursor.getString(4)+
// "Publisher:" + 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 = 'people's Posts and Telecommunications Publishing House'");
}
});
}
@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;
}
}