Android SQLite数据显示于ListView

Android SQLite数据显示于ListView

1. 新建DBOpenHelper继承自SQLiteOpenHelper类：

public class DBOpenHelper extends SQLiteOpenHelper {

    private final static String DATABASENAME = "itcast.db";
    private final static int DATABASEVERSION = 2;

    public DBOpenHelper(Context context) {
        super(context, DATABASENAME, null, DATABASEVERSION);

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i("old", oldVersion +"");
        Log.i("new", newVersion + "");
        db.execSQL("ALTER TABLE person ADD amount integer");
    }

}

2.新建对应数据库中表的字段的JavaBean数据类：

public class Person {

    private Integer id;
    private String name;
    private Integer amount;

    public Person() {

    }    
    public Person(Integer id, String name, Integer amount) {
        super();
        this.id = id;
        this.name = name;
        this.amount = amount;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getAmount() {
        return amount;
    }
    public void setAmount(Integer amount) {
        this.amount = amount;
    }
    @Override
    public String toString() {
        return "Person [id=" + id + ", name=" + name + ", amount=" + amount
                + "]";
    }
}

3. 新建事务操作方法：

public class DBService {

    private DBOpenHelper dbHelper;

    public DBService(Context context) {
        this.dbHelper = new DBOpenHelper(context);
    }

    public void payment() {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.beginTransaction();
        try {
            db.execSQL("update person set amount=amount-10 where personid=?", new Object[]{1});
            db.execSQL("update person set amount=amount+10 where personid=?", new Object[]{2});
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    public void save(Person person) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.execSQL("insert into person (name, amount) values(?,?)", new Object[] {person.getName(),person.getAmount()});
        //db.close();
    }

    public void update(Person person) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.execSQL("update person set amount=? where personid=?",
                new Object[] {person.getAmount(), person.getId()});
    }

    public void delete(Integer id) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.execSQL("delete from person where personid=?",
                new Object[] {id.toString()});
    }

    public Person find(Integer id) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from person where personid=?",
                new String[]{id.toString()});
        if(cursor.moveToFirst()) {
            int personId = cursor.getInt(cursor.getColumnIndex("personid"));
            String personName = cursor.getString(cursor.getColumnIndex("name")); 
            int personAmount = cursor.getInt(cursor.getColumnIndex("amount")); 
            return new Person(personId, personName, personAmount);
        }
        cursor.close();
        return null;
    }

    public List<Person> getScrollData(Integer offset, Integer maxResult) {
        List<Person> persons = new ArrayList<Person>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from person limit?,?",
                new String[]{offset.toString(), maxResult.toString()});
        while(cursor.moveToNext()) {
            int personId = cursor.getInt(cursor.getColumnIndex("personid"));
            String personName = cursor.getString(cursor.getColumnIndex("name"));
            int personAmount = cursor.getInt(cursor.getColumnIndex("amount"));
            Person person = new Person(personId, personName, personAmount);
            persons.add(person);
        }
        cursor.close();
        return persons;
    }

    public Cursor getCursorScrollData(Integer offset, Integer maxResult) {
        List<Person> persons = new ArrayList<Person>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        return db.rawQuery("select personid as _id, name, amount from person limit?,?",
                new String[]{offset.toString(), maxResult.toString()});
    }

    public long getCount() {
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select count(*) from person", null);
        cursor.moveToFirst();
        return cursor.getLong(0);
    }

}

4. activity_main.xml文件：

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity" >

    <ListView
        android:id="@+id/listview"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        />

</LinearLayout>

5. item.xml文件：

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:orientation="horizontal" >

    <TextView 
        android:layout_width="80dp"
        android:layout_height="wrap_content"
        android:id="@+id/id"
        android:text="12"
        />

    <TextView 
        android:layout_width="100dp"
        android:layout_height="wrap_content"
        android:id="@+id/name"
        android:text="李明"
        />

    <TextView 
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/amount"
        android:text="500"
        />

</LinearLayout>

6. MainActivity.java中进行具体操作：

public class MainActivity extends Activity {

    private DBService dbService;
    private ListView listview;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        listview = (ListView) findViewById(R.id.listview);
        dbService = new DBService(this);
        Cursor cursor = dbService.getCursorScrollData(0, 100);
        SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, R.layout.item, cursor, 
                new String[]{"_id", "name", "amount"}, new int[]{R.id.id, R.id.name, R.id.amount});
        listview.setAdapter(adapter);

        /*List<Person> persons = dbService.getScrollData(0, 100);
        List<HashMap<String, Object>> data = new ArrayList<HashMap<String, Object>>();
        HashMap<String, Object> item = new HashMap<String, Object>();
        item.put("id", "用户ID");
        item.put("name", "用户名");
        item.put("amount", "用户存款");
        data.add(item);
        for(Person person : persons) {
            item = new HashMap<String, Object>();
            item.put("id", person.getId());
            item.put("name", person.getName());
            item.put("amount", person.getAmount());
            data.add(item);
        }

        SimpleAdapter adapter = new SimpleAdapter(this, data, R.layout.item, 
                new String[]{"id", "name", "amount"}, new int[]{R.id.id, R.id.name, R.id.amount});
        listview.setAdapter(adapter);

        listview.setOnItemClickListener(new OnItemClickListener() {

            @Override
            public void onItemClick(AdapterView<?> arg0, View arg1, int arg2,
                    long arg3) {
                // TODO Auto-generated method stub
                ListView lview = (ListView) arg0;
                HashMap<String, Object> item = (HashMap<String, Object>) lview.getItemAtPosition(arg2);
                Toast.makeText(MainActivity.this, item.get("id")+"", Toast.LENGTH_SHORT).show();
            }
        });*/
    }

}