AddressInfoDao.java

/**
 * author :  lipan
 * filename :  AddressInfoDao.java
 * create_time : 2014年8月31日 上午11:27:27
 */
package com.sets.speedtest.db;

import java.util.ArrayList;
import java.util.List;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.sets.speedtest.domain.AddressInfo;
import com.sets.speedtest.manager.DBOpenManager;
import com.sets.speedtest.utils.StringB;

/**
 * @author : lipan
 * @create_time : 2014年8月31日 上午11:27:27
 * @desc : 地址信息
 * @update_person:
 * @update_time :
 * @update_desc :
 *
 */
public class AddressInfoDao
{
    private DBOpenHelper helper;

    private static final String INSERT_RESULT = "insert into address_info(address_id, city, college, address, seat_number, download, signal_strength, recommend_num, test_time)"
            + "  values(?,?,?,?,?,?,?,?,?)";
    private static final String INSERT_TEMP_ADDS = "insert into address_info(address_id, city, college, address, seat_number)"
            + "  values(?,?,?,?,?)";
    private static final String DELETE_TEMP_ADDS = "delete from address_info where address_id=?";

    public AddressInfoDao()
    {
        DBOpenManager dbOpenManager = DBOpenManager.getInstance();
        helper = dbOpenManager.getHelper();
    }

    /**
     * 清空临时地址信息
     * 
     * @param add
     */
    public void deleteTempAdds()
    {
        SQLiteDatabase db = helper.getReadableDatabase();
        db.execSQL(DELETE_TEMP_ADDS, new Object[] { -1 });
    }

    /**
     * 新增地址信息,将地址id赋值为-1，新增的数据作为当前用户的临时地址信息， 每次登录后将清空这些数据，再重新插入
     * 
     * @param add
     */
    public void insertTempAdds(AddressInfo add)
    {
        SQLiteDatabase db = helper.getReadableDatabase();
        db.execSQL(
                INSERT_TEMP_ADDS,
                new Object[] { -1, add.getCity(), add.getCollege(), add.getAddress(),
                        add.getSeatNumber() });
    }

    /**
     * 查询地址信息
     * 
     * @return
     */
    public Cursor queryTempAdds(AddressInfo info)
    {
        SQLiteDatabase db = helper.getReadableDatabase();
        String where = "";
        String[] whereArgs = null;
        if (StringB.isBlank(info.getAddress()))
        {
            where = "address_id=-1 and city = ? and college = ?";
            whereArgs = new String[] { info.getCity(), info.getCollege() };
        } else
        {
            where = "address_id=-1 and city=? and college=? and address like ?";
            whereArgs = new String[] { info.getCity(), info.getCollege(), "%" + info.getAddress() + "%" };
        }
        return db.query("address_info", new String[] { "address as _id", "seat_number" }, where,
                whereArgs, null, null, null);
    }

    /**
     * 新增测速信息
     * 
     * @param add
     */
    public void insertResult(AddressInfo add)
    {
        SQLiteDatabase db = helper.getReadableDatabase();
        db.execSQL(
                INSERT_RESULT,
                new Object[] { add.getAddressId(), add.getCity(), add.getCollege(),
                        add.getAddress(), add.getSeatNumber(), add.getDownload(),
                        add.getSignalStrength(), add.getRecommendExamNum(), add.getTestTime() });

    }

    /**
     * 查询日志
     * 
     * @return
     */
    public List<AddressInfo> query()
    {
        List<AddressInfo> adds = new ArrayList<AddressInfo>();
        SQLiteDatabase db = helper.getReadableDatabase();
        // query 方法
        Cursor cursor = db.query("address_info", null, null, null, null, null, "integer desc");
        while (cursor.moveToNext())
        {
            String college = cursor.getString(cursor.getColumnIndex("college"));
            String address = cursor.getString(cursor.getColumnIndex("address"));
            int seatNumber = cursor.getInt(cursor.getColumnIndex("seat_number"));
            int signalStrength = cursor.getInt(cursor.getColumnIndex("signal_strength"));
            int download = cursor.getInt(cursor.getColumnIndex("download"));
            int recommendExamNum = cursor.getInt(cursor.getColumnIndex("recommend_num"));
            long testTime = cursor.getLong(cursor.getColumnIndex("test_time"));
            adds.add(new AddressInfo(college, address, seatNumber, signalStrength, download,
                    recommendExamNum, testTime));
        }
        cursor.close();
        return adds;
    }
}