List of usage examples for org.springframework.jdbc.datasource DataSourceUtils getConnection
public static Connection getConnection(DataSource dataSource) throws CannotGetJdbcConnectionException
From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java
@Override public LinkedHashMap<String, Map<String, Object>> getColumnInfoByColumnName(int companyId, String column) { DataSource ds = (DataSource) applicationContext.getBean("dataSource"); LinkedHashMap<String, Map<String, Object>> list = new LinkedHashMap<String, Map<String, Object>>(); ResultSet resultSet = null;// ww w .ja v a 2 s. co m Connection con = DataSourceUtils.getConnection(ds); try { if (AgnUtils.isOracleDB()) { resultSet = con.getMetaData().getColumns(null, AgnUtils.getDefaultValue("jdbc.username").toUpperCase(), "CUSTOMER_" + companyId + "_TBL", column.toUpperCase()); } else { resultSet = con.getMetaData().getColumns(null, null, "customer_" + companyId + "_tbl", column); } if (resultSet != null) { while (resultSet.next()) { String type; String col = resultSet.getString(4).toLowerCase(); Map<String, Object> mapping = new HashMap<String, Object>(); mapping.put("column", col); mapping.put("shortname", col); type = ImportUtils.dbtype2string(resultSet.getInt(5)); mapping.put("type", type); mapping.put("length", resultSet.getInt(7)); if (resultSet.getInt(11) == DatabaseMetaData.columnNullable) { mapping.put("nullable", 1); } else { mapping.put("nullable", 0); } list.put((String) mapping.get("shortname"), mapping); } } resultSet.close(); } catch (Exception e) { logger.error(MessageFormat.format("Failed to get column ({0}) info for admin ({1})", column, companyId), e); // TODO: Check this: is "admin" in combination with companyId correct here??? } finally { DataSourceUtils.releaseConnection(con, ds); } return list; }
From source file:org.agnitas.dao.impl.RecipientDaoImpl.java
/** * Load complete Subscriber-Data from DB. customerID must be set first for this method. * * @return Map with Key/Value-Pairs of customer data *//*from w w w . j a va2s . co m*/ @Override public CaseInsensitiveMap<Object> getCustomerDataFromDb(int companyID, int customerID) { String aName = null; String aValue = null; int a; java.sql.Timestamp aTime = null; Recipient cust = (Recipient) applicationContext.getBean("Recipient"); if (cust.getCustParameters() == null) { cust.setCustParameters(new CaseInsensitiveMap<Object>()); } String getCust = "SELECT * FROM customer_" + companyID + "_tbl WHERE customer_id=" + customerID; if (cust.getCustDBStructure() == null) { cust.loadCustDBStructure(); } DataSource ds = (DataSource) this.applicationContext.getBean("dataSource"); Connection con = DataSourceUtils.getConnection(ds); try { Statement stmt = con.createStatement(); ResultSet rset = stmt.executeQuery(getCust); if (logger.isInfoEnabled()) { logger.info("getCustomerDataFromDb: " + getCust); } if (rset.next()) { ResultSetMetaData aMeta = rset.getMetaData(); for (a = 1; a <= aMeta.getColumnCount(); a++) { aValue = null; aName = aMeta.getColumnName(a).toLowerCase(); switch (aMeta.getColumnType(a)) { case java.sql.Types.TIMESTAMP: case java.sql.Types.TIME: case java.sql.Types.DATE: try { aTime = rset.getTimestamp(a); } catch (Exception e) { aTime = null; } if (aTime == null) { cust.getCustParameters().put(aName + "_DAY_DATE", ""); cust.getCustParameters().put(aName + "_MONTH_DATE", ""); cust.getCustParameters().put(aName + "_YEAR_DATE", ""); cust.getCustParameters().put(aName + "_HOUR_DATE", ""); cust.getCustParameters().put(aName + "_MINUTE_DATE", ""); cust.getCustParameters().put(aName + "_SECOND_DATE", ""); cust.getCustParameters().put(aName, ""); } else { GregorianCalendar aCal = new GregorianCalendar(); aCal.setTime(aTime); cust.getCustParameters().put(aName + "_DAY_DATE", Integer.toString(aCal.get(GregorianCalendar.DAY_OF_MONTH))); cust.getCustParameters().put(aName + "_MONTH_DATE", Integer.toString(aCal.get(GregorianCalendar.MONTH) + 1)); cust.getCustParameters().put(aName + "_YEAR_DATE", Integer.toString(aCal.get(GregorianCalendar.YEAR))); cust.getCustParameters().put(aName + "_HOUR_DATE", Integer.toString(aCal.get(GregorianCalendar.HOUR_OF_DAY))); cust.getCustParameters().put(aName + "_MINUTE_DATE", Integer.toString(aCal.get(GregorianCalendar.MINUTE))); cust.getCustParameters().put(aName + "_SECOND_DATE", Integer.toString(aCal.get(GregorianCalendar.SECOND))); SimpleDateFormat bdfmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); cust.getCustParameters().put(aName, bdfmt.format(aCal.getTime())); } break; default: aValue = rset.getString(a); if (aValue == null) { aValue = ""; } cust.getCustParameters().put(aName, aValue); break; } } } rset.close(); stmt.close(); } catch (Exception e) { logger.error("getCustomerDataFromDb: " + getCust, e); AgnUtils.sendExceptionMail("sql:" + getCust, e); } DataSourceUtils.releaseConnection(con, ds); cust.setChangeFlag(false); Map<String, Object> result = cust.getCustParameters(); if (result instanceof CaseInsensitiveMap) { return (CaseInsensitiveMap<Object>) result; } else { return new CaseInsensitiveMap<Object>(result); } }
From source file:org.agnitas.dao.impl.RecipientDaoImpl.java
@Override public CaseInsensitiveMap<CsvColInfo> readDBColumns(int companyID) { String sqlGetTblStruct = "SELECT * FROM customer_" + companyID + "_tbl WHERE 1=0"; CsvColInfo aCol = null;//from w w w . ja va2 s .c om int colType; CaseInsensitiveMap<CsvColInfo> dbAllColumns = new CaseInsensitiveMap<CsvColInfo>(); DataSource ds = (DataSource) this.applicationContext.getBean("dataSource"); Connection con = DataSourceUtils.getConnection(ds); try { Statement stmt = con.createStatement(); ResultSet rset = stmt.executeQuery(sqlGetTblStruct); ResultSetMetaData meta = rset.getMetaData(); for (int i = 1; i <= meta.getColumnCount(); i++) { if (!meta.getColumnName(i).equals("change_date") && !meta.getColumnName(i).equals("creation_date") && !meta.getColumnName(i).equals("datasource_id")) { // if (meta.getColumnName(i).equals("customer_id")) { // if (status == null) { // initStatus(getWebApplicationContext()); // } // if (!( mode == ImportWizardServiceImpleImpl.MODE_ONLY_UPDATE && status.getKeycolumn().equals("customer_id"))) { // continue; // } // } aCol = new CsvColInfo(); aCol.setName(meta.getColumnName(i)); aCol.setLength(meta.getColumnDisplaySize(i)); aCol.setType(CsvColInfo.TYPE_UNKNOWN); aCol.setActive(false); aCol.setNullable(meta.isNullable(i) != 0); colType = meta.getColumnType(i); aCol.setType(dbTypeToCsvType(colType)); dbAllColumns.put(meta.getColumnName(i), aCol); } } rset.close(); stmt.close(); } catch (Exception e) { logger.error("readDBColumns (companyID: " + companyID + ")", e); } DataSourceUtils.releaseConnection(con, ds); return dbAllColumns; }
From source file:org.agnitas.util.AgnUtils.java
/** * Getter for property bshInterpreter./*from w w w .j a v a 2 s .com*/ * * @return Value of property bshInterpreter. */ public static Interpreter getBshInterpreter(int cID, int customerID, ApplicationContext con) { DataSource ds = (DataSource) con.getBean("dataSource"); Interpreter aBsh = new Interpreter(); NameSpace aNameSpace = aBsh.getNameSpace(); aNameSpace.importClass("org.agnitas.util.AgnUtils"); String sqlStatement = "select * from customer_" + cID + "_tbl cust where cust.customer_id=" + customerID; Connection dbCon = DataSourceUtils.getConnection(ds); try { Statement stmt = dbCon.createStatement(); ResultSet rset = stmt.executeQuery(sqlStatement); ResultSetMetaData aMeta = rset.getMetaData(); if (rset.next()) { for (int i = 1; i <= aMeta.getColumnCount(); i++) { switch (aMeta.getColumnType(i)) { case java.sql.Types.BIGINT: case java.sql.Types.INTEGER: case java.sql.Types.NUMERIC: case java.sql.Types.SMALLINT: case java.sql.Types.TINYINT: if (rset.getObject(i) != null) { aNameSpace.setTypedVariable(aMeta.getColumnName(i), java.lang.Integer.class, new Integer(rset.getInt(i)), null); } else { aNameSpace.setTypedVariable(aMeta.getColumnName(i), java.lang.Integer.class, null, null); } break; case java.sql.Types.DECIMAL: case java.sql.Types.DOUBLE: case java.sql.Types.FLOAT: if (rset.getObject(i) != null) { aNameSpace.setTypedVariable(aMeta.getColumnName(i), java.lang.Double.class, new Double(rset.getDouble(i)), null); } else { aNameSpace.setTypedVariable(aMeta.getColumnName(i), java.lang.Double.class, null, null); } break; case java.sql.Types.CHAR: case java.sql.Types.LONGVARCHAR: case java.sql.Types.VARCHAR: aNameSpace.setTypedVariable(aMeta.getColumnName(i), java.lang.String.class, rset.getString(i), null); break; case java.sql.Types.DATE: case java.sql.Types.TIME: case java.sql.Types.TIMESTAMP: aNameSpace.setTypedVariable(aMeta.getColumnName(i), java.util.Date.class, rset.getTimestamp(i), null); break; default: logger.error("Ignoring: " + aMeta.getColumnName(i)); } } } rset.close(); stmt.close(); // add virtual column "sysdate" aNameSpace.setTypedVariable(AgnUtils.getHibernateDialect().getCurrentTimestampSQLFunctionName(), Date.class, new Date(), null); } catch (Exception e) { sendExceptionMail("Sql: " + sqlStatement, e); logger.error("getBshInterpreter: " + e.getMessage()); aBsh = null; } DataSourceUtils.releaseConnection(dbCon, ds); return aBsh; }
From source file:org.agnitas.web.ExportWizardAction.java
/** * Creates sql query for getting recipients according to the export definition conditions; * gets recipients from database;//from www. j a v a 2 s.c o m * stores the export result in temporary zip file. * * @param aForm ExportWizardForm object * @param aContext application context * @param req HTTP request */ protected void collectContent(ExportWizardForm aForm, ApplicationContext aContext, HttpServletRequest req) { int companyID = AgnUtils.getAdmin(req).getCompany().getId(); Locale loc_old = Locale.getDefault(); aForm.setDbExportStatusMessages(new LinkedList<String>()); aForm.setDbExportStatus(100); aForm.setLinesOK(0); Target aTarget = null; if (aForm.getTargetID() != 0) { aTarget = targetDao.getTarget(aForm.getTargetID(), companyID); aForm.setTargetID(aTarget.getId()); } String charset = aForm.getCharset(); if (charset == null || charset.trim().equals("")) { charset = "UTF-8"; aForm.setCharset(charset); // charset also in form } StringBuffer usedColumnsString = new StringBuffer(); int exportStartColumn = 2; for (String columnName : aForm.getColumns()) { // customer_id is selected by default in the the base sql statement if ("customer_id".equalsIgnoreCase(columnName)) { // mark customer_id to be exported too exportStartColumn = 1; } else { usedColumnsString.append(", cust." + columnName + " " + columnName); } } if (aForm.getMailinglists() != null) { for (int i = 0; i < aForm.getMailinglists().length; i++) { String ml = aForm.getMailinglists()[i]; usedColumnsString.append(", (SELECT m" + ml + ".user_status FROM customer_" + companyID + "_binding_tbl m" + ml + " WHERE m" + ml + ".customer_id = cust.customer_id AND m" + ml + ".mailinglist_id = " + ml + " AND m" + ml + ".mediatype = 0) as Userstate_Mailinglist_" + ml); usedColumnsString.append(", (SELECT m" + ml + "." + AgnUtils.changeDateName() + " FROM customer_" + companyID + "_binding_tbl m" + ml + " WHERE m" + ml + ".customer_id = cust.customer_id AND m" + ml + ".mailinglist_id = " + ml + " AND m" + ml + ".mediatype = 0) as Mailinglist_" + ml + "_Timestamp"); } } StringBuffer whereString = new StringBuffer(""); StringBuffer customerTableSql = new StringBuffer("SELECT * FROM (SELECT DISTINCT cust.customer_id" + usedColumnsString.toString() + " FROM customer_" + companyID + "_tbl cust"); if (aForm.getMailinglistID() != -1 && (aForm.getMailinglistID() > 0 || !aForm.getUserType().equals("E") || aForm.getUserStatus() != 0)) { customerTableSql.append(", customer_" + companyID + "_binding_tbl bind"); whereString.append(" cust.customer_id = bind.customer_id AND bind.mediatype=0"); } if (aForm.getMailinglistID() > 0) { whereString.append(" and bind.mailinglist_id = " + aForm.getMailinglistID()); } if (aForm.getMailinglistID() == NO_MAILINGLIST) { whereString.append(" NOT EXISTS (SELECT 1 FROM customer_" + companyID + "_binding_tbl bind WHERE cust.customer_id = bind.customer_id) "); } else { if (!aForm.getUserType().equals("E")) { whereString .append(" AND bind.user_type = '" + SafeString.getSQLSafeString(aForm.getUserType()) + "'"); } if (aForm.getUserStatus() != 0) { whereString.append(" AND bind.user_status = " + aForm.getUserStatus()); } } if (aForm.getTargetID() != 0) { if (aForm.getMailinglistID() != 0 || !aForm.getUserType().equals("E") || aForm.getUserStatus() != 0) { whereString.append(" AND "); } whereString.append(" (" + aTarget.getTargetSQL() + ")"); } String datesParametersString = getDatesParametersString(aForm); if (!StringUtils.isEmpty(whereString.toString())) { whereString.append(" and "); } whereString.append(datesParametersString); if (whereString.length() > 0) { customerTableSql.append(" WHERE " + whereString); } AgnUtils.userlogger().info("Generated export SQL query: " + customerTableSql); Connection con = DataSourceUtils.getConnection(dataSource); aForm.setCsvFile(null); PrintWriter out = null; Statement stmt = null; ResultSet rset = null; try { File systemUploadDirectory = AgnUtils.createDirectory(AgnUtils.getDefaultValue("system.upload")); File outFile = File.createTempFile("exp" + companyID + "_", ".zip", systemUploadDirectory); ZipOutputStream aZip = new ZipOutputStream(new FileOutputStream(outFile)); AgnUtils.userlogger().info("Export file <" + outFile.getAbsolutePath() + ">"); stmt = con.createStatement(); rset = stmt.executeQuery(customerTableSql.toString()); aZip.putNextEntry(new ZipEntry("emm_export.csv")); Locale.setDefault(new Locale("en")); out = new PrintWriter(new BufferedWriter(new OutputStreamWriter(aZip, charset))); ResultSetMetaData mData = rset.getMetaData(); int columnCount = mData.getColumnCount(); // Write CSV-Header line for (int i = exportStartColumn; i <= columnCount; i++) { if (i > exportStartColumn) { out.print(aForm.getSeparator()); } String columnName = mData.getColumnName(i); out.print(aForm.getDelimiter() + escapeChars(columnName, aForm.getDelimiter()) + aForm.getDelimiter()); } out.print("\n"); // Write data lines while (rset.next()) { for (int i = exportStartColumn; i <= columnCount; i++) { if (i > exportStartColumn) { out.print(aForm.getSeparator()); } String aValue; try { aValue = rset.getString(i); } catch (Exception ex) { aValue = null; // Exceptions should not break the export, but should be logged AgnUtils.userlogger().error("Exception in export:collectContent:", ex); } if (aValue == null) { // null values should be displayed as empty string aValue = ""; } else { aValue = escapeChars(aValue, aForm.getDelimiter()); aValue = aForm.getDelimiter() + aValue + aForm.getDelimiter(); } out.print(aValue); } out.print("\n"); aForm.setLinesOK(aForm.getLinesOK() + 1); } aForm.setCsvFile(outFile); } catch (Exception e) { AgnUtils.userlogger().error("collectContent: " + e); e.printStackTrace(); } finally { if (out != null) { out.close(); } if (rset != null) { try { rset.close(); } catch (SQLException e) { e.printStackTrace(); } } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } DataSourceUtils.releaseConnection(con, dataSource); aForm.setDbExportStatus(1001); Locale.setDefault(loc_old); } }
From source file:org.agnitas.web.ImportWizardForm.java
/** * Reads columns from database./*from w w w . j a v a 2s .c o m*/ */ protected void readDBColumns(int companyID, DataSource ds) { String sqlGetTblStruct = "SELECT * FROM customer_" + companyID + "_tbl WHERE 1=0"; CsvColInfo aCol = null; String colType = null; dbAllColumns = new CaseInsensitiveMap(); Connection con = DataSourceUtils.getConnection(ds); try { Statement stmt = con.createStatement(); ResultSet rset = stmt.executeQuery(sqlGetTblStruct); ResultSetMetaData meta = rset.getMetaData(); for (int i = 1; i <= meta.getColumnCount(); i++) { if (!meta.getColumnName(i).equals("change_date") && !meta.getColumnName(i).equals("creation_date") && !meta.getColumnName(i).equals("datasource_id")) { if (meta.getColumnName(i).equals("customer_id")) { if (status == null) { initStatus(getWebApplicationContext()); } if (!(this.mode == ImportWizardForm.MODE_ONLY_UPDATE && this.status.getKeycolumn().equals("customer_id"))) { continue; } } aCol = new CsvColInfo(); aCol.setName(meta.getColumnName(i)); aCol.setLength(meta.getColumnDisplaySize(i)); aCol.setType(CsvColInfo.TYPE_UNKNOWN); aCol.setActive(false); colType = meta.getColumnTypeName(i); if (colType.startsWith("VARCHAR")) { aCol.setType(CsvColInfo.TYPE_CHAR); } else if (colType.startsWith("CHAR")) { aCol.setType(CsvColInfo.TYPE_CHAR); } else if (colType.startsWith("NUM")) { aCol.setType(CsvColInfo.TYPE_NUMERIC); } else if (colType.startsWith("INTEGER")) { aCol.setType(CsvColInfo.TYPE_NUMERIC); } else if (colType.startsWith("DOUBLE")) { aCol.setType(CsvColInfo.TYPE_NUMERIC); } else if (colType.startsWith("TIME")) { aCol.setType(CsvColInfo.TYPE_DATE); } else if (colType.startsWith("DATE")) { aCol.setType(CsvColInfo.TYPE_DATE); } this.dbAllColumns.put(meta.getColumnName(i), aCol); } } rset.close(); stmt.close(); } catch (Exception e) { AgnUtils.logger().error("readDBColumns: " + e); } DataSourceUtils.releaseConnection(con, ds); }
From source file:org.apache.syncope.core.audit.AuditConnectionFactory.java
public static Connection getConnection() { if (datasource != null) { return DataSourceUtils.getConnection(datasource); }//from w w w . ja v a2 s .c om throw new IllegalStateException("Audit dataSource init failed: check logs"); }
From source file:org.apache.syncope.core.logic.audit.AuditConnectionFactory.java
public static Connection getConnection() { if (DATASOURCE != null) { return DataSourceUtils.getConnection(DATASOURCE); }/*from www .j a v a2s .co m*/ throw new IllegalStateException("Audit dataSource init failed: check logs"); }
From source file:org.apache.syncope.core.logic.init.JobManagerImpl.java
private boolean isRunningElsewhere(final JobKey jobKey) throws SchedulerException { if (!scheduler.getScheduler().getMetaData().isJobStoreClustered()) { return false; }/*from w ww. ja v a 2s .com*/ Connection conn = DataSourceUtils .getConnection(domainsHolder.getDomains().get(SyncopeConstants.MASTER_DOMAIN)); PreparedStatement stmt = null; try { stmt = conn.prepareStatement("SELECT 1 FROM " + Constants.DEFAULT_TABLE_PREFIX + "FIRED_TRIGGERS " + "WHERE JOB_NAME = ? AND JOB_GROUP = ?"); stmt.setString(1, jobKey.getName()); stmt.setString(2, jobKey.getGroup()); return stmt.executeQuery().next(); } catch (SQLException e) { throw new SchedulerException(e); } finally { IOUtil.quietClose(stmt); IOUtil.quietClose(conn); } }
From source file:org.apache.syncope.core.persistence.jpa.content.XMLContentExporter.java
@Override public void export(final String domain, final OutputStream os, final String uwfPrefix, final String gwfPrefix, final String awfPrefix) throws SAXException, TransformerConfigurationException { if (StringUtils.isNotBlank(uwfPrefix)) { TABLE_PREFIXES_TO_BE_EXCLUDED.add(uwfPrefix); }//from w w w.j a v a 2 s .co m if (StringUtils.isNotBlank(gwfPrefix)) { TABLE_PREFIXES_TO_BE_EXCLUDED.add(gwfPrefix); } if (StringUtils.isNotBlank(awfPrefix)) { TABLE_PREFIXES_TO_BE_EXCLUDED.add(awfPrefix); } StreamResult streamResult = new StreamResult(os); final SAXTransformerFactory transformerFactory = (SAXTransformerFactory) SAXTransformerFactory .newInstance(); transformerFactory.setFeature(javax.xml.XMLConstants.FEATURE_SECURE_PROCESSING, true); TransformerHandler handler = transformerFactory.newTransformerHandler(); Transformer serializer = handler.getTransformer(); serializer.setOutputProperty(OutputKeys.ENCODING, StandardCharsets.UTF_8.name()); serializer.setOutputProperty(OutputKeys.INDENT, "yes"); handler.setResult(streamResult); handler.startDocument(); handler.startElement("", "", ROOT_ELEMENT, new AttributesImpl()); DataSource dataSource = domainsHolder.getDomains().get(domain); if (dataSource == null) { throw new IllegalArgumentException("Could not find DataSource for domain " + domain); } String dbSchema = ApplicationContextProvider.getBeanFactory().getBean(domain + "DatabaseSchema", String.class); Connection conn = null; ResultSet rs = null; try { conn = DataSourceUtils.getConnection(dataSource); final DatabaseMetaData meta = conn.getMetaData(); rs = meta.getTables(null, StringUtils.isBlank(dbSchema) ? null : dbSchema, null, new String[] { "TABLE" }); final Set<String> tableNames = new TreeSet<>(String.CASE_INSENSITIVE_ORDER); while (rs.next()) { String tableName = rs.getString("TABLE_NAME"); LOG.debug("Found table {}", tableName); if (isTableAllowed(tableName)) { tableNames.add(tableName); } } LOG.debug("Tables to be exported {}", tableNames); // then sort tables based on foreign keys and dump for (String tableName : sortByForeignKeys(dbSchema, conn, tableNames)) { try { doExportTable(handler, dbSchema, conn, tableName, TABLES_TO_BE_FILTERED.get(tableName.toUpperCase())); } catch (Exception e) { LOG.error("Failure exporting table {}", tableName, e); } } } catch (SQLException e) { LOG.error("While exporting database content", e); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { LOG.error("While closing tables result set", e); } } DataSourceUtils.releaseConnection(conn, dataSource); if (conn != null) { try { if (!conn.isClosed()) { conn.close(); } } catch (SQLException e) { LOG.error("While releasing connection", e); } } } handler.endElement("", "", ROOT_ELEMENT); handler.endDocument(); }