Java tutorial
/* * Copyright 2014 by SCSK Corporation. * * This file is part of PrimeCloud Controller(TM). * * PrimeCloud Controller(TM) is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 2 of the License, or * (at your option) any later version. * * PrimeCloud Controller(TM) is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with PrimeCloud Controller(TM). If not, see <http://www.gnu.org/licenses/>. */ package jp.primecloud.auto.tool.management.db; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Locale; import java.util.Map; import java.util.regex.Pattern; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; public class SQLExecuter { private DBConnector dbConnector; protected static Log log = LogFactory.getLog(SQLExecuter.class); public SQLExecuter(String url, String username, String password) { if (dbConnector == null) { this.dbConnector = new DBConnector(url, username, password); } } private static String passwordMask(String message) { Pattern pattern = Pattern.compile("PASSWORD='\\w*'"); return pattern.matcher(message).replaceAll("PASSWORD='\\*\\*\\*\\*\\*'"); } static public String escape(String input) { input = substitute(input, "'", "''"); input = substitute(input, "\\", "\\\\"); return input; } public static String substitute(String input, String pattern, String replacement) { // ???? int index = input.indexOf(pattern); // ?????? if (index == -1) { return input; } // ????? StringBuffer StringBuffer buffer = new StringBuffer(); buffer.append(input.substring(0, index) + replacement); if (index + pattern.length() < input.length()) { // ???? String rest = input.substring(index + pattern.length(), input.length()); buffer.append(substitute(rest, pattern, replacement)); } return buffer.toString(); } public void execute(String sql) throws SQLException, Exception { Connection con = null; Statement stmt = null; ResultSet rs = null; String logSQL = ""; // ?? logSQL = passwordMask(sql); log.info("[" + logSQL + "] ???"); try { con = dbConnector.getConnection(); stmt = con.createStatement(); stmt.execute(sql); log.info("[" + logSQL + "] ????"); } catch (SQLException e) { log.error(passwordMask(e.getMessage()), e); throw new SQLException(e); } catch (Exception e) { log.error(passwordMask(e.getMessage()), e); throw new Exception(e); } finally { try { dbConnector.closeConnection(con, stmt, rs); } catch (Exception e) { e.printStackTrace(); } } } public void executePrepared(String sql, String... params) throws SQLException, Exception { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; // ?? String logSQL = passwordMask(sql); log.info("[" + logSQL + "] ???"); try { con = dbConnector.getConnection(); ps = con.prepareStatement(sql); for (int i = 0; i < params.length; i++) { ps.setString(i + 1, params[i]); } ps.execute(); log.info("[" + logSQL + "] ????"); } catch (SQLException e) { log.error(passwordMask(e.getMessage()), e); throw new SQLException(e); } catch (Exception e) { log.error(passwordMask(e.getMessage()), e); throw new Exception(e); } finally { try { dbConnector.closeConnection(con, ps, rs); } catch (Exception e) { e.printStackTrace(); } } } public int getNextid(String sql) throws SQLException, Exception { Connection con = null; Statement stmt = null; ResultSet rs = null; int nextid = 0; log.info("[" + sql + "] ???"); try { con = dbConnector.getConnection(); stmt = con.createStatement(); rs = stmt.executeQuery(sql); while (rs.next()) { nextid = rs.getInt("nextid"); } log.info("[" + sql + "] ????"); } catch (SQLException e) { log.error(e.getMessage(), e); throw new SQLException(e); } catch (Exception e) { log.error(e.getMessage(), e); throw new Exception(e); } finally { try { dbConnector.closeConnection(con, stmt, rs); } catch (Exception e) { e.printStackTrace(); } } return nextid; } public int getGroupid(String sql) throws SQLException, Exception { Connection con = null; Statement stmt = null; ResultSet rs = null; int groupid = 0; log.info("[" + sql + "] ???"); try { con = dbConnector.getConnection(); stmt = con.createStatement(); rs = stmt.executeQuery(sql); while (rs.next()) { groupid = rs.getInt("groupid"); } log.info("[" + sql + "] ????"); } catch (SQLException e) { log.error(e.getMessage(), e); throw new SQLException(e); } catch (Exception e) { log.error(e.getMessage(), e); throw new Exception(e); } finally { try { dbConnector.closeConnection(con, stmt, rs); } catch (Exception e) { e.printStackTrace(); } } return groupid; } public Object getColumn(String sql, String columnName, String columnType) throws SQLException, Exception { Connection con = null; Statement stmt = null; ResultSet rs = null; Object object = null; log.info("[" + sql + "] ???"); try { con = dbConnector.getConnection(); stmt = con.createStatement(); rs = stmt.executeQuery(sql); while (rs.next()) { if (columnType.equals("string")) { object = rs.getString(columnName); } else if (columnType.equals("int")) { object = rs.getInt(columnName); } } log.info("[" + sql + "] ????"); } catch (SQLException e) { log.error(e.getMessage(), e); throw new SQLException(e); } catch (Exception e) { log.error(e.getMessage(), e); throw new Exception(e); } finally { try { dbConnector.closeConnection(con, stmt, rs); } catch (Exception e) { e.printStackTrace(); } } return object; } public int getColumnAsInt(String sql, String columnName) throws SQLException, Exception { Connection con = null; Statement stmt = null; ResultSet rs = null; int number = 0; log.info("[" + sql + "] ???"); try { con = dbConnector.getConnection(); stmt = con.createStatement(); rs = stmt.executeQuery(sql); while (rs.next()) { number = rs.getInt(columnName); } log.info("[" + sql + "] ????"); } catch (SQLException e) { log.error(e.getMessage(), e); throw new SQLException(e); } catch (Exception e) { log.error(e.getMessage(), e); throw new Exception(e); } finally { try { dbConnector.closeConnection(con, stmt, rs); } catch (Exception e) { e.printStackTrace(); } } return number; } public List<List<Object>> showColumn(String sql) throws SQLException, Exception { Connection con = null; Statement stmt = null; ResultSet rs = null; log.info("[" + sql + "] ???"); List<List<Object>> results = new ArrayList<List<Object>>(); try { con = dbConnector.getConnection(); stmt = con.createStatement(); rs = stmt.executeQuery(sql); ResultSetMetaData rsMetaData = rs.getMetaData(); int size = rsMetaData.getColumnCount(); List<Object> columnNames = new ArrayList<Object>(); for (int n = 1; n <= size; n++) { columnNames.add(rsMetaData.getColumnName(n)); } results.add(columnNames); while (rs.next()) { List<Object> columns = new ArrayList<Object>(); for (int i = 1; i <= size; i++) { columns.add(rs.getObject(i)); } results.add(columns); } log.info("[" + sql + "] ????"); } catch (SQLException e) { log.error(e.getMessage(), e); throw new SQLException(e); } catch (Exception e) { log.error(e.getMessage(), e); throw new Exception(e); } finally { try { dbConnector.closeConnection(con, stmt, rs); } catch (Exception e) { e.printStackTrace(); } } return results; } public List<Map<String, Object>> showColumns(String sql) throws SQLException, Exception { Connection con = null; Statement stmt = null; ResultSet rs = null; log.info("[" + sql + "] ???"); List<Map<String, Object>> results = new ArrayList<Map<String, Object>>(); try { con = dbConnector.getConnection(); stmt = con.createStatement(); rs = stmt.executeQuery(sql); ResultSetMetaData rsMetaData = rs.getMetaData(); int size = rsMetaData.getColumnCount(); while (rs.next()) { Map<String, Object> result = new HashMap<String, Object>(); for (int i = 1; i <= size; i++) { result.put(parseColumnName(rsMetaData.getColumnName(i)), rs.getObject(i)); } results.add(result); } log.info("[" + sql + "] ????"); } catch (SQLException e) { log.error(e.getMessage(), e); throw new SQLException(e); } catch (Exception e) { log.error(e.getMessage(), e); throw new Exception(e); } finally { try { dbConnector.closeConnection(con, stmt, rs); } catch (Exception e) { e.printStackTrace(); } } return results; } private String parseColumnName(String columnName) { String name = columnName.toLowerCase(Locale.ENGLISH); String[] array = name.split("_"); if (array.length == 1) { return array[0]; } StringBuilder sb = new StringBuilder(); if (array[0].length() == 1) { sb.append(Character.toUpperCase(array[0].charAt(0))); } else { sb.append(array[0]); } for (int i = 1; i < array.length; i++) { sb.append(Character.toUpperCase(array[i].charAt(0))).append(array[i].substring(1)); } return sb.toString(); } }