数据库查询操作
public class DBhelper extends SQLiteOpenHelper {
public DBhelper(Context context ) {
super(context, "yzadb", null, 3);
//null 默认的系统游标工厂 版本号1不建议0 创建数据库
}
//数据库第一次被创建的时候调用
@Override
public void onCreate(SQLiteDatabase db) {
System.out.println("进入");
//创建表
String sql="create table person(id integer primary key autoincrement," +
" name varchar(50))";
db.execSQL(sql);
}
//数据库版本号变化的时候调用
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//String sql="alter table person add phone varchar(50)";
String sql="alter table person add amount integer";
db.execSQL(sql);
}
}
public class PersonService {
private DBhelper db;
public PersonService(Context c) {
this.db = new DBhelper(c);
}
public void save(Person person) {
SQLiteDatabase d = db.getWritableDatabase();
String sql = "insert into person(name,phone,amount) values(?,?,?)";
d.execSQL(sql, new Object[] { person.getName(), person.getPhone() ,person.getAmount()});
d.close();// 只有一个地方使用可以不关闭
}
public void delete(Integer id) {
SQLiteDatabase d = db.getWritableDatabase();
String sql = "delete from person where id= ?";
d.execSQL(sql, new Object[] { id });
d.close();// 只有一个地方使用可以不关闭
}
public void update(Person person) {
SQLiteDatabase d = db.getWritableDatabase();
String sql = "update person set name = ?,phone=?,amount=? where id=?";
d.execSQL(sql, new Object[] { person.getName(), person.getName(),person.getAmount(),
person.getId() });
d.close();// 只有一个地方使用可以不关闭
}
public Person find(Integer id) {
Person p = new Person();
SQLiteDatabase d = db.getReadableDatabase();
String sql = "select * from person where id=?";
Cursor cur = d.rawQuery(sql, new String[] { id.toString() });
if (cur.moveToFirst()) {
p.setId(cur.getInt(cur.getColumnIndex("id")));
p.setName(cur.getString(cur.getColumnIndex("name")));
p.setPhone(cur.getString(cur.getColumnIndex("phone")));
p.setAmount(cur.getInt(cur.getColumnIndex("amount")));
return p;
}
cur.close();
d.close();// 只有一个地方使用可以不关闭
return null;
}
public List<Person> getpage(int begin, int max) {
List<Person> ps = new ArrayList<Person>();
SQLiteDatabase d = db.getReadableDatabase();
String sql = "select * from person order by id asc limit ?,?";
Cursor cur = d.rawQuery(sql, new String[] { String.valueOf(begin),
String.valueOf(max) });
while(cur.moveToNext()){
Person p = new Person();
p.setId(cur.getInt(cur.getColumnIndex("id")));
p.setName(cur.getString(cur.getColumnIndex("name")));
p.setPhone(cur.getString(cur.getColumnIndex("phone")));
p.setAmount(cur.getInt(cur.getColumnIndex("amount")));
ps.add(p);
}
return ps;
}
public int getCount() {
SQLiteDatabase d = db.getReadableDatabase();
String sql = "select count(*) from person ";
Cursor cur = d.rawQuery(sql, null);
cur.moveToFirst();
int resrult = cur.getInt(0);
return resrult;
}
public void payment() { //事务控制
SQLiteDatabase d = db.getWritableDatabase();
d.beginTransaction();
try {
String sql1 = "update person set amount=amount-1 where id=1 ";
String sql2 = "update person set amount=amount+1 where id=2 ";
d.execSQL(sql1);
d.execSQL(sql2);
d.setTransactionSuccessful();// 设置事务标志为true 不设置则false
} finally {
d.endTransaction();// 结束事务,若事务标志为true则提交 否则回滚
}
}
}
listview显示
main.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:orientation="vertical" >
<LinearLayout
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="horizontal" >
<TextView
android:text="姓名"
android:layout_width="100dp"
android:layout_height="wrap_content" />
<TextView
android:text="电话"
android:layout_width="150dp"
android:layout_height="wrap_content" />
<TextView
android:text="金额"
android:layout_width="fill_parent"
android:layout_height="wrap_content" />
</LinearLayout>
<ListView
android:id="@+id/listview"
android:layout_width="fill_parent"
android:layout_height="fill_parent" />
</LinearLayout>
item.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="fill_parent"
android:layout_height="fill_parent"
android:orientation="horizontal" >
<TextView
android:layout_width="100dp"
android:layout_height="wrap_content"
android:id="@+id/name" />
<TextView
android:layout_width="150dp"
android:layout_height="wrap_content"
android:id="@+id/phone" />
<TextView
android:layout_width="fill_parent"
android:layout_height="wrap_content"
android:id="@+id/amount" />
</LinearLayout>
SimpleAdapter 简单适配器
public class SqliteActivity extends Activity {
/** Called when the activity is first created. */
private ListView listView;
private PersonService ps;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
ps = new PersonService(this);
listView = (ListView) this.findViewById(R.id.listview);
show();
}
private void show() {
List<Person> pl = this.ps.getpage(0, 20);
List<HashMap<String, Object>> data = new ArrayList<HashMap<String, Object>>();
for (Person p : pl) {
HashMap<String, Object> item = new HashMap<String, Object>();
item.put("name", p.getName());
item.put("phone", p.getPhone());
item.put("amount", p.getAmount());
item.put("id", p.getId());
data.add(item);
}
SimpleAdapter sa = new SimpleAdapter(this, data, R.layout.item,
new String[] { "name", "phone", "amount" }, new int[] {
R.id.name, R.id.phone, R.id.amount });
listView.setAdapter(sa);
}
}
private void show2() { //利用游标显示 结果中必须包含"_id"这个字段,可以改表或者别名
SimpleCursorAdapter sca = new SimpleCursorAdapter(this, R.layout.item,
ps.getCursorpage(0, 20), new String[] { "name", "phone",
"amount" }, new int[] { R.id.name, R.id.phone,
R.id.amount });
listView.setAdapter(sca);
}
自定义适配器
public class PersonAdapter extends BaseAdapter {
private List<Person> pl; // 绑定的数据
private int resource;// 绑定界面
private LayoutInflater inflater;
public PersonAdapter(Context context, List<Person> pl, int resource) {
super();
this.pl = pl;
this.resource = resource;
inflater = (LayoutInflater) context
.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
}
@Override
public int getCount() {
return pl.size();
}
@Override
public Object getItem(int paramInt) {
return pl.get(paramInt);
}
@Override
public long getItemId(int paramInt) {
return paramInt;
}
@Override
public View getView(int paramInt, View paramView, ViewGroup paramViewGroup) {
if (paramView == null) {// 第一页则新建
paramView = inflater.inflate(resource, null);// 生成条目界面对象
}
TextView nameView = (TextView) paramView.findViewById(R.id.name);
TextView phoneView = (TextView) paramView.findViewById(R.id.phone);
TextView amountView = (TextView) paramView.findViewById(R.id.amount);
Person p = pl.get(paramInt);
nameView.setText(p.getName());// 数据绑定
phoneView.setText(p.getPhone());
amountView.setText(p.getAmount().toString());
return paramView;
}
}
private void show3() { //自定义适配器
List<Person> pl = this.ps.getpage(0, 30);
PersonAdapter pa= new PersonAdapter(this, pl, R.layout.item);
listView.setAdapter(pa);
}