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,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,ps.getCursorpage(0,20),new int[] { R.id.name,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);
} 原文链接：https://www.f2er.com/sqlite/200435.html