内容提供者-复习android数据库的知识

内容提供者-复习android数据库的知识

• 创建数据库
• 编写数据库DAO
• 编写数据库测试类

android数据库的课程传送门

android sqlite数据库课程

创建数据库

package com.sunofbeaches.contentproviderdemo.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.annotation.Nullable;

public class UserDatabaseHelper extends SQLiteOpenHelper {

    public static final String DB_NAME = "provider.db";
    public static final int DB_VERSION = 1;
    public static final String TABLE_NAME_USER = "user";
    public static final String FIELD_USER_NAME = "user_name";
    public static final String FIELD_SEX = "sex";
    public static final String FIELD_ID = "_id";
    public static final String FIELD_AGE = "age";
    public static final String FIELD_PASSWORD = "password";

    public UserDatabaseHelper(@Nullable Context context) {
        super(context,DB_NAME,null,DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //用户名，密码，性别，年龄
        //创建数据库
        String createSql = "create table " +
                TABLE_NAME_USER +
                "(" + FIELD_ID +
                " integer primary key autoincrement," +
                FIELD_USER_NAME +
                " varchar(30)," +
                FIELD_PASSWORD +
                " varchar(32)," +
                FIELD_SEX + " " +
                "varchar(5)," +
                FIELD_AGE +
                " integer)";
        db.execSQL(createSql);
    }

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

    }
}

编写数据库DAO

接口：

package com.sunofbeaches.contentproviderdemo.dao;

import com.sunofbeaches.contentproviderdemo.pojo.User;

import java.util.List;

public interface IUserDao {

    /**
     * 添加数据
     *
     * @param user
     * @return
     */
    long addUser(User user);

    /**
     * 删除数据
     *
     * @param id
     * @return
     */
    int delUserById(int id);

    /**
     * 更新数据
     *
     * @param user
     * @return
     */
    int updateUser(User user);

    /**
     * 查询数据
     *
     * @param id
     * @return
     */
    User getUserById(int id);

    /**
     * 列出数据
     *
     * @return
     */
    List<User> listUser();
}

实现类：

package com.sunofbeaches.contentproviderdemo.dao;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

import com.sunofbeaches.contentproviderdemo.db.UserDatabaseHelper;
import com.sunofbeaches.contentproviderdemo.pojo.User;

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

public class UserDao implements IUserDao {

    private final UserDatabaseHelper mUserDatabaseHelper;

    public UserDao(Context context) {
        mUserDatabaseHelper = new UserDatabaseHelper(context);
    }

    @Override
    public long addUser(User user) {
        SQLiteDatabase writableDatabase = mUserDatabaseHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(UserDatabaseHelper.FIELD_AGE,user.getAge());
        values.put(UserDatabaseHelper.FIELD_PASSWORD,user.getPassword());
        values.put(UserDatabaseHelper.FIELD_SEX,user.getSex());
        values.put(UserDatabaseHelper.FIELD_USER_NAME,user.getUserName());
        long result = writableDatabase.
                insert(UserDatabaseHelper.TABLE_NAME_USER,null,values);
        writableDatabase.close();
        return result;
    }

    @Override
    public int delUserById(int id) {
        SQLiteDatabase readableDatabase = mUserDatabaseHelper.getReadableDatabase();
        int result = readableDatabase.delete(UserDatabaseHelper.
                TABLE_NAME_USER,UserDatabaseHelper.FIELD_ID,new String[]{id + ""});
        readableDatabase.close();
        return result;
    }

    @Override
    public int updateUser(User user) {
        SQLiteDatabase readableDatabase = mUserDatabaseHelper.getReadableDatabase();
        ContentValues values = new ContentValues();
        if(!TextUtils.isEmpty(user.getPassword())) {
            values.put(UserDatabaseHelper.FIELD_PASSWORD,user.getPassword());
        }
        if(!TextUtils.isEmpty(user.getSex())) {
            values.put(UserDatabaseHelper.FIELD_SEX,user.getSex());
        }
        if(!TextUtils.isEmpty(user.getUserName())) {
            values.put(UserDatabaseHelper.FIELD_USER_NAME,user.getUserName());
        }
        if(user.getAge() != 0) {
            values.put(UserDatabaseHelper.FIELD_AGE,user.getAge());
        }
        int update = readableDatabase.update(mUserDatabaseHelper.TABLE_NAME_USER,values,
                UserDatabaseHelper.FIELD_ID,new String[]{user.get_id() + ""});
        readableDatabase.close();
        return update;
    }

    @Override
    public User getUserById(int id) {
        SQLiteDatabase readableDatabase = mUserDatabaseHelper.getReadableDatabase();
        String queueSql = "select * from " + UserDatabaseHelper.TABLE_NAME_USER + " where " 
                + UserDatabaseHelper.FIELD_ID + " = ?";
        Cursor cursor = readableDatabase.rawQuery(queueSql,new String[]{id + ""});
        User user = null;
        if(cursor.moveToNext()) {
            user = new User();
            int userId = cursor.getInt(cursor.getColumnIndex(UserDatabaseHelper.FIELD_ID));
            user.set_id(userId);
            String userName = cursor.getString(cursor.getColumnIndex(UserDatabaseHelper.FIELD_USER_NAME));
            user.setUserName(userName);
            int userAge = cursor.getInt(cursor.getColumnIndex(UserDatabaseHelper.FIELD_AGE));
            user.setAge(userAge);
            String password = cursor.getString(cursor.getColumnIndex(UserDatabaseHelper.FIELD_PASSWORD));
            user.setPassword(password);
            String sex = cursor.getString(cursor.getColumnIndex(UserDatabaseHelper.FIELD_SEX));
            user.setSex(sex);
        }
        readableDatabase.close();
        return user;
    }

    @Override
    public List<User> listUser() {
        SQLiteDatabase readableDatabase = mUserDatabaseHelper.getReadableDatabase();
        Cursor cursor = readableDatabase.query(UserDatabaseHelper.TABLE_NAME_USER,null,
                null,null,null,null,null,null);
        List<User> users = new ArrayList<>();
        while(cursor.moveToNext()) {
            User user = new User();
            int userId = cursor.getInt(cursor.getColumnIndex(UserDatabaseHelper.FIELD_ID));
            user.set_id(userId);
            String userName = cursor.getString(cursor.getColumnIndex(UserDatabaseHelper.FIELD_USER_NAME));
            user.setUserName(userName);
            int userAge = cursor.getInt(cursor.getColumnIndex(UserDatabaseHelper.FIELD_AGE));
            user.setAge(userAge);
            String password = cursor.getString(cursor.getColumnIndex(UserDatabaseHelper.FIELD_PASSWORD));
            user.setPassword(password);
            String sex = cursor.getString(cursor.getColumnIndex(UserDatabaseHelper.FIELD_SEX));
            user.setSex(sex);
            users.add(user);
        }
        readableDatabase.close();
        return users;
    }
}

编写数据库测试类

package com.sunofbeaches.contentproviderdemo;

import android.content.Context;
import android.support.test.InstrumentationRegistry;
import android.support.test.runner.AndroidJUnit4;
import android.util.Log;

import com.sunofbeaches.contentproviderdemo.dao.UserDao;
import com.sunofbeaches.contentproviderdemo.pojo.User;

import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;

import java.util.List;

import static org.junit.Assert.*;

@RunWith(AndroidJUnit4.class)
public class ExampleInstrumentedTest {
    private static final String TAG = "ExampleInstrumentedTest";
    private UserDao mUserDao;

    @Before
    public void prepare() {
        Context appContext = InstrumentationRegistry.getInstrumentation().getTargetContext();
        mUserDao = new UserDao(appContext);
    }


    @Test
    public void testAddUser() {
        User user = new User();
        user.setAge(32);
        user.setSex("male");
        user.setUserName("sob");
        user.setPassword("34r131");
        long result = mUserDao.addUser(user);
        Log.d(TAG,"result -- > " + result);
        assertNotEquals(-1,result);
    }

    @Test
    public void testUpdateUser() {
        User user = new User();
        user.set_id(2);
        user.setAge(23);
        user.setSex("new female");
        user.setUserName("new zhangsan");
        user.setPassword("new 143123kljaj");
        long result = mUserDao.updateUser(user);
        assertNotEquals(-1,result);
    }


    @Test
    public void testListAllUser() {
        List<User> users = mUserDao.listUser();
        System.out.println("users.size -- > " + users.size());
    }


    @Test
    public void testFineOneUser() {
        User user = mUserDao.getUserById(2);
        System.out.println("user -- > " + user);
    }


    @Test
    public void testDelUser() {
        int result = mUserDao.delUserById(2);
        assertEquals(1,result);
    }
}

数据库路径

/data/data/包名/database/数据库名

导出数据库

用SQLite Expert 打开：

下一篇文章我们将去学习：为什么要使用内容提供者。