使用listview綁定sqlite中的資料

標籤：

我想在我的安卓項目中實現一個這樣的功能，讀取sqlite資料庫中的資料並顯示到某個頁面的listview控制項中。

首先，我建立了一個Service類，來實現對資料庫的各種操作，然後在這個類中添加對資料庫操作的增刪改查方法。具體代碼如下：

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.cardemo1.Username;
import com.example.cardemo1.db.DBHelper;

public class CarService {
    private DBHelper dbHelper;


    public CarService(Context context){
        dbHelper=new DBHelper(context);

    }

    //添加車輛資訊
    public boolean register(Car car,String num){

        SQLiteDatabase sdb = dbHelper.getReadableDatabase();
        String str = "select carnumber from car where carnumber = ?";
        Cursor cursor = sdb.rawQuery(str, new String[]{num});
        if(cursor.moveToFirst() != true){
        String sql ="insert into car(_id,carbrand,carsign,carmodel,carnumber,enginenum,bodylevel,mileage,gasoline,engineperfor,transperfor,carlight) values(?,?,?,?,?,?,?,?,?,?,?,?)";
        Object obj[]={car.get_id(),car.getCarbrand(),car.getCarsign(),car.getCarmodel(),car.getCarnumber(),car.getEnginenum(),
                car.getBodylevel(),car.getMileage(),car.getGasoline(),car.getEngineperfor(),car.getTransperfor(),car.getCarlight()};
        sdb.execSQL(sql,obj);
        return true;
        }else{
        return false;
        }
    }

    //查詢車輛資訊,傳回型別是ArrayList
    public ArrayList select(String _id){

        ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();

        SQLiteDatabase sdb = dbHelper.getReadableDatabase();
        String sql="select * from car where _id=?";
        Cursor cursor = sdb.rawQuery(sql, new String[]{_id});

        while(cursor.moveToNext()){
            HashMap<String, String> map= new HashMap<String, String>();
            map.put("carbrand",cursor.getString(1));
            map.put("carsign",cursor.getString(2));
            map.put("carmodel",cursor.getString(3));
            map.put("carnumber",cursor.getString(4));
            map.put("enginenum",cursor.getString(5));
            map.put("bodylevel",cursor.getString(6));
            map.put("mileage",cursor.getString(7));
            map.put("gasoline",cursor.getString(8));
            map.put("engineperfor", cursor.getString(9));
            map.put("transperfor", cursor.getString(10));
            map.put("carlight", cursor.getString(11));
            list.add(map);
        }
        return list;

    }
   
    //查詢車牌號，傳回型別是數組
    public String[] select_carnum(String _id){
        String[] carnum = new String[20];
        String carnumber;
        int index;
        SQLiteDatabase sdb = dbHelper.getReadableDatabase();
        String sql="select carnumber from car where _id=?";
        Cursor cursor = sdb.rawQuery(sql, new String[]{_id});
        int i = 0;
        while(cursor.moveToNext()){
            index = cursor.getColumnIndex("carnumber");
            carnum[i] = cursor.getString(index);
            i++;
        }
        return carnum;

    }
    //更新車輛資訊
    public boolean update(Car car,String num){
        SQLiteDatabase sdb = dbHelper.getReadableDatabase();
        String str ="select carnumber from car where carnumber = ?";
        Cursor cursor = sdb.rawQuery(str, new String[]{num});
        if(cursor.moveToFirst() == true){
        String sql="update car set carbrand=?,carsign=?,carmodel=?,enginenum=?,bodylevel=?,mileage=?,gasoline=?,engineperfor=?,transperfor=?,carlight=? where _id = ?" ;
        Object obj[]={car.getCarbrand(),car.getCarsign(),car.getCarmodel(),car.getEnginenum(),
                car.getBodylevel(),car.getMileage(),car.getGasoline(),car.getEngineperfor(),car.getTransperfor(),car.getCarlight(),car.get_id()};
        sdb.execSQL(sql,obj);
        return true;
        }else
        {
        String sql="update car set carbrand=?,carsign=?,carmodel=?,carnumber=?,enginenum=?,bodylevel=?,mileage=?,gasoline=?,engineperfor=?,transperfor=?,carlight=? where _id = ?" ;
        Object obj[]={car.getCarbrand(),car.getCarsign(),car.getCarmodel(),car.getCarnumber(),car.getEnginenum(),
        car.getBodylevel(),car.getMileage(),car.getGasoline(),car.getEngineperfor(),car.getTransperfor(),car.getCarlight(),car.get_id()};
        sdb.execSQL(sql,obj);
        return true;
        }

}
其中加粗的是我要提取並和listview繫結資料。然後我在activity類中是這麼寫的：

Username uname = (Username) getApplicationContext();
        String name =  uname.getUsername();
        CarService service = new CarService(CarSeeActivity.this);
//調用Service中的select方法 來擷取資料庫中的資料      

service.select(name);

//執行個體化listview空間
        ListView lv = (ListView) findViewById(R.id.lv);

//建立adapter
        SimpleAdapter adapter = new SimpleAdapter(CarSeeActivity.this, service.select(name), R.layout.item, new String[]{"carbrand","carsign","carmodel","carnumber","enginenum","bodylevel","mileage","gasoline","engineperfor","transperfor","carlight"},
                new int[]{R.id.car_carbrand,R.id.car_carsign,R.id.car_carmodel,R.id.car_carnumber,R.id.car_enginenum,R.id.car_bodylevel,R.id.car_mileage,R.id.car_gasoline,R.id.car_engineperfor,R.id.car_transperfor,R.id.car_light});
        //將listview和adapter綁定
        lv.setAdapter(adapter);

//設定點擊事件
        lv.setOnItemClickListener(new ItemClickListener());

 

前兩句是擷取使用者名稱的，為了在資料庫中進行尋找，擷取使用者名稱就可以。

public class ItemClickListener implements OnItemClickListener{

        @Override
        public void onItemClick(AdapterView<?> parent, View view, int position,
                long id) {
            // TODO Auto-generated method stub
            ListView listview = (ListView)parent;
            HashMap<String, String> data = (HashMap<String, String>)listview.getItemAtPosition(position);  
            String carid = data.get("carnumber");  
            Toast.makeText(getApplicationContext(), "車牌號為"+carid+"的車輛資訊", 1).show();  
            
        }

 

使用listview綁定sqlite中的資料