android中炫酷划屏事件及sqlite所有操作Demo(2)

4、编写数据库操作的代码

4.1、编写简单的pojo类

package com.jftt.pojo;public class WebSite {    private int ws_id;    private String ws_name;    private String ws_url;    public WebSite( String string, String string2) {		 		this.ws_name=string;		this.ws_url=string2;	}	public WebSite(int wid, String string, String string2) {		this.ws_id=wid;		this.ws_name=string;		this.ws_url=string2;	}	public int getWs_id() {		return ws_id;	}	public void setWs_id(int wsId) {		ws_id = wsId;	}	public String getWs_name() {		return ws_name;	}	public void setWs_name(String wsName) {		ws_name = wsName;	}	public String getWs_url() {		return ws_url;	}	public void setWs_url(String wsUrl) {		ws_url = wsUrl;	}}package com.jftt.pojo;public class City {	    private int c_id;    private String c_name;    private String c_pic;    public City(String cname,String cpic){    	    	     	this.c_name=cname;    	this.c_pic=cpic;    }    public City(int cid,String cname,String cpic){    	    	this.c_id=cid;    	this.c_name=cname;    	this.c_pic=cpic;    }	public int getC_id() {		return c_id;	}	public void setC_id(int cId) {		c_id = cId;	}	public String getC_name() {		return c_name;	}	public void setC_name(String cName) {		c_name = cName;	}	public String getC_pic() {		return c_pic;	}	public void setC_pic(String cPic) {		c_pic = cPic;	}    }package com.jftt.pojo;public class City_Web {	private int cw_id;	private int c_id;	private int w_id;	public City_Web(int cid,int wid){		this.c_id=cid;		this.w_id=wid;					}	public int getCw_id() {		return cw_id;	}	public void setCw_id(int cwId) {		cw_id = cwId;	}	public int getC_id() {		return c_id;	}	public void setC_id(int cId) {		c_id = cId;	}	public int getW_id() {		return w_id;	}	public void setW_id(int wId) {		w_id = wId;	}}

4.2、编写数据库辅助类DBHelper.java

package com.jftt.service;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.util.Log;public class DBHelper extends SQLiteOpenHelper{  private static final String DB_NAME="jftt";   private static final int VERSION=1;	public DBHelper(Context context){	      super(context,DB_NAME,null,VERSION);		 }    @Override	public void onCreate(SQLiteDatabase db)	{    String sql="create table city(c_id integer primary key autoincrement,c_name varchar(30),c_pic varchar(30))";	     String sql2="create table website(ws_id integer primary key autoincrement,ws_name varchar(30),ws_url varchar(30))";	     String sql3="create table cw(cw_id integer primary key autoincrement,c_id integer,w_id integer)";	     Log.e("tag","::");		 db.execSQL(sql);		 Log.e("tag","123");		 db.execSQL(sql3);		 db.execSQL(sql2);	}	@Override	public void onUpgrade(SQLiteDatabase db, int arg1, int arg2)	{		db.execSQL("drop table if exists person");		onCreate(db);	}}

4.3、编写数据库操作的类?

package com.jftt.service;import java.util.ArrayList;import java.util.List;import com.jftt.FlipperDemo;import com.jftt.pojo.City;import com.jftt.pojo.City_Web;import com.jftt.pojo.WebSite;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.util.Log;public class DBManager {	private static DBHelper dbHelper;	private Context context;	private static SQLiteDatabase db;	public DBManager(Context context) {		this.context = context;		dbHelper = new DBHelper(context);	}	/* 批量插入网站 */	public static void insertWebSite(WebSite[] websites) {		String sql = "insert into website(ws_name,ws_url)values(?,?)";		db = dbHelper.getWritableDatabase();		for (int i = 0; i < websites.length; i++) {			db.execSQL(sql, new Object[] { websites[i].getWs_name(),					websites[i].getWs_url() });		}		db.close();	}	/* cw 批量插入cws */	public static void insertCws(City_Web[] c_web) {		String sql = "insert into cw(c_id,w_id)values(?,?)";		db = dbHelper.getWritableDatabase();		for (int i = 0; i < c_web.length; i++) {			db.execSQL(sql,					new Object[] { c_web[i].getC_id(), c_web[i].getW_id() });		}		db.close();	}	/* city 批量插入 */	public static void insertCities(City[] cities) {		String sql = "insert into city(c_name,c_pic)values(?,?)";		db = dbHelper.getWritableDatabase();		for (int i = 0; i < cities.length; i++) {			db.execSQL(					sql,					new Object[] { cities[i].getC_name(), cities[i].getC_pic() });		}		db.close();	}	/* 删除城市 */	public static void deleteCity(Integer... ids) {		StringBuffer sb = new StringBuffer();		if (ids.length > 0) {			for (Integer id : ids) {				sb.append('?').append(',');			}			sb = sb.deleteCharAt(sb.length() - 1);		}		db = dbHelper.getWritableDatabase();		db.execSQL("delete from city where c_id in(" + sb + ")", (Object[]) ids);		db.close();	}	/*	 * find city id by web ide 根据网站id查找所有城市ID列表	 */	public static List<Integer> findCityIdByWebID(int wid) {		List<Integer> array = new ArrayList<Integer>();		db = dbHelper.getWritableDatabase();		 Cursor cur = db.rawQuery("select * from cw where w_id=?",				new String[] { String.valueOf(wid) }); 	  while(cur.moveToNext()) {						array.add(cur.getInt(1));		}		Log.e("myTag", array.size()+"");		return array;	}	/*	 * find city names by ids 根据IDs查找全部城市 返回符合要求的城市名称集合	 */	public static List<String> findCitiesByIDs(List<Integer> cids) {		List<String> cityNames = new ArrayList<String>();		db = dbHelper.getWritableDatabase();		for (int i = 0; i < cids.size(); i++) {			Cursor cur = db.rawQuery("select * from city where c_id=?",					new String[] { String.valueOf(cids.get(i)) });			if (cur.moveToNext()) {				cityNames.add(cur.getString(1));			}		}		return cityNames;	}	/*	 * find website by id 根据id查找网站 返回网站Website	 */	public static WebSite findByID(int wid) {		WebSite person = null;		db = dbHelper.getWritableDatabase();		Cursor cur = db.rawQuery("select * from website where ws_id=?",				new String[] { String.valueOf(wid) });		if (cur.moveToNext()) {			person = new WebSite(wid, cur.getString(1), cur.getString(2));			return person;		}		return null;	}	/*	 * find city by id 根据ID查找城市	 */	public static City findCityByID(int cid) {		City city = null;		db = dbHelper.getWritableDatabase();		Cursor cur = db.rawQuery("select * from city where c_id=?",				new String[] { String.valueOf(cid) });		if (cur.moveToNext()) {			city = new City(cid, cur.getString(1), cur.getString(2));			return city;		}		return null;	}	/*	 * find pic by city name 根据城市名称查找图片名称	 */	public static String findCityPicByCityName(String cname) {		db = dbHelper.getWritableDatabase();		Cursor cur = db.rawQuery("select * from city where c_name=?",				new String[] { String.valueOf(cname) });		if (cur.moveToNext()) {			return cur.getString(2);		}		return null;	}	/*	 * find all cities 查找所有城市 返回所有城市名称集合	 */	public static List<String> findAllCities() {		List<String> cities = new ArrayList<String>();		db = dbHelper.getReadableDatabase();		Cursor cur = db.rawQuery("select * from city ", null);		while (cur.moveToNext()) {			cities.add(cur.getString(1));		}		return cities;	}	/* get the number of all the city record */	public static int getCityCount() {		db = dbHelper.getWritableDatabase();		Cursor cur = db.rawQuery("select count(*) from city", null);		if (cur.moveToNext()) {			return cur.getInt(0);		}		return 0;	}	/* get the number of all the web record */	public static int getWebCount() {		db = dbHelper.getWritableDatabase();		Cursor cur = db.rawQuery("select count(*) from website", null);		if (cur.moveToNext()) {			return cur.getInt(0);		}		return 0;	}	/* get the number of all the cw record */	public static int getCwCount() {		db = dbHelper.getWritableDatabase();		// Cursor cur = db.rawQuery("select count(*) from cw", null);		// if (cur.moveToNext()) {		// return cur.getInt(0);		//		// }		return 0;	}}