SQLite数据库的简单介绍

于 2017-02-21 15:52:48 发布
阅读量996 收藏
点赞数
分类专栏： SQLite 文章标签： 数据库 android sqlite
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_33337504/article/details/56285820
版权

SQLite是android已经封装好的数据库，只需要简单的代码就可以实现增删改查。用来做android简易的数据库非常方便，下面简单的介绍下其实现：

首选需要创建数据库，使用SQLiteOpenHelper来实现创建和更新版本

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.linqinen.mho.bean.NormalMaterialPrice;

import java.util.List;
public class SQlite extends SQLiteOpenHelper {

    private final String TAG = "SQlite ";

    public static final String TABLE_NAME = "rank";
    public static int version = 1;

    List<NormalMaterialPrice> mListCache;

    public SQlite(Context context, String name, CursorFactory factory,
                  int version) {
        super(context, TABLE_NAME+".db"/*数据库名*/, null/*factory 一般不用*/, version);
    }
    public SQlite(Context context,List<NormalMaterialPrice> mListCache){
        super(context, TABLE_NAME+".db", null/*factory 一般不用*/, version);
        this.mListCache = mListCache;


    }

    //onCreate用来创建数据库
    @Override
    public void onCreate(SQLiteDatabase db) {

        //当调用了getReadableDataBase或者getWritealbeDatabase函数就会创建数据库
        db.execSQL("create table " + TABLE_NAME + "(_id integer primary key autoincrement , name text , num integer , price integer , isRefresh integer)");
        /**/
        //参数1.表名
                //参数2.指定是否可以为空（尽量不要为空）
                //参数3.要保存的数据
                //key是字段的名字，必须和建表时字段相同

        for (int i = 0; i < mListCache.size(); i++) {
            ContentValues contentValuses = new ContentValues();
            contentValuses.put("name", mListCache.get(i).getName());
            contentValuses.put("num", mListCache.get(i).getNum());
            contentValuses.put("price", mListCache.get(i).getPrice());
            contentValuses.put("isRefresh", mListCache.get(i).getIsRefresh());

            db.insert(SQlite.TABLE_NAME, "name", contentValuses);
        }

    }

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

}

之后创建一个自定义的类，实现增删改查等方法

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.linqinen.mho.bean.NormalMaterialPrice;

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


//数据库的增删改查
public class RankDBhelper{

    private final String TAG = "RankDBhelper";
    private List<NormalMaterialPrice> mListCache;

    private SQlite sqlite;
    private SQLiteDatabase dataBase;
    public RankDBhelper(Context context,List<NormalMaterialPrice> mListCache){
        this.mListCache = mListCache;
        if (sqlite == null) {
            sqlite = new SQlite(context,mListCache);
        }
        dataBase = sqlite.getWritableDatabase();
    }



    public void closeSQLiteDatabase(){
        if (dataBase.isOpen()) {
            dataBase.close();
        }
    }

    public Cursor cursorQuery(){
        String querySql = "select * from " + SQlite.TABLE_NAME;
        return dataBase.rawQuery(querySql, null);
    }

    public void onlyQueryAllData(){

        //cursor游标（迭代器）方法的各个参数意义：1.表名 2.列名 3和4.需要查询的条件                          5和6.其他条件   7排序的方式
        Cursor cursor = dataBase.query(SQlite.TABLE_NAME,null,null,null,null,null,null);
        while (cursor.moveToNext()) {
            NormalMaterialPrice mBean = new NormalMaterialPrice();
            mBean.setName(cursor.getString(cursor.getColumnIndex("name")));
            mBean.setNum(cursor.getString(cursor.getColumnIndex("num")));
            mBean.setPrice(cursor.getString(cursor.getColumnIndex("price")));
            mBean.setIsRefresh(cursor.getInt(cursor.getColumnIndex("isRefresh")));
            mListCache.add(mBean);
        }
//每次游标cursor用完后都需要关闭，否则会报异常
        cursor.close();
    }

    //查询并更新数据
    public void updateData(String oldName,String newName , String num , String price){
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", newName);
        contentValues.put("num", num);
        contentValues.put("price", price);
        contentValues.put("isRefresh", 1);
        dataBase.update(SQlite.TABLE_NAME, contentValues, "name=?", new String[]{oldName});
    }

    /**删除数据库条目*/
    public void deleteData(String oldName){
        dataBase.delete(SQlite.TABLE_NAME,  "name=?", new String[]{oldName});
    }

    //清空数据库存档
    public void resetData(){
        dataBase.delete(SQlite.TABLE_NAME, null, null);
    }

    /**增加单条数据库条目*/
    public void addData(String name , String num , String price){
        ContentValues contentValues = new ContentValues();
        contentValues.put("name", name);
        contentValues.put("num", num);
        contentValues.put("price", price);
        contentValues.put("isRefresh", 1);
        dataBase.insert(SQlite.TABLE_NAME, "name", contentValues);
    }

    /**增加所有数据库条目*/
    public void addAllData(){
        for (int i = 0; i < mListCache.size(); i++) {
            ContentValues contentValues = new ContentValues();
            contentValues.put("name", mListCache.get(i).getName());
            contentValues.put("num", mListCache.get(i).getNum());
            contentValues.put("price", mListCache.get(i).getPrice());
            contentValues.put("isRefresh", mListCache.get(i).getIsRefresh());
            dataBase.insert(SQlite.TABLE_NAME, "name", contentValues);
        }
    }


}

到此数据库搭建完成，下面看看如何使用：

import android.app.AlertDialog;
import android.content.Context;
import android.content.DialogInterface;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.LinearLayoutManager;
import android.support.v7.widget.Toolbar;
import android.view.LayoutInflater;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.EditText;
import android.widget.Spinner;

import java.text.Collator;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Locale;

import butterknife.BindView;
import butterknife.ButterKnife;

public class MainActivity extends AppCompatActivity {

    @BindView(R.id.recyclerView)
    android.support.v7.widget.RecyclerView mRecyclerView;

    public RankDBhelper mDBhelper;
    private String name = "全部";
    private NormalMaterialPriceListAdapter mAdapter;
    public List<NormalMaterialPrice> mListCache = new ArrayList<>();
    private List<NormalMaterialPrice> mList = new ArrayList<>();

    private Context mContext;
    public static final int ADD_ITEM = 0, DELETE_ALL_DATA = 1, FILTRATE_BE_MODIFIED_DATA = 2;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        ButterKnife.bind(this);
        Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
        setSupportActionBar(toolbar);

        mContext = this;
        initData();
    }

/**每次activity，onDestroy的时候，务必关闭数据库，否则容易报异常*/
    @Override
    public void onDestroy() {
        super.onDestroy();
        mDBhelper.closeSQLiteDatabase();
    }

/**初始化数据库*/
    private void initData() {

        mDBhelper = new RankDBhelper(mContext, mListCache);
        mDBhelper.onlyQueryAllData();
        if (mListCache.size() < 1) {
            addRecyclerViewData();
            mDBhelper.addAllData();
        }

        initFirstSpinner();
        initRecyclerView();
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.menu_normal_material_price, menu);

        return true;
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        setOnOptionsItemSelected(item.getItemId());
        return super.onOptionsItemSelected(item);
    }

    public void setOnOptionsItemSelected(int selecte) {
        if (mAdapter != null) {
            switch (selecte) {
                case R.id.addItem:
                    LayoutInflater inflater = (LayoutInflater) mContext.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
                    View view = inflater.inflate(R.layout.inflate_addsqlitedata_normal_material_price, null);
                    final EditText mEtName = (EditText) view.findViewById(R.id.et_name);
                    final EditText mEtNum = (EditText) view.findViewById(R.id.et_num);
                    final EditText mEtMinPrice = (EditText) view.findViewById(R.id.et_minPrice);
                    final EditText mEtMaxPrice = (EditText) view.findViewById(R.id.et_maxPrice);
                    new AlertDialog.Builder(mContext)
                            .setView(view)
                            .setPositiveButton("确定", new DialogInterface.OnClickListener() {
                                @Override
                                public void onClick(DialogInterface dialog, int which) {
                                    mDBhelper.addData(mEtName.getText().toString(), mEtNum.getText().toString(), mEtMinPrice.getText().toString() + "--" + mEtMaxPrice.getText().toString());
                                    mListCache.add(new NormalMaterialPrice(mEtName.getText().toString(),
                                            mEtNum.getText().toString(), mEtMinPrice.getText().toString() + "--" + mEtMaxPrice.getText().toString(),
                                            1
                                    ));
                                    mList.clear();
                                    mList.addAll(mListCache);
                                    mAdapter.notifyDataSetChanged();
                                }
                            })
                            .setNegativeButton("取消", null)
                            .show();
                    break;
                case R.id.deleteAllData:
                    new AlertDialog.Builder(mContext)
                            .setTitle("温馨提示")
                            .setMessage("是否删除所有数据库信息")
                            .setPositiveButton("删除", new DialogInterface.OnClickListener() {
                                @Override
                                public void onClick(DialogInterface dialog, int which) {
                                    mDBhelper.resetData();
                                    mList.clear();
                                    mListCache.clear();
                                    addRecyclerViewData();
                                    mList.addAll(mListCache);
                                    mAdapter.notifyDataSetChanged();
                                }
                            })
                            .setNegativeButton("取消", null)
                            .show();
                    break;
                case R.id.filtrateBeModifiedData:
                    mList.clear();
                    for (int i = 0; i < mListCache.size(); i++) {
                        if (mListCache.get(i).getIsRefresh() == 1) {
                            mList.add(mListCache.get(i));
                        }
                    }
                    mAdapter.notifyDataSetChanged();
                    break;

                default:
                    break;
            }
        }
    }

    private void initRecyclerView() {

        mRecyclerView.setLayoutManager(new LinearLayoutManager(mContext));
        mAdapter = new NormalMaterialPriceListAdapter(mContext, mList);
        mRecyclerView.setAdapter(mAdapter);
        mAdapter.setOnItemLongClickListener(new MyRecyclerViewAdapter.OnItemLongClickListener() {

            @Override
            public void onItemLongClick(View view2, int position) {
                LayoutInflater inflater = (LayoutInflater) mContext.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
                View view = inflater.inflate(R.layout.inflate_addsqlitedata_normal_material_price, null);
                final EditText mEtName = (EditText) view.findViewById(R.id.et_name);
                final EditText mEtNum = (EditText) view.findViewById(R.id.et_num);
                final EditText mEtMinPrice = (EditText) view.findViewById(R.id.et_minPrice);
                final EditText mEtMaxPrice = (EditText) view.findViewById(R.id.et_maxPrice);

                final NormalMaterialPrice mNormalMaterialPrice = mList.get(position);
                mEtName.setText(mNormalMaterialPrice.getName());
                mEtNum.setText(mNormalMaterialPrice.getNum());

                String price[] = mNormalMaterialPrice.getPrice().split("--");
                if (price.length > 0) {
                    mEtMinPrice.setText(price[0]);
                }
                if (price.length > 1) {
                    mEtMaxPrice.setText(price[1]);
                }

                new AlertDialog.Builder(mContext)
                        .setView(view)
                        .setPositiveButton("确定", new DialogInterface.OnClickListener() {
                            @Override
                            public void onClick(DialogInterface dialog, int which) {

                                mDBhelper.updateData(mNormalMaterialPrice.getName(), mEtName.getText().toString(), mEtNum.getText().toString(), mEtMinPrice.getText().toString() + "--" + mEtMaxPrice.getText().toString());
                                mList.clear();
                                mListCache.clear();
                                mDBhelper.onlyQueryAllData();

                                if (!name.equals("全部")) {
                                    reInitRecyclerViewNameData(name);
                                } else {
                                    mList.addAll(mListCache);
                                    mAdapter.notifyDataSetChanged();
                                }
//                                mAdapter.notifyItemChanged(position);
                            }
                        })
                        .setNeutralButton("删除", new DialogInterface.OnClickListener() {
                            @Override
                            public void onClick(DialogInterface dialog, int which) {
                                mDBhelper.deleteData(mNormalMaterialPrice.getName());
                                mList.clear();
                                mListCache.clear();
                                mDBhelper.onlyQueryAllData();
                                mList.addAll(mListCache);
                                mAdapter.notifyDataSetChanged();
                            }
                        })
                        .setNegativeButton("取消", null)
                        .show();
            }
        });

    }


    private void initFirstSpinner() {
        Spinner spinner = (Spinner) findViewById(R.id.spinner_first);

        final List<String> list = new ArrayList<>();

        for (int i = 0; i < mListCache.size(); i++) {
            list.add(mListCache.get(i).getName());
        }

        /**java中的中文排序逻辑*/
        Collator coll = Collator.getInstance(Locale.CHINESE);
        Collections.sort(list, coll);

        list.add(0, "全部");

        //android.R.layout.simple_spinner_item为系统默认样式
        ArrayAdapter<String> adapter = new ArrayAdapter<>(mContext, android.R.layout.simple_spinner_item, list);

        adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
        spinner.setAdapter(adapter);

        spinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
            @Override
            public void onItemSelected(AdapterView<?> adapterView, View view, int position, long l) {
                name = list.get(position).substring(2);
                if (position == 0) {
                    mList.clear();
                    mList.addAll(mListCache);
                    mAdapter.notifyDataSetChanged();
                } else {
                    reInitRecyclerViewNameData(name);
                }
            }

            @Override
            public void onNothingSelected(AdapterView<?> adapterView) {

            }
        });
    }

    public void reInitRecyclerViewNameData(String name) {
        mList.clear();
        for (int i = 0; i < mListCache.size(); i++) {
            if (mListCache.get(i).getName().contains(name)) {
                mList.add(mListCache.get(i));
            }
        }
        mAdapter.notifyDataSetChanged();
    }


    private void addRecyclerViewData() {
        mListCache.add(new NormalMaterialPrice("土蜂幼虫",
                "1", "98--198", 0
        ));
        mListCache.add(new NormalMaterialPrice("杀人蜂幼虫",
                "1", "98--222", 0
        ));
        mListCache.add(new NormalMaterialPrice("山青虫",
                "1", "99--133", 0
        ));
        mListCache.add(new NormalMaterialPrice("蓝闪蝶",
                "1", "520--655", 0
        ));
    }

}

Demo下载地址https://github.com/linqinen708/SQLite.git

这个是之前看到的SQlite写的非常详细的文章，大家可以参考下，非常推荐 http://blog.csdn.net/codeeer/article/details/30237597/

冰封砺雪
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值