内容提供者-复习android数据库的知识
- 创建数据库
- 编写数据库DAO
- 编写数据库测试类
android数据库的课程传送门
创建数据库
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 打开:
下一篇文章我们将去学习:为什么要使用内容提供者。