android开发_004：从SQLite读取数据到RecyclerView，并实现多表查询

使用RecyclerView要添加依赖，上篇文章有方法：https://blog.csdn.net/zzq1824837536/article/details/80349498

建立数据库  MyDatabaseHelper.java

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

public class MyDatabaseHelper extends SQLiteOpenHelper {
    /*integer 整型，real 浮点型，primary key 主键，autoincrement 自增长，text 文本类型，blob 二进制数，*/
    public static final String CREATE_BOOK="create table Book("
        +"id integer primary key autoincrement,"
            +"author text,"
            +"price real,"
            +"pages integer,"+
            "name text)";
    public static final String CREATE_CATEGORY="create table Category("
            +"id integer primary key autoincrement,"
            +"category_name text,"
            +"category_code integer)";
    private Context mContext;

    public MyDatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
        mContext=context;
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(CREATE_BOOK);
        sqLiteDatabase.execSQL(CREATE_CATEGORY);
        Toast.makeText(mContext,"create success!", Toast.LENGTH_SHORT).show();
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        sqLiteDatabase.execSQL("drop table if exists Book");
        sqLiteDatabase.execSQL("drop table if exists Category");
        onCreate(sqLiteDatabase);
    }

}

RecyclerView 适配布局 book_item.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="wrap_content">

    <TextView
        android:id="@+id/book_id"
        android:gravity="center"
        android:layout_weight="1"
        android:text="序号"
        android:layout_width="0dp"
        android:layout_height="50dp" />
    <TextView
        android:id="@+id/book_name"
        android:gravity="center"
        android:layout_weight="1"
        android:text="书名"
        android:layout_width="0dp"
        android:layout_height="50dp" />
    <TextView
        android:id="@+id/book_author"
        android:gravity="center"
        android:layout_weight="1"
        android:text="作者"
        android:layout_width="0dp"
        android:layout_height="50dp" />
    <TextView
        android:id="@+id/book_pages"
        android:gravity="center"
        android:layout_weight="1"
        android:text="页数"
        android:layout_width="0dp"
        android:layout_height="50dp" />
    <TextView
        android:id="@+id/book_price"
        android:gravity="center"
        android:layout_weight="1"
        android:text="价格"
        android:layout_width="0dp"
        android:layout_height="50dp" />
    <TextView
        android:id="@+id/category_name"
        android:gravity="center"
        android:layout_weight="2"
        android:text="种类"
        android:layout_width="0dp"
        android:layout_height="50dp" />
    <TextView
        android:id="@+id/category_code"
        android:gravity="center"
        android:layout_weight="1"
        android:text="编号"
        android:layout_width="0dp"
        android:layout_height="50dp" />

</LinearLayout>

RecyclerView 布局文件 activity_main.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="vertical">

    <include layout="@layout/book_item">

    </include>

    <android.support.v7.widget.RecyclerView
        android:id="@+id/Recycler_View"
        android:layout_width="match_parent"
        android:layout_height="match_parent" />

</LinearLayout>

显示对象javaBean BookStore.java

public class BookStore {
    private int id;
    private String name;
    private String author;
    private int pages;
    private double price;
    private String category_name;
    private int category_code;

    public BookStore(int id, String name, String author, int pages, double price,String category_name,int category_code){
        this.id=id;
        this.name=name;
        this.author=author;
        this.pages=pages;
        this.price=price;
        this.category_name=category_name;
        this.category_code=category_code;
    }

    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getAuthor() {
        return author;
    }

    public int getPages() {
        return pages;
    }

    public double getPrice() {
        return price;
    }

    public String getCategory_name() {
        return category_name;
    }

    public int getCategory_code() {
        return category_code;
    }
}

RecyclerView适配器  BookAdapter.java

import android.support.v7.widget.RecyclerView;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.TextView;

import java.util.List;

public class BookAdapter extends RecyclerView.Adapter<BookAdapter.ViewHolder>{
    private List<BookStore> mList;

    static class ViewHolder extends RecyclerView.ViewHolder{
        TextView book_id;
        TextView book_name;
        TextView book_author;
        TextView book_pages;
        TextView book_price;
        TextView category_name;
        TextView category_code;
        public ViewHolder(View view){
            super(view);
            book_id=(TextView)view.findViewById(R.id.book_id);
            book_name=(TextView)view.findViewById(R.id.book_name);
            book_author=(TextView)view.findViewById(R.id.book_author);
            book_pages=(TextView)view.findViewById(R.id.book_pages);
            book_price=(TextView)view.findViewById(R.id.book_price);
            category_name=(TextView)view.findViewById(R.id.category_name);
            category_code=(TextView)view.findViewById(R.id.category_code);
        }
    }
    public BookAdapter(List<BookStore>bookStoresList){
        mList=bookStoresList;
    }
    @Override
    public ViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
        View view= LayoutInflater.from(parent.getContext()).inflate(R.layout.book_item,parent,false);
        ViewHolder holder=new ViewHolder(view);
        return holder;
    }

    @Override
    public void onBindViewHolder(ViewHolder holder, int position) {
        BookStore bookStore=mList.get(position);
        holder.book_id.setText(String.valueOf(bookStore.getId()));
        holder.book_name.setText(bookStore.getName());
        holder.book_author.setText(bookStore.getAuthor());
        holder.book_pages.setText(String.valueOf(bookStore.getPages()));
        holder.book_price.setText(String.valueOf(bookStore.getPrice()));
        holder.category_name.setText(bookStore.getCategory_name());
        holder.category_code.setText(String.valueOf(bookStore.getCategory_code()));
    }

    @Override
    public int getItemCount() {
        return mList.size();
    }


}

主页面布局文件activity_main2.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity">

    <Button
        android:id="@+id/Create_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="创建数据库"
        />

    <Button
        android:id="@+id/Add_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="添加数据"
        />

    <Button
        android:id="@+id/Update_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="更新数据"/>

    <Button
        android:id="@+id/Delete_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="删除数据"/>

    <Button
        android:id="@+id/Query_button"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="查询数据"/>
</LinearLayout>

启动活动Main2Activity.java

import android.content.ContentValues;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.view.View;
import android.widget.Button;

public class Main2Activity extends AppCompatActivity {
    private Button bt1,bt2,bt3,bt4,bt5;
    private MyDatabaseHelper databaseHelper;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        databaseHelper=new MyDatabaseHelper(this,"BookStore.db",null,2);
        setContentView(R.layout.activity_main2);
        bt1=(Button)findViewById(R.id.Create_button);
        bt1.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                databaseHelper.getWritableDatabase();//先检测程序中有无这个数据库，如果没有，则创建
            }
        });
        bt2=(Button)findViewById(R.id.Add_button);
        bt2.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                SQLiteDatabase db=databaseHelper.getWritableDatabase();
                ContentValues values=new ContentValues();//获取ContentValues对象
                values.put("name","The Da Code");
                values.put("author","Dan");
                values.put("pages",454);
                values.put("price",46.69);
                db.insert("Book",null,values);//插入第一条数据
                values.clear();

                values.put("name","The Lost Symbol");
                values.put("author","Lost");
                values.put("pages",996);
                values.put("price",82.65);
                db.insert("Book",null,values);//插入第二条数据
                values.clear();

                values.put("name","Thinking");
                values.put("author","Dell");
                values.put("pages",4516);
                values.put("price",100.65);
                db.insert("Book",null,values);//插入第三条数据
                values.clear();

                values.put("category_name","computer");
                values.put("category_code",156);
                db.insert("Category",null,values);
                values.clear();

                values.put("category_name","animal");
                values.put("category_code",456);
                db.insert("Category",null,values);
            }
        });
        bt3=(Button)findViewById(R.id.Update_button);
        bt3.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                SQLiteDatabase db=databaseHelper.getWritableDatabase();
                ContentValues values=new ContentValues();
                values.put("price",10.99);
                db.update("Book",values,"name=?",new String[]{"The Da Code"});
            }
        });
        bt4=(Button)findViewById(R.id.Delete_button);
        bt4.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                SQLiteDatabase db=databaseHelper.getWritableDatabase();
                db.delete("Book","pages > ?",new String[]{"500"});//删除页数超过500的书
            }
        });
        bt5=(Button)findViewById(R.id.Query_button);
        bt5.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
//                SQLiteDatabase db=databaseHelper.getWritableDatabase();//获取数据库对象
//                Cursor cursor=db.query("Book",null,null,null,null,null,null);//查询Book表中的所有数据
//                if(cursor.moveToFirst()){
//                    do{
//                        String name=cursor.getString(cursor.getColumnIndex("name"));
//                        String author=cursor.getString(cursor.getColumnIndex("author"));
//                        int pages=cursor.getInt(cursor.getColumnIndex("pages"));
//                        double price=cursor.getDouble(cursor.getColumnIndex("price"));
//                        Log.d("MainActivity","Book name is:"+name);
//                        Log.d("MainActivity","Book author is:"+author);
//                        Log.d("MainActivity","Book page is:"+pages);
//                        Log.d("MainActivity","Book price is:"+price);
//                    }while(cursor.moveToNext());
//                }
//                cursor.close();
                Intent intent=new Intent(Main2Activity.this,MainActivity.class);
                startActivity(intent);
            }
        });
    }
}

数据显示界面 MainActivity.java

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.RecyclerView;

import java.util.ArrayList;
import java.util.List;

public class MainActivity extends AppCompatActivity {
    private List<BookStore> BookList=new ArrayList<>();
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        initBook();
        RecyclerView recyclerView=(RecyclerView)findViewById(R.id.Recycler_View);
        LinearLayoutManager linearLayoutManager=new LinearLayoutManager(this);
        recyclerView.setLayoutManager(linearLayoutManager);
        BookAdapter bookAdapter=new BookAdapter(BookList);
        recyclerView.setAdapter(bookAdapter);
    }
    private void initBook(){
        String sqlSelect="SELECT * FROM Book INNER JOIN Category ON Book.id=Category.id  ";
        //扫描数据库，将信息放入booklist
        MyDatabaseHelper mdb = new MyDatabaseHelper(this, "BookStore.db", null, 2);//打开数据库
        SQLiteDatabase sd = mdb.getReadableDatabase();//获取数据库
        Cursor cursor=sd.rawQuery(sqlSelect,new String[]{});
        while(cursor.moveToNext()){
            int id=cursor.getInt(cursor.getColumnIndex("id"));
            String name=cursor.getString(cursor.getColumnIndex("name"));
            String author=cursor.getString(cursor.getColumnIndex("author"));
            int pages=cursor.getInt(cursor.getColumnIndex("pages"));
            double price=cursor.getDouble(cursor.getColumnIndex("price"));
            String category_name=cursor.getString(cursor.getColumnIndex("category_name"));
            int category_code=cursor.getInt(cursor.getColumnIndex("category_code"));
            BookStore bookStore=new BookStore(id,name,author,pages,price,category_name,category_code);
            BookList.add(bookStore);
        }
        cursor.close();

    }
}

运行截图：