【Java转Android】29. SQLite数据库存储

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
package nopi.aystudio.mthread;

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

public class DatabaseHelper extends SQLiteOpenHelper {

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

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_BOOK);
        db.execSQL(CREATE_CATEGORY);
        Toast.makeText(mContent, "数据库表创建成功", Toast.LENGTH_SHORT).show();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table if exists Book");
        db.execSQL("drop table if exists Category");
        onCreate(db);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
package nopi.aystudio.mthread;

import android.app.Notification;
import android.app.NotificationManager;
import android.app.PendingIntent;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.BitmapFactory;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.text.TextUtils;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xmlpull.v1.XmlPullParser;
import org.xmlpull.v1.XmlPullParserException;
import org.xmlpull.v1.XmlPullParserFactory;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.StringReader;
import java.util.List;
import java.util.SortedMap;

import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParserFactory;

import okhttp3.OkHttpClient;
import okhttp3.Request;
import okhttp3.Response;

public class MainActivity extends AppCompatActivity implements View.OnClickListener{

    Button button;
    Button btn_add;
    Button btn_read;
    Button btn_del;
    Button btn_update;
    private DatabaseHelper databaseHelper;
    private static final String TAG = "MainActivity";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        button = findViewById(R.id.btn_get);
        btn_add = findViewById(R.id.btn_add);
        btn_read = findViewById(R.id.btn_read);
        btn_del = findViewById(R.id.btn_del);
        btn_update = findViewById(R.id.btn_update);
        button.setOnClickListener(this);
        btn_del.setOnClickListener(this);
        btn_update.setOnClickListener(this);
        btn_read.setOnClickListener(this);
        btn_add.setOnClickListener(this);
        databaseHelper = new DatabaseHelper(this,"BookStore.db",null,2);
    }

    @Override
    public void onClick(View v) {
        SQLiteDatabase db = databaseHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        switch (v.getId()) {
            case R.id.btn_get:
                break;
            case R.id.btn_add:
                values.put("name","Tom Menu");
                values.put("author","Tom");
                values.put("pages","454");
                values.put("price",16.96);
                db.insert("Book",null,values);
                values.clear();
                values.put("name","Alice Menu");
                values.put("author","Alice");
                values.put("pages","510");
                values.put("price",19.95);
                db.insert("Book",null,values);
                break;
            case R.id.btn_read:
                Cursor cursor = db.query("Book",null,null,null,null,null,null);
                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(TAG, "onClick: "+name+"=========="+author+"======="+pages+"======="+price);
                    }while (cursor.moveToNext());
                }
                cursor.close();
                break;
            case R.id.btn_del:
                db.delete("Book","pages > ?",new String[]{"500"});
                break;
            case R.id.btn_update:
                values.put("price",10.99);
                db.update("Book",values,"author = ?",new String[]{"Tom"});
                break;
            default:
                break;
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<?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:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity">

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

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

    <Button
        android:id="@+id/btn_read"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="读取数据"/>

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

    <Button
        android:id="@+id/btn_update"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="修改数据"/>

</LinearLayout>