赵雅智_使用SQLiteDatabase操作SQLite数据库及事务

lang110

　　知识点详解：http://blog.csdn.net/zhaoyazhi2129/article/details/9025995
　　具体代码：

MainActivity.java

package com.example.android_sqlite;
import android.app.Activity;
import android.os.Bundle;
import com.example.android_sqlite.dao.impl.UserDaoImpls;
import com.example.android_sqlite.database.DatabaseHelper;
public class MainActivity extends Activity {
private UserDaoImpls userDaoImpls;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
// 创建数据库
DatabaseHelper dh = new DatabaseHelper(this);
userDaoImpls = new UserDaoImpls(dh);
}
}






Users.java

package com.example.android_sqlite.domain;
import java.io.Serializable;
public class Users implements Serializable{
private static final long serialVersionUID = 1L;
private Integer userId;
private String userName;
private Integer userAge;
private Double userSalary;
public Users() {
super();
}
public Users(Integer userId, String userName, Integer userAge,
Double userSalary) {
super();
this.userId = userId;
this.userName = userName;
this.userAge = userAge;
this.userSalary = userSalary;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public Integer getUserAge() {
return userAge;
}
public void setUserAge(Integer userAge) {
this.userAge = userAge;
}
public Double getUserSalary() {
return userSalary;
}
public void setUserSalary(Double userSalary) {
this.userSalary = userSalary;
}
@Override
public String toString() {
return "Users [userId=" + userId + ", userName=" + userName
+ ", userAge=" + userAge + ", userSalary=" + userSalary + "]";
}
}






BaseDao.java

package com.example.android_sqlite.dao;
import java.util.List;
public interface BaseDao<T, PK> {
/**
* 插入实体对象
*
* @param entity
* @return
*/
public boolean insert(T entity);
/**
* 更新实体对象
*
* @param entity
* @return
*/
public boolean update(T entity);
/**
* 删除实体对象
*
* @param entity
* @return
*/
public boolean delete(T entity);
/**
* 根据id删除对象
*
* @param id
* @return
*/
public boolean deleteByid(PK id);
/**
* 根据id查找对象
*
* @param id
* @return
*/
public T findByid(PK id);
/**
* 查找所有
*
* @return
*/
public List<T> findAll();
}






UserDao.java

package com.example.android_sqlite.dao;
import com.example.android_sqlite.domain.Users;

public interface UserDao extends BaseDao<Users, Integer>{
}






UserDaoImple.java

package com.example.android_sqlite.dao.impl;
import java.util.ArrayList;
import java.util.List;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.example.android_sqlite.dao.UserDao;
import com.example.android_sqlite.database.DatabaseHelper;
import com.example.android_sqlite.domain.Users;
public class UserDaoImpls implements UserDao {
// 依赖对象
private DatabaseHelper dh;
// 通过数据库的构造器实例化
public UserDaoImpls(DatabaseHelper databaseHelper) {
this.dh = databaseHelper;
}
@Override
public boolean insert(Users entity) {
// 得到数据库操作对象
try {
SQLiteDatabase db = dh.getWritableDatabase();
/*
* 方法一
* db.execSQL("insert into users(username,userage,usersalary) values('"
* + entity.getUserName() + "'," + entity.getUserAge() + "," +
* entity.getUserSalary() + ")");
*/
// 方法二
db.execSQL(
"insert into users(username,userage,usersalary) values(?,?,?)",
new Object[] { entity.getUserName(), entity.getUserAge(),
entity.getUserSalary() });
db.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
@Override
public boolean update(Users entity) {
try {
SQLiteDatabase db = dh.getWritableDatabase();
db.execSQL(
"update users set username=?,userage=?,usersalary=? where userid=?",
new Object[] { entity.getUserName(), entity.getUserAge(),
entity.getUserSalary(), entity.getUserId() });
db.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
@Override
public boolean delete(Users entity) {
return deleteByid(entity.getUserId());
}
@Override
public boolean deleteByid(Integer id) {
try {
SQLiteDatabase db = dh.getWritableDatabase();
db.execSQL("delete from users where userid=?", new Object[] { id });
db.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
@Override
public Users findByid(Integer id) {
Users entity = null;
SQLiteDatabase db = dh.getWritableDatabase();
Cursor c = db
.rawQuery(
"select userid,username,userage,usersalary from users where userid=?",
new String[] { id + "" });
if (c.moveToNext()) {
entity = new Users();
entity.setUserId(c.getInt(c.getColumnIndex("userid")));
entity.setUserName(c.getString(c.getColumnIndex("userid")));
entity.setUserAge(c.getInt(c.getColumnIndex("userage")));
entity.setUserSalary(c.getDouble(c.getColumnIndex("usersalary")));
}
return entity;
}
@Override
public List<Users> findAll() {
List<Users> entities = new ArrayList<Users>();
SQLiteDatabase db = dh.getWritableDatabase();
Cursor c = db
.rawQuery(
"select userid,username,userage,usersalary from users",
null);
while (c.moveToNext()) {
Users entity = new Users();
entity.setUserId(c.getInt(c.getColumnIndex("userid")));
entity.setUserName(c.getString(c.getColumnIndex("userid")));
entity.setUserAge(c.getInt(c.getColumnIndex("userage")));
entity.setUserSalary(c.getDouble(c.getColumnIndex("usersalary")));
entities.add(entity);
}
return entities;
}
}






DatabaseTese.java

package com.example.android_sqlite.test;
import java.util.List;
import com.example.android_sqlite.dao.UserDao;
import com.example.android_sqlite.dao.impl.UserDaoImpls;
import com.example.android_sqlite.database.DatabaseHelper;
import com.example.android_sqlite.domain.Users;
import android.test.AndroidTestCase;
public class DatabaseTeses extends AndroidTestCase {
public void createDatabase() {
DatabaseHelper dh = new DatabaseHelper(getContext());
dh.getWritableDatabase();
}
public void insert() {
DatabaseHelper databaseHelper = new DatabaseHelper(getContext());
UserDao userDao = new UserDaoImpls(databaseHelper);
Users entity = new Users(null, "iii2", 20, 4000.0);
boolean flag = userDao.insert(entity);
assertEquals(true, flag);
}
public void update() {
DatabaseHelper databaseHelper = new DatabaseHelper(getContext());
UserDao userDao = new UserDaoImpls(databaseHelper);
Users entity = new Users(7, "aaa", 21, 3000.0);
boolean flag = userDao.update(entity);
assertEquals(true, flag);
}
public void delete() {
DatabaseHelper databaseHelper = new DatabaseHelper(getContext());
UserDao userDao = new UserDaoImpls(databaseHelper);
Users entity = new Users(1, "", null, null);
boolean flag = userDao.delete(entity);
assertEquals(true, flag);
}
public void findById() {
DatabaseHelper databaseHelper = new DatabaseHelper(getContext());
UserDao userDao = new UserDaoImpls(databaseHelper);
Users entity = userDao.findByid(3);
if (entity != null) {
System.out.println(entity.toString());
} else {
System.out.println("no message");
}
}
public void findAll() {
DatabaseHelper databaseHelper = new DatabaseHelper(getContext());
UserDao userDao = new UserDaoImpls(databaseHelper);
List<Users> entities = userDao.findAll();
for (Users entity : entities) {
System.out.println(entity.toString());
}
}
}