Start line:  
End line:  

Snippet Preview

Snippet HTML Code

Stack Overflow Questions
  package org.sqlproc.engine.spring;
  
  import java.sql.ResultSet;
  import java.sql.Statement;
 import java.util.List;
 import java.util.Map;
 
The Spring stack implementation of the SQL Engine query contract. In fact it's an adapter the internal Spring stuff.

For more info please see the Tutorials.

Author(s):
Vladimir Hudec
 
 public class SpringQuery implements SqlQuery {

    
The internal slf4j logger.
 
     final Logger logger = LoggerFactory.getLogger(getClass());

    
The Spring JdbcTemplate, the central class for all Spring database operations.
 
     JdbcTemplate jdbcTemplate;
    
The SQL query/statement command.
 
     String queryString;
    
The collection of all scalars (output values declarations).
 
     List<Stringscalars = new ArrayList<String>();
    
The collection of all scalars types.
 
     Map<StringObjectscalarTypes = new HashMap<StringObject>();
    
The collection of all parameters (input value declarations).
 
     List<Stringparameters = new ArrayList<String>();
    
The collection of all parameters values.
 
     Map<StringObjectparameterValues = new HashMap<StringObject>();
    
The collection of all parameters types.
 
     Map<StringObjectparameterTypes = new HashMap<StringObject>();
    
The collection of all parameters types for output values.
 
     Map<StringObjectparameterOutValueTypes = new HashMap<StringObject>();
    
The collection of all parameters output value setters.
 
The collection of all parameters, which have to be picked-up.
 
The collection of all (auto-generated) identities.
 
     List<Stringidentities = new ArrayList<String>();
    
The collection of all identities setters.
The collection of all identities types.
    Map<StringObjectidentityTypes = new HashMap<StringObject>();
    
A timeout for the underlying query.
    Integer timeout;
    
The first row to retrieve.
    Integer firstResult;
    
The maximum number of rows to retrieve.
    Integer maxResults;
    
The SQL output is sorted.
    boolean ordered;
    
The failed SQL command should be logged.
    boolean logError;

    
Creates a new instance of this adapter.

Parameters:
jdbcTemplate the Spring JdbcTemplate instance
queryString the SQL query/statement command
    public SpringQuery(JdbcTemplate jdbcTemplateString queryString) {
        this. = jdbcTemplate;
        this. = queryString;
        // logger.info("query: " + queryString);
    }

    
    @Override
    public Object getQuery() {
        return ;
    }

    
    @Override
    public SqlQuery setTimeout(int timeout) {
        this. = timeout;
        return this;
    }

    
    @Override
    public SqlQuery setFirstResult(int firstResult) {
        this. = firstResult;
        return this;
    }

    
    @Override
    public SqlQuery setMaxResults(int maxResults) {
        this. = maxResults;
        return this;
    }

    
    @Override
    public SqlQuery setOrdered(boolean ordered) {
        this. = ordered;
        return this;
    }

    
    @Override
    public List list() throws SqlProcessorException {
        final StringBuilder queryResult = ( != null) ? new StringBuilder(.length() + 100) : null;
        final SqlFromToPlugin.LimitType limitType = ( != null) ? SqlProcessContext.getPluginFactory()
                .getSqlFromToPlugin().limitQuery(queryResult) : null;
        final String query = limitType != null ? queryResult.toString() : ;
        if (.isDebugEnabled()) {
            .debug("list, query=" + query);
        }
        PreparedStatementCreator psc = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection conthrows SQLException {
                PreparedStatement ps = con.prepareStatement(query);
                if ( != null)
                    ps.setQueryTimeout();
                return ps;
            }
        };
        PreparedStatementSetter pss = new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement psthrows SQLException {
                setParameters(pslimitType, 1);
            }
        };
        ResultSetExtractor<Listrse = new ResultSetExtractor<List>() {
            @Override
            public List extractData(ResultSet rsthrows SQLExceptionDataAccessException {
                return getResults(rs);
            }
        };
        try {
            List list = .query(pscpssrse);
            if (.isDebugEnabled()) {
                .debug("list, number of returned rows=" + ((list != null) ? list.size() : "null"));
            }
            return list;
        } catch (DataAccessException ex) {
            throw newSqlProcessorException(exquery);
        }
    }

    
    @Override
    public Object unique() throws SqlProcessorException {
        List list = list();
        int size = list.size();
        if (size == 0)
            return null;
        Object first = list.get(0);
        for (int i = 1; i < sizei++) {
            if (list.get(i) != first) {
                throw new SqlProcessorException("There's no unique result, the number of returned rows is "
                        + list.size());
            }
        }
        return first;
    }

    
    @Override
    public int update() throws SqlProcessorException {
        if (.isDebugEnabled()) {
            .debug("update, query=" + );
        }
        PreparedStatementCreator psc = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection conthrows SQLException {
                PreparedStatement ps;
                if (isSetJDBCIdentity()) {
                    ps = con.prepareStatement(.);
                } else {
                    ps = con.prepareStatement();
                }
                if ( != null)
                    ps.setQueryTimeout();
                return ps;
            }
        };
        PreparedStatementSetter pss = new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement psthrows SQLException {
                setParameters(psnull, 1);
            }
        };
        try {
            int updated = 0;
            if (!.isEmpty()) {
                String identityName = .get(0);
                if (isSetJDBCIdentity()) {
                    updated = updateWithGenKeys(pscpssidentityName);
                } else {
                    updated = updateWithoutGenKeys(pscpss);
                    doIdentitySelect(identityName);
                }
            } else {
                updated = updateWithoutGenKeys(pscpss);
            }
            if (.isDebugEnabled()) {
                .debug("update, number of updated rows=" + updated);
            }
            return updated;
        } catch (DataAccessException ex) {
            throw newSqlProcessorException(ex);
        }
    }
    private boolean isSetJDBCIdentity() {
        for (String identityName : ) {
            IdentitySetter identitySetter = .get(identityName);
            if (identitySetter.getIdentitySelect().equals(.)) {
                return true;
            }
        }
        return false;
    }

    
Runs the select to obtain the value of auto-generated identity.

Parameters:
identityName the identity name from the META SQL statement
    private void doIdentitySelect(final String identityName) {
        final IdentitySetter identitySetter = .get(identityName);
        final Object identityType = .get(identityName);
        if (.isDebugEnabled()) {
            .debug("identity, name=" + identityName + ", select=" + identitySetter.getIdentitySelect()
                    + ", identityType=" + identityType);
        }
        PreparedStatementCreator psc = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection conthrows SQLException {
                PreparedStatement ps = con.prepareStatement(identitySetter.getIdentitySelect());
                return ps;
            }
        };
        ResultSetExtractor<Objectrse = new ResultSetExtractor<Object>() {
            @Override
            public Object extractData(ResultSet rsthrows SQLExceptionDataAccessException {
                Object identityValue = null;
                while (rs.next()) {
                    if (identityType != null && identityType instanceof JdbcSqlType) {
                        identityValue = ((JdbcSqlTypeidentityType).get(rsidentityName);
                    } else {
                        identityValue = rs.getObject(1);
                    }
                    if (rs.wasNull())
                        identityValue = null;
                }
                return identityValue;
            }
        };
        try {
            Object identityValue = .query(pscnullrse);
            identitySetter.setIdentity(identityValue);
            if (.isDebugEnabled()) {
                .debug("identity, result=" + identityValue);
            }
        } catch (DataAccessException ex) {
            throw new SqlProcessorException("Identity select failed."ex);
        }
    }

    
Retrieves the value of auto-generated identity from executed prepared statement.

Parameters:
identityName the identity name from the META SQL statement
statement statement to retrieve auto-generated keys from
    private void getGeneratedKeys(String identityNameStatement statement) {
        IdentitySetter identitySetter = .get(identityName);
        Object identityType = .get(identityName);
        if (.isDebugEnabled()) {
            .debug("identity, name=" + identityName + ", getGeneratedKeys(), identityType=" + identityType);
        }
        ResultSet rs = null;
        Object identityValue = null;
        try {
            rs = statement.getGeneratedKeys();
            while (rs.next()) {
                if (identityType != null && identityType instanceof JdbcSqlType) {
                    identityValue = ((JdbcSqlTypeidentityType).get(rsidentityName);
                } else {
                    identityValue = rs.getObject(1);
                }
                if (rs.wasNull())
                    identityValue = null;
            }
            identitySetter.setIdentity(identityValue);
            if (.isDebugEnabled()) {
                .debug("identity, result=" + identityValue);
            }
        } catch (SQLException he) {
            throw new SqlProcessorException("Statement.getGeneratedKeys() failed."he);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }

    
This is a workaround, as this method is not visible in JdbcTemplate.

It executes the prepared SQL statement and retrieves the values of generated identities from the statement. The generated identities cannot be obtained later because the ResultSet java.sql.Statement.getGeneratedKeys() is closed after this method finishes.

    protected int updateWithGenKeys(final PreparedStatementCreator pscfinal PreparedStatementSetter pss,
            final String identityNamethrows DataAccessException {
        .debug("Executing prepared SQL update with generated keys retrieval");
        return .execute(pscnew PreparedStatementCallback<Integer>() {
            public Integer doInPreparedStatement(PreparedStatement psthrows SQLException {
                try {
                    if (pss != null) {
                        pss.setValues(ps);
                    }
                    int rows = ps.executeUpdate();
                    if (.isDebugEnabled()) {
                        .debug("SQL update affected " + rows + " rows");
                    }
                    if (identityName != null) {
                        getGeneratedKeys(identityNameps);
                    }
                    return rows;
                } finally {
                    if (pss instanceof ParameterDisposer) {
                        ((ParameterDisposerpss).cleanupParameters();
                    }
                }
            }
        });
    }

    
This is a workaround, as this method is not visible in JdbcTemplate.
    protected int updateWithoutGenKeys(final PreparedStatementCreator pscfinal PreparedStatementSetter pss)
            throws DataAccessException {
        .debug("Executing prepared SQL update");
        return .execute(pscnew PreparedStatementCallback<Integer>() {
            public Integer doInPreparedStatement(PreparedStatement psthrows SQLException {
                try {
                    if (pss != null) {
                        pss.setValues(ps);
                    }
                    int rows = ps.executeUpdate();
                    if (.isDebugEnabled()) {
                        .debug("SQL update affected " + rows + " rows");
                    }
                    return rows;
                } finally {
                    if (pss instanceof ParameterDisposer) {
                        ((ParameterDisposerpss).cleanupParameters();
                    }
                }
            }
        });
    }
    static final Pattern CALL = Pattern.compile("\\s*\\{?\\s*(\\?)?\\s*=?\\s*call\\s*(.*?)\\s*}?\\s*");

    
    @Override
    public List callList() throws SqlProcessorException {
        if (.isDebugEnabled()) {
            .debug("callList, query=" + );
        }
        CallableStatementCreator psc = new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection conthrows SQLException {
                Matcher matcher = .matcher();
                if (!matcher.matches())
                    throw new SqlProcessorException("'" +  + "' isn't the correct call statement");
                String query = (matcher.group(1) != null) ? "{? = call " + matcher.group(2) + "}" : "{ call "
                        + matcher.group(2) + "}";
                CallableStatement cs = con.prepareCall(query);
                if ( != null)
                    cs.setQueryTimeout();
                return cs;
            }
        };
            public List doInCallableStatement(CallableStatement csthrows SQLException {
                ResultSet rs = null;
                List list = null;
                try {
                    setParameters(csnull, 1);
                    boolean hasResultSet = cs.execute();
                    if (hasResultSet || cs.getMoreResults()) {
                        rs = cs.getResultSet();
                        ResultSet rsToUse = rs;
                        if (.getNativeJdbcExtractor() != null) {
                            rsToUse = .getNativeJdbcExtractor().getNativeResultSet(rs);
                        }
                        list = getResults(rsToUse);
                        getParameters(csfalse);
                    } else {
                        rs = (ResultSetgetParameters(cstrue);
                        ResultSet rsToUse = rs;
                        if (.getNativeJdbcExtractor() != null) {
                            rsToUse = .getNativeJdbcExtractor().getNativeResultSet(rs);
                        }
                        list = getResults(rsToUse);
                    }
                } finally {
                    JdbcUtils.closeResultSet(rs);
                }
                return list;
            }
        };
        try {
            List list = .execute(psccsc);
            if (.isDebugEnabled()) {
                .debug("callList, number of returned rows=" + ((list != null) ? list.size() : "null"));
            }
            return list;
        } catch (DataAccessException ex) {
            throw newSqlProcessorException(ex);
        }
    }

    
    @Override
    public Object callUnique() throws SqlProcessorException {
        List list = callList();
        int size = list.size();
        if (size == 0)
            return null;
        Object first = list.get(0);
        for (int i = 1; i < sizei++) {
            if (list.get(i) != first) {
                throw new SqlProcessorException("There's no unique result, the number of returned rows is "
                        + list.size());
            }
        }
        return first;
    }

    
    @Override
    public int callUpdate() throws SqlProcessorException {
        if (.isDebugEnabled()) {
            .debug("callUpdate, query=" + );
        }
        CallableStatementCreator psc = new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection conthrows SQLException {
                Matcher matcher = .matcher();
                if (!matcher.matches())
                    throw new SqlProcessorException("'" +  + "' isn't the correct call statement");
                String query = (matcher.group(1) != null) ? "{? = call " + matcher.group(2) + "}" : "{ call "
                        + matcher.group(2) + "}";
                CallableStatement cs = con.prepareCall(query);
                if ( != null)
                    cs.setQueryTimeout();
                return cs;
            }
        };
            public Integer doInCallableStatement(CallableStatement csthrows SQLException {
                setParameters(csnull, 1);
                cs.execute();
                Integer updated = cs.getUpdateCount();
                getParameters(csfalse);
                return updated;
            }
        };
        try {
            Integer updated = .execute(psccsc);
            if (.isDebugEnabled()) {
                .debug("callUpdate, number of updated rows=" + updated);
            }
            return updated;
        } catch (DataAccessException ex) {
            throw newSqlProcessorException(ex);
        }
    }

    
    @Override
    public Object callFunction() throws SqlProcessorException {
        if (.isDebugEnabled()) {
            .debug("callList, query=" + );
        }
        CallableStatementCreator psc = new CallableStatementCreator() {
            @Override
            public CallableStatement createCallableStatement(Connection conthrows SQLException {
                Matcher matcher = .matcher();
                if (!matcher.matches())
                    throw new SqlProcessorException("'" +  + "' isn't the correct call statement");
                String query = (matcher.group(1) != null) ? "{? = call " + matcher.group(2) + "}" : "{ call "
                        + matcher.group(2) + "}";
                CallableStatement cs = con.prepareCall(query);
                if ( != null)
                    cs.setQueryTimeout();
                return cs;
            }
        };
            public Object doInCallableStatement(CallableStatement csthrows SQLException {
                ResultSet rs = null;
                List list = null;
                Object result = null;
                try {
                    setParameters(csnull, 1);
                    boolean hasResultSet = cs.execute();
                    if (hasResultSet) {
                        rs = cs.getResultSet();
                        ResultSet rsToUse = rs;
                        if (.getNativeJdbcExtractor() != null) {
                            rsToUse = .getNativeJdbcExtractor().getNativeResultSet(rs);
                        }
                        list = getResults(rsToUse);
                        if (list != null && !list.isEmpty())
                            result = list.get(0);
                        getParameters(csfalse);
                    } else {
                        result = getParameters(cstrue);
                    }
                } finally {
                    JdbcUtils.closeResultSet(rs);
                }
                return result;
            }
        };
        try {
            Object result = .execute(psccsc);
            if (.isDebugEnabled()) {
                .debug("callFunction, result=" + result);
            }
            return result;
        } catch (DataAccessException ex) {
            throw newSqlProcessorException(ex);
        }
    }

    
    @Override
    public SqlQuery addScalar(String columnAlias) {
        .add(columnAlias);
        return this;
    }

    
    @Override
    public SqlQuery addScalar(String columnAliasObject type) {
        .add(columnAlias);
        .put(columnAliastype);
        return this;
    }

    
    @Override
    public SqlQuery setParameter(String nameObject valthrows SqlProcessorException {
        .add(name);
        .put(nameval);
        return this;
    }

    
    @Override
    public SqlQuery setParameter(String nameObject valObject typethrows SqlProcessorException {
        if (val != null && val instanceof IdentitySetter) {
            .add(name);
            .put(name, (IdentitySetterval);
            .put(nametype);
        } else if (val != null && val instanceof OutValueSetter) {
            if (!.containsKey(name)) {
                .add(name);
                .put(nametype);
            }
            .put(nametype);
            .put(name, (OutValueSetterval);
        } else {
            .add(name);
            .put(nameval);
            .put(nametype);
        }
        return this;
    }

    
    @Override
    public SqlQuery setParameterList(String nameObject[] valsthrows SqlProcessorException {
        throw new UnsupportedOperationException();
    }

    
    @Override
    public SqlQuery setParameterList(String nameObject[] valsObject typethrows SqlProcessorException {
        throw new UnsupportedOperationException();
    }

    
Sets the value of the designated parameters.

Parameters:
ps an instance of PreparedStatement
limitType the limit type to restrict the number of rows in the result set
start the index of the first parameter to bind to prepared statement
Throws:
java.sql.SQLException if a database access error occurs or this method is called on a closed PreparedStatement
    protected void setParameters(PreparedStatement psSqlFromToPlugin.LimitType limitTypeint start)
            throws SQLException {
        int ix = start;
        ix = setLimits(pslimitTypeixfalse);
        for (int i = 0, n = .size(); i < ni++) {
            String name = .get(i);
            Object type = .get(name);
            if (.containsKey(name)) {
                Object value = .get(name);
                if (type != null) {
                    if (type instanceof JdbcSqlType) {
                        ((JdbcSqlTypetype).set(psix + ivalue);
                    } else if (value == null) {
                        ps.setNull(ix + i, (Integertype);
                    } else {
                        ps.setObject(ix + ivalue, (Integertype);
                    }
                } else {
                    ps.setObject(ix + ivalue);
                }
            }
            if (.containsKey(name)) {
                CallableStatement cs = (CallableStatementps;
                if (type != null) {
                    if (type instanceof SqlProviderType) {
                        cs.registerOutParameter(ix + i, (Integer) ((SqlProviderTypetype).getProviderSqlNullType());
                    } else {
                        cs.registerOutParameter(ix + i, (Integertype);
                    }
                } else {
                    throw new SqlProcessorException("OUT parameter type for callable statement is null");
                }
                .put(iix + i);
            }
        }
        ix = setLimits(pslimitTypeix + .size(), true);
    }

    
Sets the limit related parameters.

Parameters:
ps an instance of PreparedStatement
limitType the limit type to restrict the number of rows in the result set
ix a column index
afterSql an indicator it's done after the main SQL statement execution
Returns:
the updated column index
Throws:
java.sql.SQLException if a database access error occurs or this method is called on a closed PreparedStatement
    protected int setLimits(PreparedStatement psSqlFromToPlugin.LimitType limitTypeint ixboolean afterSql)
            throws SQLException {
        if (limitType == null)
            return ix;
        if (afterSql && !limitType.afterSql)
            return ix;
        if (!afterSql && limitType.afterSql)
            return ix;
        if (limitType.maxBeforeFirst) {
            if (limitType.rowidBasedMax && limitType.alsoFirst)
                ps.setInt(ix++,  + );
            else
                ps.setInt(ix++, );
        }
        if (limitType.alsoFirst) {
            if (limitType.zeroBasedFirst)
                ps.setInt(ix++, );
            else
                ps.setInt(ix++, );
        }
        if (!limitType.maxBeforeFirst) {
            if (limitType.rowidBasedMax && limitType.alsoFirst)
                ps.setInt(ix++,  + );
            else
                ps.setInt(ix++, );
        }
        return ix;
    }

    
Gets the value of the designated OUT parameters.

Parameters:
cs an instance of CallableStatement
Throws:
java.sql.SQLException if a database access error occurs or this method is called on a closed CallableStatement
    protected Object getParameters(CallableStatement csboolean isFunctionthrows SQLException {
        Object result = null;
        boolean resultInited = false;
        for (Iterator<Integeriter = .keySet().iterator(); iter.hasNext();) {
            int i = iter.next();
            int ix = .get(i);
            String name = .get(i);
            Object type = .get(name);
            if (type == null)
                type = .get(name);
            OutValueSetter outValueSetter = .get(name);
            Object outValue = null;
            if (type != null && type instanceof JdbcSqlType) {
                outValue = ((JdbcSqlTypetype).get(csix);
            } else {
                outValue = cs.getObject(ix);
            }
            outValueSetter.setOutValue(outValue);
            if (!resultInited) {
                result = outValue;
                resultInited = true;
            }
        }
        return result;
    }

    
Gets the value of the designated columns as the objects in the Java programming language.

Parameters:
rs an instance of ResultSet
Returns:
the result list
Throws:
java.sql.SQLException if a database access error occurs or this method is called on a closed ResultSet
    protected List getResults(ResultSet rsthrows SQLException {
        List result = new ArrayList();
        if (rs == null)
            return result;
        while (rs.next()) {
            List<Objectrow = new ArrayList<Object>();
            for (int i = 0, n = .size(); i < ni++) {
                String name = .get(i);
                Object type = .get(name);
                Object value = null;
                if (type != null && type instanceof JdbcSqlType) {
                    value = ((JdbcSqlTypetype).get(rsname);
                } else {
                    value = rs.getObject(name);
                }
                if (rs.wasNull())
                    value = null;
                row.add(value);
            }
            Object[] oo = row.toArray();
            if (oo.length == 1)
                result.add(oo[0]);
            else
                result.add(oo);
        }
        return result;
    }

    
    @Override
    public int[] executeBatch(String[] statementsthrows SqlProcessorException {
        try {
            int[] result = .batchUpdate(statements);
            if (.isDebugEnabled()) {
                .debug("executeBatch, result " + SqlUtils.asList(result));
            }
            return result;
        } catch (DataAccessException ex) {
            throw new SqlProcessorException(ex);
        }
    }
        if () {
            .error("Failed SQL command '" + query + "': " + ex.getMessage());
            return new SqlProcessorException(ex);
        } else {
            return new SqlProcessorException(exquery);
        }
    }

    
Sets an indicator the failed SQL command should be logged

Parameters:
logError an indicator the failed SQL command should be logged
    public void setLogError(boolean logError) {
        this. = logError;
    }
New to GrepCode? Check out our FAQ X