Java tutorial
/* * Copyright 2012 Eng Kam Hon (kamhon@gmail.com) * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package net.kamhon.ieagle.dao; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.Iterator; import java.util.List; import java.util.Map; import net.kamhon.ieagle.datagrid.DatagridModel; import net.kamhon.ieagle.datagrid.Filter; import net.kamhon.ieagle.datagrid.Sorter; import net.kamhon.ieagle.exception.DataException; import net.kamhon.ieagle.jdbc.mysql.MySqlUtil; import net.kamhon.ieagle.jdbc.oracle.OracleUtil; import net.kamhon.ieagle.util.CollectionUtil; import org.apache.commons.lang.StringUtils; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.util.Assert; public class JdbcDao extends JdbcDaoSupport { abstract class NativeDatabaseObject { public abstract String convertToPaginationSql(String sql, int offset, int size); public abstract String performDateOperation(String fieldName, String operator, Date... dates); } private class MysqlDatabaseObject extends NativeDatabaseObject { @Override public String convertToPaginationSql(String sql, int offset, int size) { return MySqlUtil.convertToPaginationSql(sql, offset, size); } @Override public String performDateOperation(String fieldName, String operator, Date... dates) { return MySqlUtil.performDateOperation(fieldName, operator, dates); } } private class OracleDatabaseObject extends NativeDatabaseObject { @Override public String convertToPaginationSql(String sql, int offset, int size) { return OracleUtil.convertToPaginationSql(sql, offset, size); } @Override public String performDateOperation(String fieldName, String operator, Date... dates) { return null; } } private NativeDatabaseObject databaseObject; private NativeDatabaseObject getNativeDatabaseObject() { if (databaseObject == null) { if (getDataSource() instanceof DynamicDataSource) { String driverClassName = ((DynamicDataSource) getDataSource()).getDriverClassName().toUpperCase(); if (driverClassName.indexOf("MYSQL") > 0) { databaseObject = new MysqlDatabaseObject(); } else if (driverClassName.indexOf("ORACLE") > 0) { databaseObject = new OracleDatabaseObject(); } } else { try { String connectionClassName = getDataSource().getConnection().getClass().getName().toUpperCase(); if (connectionClassName.indexOf("MYSQL") > 0) { databaseObject = new MysqlDatabaseObject(); } else if (connectionClassName.indexOf("ORACLE") > 0) { databaseObject = new OracleDatabaseObject(); } } catch (Exception ex) { throw new DataException(ex); } } // END ELSE } return databaseObject; } public String performDateOperation(String fieldName, String operator, Date... dates) { return getNativeDatabaseObject().performDateOperation(fieldName, operator, dates); } public <T> List<T> query(String sql, RowMapper<T> rowMapper, Object[] params) { return getJdbcTemplate().query(sql, params, rowMapper); } public <T> List<T> query(String sql, RowMapper<T> rowMapper) { return getJdbcTemplate().query(sql, rowMapper); } public List<?> queryForList(String sql) { return getJdbcTemplate().queryForList(sql); } public List<Map<String, Object>> queryForList(String sql, Object[] params) { return getJdbcTemplate().queryForList(sql, params); } public List<Map<String, Object>> queryForList(String sql, int[] argTypes, Object[] params) { return getJdbcTemplate().queryForList(sql, params, argTypes); } public int queryForInt(String sql) { return getJdbcTemplate().queryForInt(sql); } public int queryForInt(String sql, Object[] params) { return getJdbcTemplate().queryForInt(sql, params); } public int queryForInt(String sql, int[] argTypes, Object[] params) { return getJdbcTemplate().queryForInt(sql, params, argTypes); } public long queryForLong(String sql) { return getJdbcTemplate().queryForLong(sql); } public long queryForLong(String sql, Object[] params) { return getJdbcTemplate().queryForLong(sql, params); } public long queryForLong(String sql, int[] argTypes, Object[] params) { return getJdbcTemplate().queryForLong(sql, params, argTypes); } public String queryForString(String replace) { return getJdbcTemplate().queryForObject(replace, String.class); } public String queryForString(String replace, Object[] params) { return getJdbcTemplate().queryForObject(replace, params, String.class); } public String queryForString(String replace, int[] argTypes, Object[] params) { return getJdbcTemplate().queryForObject(replace, params, argTypes, String.class); } public <T> T queryForObject(String sql, Class<T> requiredType) { return getJdbcTemplate().queryForObject(sql, requiredType); } public <T> T queryForObject(String sql, Class<T> requiredType, Object[] params) { return getJdbcTemplate().queryForObject(sql, params, requiredType); } public int update(String sql) { return getJdbcTemplate().update(sql); } public int update(String sql, Object[] params) { return getJdbcTemplate().update(sql, params); } public int update(String sql, int[] argTypes, Object[] params) { return getJdbcTemplate().update(sql, params, argTypes); } public int save(String sql, int[] argTypes, Object[] params) { return getJdbcTemplate().update(sql, params, argTypes); } public <T> void queryForDatagrid(DatagridModel<T> datagridModel, String sql, RowMapper<T> rowMapper, Object[] params) { Assert.isTrue(StringUtils.isNotBlank(sql), "The sql can not be BLANK!!"); if (CollectionUtil.isNotEmpty(datagridModel.getFilters())) { Assert.doesNotContain(sql, BasicDao.FILTER_PARAMS, "The " + BasicDao.FILTER_PARAMS + " is not found in Query [" + sql + "] if FILTERS is not EMPTY!!"); } Assert.notNull(getNativeDatabaseObject(), "The pagination Datagrid is not supported for your Database!!"); /************************* * END VALIDATION ************************/ // to re-set the sorter.sqlColumn() datagridModel.processBeforeQuery(); String finalQuery = sql; List<Object> finalParams = params != null ? Arrays.asList(params) : new ArrayList<Object>(); if (CollectionUtil.isNotEmpty(datagridModel.getFilters())) { for (Filter filter : datagridModel.getFilters()) { if (filter != null) throw new DataException("The Filter features still not implemented yet"); } } List<Object> countParams = new ArrayList<Object>(finalParams); String countQuery = "SELECT COUNT(*) FROM (" + finalQuery + ") t "; if (!datagridModel.isDisableSort() && CollectionUtil.isNotEmpty(datagridModel.getSorters())) { if (StringUtils.contains(finalQuery.toUpperCase(), "ORDER BY")) { finalQuery += ", "; } else { finalQuery += " ORDER BY "; } for (Iterator<Sorter> iter = datagridModel.getSorters().iterator(); iter.hasNext();) { Sorter sorter = iter.next(); // here different with HibernateDao finalQuery += sorter.getSqlColumn() + " " + sorter.getDirection(); if (iter.hasNext()) { finalQuery += ", "; } } } finalQuery = getNativeDatabaseObject().convertToPaginationSql(finalQuery, datagridModel.getRecordOffset(), datagridModel.getPageSize()); /*log.debug("countParams = " + countParams); log.debug("countQuery = " + countQuery); log.debug("params = " + finalParams); log.debug("finalQuery = " + finalQuery);*/ long count = 0; if (CollectionUtil.isEmpty(countParams)) { count = queryForLong(countQuery); } else { count = queryForLong(countQuery, countParams.toArray()); } List<T> result = null; if (CollectionUtil.isEmpty(finalParams)) { result = (List<T>) query(finalQuery, rowMapper); } else { result = (List<T>) query(finalQuery, rowMapper, finalParams.toArray()); } datagridModel.setRecords(result); datagridModel.setTotalRecords(count); } }