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:io.apiman.gateway.engine.policies.auth.JDBCIdentityValidator.java

/**
 * @see io.apiman.gateway.engine.policies.auth.IIdentityValidator#validate(java.lang.String, java.lang.String, io.apiman.gateway.engine.beans.ServiceRequest, io.apiman.gateway.engine.policy.IPolicyContext, java.lang.Object, io.apiman.gateway.engine.async.IAsyncHandler)
 *//*  w w  w.  java2  s. com*/
@Override
public void validate(String username, String password, ServiceRequest request, IPolicyContext context,
        JDBCIdentitySource config, IAsyncResultHandler<Boolean> handler) {
    DataSource ds = lookupDatasource(config);
    String sqlPwd = password;
    switch (config.getHashAlgorithm()) {
    case MD5:
        sqlPwd = DigestUtils.md5Hex(password);
        break;
    case SHA1:
        sqlPwd = DigestUtils.shaHex(password);
        break;
    case SHA256:
        sqlPwd = DigestUtils.sha256Hex(password);
        break;
    case SHA384:
        sqlPwd = DigestUtils.sha384Hex(password);
        break;
    case SHA512:
        sqlPwd = DigestUtils.sha512Hex(password);
        break;
    case None:
    default:
        break;
    }
    String query = config.getQuery();
    Connection conn = null;
    boolean validated = false;
    try {
        conn = ds.getConnection();
        conn.setReadOnly(true);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, username);
        statement.setString(2, sqlPwd);
        ResultSet resultSet = statement.executeQuery();
        if (resultSet.next()) {
            validated = true;
        }
        resultSet.close();
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
            }
        }
    }

    handler.handle(AsyncResultImpl.create(validated));
}

From source file:psiprobe.controllers.sql.ConnectionTestController.java

@Override
protected ModelAndView handleContext(String contextName, Context context, HttpServletRequest request,
        HttpServletResponse response) throws Exception {

    String resourceName = ServletRequestUtils.getStringParameter(request, "resource");
    DataSource dataSource = null;

    try {//w w w  . j  a  v a 2  s  . co m
        dataSource = getContainerWrapper().getResourceResolver().lookupDataSource(context, resourceName,
                getContainerWrapper());
    } catch (NamingException e) {
        request.setAttribute("errorMessage", getMessageSourceAccessor()
                .getMessage("probe.src.dataSourceTest.resource.lookup.failure", new Object[] { resourceName }));
        logger.trace("", e);
    }

    if (dataSource == null) {
        request.setAttribute("errorMessage", getMessageSourceAccessor()
                .getMessage("probe.src.dataSourceTest.resource.lookup.failure", new Object[] { resourceName }));
    } else {
        try {
            // TODO: use Spring's jdbc template?
            try (Connection conn = dataSource.getConnection()) {
                DatabaseMetaData md = conn.getMetaData();

                List<Map<String, String>> dbMetaData = new ArrayList<>();

                addDbMetaDataEntry(dbMetaData, "probe.jsp.dataSourceTest.dbMetaData.dbProdName",
                        md.getDatabaseProductName());
                addDbMetaDataEntry(dbMetaData, "probe.jsp.dataSourceTest.dbMetaData.dbProdVersion",
                        md.getDatabaseProductVersion());
                addDbMetaDataEntry(dbMetaData, "probe.jsp.dataSourceTest.dbMetaData.jdbcDriverName",
                        md.getDriverName());
                addDbMetaDataEntry(dbMetaData, "probe.jsp.dataSourceTest.dbMetaData.jdbcDriverVersion",
                        md.getDriverVersion());
                // addDbMetaDataEntry(dbMetaData, "probe.jsp.dataSourceTest.dbMetaData.jdbcVersion",
                // String.valueOf(md.getJDBCMajorVersion()));

                return new ModelAndView(getViewName(), "dbMetaData", dbMetaData);
            }
        } catch (SQLException e) {
            String message = getMessageSourceAccessor()
                    .getMessage("probe.src.dataSourceTest.connection.failure", new Object[] { e.getMessage() });
            logger.error(message, e);
            request.setAttribute("errorMessage", message);
        }
    }

    return new ModelAndView(getViewName());
}

From source file:me.j360.idgen.impl.test.TableIdGenServiceJdbcTest.java

/**
 * initialize TestCase/*from  w w w.  j av a 2  s .c o m*/
 * 
 * @throws Exception
 *             fail to initialize
 */
@Before
public void onSetUp() throws Exception {
    DataSource dataSource = (DataSource) applicationContext.getBean("util_datasource");
    try {
        Connection conn = dataSource.getConnection();
        try {
            Statement statement = conn.createStatement();

            // 1. Try to drop the table. It may not
            // exist and throw an
            // exception.
            try {
                statement.executeUpdate("DROP TABLE idstest");
            } catch (SQLException e) {
                // The table was probably just not
                // there. Ignore this.
                // System.out.println("idstest drop
                // fail.");
                // e.printStackTrace();
            }

            try {
                statement.executeUpdate("DROP TABLE ids");
            } catch (SQLException e) {
                // The table was probably just not
                // there. Ignore this.
                // System.out.println("ids drop
                // fail.");
                // e.printStackTrace();
            }

            try {
                statement.executeUpdate("DROP TABLE MY_IDS");
            } catch (SQLException e) {
                // The table was probably just not
                // there. Ignore this.
                // System.out.println("ids drop
                // fail.");
                // e.printStackTrace();
            }

            // 2. Create the table that we will use
            // in this test.
            // Different depending on the db.
            // Please add new statements as
            // new databases are
            // tested.
            statement.executeUpdate("CREATE TABLE idstest ( " + "table_name varchar(16) NOT NULL, "
                    + "next_id DECIMAL(30) NOT NULL, " + "PRIMARY KEY (table_name))");
            statement.executeUpdate("CREATE TABLE ids ( " + "table_name varchar(16) NOT NULL, "
                    + "next_id DECIMAL(30) NOT NULL, " + "PRIMARY KEY (table_name))");

            statement.executeUpdate("CREATE TABLE MY_IDS( " + "MY_KEY varchar(16) NOT NULL, "
                    + "MY_ID DECIMAL(30) NOT NULL, " + "PRIMARY KEY (MY_KEY))");

            statement.executeUpdate("INSERT INTO ids VALUES('id','0')");
        } finally {
            conn.close();
        }
    } catch (SQLException e) {
        System.err.println("Unable to initialize database for test." + e);
        fail("Unable to initialize database for test. " + e);
    }
}

From source file:com.pikai.jdbc.testcase.PoolTest.java

private void p0(final DataSource dataSource, String name, int threadCount) throws Exception {

    final CountDownLatch startLatch = new CountDownLatch(1);
    final CountDownLatch endLatch = new CountDownLatch(threadCount);
    final CountDownLatch dumpLatch = new CountDownLatch(1);

    Thread[] threads = new Thread[threadCount];
    for (int i = 0; i < threadCount; ++i) {
        Thread thread = new Thread() {
            public void run() {
                try {
                    startLatch.await();/*from  ww  w  . j  a v a  2  s  .  c  om*/

                    for (int i = 0; i < LOOP_COUNT; ++i) {
                        Connection conn = dataSource.getConnection();
                        conn.close();
                    }
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
                endLatch.countDown();

                try {
                    dumpLatch.await();
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
        };
        threads[i] = thread;
        thread.start();
    }
    long startMillis = System.currentTimeMillis();
    long startYGC = TestUtil.getYoungGC();
    long startFullGC = TestUtil.getFullGC();
    startLatch.countDown();
    endLatch.await();

    long[] threadIdArray = new long[threads.length];
    for (int i = 0; i < threads.length; ++i) {
        threadIdArray[i] = threads[i].getId();
    }
    ThreadInfo[] threadInfoArray = ManagementFactory.getThreadMXBean().getThreadInfo(threadIdArray);

    dumpLatch.countDown();

    long blockedCount = 0;
    long waitedCount = 0;
    for (int i = 0; i < threadInfoArray.length; ++i) {
        ThreadInfo threadInfo = threadInfoArray[i];
        blockedCount += threadInfo.getBlockedCount();
        waitedCount += threadInfo.getWaitedCount();
    }

    long millis = System.currentTimeMillis() - startMillis;
    long ygc = TestUtil.getYoungGC() - startYGC;
    long fullGC = TestUtil.getFullGC() - startFullGC;

    System.out.println("thread " + threadCount + " " + name + " millis : "
            + NumberFormat.getInstance().format(millis) + "; YGC " + ygc + " FGC " + fullGC + " blocked "
            + NumberFormat.getInstance().format(blockedCount) //
            + " waited " + NumberFormat.getInstance().format(waitedCount) + " physicalConn "
            + physicalConnStat.get());

}

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

private void p0(final DataSource dataSource, String name, int threadCount) throws Exception {

    final CountDownLatch startLatch = new CountDownLatch(1);
    final CountDownLatch endLatch = new CountDownLatch(threadCount);
    final CountDownLatch dumpLatch = new CountDownLatch(1);

    Thread[] threads = new Thread[threadCount];
    for (int i = 0; i < threadCount; ++i) {
        Thread thread = new Thread() {

            public void run() {
                try {
                    startLatch.await();/*  ww w. j  a  va 2s  .co  m*/

                    for (int i = 0; i < LOOP_COUNT; ++i) {
                        Connection conn = dataSource.getConnection();
                        conn.close();
                    }
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
                endLatch.countDown();

                try {
                    dumpLatch.await();
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
        };
        threads[i] = thread;
        thread.start();
    }
    long startMillis = System.currentTimeMillis();
    long startYGC = TestUtil.getYoungGC();
    long startFullGC = TestUtil.getFullGC();
    startLatch.countDown();
    endLatch.await();

    long[] threadIdArray = new long[threads.length];
    for (int i = 0; i < threads.length; ++i) {
        threadIdArray[i] = threads[i].getId();
    }
    ThreadInfo[] threadInfoArray = ManagementFactory.getThreadMXBean().getThreadInfo(threadIdArray);

    dumpLatch.countDown();

    long blockedCount = 0;
    long waitedCount = 0;
    for (int i = 0; i < threadInfoArray.length; ++i) {
        ThreadInfo threadInfo = threadInfoArray[i];
        blockedCount += threadInfo.getBlockedCount();
        waitedCount += threadInfo.getWaitedCount();
    }

    long millis = System.currentTimeMillis() - startMillis;
    long ygc = TestUtil.getYoungGC() - startYGC;
    long fullGC = TestUtil.getFullGC() - startFullGC;

    System.out.println("thread " + threadCount + " " + name + " millis : "
            + NumberFormat.getInstance().format(millis) + "; YGC " + ygc + " FGC " + fullGC + " blocked "
            + NumberFormat.getInstance().format(blockedCount) //
            + " waited " + NumberFormat.getInstance().format(waitedCount) + " physicalConn "
            + physicalConnStat.get());

}

From source file:io.personium.diff.App.java

private Connection getMySqlConnection() {
    Connection conn = null;// ww w. j a va  2s . c o  m
    Properties mysqlProperties = new Properties();
    mysqlProperties.setProperty("driverClassName", "com.mysql.jdbc.Driver");
    mysqlProperties.setProperty("url", "jdbc:mysql://" + mysqlHost);
    mysqlProperties.setProperty("username", mysqlUser);
    mysqlProperties.setProperty("password", mysqlPassword);
    try {
        DataSource ds = BasicDataSourceFactory.createDataSource(mysqlProperties);
        conn = ds.getConnection();
    } catch (Exception e) {
        log.warn("Faild to connect MySQL");
        log.info(e.getMessage());
    }
    return conn;
}

From source file:ua.com.manometer.jasperreports.AbstractJasperReportsView.java

/**
 * Fill the given report using the given JDBC DataSource and model.
 *///from ww  w .jav  a 2  s .c om
private JasperPrint doFillReport(JasperReport report, Map<String, Object> model, DataSource ds)
        throws Exception {
    // Use the JDBC DataSource.
    if (logger.isDebugEnabled()) {
        logger.debug("Filling report using JDBC DataSource [" + ds + "]");
    }
    Connection con = ds.getConnection();
    try {
        return JasperFillManager.fillReport(report, model, con);
    } finally {
        try {
            con.close();
        } catch (Throwable ex) {
            logger.debug("Could not close JDBC Connection", ex);
        }
    }
}

From source file:com.intuit.it.billing.data.BillingDAOImpl.java

Connection getConnection() throws NamingException, SQLException {
    Context initContext = null;//from   w w  w. j  a  va  2  s  . co  m
    Context envContext = null;
    Connection conn = null;
    DataSource ds = null;

    initContext = new InitialContext();
    envContext = (Context) initContext.lookup("java:/comp/env");
    ds = (DataSource) envContext.lookup("datasources/OracleDS");
    conn = ds.getConnection();

    return conn;
}

From source file:com.alfaariss.oa.engine.requestor.jdbc.JDBCRequestorPool.java

private void addRequestors(DataSource oDataSource, String sPoolsTable, String sRequestorsTable,
        String sRequestorPropertiesTable) throws RequestorException {
    Connection oConnection = null;
    PreparedStatement oPreparedStatement = null;
    ResultSet rsRequestor = null;
    ResultSet rsProperties = null;

    /** Map from RequestorId->__Requestor */
    Map<String, __Requestor> mRequestors = new HashMap<String, __Requestor>();

    try {//from ww  w. j  a v a 2s .c  o m
        oConnection = oDataSource.getConnection();

        // Get the Requestors from the Pool, order by RequestorId:
        //  SELECT (requestorpool_requestor).* 
        //  FROM (requestorpool_requestor),(requestorpool_pool) 
        //  WHERE (requestorpool_requestor.pool_id)=? 
        //  AND (requestorpool_requestor).pool_id=(requestorpool_pool).id 
        //  ORDER BY (requestorpool_requestor).id
        StringBuffer sbSelect = new StringBuffer("SELECT ");
        sbSelect.append(sRequestorsTable).append(".*");
        sbSelect.append(" FROM ");
        sbSelect.append(sRequestorsTable);
        sbSelect.append(",");
        sbSelect.append(sPoolsTable);
        sbSelect.append(" WHERE ");
        sbSelect.append(sRequestorsTable).append(".").append(JDBCRequestor.COLUMN_POOLID).append("=? AND ");
        sbSelect.append(sRequestorsTable).append(".").append(JDBCRequestor.COLUMN_POOLID).append("=")
                .append(sPoolsTable).append(".").append(JDBCRequestorPool.COLUMN_ID);
        sbSelect.append(" ORDER BY ").append(sRequestorsTable).append(".").append(JDBCRequestor.COLUMN_ID);

        // Get the properties of all the requestors, order by RequestorId
        //  SELECT (requestorpool_requestor).id, (requestorpool_requestor_properties).name, (requestorpool_requestor_properties).value
        //  FROM
        //  (requestorpool_pool) INNER JOIN (requestorpool_requestor) ON (requestorpool_requestor).pool_id=(requestorpool_pool).id
        //  LEFT OUTER JOIN (requestorpool_requestor_properties) ON (requestorpool_requestor).id=(requestorpool_requestor_properties).requestor_id
        //  WHERE (requestorpool_requestor).pool_id=?
        //  ORDER BY (requestorpool_requestor).id
        // Assert indexes are available on 
        // - requestorpool_requestor.pool_id
        // - requestorpool_requestor_properties.requestor_id

        StringBuffer sbSelectProperties = new StringBuffer("SELECT ");
        sbSelectProperties.append(sRequestorsTable).append(".").append(JDBCRequestor.COLUMN_ID).append(", ");
        sbSelectProperties.append(sRequestorPropertiesTable).append(".")
                .append(JDBCRequestor.COLUMN_PROPERTY_NAME).append(", ");
        sbSelectProperties.append(sRequestorPropertiesTable).append(".")
                .append(JDBCRequestor.COLUMN_PROPERTY_VALUE);
        sbSelectProperties.append(" FROM ");
        sbSelectProperties.append(sPoolsTable).append(" INNER JOIN ");
        sbSelectProperties.append(sRequestorsTable).append(" ON ").append(sRequestorsTable).append(".")
                .append(JDBCRequestor.COLUMN_POOLID).append("=").append(sPoolsTable).append(".")
                .append(JDBCRequestorPool.COLUMN_ID);
        sbSelectProperties.append(" LEFT OUTER JOIN ").append(sRequestorPropertiesTable).append(" ON ")
                .append(sRequestorsTable).append(".").append(JDBCRequestor.COLUMN_ID).append("=")
                .append(sRequestorPropertiesTable).append(".")
                .append(JDBCRequestor.COLUMN_PROPERTY_REQUESTOR_ID);
        sbSelectProperties.append(" WHERE ");
        sbSelectProperties.append(sRequestorsTable).append(".").append(JDBCRequestor.COLUMN_POOLID)
                .append("=?");
        sbSelectProperties.append(" ORDER BY ").append(sRequestorsTable).append(".")
                .append(JDBCRequestor.COLUMN_ID);

        // Perform database queries
        oPreparedStatement = oConnection.prepareStatement(sbSelect.toString());
        oPreparedStatement.setString(1, _sID);

        if (_logger.isTraceEnabled()) {
            _logger.trace("SQL(1): " + sbSelect.toString() + " [params: " + _sID + "]");
        }

        rsRequestor = oPreparedStatement.executeQuery();

        boolean dateLastModifiedExists = true;

        // Create Requestors in Map:
        while (rsRequestor.next()) {
            String sID = rsRequestor.getString(JDBCRequestor.COLUMN_ID);
            String sFriendlyName = rsRequestor.getString(JDBCRequestor.COLUMN_FRIENDLYNAME);
            boolean bEnabled = rsRequestor.getBoolean(JDBCRequestor.COLUMN_ENABLED);

            // Implement date_last_modified column as optional
            Date dLastModified = null;
            if (dateLastModifiedExists) {
                try {
                    dLastModified = rsRequestor.getTimestamp(JDBCRequestor.COLUMN_DATELASTMODIFIED);
                } catch (Exception e) {
                    _logger.info("No " + JDBCRequestor.COLUMN_DATELASTMODIFIED + " column found; ignoring.");
                    dateLastModifiedExists = false;
                }
            }

            __Requestor oRequestor = new __Requestor(sID, sFriendlyName, bEnabled, dLastModified);
            mRequestors.put(sID, oRequestor);
        }

        // Now add properties to Requestors in Map 
        oPreparedStatement = oConnection.prepareStatement(sbSelectProperties.toString());
        oPreparedStatement.setString(1, _sID);

        if (_logger.isTraceEnabled()) {
            _logger.trace("SQL(2): " + sbSelectProperties.toString() + " [params: " + _sID + "]");
        }

        rsProperties = oPreparedStatement.executeQuery();

        __Requestor oCurReq = null;
        while (rsProperties.next()) {
            String sID = rsProperties.getString(JDBCRequestor.COLUMN_ID);
            String sKey = rsProperties.getString(JDBCRequestor.COLUMN_PROPERTY_NAME);
            String sValue = rsProperties.getString(JDBCRequestor.COLUMN_PROPERTY_VALUE);

            if (oCurReq == null || (!oCurReq._sID.equals(sID))) {
                if (oCurReq != null) {
                    addRequestor(oCurReq.toRequestor());
                }
                oCurReq = mRequestors.get(sID);

                assert (oCurReq != null); // or database would have returned inconsistent results..
            }

            if (sKey != null) {
                oCurReq._oProps.put(sKey, sValue);
            }
        }

        // Also add the last one:
        if (oCurReq != null) {
            addRequestor(oCurReq.toRequestor());
        }

        // That's it.
    } catch (SQLException e) {
        _logger.error("Can not read from database", e);
        throw new RequestorException(SystemErrors.ERROR_RESOURCE_RETRIEVE);
    } catch (Exception e) {
        _logger.fatal("Internal error during retrieval of requestors", e);
        throw new RequestorException(SystemErrors.ERROR_INTERNAL);
    } finally {
        try {
            if (rsProperties != null)
                rsProperties.close();
            if (rsRequestor != null)
                rsRequestor.close();
        } catch (Exception e) {
            _logger.error("Could not close resultset", e);
        }

        try {
            if (oPreparedStatement != null)
                oPreparedStatement.close();
        } catch (Exception e) {
            _logger.error("Could not close statement", e);
        }

        try {
            if (oConnection != null)
                oConnection.close();
        } catch (Exception e) {
            _logger.error("Could not close connection", e);
        }
    }

}

From source file:com.taobao.diamond.server.service.DefaultPersistService.java

public void initDataSource(DataSource dataSource) {
    System.out.println("#initDataSource");
    if (dataSource == null) {
        System.out.println("dataSource is null.");
    }/*www  .  j a v a 2 s  .  c  o  m*/
    String mode = System.getProperty("diamond.server.mode");
    System.out.println("-Ddiamond.server.mode:" + mode);
    String[] attrNames = new String[] { "offline", "flying", "lostDB", "xx" };
    for (String key : attrNames) {
        if (key.equals(mode)) {
            SystemConfig.setOffline();
        }
    }
    int timeout = 3;// seconds
    boolean dsValid = false;
    Connection conn = null;
    if (dataSource != null)
        try {
            BasicDataSource bds = (BasicDataSource) dataSource;
            bds.isPoolPreparedStatements();
            conn = dataSource.getConnection();

            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(timeout);
            dsValid = true;
            try {
                ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM config_info");
                if (rs.next()) {
                    rs.getInt(1);
                } else {
                    dsValid = false;
                }
                rs = stmt.executeQuery("select count(*) from group_info");
                if (rs.next()) {
                    rs.getInt(1);
                } else {
                    dsValid = false;
                }
            } catch (Exception e) {
                dsValid = false;
            }

        } catch (Throwable t) {
            log.error(t.getMessage(), t.getCause());
        }

    if (dsValid == false) {
        // 
        if (SystemConfig.isOnlineMode()) {
            System.out.println("#########################################################");
            System.out.println("DataSource .");
            System.out.println("error occured in DataSource initilizing,connection timeout or refuse conn.");
            System.out.println("#########################################################");
            SystemConfig.system_pause();
            System.exit(0);
        }
        // 
        if (SystemConfig.isOfflineMode()) {
            String msg = "#########################################################";
            System.out.println(msg);
            log.info(msg);
            OfflinePersistService ps = new OfflinePersistService();
            persistService = ps;

        }
    } else {
        DBPersistService ps = new DBPersistService();
        ps.setDataSource(dataSource);
        persistService = ps;
    }
    System.out.println("#########################################################");
    System.out.println("Current Persist Service");
    System.out.println("persistService:" + persistService);
    System.out.println("DBPersistService:" + (persistService instanceof DBPersistService));
    System.out.println("OfflinePersistService:" + (persistService instanceof OfflinePersistService));
    System.out.println("#########################################################");
}