第九单元 SQLite数据库

SQLite 数据库

student 类

package com.example.afternoon;

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

public class helper extends SQLiteOpenHelper {
    public helper(Context context, String name,SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table student(name varchar(20),age integer(10))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

Activity类

package com.example.afternoon;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Toast;

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

public class MyHelper extends AppCompatActivity {

    private SQLiteDatabase db;
    private List<student> list=new ArrayList<>();
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_my_helper);

        helper helper = new helper(this, "user.db", null, 1);
        db = helper.getReadableDatabase();


    }

    public void click(View view) {
        //通过sql语句插入
//        db.execSQL("insert into student(name,age) values(?,?)",new Object[]{"丁一",25});
        //通过代码插入
        switch( view.getId()){
            case R.id.ap_button:
                ContentValues contentValues = new ContentValues();
                contentValues.put("name","赵四");
                contentValues.put("age",26);
                db.insert("student",null,contentValues);
                break;
            case R.id.ap_button2:
                db.execSQL("update student set name=? where age=?",new Object[]{"刘能",26});
                break;
            case R.id.ap_button3:
                db.execSQL("delete from student where age=?",new String[]{"26"});
                break;
            case R.id.ap_button4:
//                Cursor cursor = db.rawQuery("select * from student where age=?", new String[]{"26"});
//                if( cursor!=null) {
//                    while (cursor.moveToNext()) {
//                        String name = cursor.getString(cursor.getColumnIndex("name"));
//                        int age = cursor.getInt(cursor.getColumnIndex("age"));
//                        Toast.makeText(this, name+"|"+age, Toast.LENGTH_SHORT).show();
//                    }
//                }
                //系统提供的方法
                Cursor cursor = db.query("student", null, null, null, null, null, null);
                if(cursor!=null){
                    while (cursor.moveToNext()) {
                        String name = cursor.getString(cursor.getColumnIndex("name"));
                        int age = cursor.getInt(cursor.getColumnIndex("age"));
                        Toast.makeText(this, name+"|"+age, Toast.LENGTH_SHORT).show();

                        student st = new student();
                        st.setName(name);
                        st.setAge(age);
                        list.add(st);
                    }
                }

                break;
        }

    }
}

自定义类继承自SQLiteOpenHelper

package com.example.afternoon;

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

public class helper extends SQLiteOpenHelper {
    public helper(Context context, String name,SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table student(name varchar(20),age integer(10))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

主界面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"
    tools:context=".MyHelper"
    android:orientation="vertical"
    >

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="点击插入一条数据"
        android:onClick="click"
        android:id="@+id/ap_button"
        />
    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="点击更新一条数据"
        android:onClick="click"
        android:id="@+id/ap_button2"
        />
    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="点击删除一条数据"
        android:onClick="click"
        android:id="@+id/ap_button3"
        />
    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="点击查询一条数据"
        android:onClick="click"
        android:id="@+id/ap_button4"
        />

</LinearLayout>