JdbcTemplate的用法

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper; /**
* 设备消息与数据库交互部分
*
*/
public class MysqlSpiderDSFacadeImpl extends BaseSpringDao implements BaseDSFacade {

public static final String BEAN_NAME = "mySQLSpiderDSFacadeImpl";

private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

private int activityIntervalDays=15;


public int getActivityIntervalDays() {
return activityIntervalDays;
} public void setActivityIntervalDays(int activityIntervalDays) {
this.activityIntervalDays = activityIntervalDays;
} public int batch_push_pid_num=200;
public int getBatch_push_pid_num() {
return batch_push_pid_num;
} public void setBatch_push_pid_num(int batchPushPidNum) {
batch_push_pid_num = batchPushPidNum;
} private static Log log = Log.getInstance(MysqlSpiderDSFacadeImpl.class);


/*
create table push_device_info
(
pid bigint(64) not null,

device_model varchar(32),
deviceid_type varchar(10),
deviceid varchar(32),
device_imsi varchar(100),

os_version varchar(100),
cust_version varchar(100),

pe_version varchar(100),
pe_vercode varchar(100),
pe_pkgname varchar(100),

netaccess_type varchar(10),
ip varchar(64),
operation_type varchar(10),
--area_name varchar(64),
country_code varchar(32),
city_name varchar(64),

createtime datetime,
updatetime datetime,
primary key (pid)
) engine=innodb default charset=utf8;
*/ private DeviceInfoRowMapper deviceInfoRowMapper = new DeviceInfoRowMapper();

private class DeviceInfoRowMapper implements RowMapper {
public Object mapRow(ResultSet rs, int i) throws SQLException {
DeviceInfoVO dvo = new DeviceInfoVO();
dvo.setPid(rs.getLong("pid"));
dvo.setDevice_model(rs.getString("device_model"));
dvo.setDeviceid_type(rs.getString("deviceid_type"));
dvo.setDeviceid(rs.getString("deviceid"));
dvo.setDevice_imsi(rs.getString("device_imsi"));

dvo.setOs_version(rs.getString("os_version"));
dvo.setCust_version(rs.getString("cust_version"));

dvo.setPe_version(rs.getString("pe_version"));
dvo.setPe_vercode(rs.getString("pe_vercode"));
dvo.setPe_pkgname(rs.getString("pe_pkgname"));

dvo.setNetaccess_type(rs.getString("netaccess_type"));
dvo.setIp(rs.getString("ip"));
dvo.setOperation_type(rs.getString("operation_type"));
//dvo.setArea_name(rs.getString("area_name"));
dvo.setCountry_code(rs.getString("country_code"));
dvo.setCity_name(rs.getString("city_name"));

//dvo.setApn(rs.getString("apn"));
//dvo.(rs.getString("apn"));

dvo.setCreatedate(rs.getDate("createdate"));
dvo.setModifydate(rs.getDate("modifydate"));
dvo.setPepollversion(rs.getString("pepollversion"));

return dvo;
}
}

public boolean delDeviceInfo(long pid){
try {
String sql = "delete from push_device_info where pid=?";
return getJdbcTemplateForPushmarketing().update(sql, new Object[] { pid }) > 0;
} catch (Exception e) {
throw new PsbDaoException(e);// 应该换成throws ConnectionException,
// RemoteException;
}
} @SuppressWarnings("unchecked")
public DeviceInfoVO getDeviceInfo(long pid) {
Logger logger=Logger.getLogger("getDeviceInfo");
long startTime=System.currentTimeMillis();
try {
String sql = "select * from push_device_info where pid=?";
List<DeviceInfoVO> list = getJdbcTemplateForPushmarketing().query(sql,
new Object[] { pid }, deviceInfoRowMapper);
logger.debug("Call MYSQL getDeviceInfo sql["+sql+"]");
logger.debug("Call MYSQL getDeviceState cost["+(System.currentTimeMillis()-startTime)+"]ms");
if (list.size() > 0) {
return list.get(0);
}
return null;
} catch (Exception e) {
throw new PsbDaoException(e);
}
}

@SuppressWarnings("unchecked")
public List<Long> getDeviceListByCondition(String condition) {
Logger logger=Logger.getLogger("getDeviceListByCondition");
long startTime=System.currentTimeMillis();
try {
String sql = "select pid from push_device_info where "+condition;
List<Long> list = (ArrayList<Long>)getJdbcTemplateForPushmarketing().query(sql, new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum)
throws SQLException {
Long d = 0l;
d=rs.getLong("pid");
return d;
}
});
log.debug("getDeviceListByCondition sql["+sql+"]");
logger.debug("Call MYSQL getDeviceListByCondition sql["+sql+"]");
logger.debug("Call MYSQL getDeviceListByCondition cost["+(System.currentTimeMillis()-startTime)+"]ms");
if (list.size() > 0) {
return list;
}
return null;
} catch (Exception e) {
throw new PsbDaoException(e);
}
}

@SuppressWarnings("unchecked")
public List<String[]> getDeviceMapListByCondition(String condition) {
Logger logger=Logger.getLogger("getDeviceMapListByCondition");
long startTime=System.currentTimeMillis();
try {
//增加活跃用户的条件过滤
long modifydatetime=(System.currentTimeMillis()-activityIntervalDays*24*3600*1000l)/1000;
String sql = "select pid,pepollversion from push_device_info where UNIX_TIMESTAMP(modifydate)>? and "+condition;
//String sql = "select pid,pepollversion from push_device_info where "+condition;
List<String[]> list = (ArrayList<String[]>)getJdbcTemplateForPushmarketing().query(sql,new Object[] { modifydatetime }, new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum)
throws SQLException {
String[] pid_peversion = new String[2];
pid_peversion[0]=String.valueOf(rs.getLong("pid"));
pid_peversion[1]=rs.getString("pepollversion");
return pid_peversion;
}
});
log.info("getDeviceMapListByCondition sql["+sql+"]first ?["+modifydatetime+"]");
logger.debug("Call MYSQL getDeviceMapListByCondition sql["+sql+"]");
logger.debug("Call MYSQL getDeviceMapListByCondition cost["+(System.currentTimeMillis()-startTime)+"]ms");
if (list.size() > 0) {
return list;
}
return null;
} catch (Exception e) {
throw new PsbDaoException(e);
}
} @SuppressWarnings("unchecked")
public List<String[]> getDeviceMapListByAssginPids(String pids) {
Logger logger=Logger.getLogger("getDeviceMapListByAssginPids");
long startTime=System.currentTimeMillis();
try {
String sql = "select pid,pepollversion from push_device_info where pid in ("+pids+")";
List<String[]> list = (ArrayList<String[]>)getJdbcTemplateForPushmarketing().query(sql, new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum)
throws SQLException {
String[] pid_peversion = new String[2];
pid_peversion[0]=String.valueOf(rs.getLong("pid"));
pid_peversion[1]=rs.getString("pepollversion");
return pid_peversion;
}
});
log.debug("getDeviceMapListByAssginPids sql["+sql+"]");
logger.debug("Call MYSQL getDeviceMapListByAssginPids sql["+sql+"]");
logger.debug("Call MYSQL getDeviceMapListByAssginPids cost["+(System.currentTimeMillis()-startTime)+"]ms");
if (list.size() > 0) {
return list;
}
return null;
} catch (Exception e) {
throw new PsbDaoException(e);
}
}

/*
* push_device_info
deviceInfoVO = new DeviceInfoVO();
deviceInfoVO.setDevice_model(ussData.getRegister_devicemodel());
deviceInfoVO.setDeviceid_type(ussData.getRegister_deviceidtype());
deviceInfoVO.setDeviceid(ussData.getRegister_deviceid()); deviceInfoVO.setIp(IpConvertUtil.toDotFormat(ussData
.getRegister_source_ip())); deviceInfoVO.setOs_version(ussData.getRegister_osversion());
deviceInfoVO.setPe_version(filter_resource);
deviceInfoVO.setDevice_imsi(ussData.getRegister_imsi());

deviceInfoVO.setCreatedate(sdf
.parse(ussData.getRegister_date()));
deviceInfoVO.setModifydate(new Date());
device_model deviceid_type deviceid os_version pe_version device_imsi pepollversion createdate modifydate
*/
public boolean updateDeviceInfoByPidSyc(DeviceInfoVO deviceInfoVO) {
Logger logger=Logger.getLogger("updateDeviceInfoByPidSyc");
//long startTime=System.currentTimeMillis();

try {

//暂时持久化Netaccess_typ apn Pepollversion ip modifydate

String updateSql = "update push_device_info set device_model =?,deviceid_type =?,deviceid =?, " +
" os_version =? , pe_version =?, device_imsi =? , createdate =?, "+
" modifydate =? , pepollversion =? where pid=?;";

int result=getJdbcTemplateForPushmarketing().update(updateSql,
new Object[] {
deviceInfoVO.getDevice_model(),
deviceInfoVO.getDeviceid_type(),
deviceInfoVO.getDeviceid(),
//deviceInfoVO.getIp();
deviceInfoVO.getOs_version(),
deviceInfoVO.getPe_version(),
deviceInfoVO.getDevice_imsi(),
deviceInfoVO.getCreatedate(),
deviceInfoVO.getModifydate(),
deviceInfoVO.getPepollversion(),
deviceInfoVO.getPid()
});
logger.debug("Call MYSQL updateDeviceInfo updateSql["+updateSql+"]"); return result>0;

} catch (Exception e) {
throw new PsbDaoException(e);
}

//return true;
}

/*
* push_device_info
* pid bigint(64) not null, device_model varchar(32),
deviceid_type varchar(10),
deviceid varchar(32),
device_imsi varchar(100),

os_version varchar(100),
cust_version varchar(100),

pe_version varchar(100),
pe_vercode varchar(100),
pe_pkgname varchar(100),

netaccess_type varchar(10),
ip varchar(64),
operation_type varchar(10),
--area_name varchar(64),
country_code varchar(32),
city_name varchar(64),
createtime datetime,
updatetime datetime,
*/
public boolean updateDeviceInfo(DeviceInfoVO deviceInfoVO) {
Logger logger=Logger.getLogger("updateDeviceInfo");
long startTime=System.currentTimeMillis();

try {

//暂时持久化Netaccess_typ apn Pepollversion ip modifydate

String updateSql = "update push_device_info set netaccess_type =?,ip =?,apn =?, " +
" modifydate =? , pepollversion =? ,accessnum =? where pid=?;";

String insertSql = "insert into push_device_info(pid,device_model,deviceid_type,deviceid,device_imsi,os_version,cust_version," +
"pe_version,pe_vercode,pe_pkgname,netaccess_type,ip,operation_type,apn,charge_status,country_code,city_name,sysid,locid,cellid,latitude,longitude,createdate,modifydate,pepollversion,accessnum ) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
int result=getJdbcTemplateForPushmarketing().update(updateSql,
new Object[] {
deviceInfoVO.getNetaccess_type(),
deviceInfoVO.getIp(),
deviceInfoVO.getApn(),
deviceInfoVO.getModifydate(),
deviceInfoVO.getPepollversion(),
deviceInfoVO.getAccessnum(),
deviceInfoVO.getPid()
});
logger.debug("Call MYSQL updateDeviceInfo updateSql["+updateSql+"]");
//更新失败,插入记录
if(result<1){

result= getJdbcTemplateForPushmarketing().update(insertSql,
new Object[] {
deviceInfoVO.getPid(),
deviceInfoVO.getDevice_model(),
deviceInfoVO.getDeviceid_type(),
deviceInfoVO.getDeviceid(),
deviceInfoVO.getDevice_imsi(),
deviceInfoVO.getOs_version(),
deviceInfoVO.getCust_version(),
deviceInfoVO.getPe_version(),
deviceInfoVO.getPe_vercode(),
deviceInfoVO.getPe_pkgname(),
deviceInfoVO.getNetaccess_type(),
deviceInfoVO.getIp(),
deviceInfoVO.getOperation_type(),
deviceInfoVO.getApn(),
deviceInfoVO.getCharge_status(),
//deviceInfoVO.getArea_name(),
deviceInfoVO.getCountry_code(),
deviceInfoVO.getCity_name(),
deviceInfoVO.getSysid(),
deviceInfoVO.getLocid(),
deviceInfoVO.getCellid(),
deviceInfoVO.getLatitude(),
deviceInfoVO.getLongitude(),
new Date(),
new Date(),
deviceInfoVO.getPepollversion(),
deviceInfoVO.getAccessnum(),
});
logger.debug("Call MYSQL updateDeviceInfo insertSql["+insertSql+"]");
}
logger.debug("Call MYSQL updateDeviceInfo cost["+(System.currentTimeMillis()-startTime)+"]ms");
return result>0;

} catch (Exception e) {
throw new PsbDaoException(e);
}

//return true;
} @Override
public boolean saveDeviceInfo(DeviceInfoVO deviceInfoVO) {
Logger logger=Logger.getLogger("saveDeviceInfo");
long startTime=System.currentTimeMillis();


/*
*
deviceInfoVO.getDevice_model(),
deviceInfoVO.getDeviceid_type(),
deviceInfoVO.getDeviceid(),
deviceInfoVO.getDevice_imsi(),
deviceInfoVO.getOs_version(),
deviceInfoVO.getCust_version(),
deviceInfoVO.getPe_version(),
deviceInfoVO.getPe_vercode(),
deviceInfoVO.getPe_pkgname(),
deviceInfoVO.getNetaccess_type(),
deviceInfoVO.getIp(),
deviceInfoVO.getCreatedate(),
deviceInfoVO.getModifydate(),
deviceInfoVO.getPepollversion(),
deviceInfoVO.getChannelname(),
deviceInfoVO.getPid()
createdate,modifydate, pepollversion,channelname
*/
String updateSql = "update push_device_info set device_model =?,deviceid_type =?,deviceid =?, " +
" device_imsi =?,os_version =?,cust_version =?, " +
" pe_version =?,pe_vercode =?,pe_pkgname =?, " +
" netaccess_type =?,ip =?,createdate =?,modifydate=?,pepollversion=?," +
" channelname =?,accessnum =? where pid=?;";

int result=0;
//暂时未持久化apn、operator_code、charge_status等动态信息，只在缓存中更新
String insertSql = "insert into push_device_info(pid,device_model,deviceid_type,deviceid,device_imsi,os_version,cust_version,pe_version,pe_vercode,pe_pkgname,netaccess_type,ip,operation_type,country_code,city_name,operator_code,apn,sysid,locid,cellid,latitude,longitude,charge_status,createdate,modifydate, pepollversion,channelname,accessnum) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
//logger.debug("Call MYSQL saveDeviceInfo insertSql["+insertSql+"]");

try {
result= getJdbcTemplateForPushmarketing().update(insertSql,
new Object[] {
deviceInfoVO.getPid(),
deviceInfoVO.getDevice_model(),
deviceInfoVO.getDeviceid_type(),
deviceInfoVO.getDeviceid(),
deviceInfoVO.getDevice_imsi(),
deviceInfoVO.getOs_version(),
deviceInfoVO.getCust_version(),
deviceInfoVO.getPe_version(),
deviceInfoVO.getPe_vercode(),
deviceInfoVO.getPe_pkgname(),
deviceInfoVO.getNetaccess_type(),
deviceInfoVO.getIp(),
deviceInfoVO.getOperation_type(),
//deviceInfoVO.getArea_name(),
deviceInfoVO.getCountry_code(),
deviceInfoVO.getCity_name(),
deviceInfoVO.getOperator_code(),
deviceInfoVO.getApn(),
deviceInfoVO.getSysid(),
deviceInfoVO.getLocid(),
deviceInfoVO.getCellid(),
deviceInfoVO.getLatitude(),
deviceInfoVO.getLongitude(),
deviceInfoVO.getCharge_status(),
new Date(),
new Date(),
deviceInfoVO.getPepollversion(),
deviceInfoVO.getChannelname(),
deviceInfoVO.getAccessnum()
});
logger.debug("Call MYSQL saveDeviceInfo cost["+(System.currentTimeMillis()-startTime)+"]ms");


return result>0;
} catch (Exception e) {

//插入失败,更新记录
if(result<1){

result=getJdbcTemplateForPushmarketing().update(updateSql,
new Object[] {
deviceInfoVO.getDevice_model(),
deviceInfoVO.getDeviceid_type(),
deviceInfoVO.getDeviceid(),
deviceInfoVO.getDevice_imsi(),
deviceInfoVO.getOs_version(),
deviceInfoVO.getCust_version(),
deviceInfoVO.getPe_version(),
deviceInfoVO.getPe_vercode(),
deviceInfoVO.getPe_pkgname(),
deviceInfoVO.getNetaccess_type(),
deviceInfoVO.getIp(),
deviceInfoVO.getCreatedate(),
deviceInfoVO.getModifydate(),
deviceInfoVO.getPepollversion(),
deviceInfoVO.getChannelname(),
deviceInfoVO.getAccessnum(),
deviceInfoVO.getPid()
});
logger.debug("Call MYSQL saveDeviceInfo updateSql["+updateSql+"]");
}

return result>0;
}
} @SuppressWarnings("unchecked")
@Override
public List<DeviceInfoVO> getHistoryNeedIPConvertDevices() {
List<DeviceInfoVO> list=new ArrayList<DeviceInfoVO>();
Logger logger=Logger.getLogger("getHistoryNeedIPConvertDevices");
long startTime=System.currentTimeMillis();
try {
String sql = "select pid,ip from push_device_info where UNIX_TIMESTAMP(createdate)<? and city_name is null ";
list = getJdbcTemplateForPushmarketing().query(sql,new Object[] { startTime},new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum)
throws SQLException {
DeviceInfoVO d = new DeviceInfoVO();
d.setPid(rs.getLong("pid"));
d.setIp(rs.getString("ip"));
return d;
}
});
logger.debug("Call MYSQL getHistoryNeedIPConvertDevices sql["+sql+"]");
logger.debug("Call MYSQL getHistoryNeedIPConvertDevices cost["+(System.currentTimeMillis()-startTime)+"]ms");
if (list.size() > 0) {
return list;
}
} catch (Exception e) {
throw new PsbDaoException(e);
}
return list;
} @SuppressWarnings("unchecked")
@Override
public List<DeviceInfoVO> getLastDayNeedIPConvertDevices() {
List<DeviceInfoVO> list=new ArrayList<DeviceInfoVO>();
Logger logger=Logger.getLogger("getLastDayNeedIPConvertDevices");
long startTime=System.currentTimeMillis();
long yesterdayTime=(System.currentTimeMillis()-24*3600*1000l)/1000;
try {
String sql = "select * from push_device_info where UNIX_TIMESTAMP(createdate)>? and ( city_name is null or city_name=? );";
list = getJdbcTemplateForPushmarketing().query(sql,new Object[] { yesterdayTime,"null"}, new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum)
throws SQLException {
DeviceInfoVO d = new DeviceInfoVO();
d.setPid(rs.getLong("pid"));
d.setIp(rs.getString("ip"));
return d;
}
});
log.debug("Call MYSQL getLastDayNeedIPConvertDevices sql["+sql+"]");
logger.debug("Call MYSQL getLastDayNeedIPConvertDevices sql["+sql+"]");
logger.debug("Call MYSQL getLastDayNeedIPConvertDevices cost["+(System.currentTimeMillis()-startTime)+"]ms");
if (list.size() > 0) {
return list;
}
} catch (Exception e) {
throw new PsbDaoException(e);
}
return list;
}

@SuppressWarnings("unchecked")
public List<DeviceInfoVO> getLastDaysNeedIPConvertDevices(int lastdays) {
List<DeviceInfoVO> list=new ArrayList<DeviceInfoVO>();
Logger logger=Logger.getLogger("getLastDaysNeedIPConvertDevices");
long startTime=System.currentTimeMillis();
long yesterdayTime=(System.currentTimeMillis()-lastdays*24*3600*1000l)/1000;
try {
String sql = "select * from push_device_info where UNIX_TIMESTAMP(createdate)>? and ( city_name is null or city_name=? );";
list = getJdbcTemplateForPushmarketing().query(sql,new Object[] { yesterdayTime,"null"}, new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum)
throws SQLException {
DeviceInfoVO d = new DeviceInfoVO();
d.setPid(rs.getLong("pid"));
d.setIp(rs.getString("ip"));
return d;
}
});
log.debug("Call MYSQL getLastDaysNeedIPConvertDevices sql["+sql+"]");
logger.debug("Call MYSQL getLastDaysNeedIPConvertDevices sql["+sql+"]");
logger.debug("Call MYSQL getLastDaysNeedIPConvertDevices cost["+(System.currentTimeMillis()-startTime)+"]ms");
if (list.size() > 0) {
return list;
}
} catch (Exception e) {
throw new PsbDaoException(e);
}
return list;
} @Override
public boolean updateDeviceOperationAndCity(long pid, String operation,
String countryCode, String cityName) {
Logger logger=Logger.getLogger("updateDeviceOperationAndCity");
long startTime=System.currentTimeMillis();
try {
String updateSql = "update push_device_info set operation_type =?, country_code=?,city_name=? where pid=?;";

int result=getJdbcTemplateForPushmarketing().update(updateSql,
new Object[] { operation,countryCode,cityName,pid});

logger.debug("Call MYSQL updateDeviceOperationAndCity cost["+(System.currentTimeMillis()-startTime)+"]ms");
return result>0;
} catch (Exception e) {
throw new PsbDaoException(e);
}
}

public boolean saveUss2PushDeviceInfo(DeviceInfoVO deviceInfoVO) {
Logger logger = Logger.getLogger("insertDeviceinfo");
long startTime = System.currentTimeMillis();
try { int result = 0;
String insertSql = "insert into push_device_info(pid,device_model,deviceid_type,deviceid,device_imsi,os_version,cust_version,"
+ "pe_version,pe_vercode,pe_pkgname,netaccess_type,ip,operation_type,city_name,createdate,modifydate ) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"; result = getJdbcTemplateForPushmarketing().update(
insertSql,
new Object[] { deviceInfoVO.getPid(),
deviceInfoVO.getDevice_model(),
deviceInfoVO.getDeviceid_type(),
deviceInfoVO.getDeviceid(),
deviceInfoVO.getDevice_imsi(),
deviceInfoVO.getOs_version(),
deviceInfoVO.getCust_version(),
deviceInfoVO.getPe_version(),
deviceInfoVO.getPe_vercode(),
deviceInfoVO.getPe_pkgname(),
deviceInfoVO.getNetaccess_type(),
deviceInfoVO.getIp(),
deviceInfoVO.getOperation_type(),
deviceInfoVO.getCity_name(),
deviceInfoVO.getCreatedate(),
deviceInfoVO.getCreatedate() });
logger.debug("Uss Uss2PushDeviceInfo insertSql[" + insertSql + "]");
// } logger.debug("Uss Uss2PushDeviceInfo cost["
+ (System.currentTimeMillis() - startTime) + "]ms");
return result > 0;
} catch (Exception e) {
throw new PsbDaoException(e);
}
}
private PushTaskInfoRowMapper pushTaskInfoRowMapper = new PushTaskInfoRowMapper();
/*
drop table if exists push_task_info;
create table push_task_info (
id varchar(32) not null,
task_name varchar(64) default null,
task_type varchar(10) not null,
task_status varchar(10) default null,
task_source_sid varchar(100) default null,
task_source_trust varchar(10) default null,
task_priority varchar(10) default null,
task_condition text,
task_pushcontent text,
task_keywords varchar(100) default null,
task_description text,
task_startdate datetime default null,
task_enddate datetime default null,
assignuser_type int(10) not null,
assignuser_list text,
blacklist_type int(10) not null,
blacklist_content text,
createdate datetime default null,
modifydate datetime default null,
primary key (id)
) engine=innodb default charset=utf8;
*/
private class PushTaskInfoRowMapper implements RowMapper {
public Object mapRow(ResultSet rs, int i) throws SQLException {
PushTaskInfo pushTaskInfo = new PushTaskInfo();
pushTaskInfo.setId(rs.getString("id"));
pushTaskInfo.setTask_name(rs.getString("task_name"));
pushTaskInfo.setTask_biz_type(rs.getString("task_biz_type"));
pushTaskInfo.setTask_priority(rs.getString("task_priority"));
pushTaskInfo.setTask_status(rs.getString("task_status"));
pushTaskInfo.setTask_pushcontent(rs.getString("task_pushcontent"));
pushTaskInfo.setTask_condition(rs.getString("task_condition"));
pushTaskInfo.setAssignuser_type(rs.getString("assignuser_type"));
pushTaskInfo.setAssignuser_list(rs.getString("assignuser_list"));
pushTaskInfo.setBlacklist_type(rs.getString("blacklist_type"));
pushTaskInfo.setBlacklist_content(rs.getString("blacklist_content"));
pushTaskInfo.setTask_startdate(rs.getDate("createdate"));
pushTaskInfo.setTask_source_sid(rs.getString("task_source_sid"));
pushTaskInfo.setTask_source_trust(rs.getString("task_source_trust"));
pushTaskInfo.setTask_keywords(rs.getString("task_keywords"));
pushTaskInfo.setTask_description(rs.getString("task_description"));
pushTaskInfo.setPepollversion(rs.getString("pepollversion"));
pushTaskInfo.setTask_pushurl(rs.getString("task_pushurl"));
pushTaskInfo.setTask_ad_type(rs.getString("task_ad_type"));
pushTaskInfo.setTask_pushtitle(rs.getString("task_pushtitle"));
pushTaskInfo.setTask_adbiz_type(rs.getString("task_adbiz_type"));
pushTaskInfo.setTask_appaction(rs.getString("task_appaction"));
pushTaskInfo.setTask_msg_ttl(rs.getInt("task_msg_ttl"));
return pushTaskInfo;
}
}

@SuppressWarnings("unchecked")
@Override
public List<PushTaskInfo> queryPushTaskListInOneDay(String task_status) {

String start_datestr=DateUtil.DateBefAft(-1, "yyyy-MM-dd");
start_datestr+=" 23:59:59";
Date startdate=DateUtil.parseStringToDate(start_datestr);

long starttime=startdate.getTime();
long endtime=starttime+24*3600*1000l;
log.debug("queryPushTaskListInOneDay start_datestr["+start_datestr+"] startTIME["+startdate.getTime()+"]"+"endTIME["+endtime+"]");

List<PushTaskInfo> list=new ArrayList<PushTaskInfo>();
Logger logger=Logger.getLogger("queryPushTaskListInOneDay");
long startTime=System.currentTimeMillis();
//long yesterdayTime=System.currentTimeMillis()-24*3600*1000l;
try {
String sql = "select * from push_task_info where UNIX_TIMESTAMP(task_startdate)>? and UNIX_TIMESTAMP(task_startdate)<? and task_status=? ;";
list = getJdbcTemplateForPushmarketing().query(sql,new Object[] { starttime/1000,endtime/1000,task_status},pushTaskInfoRowMapper);
log.debug("sql["+sql+"]first ?["+starttime/1000+"]second ?["+endtime/1000+"]third ?["+task_status+"]");
logger.debug("Call MYSQL queryPushTaskListInOneDay sql["+sql+"]");
logger.debug("Call MYSQL queryPushTaskListInOneDay cost["+(System.currentTimeMillis()-startTime)+"]ms");
if (list.size() > 0) {
return list;
}
} catch (Exception e) {
throw new PsbDaoException(e);
}
return list;
}

@SuppressWarnings("unchecked")
public List<PushTaskInfo> queryPushTaskListDays(int lastdays,String task_status) {

String start_datestr=DateUtil.DateBefAft(-(lastdays), "yyyy-MM-dd");
start_datestr+=" 23:59:59";
Date startdate=DateUtil.parseStringToDate(start_datestr);

long starttime=startdate.getTime();
long endtime=starttime+24*3600*1000l;
log.debug("queryPushTaskListInOneDay start_datestr["+start_datestr+"] startTIME["+startdate.getTime()+"]"+"endTIME["+endtime+"]");

List<PushTaskInfo> list=new ArrayList<PushTaskInfo>();
Logger logger=Logger.getLogger("queryPushTaskListInOneDay");
long startTime=System.currentTimeMillis();
//long yesterdayTime=System.currentTimeMillis()-24*3600*1000l;
try {
String sql = "select * from push_task_info where UNIX_TIMESTAMP(task_startdate)>? and UNIX_TIMESTAMP(task_startdate)<? and task_status=? ;";
list = getJdbcTemplateForPushmarketing().query(sql,new Object[] { starttime/1000,endtime/1000,task_status},pushTaskInfoRowMapper);
log.debug("sql["+sql+"]");
logger.debug("Call MYSQL queryPushTaskListInOneDay sql["+sql+"]");
logger.debug("Call MYSQL queryPushTaskListInOneDay cost["+(System.currentTimeMillis()-startTime)+"]ms");
if (list.size() > 0) {
return list;
}
} catch (Exception e) {
throw new PsbDaoException(e);
}
return list;
}

@SuppressWarnings("unchecked")
@Override
public PushTaskInfo queryPushTaskInfoById(String id,String task_status) {

List<PushTaskInfo> list=new ArrayList<PushTaskInfo>();
Logger logger=Logger.getLogger("queryPushTaskInfoById");
long startTime=System.currentTimeMillis();
//long yesterdayTime=System.currentTimeMillis()-24*3600*1000l;
try {
String sql = "select * from push_task_info where id=? and task_status=? ;";
list = getJdbcTemplateForPushmarketing().query(sql,new Object[] { id, task_status},pushTaskInfoRowMapper);
logger.debug("Call MYSQL queryPushTaskListInOneDay sql["+sql+"]");
logger.debug("Call MYSQL queryPushTaskListInOneDay cost["+(System.currentTimeMillis()-startTime)+"]ms");
if (list.size() > 0) {
return list.get(0);
}
} catch (Exception e) {
throw new PsbDaoException(e);
}
return null;
} @Override
public boolean updatePushTaskInfoById(String id, String taskStatus) {
Logger logger=Logger.getLogger("updatePushTaskInfoById");
long startTime=System.currentTimeMillis();
try {
String updateSql = "update push_task_info set task_status=? where id=?;";

int result=getJdbcTemplateForPushmarketing().update(updateSql,
new Object[] { taskStatus,id});

logger.debug("Call MYSQL updatePushTaskInfoById cost["+(System.currentTimeMillis()-startTime)+"]ms");
return result>0;
} catch (Exception e) {
throw new PsbDaoException(e);
}
}

@Override
public boolean updatePushTaskInfoActdevicenum(String id, int task_actdevice_cnt) {
Logger logger=Logger.getLogger("updatePushTaskInfoActdevicenum");
long startTime=System.currentTimeMillis();
try {
String updateSql = "update push_task_info set task_actdevice_cnt=? where id=?;";

int result=getJdbcTemplateForPushmarketing().update(updateSql,
new Object[] { task_actdevice_cnt,id});

logger.debug("Call MYSQL updatePushTaskInfoActdevicenum cost["+(System.currentTimeMillis()-startTime)+"]ms");
return result>0;
} catch (Exception e) {
throw new PsbDaoException(e);
}
}
@Override
public boolean updatePushTaskInfoById(String id, String taskStatus,int devicenum) {
Logger logger=Logger.getLogger("updatePushTaskInfoById");
long startTime=System.currentTimeMillis();
try {
String updateSql = "update push_task_info set task_status=?,task_actdevice_cnt=? where id=?;";

int result=getJdbcTemplateForPushmarketing().update(updateSql,
new Object[] { taskStatus,devicenum ,id});

logger.debug("Call MYSQL updatePushTaskInfoById cost["+(System.currentTimeMillis()-startTime)+"]ms");
return result>0;
} catch (Exception e) {
throw new PsbDaoException(e);
}
}

@Override
public boolean updatePushTaskInfoById(PushTaskInfo pushTaskInfo) {
// TODO Auto-generated method stub

return false;
}

@SuppressWarnings("unchecked")
@Override
public boolean flushDeviceDimenBylastdays(int lastdays,final String dimenkey) {
Logger logger=Logger.getLogger("flushDeviceModelDimenBylastdays");
long startTime=System.currentTimeMillis();

String start_daystr=DateUtil.DateBefAft(-(lastdays), "yyyy-MM-dd");
String end_daystr=DateUtil.DateBefAft(0, "yyyy-MM-dd");
//String act_startdaystr=DateUtil.DateBefAft(-(activityIntervalDays), "yyyy-MM-dd");
String start_datestr=start_daystr+" 00:00:00";
String end_datestr=end_daystr+" 23:59:59";
//String act_startdatestr=act_startdaystr+" 00:00:00";
Date startdate=DateUtil.parseStringToDate(start_datestr);
Date enddate=DateUtil.parseStringToDate(end_datestr);

long act_starttime=(System.currentTimeMillis()-activityIntervalDays*24*3600*1000l)/1000;
log.debug("Call MYSQL flushDeviceDimenBylastdays start_daystr["+start_daystr+"]startdate.getTime()["+startdate.getTime()+"] ");
log.debug("Call MYSQL flushDeviceDimenBylastdays end_daystr["+end_daystr+"]enddate.getTime()["+enddate.getTime()+"] ");
log.debug("Call MYSQL flushDeviceDimenBylastdays act_starttime["+act_starttime+"] ");

List<DeviceDimenInfo> list=new ArrayList<DeviceDimenInfo>();
List<DeviceDimenInfo> batchInsertlist=new ArrayList<DeviceDimenInfo>(); try {
String sql = "select "+dimenkey+" ,count(*) as act_devicenum from push_device_info where UNIX_TIMESTAMP(createdate)>? and UNIX_TIMESTAMP(createdate) <? and UNIX_TIMESTAMP(modifydate)>? group by "+dimenkey+" ;";
list = getJdbcTemplateForPushmarketing().query(sql,new Object[] { (startdate.getTime())/1000,(enddate.getTime())/1000,act_starttime}, new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum)
throws SQLException {
DeviceDimenInfo d = new DeviceDimenInfo();
d.setDimen_key(dimenkey);
if(StringUtils.isBlank(rs.getString(dimenkey))||"null".equals(rs.getString(dimenkey))||"unknown".equals(rs.getString(dimenkey))||"unkown".equals(rs.getString(dimenkey))){
return null;
}else{
d.setDimen_value(rs.getString(dimenkey));
}

d.setAct_devicenum(rs.getInt("act_devicenum"));
//暂时固定为0
d.setAll_devicenum(0);
d.setP_htmlfilepath("");
return d;
}
});

if(log.isDebugEnabled()){
sql="select "+dimenkey+" ,count(*) as act_devicenum from push_device_info where UNIX_TIMESTAMP(createdate)>"+
(startdate.getTime())/1000+ " and UNIX_TIMESTAMP(createdate) <"+(enddate.getTime())/1000+" and UNIX_TIMESTAMP(modifydate)>"+act_starttime+" group by "+dimenkey+";";
log.debug("Call MYSQL flushDeviceDimenBylastdays sql["+sql+"]");
logger.debug("Call MYSQL flushDeviceDimenBylastdays sql["+sql+"]");
logger.debug("Call MYSQL flushDeviceDimenBylastdays cost["+(System.currentTimeMillis()-startTime)+"]ms");
}


if (list!=null&&list.size() > 0) {
//如果数据库不存在该项维度信息，则添加至批量插入维度信息List
for(DeviceDimenInfo ddi:list){
if(ddi!=null&&!getDeviceDimenInfo(ddi)){
batchInsertlist.add(ddi);
}
}

}

saveDeviceDimenlist(batchInsertlist);

return true;

} catch (Exception e) {
throw new PsbDaoException(e);

}

//return list;
}

@SuppressWarnings("unchecked")
public boolean flushDeviceDimenBylastManydays(int lastdays,final String dimenkey) {
Logger logger=Logger.getLogger("flushDeviceDimenBylastManydays");
long startTime=System.currentTimeMillis();

String start_daystr=DateUtil.DateBefAft(-(lastdays), "yyyy-MM-dd");
String end_daystr=DateUtil.DateBefAft(0, "yyyy-MM-dd");
String start_datestr=start_daystr+" 00:00:00";
String end_datestr=end_daystr+" 23:59:59";
Date startdate=DateUtil.parseStringToDate(start_datestr);
Date enddate=DateUtil.parseStringToDate(end_datestr);

long act_starttime=(System.currentTimeMillis()-lastdays*24*3600*1000l)/1000;
log.debug("Call MYSQL flushDeviceDimenBylastdays start_daystr["+start_daystr+"]startdate.getTime()["+startdate.getTime()+"] ");
log.debug("Call MYSQL flushDeviceDimenBylastdays end_daystr["+end_daystr+"]enddate.getTime()["+enddate.getTime()+"] ");
log.debug("Call MYSQL flushDeviceDimenBylastdays act_starttime["+act_starttime+"] ");

List<DeviceDimenInfo> list=new ArrayList<DeviceDimenInfo>();
List<DeviceDimenInfo> batchInsertlist=new ArrayList<DeviceDimenInfo>();


//查询出当前维度信息表dimenkey对应的dimen_value集合：
String querydimenvalue_sql = "select dimen_value from device_dimen_info where dimen_key='"+dimenkey+"';";
List<String> dimenvaluelist=new ArrayList<String>();
try {
dimenvaluelist = getJdbcTemplateForPushmarketing().query(querydimenvalue_sql,new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum)
throws SQLException {
String dv=rs.getString("dimen_value");
return dv;
}
});
} catch (Exception e) {
throw new PsbDaoException(e);
}

//按最近X天范围,新增/更新维度信息(活跃设备数)
try {
String sql = "select "+dimenkey+" ,count(*) as act_devicenum from push_device_info where UNIX_TIMESTAMP(modifydate)>? and UNIX_TIMESTAMP(modifydate) <? group by "+dimenkey+" ;";
//list = getJdbcTemplateForPushmarketing().query(sql,new Object[] { (startdate.getTime())/1000,(enddate.getTime())/1000,act_starttime}, new RowMapper() {
list = getJdbcTemplateForPushmarketing().query(sql,new Object[] { (startdate.getTime())/1000,(enddate.getTime())/1000}, new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum)
throws SQLException {
DeviceDimenInfo d = new DeviceDimenInfo();
//d.setId(rs.getString("id"));
d.setDimen_key(dimenkey);
if(StringUtils.isBlank(rs.getString(dimenkey))||"null".equals(rs.getString(dimenkey))||"unknown".equals(rs.getString(dimenkey))||"unkown".equals(rs.getString(dimenkey))){
return null;
}else{
d.setDimen_value(rs.getString(dimenkey));
}

d.setAct_devicenum(rs.getInt("act_devicenum"));
//暂时固定为0
d.setAll_devicenum(0);
d.setP_htmlfilepath("");
return d;
}
});
if(log.isDebugEnabled()){
sql="select "+dimenkey+" ,count(*) as act_devicenum from push_device_info where UNIX_TIMESTAMP(modifydate) >"+(startdate.getTime())/1000+" and UNIX_TIMESTAMP(modifydate)<"+(enddate.getTime())/1000+" group by "+dimenkey+";";
log.debug("Call MYSQL flushDeviceDimenBylastdays sql["+sql+"]");
}
logger.debug("Call MYSQL flushDeviceDimenBylastdays sql["+sql+"]");
logger.debug("Call MYSQL flushDeviceDimenBylastdays cost["+(System.currentTimeMillis()-startTime)+"]ms");

if (list!=null&&list.size() > 0) {
for(DeviceDimenInfo ddi:list){
if(ddi!=null){
if(!getDeviceDimenInfo(ddi)){
//如果数据库不存在该项维度信息，则添加至批量插入维度信息List
batchInsertlist.add(ddi);
}else {
//否则，批量update维度信息活跃设备数。
updateDeviceDimen(ddi);
}
}
}
}

//保存新增维度信息
saveDeviceDimenlist(batchInsertlist);

//删除不复存在的维度信息nowdimenkeylist[now]:dimenkeylist[all]
List<String> nowdimenvaluelist=new ArrayList<String>();
//整理当前维度信息
if (list!=null&&list.size() > 0) {
for(DeviceDimenInfo ddi:list){
if(ddi!=null && ddi.getDimen_value()!=null && !StringUtils.isBlank(ddi.getDimen_value())){
nowdimenvaluelist.add(ddi.getDimen_value());
}
}
}
if (nowdimenvaluelist!=null&&nowdimenvaluelist.size() > 0) {
for(String dv:dimenvaluelist){
if(!nowdimenvaluelist.contains(dv)){
delDeviceDimen(dimenkey,dv);
}
}
}

return true;

} catch (Exception e) {
throw new PsbDaoException(e);

}

//return list;
} /*
`id` varchar(32) NOT NULL,
`dimen_key` varchar(20) DEFAULT NULL,
`dimen_value` varchar(100) DEFAULT NULL,
`all_devicenum` int(32) DEFAULT NULL,
`act_devicenum` int(32) DEFAULT NULL,
`p_htmlfilepath` varchar(255) DEFAULT NULL,
`createdate` datetime DEFAULT NULL,
`modifydate` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
*/
public Boolean saveDeviceDimenlist(final List<DeviceDimenInfo> list) {
Logger logger=Logger.getLogger("saveDeviceDimenlist");
long startTime=System.currentTimeMillis();
try {
String sql = "insert into device_dimen_info(id,dimen_key,dimen_value,act_devicenum,all_devicenum,p_htmlfilepath) values(?,?,?,?,?,?)";
getJdbcTemplateForPushmarketing().batchUpdate(sql,
new BatchPreparedStatementSetter() {
@SuppressWarnings("unchecked")
private Iterator it = list.iterator(); public int getBatchSize() {
return list.size();
} public void setValues(PreparedStatement ps, int i)
throws SQLException { if (it.hasNext()) {
DeviceDimenInfo ddi = (DeviceDimenInfo) it.next();
int j = 1;
ps.setString(j++, CommonUtil.getUUID());
ps.setString(j++, ddi.getDimen_key());
if(ddi.getDimen_value()==null||"null".equals(ddi.getDimen_value())){
ddi.setDimen_value("");
}
ps.setString(j++, ddi.getDimen_value());
ps.setInt(j++, ddi.getAct_devicenum());
ps.setInt(j++, ddi.getAll_devicenum());
ps.setString(j++, "");

if(log.isDebugEnabled()){
String sql="insert into device_dimen_info("+ddi.getDimen_key()+","+ddi.getDimen_value()+","+ddi.getAct_devicenum()+");";
log.debug("insert DeviceDimen sql["+sql+"]");
}
}
}
});


logger.debug("Call MYSQL saveDeviceDimenlist cost["+(System.currentTimeMillis()-startTime)+"]ms");
return true;
} catch (Exception e) {
throw new PsbDaoException(e);
}

}

public Boolean updateDeviceDimen(DeviceDimenInfo ddi) {
Logger logger=Logger.getLogger("updateDeviceDimen");
long startTime=System.currentTimeMillis();
try {
String updateSql = "update device_dimen_info set act_devicenum=? where dimen_key=? and dimen_value=? ;";
int result=getJdbcTemplateForPushmarketing().update(updateSql,
new Object[] { ddi.getAct_devicenum(),ddi.getDimen_key() ,ddi.getDimen_value()});

if(log.isDebugEnabled()){
updateSql="update device_dimen_info set act_devicenum="+ddi.getAct_devicenum()+" where Dimen_key="+ddi.getDimen_key()+"Dimen_value="+ddi.getDimen_value()+";";
log.debug("Call MYSQL updateDeviceDimen sql["+updateSql+"]");
}
logger.debug("Call MYSQL updateDeviceDimen cost["+(System.currentTimeMillis()-startTime)+"]ms");

return result>0;
} catch (Exception e) {

throw new PsbDaoException(e);
}

}

public Boolean delDeviceDimen(String dimenkey,String dimen_value) {
Logger logger=Logger.getLogger("delDeviceDimen");
long startTime=System.currentTimeMillis();
try {
String delSql = "delete from device_dimen_info where dimen_key=? and dimen_value=? ;";
int result=getJdbcTemplateForPushmarketing().update(delSql,new Object[] {dimenkey,dimen_value});

if(log.isDebugEnabled()){
delSql="delete from device_dimen_info where dimen_key="+dimenkey+" and dimen_value="+dimen_value+";";
log.debug("Call MYSQL delDeviceDimen sql["+delSql+"]");
}
logger.debug("Call MYSQL delDeviceDimen cost["+(System.currentTimeMillis()-startTime)+"]ms");

return result>0;
} catch (Exception e) {

throw new PsbDaoException(e);
}

}

/*
`id` varchar(32) NOT NULL,
`dimen_key` varchar(20) DEFAULT NULL,
`dimen_value` varchar(100) DEFAULT NULL,
`all_devicenum` int(32) DEFAULT NULL,
`act_devicenum` int(32) DEFAULT NULL,
`p_htmlfilepath` varchar(255) DEFAULT NULL,
`createdate` datetime DEFAULT NULL,
`modifydate` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
*/
public boolean getDeviceDimenInfo(DeviceDimenInfo deviceDimenInfo) {
Logger logger=Logger.getLogger("getDeviceDimenInfo");
long startTime=System.currentTimeMillis();
try {

//暂时未持久化apn、operator_code、charge_status等动态信息，只在缓存中更新

String sql = "select count(*) from device_dimen_info where dimen_key =? and dimen_value =? ;";

int result=getJdbcTemplateForPushmarketing().queryForInt(sql,new Object[] {deviceDimenInfo.getDimen_key(),deviceDimenInfo.getDimen_value()});
logger.debug("Call MYSQL getDeviceDimenInfo sql["+sql+"]");

logger.debug("Call MYSQL getDeviceDimenInfo cost["+(System.currentTimeMillis()-startTime)+"]ms");

return result>0;
} catch (Exception e) {
throw new PsbDaoException(e);
}
} /*
* reportType: --1:日报 7：周报 10：sid接受消息分布统计 11：sid到达消息分布统计
*
*/
public long savePushIncatorToDB(int reportType,String[] pushindicatorargs) {

//保存至数据库psb_push_indicator表
// create table psb_push_indicator
// (
// id bigint not null auto_increment,
// indicator_type int, --1:日报 7：周报 10：sid接受消息分布统计 11：sid到达消息分布统计
// commit_time bigint not null,
// devices_allcnt bigint,
// devices_newcnt bigint,
// devices_activitycnt bigint,
// receivedmsgs_allcnt bigint,
// dealmsgs_allcnt bigint,
// arrivedmsgs_allcnt bigint,
// tianqi_msgs_cnt bigint,
// neirong_msgs_cnt bigint,
// qiyeyoujian_msgs_cnt bigint,
// gexinghuapush_msgs_cnt bigint,
// leshangdian_msgs_cnt bigint,
// gerenyoujian_msgs_cnt bigint,
// rsys001_msgs_cnt bigint,
// qita_msgs_cnt bigint,
// singlepoll_devices_cnt bigint,
// primary key (id),
// KEY `idx_commit_time` (`commit_time`)
// )ENGINE=InnoDB;

long commit_time=System.currentTimeMillis();
StringBuffer sqlsb=new StringBuffer("insert into psb_push_indicator(indicator_type,commit_time,devices_allcnt,devices_newcnt,devices_activitycnt,receivedmsgs_allcnt,dealmsgs_allcnt,arrivedmsgs_allcnt,tianqi_msgs_cnt,neirong_msgs_cnt,qiyeyoujian_msgs_cnt,gexinghuapush_msgs_cnt,leshangdian_msgs_cnt,gerenyoujian_msgs_cnt,rsys001_msgs_cnt,qita_msgs_cnt,singlepoll_devices_cnt) values(");
sqlsb.append(reportType);
sqlsb.append(",");
sqlsb.append(commit_time);
sqlsb.append(",");

for(int i=0;i<pushindicatorargs.length;i++){

if (StringUtils.isBlank(pushindicatorargs[i])) {
log.debug(" pushindicatorargs["+i+"] IS Blank");
sqlsb.append(0);
if(i<pushindicatorargs.length-1){
sqlsb.append(",");
}
continue;
}
sqlsb.append(Long.parseLong(pushindicatorargs[i]));
if(i<pushindicatorargs.length-1){
sqlsb.append(",");
}
}
sqlsb.append(") ;");

log.debug("insert into psb_push_indicator sql["+sqlsb.toString()+"]");

int result=getJdbcTemplateForPushmarketing().update(sqlsb.toString());

return result;
}




/**
* @param deviceInfoVO
* @return
* @author
*/
public boolean saveDeviceInfoById(DeviceInfoVO deviceInfoVO) {
Logger logger = Logger.getLogger("saveDeviceInfoById");
System.out.println("---------------------------start to save deviceinfo " + deviceInfoVO.getPid());
long startTime = System.currentTimeMillis();
try {
int result = 0;
String insertSql = "insert into push_device_info(pid,device_model,deviceid_type,deviceid,device_imsi,os_version,cust_version,"
+ "pe_version,pe_vercode,pe_pkgname,netaccess_type,ip,operation_type,city_name,createdate,modifydate ) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"; result = getJdbcTemplateForPushmarketing().update(
insertSql,
new Object[] { deviceInfoVO.getPid(),
deviceInfoVO.getDevice_model(),
deviceInfoVO.getDeviceid_type(),
deviceInfoVO.getDeviceid(),
deviceInfoVO.getDevice_imsi(),
deviceInfoVO.getOs_version(),
deviceInfoVO.getCust_version(),
deviceInfoVO.getPe_version(),
deviceInfoVO.getPe_vercode(),
deviceInfoVO.getPe_pkgname(),
deviceInfoVO.getNetaccess_type(),
deviceInfoVO.getIp(),
deviceInfoVO.getOperation_type(),
deviceInfoVO.getCity_name(),
deviceInfoVO.getCreatedate(),
deviceInfoVO.getCreatedate() });
System.out.println("--------------------Uss Uss2PushDeviceInfo insertSql[" + insertSql + "]"); System.out.println("--------------------Uss Uss2PushDeviceInfo savedeviceinfo cost["
+ (System.currentTimeMillis() - startTime) + "]ms");
return result > 0;
} catch (Exception e) {
throw new PsbDaoException(e);
}
} /**
* @param deviceInfoVOList
* @return
* @author
*/
public boolean saveDeviceInfoList(List<DeviceInfoVO> deviceInfoVOList) {// String insertSql = "insert into push_device_info(pid,device_model,deviceid_type,deviceid,device_imsi,os_version,cust_version,"
+ "pe_version,pe_vercode,pe_pkgname,netaccess_type,ip,operation_type,city_name,createdate,modifydate ) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
try {
getJdbcTemplateForPushmarketing().batchUpdate(insertSql,
new MyBatchPreparedStatementSetter(deviceInfoVOList));
} catch (org.springframework.dao.DataAccessException e) {
e.printStackTrace();
} return true;
} /**
* @param deviceInfoVOList
* @author liaozl1
*/

public void saveBatchDeviceInfo(List<DeviceInfoVO> deviceInfoVOList) {
long startTime = System.currentTimeMillis();
StringBuffer batchSql = new StringBuffer();
int count = 0;

batchSql
.append("insert into push_device_info( pid,device_model,deviceid_type,deviceid,device_imsi,os_version,cust_version,");
batchSql
.append("pe_version,pe_vercode,pe_pkgname,netaccess_type,ip,operation_type,city_name,createdate,modifydate ) values");
if (deviceInfoVOList != null && deviceInfoVOList.size() != 0) {

int size = deviceInfoVOList.size();
for (DeviceInfoVO it : deviceInfoVOList) {
if (it.getCreatedate() != null) {
batchSql.append("(" + it.getPid() + ",'"
+ it.getDevice_model() + "','"
+ it.getDeviceid_type() + "','" + it.getDeviceid()
+ "','" + it.getDevice_imsi() + "','"
+ it.getOs_version() + "','" + it.getCust_version()
+ "','" + it.getPe_version() + "','"
+ it.getPe_vercode() + "','" + it.getPe_pkgname()
+ "','" + it.getNetaccess_type() + "','"
+ it.getIp() + "','" + it.getOperation_type()
+ "','" + it.getCity_name() + "','"
+ sdf.format(it.getCreatedate()) + "','"
+ sdf.format(it.getCreatedate()) + "')");
if (count != size - 1) {
batchSql.append(",");
}
count++;
} else {
batchSql.append("(" + it.getPid() + ",'"
+ it.getDevice_model() + "','"
+ it.getDeviceid_type() + "','" + it.getDeviceid()
+ "','" + it.getDevice_imsi() + "','"
+ it.getOs_version() + "','" + it.getCust_version()
+ "','" + it.getPe_version() + "','"
+ it.getPe_vercode() + "','" + it.getPe_pkgname()
+ "','" + it.getNetaccess_type() + "','"
+ it.getIp() + "','" + it.getOperation_type()
+ "','" + it.getCity_name() + "','" + "','" + "')");
if (count != size - 1) {
batchSql.append(",");
}
count++;
}
}
getJdbcTemplateForPushmarketing().execute(batchSql.toString());
} log.info("sqls : " + batchSql.toString());
log.info("Uss Uss2PushDeviceInfoList cost["
+ (System.currentTimeMillis() - startTime) + "]ms"); }
public void test2(List<DeviceInfoVO> deviceInfoVOList) {
long startTime = System.currentTimeMillis();
List<String> sqlList = new ArrayList<String>();
String[] sqls = null; for (DeviceInfoVO it : deviceInfoVOList) {
StringBuffer batchSql = new StringBuffer();
batchSql
.append("insert into push_device_info( pid,device_model,deviceid_type,deviceid,device_imsi,os_version,cust_version,");
batchSql
.append("pe_version,pe_vercode,pe_pkgname,netaccess_type,ip,operation_type,city_name,createdate,modifydate ) values");
batchSql.append("(" + it.getPid() + ",'" + it.getDevice_model()
+ "','" + it.getDeviceid_type() + "','" + it.getDeviceid()
+ "','" + it.getDevice_imsi() + "','" + it.getOs_version()
+ "','" + it.getCust_version() + "','" + it.getPe_version()
+ "','" + it.getPe_vercode() + "','" + it.getPe_pkgname()
+ "','" + it.getNetaccess_type() + "','" + it.getIp()
+ "','" + it.getOperation_type() + "','"
+ it.getCity_name() + "','"
+ sdf.format(it.getCreatedate()) + "','"
+ sdf.format(it.getCreatedate()) + "')");
sqlList.add(batchSql.toString());
}
if (sqlList != null && sqlList.size() != 0) {
sqls = new String[sqlList.size()];
sqls = (String[]) sqlList.toArray();
} getJdbcTemplateForPushmarketing().batchUpdate(sqls);
log.debug("Uss Uss2PushDeviceInfoList");
log.debug("Uss Uss2PushDeviceInfoList cost["
+ (System.currentTimeMillis() - startTime) + "]ms"); }
public void test1(List<DeviceInfoVO> deviceInfoVOList) {
long startTime = System.currentTimeMillis();
List<String> sqlList = new ArrayList<String>();
String[] sqls = null; for (DeviceInfoVO it : deviceInfoVOList) {
StringBuffer batchSql = new StringBuffer();
batchSql
.append("insert into push_device_info( pid,device_model,deviceid_type,deviceid,device_imsi,os_version,cust_version,");
batchSql
.append("pe_version,pe_vercode,pe_pkgname,netaccess_type,ip,operation_type,city_name,createdate,modifydate ) values");
batchSql.append("(" + it.getPid() + ",'" + it.getDevice_model()
+ "','" + it.getDeviceid_type() + "','" + it.getDeviceid()
+ "','" + it.getDevice_imsi() + "','" + it.getOs_version()
+ "','" + it.getCust_version() + "','" + it.getPe_version()
+ "','" + it.getPe_vercode() + "','" + it.getPe_pkgname()
+ "','" + it.getNetaccess_type() + "','" + it.getIp()
+ "','" + it.getOperation_type() + "','"
+ it.getCity_name() + "','"
+ sdf.format(it.getCreatedate()) + "','"
+ sdf.format(it.getCreatedate()) + "')");
sqlList.add(batchSql.toString());
}
if (sqlList != null && sqlList.size() != 0) {
sqls = new String[sqlList.size()];
sqls = (String[]) sqlList.toArray();
} getJdbcTemplateForPushmarketing().batchUpdate(sqls);
log.debug("Uss Uss2PushDeviceInfoList");
log.debug("Uss Uss2PushDeviceInfoList cost["
+ (System.currentTimeMillis() - startTime) + "]ms"); }

class MyBatchPreparedStatementSetter implements BatchPreparedStatementSetter {
final List<DeviceInfoVO> temList; /** 通过构造函数把要插入的数据传递进来处理 */
public MyBatchPreparedStatementSetter(List<DeviceInfoVO> list) {
temList = list;
} public int getBatchSize() {
return temList.size();
} @Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
// TODO Auto-generated method stub
DeviceInfoVO deviceInfoVO = (DeviceInfoVO) temList.get(i); ps.setLong(1, deviceInfoVO.getPid());
ps.setString(2, deviceInfoVO.getDevice_model());
ps.setString(3, deviceInfoVO.getDeviceid_type());
ps.setString(4, deviceInfoVO.getDeviceid());
ps.setString(5, deviceInfoVO.getDevice_imsi());
ps.setString(6, deviceInfoVO.getOs_version());
ps.setString(7, deviceInfoVO.getCust_version());
ps.setString(8, deviceInfoVO.getPe_version());
ps.setString(9, deviceInfoVO.getPe_vercode());
ps.setString(10, deviceInfoVO.getPe_pkgname());
ps.setString(11, deviceInfoVO.getNetaccess_type());
ps.setString(12, deviceInfoVO.getIp());
ps.setString(13, deviceInfoVO.getOperation_type());
ps.setString(14, deviceInfoVO.getCity_name());
ps.setString(15, deviceInfoVO.getCreatedate().toString());
ps.setString(16, deviceInfoVO.getCreatedate().toString());
} }

}