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.ibm.bluemix.mobilestarterkit.service.ServiceAPI.java

private void saveLog(String PAGE_ADDRESS, String IP_ADDRESS, String BROWSER) {
    System.out.println("saveLog () ");
    /*/*from   w  w  w  . ja  v a  2  s  . co  m*/
     * Look up DB for dashDB to connect with VCAP_Service
     * 
     */
    String lookupName = null;
    try {
        com.ibm.json.java.JSONObject vcap = getVcapServices();
        if (vcap.get("dashDB") != null) {
            com.ibm.json.java.JSONObject dashDB0 = (com.ibm.json.java.JSONObject) ((com.ibm.json.java.JSONArray) vcap
                    .get("dashDB")).get(0);
            String luName = (String) dashDB0.get("name");
            if (luName != null) {
                lookupName = "jdbc/" + luName;
            }
        }

    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    DataSource dataSource = null;

    try {
        javax.naming.Context ctx = new InitialContext();
        if (lookupName != null) {
            dataSource = (DataSource) ctx.lookup(lookupName);
        } else {
            dataSource = (DataSource) ctx.lookup("jdbc/dashDB-microsite");
        }

    } catch (NamingException e) {
        e.printStackTrace();
    }

    /*
     * Check if USER_LOG table exists in DashDB
     * 
     */

    try {
        Connection conn = dataSource.getConnection();
        PreparedStatement stmt = conn
                .prepareStatement("SELECT TABNAME,TABSCHEMA FROM SYSCAT.TABLES where TABNAME = 'USER_LOG'");
        ResultSet rs = stmt.executeQuery();

        boolean tableExist = false;

        if (rs != null) {
            while (rs.next()) {
                System.out.println("table name : " + rs.getString(1));
                if (rs.getString(1).equals("USER_LOG")) {
                    tableExist = true;
                }
            }
        } else {
            tableExist = false;
        }

        /*
         * If there is no table, create it.
         * 
         */
        if (!tableExist) {
            System.out.println("table NOT exist ");

            Statement crtStatement = conn.createStatement();
            String crtSql = "CREATE TABLE USER_LOG(PAGE_ADDRESS VARCHAR (200), IP_ADDRESS VARCHAR (200), BROWSER CHAR (200), ACCESS_TIME TIMESTAMP) organize by row";
            crtStatement.executeUpdate(crtSql);

            System.out.println("Create done!!");
        }

        /*
         * Insert Log information
         * 
         */
        Statement insertStatement = conn.createStatement();
        String insertSql = "INSERT INTO USER_LOG (PAGE_ADDRESS, IP_ADDRESS, BROWSER, ACCESS_TIME) VALUES ('"
                + PAGE_ADDRESS + "','" + IP_ADDRESS + "','" + BROWSER + "',  CURRENT TIMESTAMP)";
        insertStatement.executeUpdate(insertSql);

    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:io.cloudslang.content.database.services.dbconnection.DBConnectionManager.java

/**
 * @param aDbType   one of the supported db type, for example ORACLE, NETCOOL
 * @param aDbUrl    connection url//from  ww  w . j a v a  2s .c o m
 * @param aUsername username to connect to db
 * @param aPassword password to connect to db
 * @return a db Connection which is pooled
 * @throws SQLException
 */
protected Connection getPooledConnection(DBType aDbType, String aDbUrl, String aUsername, String aPassword)
        throws SQLException {
    Connection retCon;

    //key to hashtable of datasources for that dbms
    String dbmsKey = aDbType + "." + aDbUrl;

    if (dbmsPoolTable.containsKey(dbmsKey)) {

        //each pool has pooled datasources, pool is based on dbUrl
        //so we can control the total size of connection to dbms
        Hashtable<String, DataSource> dsTable = dbmsPoolTable.get(dbmsKey);

        String encryptedPass;
        try {
            encryptedPass = TripleDES.encryptPassword(aPassword);
        } catch (Exception e) {
            throw new SQLException("Failed to encrypt password for key = " + dbmsKey, e);
        }

        String dsTableKey = aDbUrl + "." + aUsername + "." + encryptedPass;

        DataSource ds;
        if (dsTable.containsKey(dsTableKey)) {

            ds = dsTable.get(dsTableKey);
            retCon = ds.getConnection();
        } else {

            //need to check if it is ok to create another ds
            ds = this.createDataSource(aDbType, aDbUrl, aUsername, aPassword, dsTable);
            retCon = ds.getConnection();

            dsTable.put(dsTableKey, ds);
        }
    } else//don't have dbmsKey, will create one for that dbtype.dburl
    {

        //just create, don't need to check, since we don't have this dbmsKey
        PooledDataSource ds = (PooledDataSource) this.createDataSource(aDbType, aDbUrl, aUsername, aPassword);
        retCon = getPooledConnection(ds, aUsername, aPassword);

        Hashtable<String, DataSource> dsTable = new Hashtable<>();
        String encryptedPass;
        try {
            encryptedPass = TripleDES.encryptPassword(aPassword);
        } catch (Exception e) {
            throw new SQLException("Failed to encrypt password for key = " + dbmsKey, e);
        }
        String dsTableKey = aDbUrl + "." + aUsername + "." + encryptedPass;
        dsTable.put(dsTableKey, ds);
        dbmsPoolTable.put(dbmsKey, dsTable);
    }
    return retCon;
}

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

/**
 * initialze min value of sequence//www. j  a  v a2  s. co m
 * 
 * @param tableName
 *            table name
 * @param nextId
 *            next id
 */
private void initializeNextLongId(String tableName, long nextId) {
    try {
        DataSource dataSource = (DataSource) applicationContext.getBean("util_datasource");
        Connection conn = dataSource.getConnection();
        try {
            Statement statement = conn.createStatement();

            statement.executeUpdate(
                    "INSERT INTO idstest (table_name, next_id) VALUES ('" + tableName + "', " + nextId + ")");
        } finally {
            conn.close();
        }
    } catch (Exception e) {
        System.err.println("Unable to initialize next_id." + e);
        fail("Unable to initialize next_id. " + e);
    }
}

From source file:gobblin.writer.initializer.JdbcWriterInitializer.java

/**
 * Creating JDBC connection using publisher's connection information. It is OK to use publisher's information
 * as JdbcWriter is coupled with JdbcPublisher.
 *
 * @return JDBC Connection//w w w.ja  v  a 2 s  .com
 * @throws SQLException
 */
@VisibleForTesting
public Connection createConnection() throws SQLException {
    DataSource dataSource = DataSourceBuilder.builder()
            .url(this.state.getProp(JdbcPublisher.JDBC_PUBLISHER_URL))
            .driver(this.state.getProp(JdbcPublisher.JDBC_PUBLISHER_DRIVER))
            .userName(this.state.getProp(JdbcPublisher.JDBC_PUBLISHER_USERNAME))
            .passWord(this.state.getProp(JdbcPublisher.JDBC_PUBLISHER_PASSWORD))
            .cryptoKeyLocation(this.state.getProp(JdbcPublisher.JDBC_PUBLISHER_ENCRYPTION_KEY_LOC))
            .maxActiveConnections(1).maxIdleConnections(1).state(this.state).build();

    return dataSource.getConnection();
}

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

/**
 * initialze next BigDecimal Id/*  w w  w.  j  ava 2s.  co m*/
 * 
 * @param tableName
 *            table name
 * @param nextId
 *            next id
 */
private void initializeNextBigDecimalId(String tableName, BigDecimal nextId) {
    try {
        DataSource dataSource = (DataSource) applicationContext.getBean("util_datasource");
        Connection conn = dataSource.getConnection();
        try {
            Statement statement = conn.createStatement();

            // Need to quote the BigDecimal as it
            // is larger than normal
            // numbers can be.
            // Was causing problems with MySQL
            statement.executeUpdate("INSERT INTO idstest (table_name, next_id) VALUES ('" + tableName + "', '"
                    + nextId.toString() + "')");
        } finally {
            conn.close();
        }
    } catch (Exception e) {
        System.err.println("Unable to initialize next_id." + e);
        fail("Unable to initialize next_id. " + e);
    }
}

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

protected ModelAndView handleContext(String contextName, Context context, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    String resourceName = ServletRequestUtils.getStringParameter(request, "resource");
    DataSource dataSource = null;

    try {//w  ww .j av  a2  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 }));
    }

    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?
            Connection conn = dataSource.getConnection();
            try {
                DatabaseMetaData md = conn.getMetaData();

                List 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);
            } finally {
                conn.close();
            }
        } 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

/**
 * get next BigDecimal id using query directly.
 * //from w  w w  .j a  va2 s  .  co m
 * @param tableName
 *            table name
 * @return next BigDecimal Id
 */
private BigDecimal peekNextBigDecimalId(String tableName) {
    try {
        DataSource dataSource = (DataSource) applicationContext.getBean("util_datasource");
        Connection conn = dataSource.getConnection();
        try {
            Statement statement = conn.createStatement();

            ResultSet rs = statement
                    .executeQuery("SELECT next_id FROM idstest " + "WHERE table_name = '" + tableName + "'");
            if (rs.next()) {
                return rs.getBigDecimal(1);
            } else {
                fail(tableName + " row not in ids table.");
                return null; // for compiler
            }
        } finally {
            conn.close();
        }
    } catch (Exception e) {
        System.err.println("Unable to peek next_id." + e);
        fail("Unable to peek next_id. " + e);
        return null; // for compiler
    }
}

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

/**
 * get next Long id using query directly.
 * //ww  w.j av  a  2s  .c  o m
 * @param tableName
 *            table name
 * @return next Long Id
 */
private long peekNextLongId(String tableName) {
    try {
        DataSource dataSource = (DataSource) applicationContext.getBean("util_datasource");
        Connection conn = dataSource.getConnection();
        try {
            Statement statement = conn.createStatement();

            ResultSet rs = statement
                    .executeQuery("SELECT next_id FROM idstest " + "WHERE table_name = '" + tableName + "'");
            if (rs.next()) {
                return rs.getLong(1);
            } else {
                fail(tableName + " row not in ids table.");
                return -1; // for compiler
            }
        } finally {
            conn.close();
        }
    } catch (Exception e) {
        System.err.println("Unable to peek next_id." + e);
        fail("Unable to peek next_id. " + e);
        return -1; // for compiler
    }
}

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

/**
 * Tests to see whether or not the current DataSource supports BigDecimal
 * //from  w w w .j  av a  2  s  .c  o m
 * @return boolean check bigcimal implemented
 */
private boolean isBigDecimalImplemented() {
    String tableName = "foorbar_table";

    // Add a row that can be selected.
    initializeNextLongId(tableName, 1);

    try {
        DataSource dataSource = (DataSource) applicationContext.getBean("util_datasource");
        Connection conn = dataSource.getConnection();
        try {
            Statement statement = conn.createStatement();

            ResultSet rs = statement
                    .executeQuery("SELECT next_id FROM idstest " + "WHERE table_name = '" + tableName + "'");
            if (rs.next()) {
                rs.getBigDecimal(1);
            } else {
                fail(tableName + " row not in ids table.");
                return false; // for compiler
            }
        } finally {
            conn.close();
        }

        // Implemented
        return true;
    } catch (Exception e) {
        if (e.toString().toLowerCase().indexOf("implemented") > 0) {
            // Not implemented
            return false;
        }
        System.err.println("Unable to test for BigDecimal support." + e);
        fail("Unable to test for BigDecimal support. " + e);
        return false; // for compiler
    }
}

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

@After
public void onTearDown() throws Exception {
    DataSource dataSource = (DataSource) applicationContext.getBean("util_datasource");
    try {/*  w  w  w. ja  v  a 2s  .c om*/
        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("my_ids drop fail.");
                e.printStackTrace();
            }
        } finally {
            conn.close();
        }
    } catch (SQLException e) {
        System.err.println("Unable to initialize database for test." + e);
        fail("Unable to initialize database for test. " + e);
    }
}