Java tutorial
package com.consult.app.dao.impl; import java.io.File; import java.io.FileWriter; import java.io.IOException; import java.math.BigInteger; import java.util.ArrayList; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.Transaction; import org.hibernate.transform.Transformers; import org.hibernate.type.StandardBasicTypes; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; import com.consult.app.async.AsyncProcess; import com.consult.app.dao.CargoMessageDao; import com.consult.app.dao.interceptor.CargoMessageInterceptor; import com.consult.app.modal.CargoMessage; import com.consult.app.modal.City; import com.consult.app.request.IdRequest; import com.consult.app.request.IdTypeUpdateTimeRequest; import com.consult.app.request.admin.GetCargosRequest; import com.consult.app.request.cargo.NewAsignedCargoRequest; import com.consult.app.request.cargo.SearchCargoRequest; import com.consult.app.request.user.MessageContactItem; import com.consult.app.request.user.TelephoneBeforeAfterCountRequest; import com.consult.app.request.user.TelephoneRequest; import com.consult.app.response.cargo.CargoMessageItem; import com.consult.app.response.cargo.SalesItem; import com.consult.app.utils.Constant; import com.consult.app.utils.Cookie; import com.consult.app.utils.LogUtil; import com.consult.app.utils.RandomNumber; import com.consult.app.utils.StringUtil; import com.consult.app.utils.TimeUtils; @Repository public class CargoMessageDaoImpl implements CargoMessageDao { @Autowired private SessionFactory sessionFactory; @Autowired private AsyncProcess updateSubscribeProcess; @Autowired private SessionFactory sessionReadFactory; public void setSessionReadFactory(SessionFactory sessionReadFactory) { this.sessionReadFactory = sessionReadFactory; } public void setSessionFactory(SessionFactory factory) { this.sessionFactory = factory; } public int save(CargoMessage obj) { Session session = null; Transaction tx = null; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(obj.getUpdateTime()); session = sessionFactory.openSession(inter); tx = session.beginTransaction(); tx.setTimeout(Constant.TRANSCTION_TIMEOUT); session.save(obj); tx.commit(); return 1; } catch (Exception e) { e.printStackTrace(); if (tx != null) { tx.rollback(); } return 0; } finally { if (session != null) { session.close(); session = null; } } // sessionFactory.getCurrentSession().save(obj); } public void update(CargoMessage obj) { Session session = null; Transaction tx = null; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(obj.getUpdateTime()); session = sessionFactory.openSession(inter); tx = session.beginTransaction(); tx.setTimeout(Constant.TRANSCTION_TIMEOUT); session.update(obj); tx.commit(); } catch (Exception e) { e.printStackTrace(); if (tx != null) { tx.rollback(); } } finally { if (session != null) { session.close(); session = null; } } // sessionFactory.getCurrentSession().update(obj); } public void delete(CargoMessage obj) { Session session = null; Transaction tx = null; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(obj.getUpdateTime()); session = sessionFactory.openSession(inter); tx = session.beginTransaction(); tx.setTimeout(Constant.TRANSCTION_TIMEOUT); session.delete(obj); tx.commit(); } catch (Exception e) { e.printStackTrace(); if (tx != null) { tx.rollback(); } } finally { if (session != null) { session.close(); session = null; } } // sessionFactory.getCurrentSession().delete(obj); } public CargoMessage findById(Long id) { Session session = null; CargoMessage message = null; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(); long triggerTime = System.currentTimeMillis(); // for(int i = 0; i < Constant.SEARCH_TABLE_COUNT; i++) { inter.setShardCriteria(triggerTime); session = sessionFactory.openSession(inter); Query query = session.createQuery("from CargoMessage message where message.messageId=?"); query.setLong(0, id); message = (CargoMessage) query.uniqueResult(); if (message != null) { return message; } if (inter.isFinishSearch(0)) { return null; } triggerTime = inter.getTriggerTime(); // } return message; } catch (Exception e) { e.printStackTrace(); return null; } finally { if (session != null) { session.close(); session = null; } } // try { // Query query = sessionFactory.getCurrentSession().createQuery("from CargoMessage message where message.messageId=?"); // query.setLong(0, id); // return (CargoMessage)query.uniqueResult(); // } catch(Exception e) { // e.printStackTrace(); // return null; // } } /** * ??? * */ public CargoMessageItem selectById(Long id) { Session session = null; CargoMessageItem message = null; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(); long triggerTime = System.currentTimeMillis(); inter.setShardCriteria(triggerTime); session = sessionFactory.openSession(inter); Query query = session.createQuery( "select new com.consult.app.response.cargo.CargoMessageItem(message.messageId, message.type, message.weight, message.capacity, message.truckType, message.contact, message.telephone, message.start, message.end, message.description, message.updateTime, message.truckLength, message.companyName, message.companyAddress, message.landlines, message.picture, message.avatarAuthenticate, message.userId, message.licenseAuthenticate, message.charges, message.cargoType, message.score, message.orderCount, message.messageCount, message.truckLengthSet) from CargoMessage message where message.messageId=? and message.updateTime>? and message.type>0 and message.type<?"); query.setLong(0, id); query.setLong(1, TimeUtils.getStartOfDay(triggerTime)); query.setInteger(2, Constant.TYPE_ORDERED); message = (CargoMessageItem) query.uniqueResult(); if (message != null) { return message; } if (inter.isFinishSearch(0)) { return null; } triggerTime = inter.getTriggerTime(); return message; } catch (Exception e) { e.printStackTrace(); return null; } finally { if (session != null) { session.close(); session = null; } } } /** * Common Search cargo * */ @Override public List<Object> search(SearchCargoRequest req, City endCity) { if (!req.getTruckLength().equals("-100")) {// new Version: multiple trucklength search return getMultipleTruckLengthSearch(req, endCity); } else {//old version return getAllMessage(req, endCity); } } /** * Common Search cargo, used by 274 Version! * This supports multiple truck length * */ private List<Object> getMultipleTruckLengthSearch(SearchCargoRequest req, City endCity) { Long today = TimeUtils.getStartOfDay(System.currentTimeMillis()); StringBuilder sb = new StringBuilder(String.format(Constant.CARGO_SEARCH_MULTIPLE_TRUCKLENGTH, today)); if (Double.valueOf(req.getTruckLength()) >= 0) { sb.append(" and find_in_set(").append(req.getTruckLength()).append(",message.truck_length_set)"); } if (req.getTruckType() >= 0) { sb.append(" and message.truck_type=").append(req.getTruckType()); } if (req.getWeightRange() > 0) { sb.append(" and message.cargo_weight_range=").append(req.getWeightRange()); } sb.append(" and (message.start=").append(req.getStart()).append(" or message.start_father=") .append(req.getStart()).append(" or message.start_grand=").append(req.getStart()) .append(") and (message.end=").append(endCity.getId()).append(" or message.end_father=") .append(endCity.getId()).append(" or message.end_grand=").append(endCity.getId()).append(" or 0=") .append(endCity.getId()).append(")"); sb.append(" and message.type>=? and message.type<4 order by message.update_time desc"); Long triggerTime = req.getAfter(); if (triggerTime.equals(Long.MAX_VALUE)) { triggerTime = System.currentTimeMillis(); } CargoMessageInterceptor inter = new CargoMessageInterceptor(); int count = req.getCount(); // for(int i = 0; i < Constant.SEARCH_TABLE_COUNT; i++) { Session session = null; try { // Long messageId, int type, // double weight, double capacity, int truckType, // String contact, String telephone, int start, int end, // String description, long updateTime, double truckLength, // String companyName, String companyAddress, String landlines, // String picture, int avatarAuthenticate, Long userId inter.setShardCriteria(System.currentTimeMillis()); session = sessionFactory.openSession(inter); Query query = session.createSQLQuery(sb.toString()).addScalar("messageId", StandardBasicTypes.LONG) .addScalar("type", StandardBasicTypes.INTEGER).addScalar("weight", StandardBasicTypes.DOUBLE) .addScalar("capacity", StandardBasicTypes.DOUBLE) .addScalar("truckType", StandardBasicTypes.INTEGER) .addScalar("contact", StandardBasicTypes.STRING) .addScalar("telephone", StandardBasicTypes.STRING) .addScalar("start", StandardBasicTypes.INTEGER).addScalar("end", StandardBasicTypes.INTEGER) .addScalar("description", StandardBasicTypes.STRING) .addScalar("updateTime", StandardBasicTypes.LONG) .addScalar("truckLength", StandardBasicTypes.DOUBLE) .addScalar("companyName", StandardBasicTypes.STRING) .addScalar("companyAddress", StandardBasicTypes.STRING) .addScalar("landlines", StandardBasicTypes.STRING) .addScalar("picture", StandardBasicTypes.STRING) .addScalar("avatarAuthenticate", StandardBasicTypes.INTEGER) .addScalar("userId", StandardBasicTypes.LONG) .addScalar("licenseAuthenticate", StandardBasicTypes.INTEGER) .addScalar("cargoType", StandardBasicTypes.INTEGER) .addScalar("score", StandardBasicTypes.DOUBLE) .addScalar("orderCount", StandardBasicTypes.INTEGER) .addScalar("charges", StandardBasicTypes.INTEGER) .addScalar("messageCount", StandardBasicTypes.LONG) .addScalar("truckLengthSet", StandardBasicTypes.STRING).setResultTransformer( Transformers.aliasToBean(com.consult.app.response.cargo.CargoMessageItem.class)); query.setLong(0, req.getBefore()); query.setLong(1, triggerTime); query.setInteger(2, Constant.TYPE_NORMAL); query.setFirstResult(0); query.setMaxResults(count); @SuppressWarnings("unchecked") List<Object> tmpList = query.list(); // if(tmpList != null && tmpList.size() > 0) { // list.addAll(tmpList); // if(tmpList.size() >= count) { // tx.commit(); // return list; // } // count -= tmpList.size(); //// min = metaList.get(metaList.size() - 1).getUpdateTime(); // } // if(inter.isFinishSearch(req.getBefore())) { // tx.commit(); // return list; // } // triggerTime = inter.getTriggerTime(); return tmpList; } catch (Exception e) { e.printStackTrace(); return null; } finally { if (session != null) { session.close(); session = null; } } } /** * Common Search cargo, used by First Version! * Deprecated! */ private List<Object> getAllMessage(SearchCargoRequest req, City endCity) { StringBuilder sb = new StringBuilder(Constant.CARGO_SEARCH); if (req.getTruckLengthRange() > 0) { sb.append(" and message.truckLengthRange=").append(req.getTruckLengthRange()); } if (req.getTruckType() >= 0) { sb.append(" and message.truckType=").append(req.getTruckType()); } if (req.getWeightRange() > 0) { sb.append(" and message.cargoWeightRange=").append(req.getWeightRange()); } sb.append(" and (message.start=").append(req.getStart()).append(" or message.startFather=") .append(req.getStart()).append(") and (message.end=").append(endCity.getId()) .append(" or message.endFather=").append(endCity.getId()).append(" or message.endGrand=") .append(endCity.getId()).append(" or 0=").append(endCity.getId()).append(")"); sb.append(" and message.type>=? and message.type<4 order by message.updateTime desc"); List<Object> list = new ArrayList<Object>(); Long triggerTime = req.getAfter(); if (triggerTime.equals(Long.MAX_VALUE)) { triggerTime = System.currentTimeMillis(); } CargoMessageInterceptor inter = new CargoMessageInterceptor(); int count = req.getCount(); // for(int i = 0; i < Constant.SEARCH_TABLE_COUNT; i++) { Session session = null; try { inter.setShardCriteria(triggerTime); session = sessionFactory.openSession(inter); Query query = session.createQuery(sb.toString()); query.setLong(0, req.getBefore()); query.setLong(1, triggerTime); query.setInteger(2, Constant.TYPE_NORMAL); query.setFirstResult(0); query.setMaxResults(count); @SuppressWarnings("unchecked") List<Object> tmpList = query.list(); if (tmpList != null && tmpList.size() > 0) { list.addAll(tmpList); if (tmpList.size() >= count) { return list; } count -= tmpList.size(); } if (inter.isFinishSearch(req.getBefore())) { return list; } triggerTime = inter.getTriggerTime(); } catch (Exception e) { e.printStackTrace(); } finally { if (session != null) { session.close(); session = null; } } // } return list; } @Override public Object getMessageInfo(Long updateTime, Long id) { String hql = "select new com.consult.app.response.cargo.CargoMessageItem" + "(message.messageId, message.type, message.weight, message.capacity," + "message.truckType, message.contact, message.telephone, message.start, message.end," + "message.description, message.updateTime, message.truckLength, message.companyName," + "message.companyAddress, message.landlines, message.picture, message.avatarAuthenticate, " + "message.userId, message.licenseAuthenticate, message.charges, message.cargoType, message.score, " + "message.orderCount, message.messageCount, message.truckLengthSet) " + "from CargoMessage message where message.messageId=?"; Session session = null; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(updateTime); session = sessionFactory.openSession(inter); Query query = session.createQuery(hql); query.setLong(0, id); CargoMessageItem message = (CargoMessageItem) query.uniqueResult(); return message; } catch (Exception e) { e.printStackTrace(); return null; } finally { if (session != null) { session.close(); session = null; } } // Query query = sessionFactory.getCurrentSession().createQuery(hql); // query.setLong(0, id); // query.setLong(1, updateTime); // return (CargoMessageItem)query.uniqueResult(); } public static void writeToFile(FileWriter fileWriter, String fileName, ArrayList<MessageContactItem> list) { try { for (MessageContactItem item : list) { fileWriter.write(item.getTelephone() + "\n"); fileWriter.flush(); } } catch (IOException e) { e.printStackTrace(); } } public static String generateNewFile() { try { String dirName = "/contact/message/" + TimeUtils.getCurYear() + "/" + TimeUtils.getFormatedDate(System.currentTimeMillis()); int fileIndex = 1; String fileName = Constant.CONTACT_PATH + dirName; File file = new File(fileName); if (!file.exists()) { file.mkdirs(); } fileName = fileName + "/contact" + fileIndex + ".txt"; file = new File(fileName); if (!file.exists()) { file.createNewFile(); } return fileName; } catch (IOException e) { e.printStackTrace(); return ""; } } @Override public void doReSend(long start, long end, long now) { String hql = "from CargoMessage message where message.updateTime>? and message.updateTime<? and (message.type=? or message.type=?) order by message.updateTime desc"; if (end <= start) { return; } if (end - start < Integer.MAX_VALUE) { end = start + (end - start) / 4 + RandomNumber.createRandomInt((int) (end - start) * 3 / 4); } Session session = null; List<CargoMessage> list = null; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(end); session = sessionFactory.openSession(inter); Query query = session.createQuery(hql); query.setLong(0, start); query.setLong(1, end); query.setInteger(2, Constant.TYPE_NORMAL); query.setInteger(3, Constant.TYPE_XIWEI); query.setFirstResult(0); query.setMaxResults(20); list = query.list(); } catch (Exception e) { e.printStackTrace(); } finally { if (session != null) { session.close(); session = null; } } if (list != null && !list.isEmpty()) { int i = RandomNumber.createRandomInt(list.size()); CargoMessage message = (CargoMessage) list.get(i); if (message.getType() == Constant.TYPE_ARTIFICIAL) { message.setType(Constant.TYPE_DELETE); update(message); } message.setMessageId(null); message.setUpdateTime(now); message.setModifyTime(now); message.setType(Constant.TYPE_ARTIFICIAL); // message.setUserId(0L); save(message); LogUtil.i(CargoMessage.class.getSimpleName(), "CargoMessage RESEND Success."); updateSubscribeProcess.processAddWithoutPush(message.getStart(), message.getEnd(), message.getEndFather(), message.getEndGrand(), Constant.MESSAGE_TYPE_CARGO, "", now); } else { LogUtil.i(CargoMessage.class.getSimpleName(), "CargoMessage RESEND Nothing."); } } @Override public int getTodayCargoCount(int city) { Session session = null; long startOfDay = TimeUtils.getStartOfDay(System.currentTimeMillis()); try { CargoMessageInterceptor inter = new CargoMessageInterceptor(startOfDay + 1); session = sessionFactory.openSession(inter); String hql = "select count(distinct message.messageId) from CargoMessage message where message.updateTime>? and (message.start=? or message.startFather=?)"; Query query = session.createQuery(hql); query.setLong(0, startOfDay); query.setInteger(1, city); query.setInteger(2, city); int count = ((Long) query.uniqueResult()).intValue(); return count; } catch (Exception e) { e.printStackTrace(); } finally { if (session != null) { session.close(); session = null; } } return 0; } @Override public void updateMessage(IdTypeUpdateTimeRequest req, long userId) { String hql = "update CargoMessage message set message.type=-message.type, message.updateTime=? where message.userId=? and message.messageId=? and message.type<>? and message.type>=?"; Session session = null; Transaction tx = null; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(req.getUpdateTime()); session = sessionFactory.openSession(inter); tx = session.beginTransaction(); tx.setTimeout(Constant.TRANSCTION_TIMEOUT); Query query = session.createQuery(hql); // query.setInteger(0, Constant.TYPE_DELETE); query.setLong(0, System.currentTimeMillis()); query.setLong(1, userId); query.setLong(2, req.getId()); query.setInteger(3, Constant.TYPE_ORDERED); query.setInteger(4, Constant.TYPE_NORMAL); query.executeUpdate(); tx.commit(); } catch (Exception e) { e.printStackTrace(); if (tx != null) { tx.rollback(); } } finally { if (session != null) { session.close(); session = null; } } } @Override public CargoMessage findByIdUpdateTime(Long id, Long updateTime) { String hql = "from CargoMessage message where message.messageId=?"; Session session = null; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(updateTime); Session currentSession = sessionFactory.getCurrentSession(); session = sessionFactory.openSession(currentSession.connection(), inter); Query query = session.createQuery(hql); query.setLong(0, id); CargoMessage message = (CargoMessage) query.uniqueResult(); return message; } catch (Exception e) { e.printStackTrace(); return null; } } @Override public List<Object> searchNearBy(SearchCargoRequest req, City endCity, long userId) { if (!req.getTruckLength().equals("-100")) {// new Version: multiple trucklength search return getNearbyMultipleTruckLengthSearch(req, endCity); } else {//old version return getNearbyMessage(req, endCity); } } /** * Search Nearby cargoMessages by multiple trucklength * @param req * @param endCity * @return */ private List<Object> getNearbyMultipleTruckLengthSearch(SearchCargoRequest req, City endCity) { // List<Object> list = new ArrayList<Object>(); Long today = TimeUtils.getStartOfDay(System.currentTimeMillis()); StringBuilder sb = new StringBuilder( String.format(Constant.NEARBY_CARGO_SEARCH_MULTIPLE_TRUCKLENGTH, today)); if (Double.valueOf(req.getTruckLength()) >= 0) { sb.append(" and find_in_set(").append(req.getTruckLength()).append(",message.truck_length_set)"); } if (req.getTruckType() >= 0) { sb.append(" and message.truck_type=").append(req.getTruckType()); } if (req.getWeightRange() > 0) { sb.append(" and message.cargo_weight_range=").append(req.getWeightRange()); } sb.append(" and city.id=").append(req.getStart()).append( " and (find_in_set(message.start, city.near_by) or find_in_set(message.start_father, city.near_by))") .append(" and (message.end=").append(endCity.getId()).append(" or message.end_father=") .append(endCity.getId()).append(" or message.end_grand=").append(endCity.getId()).append(" or 0=") .append(endCity.getId()).append(")"); sb.append(" and message.type>=? and message.type<4 order by message.update_time desc"); Long triggerTime = req.getAfter(); if (triggerTime.equals(Long.MAX_VALUE)) { triggerTime = System.currentTimeMillis(); } CargoMessageInterceptor inter = new CargoMessageInterceptor(); int count = req.getCount(); // for(int i = 0; i < Constant.SEARCH_TABLE_COUNT; i++) { Session session = null; try { // Long messageId, int type, // double weight, double capacity, int truckType, // String contact, String telephone, int start, int end, // String description, long updateTime, double truckLength, // String companyName, String companyAddress, String landlines, // String picture, int avatarAuthenticate, Long userId inter.setShardCriteria(triggerTime); session = sessionReadFactory.openSession(inter); Query query = session.createSQLQuery(sb.toString()).addScalar("messageId", StandardBasicTypes.LONG) .addScalar("type", StandardBasicTypes.INTEGER).addScalar("weight", StandardBasicTypes.DOUBLE) .addScalar("capacity", StandardBasicTypes.DOUBLE) .addScalar("truckType", StandardBasicTypes.INTEGER) .addScalar("contact", StandardBasicTypes.STRING) .addScalar("telephone", StandardBasicTypes.STRING) .addScalar("start", StandardBasicTypes.INTEGER).addScalar("end", StandardBasicTypes.INTEGER) .addScalar("description", StandardBasicTypes.STRING) .addScalar("updateTime", StandardBasicTypes.LONG) .addScalar("truckLength", StandardBasicTypes.DOUBLE) .addScalar("companyName", StandardBasicTypes.STRING) .addScalar("companyAddress", StandardBasicTypes.STRING) .addScalar("landlines", StandardBasicTypes.STRING) .addScalar("picture", StandardBasicTypes.STRING) .addScalar("avatarAuthenticate", StandardBasicTypes.INTEGER) .addScalar("userId", StandardBasicTypes.LONG) .addScalar("licenseAuthenticate", StandardBasicTypes.INTEGER) .addScalar("cargoType", StandardBasicTypes.INTEGER) .addScalar("score", StandardBasicTypes.DOUBLE) .addScalar("orderCount", StandardBasicTypes.INTEGER) .addScalar("charges", StandardBasicTypes.INTEGER) .addScalar("messageCount", StandardBasicTypes.LONG) .addScalar("truckLengthSet", StandardBasicTypes.STRING).setResultTransformer( Transformers.aliasToBean(com.consult.app.response.cargo.CargoMessageItem.class)); query.setLong(0, req.getBefore()); query.setLong(1, triggerTime); query.setInteger(2, Constant.TYPE_NORMAL); query.setFirstResult(0); query.setMaxResults(count); @SuppressWarnings("unchecked") List<Object> tmpList = query.list(); // if(tmpList != null && tmpList.size() > 0) { // list.addAll(tmpList); // if(tmpList.size() >= count) { // tx.commit(); // return list; // } // count -= tmpList.size(); //// min = metaList.get(metaList.size() - 1).getUpdateTime(); // } // if(inter.isFinishSearch(req.getBefore())) { // tx.commit(); // return list; // } // triggerTime = inter.getTriggerTime(); return tmpList; } catch (Exception e) { e.printStackTrace(); return null; } finally { if (session != null) { session.close(); session = null; } } // } } /** * Search Nearby cargoMessages * Deprecated! * @param req * @param endCity * @return */ private List<Object> getNearbyMessage(SearchCargoRequest req, City endCity) { // List<Object> list = new ArrayList<Object>(); StringBuilder sb = new StringBuilder(Constant.NEARBY_CARGO_SEARCH); if (req.getTruckLengthRange() > 0) { sb.append(" and message.truck_length_range=").append(req.getTruckLengthRange()); } if (req.getTruckType() >= 0) { sb.append(" and message.truck_type=").append(req.getTruckType()); } if (req.getWeightRange() > 0) { sb.append(" and message.cargo_weight_range=").append(req.getWeightRange()); } sb.append(" and city.id=").append(req.getStart()).append( " and (find_in_set(message.start, city.near_by) or find_in_set(message.start_father, city.near_by))") .append(" and (message.end=").append(endCity.getId()).append(" or message.end_father=") .append(endCity.getId()).append(" or message.end_grand=").append(endCity.getId()).append(" or 0=") .append(endCity.getId()).append(")"); sb.append(" and message.type>=? and message.type<4 order by message.update_time desc"); Long triggerTime = req.getAfter(); if (triggerTime.equals(Long.MAX_VALUE)) { triggerTime = System.currentTimeMillis(); } CargoMessageInterceptor inter = new CargoMessageInterceptor(); int count = req.getCount(); // for(int i = 0; i < Constant.SEARCH_TABLE_COUNT; i++) { Session session = null; try { // Long messageId, int type, // double weight, double capacity, int truckType, // String contact, String telephone, int start, int end, // String description, long updateTime, double truckLength, // String companyName, String companyAddress, String landlines, // String picture, int avatarAuthenticate, Long userId inter.setShardCriteria(triggerTime); session = sessionFactory.openSession(inter); Query query = session.createSQLQuery(sb.toString()).addScalar("messageId", StandardBasicTypes.LONG) .addScalar("type", StandardBasicTypes.INTEGER).addScalar("weight", StandardBasicTypes.DOUBLE) .addScalar("capacity", StandardBasicTypes.DOUBLE) .addScalar("truckType", StandardBasicTypes.INTEGER) .addScalar("contact", StandardBasicTypes.STRING) .addScalar("telephone", StandardBasicTypes.STRING) .addScalar("start", StandardBasicTypes.INTEGER).addScalar("end", StandardBasicTypes.INTEGER) .addScalar("description", StandardBasicTypes.STRING) .addScalar("updateTime", StandardBasicTypes.LONG) .addScalar("truckLength", StandardBasicTypes.DOUBLE) .addScalar("companyName", StandardBasicTypes.STRING) .addScalar("companyAddress", StandardBasicTypes.STRING) .addScalar("landlines", StandardBasicTypes.STRING) .addScalar("picture", StandardBasicTypes.STRING) .addScalar("avatarAuthenticate", StandardBasicTypes.INTEGER) .addScalar("userId", StandardBasicTypes.LONG) .addScalar("licenseAuthenticate", StandardBasicTypes.INTEGER) .addScalar("cargoType", StandardBasicTypes.INTEGER) .addScalar("score", StandardBasicTypes.DOUBLE) .addScalar("orderCount", StandardBasicTypes.INTEGER) .addScalar("charges", StandardBasicTypes.INTEGER) .addScalar("messageCount", StandardBasicTypes.LONG) .addScalar("truckLengthSet", StandardBasicTypes.STRING).setResultTransformer( Transformers.aliasToBean(com.consult.app.response.cargo.CargoMessageItem.class)); query.setLong(0, req.getBefore()); query.setLong(1, triggerTime); query.setInteger(2, Constant.TYPE_NORMAL); query.setFirstResult(0); query.setMaxResults(count); @SuppressWarnings("unchecked") List<Object> tmpList = query.list(); // if(tmpList != null && tmpList.size() > 0) { // list.addAll(tmpList); // if(tmpList.size() >= count) { // tx.commit(); // return list; // } // count -= tmpList.size(); //// min = metaList.get(metaList.size() - 1).getUpdateTime(); // } // if(inter.isFinishSearch(req.getBefore())) { // tx.commit(); // return list; // } // triggerTime = inter.getTriggerTime(); return tmpList; } catch (Exception e) { e.printStackTrace(); return null; } finally { if (session != null) { session.close(); session = null; } } // } } @Override public int getMessageCount(long value) { Session session = null; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(value); session = sessionFactory.openSession(inter); int count = 0; String hql = "select count(*) from CargoMessage msg where msg.updateTime<? and msg.updateTime>?"; Query query = session.createQuery(hql); query.setLong(0, TimeUtils.getEndOfDay(value)); query.setLong(1, TimeUtils.getStartOfDay(value)); count = ((Long) query.uniqueResult()).intValue(); return count; } catch (Exception e) { e.printStackTrace(); return 0; } finally { if (session != null) { session.close(); session = null; } } } @Override public void setOrdered(Long messageId, Long updateTime) { String hql = "update CargoMessage message set message.type=?, message.updateTime=? where message.messageId=?"; Session session = null; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(updateTime); Session currentSession = sessionFactory.getCurrentSession(); session = sessionFactory.openSession(currentSession.connection(), inter); Query query = session.createQuery(hql); query.setInteger(0, Constant.TYPE_ORDERED); query.setLong(1, System.currentTimeMillis()); query.setLong(2, messageId); query.executeUpdate(); // tx.commit(); } catch (Exception e) { e.printStackTrace(); } } /** * ????? * @param user_id * @param start * @param end * @return bool */ public boolean judgeTodayCargo(Long user_id, int start, int end) { Session session = null; String sql = "select message.message_id from cargo_messages message where (message.type=1 or message.type=-1) and message.user_id=? and message.start=? and message.end =? and message.update_time>?"; long startOfDay = TimeUtils.getStartOfDay(System.currentTimeMillis()); try { CargoMessageInterceptor inter = new CargoMessageInterceptor(startOfDay + 1); session = sessionFactory.openSession(inter); Query query = session.createSQLQuery(sql).addScalar("message_id", StandardBasicTypes.LONG); query.setLong(0, user_id); query.setInteger(1, start); query.setInteger(2, end); query.setLong(3, startOfDay); @SuppressWarnings("rawtypes") List list = query.list(); if (list != null && list.size() > 1) { return true; } else { return false; } } catch (Exception e) { e.printStackTrace(); return false; } finally { if (session != null) { session.close(); session = null; } } } @Override public List<Object> getTodaySuspiciousCargoer() { Session session = null; String sql = "select u.install_place as city, u.telephone as telephone, p.user_name as userName, count(m.message_id) as cnt from cargo_message_00 m, users u, profiles p where m.user_id=u.user_id and m.user_id=p.user_id and (m.type=1 or m.type=-1 or m.type=4) and m.update_time>? group by m.user_id having cnt>=20 order by cnt desc;"; long startOfDay = TimeUtils.getStartOfDay(System.currentTimeMillis()); try { CargoMessageInterceptor inter = new CargoMessageInterceptor(startOfDay + 1); session = sessionFactory.openSession(inter); Query query = session.createSQLQuery(sql).addScalar("city", StandardBasicTypes.INTEGER) .addScalar("telephone", StandardBasicTypes.LONG) .addScalar("userName", StandardBasicTypes.STRING).addScalar("cnt", StandardBasicTypes.INTEGER) .setResultTransformer(Transformers.aliasToBean(com.consult.app.response.cargo.CargoItem.class)); query.setLong(0, startOfDay); List list = query.list(); return list; } catch (Exception e) { e.printStackTrace(); return null; } finally { if (session != null) { session.close(); session = null; } } } @Override public List<SalesItem> getCargoStat(String sqlCity) { // Session session = null; // Transaction tx = null; //// Long userId, String city, int cityId, String name, int cargoCount, int asignDriverCount // String sql = "select u.user_id as userId, c.short_name as city, c.id as cityId, p.user_name as name, u.telephone as telephone, count(m.message_id) as cargoCount, 0 as asignDriverCount from cargo_message_00 m, users u, profiles p, city c where m.user_id=u.user_id and m.user_id=p.user_id and c.id=u.install_place and (m.type=1 or m.type=-1 or m.type=4) and m.update_time>? " + sqlCity +" group by m.user_id order by c.id desc;"; // long startOfDay = TimeUtils.getStartOfDay(System.currentTimeMillis()); // List<SalesItem> list = new ArrayList<SalesItem>(); // try { // CargoMessageInterceptor inter = new CargoMessageInterceptor( // startOfDay + 1); // session = sessionFactory.openSession(inter); // tx = session.beginTransaction(); // tx.setTimeout(Constant.TRANSCTION_TIMEOUT); // Query query = session.createSQLQuery(sql) // .addScalar("userId", StandardBasicTypes.LONG) // .addScalar("city", StandardBasicTypes.STRING) // .addScalar("cityId", StandardBasicTypes.INTEGER) // .addScalar("name", StandardBasicTypes.STRING) // .addScalar("telephone", StandardBasicTypes.STRING) // .addScalar("cargoCount", StandardBasicTypes.INTEGER) // .addScalar("asignDriverCount", StandardBasicTypes.INTEGER) // .setResultTransformer(Transformers.aliasToBean(com.consult.app.response.cargo.SalesItem.class)); // query.setLong(0, startOfDay); // List<SalesItem> tmpList = query.list(); // if (tmpList != null && tmpList.size() > 0) { // list.addAll(tmpList); // } // tx.commit(); // // } catch (Exception e) { // e.printStackTrace(); // return null; // } finally { // if(session != null) { // session.close(); // session = null; // } // } // List<SalesItem> retList = new ArrayList<SalesItem>(); // try { // for (SalesItem item : list) { // String hql = "select count(o.id) as asignDriverCount, group_concat(o.driver_telephone) as driverTelephones from orders o, users u where u.user_id=o.cargo_user_id and o.cargo_user_id=? and o.create_time>?;"; // Query query = sessionFactory.getCurrentSession().createSQLQuery(hql) // .addScalar("asignDriverCount", StandardBasicTypes.INTEGER) // .addScalar("driverTelephones", StandardBasicTypes.STRING) // .setResultTransformer(Transformers.aliasToBean(com.consult.app.response.cargo.SalesTmpItem.class)); // query.setLong(0, item.getUserId()); // query.setLong(1, startOfDay); // SalesTmpItem ret = (SalesTmpItem)query.uniqueResult(); // item.setAsignDriverCount(ret.getAsignDriverCount()); // item.setDriverTelephones(ret.getDriverTelephones() == null ? "" : ret.getDriverTelephones()); // retList.add(item); // } // } catch (Exception e) { // e.printStackTrace(); // } // return retList; return null; } @Override public int newAsignCargo(CargoMessage message, NewAsignedCargoRequest req) { return 0; } // SQL_OPTIMIZE @Override public int searchNearByCount(SearchCargoRequest req, City startCity, City endCity, long userId) { StringBuilder sb = new StringBuilder(Constant.NEARBY_COUNT_CARGO_SEARCH_MULTIPLE_TRUCKLENGTH); if (Double.valueOf(req.getTruckLength()) >= 0) { sb.append(" and find_in_set(").append(req.getTruckLength()).append(",message.truck_length_set)"); } if (req.getTruckType() >= 0) { sb.append(" and message.truck_type=").append(req.getTruckType()); } if (req.getWeightRange() > 0) { sb.append(" and message.cargo_weight_range=").append(req.getWeightRange()); } if (startCity.getNearBy() != null && !startCity.getNearBy().equals("")) { sb.append(" and (").append(StringUtil.convertMysqlSetByField("message.start", startCity.getNearBy())) .append(" or ") .append(StringUtil.convertMysqlSetByField("message.start_father", startCity.getNearBy())) .append(")"); } else { return 0; } // sb.append(" and (find_in_set(message.start, city.near_by) or find_in_set(message.start_father, city.near_by))") sb.append(" and (message.end=").append(endCity.getId()).append(" or message.end_father=") .append(endCity.getId()).append(" or message.end_grand=").append(endCity.getId()).append(" or 0=") .append(endCity.getId()).append(")"); sb.append(" and message.type>=? and message.type<4 limit 1"); Long triggerTime = req.getAfter(); if (triggerTime.equals(Long.MAX_VALUE)) { triggerTime = System.currentTimeMillis(); } CargoMessageInterceptor inter = new CargoMessageInterceptor(); // for(int i = 0; i < Constant.SEARCH_TABLE_COUNT; i++) { Session session = null; try { inter.setShardCriteria(triggerTime); session = sessionFactory.openSession(inter); Query query = session.createSQLQuery(sb.toString()); // query.setLong(0, req.getBefore()); long startTime = req.getBefore() > Cookie.getSearchStartTime() ? req.getBefore() : Cookie.getSearchStartTime(); query.setLong(0, startTime); query.setLong(1, triggerTime); query.setInteger(2, Constant.TYPE_NORMAL); // query.setFirstResult(0); // query.setMaxResults(count); int ret = ((BigInteger) query.uniqueResult()).intValue(); return ret; } catch (Exception e) { e.printStackTrace(); return 0; } finally { if (session != null) { session.close(); session = null; } } // } int count = ((BigInteger)query.uniqueResult()).intValue(); } @Override public int getMessageDayCount(int type1, int type2, Long userId) { CargoMessageInterceptor inter = new CargoMessageInterceptor(); long triggerTime = System.currentTimeMillis(); int ret = 0; String sql = "select count(distinct FROM_UNIXTIME(update_time/1000, '%Y%m%d')) from cargo_messages where (type=? or type=? or type=4) and user_id=?;"; // for(int i = 0; i < Constant.SEARCH_TABLE_COUNT; i++) { Session session = null; try { inter.setShardCriteria(triggerTime); session = sessionFactory.openSession(inter); Query query = session.createSQLQuery(sql); query.setInteger(0, type1); query.setInteger(1, type2); query.setLong(2, userId); ret += ((BigInteger) query.uniqueResult()).intValue(); if (inter.isFinishSearch(0)) { return ret; } triggerTime = inter.getTriggerTime(); } catch (Exception e) { e.printStackTrace(); return 0; } finally { if (session != null) { session.close(); session = null; } } // } return 0; } @Override public CargoMessage getFirstLineMessage(int start, int end, Long userId, Long updateTime) { String hql = "from CargoMessage message where message.updateTime>=? and message.userId=? and message.start=? and message.end=?"; Session session = null; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(updateTime); session = sessionFactory.openSession(inter); Query query = session.createQuery(hql); query.setLong(0, TimeUtils.getStartOfDay(updateTime)); query.setLong(1, userId); query.setInteger(2, start); query.setInteger(3, end); query.setFirstResult(0); query.setMaxResults(1); CargoMessage message = (CargoMessage) query.uniqueResult(); return message; } catch (Exception e) { e.printStackTrace(); return null; } finally { if (session != null) { session.close(); session = null; } } } @Override public int refreshCargoMessages() { LogUtil.i(this.getClass().getSimpleName(), "refreshCargoMessages"); Session session = null; Transaction tx = null; int size = 0; long twenty_m_ago = System.currentTimeMillis() - 1200000; long now = System.currentTimeMillis(); String hql = "update CargoMessage message set message.updateTime=?-(1200000-MOD(message.updateTime, 1200000)) where message.updateTime<=? and message.updateTime>? and message.type=1"; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(System.currentTimeMillis()); session = sessionFactory.openSession(inter); tx = session.beginTransaction(); tx.setTimeout(Constant.TRANSCTION_TIMEOUT); Query query = session.createQuery(hql); query.setLong(0, now); query.setLong(1, twenty_m_ago); query.setLong(2, TimeUtils.getStartOfDay(now)); size = query.executeUpdate(); tx.commit(); } catch (Exception e) { e.printStackTrace(); if (tx != null) { tx.rollback(); } } finally { if (session != null) { session.close(); session = null; } } return size; } @Override public int getUserMessageCountByTime(long startOfDay, long endOfDay, Long userId) { Session session = null; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(startOfDay + 1); session = sessionFactory.openSession(inter); int count = 0; String hql = "select count(*) from CargoMessage msg where msg.updateTime<? and msg.updateTime>? and (msg.type=1 or msg.type=-1 or msg.type=4) and msg.userId=?"; Query query = session.createQuery(hql); query.setLong(0, endOfDay); query.setLong(1, startOfDay); query.setLong(2, userId); count = ((Long) query.uniqueResult()).intValue(); return count; } catch (Exception e) { e.printStackTrace(); return 0; } finally { if (session != null) { session.close(); session = null; } } } @Override public List<Object> searchCargoByClerk(TelephoneRequest req) { Session session = null; long startOfDay = TimeUtils.getStartOfDay(System.currentTimeMillis()) + 1; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(startOfDay + 1); session = sessionFactory.openSession(inter); String hql = "select new com.consult.app.response.admin.ClerkMessageItem(message) " + "from CargoMessage message " + "where message.telephone=? and message.updateTime>? and (message.type=1 or message.type=-1 or message.type=2 or message.type=-2)"; Query query = session.createQuery(hql); query.setString(0, req.getTelephone()); query.setLong(1, startOfDay); query.setFirstResult(0); query.setMaxResults(100); List<Object> list = query.list(); return list; } catch (Exception e) { e.printStackTrace(); } finally { if (session != null) { session.close(); session = null; } } return null; } @Override public List<Object> searchAllCargoByAdmin(TelephoneBeforeAfterCountRequest req) { Session session = null; List<Object> list = null; // long startOfDay = TimeUtils.getStartOfDay(System.currentTimeMillis()) + 1; try { CargoMessageInterceptor interBefore = new CargoMessageInterceptor(req.getBefore()); int beforeIndex = interBefore.getTableIndex(); CargoMessageInterceptor interAfter = new CargoMessageInterceptor(req.getAfter()); int afterIndex = interAfter.getTableIndex(); if (beforeIndex != afterIndex) { LogUtil.i("searchAllCargoByAdmin", " beforeIndex != afterIndex "); return list; } session = sessionFactory.openSession(interBefore); String hql = "select new com.consult.app.response.admin.ClerkMessageItem(message) " + "from CargoMessage message " + "where message.telephone=? and message.createTime>? and message.createTime<? order by " + " message.createTime desc"; Query query = session.createQuery(hql); query.setString(0, String.valueOf(req.getTelephone())); query.setLong(1, req.getBefore()); query.setLong(2, req.getAfter()); query.setMaxResults(req.getCount()); list = query.list(); return list; } catch (Exception e) { e.printStackTrace(); } finally { if (session != null) { session.close(); session = null; } } return list; } @Override public int deleteClerkCargo(IdRequest req) { Session session = null; Transaction tx = null; long now = System.currentTimeMillis(); String hql = "update CargoMessage message set message.type=-message.type where message.updateTime>? and message.type>0 and message.type<4 and message.messageId=?"; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(System.currentTimeMillis()); session = sessionFactory.openSession(inter); tx = session.beginTransaction(); tx.setTimeout(Constant.TRANSCTION_TIMEOUT); Query query = session.createQuery(hql); query.setLong(0, TimeUtils.getStartOfDay(now)); query.setLong(1, req.getId()); query.executeUpdate(); tx.commit(); } catch (Exception e) { e.printStackTrace(); if (tx != null) { tx.rollback(); } return Cookie.RESPONSE_SERVER_QUERY_ERROR; } finally { if (session != null) { session.close(); session = null; } } return Cookie.RESPONSE_SUCCESS; } @Override public List<Object> getCargosByTel(GetCargosRequest req) { Session session = null; long now = System.currentTimeMillis(); String hql = "select new com.consult.app.response.admin.GetCargosItem(message.messageId,message.userId,message.start,message.end,message.telephone," + "message.contact,message.weight,message.capacity,message.truckLength,message.truckType,message.type,message.description,message.modifyTime," + "message.updateTime,message.companyName,message.companyAddress,message.landlines,message.charges,message.cargoType,message.truckLengthSet) from CargoMessage message " + "where message.telephone=? and message.modifyTime > ? and message.modifyTime < ? and (message.type=1 or message.type=-1 or message.type=4)"; if (req.getCity() > 0) { hql += " and message.start = ?"; } hql += " order by message.modifyTime desc"; try { CargoMessageInterceptor inter = new CargoMessageInterceptor(System.currentTimeMillis()); session = sessionReadFactory.openSession(inter); Query query = session.createQuery(hql); query.setLong(0, req.getTelephone()); query.setLong(1, TimeUtils.getStartOfDay(req.getUpdateTime())); query.setLong(2, TimeUtils.getEndOfDay(req.getUpdateTime())); if (req.getCity() > 0) { query.setInteger(3, req.getCity()); } List<Object> list = query.list(); return list; } catch (Exception e) { e.printStackTrace(); } finally { if (session != null) { session.close(); session = null; } } return null; } }