[Android exercise] SQLite, ListView, Intent data transmission (comprehensive application)

Article Directory


SQLite, ListView, Intent data transmission comprehensive application

1. Problem description

We have created a database named "Books.db", which contains a table: books table, which stores the basic information of books. The table structure is shown in Table-1:

Insert picture description here


The customized MyDataBaseHelper.java file is in the [resource class] folder, please check it yourself.

package com.example.learning.SQLite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MyDataBaseHelper extends SQLiteOpenHelper {
    // 声明数据库名称
    private static final String DATABASE_NAME = "Books.db";
    // 声明版本号
    private static final int DATABASE_VERSION = 1;
    // 建表语句
    private static final String creat_book = "CREATE TABLE books ( " +
            "_id INTEGER PRIMARY KEY," +
            "book_name TEXT, " +
            "book_isbn TEXT, " +
            "book_author TEXT, " +
            "book_press TEXT, " +
            "book_price NUMBERIC " +
            ");";
    // 三条数据插入语句        
    private static final String insert_book1 = "insert into books(book_name,book_isbn,book_author,book_press,book_price)" +
            " values('Java程序设计','0001-9999-0001','张力','电子工业出版社',36.0)";
    private static final String insert_book2 = "insert into books(book_name,book_isbn,book_author,book_press,book_price)" +
            " values('Android权威指南','0001-8888-0001','王琦','人民出版社',59.0)";
    private static final String insert_book3 = "insert into books(book_name,book_isbn,book_author,book_press,book_price)" +
            " values('SQLite数据库应用指南','0001-6666-0001','李帅','希望出版社',28.0)";
    
    // 构造方法
    public MyDataBaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // 数据库第一次创建的时候就会调用
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(creat_book);
        db.execSQL(insert_book1);
        db.execSQL(insert_book2);
        db.execSQL(insert_book3);
    }

    // 当有版本更新的时候才会调用 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table books");
        onCreate(db);
    }
}

Back to top


2. Demand

Need to create a book name list interface, and then the user clicks to select the book name, the system opens the application of the book detail interface, and requires the book name information to be displayed in a list in an activity, and then query and display the detailed information of a certain book in another activity , The running result is shown in Figure 2 below:

Insert picture description here


Remarks:
(1) The "Book Name List" interface needs to use SimpleCursorAdapter.
(2) The layout file book_list.xml used by MyDataBaseHelper.java and SimpleCursorAdapter has been provided in the "resource class" folder.
(3) The "Book Name List" interface needs to pass the book Id to the "Book Details" interface through an Intent, and the "Book Details" interface then queries the book details based on the Id.
(4) Candidates save the test question files in the directory prompted by the system, and create a folder for each test question, and the folder is named the question number. The test question folder is named "2", and the folder should contain 6 files, namely:

	项目打包压缩文件:App2.zip
	Activity 的 Java 文件(书籍名列表界面):MainActivity.java(继承 ListActivity)
	Activity 的 Java 文件(书籍详细信息界面):BookActivity.java
	Activity 的界面描述文件(书籍详细信息界面):activity_book.xml
	程序运行界面截图:1.png(书籍名列表界面)、2.png(书籍详细信息界面)

Back to top


Three, analysis

Insert picture description here


Back to top


Four, effect display

Insert picture description here

Five, code analysis

Step 1: Query database information

Insert picture description here


① The MyDataBaseHelperclass is clearly given in the body of the question, which is used to create a database and create a bookstable while inserting data. We only need to call it, and pass in the parameter through the parameter structure: the current Activity; when we start the application, super(context, DATABASE_NAME, null, DATABASE_VERSION);create it first The database, and then the MyDataBaseHelper class OnCreate()will be called to create.

So the second step ② We myDataBaseHelperget used to a database operation SQLiteDatabase, examples of getReadableDatabase()the method is to open the database in read-write mode, if the database disk space is full, it will open failed, when the failure will continue to try to open only Open the database in read mode.

③ In this step, we construct the query target column, which is the "in the general query statement" select * from table;"In the asterisk.

④ Querying data in Android is realized through Cursorclasses. When we use SQLiteDatabase.query()the time method, you will get a Cursor object, Cursor points to is that every piece of data. Note that the parameter of the query() method here truemeans de-duplication, only to check the uniqueness, and there are parameter names in the corresponding figure, which is easy to understand.

⑤ Every time we find the data, we are here, traverse to get it, and at the same time encapsulate the data into the entity class corresponding to the table.

⑥ After the queried data is encapsulated in the entity class, we will need to use the queried data later, so we define one ArrayList<Books>to store the data queried each time.

Back to top


Step 2: Adapt the data to the component

Insert picture description here


① We first get Activity1the ListViewcomponents in the middle, and then use them for assembling data

② We call the database query method in the previous step, and the data at this time already exists ArrayList<Books>.

③ Here I am using an ArrayAdapteradapter, so I first ArrayList<Books>take out the title of each book in and put it String[] book_namesin as the data source.

④ Create the ArrayAdapter adapter and configure the String type book name to bind the data, which android.R.layout.simple_list_item_1is a simple list form.

⑤ Finally, do not forget to ArrayAdapterbind the adapter to ListViewcontrol up.

  • The effect after setting the adapter:
Insert picture description here


back to the top


Step 3: Click on item to jump to Activity2 and transfer data

Insert picture description here


ListViewThe itemclick event we added first :setOnItemClickListener()

② Then use to Intentrealize the page jump and transfer data at the same time.

③ First we have to get the data to be transmitted, ListViewthe positionparameter is the trigger event itemnumber, so we can get through this number corresponds to the position of ArrayList<Books>the Booksobject.

④ The Booksobject already contains books info, so we use 可序列化对象and Bundletransmit. ①

⑤ Finally, don't forget to start intent.

Back to top


Step 4: Activity2 obtains the data transmitted by Activity1 and displays the page

Insert picture description here


① We first get the controls of the info display interface

② Get the data transmitted by Activity1 through Intent.

③ Set the corresponding data to the corresponding component for display.

  • Display of results:
Insert picture description here

Back to top


Six, complete code

Two layouts

ListView layout.xml

Insert picture description here
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".SQLite.SQLiteBookActivity">

    <ListView
        android:id="@+id/booksView"
        android:layout_width="match_parent"
        android:layout_height="match_parent" />
</RelativeLayout>

Activity2 layout

Insert picture description here
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".SQLite.InfoActivity"
    android:orientation="vertical">
    <TextView
        android:id="@+id/info_id"
        android:layout_width="143dp"
        android:layout_height="wrap_content"
        android:layout_alignParentStart="true"
        android:layout_alignParentTop="true"
        android:layout_marginStart="5dp"
        android:layout_marginTop="14dp"
        android:layout_weight="1"
        android:text="_id" />
    <TextView
        android:id="@+id/info_name"
        android:layout_width="142dp"
        android:layout_height="wrap_content"
        android:layout_alignParentStart="true"
        android:layout_alignParentTop="true"
        android:layout_marginStart="5dp"
        android:layout_marginTop="57dp"
        android:layout_weight="1"
        android:text="book_name" />
    <TextView
        android:id="@+id/info_isbn"
        android:layout_width="141dp"
        android:layout_height="wrap_content"
        android:layout_alignParentStart="true"
        android:layout_alignParentTop="true"
        android:layout_marginStart="5dp"
        android:layout_marginTop="110dp"
        android:layout_weight="1"
        android:text="book_isbn" />
    <TextView
        android:id="@+id/info_author"
        android:layout_width="142dp"
        android:layout_height="wrap_content"
        android:layout_alignParentStart="true"
        android:layout_alignParentTop="true"
        android:layout_marginStart="5dp"
        android:layout_marginTop="151dp"
        android:layout_weight="1"
        android:text="book_author" />
    <TextView
        android:id="@+id/info_press"
        android:layout_width="141dp"
        android:layout_height="wrap_content"
        android:layout_alignParentStart="true"
        android:layout_alignParentTop="true"
        android:layout_marginStart="5dp"
        android:layout_marginTop="196dp"
        android:layout_weight="1"
        android:text="book_press" />
    <TextView
        android:id="@+id/info_price"
        android:layout_width="142dp"
        android:layout_height="wrap_content"
        android:layout_alignParentStart="true"
        android:layout_alignParentTop="true"
        android:layout_centerHorizontal="true"
        android:layout_marginStart="5dp"
        android:layout_marginTop="241dp"
        android:layout_weight="1"
        android:text="book_privce" />
</RelativeLayout>

Back to top


Database table entity class

package com.example.learning.SQLite;

import java.io.Serializable;

public class Books implements Serializable {
    private Integer _id;
    private String book_name;
    private String book_isbn;
    private String book_author;
    private String book_press;
    private Double book_price;
    public Integer get_id() {
        return _id;
    }
    public void set_id(Integer _id) {
        this._id = _id;
    }
    public String getBook_name() {
        return book_name;
    }
    public void setBook_name(String book_name) {
        this.book_name = book_name;
    }
    public String getBook_isbn() {
        return book_isbn;
    }
    public void setBook_isbn(String book_isbn) {
        this.book_isbn = book_isbn;
    }
    public String getBook_author() {
        return book_author;
    }
    public void setBook_author(String book_author) {
        this.book_author = book_author;
    }
    public String getBook_press() {
        return book_press;
    }
    public void setBook_press(String book_press) {
        this.book_press = book_press;
    }
    public Double getBook_price() {
        return book_price;
    }
    public void setBook_price(Double book_price) {
        this.book_price = book_price;
    }
    @Override
    public String toString() {
        return "Books{" +
                "_id=" + _id +
                ", book_name='" + book_name + '\'' +
                ", book_isbn='" + book_isbn + '\'' +
                ", book_author='" + book_author + '\'' +
                ", book_press='" + book_press + '\'' +
                ", book_price=" + book_price +
                '}';
    }
}

Back to top


Activity1 query data display ListView

package com.example.learning.SQLite;

import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.ListView;
import com.example.learning.R;
import java.io.File;
import java.util.ArrayList;
import java.util.List;

public class SQLiteBookActivity extends AppCompatActivity {

    // 声明组件
    private ListView booksView;
    private List<Books> books = new ArrayList<>();

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_s_q_lite);

        // 获取组件
        booksView = findViewById(R.id.booksView);

        find();
        // 绑定ListView
        // 1.获取ListView展示的书名
        String[] book_names = new String[3];
        for (int i = 0; i < books.size(); i++) {
            book_names[i] = books.get(i).getBook_name();
        }
        // 添加适配器
        ArrayAdapter<String> arrayAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, book_names);
        booksView.setAdapter(arrayAdapter);

        // ListView 点击事件
        booksView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                // 详情页面跳转
                Intent intent = new Intent(SQLiteBookActivity.this, InfoActivity.class);
                // 获取选中的图书对象
                Books book = books.get(position);
                // 传输图书对象
                Bundle bundle = new Bundle();
                bundle.putSerializable("selectedBook",book);
                intent.putExtras(bundle);
                // 启动
                startActivity(intent);
            }
        });
    }

    // 数据库数据查询
    public void find() {
        // 创建 myDataBaseHelper
        MyDataBaseHelper myDataBaseHelper = new MyDataBaseHelper(SQLiteBookActivity.this);
        // 获取可读的数据库对象
        SQLiteDatabase db = myDataBaseHelper.getReadableDatabase();
        String[] columns = {"_id", "book_name", "book_isbn", "book_author", "book_press", "book_price"};
        Cursor cursor = db.query(true, "books", columns, null, null, null, null, null, null);
        // 遍历
        while (cursor.moveToNext()) {
            Books book = new Books();
            book.set_id(cursor.getInt(0));
            book.setBook_name(cursor.getString(1));
            book.setBook_isbn(cursor.getString(2));
            book.setBook_author(cursor.getString(3));
            book.setBook_press(cursor.getString(4));
            book.setBook_price(cursor.getDouble(5));
            books.add(book);
        }
    }
}

Back to top


Activity2 display details

package com.example.learning.SQLite;

import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.os.Bundle;
import android.widget.TextView;
import com.example.learning.R;

public class InfoActivity extends AppCompatActivity {

    // 声明组件
    private TextView info_id,info_name,info_isbn,info_author,info_press,info_price;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_info);
        // 获取组件
        info_id = findViewById(R.id.info_id);
        info_name = findViewById(R.id.info_name);
        info_isbn = findViewById(R.id.info_isbn);
        info_author = findViewById(R.id.info_author);
        info_press = findViewById(R.id.info_press);
        info_price = findViewById(R.id.info_price);
        // 获取到传输的数据
        Intent intent = this.getIntent();
        Bundle bundle = intent.getExtras();
        Books book = (Books) bundle.getSerializable("selectedBook");
        // 设置数据
        info_id.setText(String.valueOf(book.get_id()));
        info_name.setText(book.getBook_name());
        info_isbn.setText(book.getBook_isbn());
        info_author.setText(book.getBook_author());
        info_press.setText(book.getBook_press());
        info_price.setText(String.valueOf(book.getBook_price()));
    }
}

Back to top


supplement

The following code is the SimpleCursorAdapter adapter used, with some changes, you can refer to it, and it is more in line with the original question~

package com.example.Exam.e_2019;

import androidx.appcompat.app.AppCompatActivity;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import com.example.learning.R;
import java.util.ArrayList;
import java.util.List;

public class BookActivity extends AppCompatActivity {
    private ListView list_book;
    private MyDataBaseHelper myDataBaseHelper;
    private SQLiteDatabase db;
    private Cursor cursor;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_book);
        // 获取组件
        list_book = findViewById(R.id.list_book);
        // 获取查询的数据
        List<Books> books = findBooks();
        // 绑定Adapter
        SimpleCursorAdapter simpleCursorAdapter = new SimpleCursorAdapter(
                this,                     // context
                R.layout.activity_item,   // ListView的item布局
                cursor,                   // 查询获取的Cursor对象
                new String[]{"book_name"},// Cursor中的字段值
                new int[]{R.id.textView10},// 将上面的字段值设置到显示的item中的控件中
                0
        );
        list_book.setAdapter(simpleCursorAdapter);
        // 点击事件
        list_book.setOnItemClickListener(new AdapterView.OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                // 详情页面跳转
                Intent intent = new Intent(BookActivity.this, InfoActivity.class);
                // 获取选中的图书对象
                Books book = books.get(position);
                // 传输图书对象
                Bundle bundle = new Bundle();
                bundle.putSerializable("selectedBook",book);
                intent.putExtras(bundle);
                // 启动
                startActivity(intent);
            }
        });
    }
    // 定义方法查询数据库信息
    public List<Books> findBooks() {
        // 定义集合存储查到的图书
        List<Books> books = new ArrayList<>();
        // 创建DBHelper对象创建数据库
        myDataBaseHelper = new MyDataBaseHelper(BookActivity.this);
        // 获取可读数据库对象
        db = myDataBaseHelper.getReadableDatabase();
        // 声明查询的字段
        String[] columns = {"_id", "book_name", "book_isbn", "book_author", "book_press", "book_price"};
        cursor = db.query(true, "books", columns, null, null, null, null, null, null, null);
        // 遍历获取
        while (cursor.moveToNext()) {
            Books book = new Books();
            book.set_id(cursor.getInt(0));
            book.setBook_name(cursor.getString(1));
            book.setBook_isbn(cursor.getString(2));
            book.setBook_author(cursor.getString(3));
            book.setBook_press(cursor.getString(4));
            book.setBook_price(cursor.getDouble(5));
            books.add(book);
        }
        return books;
    }
}

Reference: SimpleCursorAdapter of Adapter in Android Reference: Instructions and solutions for the error " column'_id
' does not exist" when using CursorAdapter()