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; } }