Java tutorial
/* * Copyright 2007-2012 Jiemamy Project and the Others. * Created on 2009/05/22 * * This file is part of Jiemamy. * * 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 org.jiemamy.utils.sql; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.io.Reader; import java.io.StringReader; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.apache.commons.dbutils.DbUtils; import org.apache.commons.lang.Validate; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.jiemamy.utils.LogMarker; /** * SQL ? * * <p>?? SQL ???????? SQL ???????? SQL ??????????</p> * * <p>? SQL ?????? SQL ???????????? * ???????????????SQL????{@link SQLException}?</p> * * <p>????????????????{@code "SELECT ENAME FROM EMP"} ? * {@code "SELECT DNAME FROM DEPT"} ??? SQL ?</p> * <p><pre><code> * String sql = "SELECT ENAME FROM EMP; SELECT DNAME FROM DEPT;"; * SqlExecutor executor = new SqlExecutor(connection); * executor.execute(sql); * </code></pre></p> * * @version $Id$ * @author Keisuke.K */ public class SqlExecutor { private static Logger logger = LoggerFactory.getLogger(SqlExecutor.class); static final char SINGLEQUOTE = '\''; static final char SEMICOLON = ';'; final Connection connection; /** * ?? * * @param connection * @throws IllegalArgumentException ?{@code null}??? */ public SqlExecutor(Connection connection) { Validate.notNull(connection); this.connection = connection; } /** * SQL? * * @param is SQL????????? * @throws SQLException SQL????? * @throws IOException SQL?????? * @throws IllegalArgumentException ?{@code null}??? */ public void execute(InputStream is) throws SQLException, IOException { Validate.notNull(is); execute(is, null); } /** * SQL ????? * * <p>?????????{@link #execute(Reader, SqlExecutorHandler)} ????</p> * * @param is SQL????????? * @param handler SQL??????{@code null}???????? * @throws SQLException SQL????? * @throws IOException SQL?????? * @throws IllegalArgumentException {@code is}?{@code null}??? * @see #execute(Reader, SqlExecutorHandler) */ public void execute(InputStream is, SqlExecutorHandler handler) throws SQLException, IOException { Validate.notNull(is); execute(new InputStreamReader(is), handler); } /** * SQL? * * @param in SQL????????? * @throws SQLException SQL????? * @throws IOException SQL?????? * @throws IllegalArgumentException ?{@code null}??? */ public void execute(Reader in) throws SQLException, IOException { Validate.notNull(in); execute(in, null); } /** * SQL ????? * * <p> {@code handler} ? {@code null} ?????SQL ?? {@link SqlExecutorHandler} ??? * ?????????? SQL ???? * {@link SqlExecutorHandler#handleResultSet(String, ResultSet)}?? SQL ???? * {@link SqlExecutorHandler#handleUpdateCount(String, int)} ??</p> * * <p>???{@code SqlExecutor} ? {@link java.sql.Statement#getMoreResults()} ????????SQL * ????? {@link java.sql.ResultSet} ???????????????????? * ???</p> * * @param in SQL????????? * @param handler SQL??????{@code null}???????? * @throws SQLException SQL????? * @throws IOException SQL?????? * @throws IllegalArgumentException {@code in}?{@code null}??? */ public void execute(Reader in, SqlExecutorHandler handler) throws SQLException, IOException { Validate.notNull(in); StringBuilder builder = new StringBuilder(); boolean quotedFlag = false; boolean execFlag = false; for (int ch = in.read(); ch != -1; ch = in.read()) { switch (ch) { case SINGLEQUOTE: quotedFlag = !quotedFlag; break; case SEMICOLON: execFlag = !quotedFlag; break; default: if (builder.length() <= 0 && Character.isWhitespace(ch)) { continue; } // no more operations, go ahead. } if (execFlag) { executeSingleSql(builder.toString(), handler); builder.setLength(0); execFlag = false; } else { builder.append((char) ch); } } } /** * SQL? * * @param sql ?SQL???????? * @throws SQLException SQL????? * @throws IllegalArgumentException {@code sql}?{@code null}??? */ public void execute(String sql) throws SQLException { execute(sql, null); } /** * SQL ????? * * <p>?????????{@link #execute(Reader, SqlExecutorHandler)} ????</p> * * @param sql ?SQL???????? * @param handler SQL??????{@code null}???????? * @throws SQLException SQL????? * @throws IllegalArgumentException {@code sql}?{@code null}??? * @see #execute(Reader, SqlExecutorHandler) */ public void execute(String sql, SqlExecutorHandler handler) throws SQLException { Validate.notNull(sql); try { execute(new StringReader(sql), handler); } catch (IOException e) { throw new SQLException(e.getMessage()); } } void executeSingleSql(String sql, SqlExecutorHandler handler) throws SQLException { logger.info(LogMarker.DETAIL, sql); boolean isAutoCommit = connection.getAutoCommit(); connection.setAutoCommit(false); Statement stmt = null; ResultSet rs = null; try { stmt = connection.createStatement(); if (stmt.execute(sql)) { if (handler != null) { rs = stmt.getResultSet(); handler.handleResultSet(sql, rs); } } else { if (handler != null) { int count = stmt.getUpdateCount(); if (count >= 0) { handler.handleUpdateCount(sql, count); } } } connection.commit(); } catch (SQLException e) { logger.warn(sql, e); connection.rollback(); throw e; } finally { connection.setAutoCommit(isAutoCommit); DbUtils.closeQuietly(rs); DbUtils.closeQuietly(stmt); } } }