public class MainActivity extends Activity {
private List<City> cities;
private ListView citylistview;
private SqLiteHelper sqLiteHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mkDB();
sqLiteHelper = SqLiteHelper.getSqlSqLiteHelper(MainActivity.this);
initDatas();
}
private void initDatas() {
cities = sqLiteHelper.queryCities(0, 20);
citylistview = (ListView) findViewById(R.id.citylistview);
MyAdapter adapter = new MyAdapter(cities, MainActivity.this);
citylistview.setAdapter(adapter);
}
private void mkDB() {
String path = "data/data/" + getPackageName() + "/databases";// 得到数据库的路径
File db_path = new File(path);// 在path创建db_path路径
if (!db_path.exists()) {// 判断改文件是否存在
db_path.mkdirs();
}
File file = new File(db_path, "city.db");// 在db_path路径下创建city.db数据库
if (!file.exists()) {
try {
InputStream input = getResources().openRawResource(R.raw.city);// 读取raw目录下资源文件内容
OutputStream output = new FileOutputStream(file);// output
// 写入file下的city.db中
byte[] b = new byte[1024];
int len = 0;
while ((len = input.read(b)) > 0) {
output.write(b, 0, len);
}
input.close();
output.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
public class SqLiteHelper extends SQLiteOpenHelper {
private List<City> cities;
private static SqLiteHelper msqLiteHelper = null;
private static final String NAME = "city.db";
private SqLiteHelper(Context context, String name, CursorFactory factory,
int version) {
super(context, NAME, factory, 1);
// TODO Auto-generated constructor stub
}
private SqLiteHelper(Context context) {
super(context, NAME, null, 1);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
public static SqLiteHelper getSqlSqLiteHelper(Context context) {
if (msqLiteHelper == null) {
msqLiteHelper = new SqLiteHelper(context);
}
return msqLiteHelper;
}
// CREATE TABLE city (_id INTEGER PRIMARY KEY AUTOINCREMENT,
// province TEXT, name TEXT, number TEXT, pinyin TEXT, py TEXT)
public List<City> queryCities(Integer startIndex,Integer maxIndex) {
String sql="select * from city limit ?,?";
Cursor cursor = getReadableDatabase().rawQuery(sql, new String []{startIndex.toString(),maxIndex.toString()});
cities = new ArrayList<City>();
City city = null;
while (cursor.moveToNext()) {
//获取索引值
int IdIndex = cursor.getColumnIndex("_id");
int provinceIndex = cursor.getColumnIndex("province");
int nameIndex = cursor.getColumnIndex("name");
int numberIndex = cursor.getColumnIndex("number");
int pinyinIndex = cursor.getColumnIndex("pinyin");
int pyIndex = cursor.getColumnIndex("py");
//通过索引值来确定值
int _id = cursor.getInt(IdIndex);
String province = cursor.getString(provinceIndex);
String name = cursor.getString(nameIndex);
String number = cursor.getString(numberIndex);
String pinyin = cursor.getString(pinyinIndex);
String py = cursor.getString(pyIndex);
city = new City(_id, province, name, number, pinyin, py);
cities.add(city);
}
return cities;
}
}
public class MyAdapter extends BaseAdapter {
private List<City> cities;
private Context context;
public MyAdapter(List<City> cities, Context context) {
this.cities = cities;
this.context = context;
}
@Override
public int getCount() {
// TODO Auto-generated method stub
return cities.size();
}
@Override
public Object getItem(int position) {
// TODO Auto-generated method stub
return position;
}
@Override
public long getItemId(int position) {
// TODO Auto-generated method stub
return 0;
}
@Override
public View getView(int position, View convertView, ViewGroup parent) {
ViewHolder viewHolder;
if (convertView == null) {
convertView = LayoutInflater.from(context).inflate(R.layout.item,
parent, false);
viewHolder = new ViewHolder();
viewHolder.cityid = (TextView) convertView
.findViewById(R.id.cityid);
viewHolder.cityname = (TextView) convertView
.findViewById(R.id.cityname);
viewHolder.provincename = (TextView) convertView
.findViewById(R.id.provincename);
convertView.setTag(viewHolder);
} else {
viewHolder = (ViewHolder) convertView.getTag();
}
viewHolder.cityid.setText(cities.get(position).get_id()+"");
viewHolder.cityname.setText(cities.get(position).getName());
viewHolder.provincename.setText(cities.get(position).getProvince());
return convertView;
}
private static class ViewHolder {
public TextView cityid;
public TextView cityname;
public TextView provincename;
}
}