数据库操作，BaseAdapter适配器

listView设置适配器BaseAdapter

通过api方式操作数据库

testTransaction事务回滚

listView设置适配器BaseAdapter

package com.heima.sqlitedemo;
 
import java.util.List;
 
import com.heima.dao.PersonDao;
import com.heima.entites.Person;
 
import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.ListView;
import android.widget.TextView;
 
public class Sqlite_DBActivity extends Activity {
    private List<Person> personList;
 
	/** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main1);
        
        ListView listView = (ListView) findViewById(R.id.listview);
        
        PersonDao dao = new PersonDao(this);
        personList = dao.getAll();
        //把view层对象ListVew和控制器BaseAdapter关联起来
        listView.setAdapter(new MyAdapter());
    }
    
    //关联起来之后就会调用适配器的四个方法   主要是getCount和getView
    class MyAdapter extends BaseAdapter{
 
    	//定义ListView的数据的长度
		@Override
		public int getCount() {
			// TODO Auto-generated method stub
			return personList.size();
		}
 
		@Override
		public Object getItem(int position) {
			// TODO Auto-generated method stub
			return null;
		}
 
		@Override
		public long getItemId(int position) {
			// TODO Auto-generated method stub
			return 0;
		}
 
		//此方法返回的是ListView列表中某一行的View对象   ListView就是一行一行的TextView
		//position 当前返回的view的索引位置
		//converView缓存对象    例如往下拉时可以把上面的view对象缓存起来
		//parent就是ListView对象    一般用不到
		@Override
		public View getView(int position, View convertView, ViewGroup parent) {
			// TODO Auto-generated method stub
			//这里的上下文是MyAdapter，所以不能直接this，this是Sqlite_DBActivity
			TextView tv = null;
			Log.i("adapter", "textview: " + position);
			
			if(convertView != null){ //判断缓存对象是否为null,不为空时已经缓存了对象
				tv = (TextView) convertView;
			}else{ //等于null说明第一次显示，则TextView都是新创建的
				tv = new TextView(Sqlite_DBActivity.this);
			}
			//不使用缓存的话，就是每一次都new一个TextView   目的是只有几个TextView不断复用
			
			tv.setTextSize(16);
			
			Person person = personList.get(position);
			tv.setText(person.toString());
			
			return tv;
		}
    	
    }
}

package com.heima.sqlitedemo.db;
 
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
 
//继承的是数据库帮助类，用于创建和管理数据库的
public class personSQLITEOpenHelper extends SQLiteOpenHelper {
 
	//只剩下Context   其他都删掉
	public personSQLITEOpenHelper(Context context) {
		//第一参数   上下文
		//第二参数   数据库名字
		//第三参数   游标集   可以自定义，默认使用已有的。。一般都是默认
		//第四参数   版本号   至少从1开始
		super(context, "heima_Db", null, 2);
		// TODO Auto-generated constructor stub
	}
 
	//数据库第一次创建时会调用这个方法
	//一般用来初始化一些表
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		//创建表
		String sql = "create table person(_id integer primary key,name varchar(20),age integer);";
		//执行sql语句
		db.execSQL(sql);
	}
 
	
	//数据库的版本号更新时会调这个方法
	//更新数据库的内容   对表进行增删改查
	//一运行就更新    上面构造函数的版本号必须修改
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
		if(oldVersion == 1 && newVersion == 2){ //在person表中添加一个余额列balance
			Log.i("alter", "alter");
			db.execSQL("alter table person add balance integer;");
			
		}
		
	}
 
}

package com.heima.dao;
 
import java.util.ArrayList;
import java.util.List;
 
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
 
import com.heima.entites.Person;
import com.heima.sqlitedemo.db.personSQLITEOpenHelper;
 
public class PersonDao {
	private personSQLITEOpenHelper mOpenHelper;
 
	public PersonDao(Context context){
		mOpenHelper = new personSQLITEOpenHelper(context);
	}
	
	//添加person数据  
	public void insert(Person person){
		//获取只写的数据库
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		//判断数据库是否打开
		if(db.isOpen()){//如果打开就执行添加的操作
			
			//执行添加操作
			//拼接字符串
//			db.execSQL("insert into person(name, age) values('lisi', 19)");
			//可以传递参数的方式      这样还可以防止sql注入
			db.execSQL("insert into person(name, age) values(?,?)", new Object[]{person.getName(),person.getAge()});
			
			//打开就要记得关掉
			db.close();
		}
	}
	
	public List<Person> getAll(){
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		List<Person> personList = new ArrayList<Person>();
		if(db.isOpen()){
			Cursor cursor = db.rawQuery("select _id, name, age from person", null);
			if(cursor!=null && cursor.getCount()>0){
				int id;
				String name;
				int age;
				
				while(cursor.moveToNext()){
					id = cursor.getInt(0);
					name = cursor.getString(1);
					age = cursor.getInt(2);
					personList.add(new Person(id,name,age));
				}
				
			}
			cursor.close();
			db.close();
			return personList;
		}
		
		return null;
	}
}

package com.heima.dao;
 
import java.util.ArrayList;
import java.util.List;
 
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
 
import com.heima.entites.Person;
import com.heima.sqlitedemo.db.personSQLITEOpenHelper;
 
public class PersonDao2 {
	private personSQLITEOpenHelper mOpenHelper;
 
	public PersonDao2(Context context){
		mOpenHelper = new personSQLITEOpenHelper(context);
	}
	
	//添加person数据  
	public void insert(Person person){
		//获取只写的数据库
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		//判断数据库是否打开
		if(db.isOpen()){//如果打开就执行添加的操作
			
			ContentValues values = new ContentValues();
			values.put("name", person.getName());
			values.put("age", person.getAge());
			//id是影响行   也就是插在那一行的那个主键
			//第二参数是当values为空的时候用来说明那个列的字段是null
			//db.insert("person","name",null);
			long id = db.insert("person", null, values);
			Log.i("insert", id+"");
			
			db.close();
		}
	}
	
	public void delete(int id){
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		if(db.isOpen()){
			String whereClause = "id = ?";  //把where等关键字都去掉就是了
			String[] whereArgs = {id+""};
			int count = db.delete("person", whereClause, whereArgs);
			Log.i("delete", count+"");
			db.close();
		}
	}
	
	public void update(int id,String name){
		SQLiteDatabase db = mOpenHelper.getWritableDatabase();
		if(db.isOpen()){
			ContentValues values = new ContentValues();
			values.put("name", name);
			String whereClause = "id = ?";
			String[] whereArgs = {id+""};//一定要String类型？
			int count = db.update("person", values, whereClause, whereArgs);
			Log.i("update", count+"");
			db.close();
		}
	}
	
	public List<Person> queryAll(){
		SQLiteDatabase db = mOpenHelper.getReadableDatabase();
		if(db.isOpen()){
			
			//罗列出要选出的字段
			String[] columns = {"_id","name","age"};
			String selection = null;  //选择查询条件   null为查询所有
			String[] selectionArgs = null; //选择条件参数  替代？的值
			String groupBy = null; //分组语句   group by name
			String having = null; //过滤语句
			String orderBy = null; //排序语句
			Cursor cursor = db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy);
			List<Person> personList = new ArrayList<Person>();
			Person person = null;
			if(cursor!=null && cursor.getCount()>0){
				int id;
				String name;
				int age;
				while(cursor.moveToNext()){
					id = cursor.getInt(0);
					name = cursor.getString(1);
					age = cursor.getInt(2);
					person = new Person(id,name,age);
					personList.add(person);
				}
			}
			//cursor用完之后一定要close，这是结果集，如果没关闭会一直存在，时间久了就是内存溢出
			cursor.close();
			db.close();
			return personList;
			
		}
		
		return null;
	}
	
}

package com.heima.entites;
 
public class Person {
 
	private int id;
	private String name;
	private int age;
	public Person() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Person(int id, String name, int age) {
		super();
		this.id = id;
		this.name = name;
		this.age = age;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	@Override
	public String toString() {
		return "Person [id=" + id + ", name=" + name + ", age=" + age + "]";
	}
	
}

通过test测试数据库是否创建成功

    需要配置清单加上配置

testTransaction测试事务回滚

package com.heima.test;
 
import java.util.List;
 
import com.heima.dao.PersonDao;
import com.heima.dao.PersonDao2;
import com.heima.entites.Person;
import com.heima.sqlitedemo.db.personSQLITEOpenHelper;
 
import android.database.sqlite.SQLiteDatabase;
import android.provider.OpenableColumns;
import android.test.AndroidTestCase;
import android.util.Log;
 
public class test extends AndroidTestCase {
 
	public void test(){
		//什么时候创建
		personSQLITEOpenHelper openHelper = new personSQLITEOpenHelper(getContext());//android提供的上下文
		//这里开始创建数据库     在data/data下多了一个database的文件夹     onCreate会被调用
		openHelper.getReadableDatabase();  
	}
	
	public void testInsert(){
		PersonDao dao = new PersonDao(getContext());
		dao.insert(new Person(0,"zhangsan",23));
		dao.insert(new Person(0,"lisi",23));
		dao.insert(new Person(0,"wangwu",23));
	}
	public void testGetAll(){
		PersonDao dao = new PersonDao(getContext());
		
		List<Person> personList = dao.getAll();
		
		for(Person person : personList){
			Log.i("test", person.getName());
		}
	}
	public void testQueryAll(){
		PersonDao2 dao = new PersonDao2(getContext());
		List<Person> personList = dao.queryAll();
		for(Person person : personList){
			Log.i("test", person.getName());
		}
	}
	
	public void testTransaction(){
		personSQLITEOpenHelper openHelper = new personSQLITEOpenHelper(getContext());
		SQLiteDatabase db = openHelper.getWritableDatabase();
		if(db.isOpen()){
			try {
				//开始事务
				db.beginTransaction();
				db.execSQL("update person set balance = balance - 1000 where name = 'zhangsan';");
				db.execSQL("update person set balance = balance + 1000 where name = 'lisi';");
				db.close();
				//标记事务成功    可以写多次，相当于回滚点
				db.setTransactionSuccessful();
			} catch (Exception e) {
				// TODO: handle exception
				//停止事务
				db.endTransaction();
				//不需要回滚，因为如果没有执行到事务成功的标志，那么就会自动回滚
			}
		}
	}
}