android连接本地数据库sqlite，实现增删改查

1.android连接本地数据库需要继承SQLiteOpenHelper类如下：

 

package com.demo.safetydrill.db;

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


/**
 * 初始化数据库
 */
public class DBHelper extends SQLiteOpenHelper {

    //创建用户表
    private static final String CREATE_USER = "create table user(id integer primary key autoincrement, user_name text not null, phone text not null, role text not null, password text not null, create_date text not null);";
    //创建项目
    private static final String CREATE_PROJECT = "create table project(id integer primary key autoincrement, title text not null, des1 text not null, des2 text not null, video text not null, state text not null, evaluate1 text not null, evaluate2 text not null, evaluate3 text not null, companyId text not null, create_date text not null);";

    //数据库版本
    public static final int DATABASE_VERSION = 1;
    //数据库名
    public static final String DATABSE_NAME = "safety.db";

    public DBHelper(Context context) {
        super(context, DATABSE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_USER);
        db.execSQL(CREATE_PROJECT);

    }

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

2.创建实体类如用户类：

package com.demo.safetydrill.bean;

import java.io.Serializable;

//用户实体类
public class UserBean implements Serializable {
    //主键
    private int id;
    //用户姓名
    private String user_name;
    //手机号
    private String phone;
    //角色（0企业，1管理员，2专家）
    private String role;
    //密码
    private String password;
    //创建时间
    private String create_date;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getRole() {
        return role;
    }

    public void setRole(String role) {
        this.role = role;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getCreate_date() {
        return create_date;
    }

    public void setCreate_date(String create_date) {
        this.create_date = create_date;
    }

    @Override
    public String toString() {
        return "UserBean{" +
                "id=" + id +
                ", user_name='" + user_name + '\'' +
                ", phone='" + phone + '\'' +
                ", role='" + role + '\'' +
                ", password='" + password + '\'' +
                ", create_date='" + create_date + '\'' +
                '}';
    }
}

3.实现增删改查功能类

package com.demo.safetydrill.db;

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

import com.demo.safetydrill.MyApp;
import com.demo.safetydrill.bean.UserBean;

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

public class UserDao {

    //添加
    public static boolean add(UserBean bean) {
        DBHelper helper = new DBHelper(MyApp.mApp);
        SQLiteDatabase db = helper.getReadableDatabase();
        ContentValues values = new ContentValues();
        values.put("user_name", bean.getUser_name());
        values.put("create_date", bean.getCreate_date());
        values.put("phone", bean.getPhone());
        values.put("role", bean.getRole());
        values.put("password", bean.getPassword());
        long result = db.insert("user", null, values);
        db.close();
        helper.close();
        boolean f = false;
        if (result > 0) {
            f = true;
        }
        return f;
    }

    //更新
    public static boolean update(UserBean bean) {
        DBHelper helper = new DBHelper(MyApp.mApp);
        SQLiteDatabase db = helper.getReadableDatabase();
        ContentValues values = new ContentValues();
        values.put("user_name", bean.getUser_name());
        values.put("create_date", bean.getCreate_date());
        values.put("phone", bean.getPhone());
        values.put("role", bean.getRole());
        values.put("password", bean.getPassword());
        int result = db.update("user", values, "id=?", new String[]{String.valueOf(bean.getId())});
        db.close();
        helper.close();
        boolean f = false;
        if (result > 0) {
            f = true;
        }
        return f;
    }

    //删除
    public static boolean delete(String id) {
        DBHelper helper = new DBHelper(MyApp.mApp);
        SQLiteDatabase db = helper.getWritableDatabase();
        int result = db.delete("user", "id=?", new String[]{id});
        db.close();
        helper.close();
        boolean f = false;
        if (result > 0) {
            f = true;
        }
        return f;
    }

    //获取所有数据
    public static List<UserBean> getList() {
        try {
            DBHelper helper = new DBHelper(MyApp.mApp);
            SQLiteDatabase db = helper.getWritableDatabase();
            List<UserBean> list = new ArrayList<>();
            Cursor cursor = db.query("user", null, null, null, null, null,
                    null);
            while (cursor.moveToNext()) {
                UserBean bean = new UserBean();
                bean.setId(getInt(cursor, "id"));
                bean.setUser_name(getString(cursor, "user_name"));
                bean.setCreate_date(getString(cursor, "create_date"));
                bean.setPhone(getString(cursor, "phone"));
                bean.setPassword(getString(cursor, "password"));
                bean.setRole(getString(cursor, "role"));
                list.add(bean);
            }
            db.close();
            helper.close();
            if (!list.isEmpty()) {
                return list;
            }
        } catch (Exception e) {

        }
        return null;
    }

    //获取所有数据
    public static UserBean getBeanByPhone(String phone,String role) {
        try {
            DBHelper helper = new DBHelper(MyApp.mApp);
            SQLiteDatabase db = helper.getWritableDatabase();
            Cursor cursor = db.query("user", null, "phone='" + phone+"' and role='" + role+"'", null, null, null,
                    null);
            UserBean bean = null;
            if (cursor.moveToNext()) {
                bean = new UserBean();
                bean.setId(getInt(cursor, "id"));
                bean.setUser_name(getString(cursor, "user_name"));
                bean.setCreate_date(getString(cursor, "create_date"));
                bean.setPhone(getString(cursor, "phone"));
                bean.setPassword(getString(cursor, "password"));
                bean.setRole(getString(cursor, "role"));
            }
            db.close();
            helper.close();
            return bean;
        } catch (Exception e) {
            Log.e("Exception->",e.getMessage(),e);
        }
        return null;
    }

    private static int getInt(Cursor cursor, String name) {
        return cursor.getInt(cursor.getColumnIndex(name));
    }

    private static String getString(Cursor cursor, String name) {
        return cursor.getString(cursor.getColumnIndex(name));
    }
}

4.其他就可以调用数据库的增删改查功能写业务逻辑了。

如下给出一个自己写的demo

大致界面如下：

项目下载地址：android连接本地数据库的小项目，安全演练-Android文档类资源-CSDN下载