Java tutorial
/* * Copyright (c) 2013 IANA. All Rights Reserved. THE AUTHOR MAKES NO * REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY OF THE SOFTWARE, EITHER * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. THE * AUTHOR SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE AS A RESULT * OF USING, MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES. */ package com.iana.dver.dao.impl; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import org.apache.log4j.Logger; import org.hibernate.HibernateException; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.dao.DataAccessException; import org.springframework.orm.hibernate3.HibernateCallback; import org.springframework.orm.hibernate3.SessionFactoryUtils; import org.springframework.orm.hibernate3.support.HibernateDaoSupport; import org.springframework.stereotype.Repository; import org.springframework.util.CollectionUtils; import com.iana.dver.dao.DverUsersDao; import com.iana.dver.domain.DverConfig; import com.iana.dver.domain.DverNotif; import com.iana.dver.domain.DverUserType; import com.iana.dver.domain.DverUsers; import com.iana.dver.domain.UserLogin; /** * * @author tgbaxi * @date 03-Sep-2013 * */ @Repository(value = "dverUsersDao") @SuppressWarnings({ "rawtypes", "unchecked" }) public class DverUsersDaoImpl extends HibernateDaoSupport implements DverUsersDao { private static final Logger logger = Logger.getLogger(DverUsersDaoImpl.class); private final static String FIND_BY_SCAC_CODE_AND_USER_TYPE = "FROM DverUsers DU WHERE DU.scac = :scacCode AND DU.dverUserType.userRole = :userType"; private final static String FIND_BY_USDOT_CODE_AND_USER_TYPE = "FROM DverUsers DU WHERE TRIM(LEADING '0' FROM DU.usdot) = :usdot AND DU.dverUserType.userRole = :userType"; private final static String FIND_BY_EMAIL_ID = "FROM DverUsers DU WHERE DU.email = :email"; private final static String FIND_BY_USER_NAME = "FROM UserLogin UL WHERE UL.userName = :userName"; private final static String FIND_DVER_CONFIG_BY_EMAIL_ID = "FROM DverConfig DC WHERE DC.email = :email"; private final static String FIND_DVER_CONFIG_BY_USER_ID = "FROM DverConfig DC WHERE DC.dverUsers.dverUserId = :dverUserId"; private final static String FIND_BY_USER_ID = "FROM UserLogin UL WHERE dverLoginId= :dverLoginId"; private final static String FIND_DVER_CONFIG_BY_DOT = "FROM DverConfig DC WHERE TRIM(LEADING '0' FROM DC.dverUsers.usdot ) = :dot"; private final static String GET_OLD_PASSWORD_BY_USERID = "SELECT UL.password FROM UserLogin UL WHERE UL.dverUsers.dverUserId = :userId"; @Autowired public DverUsersDaoImpl(@Qualifier(value = "sessionFactory") SessionFactory sessionFactory) { super.setSessionFactory(sessionFactory); } @Override public void create(final DverUsers dverUser) throws DataAccessException { getHibernateTemplate().saveOrUpdate(dverUser); } @Override public void update(final DverUsers dverUser) throws DataAccessException { getHibernateTemplate().saveOrUpdate(dverUser); } @Override public void delete(final DverUsers dverUser) throws DataAccessException { getHibernateTemplate().delete(dverUser); } @Override public DverUsers findById(final Integer dverUserId) throws DataAccessException { return getHibernateTemplate().get(DverUsers.class, dverUserId); } @Override public List<DverUsers> findAllUsers() throws DataAccessException { logger.info("find All Users "); return getHibernateTemplate().find(" from " + DverUsers.class.getName()); } @Override public DverUserType getDverUserTypeById(Integer userTypeId) throws DataAccessException { logger.info("get DverUserType By Id "); DverUserType userType = getHibernateTemplate().get(DverUserType.class, userTypeId); return userType; } @Override public List<DverUsers> findByScacCodeAndUserType(final String scacCode, final String userType) throws DataAccessException { logger.info("find By Scac Code AND User Type"); List result = getHibernateTemplate().findByNamedParam(FIND_BY_SCAC_CODE_AND_USER_TYPE, new String[] { "scacCode", "userType" }, new String[] { scacCode, userType }); return result != null ? result : new ArrayList<DverUsers>(); } @Override public List<DverUsers> findByDotAndUserType(final String usdot, final String userType) throws DataAccessException { logger.info("find By USDOT AND User Type"); List result = getHibernateTemplate().findByNamedParam(FIND_BY_USDOT_CODE_AND_USER_TYPE, new String[] { "usdot", "userType" }, new String[] { usdot, userType }); return result != null ? result : new ArrayList<DverUsers>(); } @Override public List<DverUsers> findByEmailId(final String email) throws DataAccessException { logger.info("find By EmailId "); List result = getHibernateTemplate().findByNamedParam(FIND_BY_EMAIL_ID, "email", email); return result != null ? result : new ArrayList<DverUsers>(); } @Override public List<UserLogin> findByUserName(final String userName) throws DataAccessException { logger.info("find By UserName "); List result = getHibernateTemplate().findByNamedParam(FIND_BY_USER_NAME, "userName", userName); return result != null ? result : new ArrayList<DverUsers>(); } @Override public List<DverConfig> findDverConfigByEmailId(final String email) throws DataAccessException { logger.info("find DverConfig By EmailId"); List result = getHibernateTemplate().findByNamedParam(FIND_DVER_CONFIG_BY_EMAIL_ID, "email", email); return result != null ? result : new ArrayList<DverConfig>(); } @Override public List<DverUsers> findAllUsersByType(final Integer userTypeId, final Integer start, final Integer end, final String sortColumnIndex, final String sSortDirection) throws DataAccessException { List result = getHibernateTemplate().executeFind(new HibernateCallback<List<DverUsers>>() { @Override public List<DverUsers> doInHibernate(Session session) throws HibernateException, SQLException { logger.info("find AllUsers By Type "); StringBuilder query = new StringBuilder( "FROM DverUsers DU WHERE DU.dverUserType.dverUserTypeId in (2,3) "); if (sortColumnIndex.equalsIgnoreCase("0")) { query.append("order by DU.companyName "); } else if (sortColumnIndex.equalsIgnoreCase("2")) { query.append("order by DU.fname "); } else if (sortColumnIndex.equalsIgnoreCase("3")) { query.append("order by DU.lname "); } query.append(sSortDirection); logger.info("All user listing query is :: " + query.toString()); Query q = session.createQuery(query.toString()); q.setFirstResult(start); q.setMaxResults(end); return q.list(); } }); return result != null ? result : new ArrayList<DverUsers>(); } @Override public List<DverUsers> findAllUsersForExcel() throws DataAccessException { List result = getHibernateTemplate().executeFind(new HibernateCallback<List<DverUsers>>() { @Override public List<DverUsers> doInHibernate(Session session) throws HibernateException, SQLException { logger.info("find AllUsers By Type "); StringBuilder query = new StringBuilder( "FROM DverUsers AS DU INNER JOIN FETCH DU.dverConfigs WHERE DU.dverUserType.dverUserTypeId in (2,3) "); query.append("order by DU.dverUserType.dverUserTypeId "); logger.info("All user listing query for Excel is :: " + query.toString()); Query q = session.createQuery(query.toString()); return q.list(); } }); return result != null ? result : new ArrayList<DverUsers>(); } @Override public void updateDverNotif(DverNotif dverNotif) throws DataAccessException { getHibernateTemplate().saveOrUpdate(dverNotif); } @Override public DverNotif findDverNotifById(Integer dverNotifId) throws DataAccessException { return getHibernateTemplate().get(DverNotif.class, dverNotifId); } @Override public int countAllUsersByType(Integer userTypeId) throws DataAccessException { logger.info("Count AllUsers By Type "); List result = null; result = getHibernateTemplate().find( "SELECT COUNT(DU.dverUserId) FROM DverUsers DU WHERE DU.dverUserType.dverUserTypeId in (2,3)"); return CollectionUtils.isEmpty(result) ? 0 : Integer.valueOf(result.get(0).toString()); } @Override public DverConfig findDverConfigByUserId(Integer userId) throws DataAccessException { logger.info("find DverConfig By UserId "); List result = getHibernateTemplate().findByNamedParam(FIND_DVER_CONFIG_BY_USER_ID, "dverUserId", userId); return CollectionUtils.isEmpty(result) ? null : (DverConfig) result.get(0); } @Override public void updateDverConfig(DverConfig dverConfig) throws DataAccessException { logger.info("update DverConfig "); getHibernateTemplate().saveOrUpdate(dverConfig); } @Override public void resetPassword(UserLogin userLogin) throws DataAccessException { logger.info("Reset Password "); getHibernateTemplate().saveOrUpdate(userLogin); } @Override public UserLogin findByLoginId(Integer loginId) throws DataAccessException { logger.info("find By LoginId "); List result = getHibernateTemplate().findByNamedParam(FIND_BY_USER_ID, "dverLoginId", loginId); return CollectionUtils.isEmpty(result) ? null : (UserLogin) result.get(0); } @Override public DverConfig findDverConfigByDOT(String dot) throws DataAccessException { logger.info("find DverConfig By DOT "); List result = getHibernateTemplate().findByNamedParam(FIND_DVER_CONFIG_BY_DOT, "dot", dot); return CollectionUtils.isEmpty(result) ? null : (DverConfig) result.get(0); } @Override public String getOldPwd(Integer userId) throws DataAccessException { logger.info("get Old Password"); List result = getHibernateTemplate().findByNamedParam(GET_OLD_PASSWORD_BY_USERID, "userId", userId); return CollectionUtils.isEmpty(result) ? "" : (String) result.get(0); } @Override public List<DverUsers> filterUserByScacOrDot(Integer userTypeId, Map<String, String> filtermap, Integer start, Integer end) throws DataAccessException { logger.info("filter User By Scac Or Dot....."); Session session = SessionFactoryUtils.getSession(this.getSessionFactory(), true); String filterQuery = generateQuery(userTypeId, filtermap, Boolean.FALSE); logger.info("user filter query is :: " + filterQuery); Query filters = session.createQuery(filterQuery); filters.setFirstResult(start); filters.setMaxResults(end); List result = filters.list(); session.close(); return result != null ? result : new ArrayList<DverUsers>(); } private String generateQuery(Integer userTypeId, Map<String, String> filtermap, Boolean isCntQuery) { StringBuilder filterQuery = new StringBuilder(); if (isCntQuery) { filterQuery.append("SELECT COUNT(DU.dverUserId) "); } if (userTypeId == 0) { filterQuery.append("FROM DverUsers DU WHERE DU.dverUserType.dverUserTypeId IN (2, 3)"); } else { filterQuery.append("FROM DverUsers DU WHERE DU.dverUserType.dverUserTypeId = " + userTypeId); } if (filtermap.containsKey("scacFilter")) { filterQuery.append(" AND DU.scac like '" + filtermap.get("scacFilter") + "'"); } if (filtermap.containsKey("dotFilter")) { filterQuery.append(" AND TRIM(LEADING '0' FROM DU.usdot) like '" + filtermap.get("dotFilter") + "'"); } if (filtermap.containsKey("companyNameFilter")) { filterQuery.append(" AND DU.companyName like '" + filtermap.get("companyNameFilter") + "'"); } if (filtermap.containsKey("firstNameFilter")) { filterQuery.append(" AND DU.fname like '" + filtermap.get("firstNameFilter") + "'"); } if (filtermap.containsKey("lastNameFilter")) { filterQuery.append(" AND DU.lname like '" + filtermap.get("lastNameFilter") + "'"); } if (filtermap.containsKey("emailFilter")) { filterQuery.append(" AND DU.email like '" + filtermap.get("emailFilter") + "'"); } if (filtermap.containsKey("sortColumnIndex")) { String sortColumnIndex = filtermap.get("sortColumnIndex"); if (sortColumnIndex.equalsIgnoreCase("0")) { filterQuery.append(" order by DU.companyName "); } else if (sortColumnIndex.equalsIgnoreCase("2")) { filterQuery.append(" order by DU.fname "); } else if (sortColumnIndex.equalsIgnoreCase("3")) { filterQuery.append(" order by DU.lname "); } } filterQuery.append(filtermap.containsKey("sSortDirection") ? filtermap.get("sSortDirection") : " asc"); return filterQuery.toString(); } @Override public int countFilteredUsersByScacOrDot(Integer userTypeId, Map<String, String> filtermap) throws DataAccessException { logger.info("count Filtered Users By Scac Or Dot"); Session session = SessionFactoryUtils.getSession(this.getSessionFactory(), true); String filterQuery = generateQuery(userTypeId, filtermap, Boolean.TRUE); logger.info("user filter query is :: " + filterQuery); Query filters = session.createQuery(filterQuery); List result = filters.list(); session.close(); return CollectionUtils.isEmpty(result) ? 0 : Integer.valueOf(result.get(0).toString()); } }