数据库SQList 添加 查询 修改 删除

SQListActivity

package com.example.administrator.myapplication.database.Activity;

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

import com.example.administrator.myapplication.R;
import com.example.administrator.myapplication.database.MySQLiteOpenHelper;
import com.example.administrator.myapplication.util.Common;

public class SQLiteActivity extends AppCompatActivity {
    Button insertBtn;
    Button queryBtn;
    Button deleteBtn;
    Button updateBtn;
    EditText titleET;
    EditText deleteET;
    EditText updateET;
    String TitleReceiver;
    String deleteReceiver;
    String updateReceiver;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite);
        titleET = (EditText) findViewById(R.id.titleText);
        deleteET = (EditText) findViewById(R.id.deleteText);
        updateET = (EditText) findViewById(R.id.updateText);
        insertBtn = (Button) findViewById(R.id.insertData);
        insertBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                insertData();
            }
        });
        queryBtn = (Button) findViewById(R.id.queryData);
        queryBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                queryData();
            }
        });
        deleteBtn = (Button) findViewById(R.id.deleteData);
        deleteBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                deleteData();
            }
        });
        updateBtn = (Button) findViewById(R.id.updateData);
        updateBtn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                updateData();
            }
        });
    }

    //插入数据
    public void insertData(){
        TitleReceiver = titleET.getText().toString();
        //调用帮助类的构造函数，创建数据库
        MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
        //调用帮助类的onCreate方法，创建表
        SQLiteDatabase sqLiteDatabase = mySQLiteOpenHelper.getWritableDatabase();
        //将保存的数据放入ContentValues中
        ContentValues contentValues = new ContentValues();
        contentValues.put(Common.NewsTable.TBL_TITLE,TitleReceiver);
        contentValues.put(Common.NewsTable.TBL_CONTENT,"内容");
        contentValues.put(Common.NewsTable.TBL_SRC,"图片");
        contentValues.put(Common.NewsTable.TBL_DATE,"日期");
        //调用insert方法，保存数据
        sqLiteDatabase.insert(Common.NewsTable.TBL_NAME,null,contentValues);
        //!!!数据库操作完成后，一定要关闭数据库
        sqLiteDatabase.close();
        Toast.makeText(getApplication(),"保存数据成功！",Toast.LENGTH_SHORT).show();
    }
    //查询数据
    public void queryData(){
        MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
        SQLiteDatabase sqLiteDatabase = mySQLiteOpenHelper.getReadableDatabase();
        //调用SQLiteDatabase的Query方法，返回数据库查询的结果集
        Cursor cursor = sqLiteDatabase.query(
                Common.NewsTable.TBL_NAME,
                new String[]{Common.NewsTable.TBL_TITLE,Common.NewsTable.TBL_CONTENT,Common.NewsTable.TBL_SRC,Common.NewsTable.TBL_DATE},
                null,null,null,null,null
        );
        //！！查询对应数据的时候，一定要现获取列的索引值
        while (cursor.moveToNext()){
            int titleIndex = cursor.getColumnIndex(Common.NewsTable.TBL_TITLE);
            String title = cursor.getString(titleIndex);
            Toast.makeText(getApplication(),title,Toast.LENGTH_SHORT).show();
        }
        //数据查询完毕以后，1:关系结果集 2.关闭SQLiteDataBase
        cursor.close();
        sqLiteDatabase.close();
        Toast.makeText(getApplication(),"查询完毕！",Toast.LENGTH_SHORT).show();
    }
    //删除数据
    private void deleteData() {
        deleteReceiver = deleteET.getText().toString();
        MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
        SQLiteDatabase sqLiteDatabase = mySQLiteOpenHelper.getWritableDatabase();
        sqLiteDatabase.delete(
                Common.NewsTable.TBL_NAME,
                // ?：占位符 " = ? "相当于 " = 标题 ", null
                Common.NewsTable.TBL_TITLE+ " = ?",
                new String[]{deleteReceiver}
        );
        sqLiteDatabase.close();
        Toast.makeText(getApplication(),"删除成功!",Toast.LENGTH_SHORT).show();
    }
    //修改数据
    private void updateData() {
        updateReceiver = updateET.getText().toString();
        MySQLiteOpenHelper mySQLiteOpenHelper = new MySQLiteOpenHelper(this);
        SQLiteDatabase sqLiteDatabase = mySQLiteOpenHelper.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put(Common.NewsTable.TBL_TITLE,"这是修改后的数据");
        sqLiteDatabase.update(
                Common.NewsTable.TBL_NAME,
                contentValues,
                Common.NewsTable.TBL_TITLE + " = ?",
                new String[]{updateReceiver}
        );
        sqLiteDatabase.close();
        Toast.makeText(getApplication(),"修改成功!",Toast.LENGTH_SHORT).show();
    }
}

MySQListOPENHelper   创建数据库 创建表

package com.example.administrator.myapplication.database;

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

import com.example.administrator.myapplication.util.Common;

/**
 * Created by Administrator on 2016-9-19.
 */

//帮助类继承SQLiteOpenHelper，方法：构造函数，创建数据库，创建表，更新表
public class MySQLiteOpenHelper extends SQLiteOpenHelper {
    private static final String DB_Name = "new.db"; //数据库后缀为db，常量用大写
    private static final int VERSION = 1; //数据库版本号
    //上下文，数据库名字，factory默认null,版本号
    /*public MySQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, DB_Name, factory, VERSION);
    }*/
    //实现了数据库的创建，factory默认null,版本号固定
    public MySQLiteOpenHelper(Context context) {
        super(context, DB_Name, null, VERSION);
    }

    //在onCreate方法中创建表，只执行一次
    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = Common.NewsTable.getCreateTableSQL();
        db.execSQL(sql);
    }
    //onUpgrade更新表
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //先删除原表，再调用onCreate创建新表
        db.execSQL("drop table "+ Common.NewsTable.TBL_NAME);
        onCreate(db);
    }
}

Common 公共类

package com.example.administrator.myapplication.util;

/**
 * Created by Administrator on 2016-9-19.
 */
public class Common {
    public static class NewsTable{
        public static final String TBL_NAME ="News";
        public static final String TBL_TITLE = "NewsTitle";
        public static final String TBL_CONTENT = "NewsContent";
        public static final String TBL_SRC = "NewsImg";
        public static final String TBL_DATE = "NewsDate";

        public static String getCreateTableSQL(){
            String sql = "create table if not exists "
                        + TBL_NAME
                        + "("
                        + " _id integer primary key autoincrement,"
                        + TBL_TITLE + " text,"
                        + TBL_CONTENT + " text,"
                        + TBL_SRC + " text,"
                        + TBL_DATE + " varchar(50)"
                        + ")";
            return sql;
        }
    }
}

activity_sqlite

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout 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:orientation="vertical"
    tools:context=".database.Activity.SQLiteActivity">

    <EditText
        android:id="@+id/titleText"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="请输入标题" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="请输入要删除的数据" />

        <EditText
            android:id="@+id/deleteText"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="请输入要修改的数据" />

        <EditText
            android:id="@+id/updateText"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" />
    </LinearLayout>

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

    <Button
        android:id="@+id/queryData"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="查找数据  " />

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

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