数据库中的增删改查、以及sql语句在android中的使用注意事项、保存头像到本地方法

/**
 * 
 * TODO<联系人离线数据库>
 * 
 * @author YueFeng.Zhang
 * @data: 2016-5-25 下午2:19:41
 * @version: V1.0
 */
public class ContanctsDbHelper {
private static ContanctsDbHelper instance = null;
private SqlHelper sqlHelper;
private SQLiteDatabase db;


public ContanctsDbHelper(Context context) {
sqlHelper = new SqlHelper(context);
db = sqlHelper.getWritableDatabase();
}


/**
* 
* TODO<获取联系人数据库操作类单例对象>
* 
* @throw
* @return ContanctsDbHelper
*/
public static ContanctsDbHelper getInstance(Context context) {
if (instance == null) {
instance = new ContanctsDbHelper(context);
}
return instance;
}


public void closeDb() {
if (sqlHelper != null) {
sqlHelper.close();
db.close();
}
}


private class SqlHelper extends SQLiteOpenHelper {
private static final String DB_NAME = "contanctsdb";
/**
* 1，增加更新登录用户的好友列表方法
* 2，增加通过手机号码查询该用户名字，供会话使用。
* 3，增加好友详情查询功能
*/
private static final int DB_VERSION = 2;


public SqlHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
// TODO Auto-generated constructor stub
}


@Override
public void onCreate(SQLiteDatabase db) {
String sql = "CREATE TABLE  IF NOT EXISTS "
+ DB_NAME
+ "( id INTEGER PRIMARY KEY AUTOINCREMENT,email text, headPath text, mobile text, officePhone text, orgListName text, sex text, skype text, userId text, userName text, userPosition text, who text)";
db.execSQL(sql);


}


@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
dropTable(db);
onCreate(db);
}


private void dropTable(SQLiteDatabase db) {
String sql = "DROP TABLE IF EXISTS " + DB_NAME;
db.execSQL(sql);
}
}


/**
* 
* TODO<将联系人集合内的人员加入数据库>
* 
* @throw
* @return void
*/
public void addContanctsDb(List<AddressBook> list,final Context context) {
ContentValues values = new ContentValues();
for (AddressBook Ab : list) {
String email = Ab.getEmail();

final String headPath = Ab.getHeadPath();

String mobile = Ab.getMobile();

String officePhone = Ab.getOfficePhone();

String orgListName = Ab.getOrgListName();

String sex = Ab.getSex();

String skype = Ab.getSkype();

String userId = Ab.getUserId();

String userName = Ab.getUserName();

String userPosition = Ab.getUserPosition();

/**************保存头像到本地**************************/
final String fileName = mobile + ".jpg";
File file = new File(Constants.ICON + "/" + fileName);
if (file.exists()) {// 先判断本地是否有缓存，有就删了
// 先删除
file.delete();
}
// 再保存新的到本地
ThreadPoolManager.getInstance().addTask(new Runnable() {
@Override
public void run() {
Bitmap bp = ImageUtil.getBitmaoByurl(headPath);
if (bp != null) {
FileUtil.saveFile(context, fileName, bp);
}
}
});

if (email != null && !"".equals(email.trim())) {
values.put("email", email);
} else {
values.put("email", "");
}


if (headPath != null && !"".equals(headPath.trim())) {
values.put("headPath", headPath);
} else {
values.put("headPath", "");
}


if (mobile != null && !"".equals(mobile.trim())) {
values.put("mobile", mobile);
} else {
values.put("mobile", "");
}


if (officePhone != null && !"".equals(officePhone)) {
values.put("officePhone", officePhone);
} else {
values.put("officePhone", "");
}


if (orgListName != null && !"".equals(orgListName.trim())) {
values.put("orgListName", orgListName);
} else {
values.put("orgListName", "");
}


if (sex != null && !"".equals(sex.trim())) {
values.put("sex", sex);
} else {
values.put("sex", "");
}


if (skype != null && !"".equals(skype.trim())) {
values.put("skype", skype);
} else {
values.put("skype", "");
}


if (userId != null && !"".equals(userId.trim())) {
values.put("userId", userId);
} else {
values.put("userId", "");
}


if (userName != null && !"".equals(userName.trim())) {
values.put("userName", userName);
} else {
values.put("userName", "");
}


if (userPosition != null && !"".equals(userPosition.trim())) {
values.put("userPosition", userPosition);
} else {
values.put("userPosition", "");
}

values.put("who", Constants.LOGINIQ.getUserId());
db.insert(SqlHelper.DB_NAME, "id", values);
}
}


/**
* 
* TODO<获取自己的好友列表>
* 
* @throw
* @return List<AddressBook>
*/
public List<AddressBook> getAddressBookList(String userId) {
List<AddressBook> addressBooks =  new ArrayList<AddressBook>();
String sql = "select email, headPath, mobile, officePhone,orgListName, sex, skype, userId, userName, userPosition from "
+ SqlHelper.DB_NAME + " where who = ?";
Cursor cursor = db.rawQuery(sql, new String[]{Constants.LOGINIQ.getUserId()});
while (cursor.moveToNext()) {
AddressBook addressBook = new AddressBook(cursor.getString(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),
cursor.getString(5), cursor.getString(6), cursor.getString(7), cursor.getString(8), cursor.getString(9));
// System.out.println("联系人数据库:" + cursor.getString(0) + cursor.getString(1) + cursor.getString(2) + cursor.getString(3) + cursor.getString(4)
// + cursor.getString(5) + cursor.getString(6) + cursor.getString(7) + cursor.getString(8) + cursor.getString(9));
addressBooks.add(addressBook);
}
return addressBooks;
}
/**
* 
* TODO<根据userId更新好友列表> 
* @throw 
* @return void
*/
public void updateContancesDb(List<AddressBook> list, String userId){


ContentValues values = new ContentValues();
for (AddressBook Ab : list) {
String email = Ab.getEmail();
String headPath = Ab.getHeadPath();
String mobile = Ab.getMobile();
String officePhone = Ab.getOfficePhone();
String orgListName = Ab.getOrgListName();
String sex = Ab.getSex();
String skype = Ab.getSkype();
String userId02 = Ab.getUserId();
String userName = Ab.getUserName();
String userPosition = Ab.getUserPosition();


if (email != null && !"".equals(email.trim())) {
values.put("email", email);
} else {
values.put("email", "");
}


if (headPath != null && !"".equals(headPath.trim())) {
values.put("headPath", headPath);
} else {
values.put("headPath", "");
}


if (mobile != null && !"".equals(mobile.trim())) {
values.put("mobile", mobile);
} else {
values.put("mobile", "");
}


if (officePhone != null && !"".equals(officePhone)) {
values.put("officePhone", officePhone);
} else {
values.put("officePhone", "");
}


if (orgListName != null && !"".equals(orgListName.trim())) {
values.put("orgListName", orgListName);
} else {
values.put("orgListName", "");
}


if (sex != null && !"".equals(sex.trim())) {
values.put("sex", sex);
} else {
values.put("sex", "");
}


if (skype != null && !"".equals(skype.trim())) {
values.put("skype", skype);
} else {
values.put("skype", "");
}


if (userId02 != null && !"".equals(userId02.trim())) {
values.put("userId", userId02);
} else {
values.put("userId", "");
}


if (userName != null && !"".equals(userName.trim())) {
values.put("userName", userName);
} else {
values.put("userName", "");
}


if (userPosition != null && !"".equals(userPosition.trim())) {
values.put("userPosition", userPosition);
} else {
values.put("userPosition", "");
}

values.put("who", Constants.LOGINIQ.getUserId());

db.update(SqlHelper.DB_NAME, values, "who = ? and userId = ?", new String[]{userId, userId02});
}

}
/**
* 
* TODO<根据手机号获取好友详情对象> 
* @throw 
* @return AddressBook
*/
public AddressBook getFriendInfoForMobile(String mob){
String sql = "select email,headPath,mobile,officePhone,orgListName,sex,skype,userId,userName,userPosition from " + SqlHelper.DB_NAME + " where mobile like ?";
Cursor rawQuery = db.rawQuery(sql, new String[]{"%"+mob+"%"});
AddressBook addressBook = null;
while(rawQuery.moveToNext()){
String email = rawQuery.getString(0);
String headPath = rawQuery.getString(1);
String mobile = rawQuery.getString(2);
String officePhone = rawQuery.getString(3);
String orgListName = rawQuery.getString(4);
String sex = rawQuery.getString(5);
String skype = rawQuery.getString(6);
String userId = rawQuery.getString(7);
String userName = rawQuery.getString(8);
String userPosition = rawQuery.getString(9);

addressBook = new AddressBook(email,headPath,mobile,officePhone,orgListName,sex,skype,userId,userName,userPosition);
}
return addressBook;
}
/**
* 
* TODO<根据手机号查询好友姓名> 
* @throw 
* @return AddressBook
*/
public String getUserNameForMoile(String mobile){
String userName = "";
String sql = "select userName from contanctsdb where mobile like ?";
Cursor rawQuery = db.rawQuery(sql, new String[]{"%"+mobile+"%"});
while(rawQuery.moveToNext()){
userName = rawQuery.getString(0);
}
return userName;
}
/**
* 
* TODO<根据用户的userId删除对应的数据> 
* @throw 
* @return void
*/
public void deleteDbForWho(String userId){

db.delete(SqlHelper.DB_NAME, "who = ?", new String[]{userId});
}
/**
* 
* TODO<清空数据库>
* 
* @throw
* @return void
*/
public void clear() {
db.delete(SqlHelper.DB_NAME, "id>?", new String[] { "0" });
}
}