Example usage for javax.sql DataSource getConnection

List of usage examples for javax.sql DataSource getConnection

  1. HOME
  2. Java
  3. javax
  4. javax.sql.*
  5. DataSource
  6. getConnection

Introduction

In this page you can find the example usage for javax.sql DataSource getConnection.

Prototype

Connection getConnection() throws SQLException;

Source Link

Document

Attempts to establish a connection with the data source that this DataSource object represents.

Usage

From source file:com.emc.plants.service.impl.ReportGeneratorBean.java

/** 
 * Run the report to get the top zip codes for a range of dates.
 *
 * @param startdate Start of date range.
 * @param enddate End of date range./*from  w w w. java2 s. c om*/
 * @param quantity Number of items to return in report.
 * @param reportFormat - Report format information.
 * @return Report containing results.
 */
@SuppressWarnings("unchecked")
public Report getTopSellingZipsForDates(java.util.Date startdate, java.util.Date enddate, int quantity,
        ReportFormat reportFormat) {
    Report report = null;
    Connection conn = null;
    ResultSet results = null;
    PreparedStatement sqlStatement = null;
    try {
        // Establish connection to datasource.
        String orderInfoTableName = "ORDER1";

        DataSource ds = (DataSource) Util.getInitialContext().lookup("jdbc/PlantsByWebSphereDataSource");
        conn = ds.getConnection();

        // Set sort order of ascending or descending.
        String sortOrder;
        if (reportFormat.isAscending())
            sortOrder = "ASC";
        else
            sortOrder = "DESC";

        // Set up where by clause.
        String startDateString = Long.toString(startdate.getTime());
        if (startDateString.length() < 14) {
            StringBuffer sb = new StringBuffer(Util.ZERO_14);
            sb.replace((14 - startDateString.length()), 14, startDateString);
            startDateString = sb.toString();
        }
        String endDateString = Long.toString(enddate.getTime());
        if (endDateString.length() < 14) {
            StringBuffer sb = new StringBuffer(Util.ZERO_14);
            sb.replace((14 - endDateString.length()), 14, endDateString);
            endDateString = sb.toString();
        }
        String whereString = " WHERE sellDate BETWEEN '" + startDateString + "' AND '" + endDateString + "' ";

        // Create SQL statement.
        String sqlString = "SELECT billZip, SUM(profit) AS PROFITS FROM " + orderInfoTableName + whereString
                + " GROUP BY billZip ORDER BY PROFITS " + sortOrder + ", billZip";

        Util.debug("sqlstring=" + sqlString + "=");
        sqlStatement = conn.prepareStatement(sqlString);
        results = sqlStatement.executeQuery();
        int i;

        // Initialize vectors to store data in.
        Vector[] vecs = new Vector[2];
        for (i = 0; i < vecs.length; i++) {
            vecs[i] = new Vector();
        }

        // Sift thru results.
        int count = 0;
        while ((results.next()) && (count < quantity)) {
            count++;
            i = 1;
            vecs[0].addElement(results.getString(i++));
            vecs[1].addElement(new Float(results.getFloat(i++)));
        }

        // Create report.
        report = new Report();
        report.setReportFieldByRow(Report.ORDER_BILLZIP, vecs[0]);
        report.setReportFieldByRow(Report.PROFITS, vecs[1]);
    } catch (NamingException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally { // Clean up.
        try {
            if (results != null)
                results.close();
        } catch (Exception ignore) {
        }

        try {
            if (sqlStatement != null)
                sqlStatement.close();
        } catch (Exception ignore) {
        }

        // Close Connection.
        try {
            if (conn != null)
                conn.close();
        } catch (Exception ignore) {
        }
    }

    return report;
}

From source file:com.jedi.oracle.OracleCall.java

@Override
public void execute(Connection connection) throws Exception {
    if (connection == null) {
        DataSource dataSource = DataSourceManager.getInstance().getDataSource();
        if (dataSource == null) {
            throw new RuntimeException("Datasource is null");
        }/*ww  w  .  ja  v  a 2s .c  o m*/

        connection = dataSource.getConnection();
    }

    fillParametersFromFields();

    String sql = this.createSQL(this.getName());
    Map customTypes = this.getTypeMap();
    if (customTypes != null && !customTypes.isEmpty()) {
        Map map = connection.getTypeMap();
        map.putAll(customTypes);
        connection.setTypeMap(map);
    }

    OracleCallableStatement statement = (OracleCallableStatement) connection.prepareCall(sql);
    OracleParameterUtils.register(statement, this.parameters);
    try {
        statement.execute();
    } catch (SQLException e) {
        if (reExecutionRequired(e)) {
            statement.execute();
        } else {
            throw e;
        }
    }

    OracleParameterUtils.bind(this.parameters, statement);
    fillFieldValuesFromParameters();
}

From source file:it.lufraproini.cms.servlet.upload_user_img.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods./*from  w  w  w . j  a v a2 s  .c o m*/
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    //verifica validit sessione
    HttpSession s = SecurityLayer.checkSession(request);
    if (s != null) {
        try {
            //DBMS
            DataSource ds = (DataSource) getServletContext().getAttribute("datasource");
            Connection connection = ds.getConnection();

            CMSDataLayerImpl datalayer = new CMSDataLayerImpl(connection);

            Utente U = datalayer.getUtente(SecurityLayer.checkNumeric(s.getAttribute("userid").toString()));
            Map infoFile = prendiInfoFile(request);
            String spazio_non_sufficiente = memorizzaImmagine(datalayer, infoFile, U);
            if (spazio_non_sufficiente == null) {
                response.sendRedirect("visualizza?pagina=account");
            } else {
                response.sendRedirect("visualizza?pagina=account&err=" + spazio_non_sufficiente);
            }

        } catch (ErroreGrave ex) {
            Logger.getLogger(upload_user_img.class.getName()).log(Level.SEVERE, null, ex);
            FailureResult res = new FailureResult(getServletContext());
            res.activate(ex.getMessage(), request, response);
        } catch (SQLException ex) {
            Logger.getLogger(upload_user_img.class.getName()).log(Level.SEVERE, null, ex);
            FailureResult res = new FailureResult(getServletContext());
            res.activate(ex.getMessage(), request, response);
        }
    } else {
        response.sendRedirect("visualizza?pagina=home&err=auth");
    }
}

From source file:fll.web.api.JudgesServlet.java

@SuppressFBWarnings(value = {
        "SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING" }, justification = "Dynamic table based upon categories")
@Override//w ww .  ja v  a 2 s. c  o  m
protected final void doPost(final HttpServletRequest request, final HttpServletResponse response)
        throws IOException, ServletException {
    final ObjectMapper jsonMapper = new ObjectMapper();
    response.reset();
    response.setContentType("application/json");
    final PrintWriter writer = response.getWriter();

    final ServletContext application = getServletContext();

    int numNewJudges = 0;
    final DataSource datasource = ApplicationAttributes.getDataSource(application);
    Connection connection = null;
    PreparedStatement insertJudge = null;
    try {
        connection = datasource.getConnection();

        final int currentTournament = Queries.getCurrentTournament(connection);

        final StringWriter debugWriter = new StringWriter();
        IOUtils.copy(request.getReader(), debugWriter);

        if (LOGGER.isTraceEnabled()) {
            LOGGER.trace("Read data: " + debugWriter.toString());
        }

        final Reader reader = new StringReader(debugWriter.toString());

        final Collection<JudgeInformation> judges = jsonMapper.readValue(reader,
                JudgesTypeInformation.INSTANCE);

        final Collection<JudgeInformation> currentJudges = JudgeInformation.getJudges(connection,
                currentTournament);

        insertJudge = connection
                .prepareStatement("INSERT INTO Judges (id, category, Tournament, station) VALUES (?, ?, ?, ?)");
        insertJudge.setInt(3, currentTournament);

        for (final JudgeInformation judge : judges) {

            if (null != judge) {
                JudgeInformation found = null;
                for (final JudgeInformation cjudge : currentJudges) {
                    if (ComparisonUtils.safeEquals(cjudge, judge)) {
                        found = cjudge;
                    }
                }

                if (null == found) {
                    insertJudge.setString(1, judge.getId());
                    insertJudge.setString(2, judge.getCategory());
                    insertJudge.setString(4, judge.getGroup());
                    insertJudge.executeUpdate();
                    ++numNewJudges;
                }
            } // non-null judge
        } // foreach judge sent

        final UploadResult result = new UploadResult(true, "Successfully uploaded judges", numNewJudges);
        response.reset();
        jsonMapper.writeValue(writer, result);

    } catch (final SQLException e) {
        LOGGER.error("Error uploading judges", e);

        final UploadResult result = new UploadResult(false, e.getMessage(), numNewJudges);
        jsonMapper.writeValue(writer, result);
    } finally {
        SQLFunctions.close(insertJudge);
        SQLFunctions.close(connection);
    }

}

From source file:com.emc.plants.service.impl.ReportGeneratorBean.java

/** 
 * Run the report to get the top selling items for a range of dates.
 *
 * @param startdate Start of date range.
 * @param enddate End of date range.//from  w  w w  .jav  a  2s  .c o  m
 * @param quantity Number of items to return in report.
 * @param reportFormat - Report format information.
 * @return Report containing results.
 */
@SuppressWarnings("unchecked")
public Report getTopSellersForDates(java.util.Date startdate, java.util.Date enddate, int quantity,
        ReportFormat reportFormat) {
    Report report = null;
    Connection conn = null;
    ResultSet results = null;
    PreparedStatement sqlStatement = null;
    try {
        // Establish connection to datasource.
        String orderItemsTableName = "ORDERITEM";
        DataSource ds = (DataSource) Util.getInitialContext().lookup("jdbc/PlantsByWebSphereDataSource");
        conn = ds.getConnection();

        // Set sort order of ascending or descending.
        String sortOrder;
        if (reportFormat.isAscending())
            sortOrder = "ASC";
        else
            sortOrder = "DESC";

        // Set up where by clause.
        String startDateString = Long.toString(startdate.getTime());
        if (startDateString.length() < 14) {
            StringBuffer sb = new StringBuffer(Util.ZERO_14);
            sb.replace((14 - startDateString.length()), 14, startDateString);
            startDateString = sb.toString();
        }
        String endDateString = Long.toString(enddate.getTime());
        if (endDateString.length() < 14) {
            StringBuffer sb = new StringBuffer(Util.ZERO_14);
            sb.replace((14 - endDateString.length()), 14, endDateString);
            endDateString = sb.toString();
        }
        String whereString = " WHERE sellDate BETWEEN '" + startDateString + "' AND '" + endDateString + "' ";

        // Create SQL statement.
        String sqlString = "SELECT inventoryID, name, category,"
                + " SUM(quantity * (price - cost)) as PROFIT FROM " + orderItemsTableName + whereString
                + " GROUP BY inventoryID, name, category ORDER BY PROFIT " + sortOrder + ", name";

        Util.debug("sqlstring=" + sqlString);

        sqlStatement = conn.prepareStatement(sqlString);
        results = sqlStatement.executeQuery();
        int i;

        // Initialize vectors to store data in.
        Vector[] vecs = new Vector[4];
        for (i = 0; i < vecs.length; i++) {
            vecs[i] = new Vector();
        }

        // Sift thru results.
        int count = 0;
        while ((results.next()) && (count < quantity)) {
            count++;
            i = 1;
            vecs[0].addElement(results.getString(i++));
            vecs[1].addElement(results.getString(i++));
            vecs[2].addElement(new Integer(results.getInt(i++)));
            vecs[3].addElement(new Float(results.getFloat(i++)));
        }

        // Create report.
        report = new Report();
        report.setReportFieldByRow(Report.ORDER_INVENTORY_ID, vecs[0]);
        report.setReportFieldByRow(Report.ORDER_INVENTORY_NAME, vecs[1]);
        report.setReportFieldByRow(Report.ORDER_INVENTORY_CATEGORY, vecs[2]);
        report.setReportFieldByRow(Report.PROFITS, vecs[3]);
    }

    catch (Exception e) {
        Util.debug("exception in ReportGeneratorBean:getTopSellersForDates.  " + e);
        e.printStackTrace();
    } finally { // Clean up.
        try {
            if (results != null)
                results.close();
        } catch (Exception ignore) {
        }

        try {
            if (sqlStatement != null)
                sqlStatement.close();
        } catch (Exception ignore) {
        }

        // Close Connection.
        try {
            if (conn != null)
                conn.close();
        } catch (Exception ignore) {
        }
    }

    return report;
}

From source file:com.qualogy.qafe.business.integration.rdb.SQLQueryDAO.java

/**
 * @param ds//  w  w w .j a  v a2 s. com
 * @param tableName
 * @throws SQLException
 */
private void populateTableColumnSet(DataSource ds, String tableName) throws SQLException {
    Connection conn = ds.getConnection();
    DatabaseMetaData dbmd = conn.getMetaData();
    ResultSet rsc = dbmd.getColumns(conn.getCatalog(), null, tableName, "%");
    Set<String> foundColumnSet = new HashSet<String>();
    while (rsc.next()) {
        String columnName = rsc.getString("COLUMN_NAME");
        foundColumnSet.add(columnName);
    }
    tableColumnSet.put(tableName, foundColumnSet);
    DataSourceUtils.releaseConnection(conn, ds);
}

From source file:azkaban.trigger.JdbcTriggerLoaderTest.java

@After
public void clearDB() {
    if (!testDBExists) {
        return;//  ww  w.ja  v  a 2s.  c om
    }

    DataSource dataSource = DataSourceUtils.getMySQLDataSource(host, port, database, user, password,
            numConnections);
    Connection connection = null;
    try {
        connection = dataSource.getConnection();
    } catch (SQLException e) {
        e.printStackTrace();
        testDBExists = false;
        DbUtils.closeQuietly(connection);
        return;
    }

    QueryRunner runner = new QueryRunner();
    try {
        runner.update(connection, "DELETE FROM triggers");

    } catch (SQLException e) {
        e.printStackTrace();
        testDBExists = false;
        DbUtils.closeQuietly(connection);
        return;
    }

    DbUtils.closeQuietly(connection);
}

From source file:com.migratebird.database.impl.DefaultSQLHandler.java

/**
 * Returns a Connection to the given DataSource. The first time a Connection is requested, a new one is created
 * using the given DataSource. All subsequent calls with the same DataSource as parameter will return the same
 * Connection instance./*from  w  ww . j a v  a  2s .co  m*/
 *
 * @param dataSource provides access to the database
 * @return a Connection to the database for the given DataSource.
 */
protected Connection getConnection(DataSource dataSource) {
    Connection connection = cachedConnections.get(dataSource);
    if (connection == null) {
        try {
            connection = dataSource.getConnection();
        } catch (SQLException e) {
            throw new DatabaseException("Error while creating connection", e);
        }
        cachedConnections.put(dataSource, connection);
    }
    return connection;
}

From source file:org.jasig.services.persondir.support.jdbc.SingleRowJdbcPersonAttributeDaoTest.java

@Override
protected void tearDownSchema(DataSource dataSource) throws SQLException {
    Connection con = dataSource.getConnection();

    con.prepareStatement("DROP TABLE user_table").execute();
    con.prepareStatement("SHUTDOWN").execute();

    con.close();//from   w  ww  .  ja  v  a 2s  .  c  o m
}

From source file:com.alibaba.druid.benckmark.pool.Oracle_Case0.java

private void p0(DataSource dataSource, String name) throws SQLException {
    long startMillis = System.currentTimeMillis();
    long startYGC = TestUtil.getYoungGC();
    long startFullGC = TestUtil.getFullGC();

    final int COUNT = 1000 * 1;
    for (int i = 0; i < COUNT; ++i) {
        Connection conn = dataSource.getConnection();
        PreparedStatement stmt = conn.prepareStatement("SELECT 1 FROM DUAL");
        ResultSet rs = stmt.executeQuery();
        rs.next();//from  w w  w.  j a  va 2 s.  c  o  m
        rs.close();
        stmt.close();
        conn.close();
    }
    long millis = System.currentTimeMillis() - startMillis;
    long ygc = TestUtil.getYoungGC() - startYGC;
    long fullGC = TestUtil.getFullGC() - startFullGC;

    System.out.println(name + " millis : " + NumberFormat.getInstance().format(millis) + ", YGC " + ygc
            + " FGC " + fullGC);
}