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;
	}

}

 5、部分简单文件没有上传，可以自己实现。