SQLite数据库创建，增删查改

转载请注明出处：SQLite数据库创建，增删查改_Mr_Leixiansheng的博客-CSDN博客

相关文章：更快捷操作数据库 LitePal

步骤：

1、新建自己的数据库类继承SQLiteOpenHelper并重写（设置数据库要包含的类别）

2、主程序中实现数据库的创建，和增删查改

1、创建类

package com.example.administrator.sqlitetest;

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

/**
 * Created by Administrator on 2016/12/7.
 */

public class MySQLiteHelper extends SQLiteOpenHelper {

    public final static String DB_NAME = "book.db";
    public final static String BOOK_TABLE = "book";
    public final static String PERSON_TABLE = "person";


    private static final String CREATE_BOOK = "create table " + BOOK_TABLE + " ("       //相当于 ： "create table book (_id integer primary key autoincrement,name text,age integer)"
            + "id integer primary key autoincrement, "
            + "name text, "
            + "age integer)";

    private static final String CREATE_PERSON = "create table " + PERSON_TABLE + " ("
            + "id integer primary key autoincrement, "
            + "name text, "
            + "age integer)";

    private Context mContext;

    public MySQLiteHelper(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_PERSON);
        Toast.makeText(mContext, "CREATE OK", Toast.LENGTH_SHORT).show();
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

2、实现对数据库的操作

package com.example.administrator.sqlite;

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

import static com.example.administrator.sqlitetest.MySQLiteHelper.BOOK_TABLE;
import static com.example.administrator.sqlitetest.MySQLiteHelper.DB_NAME;

import java.util.zip.CRC32;

public class MainActivity extends AppCompatActivity implements View.OnClickListener {

    private MyDatabaseHelper dbHelper;
    //新建,增删改查
    private Button create,insert,delete,change,query;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        //创建book.db数据库对象，版本号1
        dbHelper = new MyDatabaseHelper(this, DB_NAME, null, 1);
        //获取元素ID
        create = (Button) findViewById(R.id.create);
        insert = (Button) findViewById(R.id.insert);
        delete = (Button) findViewById(R.id.delete);
        change = (Button) findViewById(R.id.change);
        query = (Button) findViewById(R.id.query);

        //设置按键点击监听
        create.setOnClickListener(this);
        insert.setOnClickListener(this);
        delete.setOnClickListener(this);
        change.setOnClickListener(this);
        query.setOnClickListener(this);

    }

    @Override
    public void onClick(View view) {
        switch (view.getId()) {
            case R.id.create:
                dbHelper.getWritableDatabase();
                break;
            case R.id.insert:
                insertData();
                Toast.makeText(this, "添加成功", Toast.LENGTH_SHORT).show();
                break;
            case R.id.delete:
                deletData();
                Toast.makeText(this, "删除成功", Toast.LENGTH_SHORT).show();
                break;
            case R.id.change:
                changeDate();
                Toast.makeText(this, "更改成功", Toast.LENGTH_SHORT).show();
                break;
            case R.id.query:
                queryData();
                Toast.makeText(this, "浏览", Toast.LENGTH_SHORT).show();
                break;
            default:
                break;
        }
    }

    private void queryData() {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        //遍历整个表格 Cursor ：光标
        Cursor cursor = db.query(BOOK_TABLE, null, null, null, null, null, null);

        //遍历符合条件的行
//        Cursor cursor = db.rawQuery("select * from book where name='小明'",null);

       //遍历
        while (cursor.moveToNext()) {
            String name = cursor.getString(cursor.getColumnIndex("name"));
            int age = cursor.getInt(cursor.getColumnIndex("age"));
            int id = cursor.getInt(cursor.getColumnIndex("id"));

            //防止空指针异常
            if (name==null) {
                Log.i("MainActivity", "id:"+id+",name:" + "null" + ",age：" + String.valueOf(age));
            } else {
                Log.i("MainActivity", "id:"+id+",name:" + name + ",age：" + String.valueOf(age));
            }
        }
        cursor.close();
    }

    private void deleteData() {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.delete("book","name = ?",new String[] {"小明"});
    }

    private void changeDate() {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put("name", "小黄");

        db.update("book",values, "name = ?",new String[] {"小红"});
    }

    private void insertData() {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put("name", "小明");
        values.put("age", 5);
        db.insert("book", null, values);

        values.clear();

        values.put("name", "小红");
        values.put("age", 10);
        db.insert("book", null, values);

        values.clear();

        values.put("name","老王");
        db.insert("book", null, values);
        values.clear();

        values.put("age",100);
        db.insert("book", null, values);
    }
}

Cursor介绍：

cursor.isClosed();//如果为TRUE表示该游标已关闭  
cursor.close();//关闭游标，且释放资源  
cursor.getColumnCount();//返回所有列的总数  
cursor.getColumnNames();//返回一个字符串数组的列名，即将列名全部返回到一个字符串数组中  
cursor.getCount();//返回Cursor中的行数  
cursor.moveToFirst();//移动光标到第一行  
cursor.moveToLast();//移动光标到最后一行  
cursor.moveToNext();//移动光标到下一行  
cursor.moveToPrevious();//移动光标到上一行  
int columnIndex = 0;  
int position = 0;  
cursor.getColumnName(columnIndex);//从给定的索引返回列名  
cursor.moveToPosition(position);//移动光标到给定位置