（原创）写篇手动编写的原生数据库的增删查改的demo

最新推荐文章于 2022-03-02 14:52:44 发布

Joker-L

最新推荐文章于 2022-03-02 14:52:44 发布

阅读量947

点赞数

分类专栏：数据库 database 文章标签：数据库 database

本文链接：https://blog.csdn.net/lnn368/article/details/52123838

版权

数据库同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

database

1 篇文章 0 订阅

订阅专栏

效果图：

1、创建数据库

<pre name="code" class="java">import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicReference;

/**
 * 创建p2p.db数据库文件，并创建p2pParamsTable表
 *
 * @author zhou
 * @since 2016-08-09
 */
public class P2PHelper extends SQLiteOpenHelper {
    private static final AtomicReference<P2PHelper> arHelper = new AtomicReference<>();
    private AtomicInteger mOpenCounter = new AtomicInteger();
    private SQLiteDatabase mWriteDatabase;

    public static P2PHelper instance(Context context) {
        for (; ; ) {
            P2PHelper mHelper = arHelper.get();
            if (mHelper != null) {
                return mHelper;
            }
            mHelper = new P2PHelper(context);
            if (arHelper.compareAndSet(null, mHelper)) {
                return mHelper;
            }
        }
    }

    private P2PHelper(Context context) {
        super(context, "p2p.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table  if not exists params_table (_id integer primary key autoincrement,fileId varchar" +
                ",trackIp varchar,trackId integer,trackPort integer,path varchar" +
                ",noData integer,shareTime integer,size integer,timeout integer,fileMD5 varchar)");
    }

    /**
     * 数据库版本发生变化的回调方法
     *
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table if exists params_table;");
        onCreate(db);
    }

    /**
     * 同步获取数据库写入操作类
     *
     * @return
     */
    public synchronized SQLiteDatabase getWriteDatabase() {
        if (mOpenCounter.incrementAndGet() == 1) {
            mWriteDatabase = getWritableDatabase();
            mWriteDatabase.enableWriteAheadLogging();
        }
        return mWriteDatabase;
    }

    /**
     * 关闭数据库写入操作类
     */
    public synchronized void closeWriteDatabase() {
        if (mOpenCounter.decrementAndGet() == 0) {
            mWriteDatabase.close();

        }
    }
}

2、实现增删查改的dao

<pre name="code" class="java">import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.vsoontech.p2p.P2PParams;

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

/**
 * 数据库dao
 *
 * @author zhou
 * @since 2016-08-09
 */
public class P2PParamsDao {
    private P2PHelper helper;

    public P2PParamsDao(Context context) {
        helper = P2PHelper.instance(context);
    }

    /**
     * 添加数据
     *
     * @param p2PParams
     * @return
     */
    public synchronized long add(P2PParams p2PParams) {
        SQLiteDatabase db = helper.getWriteDatabase();
        ContentValues values = new ContentValues();
        values.put("fileId", p2PParams.fileId);
        values.put("trackIp", p2PParams.trackIp);
        values.put("trackId", p2PParams.trackId);
        values.put("trackPort", p2PParams.trackPort);
        values.put("path", p2PParams.path);
        values.put("noData", p2PParams.noData);
        values.put("shareTime", p2PParams.shareTime);
        values.put("size", p2PParams.size);
        values.put("timeout", p2PParams.timeout);
        values.put("fileMD5", p2PParams.fileMD5);
        long rowID = db.insert("params_table", null, values);
        helper.closeWriteDatabase();
        return rowID;
    }

    /**
     * 判断是否数据已经存在
     *
     * @param fileId
     * @return
     */
    public synchronized boolean isFileIdExist(String fileId) {
        boolean result = false;
        SQLiteDatabase db = helper.getReadableDatabase();
        Cursor cursor = db.query("params_table", null, "fileId=?", new String[]{fileId}, null, null, null);
        if (cursor.moveToNext()) {
            result = true;
        }
        if (!cursor.isClosed())
            cursor.close();
        db.close();
        return result;
    }

    /**
     * 通过fileId删除相应的数据
     *
     * @param fileId
     * @return
     */
    public synchronized boolean delete(String fileId) {
        try {
            SQLiteDatabase db = helper.getWriteDatabase();
            int result = db.delete("params_table", "fileId=?", new String[]{fileId});
            helper.closeWriteDatabase();
            if (result > 0) {
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {
            return false;
        }
    }

    /**
     * 通过fileId更新数据
     *
     * @param p2PParams
     * @return
     */
    public synchronized boolean update(P2PParams p2PParams) {
        try {
            SQLiteDatabase db = helper.getWriteDatabase();
            ContentValues values = new ContentValues();
            values.put("trackIp", p2PParams.trackIp);
            values.put("trackId", p2PParams.trackId);
            values.put("trackPort", p2PParams.trackPort);
            values.put("path", p2PParams.path);
            values.put("noData", p2PParams.noData);
            values.put("shareTime", p2PParams.shareTime);
            values.put("size", p2PParams.size);
            values.put("timeout", p2PParams.timeout);
            int result = db.update("params_table", values, "fileId=?", new String[]{p2PParams.fileId});
            helper.closeWriteDatabase();
            if (result > 0) {
                return true;
            } else {
                return false;
            }

        } catch (Exception e) {
            return false;
        }
    }

    /**
     * 通过fileId查询单条数据
     *
     * @param fileId
     * @return
     */
    public synchronized P2PParams query(String fileId) {
        P2PParams pParams = null;
        try {
            SQLiteDatabase db = helper.getReadableDatabase();
            Cursor cursor = db.query("params_table", null, "fileId=?", new String[]{fileId}, null, null, null);
            if (cursor.moveToNext() && !cursor.isClosed()) {
                pParams = new P2PParams();
                pParams.fileId = cursor.getString(1);
                pParams.trackIp = cursor.getString(2);
                pParams.trackId = cursor.getInt(3);
                pParams.trackPort = cursor.getInt(4);
                pParams.path = cursor.getString(5);
                pParams.noData = cursor.getInt(6);
                pParams.shareTime = cursor.getInt(7);
                pParams.size = cursor.getInt(8);
                pParams.timeout = cursor.getInt(9);
                pParams.fileMD5 = cursor.getString(10);
            }
            if (!cursor.isClosed())
                cursor.close();
            db.close();
        } catch (Exception e) {

        }
        return pParams;
    }

    /**
     * 查询所有数据
     *
     * @return
     */
    public synchronized List<P2PParams> queryAll() {
        List<P2PParams> p2PParamsList = new ArrayList<>();
        try {
            SQLiteDatabase db = helper.getReadableDatabase();
            Cursor cursor = db.query("params_table", new String[]{"_id", "fileId", "trackIp", "trackId", "trackPort"
                    , "path", "noData", "shareTime", "size", "timeout", "fileMD5"}, null, null, null, null, null);
            while (cursor.moveToNext() && !cursor.isClosed()) {
                P2PParams pParams = new P2PParams();
                pParams.fileId = cursor.getString(1);
                pParams.trackIp = cursor.getString(2);
                pParams.trackId = cursor.getInt(3);
                pParams.trackPort = cursor.getInt(4);
                pParams.path = cursor.getString(5);
                pParams.noData = cursor.getInt(6);
                pParams.shareTime = cursor.getInt(7);
                pParams.size = cursor.getInt(8);
                pParams.timeout = cursor.getInt(9);
                pParams.fileMD5 = cursor.getString(10);
                p2PParamsList.add(pParams);
            }
            if (!cursor.isClosed())
                cursor.close();
            db.close();
        } catch (Exception e) {

        }
        return p2PParamsList;
    }

    /**
     * 清除表
     */
    public synchronized void clearTable(String table) {
        String sql = "delete from " + table + ";";
        SQLiteDatabase db = helper.getReadableDatabase();
        db.execSQL(sql);
        revertSeq(table);
        db.close();
    }

    private void revertSeq(String table) {
        String sql = "update sqLite_sequence set seq=0 where name='" + table + "'";
        SQLiteDatabase db = helper.getWriteDatabase();
        db.execSQL(sql);
        helper.closeWriteDatabase();
    }
}

3、调用mainActivity

<pre name="code" class="java">package com.example.lainanzhou.databasedemo;

import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;

import com.example.lainanzhou.databasedemo.db.dao.DatabaseDao;

public class MainActivity extends AppCompatActivity {
    private DatabaseDao mDatabaseDao;
    private int i;
    private final String TAG = this.getClass().getSimpleName();

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDatabaseDao = new DatabaseDao(this);
    }

    //添加一条数据
    public void add(View v) {
        i += 1;
        Log.d(TAG, "" + mDatabaseDao.add("joker" + i, i));
    }

    //更新数据
    public void update(View v) {
        Log.d(TAG, "" + mDatabaseDao.update("joker" + i, i + 100));
    }

    public void query(View v) {
        Log.d(TAG, "" + mDatabaseDao.getUserMoney("joker" + i));
    }

    public void queryAll(View view) {
        Log.d(TAG, "" + mDatabaseDao.findAllUser());
    }

    public void delete(View view) {
        Log.d(TAG, "" + mDatabaseDao.delete("joker" + i));
    }

    public void deleteAll(View view) {
        mDatabaseDao.clearTable();
    }

}

xml布局文件

<pre name="code" class="java"><?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="com.example.lainanzhou.databasedemo.MainActivity">

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="add"
        android:text="添加数据"/>

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="update"
        android:text="更新数据"/>

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="query"
        android:text="查询一条数据"/>

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="queryAll"
        android:text="查询所有数据"/>

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="delete"
        android:text="删除一条数据"/>

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:onClick="deleteAll"
        android:text="删除所有数据"/>
</LinearLayout>

附带源码下载地址：