|
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.getWs_name(),
websites.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.getC_id(), c_web.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.getC_name(), cities.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、部分简单文件没有上传,可以自己实现。 |
|