Mobile development technology (Android) -- Experiment 8 use of SQLite database

1, Experimental purpose

  1. Master the method of creating SQLite database;
  2. Master the methods of adding, deleting, modifying and querying SQLite data;
  3. 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;
	}
}

Tags: Java Android Big Data SQLite

Posted on Tue, 23 Nov 2021 17:59:59 -0500 by leena