一：创建数据库
package com.itcode.mysqlite;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;/** * 继承SQLiteOpenHelper，创建数据库 * @author Administrator * */public class MySQLiteOpenHelper extends SQLiteOpenHelper{//一：声明构造函数/** * @param context 当前应用的环境，用来确定数据库目录 * @param name 数据库文件的名字 * @param factory 游标工厂，用来创建结果集对象 * @param version 数据库版本 从1开始 */public MySQLiteOpenHelper(Context context) {super(context, "itcode.db", null, 1);}//二：重写onCreate方法 数据库被创建时调用 public void onCreate(SQLiteDatabase db) {System.out.println("MySQLiteOpenHelper-->onCreate 被调用！");//1.创建数据库db.execSQL("create table account(_id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20),balance INTEGER)");//db.execSQL("CREATE TABLE account(_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20))");// 执行一条SQL语句}//三：重写onUpgreade方法，数据库时调用public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {//System.out.println("MySQLiteOpenHelper-->onUpgrade 被调用");//db.execSQL("alter table account ADD balance integer");//db.execSQL("ALTER TABLE account ADD balance INTEGER");}}二：对数据库进行CRUD操作
package com.itcode.mysqlite;import java.util.ArrayList;import java.util.List;import android.content.Context;import android.database.Cursor;import android.database.SQLException;import android.database.sqlite.SQLiteDatabase;public class AccountDao {//一：创建AccountDao时依据当前context创建数据库private MySQLiteOpenHelper mySQLiteOpenHelper;public AccountDao(Context context){mySQLiteOpenHelper = new MySQLiteOpenHelper(context);}//二：增:传进来一个Account,将此对象加入数据库public int add(Account a){//1.获取相应的(可写的)数据库SQLiteDatabase writableDatabase = mySQLiteOpenHelper.getWritableDatabase();//2.执行语句writableDatabase.execSQL("insert into account(name,balance) values(?,?)", new Object[]{a.getName(),a.getBalance()});//3.获取插入的数据集：Cursor rawQuery = writableDatabase.rawQuery("select _id from account order by _id desc limit 1", null);rawQuery.moveToNext();int id = rawQuery.getInt(rawQuery.getColumnIndex("_id"));//4.关闭数据库return id;}//三：删：根据id删除数据库中相应的数据项public void delete(int id){//1.获取相应的(可写的)数据库SQLiteDatabase writableDatabase = mySQLiteOpenHelper.getWritableDatabase();//2.执行删除语句writableDatabase.execSQL("Delete from account where _id = ?",new Object[]{id});//3.关闭数据库writableDatabase.close();}//四：改：传进来一个Account，根据Account的对象的id修改name 和balancepublic void update(Account a){//1.获取相应的（可写的）数据库SQLiteDatabase writableDatabase = mySQLiteOpenHelper.getWritableDatabase();//2.执行更新语句writableDatabase.execSQL("update account set name=?,balance = ? where _id = ?",new Object[]{a.getName(),a.getBalance(),a.getId()});//3.关闭数据库writableDatabase.close();}//五：查：根据id查询数据库public Account query(int id){//1.获取相应的（可读的）数据库SQLiteDatabase readableDatabase = mySQLiteOpenHelper.getReadableDatabase();//2.执行更新语句Cursor rawQuery = readableDatabase.rawQuery("select * from account where _id=?", new String[]{String.valueOf(id)});//3.将查询到的数据赋值给Account对象Account a = null;while(rawQuery.moveToNext()){Integer balance = Integer.valueOf(rawQuery.getString(rawQuery.getColumnIndex("balance")));String name = rawQuery.getString(rawQuery.getColumnIndex("name"));a = new Account(id,name,balance);}//3.关闭结果集与数据库rawQuery.close();readableDatabase.close();return a;}//六：查询所有，将查询到的所有数据放在list集合中返回public List<Account> queryAll(){List<Account> list = new ArrayList<Account>();Account a = null;//1.获取相应的（可读的）数据库SQLiteDatabase readableDatabase = mySQLiteOpenHelper.getReadableDatabase();Cursor rawQuery = readableDatabase.rawQuery("Select * from account",null);while(rawQuery.moveToNext()){Integer id = Integer.valueOf(rawQuery.getString(rawQuery.getColumnIndex("_id")));String name = rawQuery.getString(rawQuery.getColumnIndex("name"));Integer balance = Integer.valueOf(rawQuery.getString(rawQuery.getColumnIndex("balance")));a = new Account(id,name,balance);list.add(a);}//3.关闭资源rawQuery.close();readableDatabase.close();return list;}//七：分页查询：传入要查询第几页，每页显示几条数据，将查询到的数据放在list集合中返回 public List<Account> queryPage(int pageNum,int pageSize){List<Account> list = new ArrayList<Account>();Account a = null;String index = String.valueOf((pageNum-1)*pageSize);String count = String.valueOf(pageSize);//1.获取相应的（可读的）数据库SQLiteDatabase readableDatabase = mySQLiteOpenHelper.getReadableDatabase();//2.执行相应的查询语句Cursor rawQuery = readableDatabase.rawQuery("select * from account limit ?,?", new String[]{index,count});while(rawQuery.moveToNext()){Integer id = Integer.valueOf(rawQuery.getString(rawQuery.getColumnIndex("_id")));String name = rawQuery.getString(rawQuery.getColumnIndex("name"));Integer balance = Integer.valueOf(rawQuery.getString(rawQuery.getColumnIndex("balance")));a = new Account(id,name,balance);list.add(a);}//3.关闭相应的资源rawQuery.close();readableDatabase.close();return list;}//八：查询总记录条数public int queryCount(){//1.获取相应的（可读的）数据库SQLiteDatabase readableDatabase = mySQLiteOpenHelper.getReadableDatabase();//2.执行相应的语句Cursor rawQuery = readableDatabase.rawQuery("select count(*) from account", null);rawQuery.moveToNext();//不加此句可否？不能去掉！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！int count = rawQuery.getInt(0);//3.关闭资源rawQuery.close();readableDatabase.close();return count;}//九：事物管理 从一个Account对象向另一个Account对象汇款，传入参数，fromid,toId,moneySizepublic void remit(int fromId,int toId,int moneySize){SQLiteDatabase writableDatabase = null;try {//1.获取相应的（可写的）数据库writableDatabase = mySQLiteOpenHelper.getWritableDatabase();//2.开启事物writableDatabase.beginTransaction();//3.执行相应的语句writableDatabase.execSQL("update account set balance=balance-? where _id = ?", new Object[]{moneySize,fromId});writableDatabase.execSQL("update account set balance=balance+? where _id = ?",new Object[]{moneySize,toId});//4.设置成功标记writableDatabase.setTransactionSuccessful();//5.关闭资源writableDatabase.close();} catch (Exception e) {//6.结束事务，会把最后一次成功标记之前的事务提交writableDatabase.endTransaction();e.printStackTrace();}}}三：对CRUD的方法的测试：
package com.itcode.mysqlite;import java.util.List;import java.util.Random;import android.database.sqlite.SQLiteDatabase;import android.test.AndroidTestCase;public class SQLiteTest extends AndroidTestCase{//1.测试创建数据库的方法public void testCreateDB(){//1.创建数据库MySQLiteOpenHelper helper = new MySQLiteOpenHelper(getContext());//2.得到相应的数据库SQLiteDatabase db = helper.getWritableDatabase();db.execSQL("INSERT INTO account(_id,name, balance) VALUES(1,'fuck', 10000)");}//2.测试添加数据的方法public void testAdd(){AccountDao dao = new AccountDao(getContext());// Context android.test.AndroidTestCase.getContext()for(int i=1;i<100;i++){dao.add(new Account("Test" + i,new Random().nextInt(10000)));}}//3.测试删除数据的方法public void testDelete(){AccountDao dao = new AccountDao(getContext());for(int i=100;i<200;i++){dao.delete(i);}}//4.测试修改数据的方法public void testUpdate(){AccountDao dao = new AccountDao(getContext());Account a = new Account(1,"fuck you!!!~",999);dao.update(a);}//5.测试查询数据的方法，根据id查询数据库public void testQuery(){AccountDao dao = new AccountDao(getContext());System.out.println(dao.query(1));System.out.println(dao.query(3));System.out.println(dao.query(4));}//6.测试查询所有的方法public void testQueryAll(){AccountDao dao = new AccountDao(getContext());List<Account> list =null;list = dao.queryAll();for (Account account : list) {System.out.println(account);}}//7.测试分布查询的方法public void testQueryPage(){AccountDao dao = new AccountDao(getContext());List<Account> list = null;list = dao.queryPage(2, 10);for (Account account : list) {System.out.println(account);}}//8.测试查询总记录条数的方法public void testQueryCount(){AccountDao dao = new AccountDao(getContext());int count = dao.queryCount();System.out.println(count);}}
