List of usage examples for org.springframework.jdbc.object SqlUpdate setReturnGeneratedKeys
public void setReturnGeneratedKeys(boolean returnGeneratedKeys)
From source file:org.agnitas.dao.impl.ImportProfileDaoImpl.java
public int insertImportProfile(ImportProfile importProfile) { int profileId; if (AgnUtils.isOracleDB()) { logSqlStatement(logger, SELECT_NEXT_PROFILEID); profileId = getSimpleJdbcTemplate().queryForInt(SELECT_NEXT_PROFILEID); logSqlStatement(logger, INSERT_ORACLE); getSimpleJdbcTemplate().update(INSERT_ORACLE, profileId, importProfile.getCompanyId(), importProfile.getAdminId(), importProfile.getName(), importProfile.getSeparator(), importProfile.getTextRecognitionChar(), importProfile.getCharset(), importProfile.getDateFormat(), importProfile.getImportMode(), importProfile.getNullValuesAction(), importProfile.getKeyColumn(), ImportUtils.getBooleanAsInt(importProfile.getExtendedEmailCheck()), importProfile.getMailForReport(), importProfile.getCheckForDuplicates(), importProfile.getDefaultMailType(), ImportUtils.getBooleanAsInt(importProfile.getUpdateAllDuplicates())); } else {/*from ww w. j a v a2 s .c o m*/ logSqlStatement(logger, INSERT_MYSQL); SqlUpdate sqlUpdate = new SqlUpdate(getDataSource(), INSERT_MYSQL, new int[] { Types.INTEGER, Types.INTEGER, Types.VARCHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.VARCHAR, Types.BOOLEAN, Types.VARCHAR, Types.INTEGER, Types.INTEGER, Types.BOOLEAN }); sqlUpdate.setReturnGeneratedKeys(true); sqlUpdate.setGeneratedKeysColumnNames(new String[] { FIELD_ID }); sqlUpdate.compile(); GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder(); Object[] values = new Object[] { importProfile.getCompanyId(), importProfile.getAdminId(), importProfile.getName(), importProfile.getSeparator(), importProfile.getTextRecognitionChar(), importProfile.getCharset(), importProfile.getDateFormat(), importProfile.getImportMode(), importProfile.getNullValuesAction(), importProfile.getKeyColumn(), ImportUtils.getBooleanAsInt(importProfile.getExtendedEmailCheck()), importProfile.getMailForReport(), importProfile.getCheckForDuplicates(), importProfile.getDefaultMailType(), ImportUtils.getBooleanAsInt(importProfile.getUpdateAllDuplicates()) }; sqlUpdate.update(values, generatedKeyHolder); profileId = generatedKeyHolder.getKey().intValue(); } importProfile.setId(profileId); insertColumnMappings(importProfile.getColumnMapping(), importProfile.getId()); insertGenderMappings(importProfile.getGenderMapping(), importProfile.getId()); return importProfile.getId(); }
From source file:org.agnitas.dao.impl.RecipientDaoImpl.java
/** * Inserts new customer record in Database with a fresh customer-id * * @return true on success//from ww w . ja v a2s.c om */ @Override public int insertNewCust(Recipient cust) { StringBuffer Columns = new StringBuffer("("); StringBuffer Values = new StringBuffer(" VALUES ("); String aColumn = null; String aParameter = null; String ColType = null; int intValue = 0; int day, month, year; int hour = 0; int minute = 0; int second = 0; StringBuffer insertCust = new StringBuffer("INSERT INTO customer_" + cust.getCompanyID() + "_tbl "); boolean appendIt = false; boolean hasDefault = false; String appendColumn = null; String appendValue = null; NumberFormat aFormat1 = null; NumberFormat aFormat2 = null; if (cust.getCustDBStructure() == null) { cust.loadCustDBStructure(); } // logic from former method getNewCustomerID String sqlStatement = null; int customerID = 0; int companyID = cust.getCompanyID(); if (companyID == 0) { return customerID; } if (mayAdd(companyID, 1) == false) { return customerID; } try { // set customerID for Oracle if (AgnUtils.isOracleDB()) { JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource")); sqlStatement = "select customer_" + companyID + "_tbl_seq.nextval FROM dual"; customerID = tmpl.queryForInt(sqlStatement); cust.setCustomerID(customerID); } } catch (Exception e) { logger.error("Error inserting new customer", e); customerID = 0; return customerID; } if (logger.isDebugEnabled()) { logger.debug("new customerID: " + customerID); } // Oracle: put customerID in SQL statement at first // (MySQL: no customerID available, yet) if (AgnUtils.isOracleDB()) { Columns.append("customer_id"); Values.append(Integer.toString(cust.getCustomerID())); } Iterator<String> i = cust.getCustDBStructure().keySet().iterator(); while (i.hasNext()) { aColumn = i.next(); ColType = cust.getCustDBStructure().get(aColumn); appendIt = false; hasDefault = false; if (!aColumn.equalsIgnoreCase("customer_id")) { if (aColumn.equalsIgnoreCase("creation_date") || aColumn.equalsIgnoreCase("timestamp") || aColumn.equalsIgnoreCase("change_date")) { appendValue = "current_timestamp"; appendColumn = aColumn; appendIt = true; } else if (ColType.equalsIgnoreCase("DATE")) { if (cust.getCustParameters(aColumn + "_DAY_DATE") != null && cust.getCustParameters(aColumn + "_MONTH_DATE") != null && cust.getCustParameters(aColumn + "_YEAR_DATE") != null) { aFormat1 = new DecimalFormat("00"); aFormat2 = new DecimalFormat("0000"); try { if (!cust.getCustParameters(aColumn + "_DAY_DATE").trim().equals("")) { day = Integer.parseInt(cust.getCustParameters(aColumn + "_DAY_DATE")); month = Integer.parseInt(cust.getCustParameters(aColumn + "_MONTH_DATE")); year = Integer.parseInt(cust.getCustParameters(aColumn + "_YEAR_DATE")); if ((cust.getCustParameters(aColumn + "_HOUR_DATE") != null) && !cust.getCustParameters(aColumn + "_HOUR_DATE").trim().equals("")) { hour = Integer.parseInt(cust.getCustParameters(aColumn + "_HOUR_DATE")); } if ((cust.getCustParameters(aColumn + "_MINUTE_DATE") != null) && !cust.getCustParameters(aColumn + "_MINUTE_DATE").trim().equals("")) { minute = Integer.parseInt(cust.getCustParameters(aColumn + "_MINUTE_DATE")); } if ((cust.getCustParameters(aColumn + "_SECOND_DATE") != null) && !cust.getCustParameters(aColumn + "_SECOND_DATE").trim().equals("")) { second = Integer.parseInt(cust.getCustParameters(aColumn + "_SECOND_DATE")); } if (AgnUtils.isOracleDB()) { appendValue = "to_date('" + aFormat1.format(day) + "." + aFormat1.format(month) + "." + aFormat2.format(year) + " " + aFormat1.format(hour) + ":" + aFormat1.format(minute) + ":" + aFormat1.format(second) + "', 'DD.MM.YYYY HH24:MI:SS')"; } else { appendValue = "STR_TO_DATE('" + aFormat1.format(day) + "-" + aFormat1.format(month) + "-" + aFormat2.format(year) + " " + aFormat1.format(hour) + ":" + aFormat1.format(minute) + ":" + aFormat1.format(second) + "', '%d-%m-%Y %H:%i:%s')"; } appendColumn = aColumn; appendIt = true; } else { ProfileField tmp = cust.getCustDBProfileStructure().get(aColumn); if (tmp != null) { String defaultValue = tmp.getDefaultValue(); if (!StringUtils.isBlank(defaultValue)) { appendValue = createDateDefaultValueExpression(defaultValue); hasDefault = true; } } if (!hasDefault) { appendValue = "null"; } appendColumn = aColumn; appendIt = true; } } catch (Exception e1) { logger.error("insertNewCust: (" + aColumn + ") " + e1.getMessage(), e1); } } else { ProfileField tmp = cust.getCustDBProfileStructure().get(aColumn); if (tmp != null) { String defaultValue = tmp.getDefaultValue(); if (!StringUtils.isBlank(defaultValue)) { appendValue = createDateDefaultValueExpression(defaultValue); hasDefault = true; } } if (hasDefault) { appendColumn = aColumn; appendIt = true; } } } if (ColType.equalsIgnoreCase("INTEGER") || ColType.equalsIgnoreCase("DOUBLE")) { aParameter = cust.getCustParameters(aColumn); if (!StringUtils.isEmpty(aParameter)) { try { intValue = Integer.parseInt(aParameter); } catch (Exception e1) { intValue = 0; } appendValue = Integer.toString(intValue); appendColumn = aColumn; appendIt = true; } else { ProfileField tmp = cust.getCustDBProfileStructure().get(aColumn); if (tmp != null) { String defaultValue = tmp.getDefaultValue(); if (!StringUtils.isBlank(defaultValue)) { appendValue = defaultValue; hasDefault = true; } } if (hasDefault) { appendColumn = aColumn; appendIt = true; } } } if (ColType.equalsIgnoreCase("VARCHAR") || ColType.equalsIgnoreCase("CHAR")) { aParameter = cust.getCustParameters(aColumn); if (!StringUtils.isEmpty(aParameter)) { appendValue = "'" + SafeString.getSQLSafeString(aParameter) + "'"; appendColumn = aColumn; appendIt = true; } else { ProfileField tmp = cust.getCustDBProfileStructure().get(aColumn); if (tmp != null) { String defaultValue = tmp.getDefaultValue(); if (!StringUtils.isBlank(defaultValue)) { appendValue = "'" + defaultValue + "'"; hasDefault = true; } } if (hasDefault) { appendColumn = aColumn; appendIt = true; } } } if (appendIt) { // if Columns contains more than "(", i.e. customerID was set if (!Columns.toString().equals("(")) { Columns.append(", "); Values.append(", "); } Columns.append(appendColumn.toLowerCase()); Values.append(appendValue); } } } Columns.append(")"); Values.append(")"); insertCust.append(Columns.toString()); insertCust.append(Values.toString()); if (AgnUtils.isOracleDB()) { try { JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource")); tmpl.execute(insertCust.toString()); if (logger.isDebugEnabled()) { logger.debug("insertCust: " + insertCust.toString()); } } catch (Exception e3) { logger.error("insertNewCustomer in Oracle", e3); cust.setCustomerID(0); return 0; } } else { try { SqlUpdate sqlUpdate = new SqlUpdate((DataSource) this.applicationContext.getBean("dataSource"), insertCust.toString()); sqlUpdate.setReturnGeneratedKeys(true); sqlUpdate.compile(); GeneratedKeyHolder key = new GeneratedKeyHolder(); sqlUpdate.update(null, key); customerID = key.getKey().intValue(); cust.setCustomerID(customerID); } catch (Exception e3) { logger.error("insertNewCust in MySQL", e3); cust.setCustomerID(0); return 0; } } return cust.getCustomerID(); }
From source file:org.agnitas.dao.impl.RecipientDaoImpl.java
/** * Gets new customerID from Database-Sequence an stores it in member-variable "customerID" * * @return true on success//from w ww .java 2 s.c o m */ @Override public int getNewCustomerID(int companyID) { String sqlStatement = null; int customerID = 0; Dialect dialect = AgnUtils.getHibernateDialect(); if (companyID == 0) { return customerID; } if (mayAdd(companyID, 1) == false) { return customerID; } try { if (dialect.supportsSequences()) { JdbcTemplate tmpl = new JdbcTemplate((DataSource) this.applicationContext.getBean("dataSource")); sqlStatement = "select customer_" + companyID + "_tbl_seq.nextval FROM dual"; customerID = tmpl.queryForInt(sqlStatement); } else { sqlStatement = "insert into customer_" + companyID + "_tbl_seq () values ()"; SqlUpdate updt = new SqlUpdate((DataSource) this.applicationContext.getBean("dataSource"), sqlStatement); updt.setReturnGeneratedKeys(true); GeneratedKeyHolder key = new GeneratedKeyHolder(); customerID = updt.update(null, key); customerID = key.getKey().intValue(); } } catch (Exception e) { customerID = 0; System.err.println("Exception:" + e); System.err.println(AgnUtils.getStackTrace(e)); } if (logger.isDebugEnabled()) { logger.debug("new customerID: " + customerID); } return customerID; }