List of usage examples for org.springframework.jdbc.core SqlParameter SqlParameter
public SqlParameter(String name, int sqlType)
From source file:dao.DirMemberAuthorQuery.java
/** * This constructor is called when the collabrum messages method makes a * call to query.execute(). After the query is executed, the result set is * returned. For each row in the result set, the mapRow method is called by * Spring. In the very first call to mapRow() for the first row in the result * set, we make a call to RSMD to get columnNames and cache * them to a local array in this object. This way, we can avoid multiple calls * to RSMD since, spring calls mapRow many times (one per row in result set). * *//*from w w w. ja v a 2 s . c o m*/ // dont use collmsgattr.rid as it may have null values and clash with rid of collmessages. DirMemberAuthorQuery(DataSource ds) { super(ds, "select c1.entryid from diradmin c1, diradmin c2 " + "where c1.directoryid=c2.directoryid and c1.ownerid=? " + "and c2.ownerid=? limit 1"); declareParameter(new SqlParameter("ownerid", Types.BIGINT)); declareParameter(new SqlParameter("ownerid", Types.BIGINT)); compile(); }
From source file:com.mec.DAO.Passport.UserDAO.java
public List<GrantedAuthority> getUserRoles(Integer userID) { SimpleJdbcCall jdbcCall = new SimpleJdbcCall(ds).withCatalogName("SqlSp") .withProcedureName("paRolesGetByIdUsuario").withoutProcedureColumnMetaDataAccess() .declareParameters(new SqlParameter("idAplicacion", Types.INTEGER)) .declareParameters(new SqlParameter("idUsuario", Types.INTEGER)) .declareParameters(new SqlOutParameter("ErrText", Types.VARCHAR)) .returningResultSet("items", new MyRowMapper()); SqlParameterSource in = new MapSqlParameterSource().addValue("idAplicacion", 1).addValue("idUsuario", userID);//w w w.j a v a2 s . c om return (List<GrantedAuthority>) jdbcCall.execute(in).entrySet().iterator().next().getValue(); }
From source file:org.metis.jdbc.WdsStoredProcedure.java
public WdsStoredProcedure(SqlStmnt stmt) throws Exception { super(stmt.getJdbcTemplate(), stmt.getStoredProcName()); setFunction(stmt.isFunction());/* www. j av a 2s .c o m*/ myStmt = stmt; // Parameters should be declared in the same order that // they are declared in the stored procedure. The one exception // are result sets, which must be defined first!! // // Here's something I found as to why - When you make any private // static class of StoreProcedure, then in its constructor you must // declare SqlReturnResultSet before you declare SqlParameter. // Otherwise you will not be able to find return data from // StoredProcedure execution. Still not sure what this means // and why its so. // for (SqlToken sqlToken : myStmt.getSortedKeyTokens()) { if (sqlToken.isRset()) { declareParameter(new SqlReturnResultSet(sqlToken.getKey(), myStmt)); } } // now do the other parameters // iterate through tokens in proper sequence; parameters must be // declared according to the sequence in which they appear in the // statement for (SqlToken sqlToken : myStmt.getSortedKeyTokens()) { // skip result sets if (sqlToken.isRset()) { continue; } switch (sqlToken.getMode()) { case IN: declareParameter(new SqlParameter(sqlToken.getKey(), sqlToken.getJdbcType().getType())); break; case OUT: // look for CURSOR types if (sqlToken.isCursor()) { // if it is a cursor then check to see if it is Oracle or // some other DBMS and set the type accrodingly int type = (myStmt.getMetisController().isOracle()) ? ORACLE_CURSOR : Types.OTHER; declareParameter(new SqlOutParameter(sqlToken.getKey(), type, myStmt)); } else { declareParameter(new SqlOutParameter(sqlToken.getKey(), sqlToken.getJdbcType().getType())); } break; case INOUT: // note: you can't have cursors as IN params - doesn't // make sense, so don't check for them when its an INOUT declareParameter(new SqlInOutParameter(sqlToken.getKey(), sqlToken.getJdbcType().getType())); break; default: throw new Exception("WdsStoredProcedure: this invalid mode was provided: " + sqlToken.getMode()); } } // specify whether this is a function super.setFunction(myStmt.isFunction()); // compile the statement compile(); }
From source file:om.edu.squ.squportal.portlet.dps.dao.db.DpsDbImpl.java
/** * /*from w w w .ja v a2 s . c o m*/ * method name : getHigherApprover * @param studentNo * @param formName * @param roleName * @param isSequenceRequired * @return * DpsDbImpl * return type : Approver * * purpose : Get higher approver (at intial and final case it's same approver) * * Date : Jul 16, 2017 11:59:33 AM */ public Approver getHigherApprover(String studentNo, String formName, String roleName, String isSequenceRequired) { String SP_APPROVER_NEXT = queryProps.getProperty(Constants.CONST_SP_APPROVER_NEXT); Map resultProc = null; Approver approver = new Approver(); simpleJdbcCallDps.withProcedureName(SP_APPROVER_NEXT); simpleJdbcCallDps.withoutProcedureColumnMetaDataAccess(); simpleJdbcCallDps.useInParameterNames(Constants.CONST_PARAM_NAME_STUDENT_NO, Constants.CONST_PARAM_NAME_FORM_NAME, Constants.CONST_PARAM_NAME_ROLE_NAME, Constants.CONST_PARAM_NAME_IS_SEQUENCE_REQUIRED); simpleJdbcCallDps.declareParameters(new SqlParameter(Constants.CONST_PARAM_NAME_STUDENT_NO, Types.VARCHAR), new SqlParameter(Constants.CONST_PARAM_NAME_FORM_NAME, Types.VARCHAR), new SqlParameter(Constants.CONST_PARAM_NAME_ROLE_NAME, Types.VARCHAR), new SqlParameter(Constants.CONST_PARAM_NAME_IS_SEQUENCE_REQUIRED, Types.VARCHAR), new SqlOutParameter(Constants.CONST_PARAM_NAME_APPROVER_NAME_ENG, Types.VARCHAR), new SqlOutParameter(Constants.CONST_PARAM_NAME_APPROVER_NAME_AR, Types.VARCHAR), new SqlOutParameter(Constants.CONST_PARAM_NAME_APPROVER_EMAIL, Types.VARCHAR), new SqlOutParameter(Constants.CONST_PARAM_NAME_APPROVER_PHONE, Types.VARCHAR), new SqlOutParameter(Constants.CONST_PARAM_NAME_ROLE_NAME_ENG, Types.VARCHAR), new SqlOutParameter(Constants.CONST_PARAM_NAME_ROLE_NAME_AR, Types.VARCHAR), new SqlOutParameter(Constants.CONST_PARAM_NAME_IS_HIGHER_APPROVER, Types.VARCHAR)); Map<String, String> paramIn = new HashMap<String, String>(); paramIn.put(Constants.CONST_PARAM_NAME_STUDENT_NO, studentNo); paramIn.put(Constants.CONST_PARAM_NAME_FORM_NAME, formName); paramIn.put(Constants.CONST_PARAM_NAME_ROLE_NAME, roleName); paramIn.put(Constants.CONST_PARAM_NAME_IS_SEQUENCE_REQUIRED, isSequenceRequired); resultProc = simpleJdbcCallDps.execute(paramIn); approver.setNameEng((String) resultProc.get(Constants.CONST_PARAM_NAME_APPROVER_NAME_ENG)); approver.setNameAr((String) resultProc.get(Constants.CONST_PARAM_NAME_APPROVER_NAME_AR)); approver.setEmail((String) resultProc.get(Constants.CONST_PARAM_NAME_APPROVER_EMAIL)); approver.setRoleNameEng((String) resultProc.get(Constants.CONST_PARAM_NAME_ROLE_NAME_ENG)); approver.setRoleNameAr((String) resultProc.get(Constants.CONST_PARAM_NAME_ROLE_NAME_AR)); if (((String) resultProc.get(Constants.CONST_PARAM_NAME_IS_HIGHER_APPROVER)).equals(Constants.CONST_YES)) { approver.setHigherSequence(true); } else { approver.setHigherSequence(false); } resultProc = null; return approver; }
From source file:om.edu.squ.squportal.portlet.dps.registration.dropw.db.DropWDBImpl.java
/** * /*from w w w. jav a2 s .c o m*/ * method name : setDropWCourseWithdrawProc * @param dropWDTO * @return * DropWDBImpl * return type : int * * purpose : * * Date : May 7, 2017 10:36:17 AM */ @Transactional private Map setDropWCourseWithdrawProc(DropWDTO dropWDTO) throws NotSuccessFulDBUpdate { Map resultProc = null; simpleJdbcCallDpsDropW.withProcedureName(Constants.CONST_PROC_DROPW_WITHDRAW_COURSE); simpleJdbcCallDpsDropW.withoutProcedureColumnMetaDataAccess(); simpleJdbcCallDpsDropW.useInParameterNames(Constants.CONST_PROC_COL_NAME_P_STDNO, Constants.CONST_PROC_COL_NAME_P_SECTCD, Constants.CONST_PROC_COL_NAME_P_SECTNO, Constants.CONST_PROC_COL_NAME_P_USER); simpleJdbcCallDpsDropW.declareParameters( new SqlParameter(Constants.CONST_PROC_COL_NAME_P_STDNO, Types.NUMERIC), new SqlParameter(Constants.CONST_PROC_COL_NAME_P_SECTCD, Types.NUMERIC), new SqlParameter(Constants.CONST_PROC_COL_NAME_P_SECTNO, Types.NUMERIC), new SqlParameter(Constants.CONST_PROC_COL_NAME_P_USER, Types.VARCHAR) ); Map<String, Object> paramMap = new HashMap<String, Object>(); paramMap.put(Constants.CONST_PROC_COL_NAME_P_STDNO, dropWDTO.getStudentNo()); paramMap.put(Constants.CONST_PROC_COL_NAME_P_SECTCD, dropWDTO.getSectCode()); paramMap.put(Constants.CONST_PROC_COL_NAME_P_SECTNO, dropWDTO.getSectionNo()); paramMap.put(Constants.CONST_PROC_COL_NAME_P_USER, dropWDTO.getUserName()); try { resultProc = simpleJdbcCallDpsDropW.execute(paramMap); } catch (BadSqlGrammarException badGrException) { logger.error("Might be a grammatical issue in stored procedure"); throw new NotSuccessFulDBUpdate(badGrException.getMessage()); } catch (UncategorizedSQLException exception) { logger.error("Course drop not successful for student no : {}, course no {} . Details : {} - {}", dropWDTO.getStudentNo(), dropWDTO.getCourseNo(), exception.getSQLException().getErrorCode(), exception.getSQLException().getMessage()); throw new NotSuccessFulDBUpdate(exception.getMessage()); } return resultProc; }
From source file:architecture.ee.jdbc.sqlquery.factory.impl.SqlQueryImpl.java
public Object call(String statement, Object... parameters) { BoundSql sql = getBoundSql(statement); List<SqlParameter> declaredParameters = new ArrayList<SqlParameter>(); Map<String, Object> paramsToUse = new HashMap<String, Object>(); // ?? ? INPUT OUTPU ? . for (ParameterMapping mapping : sql.getParameterMappings()) { mapping.getProperty();/* w w w . j av a 2 s .c om*/ mapping.getJdbcType(); mapping.getMode(); if (mapping.getMode() == ParameterMapping.Mode.IN) { SqlParameter input = new SqlParameter(mapping.getProperty(), mapping.getJdbcType().ordinal()); declaredParameters.add(input); paramsToUse.put(mapping.getProperty(), parameters[mapping.getIndex() - 1]); } else if (mapping.getMode() == ParameterMapping.Mode.OUT) { SqlOutParameter output = new SqlOutParameter(mapping.getProperty(), mapping.getJdbcType().ordinal()); declaredParameters.add(output); } } CallableStatementCreatorFactory callableStatementFactory = new CallableStatementCreatorFactory(sql.getSql(), declaredParameters); return jdbcTemplate.call(callableStatementFactory.newCallableStatementCreator(paramsToUse), declaredParameters); }
From source file:org.easyrec.store.dao.plugin.impl.LogEntryDAOMysqlImpl.java
protected LogEntryDAOMysqlImpl(DataSource dataSource, SqlScriptService sqlScriptService, PluginRegistry pluginRegistry) { super(sqlScriptService); setDataSource(dataSource);/*from w w w . j a va 2 s .co m*/ startEntry = new SqlUpdate(dataSource, "INSERT INTO plugin_log(tenantId, pluginId, pluginVersion, startDate, assocTypeId, " + "configuration) VALUES (?, ?, ?, ?, ?, ?)", new int[] { Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR, Types.BLOB }); startEntry.compile(); endEntry = new SqlUpdate(dataSource, "INSERT INTO plugin_log(tenantId, pluginId, pluginVersion, startDate, assocTypeId, configuration, " + "endDate, statistics) VALUES (?, ?, ?, ?, ?, ?, ?, ?) " + "ON DUPLICATE KEY UPDATE endDate = ?, statistics = ?", new int[] { Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.VARCHAR, Types.BLOB, Types.TIMESTAMP, Types.BLOB, Types.TIMESTAMP, Types.BLOB }); endEntry.compile(); endAllEntries = new SqlUpdate(dataSource, "UPDATE plugin_log SET endDate = ?, statistics = ? WHERE endDate IS NULL", new int[] { Types.TIMESTAMP, Types.BLOB }); endAllEntries.compile(); getRunningTenants = new MappingSqlQuery<Integer>(dataSource, "SELECT tenantId FROM plugin_log WHERE endDate IS NULL") { @Override protected Integer mapRow(ResultSet rs, int rowNum) throws SQLException { return rs.getInt("tenantId"); } }; getRunningTenants.compile(); getLogEntries = new GetLogEntriesStatement(dataSource, pluginRegistry, "SELECT * FROM plugin_log ORDER BY endDate DESC, id DESC LIMIT ?, ?"); getLogEntries.declareParameter(new SqlParameter("offset", Types.INTEGER)); getLogEntries.declareParameter(new SqlParameter("limit", Types.INTEGER)); getLogEntries.compile(); getLogEntriesForTenant = new GetLogEntriesStatement(dataSource, pluginRegistry, "SELECT * FROM plugin_log WHERE tenantId = ? ORDER BY startDate DESC, id DESC LIMIT ?, ?"); getLogEntriesForTenant.declareParameter(new SqlParameter("tenantId", Types.INTEGER)); getLogEntriesForTenant.declareParameter(new SqlParameter("offset", Types.INTEGER)); getLogEntriesForTenant.declareParameter(new SqlParameter("limit", Types.INTEGER)); getLogEntriesForTenant.compile(); getLogEntriesWithAssocType = new GetLogEntriesStatement(dataSource, pluginRegistry, "SELECT * FROM plugin_log WHERE assocTypeId = ? ORDER BY startDate DESC, id DESC LIMIT ?, ?"); getLogEntriesWithAssocType.declareParameter(new SqlParameter("assocTypeId", Types.INTEGER)); getLogEntriesWithAssocType.declareParameter(new SqlParameter("offset", Types.INTEGER)); getLogEntriesWithAssocType.declareParameter(new SqlParameter("limit", Types.INTEGER)); getLogEntriesWithAssocType.compile(); getLogEntriesForTenantWithAssocType = new GetLogEntriesStatement(dataSource, pluginRegistry, "SELECT * FROM plugin_log WHERE tenantId = ? AND assocTypeId = ? ORDER BY startDate DESC, id DESC LIMIT ?, ?"); getLogEntriesForTenantWithAssocType.declareParameter(new SqlParameter("tenantId", Types.INTEGER)); getLogEntriesForTenantWithAssocType.declareParameter(new SqlParameter("assocTypeId", Types.INTEGER)); getLogEntriesForTenantWithAssocType.declareParameter(new SqlParameter("offset", Types.INTEGER)); getLogEntriesForTenantWithAssocType.declareParameter(new SqlParameter("limit", Types.INTEGER)); getLogEntriesForTenantWithAssocType.compile(); getNumberOfLogEntries = new SqlFunction<Integer>(dataSource, "SELECT count(*) AS entry_count FROM plugin_log"); getNumberOfLogEntries.compile(); getNumberOfLogEntriesForTenant = new SqlFunction<Integer>(dataSource, "SELECT count(*) AS entry_count FROM plugin_log WHERE tenantId = ?"); getNumberOfLogEntriesForTenant.setResultType(Integer.class); getNumberOfLogEntriesForTenant.declareParameter(new SqlParameter("tenantId", Types.INTEGER)); getNumberOfLogEntriesForTenant.compile(); deleteLogEntries = new SqlUpdate(dataSource, "TRUNCATE plugin_log"); deleteLogEntries.compile(); getComputationDurationForDate = new SqlFunction<Integer>(dataSource, "SELECT sum(timestampdiff(second, startDate, endDate)) AS sum_seconds FROM plugin_log WHERE endDate BETWEEN ? AND ?"); getComputationDurationForDate.setResultType(Integer.class); getComputationDurationForDate.declareParameter(new SqlParameter("start", Types.DATE)); getComputationDurationForDate.declareParameter(new SqlParameter("end", Types.DATE)); getComputationDurationForDate.compile(); deleteLogEntryStatement = new SqlUpdate(dataSource, "DELETE FROM plugin_log WHERE tenantId = ? AND pluginId = ? AND pluginVersion = ? AND startDate = ? AND assocTypeId = ?"); deleteLogEntryStatement.declareParameter(new SqlParameter("tenantId", Types.INTEGER)); deleteLogEntryStatement.declareParameter(new SqlParameter("pluginId", Types.VARCHAR)); deleteLogEntryStatement.declareParameter(new SqlParameter("pluginVersion", Types.VARCHAR)); deleteLogEntryStatement.declareParameter(new SqlParameter("startDate", Types.TIMESTAMP)); deleteLogEntryStatement.declareParameter(new SqlParameter("assocTypeId", Types.VARCHAR)); deleteLogEntryStatement.compile(); }
From source file:org.springframework.jdbc.core.metadata.GenericCallMetaDataProvider.java
@Override public SqlParameter createDefaultInParameter(String parameterName, CallParameterMetaData meta) { return new SqlParameter(parameterName, meta.getSqlType()); }
From source file:org.springframework.jdbc.core.simple.SimpleJdbcCallTests.java
public void testAddInvoiceProcWithoutMetaDataUsingMapParamSource() throws Exception { final int amount = 1103; final int custid = 3; initializeAddInvoiceWithoutMetaData(false); replay();//ww w .j ava2 s . c o m SimpleJdbcCall adder = new SimpleJdbcCall(mockDataSource).withProcedureName("add_invoice"); adder.declareParameters(new SqlParameter("amount", Types.INTEGER), new SqlParameter("custid", Types.INTEGER), new SqlOutParameter("newid", Types.INTEGER)); Number newId = adder.executeObject(Number.class, new MapSqlParameterSource().addValue("amount", amount).addValue("custid", custid)); assertEquals(4, newId.intValue()); }
From source file:org.springframework.jdbc.core.simple.SimpleJdbcCallTests.java
public void testAddInvoiceProcWithoutMetaDataUsingArrayParams() throws Exception { final int amount = 1103; final int custid = 3; initializeAddInvoiceWithoutMetaData(false); replay();//from www. jav a 2 s. c o m SimpleJdbcCall adder = new SimpleJdbcCall(mockDataSource).withProcedureName("add_invoice"); adder.declareParameters(new SqlParameter("amount", Types.INTEGER), new SqlParameter("custid", Types.INTEGER), new SqlOutParameter("newid", Types.INTEGER)); Number newId = adder.executeObject(Number.class, amount, custid); assertEquals(4, newId.intValue()); }