Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.vigglet.util; import com.vigglet.db.DbConnector; import com.vigglet.model.BaseModel; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Collection; import java.util.List; import java.util.logging.Level; import java.util.logging.Logger; import java.util.stream.Collectors; import org.apache.commons.lang3.StringUtils; /** * * @author vikn * @param <T> */ public abstract class ModelUtilBase<T extends BaseModel> { private static final Logger logger = Logger.getLogger(BaseModel.class.getName()); protected abstract String getTableName(); protected abstract List<String> getFieldList(); protected abstract T readModel(ResultSet rs) throws SQLException; protected abstract T insertOrUpdate(T model); protected PreparedStatement getSelectAllQuery() throws SQLException { return DbConnector.getInstance().getConnection().prepareStatement("SELECT * FROM oei." + getTableName()); } protected PreparedStatement getFindByIdQuery() throws SQLException { return DbConnector.getInstance().getConnection() .prepareStatement("SELECT * FROM oei." + getTableName() + " WHERE id=?"); } protected PreparedStatement getFindByCompanyQuery() throws SQLException { return DbConnector.getInstance().getConnection() .prepareStatement("SELECT * FROM oei." + getTableName() + " WHERE company=?"); } protected PreparedStatement getLimitedQuery() throws SQLException { return DbConnector.getInstance().getConnection() .prepareStatement("SELECT * FROM oei." + getTableName() + " WHERE company=? ORDER BY id LIMIT ?"); } protected PreparedStatement getOrderByQuery(String orderField) throws SQLException { return getOrderByQuery(orderField, "ASC"); } protected PreparedStatement getOrderByQuery(String orderField, String orderDirection) throws SQLException { return DbConnector.getInstance().getConnection().prepareStatement("SELECT * FROM oei." + getTableName() + " WHERE company=? ORDER BY " + orderField + " " + orderDirection); } protected PreparedStatement getTotalItemsQuery() throws SQLException { return DbConnector.getInstance().getConnection() .prepareStatement("SELECT COUNT(id) as totalItems FROM oei." + getTableName() + " WHERE company=?"); } protected int getMinLenghtOfSearchText() { return 1; } public int getTotalItems(int company) { int result = 0; try { PreparedStatement stmt = getTotalItemsQuery(); stmt.setInt(1, company); ResultSet rs = stmt.executeQuery(); if (rs.next()) { result = rs.getInt("totalItems"); } rs.close(); stmt.close(); } catch (SQLException ex) { logger.log(Level.SEVERE, null, ex); } return result; } public T findById(int id) { T result = null; try { PreparedStatement stmt = getFindByIdQuery(); stmt.setInt(1, id); ResultSet rs = stmt.executeQuery(); if (rs.next()) { result = readModel(rs); } rs.close(); stmt.close(); } catch (SQLException ex) { logger.log(Level.SEVERE, null, ex); } return result; } public Collection<T> findByCompany(int company, int pageSize, int page, String searchText) { return findByCompany(company, pageSize, page, searchText, 0); } public Collection<T> findByCompany(int company, int pageSize, int page, String searchText, int limit) { int skip = (pageSize * (page - 1)); if (page > 0 || (searchText != null && !searchText.isEmpty() && searchText.length() >= getMinLenghtOfSearchText())) { Collection<T> result = getOrderdList(company); if (searchText != null && !searchText.isEmpty() && searchText.length() >= getMinLenghtOfSearchText()) { String[] searchTextSplit = searchText.split(" "); if (searchTextSplit.length > 1 || !StringUtils.isNumeric(searchText)) { result = result.stream().filter((T t) -> t.matchesSearchableFields(searchTextSplit)) .collect(Collectors.toList()); } } int i = 0; if (page > 0) { Collection<T> result2 = new ArrayList<>(pageSize); for (T t : result) { if (++i < skip) { continue; } if (i == (skip + pageSize)) { break; } if (i >= skip && i <= (skip + pageSize)) { result2.add(t); } } return result2; } return result; } if (limit > 0) { return getLimitedList(company, limit); } return findByCompany(company); } public Collection<T> findByCompany(int company) { Collection<T> result = null; try { PreparedStatement stmt = getFindByCompanyQuery(); stmt.setInt(1, company); ResultSet rs = stmt.executeQuery(); rs.last(); result = new ArrayList<>(rs.getRow()); rs.beforeFirst(); while (rs.next()) { result.add(readModel(rs)); } rs.close(); stmt.close(); } catch (SQLException ex) { logger.log(Level.SEVERE, null, ex); } return result; } public List<T> getLimitedList(int company, int limit) { long startMs = System.currentTimeMillis(); logger.log(Level.INFO, "Fetching limited (" + limit + ") list..."); List<T> result = null; try { PreparedStatement stmt = getLimitedQuery(); stmt.setInt(1, company); stmt.setInt(2, limit); ResultSet rs = stmt.executeQuery(); rs.last(); result = new ArrayList<>(rs.getRow()); rs.beforeFirst(); while (rs.next()) { result.add(readModel(rs)); } rs.close(); stmt.close(); } catch (SQLException ex) { logger.log(Level.SEVERE, null, ex); } long endMs = System.currentTimeMillis(); logger.log(Level.INFO, "Done in " + (endMs - startMs) + " ms (" + (endMs - startMs) / 1000 + " s)"); return result; } public List<T> getOrderdList(int company) { return getOrderdList(company, "id", null); } public List<T> getOrderdList(int company, String orderField) { return getOrderdList(company, orderField, null); } public List<T> getOrderdList(int company, String orderField, String orderDirection) { List<T> result = null; try { PreparedStatement stmt = null; if (orderDirection != null && orderDirection.length() > 0) { stmt = getOrderByQuery(orderField, orderDirection); } else { stmt = getOrderByQuery(orderField); } stmt.setInt(1, company); ResultSet rs = stmt.executeQuery(); rs.last(); result = new ArrayList<>(rs.getRow()); rs.beforeFirst(); while (rs.next()) { result.add(readModel(rs)); } rs.close(); stmt.close(); } catch (SQLException ex) { logger.log(Level.SEVERE, null, ex); } return result; } public Collection<T> getPkMap() { Collection<T> result = null; try { PreparedStatement stmt = getSelectAllQuery(); ResultSet rs = stmt.executeQuery(); rs.last(); result = new ArrayList<>(rs.getRow()); rs.beforeFirst(); while (rs.next()) { result.add(readModel(rs)); } rs.close(); stmt.close(); } catch (SQLException ex) { logger.log(Level.SEVERE, null, ex); } return result; } protected T insertOrUpdate(T model, List<Object> values) { T result = null; try { PreparedStatement stmt = null; if (model.getId() > 0) { stmt = getUpdateStatement(); } else { stmt = getInsertStatement(); } int i = 1; for (Object obj : values) { stmt.setObject(i++, obj); } if (model.getId() > 0) { stmt.setInt(i++, model.getId()); } stmt.executeUpdate(); int id = 0; if (model.getId() > 0) { id = model.getId(); } else { ResultSet rs = stmt.getGeneratedKeys(); if (rs.next()) { id = rs.getInt(1); } rs.close(); } result = findById(id); stmt.close(); } catch (SQLException ex) { logger.log(Level.SEVERE, null, ex); } return result; } public String collectionToJson(Collection<T> collection) { StringBuilder sb = new StringBuilder(); int i = 0; sb.append("["); for (T obj : collection) { if (i++ > 0) { sb.append(", "); } sb.append(obj.toJson()); } sb.append("]"); return sb.toString(); } protected PreparedStatement getInsertStatement() throws SQLException { int i = 0; List<String> fieldList = getFieldList(); StringBuilder fields = new StringBuilder(64); StringBuilder values = new StringBuilder(64); fields.append(" ("); values.append(" VALUES ("); for (String string : fieldList) { fields.append("`"); fields.append(string); fields.append("`"); values.append("?"); if (++i < fieldList.size()) { fields.append(","); values.append(","); } } fields.append(")"); values.append(");"); return DbConnector.getInstance().getConnection().prepareStatement( "INSERT INTO oei." + getTableName() + fields.toString() + values.toString(), Statement.RETURN_GENERATED_KEYS); } protected PreparedStatement getUpdateStatement() throws SQLException { int i = 0; List<String> fieldList = getFieldList(); StringBuilder sql = new StringBuilder(64); sql.append(" SET "); for (String string : fieldList) { sql.append("`"); sql.append(string); sql.append("`=?"); if (++i < fieldList.size()) { sql.append(","); } } return DbConnector.getInstance().getConnection().prepareStatement( "UPDATE oei." + getTableName() + sql.toString() + " WHERE id=?;", Statement.RETURN_GENERATED_KEYS); } }