org.hsweb.ezorm.rdb.executor.AbstractJdbcSqlExecutor.java Source code

Java tutorial

Introduction

Here is the source code for org.hsweb.ezorm.rdb.executor.AbstractJdbcSqlExecutor.java

Source

/*
 * Copyright 2016 http://github.com/hs-web
 *
 * 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.hsweb.ezorm.rdb.executor;

import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.commons.beanutils.PropertyUtilsBean;
import org.onion.commons.StringUtils;
import org.hsweb.ezorm.core.ObjectWrapper;
import org.hsweb.ezorm.core.param.Term;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.ByteArrayInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * JDBC sql,sql.??
 *
 * @author zhouhao
 * @since 1.0
 */
public abstract class AbstractJdbcSqlExecutor implements SqlExecutor {
    protected Logger logger = LoggerFactory.getLogger(this.getClass());

    /**
     * ?jdbc,?
     *
     * @return jdbc 
     */
    public abstract Connection getConnection();

    /**
     * sql?: ${}
     *
     * @since 1.0
     */
    public static final Pattern APPEND_PATTERN = Pattern.compile("(?<=\\$\\{)(.+?)(?=\\})");

    /**
     * ??:#{}
     *
     * @since 1.0
     */
    public static final Pattern PREPARED_PATTERN = Pattern.compile("(?<=#\\{)(.+?)(?=\\})");

    /**
     * ?
     *
     * @see PropertyUtilsBean
     */
    protected PropertyUtilsBean propertyUtils = BeanUtilsBean.getInstance().getPropertyUtils();

    protected Object getSqlParamValue(Object param, String paramName) {
        try {
            Object obj = propertyUtils.getProperty(param, paramName);
            if (obj instanceof Term)
                obj = ((Term) obj).getValue();
            return obj;
        } catch (Exception e) {
            logger.warn("?sql?", e);
        }
        return null;
    }

    /**
     * sql?sql?
     * ?:${}sql,#{}
     * : ??:{name:"",age:10},sql:select * from user where name=#{name} and age=${age}
     * :select * from user where name=? and age=10 ?:[""]
     *
     * @param sql sql? ,?{@link org.hsweb.ezorm.rdb.render.support.simple.SimpleSQL}
     * @return sql ?
     */
    public SQLInfo compileSql(SQL sql) {
        String sqlTemplate = sql.getSql();
        try {
            SQLInfo sqlInfo = new SQLInfo();
            Object param = sql.getParams();
            Matcher prepared_matcher = PREPARED_PATTERN.matcher(sqlTemplate);
            Matcher append_matcher = APPEND_PATTERN.matcher(sqlTemplate);
            List<Object> params = new LinkedList<>();
            //sql
            while (append_matcher.find()) {
                String group = append_matcher.group();
                Object obj = getSqlParamValue(param, group);
                sqlTemplate = sqlTemplate.replaceFirst(
                        StringUtils.concat("\\$\\{", escapeExprSpecialWord(group), "\\}"), String.valueOf(obj));
            }
            //?sql
            while (prepared_matcher.find()) {
                String group = prepared_matcher.group();
                sqlTemplate = sqlTemplate
                        .replaceFirst(StringUtils.concat("#\\{", escapeExprSpecialWord(group), "\\}"), "?");
                Object obj = getSqlParamValue(param, group);
                params.add(obj);
            }
            sqlInfo.setSql(sqlTemplate);
            sqlInfo.setParam(params.toArray());
            return sqlInfo;
        } catch (Exception e) {
            logger.error("compile sql  {}  error", sqlTemplate, e);
            throw e;
        }

    }

    /**
     * ,sql?,
     */
    public abstract void releaseConnection(Connection connection) throws SQLException;

    @Override
    public <T> List<T> list(SQL sql, ObjectWrapper<T> wrapper) throws SQLException {
        if (sql instanceof EmptySQL)
            return new ArrayList<>();
        //sql??sql
        SQLInfo info = compileSql(sql);
        printSql(info);//?sql?
        Connection connection = getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<T> datas = new ArrayList<>();
        try {
            //SQL
            statement = connection.prepareStatement(info.getSql());
            this.preparedParam(statement, info);
            //sql
            resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int count = metaData.getColumnCount();
            //?sql??
            List<String> headers = new ArrayList<>();
            for (int i = 1; i <= count; i++) {
                headers.add(metaData.getColumnLabel(i));
            }
            wrapper.setUp(headers);
            int index = 0;
            while (resultSet.next()) {
                //,
                T data = wrapper.newInstance();
                for (int i = 0; i < headers.size(); i++) {
                    Object value = resultSet.getObject(i + 1);
                    wrapper.wrapper(data, index, headers.get(i), value);
                }
                index++;
                wrapper.done(data);
                datas.add(data);
            }
            if (logger.isDebugEnabled()) {
                logger.debug("<==      total: {}", index);
            }
        } finally {
            closeResultSet(resultSet);
            closeStatement(statement);
            //?JDBC
            releaseConnection(connection);
        }
        return datas;
    }

    protected void closeResultSet(ResultSet resultSet) {
        try {
            if (null != resultSet)
                resultSet.close();
        } catch (SQLException e) {
            logger.error("close ResultSet error", e);
        }
    }

    protected void closeStatement(Statement statement) {
        try {
            if (null != statement)
                statement.close();
        } catch (SQLException e) {
            logger.error("close ResultSet error", e);
        }
    }

    @Override
    public <T> T single(SQL sql, ObjectWrapper<T> wrapper) throws SQLException {
        if (sql instanceof EmptySQL)
            return null;
        SQLInfo info = compileSql(sql);
        printSql(info);
        Connection connection = getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        T data = null;
        try {
            statement = connection.prepareStatement(info.getSql());
            //?
            this.preparedParam(statement, info);
            resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int count = metaData.getColumnCount();
            List<String> headers = new ArrayList<>();
            for (int i = 1; i <= count; i++) {
                headers.add(metaData.getColumnLabel(i));
            }
            wrapper.setUp(headers);
            int index = 0;
            if (resultSet.next()) {
                data = wrapper.newInstance();
                for (int i = 0; i < headers.size(); i++) {
                    Object value = resultSet.getObject(i + 1);
                    wrapper.wrapper(data, index, headers.get(i), value);
                }
                index++;
                wrapper.done(data);
            }
            if (logger.isDebugEnabled()) {
                logger.debug("<==      total: {}", index);
            }
        } finally {
            closeResultSet(resultSet);
            closeStatement(statement);
            releaseConnection(connection);
        }
        return data;
    }

    @Override
    public void exec(SQL sql) throws SQLException {
        if (sql instanceof EmptySQL)
            return;
        SQLInfo info = compileSql(sql);
        printSql(info);
        Connection connection = getConnection();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(info.getSql());
            //?
            this.preparedParam(statement, info);
            statement.execute();
            if (sql.getBinds() != null) {
                for (BindSQL bindSQL : sql.getBinds()) {
                    exec(bindSQL.getSql());
                }
            }
        } finally {
            closeStatement(statement);
            releaseConnection(connection);
        }
    }

    @Override
    public int update(SQL sql) throws SQLException {
        if (sql instanceof EmptySQL)
            return 0;
        SQLInfo info = compileSql(sql);
        printSql(info);
        Connection connection = getConnection();
        int i = 0;
        try {
            PreparedStatement statement = connection.prepareStatement(info.getSql());
            this.preparedParam(statement, info);
            i = statement.executeUpdate();
            if (logger.isDebugEnabled())
                logger.debug("<==    updated: {} rows", i);
            closeStatement(statement);
        } finally {
            releaseConnection(connection);
        }
        return i;
    }

    @Override
    public int delete(SQL sql) throws SQLException {
        if (sql instanceof EmptySQL)
            return 0;
        SQLInfo info = compileSql(sql);
        printSql(info);
        Connection connection = getConnection();
        int i = 0;
        try {
            PreparedStatement statement = connection.prepareStatement(info.getSql());
            this.preparedParam(statement, info);
            i = statement.executeUpdate();
            if (sql.getBinds() != null) {
                for (BindSQL bindSQL : sql.getBinds()) {
                    i += delete(bindSQL.getSql());
                }
                return i;
            }
            if (logger.isDebugEnabled())
                logger.debug("<==     delete: {} rows", i);
            closeStatement(statement);
        } finally {
            releaseConnection(connection);
        }
        return i;
    }

    @Override
    public int insert(SQL sql) throws SQLException {
        return update(sql);
    }

    @Override
    public boolean tableExists(String tname) throws SQLException {
        Connection connection = getConnection();
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet resultSet = metaData.getTables(connection.getCatalog(), null, tname.toUpperCase(), null);
            if (resultSet.next())
                return true;
            else {
                resultSet = metaData.getTables(connection.getCatalog(), null, tname.toLowerCase(), null);
            }
            if (resultSet.next())
                return true;
        } finally {
            releaseConnection(connection);
        }
        return false;
    }

    /**
     * ?
     *
     * @param statement
     * @param info
     * @throws Exception
     */
    protected void preparedParam(PreparedStatement statement, SQLInfo info) throws SQLException {
        int index = 1;
        //?
        for (Object object : info.getParam()) {
            if (object instanceof Date)
                statement.setTimestamp(index++, new java.sql.Timestamp(((Date) object).getTime()));
            else if (object instanceof byte[]) {
                statement.setBlob(index++, new ByteArrayInputStream((byte[]) object));
            } else
                statement.setObject(index++, object);
        }
    }

    protected void printSql(SQLInfo info) {
        if (logger.isDebugEnabled()) {
            logger.debug("==>  Preparing: {}", info.getSql());
            if (info.getParam() != null && info.getParam().length > 0) {
                logger.debug("==> Parameters: {}", info.paramsString());
                String sim = info.getSql();
                Object[] param = info.getParam();
                for (int i = 0; i < param.length; i++) {
                    Object obj = param[i];
                    try {
                        sim = sim.replaceFirst("\\?", obj instanceof Number ? String.valueOf(obj)
                                : "'".concat(escapeExprSpecialWord(String.valueOf(obj))).concat("'"));
                    } catch (Exception e) {
                    }
                }
                logger.debug("==>  Simulated: {}", sim);
            }
        }
    }

    public static String escapeExprSpecialWord(String keyword) {
        if (!StringUtils.isNullOrEmpty(keyword)) {
            String[] fbsArr = { "\\", "$", "(", ")", "*", "+", ".", "[", "]", "?", "^", "{", "}", "|" };
            for (String key : fbsArr) {
                if (keyword.contains(key)) {
                    keyword = keyword.replace(key, "\\" + key);
                }
            }
        }
        return keyword;
    }

    public static class SQLInfo {
        /**
         * sql?
         */
        private String sql;

        /**
         * ?
         */
        private Object[] param;

        /**
         * ?
         */
        private String paramString;

        public String getSql() {
            return sql;
        }

        public void setSql(String sql) {
            this.sql = sql;
        }

        public Object[] getParam() {
            return param;
        }

        public void setParam(Object[] param) {
            this.param = param;
        }

        public String paramsString() {
            if (getParam() == null)
                return "";
            if (paramString == null) {
                StringBuilder builder = new StringBuilder();
                int i = 0;
                for (Object param : getParam()) {
                    if (i++ != 0)
                        builder.append(",");
                    builder.append(String.valueOf(param));
                    builder.append("(");
                    builder.append(param == null ? "null" : param.getClass().getSimpleName());
                    builder.append(")");
                }
                paramString = builder.toString();
            }
            return paramString;
        }

    }
}