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

知识点具体解释：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());
		}
	}

}