listView设置适配器BaseAdapter
通过执行sql语句操作数据库通过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();
//不需要回滚，因为如果没有执行到事务成功的标志，那么就会自动回滚
}
}
}
}
658
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
