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.util.ArrayList; import java.util.List; import org.apache.commons.lang.StringEscapeUtils; 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 org.springframework.util.StringUtils; import com.buckwa.dao.intf.excise4.Form24Dao; import com.buckwa.domain.area4.Factory; import com.buckwa.domain.area4.Form24; 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; import com.buckwa.util.excise.ExciseConstants; import com.buckwa.util.excise.ExciseHelper; @Repository("form24Dao") public class Form24DaoImpl implements Form24Dao { private static Logger logger = Logger.getLogger(Form24DaoImpl.class); @Autowired private JdbcTemplate jdbcTemplate; public PagingBean getByOffset(PagingBean pagingBean) { Form24 form24 = (Form24) pagingBean.get("form24"); List<Form24> returnList = new ArrayList<Form24>(); StringBuffer sqltotalsb = new StringBuffer(); sqltotalsb.append(" select count(*) as total_item from form24 r \n"); sqltotalsb.append(" where 1=1 "); if (BeanUtils.isNotEmpty(form24.getIndustryId())) { sqltotalsb.append(" and r.industry_id =" + form24.getIndustryId()); } if (BeanUtils.isNotEmpty(form24.getFactoryId())) { sqltotalsb.append(" and r.factory_id =" + form24.getFactoryId()); } if (BeanUtils.isNotEmpty(form24.getStep())) { sqltotalsb.append(" and r.step =" + form24.getStep()); } int rows_totalItem = jdbcTemplate.queryForInt(sqltotalsb.toString()); pagingBean.setTotalItems(rows_totalItem); StringBuffer sb = new StringBuffer(); sb.append(" select \n"); sb.append(" * from form24 r \n"); sb.append(" where 1=1 "); if (BeanUtils.isNotEmpty(form24.getIndustryId())) { sb.append(" and r.industry_id =" + form24.getIndustryId()); } if (BeanUtils.isNotEmpty(form24.getFactoryId())) { sb.append(" and r.factory_id =" + form24.getFactoryId()); } if (BeanUtils.isNotEmpty(form24.getStep())) { sb.append(" and r.step =" + form24.getStep()); } sb.append(" order by form24_id desc "); sb.append(" LIMIT " + pagingBean.getLimitItemFrom() + "," + pagingBean.getLimitItemTo()); String sql = sb.toString(); logger.info(" sql:" + sql); returnList = this.jdbcTemplate.query(sql, new Form24Mapper()); for (Form24 tmp : returnList) { // Get Address // // try{ // String sqlAddress = " select * from address where address_id ="+tmp.getAddressId(); // // Address addressReturn = this.jdbcTemplate.queryForObject(sqlAddress,new AddressMapper()); // // tmp.setAddress(addressReturn); // }catch(Exception ex){ // // } } pagingBean.setCurrentPageItem(returnList); return pagingBean; } public PagingBean getByOffsetStep(PagingBean pagingBean, String step) { Form24 form24 = (Form24) pagingBean.get("form24"); List<Form24> returnList = new ArrayList<Form24>(); StringBuffer sqltotalsb = new StringBuffer(); sqltotalsb.append(" select count(*) as total_item from form24 r \n"); sqltotalsb.append(" where 1=1 "); if (BeanUtils.isNotEmpty(form24.getIndustryId())) { sqltotalsb.append(" and r.industry_id =" + form24.getIndustryId()); } if (BeanUtils.isNotEmpty(form24.getFactoryId())) { sqltotalsb.append(" and r.factory_id =" + form24.getFactoryId()); } if (BeanUtils.isNotEmpty(form24.getStep())) { sqltotalsb.append(" and r.step =" + form24.getStep()); } if (StringUtils.hasText(step)) { sqltotalsb.append(" and r.step >= '" + StringEscapeUtils.escapeSql(step.trim()) + "'"); } int rows_totalItem = jdbcTemplate.queryForInt(sqltotalsb.toString()); pagingBean.setTotalItems(rows_totalItem); StringBuffer sb = new StringBuffer(); sb.append(" select \n"); sb.append(" * from form24 r \n"); sb.append(" where 1=1 "); if (BeanUtils.isNotEmpty(form24.getIndustryId())) { sb.append(" and r.industry_id =" + form24.getIndustryId()); } if (BeanUtils.isNotEmpty(form24.getFactoryId())) { sb.append(" and r.factory_id =" + form24.getFactoryId()); } if (BeanUtils.isNotEmpty(form24.getStep())) { sb.append(" and r.step =" + form24.getStep()); } if (StringUtils.hasText(step)) { sb.append(" and r.step >= '" + StringEscapeUtils.escapeSql(step.trim()) + "'"); } sb.append(" order by form24_id desc "); sb.append(" LIMIT " + pagingBean.getLimitItemFrom() + "," + pagingBean.getLimitItemTo()); String sql = sb.toString(); logger.info(" sql:" + sql); returnList = this.jdbcTemplate.query(sql, new Form24Mapper()); for (Form24 tmp : returnList) { // Get Address // // try{ // String sqlAddress = " select * from address where address_id ="+tmp.getAddressId(); // // Address addressReturn = this.jdbcTemplate.queryForObject(sqlAddress,new AddressMapper()); // // tmp.setAddress(addressReturn); // }catch(Exception ex){ // // } } pagingBean.setCurrentPageItem(returnList); return pagingBean; } @Override public Industry queryIndustrybyUserName(String username) { List<Industry> returnList = new ArrayList<Industry>(); String sql = " SELECT * FROM `industry` ind WHERE ind.industry_id = (" + " 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 Industry(); return returnList.get(0); } @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 Form24 form24) { KeyHolder keyHolder = new GeneratedKeyHolder(); final StringBuilder sql = new StringBuilder(); sql.append( "INSERT INTO `form24`(`form24_id`,`industry_id`,`factory_id`,`create_date`,`create_by`,`update_date`,`update_by`,step,industry_name,tax_no,factory_name) ") .append(" VALUES ( NULL,?,?,?,?,?,?,?,?,?,?)"); 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()); final Industry industry = form24.getIndustry(); final Factory factory = form24.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.setString(7, form24.getStep()); ps.setString(8, industry.getIndustryName()); ps.setString(9, industry.getTaxNo()); ps.setString(10, factory.getFactoryName()); return ps; } }, keyHolder); final Long returnidform24 = keyHolder.getKey().longValue(); form24.setForm24Id(returnidform24); logger.info("returnidform24 : " + returnidform24); //ID PRODUCT List<Product> products = form24.getProductList(); if (products != null) { final StringBuilder psql = new StringBuilder(); psql.append( "INSERT INTO `form24_product`(`form24_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, returnidform24); 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); } } } @Override public void deleteProduct(Product p) { this.jdbcTemplate.update("DELETE FROM `form24_product` WHERE `product_id`=?;", new Object[] { p.getProcuctId() }); } @Override public void update(final Form24 form24) { 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()); String sqlform24 = "UPDATE `form24` SET `factory_id`=?,`update_date`=?,`update_by`=? WHERE `form24_id`=?"; logger.info("update: " + sqlform24); this.jdbcTemplate.update(sqlform24, new Object[] { form24.getFactory().getFactoryId(), currentDate, userName, form24.getForm24Id() }); //ID PRODUCT String productSql = "UPDATE `form24_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 `form24_product`(`form24_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 = form24.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 returnidform24 = form24.getForm24Id(); ps.setLong(1, returnidform24); 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 void updateStep(final String form24Id, final String nexStep) { try { final String userName = BuckWaUtils.getUserNameFromContext(); final String fullName = BuckWaUtils.getFullNameFromContext(); final Timestamp currentDate = new java.sql.Timestamp(System.currentTimeMillis()); final String docNo = ExciseHelper.getDocNo(); logger.info(" docNo:" + docNo + " userName:" + userName + " fuulName:" + fullName + " currentDate+" + currentDate); if (ExciseConstants.FORM24_STEP_2.equals(nexStep)) { jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement("" + " update form24 set step=?,doc_no=?,audit_by=?,audit_by_full_name=?,audit_date=? where form24_id=? " + "", Statement.RETURN_GENERATED_KEYS); ps.setString(1, nexStep); ps.setString(2, docNo); ps.setString(3, userName); ps.setString(4, fullName); ps.setTimestamp(5, currentDate); ps.setLong(6, new Long(form24Id)); return ps; } }); } else { jdbcTemplate.update(" update form24 set step ='" + nexStep + "' where form24_id=" + form24Id); } } catch (BuckWaException ex) { ex.printStackTrace(); //throw ex; } } @Override public Form24 loadform24ById(Form24 form24) { String sql = "SELECT * FROM form24 WHERE form24_id=" + form24.getForm24Id(); logger.info(" loadform24ById sql:" + sql); Form24 res = jdbcTemplate.queryForObject(sql, new Form24Mapper()); return res; } @Override public void deleteById(String form24Id) { jdbcTemplate.update(" delete from form24 where form24_id=" + form24Id); jdbcTemplate.update(" delete from form24_product where form24_id=" + form24Id); } private class Form24Mapper implements RowMapper<Form24> { @Override public Form24 mapRow(ResultSet rs, int arg1) throws SQLException { Form24 form24 = new Form24(); form24.setForm24Id(rs.getLong("form24_id")); form24.setIndustryId(rs.getLong("industry_id")); form24.setFactoryId(rs.getLong("factory_id")); form24.setStep(rs.getString("step")); form24.setDocNo(rs.getString("doc_no")); form24.setAuditBy(rs.getString("audit_by")); form24.setAuditByFullName(rs.getString("audit_by_full_name")); form24.setAuditDate(rs.getDate("audit_date")); form24.setIndustryName(rs.getString("industry_name")); form24.setTaxNo(rs.getString("tax_no")); form24.setFactoryName(rs.getString("factory_name")); return form24; } } @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> loadform24Product(Form24 form24) { String sql = "SELECT * FROM `form24_product` WHERE form24_id=?"; List<Product> res = jdbcTemplate.query(sql, new Object[] { form24.getForm24Id() }, 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; } } }