Java tutorial
package com.buckwa.dao.impl.excise4; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.text.ParseException; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.apache.commons.lang.time.DateFormatUtils; import org.apache.commons.lang.time.DateUtils; import org.apache.log4j.Logger; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; import org.springframework.stereotype.Repository; import com.buckwa.dao.intf.excise4.FactoryDao; import com.buckwa.dao.intf.excise4.Form23Dao; import com.buckwa.domain.area4.Factory; import com.buckwa.domain.area4.Form23; import com.buckwa.domain.area4.Industry; import com.buckwa.domain.area4.Product; import com.buckwa.domain.common.Address; import com.buckwa.domain.common.PagingBean; import com.buckwa.util.BeanUtils; import com.buckwa.util.BuckWaException; import com.buckwa.util.BuckWaUtils; @Repository("form23Dao") public class Form23DaoImpl implements Form23Dao { private static Logger logger = Logger.getLogger(Form23DaoImpl.class); @Autowired private JdbcTemplate jdbcTemplate; @Autowired private FactoryDao factoryDao; public PagingBean getByOffset(PagingBean pagingBean) { // Form23 domain = (Form23)pagingBean.get("form23"); List<Form23> returnList = new ArrayList<Form23>(); StringBuffer sqltotalsb = new StringBuffer(); Form23 form23 = (Form23) pagingBean.get("form23"); sqltotalsb.append(" select count(*) as total_item from form23 r \n"); sqltotalsb.append(" where 1=1 "); if (BeanUtils.isNotEmpty(form23.getIndustryId())) { sqltotalsb.append(" and r.industry_id =" + form23.getIndustryId()); } if (BeanUtils.isNotEmpty(form23.getFactoryId())) { sqltotalsb.append(" and r.factory_id =" + form23.getFactoryId()); } if (BeanUtils.isNotEmpty(form23.getStep())) { sqltotalsb.append(" and r.step =" + form23.getStep()); } int rows_totalItem = jdbcTemplate.queryForInt(sqltotalsb.toString()); pagingBean.setTotalItems(rows_totalItem); StringBuffer sb = new StringBuffer(); sb.append(" select \n"); sb.append(" * FROM form23 r \n"); sb.append(" where 1=1 "); if (BeanUtils.isNotEmpty(form23.getIndustryId())) { sb.append(" and r.industry_id =" + form23.getIndustryId()); } if (BeanUtils.isNotEmpty(form23.getFactoryId())) { sb.append(" and r.factory_id =" + form23.getFactoryId()); } if (BeanUtils.isNotEmpty(form23.getStep())) { sb.append(" and r.step =" + form23.getStep()); } sb.append(" ORDER BY r.form23_id DESC "); sb.append(" LIMIT " + pagingBean.getLimitItemFrom() + "," + pagingBean.getLimitItemTo()); String sql = sb.toString(); logger.info(" sql:" + sql); returnList = this.jdbcTemplate.query(sql, new Form23Mapper()); for (Form23 tmp : returnList) { tmp.setIndustry(loadIndustrybyId(tmp.getIndustryId())); Factory fbean = factoryDao.getFactoryById(tmp.getFactoryId().toString()); tmp.setFactory(fbean); } pagingBean.setCurrentPageItem(returnList); return pagingBean; } @Override public List<Industry> queryIndustrybyUserName(String username) { List<Industry> returnList = new ArrayList<Industry>(); String sql = " SELECT * FROM `industry` ind WHERE ind.industry_id IN (" + " SELECT industry_id FROM `industry_buyer` WHERE user_name=? " + " ) "; logger.info(" sql:" + sql); returnList = this.jdbcTemplate.query(sql, new Object[] { username }, new IndustryMapper()); if (returnList.isEmpty()) return new ArrayList<Industry>(); return returnList; } @Override public Address getByAddressById(Long addressId) { Address addressReturn; try { String sql = "select * from address where address_id = ?"; addressReturn = this.jdbcTemplate.queryForObject(sql, new Object[] { addressId }, new AddressMapper()); } catch (Exception ex) { ex.printStackTrace(); addressReturn = new Address(); } return addressReturn; } private class AddressMapper implements RowMapper<Address> { @Override public Address mapRow(ResultSet rs, int rowNum) throws SQLException { Address domain = new Address(); domain.setNo(rs.getString("no")); domain.setSoi(rs.getString("soi")); domain.setRoad(rs.getString("road")); domain.setTumbon(rs.getString("tumbon")); domain.setAumpur(rs.getString("aumpur")); domain.setProvince(rs.getString("province")); domain.setZipCode(rs.getString("zip_code")); domain.setFaxNo(rs.getString("fax_no")); domain.setCreateBy(rs.getString("create_by")); domain.setCreateDate(rs.getTimestamp("create_date")); domain.setUpdateBy(rs.getString("update_by")); // domain.setUpdateDate(rs.getTimestamp("update_date")); domain.setTelNo(rs.getString("tel_no")); return domain; } } private class IndustryMapper implements RowMapper<Industry> { @Override public Industry mapRow(ResultSet rs, int rowNum) throws SQLException { Industry domain = new Industry(); domain.setIndustryId(rs.getLong("industry_id")); domain.setIndustryName(rs.getString("industryName")); ; domain.setEntrepreneurName(rs.getString("entrepreneurName")); domain.setExciseNo(rs.getString("exciseNo")); domain.setTaxNo(rs.getString("taxNo")); domain.setUserName(rs.getString("userName")); domain.setAddressId(rs.getLong("address_id")); domain.setExciseOfficeId(rs.getLong("excise_office_id")); return domain; } } @Override public void create(final Form23 form23) { KeyHolder keyHolder = new GeneratedKeyHolder(); final StringBuilder sql = new StringBuilder(); sql.append("INSERT INTO `form23`").append( " (`form23_id`,`industry_id`,`factory_id`,`create_date`,`create_by`,`update_date`,`update_by`," + "`totalScrap`,`part4flag`,`part4fullName`,`part4Date`," + "`part5flag`,`part5licenseNo`,`part5licenseDate`,`part5billingNo`,`part5billingDate`,`part5amount`,`part5Date`," + "`part6flag`,`part6Date`,`step`)") .append(" VALUES ( NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'0')"); logger.info("SQL : " + sql.toString()); String user = ""; try { user = BuckWaUtils.getUserNameFromContext(); } catch (BuckWaException e) { e.printStackTrace(); } final String userName = user; jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { Timestamp currentDate = new Timestamp(System.currentTimeMillis()); Industry industry = form23.getIndustry(); Factory factory = form23.getFactory(); PreparedStatement ps = connection.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS); ps.setLong(1, industry.getIndustryId()); if (factory.getFactoryId() != null) { ps.setLong(2, factory.getFactoryId()); } else { ps.setNull(2, java.sql.Types.BIGINT); } ps.setTimestamp(3, currentDate); ps.setString(4, userName); ps.setTimestamp(5, currentDate); ps.setString(6, userName); ps.setBigDecimal(7, form23.getTotalScrap()); ps.setString(8, form23.getPart4flag()); ps.setString(9, form23.getPart4fullName()); ps.setTimestamp(10, currentDate); ps.setString(11, form23.getPart5flag()); ps.setString(12, form23.getPart5licenseNo()); ps.setTimestamp(13, getDateFormString(form23.getPart5licenseDate()));//part5licenseDate ps.setString(14, form23.getPart5billingNo()); ps.setTimestamp(15, getDateFormString(form23.getPart5billingDate()));//part5billingDate ps.setBigDecimal(16, form23.getPart5amount()); ps.setTimestamp(17, currentDate);//part5Date ps.setString(18, form23.getPart6flag());//part5Date ps.setTimestamp(19, currentDate);//part6Date return ps; } }, keyHolder); final Long returnidform23 = keyHolder.getKey().longValue(); form23.setForm23Id(returnidform23); form23.setStep("0"); logger.info("returnidform23 : " + returnidform23); //ID PRODUCT List<Product> products = form23.getProductList(); if (products != null) { final StringBuilder psql = new StringBuilder(); psql.append( "INSERT INTO `form23_product`(`form23_id`,`seq`,`productName`,`size`,`bandColor`,`backgroudColor`,`licenseNo`,`grossnumber200`,`grossnumber400`,`corkScrap`,`totalScrap`,`create_date`,`create_by`, `update_date`,`update_by`,`product_id`) ") .append("VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)"); logger.info("SQL : " + psql.toString()); for (final Product p : products) { jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { Timestamp currentDate = new Timestamp(System.currentTimeMillis()); PreparedStatement ps = connection.prepareStatement(psql.toString(), Statement.RETURN_GENERATED_KEYS); ps.setLong(1, returnidform23); ps.setString(2, p.getSeq()); ps.setString(3, p.getProductName()); ps.setString(4, p.getSize()); ps.setString(5, p.getBandColor()); ps.setString(6, p.getBackgroudColor()); ps.setString(7, p.getLicenseNo()); ps.setBigDecimal(8, p.getGrossnumber200()); ps.setBigDecimal(9, p.getGrossnumber400()); ps.setBigDecimal(10, p.getCorkScrap()); ps.setBigDecimal(11, p.getTotalScrap()); ps.setTimestamp(12, currentDate); ps.setString(13, userName); ps.setTimestamp(14, currentDate); ps.setString(15, userName); return ps; } }, keyHolder); long returnidproduct = keyHolder.getKey().longValue(); p.setProcuctId(returnidproduct); logger.info("returnidproduct : " + returnidproduct); } } } private Timestamp getDateFormString(String str) { if (BeanUtils.isNotEmpty(str)) { try { Date d = DateUtils.parseDate(str, new String[] { "dd/MM/yyyy" }); return new Timestamp(d.getTime()); } catch (ParseException e) { e.printStackTrace(); } } return null; } @Override public void deleteProduct(Product p) { this.jdbcTemplate.update("DELETE FROM `form23_product` WHERE `product_id`=?", new Object[] { p.getProcuctId() }); } @Override public void update(final Form23 form23) { logger.info("update"); String user = ""; try { user = BuckWaUtils.getUserNameFromContext(); } catch (BuckWaException e) { e.printStackTrace(); } final String userName = user; final Timestamp currentDate = new Timestamp(System.currentTimeMillis()); if (BeanUtils.isEmpty(form23.getCodeNo())) { form23.setCodeNo("" + System.currentTimeMillis()); } String sqlform23 = "UPDATE `form23` SET `factory_id`=?,`update_date`=?,`update_by`=? " + " ,`totalScrap`=?,`part4flag`=?,`part4fullName`=?,`part4Date`=? ," + " `part5flag`=?,`part5licenseNo`=?,`part5licenseDate`=?,`part5billingNo`=?,`part5billingDate`=?,`part5amount`=?,`part5Date`=?," + " `part6flag`=?,`part6Date`=?,`step`=?,`codeNo`=? " + " WHERE `form23_id`=?"; logger.info("update: " + sqlform23); this.jdbcTemplate.update(sqlform23, new Object[] { form23.getFactory().getFactoryId(), currentDate, userName, form23.getTotalScrap(), form23.getPart4flag(), form23.getPart4fullName(), currentDate, form23.getPart5flag(), form23.getPart5licenseNo(), getDateFormString(form23.getPart5licenseDate()), form23.getPart5billingNo(), getDateFormString(form23.getPart5billingDate()), form23.getPart5amount(), currentDate, form23.getPart6flag(), currentDate, form23.getStep(), form23.getCodeNo(), form23.getForm23Id() }); //ID PRODUCT String productSql = "UPDATE `form23_product` SET `seq`=?,`productName`=?,`size`=?,`bandColor`=?,`backgroudColor`=?,`licenseNo`=?" + ",`grossnumber200`=?,`grossnumber400`=?,`corkScrap`=?,`totalScrap`=?,`update_date`=?,`update_by`=? " + "WHERE `product_id`=?"; final StringBuilder psql = new StringBuilder(); psql.append( "INSERT INTO `form23_product`(`form23_id`,`seq`,`productName`,`size`,`bandColor`,`backgroudColor`,`licenseNo`,`grossnumber200`,`grossnumber400`,`corkScrap`,`totalScrap`,`create_date`,`create_by`, `update_date`,`update_by`,`product_id`) ") .append("VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)"); List<Product> products = form23.getProductList(); if (products != null) { for (final Product p : products) { if (BeanUtils.isEmpty(p.getProcuctId())) { logger.info("create"); KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(psql.toString(), Statement.RETURN_GENERATED_KEYS); long returnidform23 = form23.getForm23Id(); ps.setLong(1, returnidform23); ps.setString(2, p.getSeq()); ps.setString(3, p.getProductName()); ps.setString(4, p.getSize()); ps.setString(5, p.getBandColor()); ps.setString(6, p.getBackgroudColor()); ps.setString(7, p.getLicenseNo()); ps.setBigDecimal(8, p.getGrossnumber200()); ps.setBigDecimal(9, p.getGrossnumber400()); ps.setBigDecimal(10, p.getCorkScrap()); ps.setBigDecimal(11, p.getTotalScrap()); ps.setTimestamp(12, currentDate); ps.setString(13, userName); ps.setTimestamp(14, currentDate); ps.setString(15, userName); return ps; } }, keyHolder); long returnidproduct = keyHolder.getKey().longValue(); p.setProcuctId(returnidproduct); logger.info("returnidproduct : " + returnidproduct); } else { logger.info("update"); this.jdbcTemplate.update(productSql, new Object[] { p.getSeq(), p.getProductName(), p.getSize(), p.getBandColor(), p.getBackgroudColor(), p.getLicenseNo(), p.getGrossnumber200(), p.getGrossnumber400(), p.getCorkScrap(), p.getTotalScrap(), currentDate, userName, p.getProcuctId() }); } } } } @Override public Form23 loadform23ById(Form23 form23) { String sql = "SELECT * FROM `form23` WHERE form23_id=?"; Form23 res = jdbcTemplate.queryForObject(sql, new Form23Mapper(), new Object[] { form23.getForm23Id() }); return res; } private class Form23Mapper implements RowMapper<Form23> { @Override public Form23 mapRow(ResultSet rs, int arg1) throws SQLException { Form23 form23 = new Form23(); form23.setForm23Id(rs.getLong("form23_id")); form23.setIndustryId(rs.getLong("industry_id")); form23.setFactoryId(rs.getLong("factory_id")); form23.setTotalScrap(rs.getBigDecimal("totalScrap")); form23.setPart4flag(rs.getString("part4flag")); form23.setPart4fullName(rs.getString("part4fullName")); if (BeanUtils.isNotEmpty(rs.getDate("part4Date"))) form23.setPart4Date(DateFormatUtils.format(rs.getDate("part4Date"), "dd/MM/yyyy")); form23.setPart5flag(rs.getString("part5flag")); form23.setPart5licenseNo(rs.getString("part5licenseNo")); if (BeanUtils.isNotEmpty(rs.getDate("part5licenseDate"))) form23.setPart5licenseDate(DateFormatUtils.format(rs.getDate("part5licenseDate"), "dd/MM/yyyy")); form23.setPart5billingNo(rs.getString("part5billingNo")); if (BeanUtils.isNotEmpty(rs.getDate("part5billingDate"))) form23.setPart5billingDate(DateFormatUtils.format(rs.getDate("part5billingDate"), "dd/MM/yyyy")); form23.setPart5amount(rs.getBigDecimal("part5amount")); form23.setPart6flag(rs.getString("part6flag")); form23.setStep(rs.getString("step")); form23.setDownloadId(rs.getString("downloadId")); form23.setStep1_by(rs.getString("step1_by")); form23.setStep2_by(rs.getString("step2_by")); if (BeanUtils.isNotEmpty(rs.getDate("part5Date"))) form23.setPart5Date(DateFormatUtils.format(rs.getDate("part5Date"), "dd/MM/yyyy")); if (BeanUtils.isNotEmpty(rs.getDate("part6Date"))) form23.setPart6Date(DateFormatUtils.format(rs.getDate("part6Date"), "dd/MM/yyyy")); form23.setCodeNo(rs.getString("codeNo")); return form23; } } @Override public Industry loadIndustrybyId(Long id) { Industry industry = new Industry(); String sql = " SELECT * FROM `industry` ind WHERE ind.industry_id=?"; industry = jdbcTemplate.queryForObject(sql, new IndustryMapper(), new Object[] { id }); return industry; } @Override public List<Product> loadform23Product(Form23 form23) { String sql = "SELECT * FROM `form23_product` WHERE form23_id=?"; List<Product> res = jdbcTemplate.query(sql, new Object[] { form23.getForm23Id() }, new ProductMapper()); return res; } private class ProductMapper implements RowMapper<Product> { @Override public Product mapRow(ResultSet rs, int arg1) throws SQLException { Product p = new Product(); p.setSeq(rs.getString("seq")); p.setProductName(rs.getString("productName")); p.setSize(rs.getString("size")); p.setBandColor(rs.getString("bandColor")); p.setBackgroudColor(rs.getString("backgroudColor")); p.setLicenseNo(rs.getString("licenseNo")); p.setGrossnumber200(rs.getBigDecimal("grossnumber200")); p.setGrossnumber400(rs.getBigDecimal("grossnumber400")); p.setCorkScrap(rs.getBigDecimal("corkScrap")); p.setTotalScrap(rs.getBigDecimal("totalScrap")); p.setProcuctId(rs.getLong("product_id")); return p; } } @Override public void removeform23(Long form23Id) { this.jdbcTemplate.update("DELETE FROM `form23` WHERE form23_id=?", new Object[] { form23Id }); } @Override public void updatepart5(Form23 form23) { String user = null; try { user = BuckWaUtils.getFullNameFromContext(); } catch (BuckWaException e) { e.printStackTrace(); } form23.setStep1_by(user); Timestamp currentDate = new Timestamp(System.currentTimeMillis()); String sql = "UPDATE `form23` " + "SET `part5flag`=? ,`part5licenseNo`=?,`part5licenseDate`=?, " + "`part5billingNo`=?,`part5billingDate`=?,`part5amount`=?,`step1_by`=?,`step1_date`=? ," + "`step`=? " + " WHERE `form23_id`=?;"; this.jdbcTemplate.update(sql, new Object[] { form23.getPart5flag(), form23.getPart5licenseNo(), getDateFormString(form23.getPart5licenseDate()), form23.getPart5billingNo(), getDateFormString(form23.getPart5billingDate()), form23.getPart5amount(), form23.getStep1_by(), currentDate, form23.getStep(), form23.getForm23Id() }); } @Override public void updatepart6(Form23 form23) { logger.info("updatepart6"); String user = null; try { user = BuckWaUtils.getFullNameFromContext(); } catch (BuckWaException e) { e.printStackTrace(); } form23.setStep2_by(user); Timestamp currentDate = new Timestamp(System.currentTimeMillis()); String sql = "UPDATE `form23` SET `part6flag`=?,`part6Date`=?,`step`=?,`step2_by`=?,`step2_date`=? WHERE `form23_id`=?"; // form23.get this.jdbcTemplate.update(sql, new Object[] { form23.getPart6flag(), currentDate, form23.getStep(), form23.getStep2_by(), currentDate, form23.getForm23Id() }); } }