Java tutorial
/** * */ package com.sinet.gage.dao; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashSet; import java.util.List; import java.util.Set; import javax.sql.DataSource; import org.joda.time.format.DateTimeFormatter; import org.joda.time.format.ISODateTimeFormat; import org.slf4j.LoggerFactory; import org.slf4j.Logger; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.BatchPreparedStatementSetter; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.stereotype.Repository; import com.sinet.gage.entities.Domain; /** * @author Rajender Tella * */ @Repository public class DomainsRepository { protected final Logger log = LoggerFactory.getLogger(getClass()); DateTimeFormatter dateTimeFormatter = ISODateTimeFormat.dateTimeParser(); private final String ETL_WHO_UPD = "etl-delta-processor"; private final String ETL_WHO_INS = "etl-full-domains-importer"; private final String DOMAIN_SELECT_SQL = "SELECT domain_id from reports.domain_excludes order by domain_id"; private final String DOMAINS_INSERT_SQL = "INSERT INTO reports.domains( " + " domain_id, guid, domain_name, login_prefix, domain_flag, domain_type, " + " parent_domain_id, parent_domain_name, state_domain_id, state_name, " + " license_type, pool_type, number_of_license, pilot, pilot_start_date, " + " pilot_end_date, full_subscription, subscription_start_date, subscription_end_date, " + " creator_user_id, creation_date, modifier_user_id, modefied_date, etl_date_ins, etl_who_ins) " + " VALUES (?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?," + " current_timestamp, '" + ETL_WHO_INS + "')"; private final String DOMAINS_UPDATE_SQL = "UPDATE reports.domains SET etl_date_upd = current_timestamp, etl_who_upd = '" + ETL_WHO_UPD + "', is_deleted = TRUE WHERE domain_id = ?"; private final String DOMAINS_FULL_UPDATE_SQL = "UPDATE reports.domains SET " + " guid = ?, domain_name = ?, login_prefix = ?, domain_flag = ?, domain_type = ?, " + " parent_domain_id = ?, parent_domain_name = ?, state_domain_id = ?, state_name = ?, " + " license_type = ?, pool_type = ?, number_of_license = ?, pilot = ?, pilot_start_date = ?, " + " pilot_end_date = ?, full_subscription = ?, subscription_start_date = ?, subscription_end_date = ?, " + " modifier_user_id = ?, modefied_date = ?, etl_date_upd = current_timestamp, etl_who_upd = '" + ETL_WHO_UPD + "'" + " WHERE domain_id = ?"; JdbcTemplate jdbcTemplate; /** * @param dataSource */ @Autowired public void setDataSource(DataSource dataSource) { this.jdbcTemplate = new JdbcTemplate(dataSource); } /** * @return all excluded domains from etl database */ public Set<Integer> getAllExcludedDomainIds() { return new HashSet<Integer>(jdbcTemplate.query(DOMAIN_SELECT_SQL, domainIdMapper)); } private RowMapper<Integer> domainIdMapper = new RowMapper<Integer>() { public Integer mapRow(ResultSet rs, int rowNum) throws SQLException { return new Integer(rs.getInt("domain_id")); } }; /** * * @param domainId * @param domains * @return */ public int updateDomains(Integer domainId) { try { return this.jdbcTemplate.update(DOMAINS_UPDATE_SQL, domainId); } catch (Exception e) { log.error("Error in deleting Domains", e); return 0; } } /** * * @param domains */ public void insertDomains(List<Domain> domains) { try { jdbcTemplate.batchUpdate(DOMAINS_INSERT_SQL, new BatchPreparedStatementSetter() { public int getBatchSize() { if (domains == null) return 0; return domains.size(); } @Override public void setValues(PreparedStatement ps, int i) throws SQLException { Domain domain = domains.get(i); ps.setLong(1, domain.getDomainId()); ps.setObject(2, domain.getGuid()); ps.setString(3, domain.getDomainName()); ps.setString(4, domain.getLoginPrefix()); ps.setLong(5, domain.getFlag()); ps.setString(6, domain.getDomainType()); ps.setLong(7, domain.getParentDomainId()); ps.setString(8, domain.getParentDomainName()); ps.setLong(9, domain.getStateDomainId()); ps.setString(10, domain.getStateDomainName()); ps.setString(11, domain.getLicenseType()); ps.setString(12, domain.getLicensePoolType()); ps.setInt(13, domain.getNoOfLicense()); ps.setBoolean(14, domain.isPilot()); ps.setDate(15, domain.getPilotStartDate()); ps.setDate(16, domain.getPilotEndDate()); ps.setBoolean(17, domain.isFullSubscription()); ps.setObject(18, domain.getSubscriptionStartDate()); ps.setObject(19, domain.getSubscriptionEndDate()); ps.setLong(20, domain.getCreatorUserId()); ps.setTimestamp(21, domain.getCreationDate()); ps.setLong(22, domain.getModifierUserId()); ps.setTimestamp(23, domain.getModifiedDate()); } }); } catch (Exception e) { log.error("Error in inserting Domains", e); } } /** * * @param domains */ public void updateDomains(List<Domain> domains) { try { jdbcTemplate.batchUpdate(DOMAINS_FULL_UPDATE_SQL, new BatchPreparedStatementSetter() { public int getBatchSize() { if (domains == null) return 0; return domains.size(); } @Override public void setValues(PreparedStatement ps, int i) throws SQLException { Domain domain = domains.get(i); ps.setObject(1, domain.getGuid()); ps.setString(2, domain.getDomainName()); ps.setString(3, domain.getLoginPrefix()); ps.setLong(4, domain.getFlag()); ps.setString(5, domain.getDomainType()); ps.setLong(6, domain.getParentDomainId()); ps.setString(7, domain.getParentDomainName()); ps.setLong(8, domain.getStateDomainId()); ps.setString(9, domain.getStateDomainName()); ps.setString(10, domain.getLicenseType()); ps.setString(11, domain.getLicensePoolType()); ps.setInt(12, domain.getNoOfLicense()); ps.setBoolean(13, domain.isPilot()); ps.setDate(14, domain.getPilotStartDate()); ps.setDate(15, domain.getPilotEndDate()); ps.setBoolean(16, domain.isFullSubscription()); ps.setObject(17, domain.getSubscriptionStartDate()); ps.setObject(18, domain.getSubscriptionEndDate()); ps.setLong(19, domain.getModifierUserId()); ps.setTimestamp(20, domain.getModifiedDate()); ps.setLong(21, domain.getDomainId()); } }); } catch (Exception e) { log.error("Error in updating Domains", e); } } }