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.alibaba.cobar.client.CobarSqlMapClientTemplate.java

protected Object executeWith(DataSource dataSource, SqlMapClientCallback action) {
    SqlMapSession session = getSqlMapClient().openSession();

    try {//from   w  w  w .  j a  v a2s .c  om
        Connection springCon = null;
        boolean transactionAware = (dataSource instanceof TransactionAwareDataSourceProxy);

        // Obtain JDBC Connection to operate on...
        try {
            springCon = (transactionAware ? dataSource.getConnection()
                    : DataSourceUtils.doGetConnection(dataSource));
            session.setUserConnection(springCon);
        } catch (SQLException ex) {
            throw new CannotGetJdbcConnectionException("Could not get JDBC Connection", ex);
        }

        try {
            return action.doInSqlMapClient(session);
        } catch (SQLException ex) {
            throw new SQLErrorCodeSQLExceptionTranslator().translate("SqlMapClient operation", null, ex);
        } catch (Throwable t) {
            throw new UncategorizedCobarClientException(
                    "unknown excepton when performing data access operation.", t);
        } finally {
            try {
                if (springCon != null) {
                    if (transactionAware) {
                        springCon.close();
                    } else {
                        DataSourceUtils.doReleaseConnection(springCon, dataSource);
                    }
                }
            } catch (Throwable ex) {
                logger.debug("Could not close JDBC Connection", ex);
            }
        }
        // Processing finished - potentially session still to be closed.
    } finally {
        session.close();
    }
}

From source file:net.refractions.udig.catalog.ui.wizard.DataBaseRegistryWizardPage.java

/**
 * This method is called in response to selection of the lookup button and gets the names of the
 * available databases on the DBMS server. Instead of overriding this method it is better to
 * override the getDatabaseResultSet method if a custom way of getting the database names is
 * needed/* w  ww .j ava  2s .  com*/
 * 
 * @param con the java.sql.Connection returned by getConnection()
 * @return An array of Strings containing the names of only the databases available on the server
 *         which are suitable for display to the user.
 */
protected String[] lookupDbNamesForDisplay(DataSource dataSource) {
    java.util.List<String> dbList = new ArrayList<String>();
    Connection con = null;
    try {
        con = dataSource.getConnection();
        ResultSet rs = null;
        if (con != null) {
            rs = getDatabaseResultSet(con);
            while (rs.next()) {
                String dbName = rs.getString(1);
                if (!excludeDbFromUserChoices(dbName)) {
                    dbList.add(dbName);
                }
            }
        }
        return dbList.toArray(new String[dbList.size()]);
    } catch (SQLException e) {
        setMessage(Messages.DataBaseRegistryWizardPage_databaseMessage);
        setErrorMessage(e.getLocalizedMessage());
        return null;
    } finally {
        if (con != null) {
            try {
                con.close(); // return to pool
            } catch (SQLException e) {
                // closing anyways
            }
        }
    }
}

From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java

@Override
public void createTemporaryTable(int adminID, int datasource_id, String keyColumn, List<String> keyColumns,
        int companyId, String sessionId) {
    final DataSource dataSource = (DataSource) applicationContext.getBean("dataSource");
    try {//from   w  w w. jav  a  2 s  .  co  m
        if (temporaryConnection != null) {
            temporaryConnection.destroy();
            temporaryConnection = null;
        }
        SingleConnectionDataSource scds = null;
        scds = new SingleConnectionDataSource(dataSource.getConnection(), true);
        setTemporaryConnection(scds);
    } catch (SQLException e) {
        throw new DataAccessResourceFailureException("Unable to create single connection data source", e);
    }

    final JdbcTemplate template = getJdbcTemplateForTemporaryTable();
    final String prefix = "cust_" + adminID + "_tmp_";
    final String tableName = prefix + datasource_id + "_tbl";

    String indexSql = "";
    String duplicateSql = "";
    if (keyColumns.isEmpty()) {
        duplicateSql += keyColumn + " as column_duplicate_check_0 ";
        indexSql = "column_duplicate_check_0";
    } else {
        for (int i = 0; i < keyColumns.size(); i++) {
            duplicateSql += keyColumns.get(i) + " as column_duplicate_check_" + i;
            indexSql += "column_duplicate_check_" + i;
            if (i != keyColumns.size() - 1) {
                duplicateSql += ", ";
                indexSql += ", ";
            }
        }
    }
    duplicateSql += " from customer_" + companyId + "_tbl where 1=0)";

    if (AgnUtils.isMySQLDB()) {
        String query = "CREATE TEMPORARY TABLE IF NOT EXISTS " + tableName + " as (select ";
        query += duplicateSql;
        template.execute(query);
        query = "ALTER TABLE " + tableName + " ADD (recipient mediumblob NOT NULL, "
                + "validator_result mediumblob NOT NULL, " + "temporary_id varchar(128) NOT NULL, " + "INDEX ("
                + indexSql + "), " + "status_type int(3) NOT NULL)";
        template.execute(query);
        query = "alter table " + tableName + " collate utf8_unicode_ci";
        template.execute(query);
    } else if (AgnUtils.isOracleDB()) {
        // @todo: we need to decide when all those tables will be removed
        String query = "CREATE TABLE " + tableName + " as (select ";
        query += duplicateSql;
        template.execute(query);
        query = "ALTER TABLE " + tableName + " ADD (recipient blob NOT NULL, "
                + "validator_result blob NOT NULL, " + "temporary_id varchar2(128) NOT NULL, "
                + "status_type number(3) NOT NULL)";
        template.execute(query);
        String indexquery = "create index " + tableName + "_cdc on " + tableName + " (" + indexSql
                + ") nologging";
        template.execute(indexquery);
        query = " INSERT INTO IMPORT_TEMPORARY_TABLES (SESSION_ID, TEMPORARY_TABLE_NAME) VALUES('" + sessionId
                + "', '" + tableName + "')";
        template.execute(query);
    }
}

From source file:org.apache.zeppelin.rest.GetUserList.java

/**
 * function to extract users from JDBCs//from   ww  w.ja v  a  2 s  .  c  o  m
 */
public List<String> getUserList(JdbcRealm obj) {
    List<String> userlist = new ArrayList<>();
    PreparedStatement ps = null;
    ResultSet rs = null;
    DataSource dataSource = null;
    String authQuery = "";
    String retval[];
    String tablename = "";
    String username = "";
    String userquery = "";
    try {
        dataSource = (DataSource) FieldUtils.readField(obj, "dataSource", true);
        authQuery = (String) FieldUtils.readField(obj, "DEFAULT_AUTHENTICATION_QUERY", true);
        LOG.info(authQuery);
        String authQueryLowerCase = authQuery.toLowerCase();
        retval = authQueryLowerCase.split("from", 2);
        if (retval.length >= 2) {
            retval = retval[1].split("with|where", 2);
            tablename = retval[0];
            retval = retval[1].split("where", 2);
            if (retval.length >= 2)
                retval = retval[1].split("=", 2);
            else
                retval = retval[0].split("=", 2);
            username = retval[0];
        }

        if (StringUtils.isBlank(username) || StringUtils.isBlank(tablename)) {
            return userlist;
        }

        userquery = "select " + username + " from " + tablename;

    } catch (IllegalAccessException e) {
        LOG.error("Error while accessing dataSource for JDBC Realm", e);
        return null;
    }

    try {
        Connection con = dataSource.getConnection();
        ps = con.prepareStatement(userquery);
        rs = ps.executeQuery();
        while (rs.next()) {
            userlist.add(rs.getString(1).trim());
        }
    } catch (Exception e) {
        LOG.error("Error retrieving User list from JDBC Realm", e);
    } finally {
        JdbcUtils.closeResultSet(rs);
        JdbcUtils.closeStatement(ps);
    }
    return userlist;
}

From source file:com.draagon.meta.manager.db.ObjectManagerDB.java

/**
 * Retrieves a connection object representing the datastore
 *///from   w  w  w . j  a  va2  s .  c  o  m
public ObjectConnection getConnection() {
    DataSource ds = getDataSource();
    if (ds == null) {
        throw new IllegalArgumentException(
                "No DataSource was specified for this ObjectManager, cannot request connection");
    }

    Connection c;
    try {
        c = ds.getConnection();
    } catch (SQLException e) {
        throw new RuntimeException("Could not retrieve a connection from the datasource: " + e.getMessage(), e);
    }

    return new ObjectConnectionDB(c);
}

From source file:net.refractions.udig.catalog.ui.wizard.DataBaseRegistryWizardPage.java

/**
 * This method is called in response to selection of the lookup button and gets the names of the
 * available schemata on the DBMS server. Like lookupDbNameForDisplay it is better to avoid
 * overiding this method if a database specific implementation is needed. Instead overide
 * getSchemaResultSet WARNING: This should never be called if !dbmsUsesSchema().
 * /*from  w  ww. java  2s .  c  om*/
 * @param con the java.sql.Connection object returned by getConnection()
 * @return An array of Strings containing the names of the schemata available on the server.
 */
protected String[] lookupSchemaNamesForDisplay(DataSource dataSource) {
    if (dataSource == null) {
        return null; // not connected
    }
    Connection con = null;
    java.util.List<String> schemaList = new ArrayList<String>();
    try {
        con = dataSource.getConnection();
        ResultSet rs = null;
        if (con != null) {
            rs = getSchemasResultSet(con);
            while (rs.next()) {
                String schemaName = rs.getString(1);
                if (!excludeSchemaFromUserChoices(schemaName)) {
                    schemaList.add(schemaName);
                }
            }
        }
        return schemaList.toArray(new String[schemaList.size()]);
    } catch (SQLException e) {
        setMessage(Messages.DataBaseRegistryWizardPage_schemaMessage);
        setErrorMessage(e.getLocalizedMessage());
        return null;
    } finally {
        if (con != null) {
            try {
                con.close(); // return to pool
            } catch (SQLException e) {
                // we are closing anyways - ignore
            }
        }
    }
}

From source file:com.inverse2.ajaxtoaster.AjaxToasterServlet.java

public ServiceOperationInterface getServiceScript(String scriptName) throws Exception {

    ServletContext context = getServletContext();
    ServicePool pool = null;/*  www. j  a v  a  2  s  .c  o m*/
    ServiceOperationInterface toaster = null;
    Connection connection = null;
    DBConnectionPool dbpool = null;
    String db_jndi_name = null;
    String db_jdbc_poolname = null;

    try {
        pool = (ServicePool) context.getAttribute(ATTRIB_SERVICE_POOL);
    } catch (Exception ex) {
        log.error("Error getting the toaster pool from the servlet context [" + ATTRIB_SERVICE_POOL + "]");
        throw new Exception("ERROR - could not get toast pool from server context [" + ATTRIB_SERVICE_POOL
                + "]: " + ex.toString(), ex);
    }

    if (pool == null) {
        /* Toaster pool is not set-up - this is very bad...! */
        log.error("The toaster pool does not exists in the servlet context.");
        throw new Exception("The toaster pool does not exist in the servlet context.");
    }

    try {
        toaster = pool.getService(scriptName);
    } catch (Exception ex) {
        log.error("Exception getting a toaster instance: " + ex.toString());
        throw new Exception(
                "ERROR - could not get toaster instance from pool for " + scriptName + ": " + ex.toString(),
                ex);
    }

    /**
     * DATABASE CONNECTION...
     *   if the DbJDNI property is set to non null value, or the default connection is a JNDI one,
     *   then try to use JNDI.
     */

    println(">> Starting database connection");

    db_jndi_name = toaster.getDbJNDI(); // get jndi name from the scripts' properties file (if any)
    db_jdbc_poolname = toaster.getJDBCpoolname(); // get jdbc name from the scripts' properties file (if any)

    if (db_jndi_name != null || default_db_jndi_name != null) {

        // JNDI - Container managed connection pool.
        // Lookup the JNDI name for the connection pool and create the database connection.

        if (db_jndi_name == null) {
            db_jndi_name = default_db_jndi_name;
        }

        println("Looking up database connection for JNDI name " + db_jndi_name + "... "
                + "An exception here probably indicates that the JNDI name or corresponing connection pool isn't setup on your application server.");

        try {
            // The following code for Websphere 6...
            InitialContext ic = new InitialContext();
            DataSource dataSource = null;

            dataSource = (DataSource) ic.lookup(db_jndi_name);
            connection = dataSource.getConnection();
        } catch (Exception ex) {
            log.error("Exception getting JNDI database connection: " + ex.toString());
            throw new Exception("ERROR - getting a connection to the database (using JNDI name " + db_jndi_name
                    + "): " + ex.toString(), ex);
        }

    } else if (db_jdbc_poolname != null || default_db_jdbc_name != null) {

        if (db_jdbc_poolname == null) {
            db_jdbc_poolname = default_db_jdbc_name; // it isn't, so use the default one.
        }

        // find the connection pool in the servlet context
        try {
            String attr = ATTRIB_JDBC_CONN_POOL + "." + db_jdbc_poolname;
            println(">> Trying to get DB connection pool (" + attr + ")");
            dbpool = (DBConnectionPool) context.getAttribute(attr);
        } catch (Exception ex) {
            log.error("Could not get a database connection from the pool: " + ex.toString());
            throw new Exception("ERROR - could not get DB connection pool from server context ("
                    + ATTRIB_JDBC_CONN_POOL + "." + db_jdbc_poolname + ") " + ex.toString(), ex);
        }

        if (dbpool == null) {
            if (toaster != null) {
                pool.freeService(scriptName, toaster);
            }
            throw new Exception("ERROR - The database connection pool has not been created (dbpool for "
                    + ATTRIB_JDBC_CONN_POOL + "." + db_jdbc_poolname + " is null).");
        }
        try {
            connection = dbpool.getConnection();
        } catch (Exception ex) {
            if (toaster != null) {
                pool.freeService(scriptName, toaster);
            }
            throw new Exception("ERROR - could not get DB connection from DB connection pool. " + ex.toString(),
                    ex);
        }

    }

    /*
     *  Give the Toaster the database connection we created earlier...
     */
    toaster.setConnection(connection);

    return (toaster);
}

From source file:gobblin.metastore.MysqlStateStore.java

/**
 * Manages the persistence and retrieval of {@link State} in a MySQL database
 * @param dataSource the {@link DataSource} object for connecting to MySQL
 * @param stateStoreTableName the table for storing the state in rows keyed by two levels (store_name, table_name)
 * @param compressedValues should values be compressed for storage?
 * @param stateClass class of the {@link State}s stored in this state store
 * @throws IOException/*from w  w w.  ja v  a  2  s . c o  m*/
 */
public MysqlStateStore(DataSource dataSource, String stateStoreTableName, boolean compressedValues,
        Class<T> stateClass) throws IOException {
    this.dataSource = dataSource;
    this.stateClass = stateClass;
    this.compressedValues = compressedValues;

    UPSERT_JOB_STATE_SQL = UPSERT_JOB_STATE_TEMPLATE.replace("$TABLE$", stateStoreTableName);
    SELECT_JOB_STATE_SQL = SELECT_JOB_STATE_TEMPLATE.replace("$TABLE$", stateStoreTableName);
    SELECT_JOB_STATE_WITH_LIKE_SQL = SELECT_JOB_STATE_WITH_LIKE_TEMPLATE.replace("$TABLE$",
            stateStoreTableName);
    SELECT_JOB_STATE_EXISTS_SQL = SELECT_JOB_STATE_EXISTS_TEMPLATE.replace("$TABLE$", stateStoreTableName);
    SELECT_JOB_STATE_NAMES_SQL = SELECT_JOB_STATE_NAMES_TEMPLATE.replace("$TABLE$", stateStoreTableName);
    DELETE_JOB_STORE_SQL = DELETE_JOB_STORE_TEMPLATE.replace("$TABLE$", stateStoreTableName);
    DELETE_JOB_STATE_SQL = DELETE_JOB_STATE_TEMPLATE.replace("$TABLE$", stateStoreTableName);
    CLONE_JOB_STATE_SQL = CLONE_JOB_STATE_TEMPLATE.replace("$TABLE$", stateStoreTableName);

    // create table if it does not exist
    String createJobTable = CREATE_JOB_STATE_TABLE_TEMPLATE.replace("$TABLE$", stateStoreTableName);
    try (Connection connection = dataSource.getConnection();
            PreparedStatement createStatement = connection.prepareStatement(createJobTable)) {
        createStatement.executeUpdate();
    } catch (SQLException e) {
        throw new IOException("Failure creation table " + stateStoreTableName, e);
    }
}

From source file:net.sourceforge.msscodefactory.v1_10.MSSBamPg8.MSSBamPg8Schema.java

public boolean connect() {
    final String S_ProcName = "connect";
    if (cnx != null) {
        return (false);
    }/*from   ww  w  .j  a  va  2 s.  c o m*/

    if (configuration != null) {
        String dbServer = configuration.getDbServer();
        int dbPort = configuration.getDbPort();
        String dbDatabase = configuration.getDbDatabase();
        String dbUserName = configuration.getDbUserName();
        String dbPassword = configuration.getDbPassword();
        String url = "jdbc:postgresql://" + dbServer + ":" + Integer.toString(dbPort) + "/" + dbDatabase;
        Properties props = new Properties();
        props.setProperty("user", dbUserName);
        props.setProperty("password", dbPassword);
        try {
            cnx = DriverManager.getConnection(url, props);
            cnx.setAutoCommit(false);
            cnx.rollback();
        } catch (SQLException e) {
            throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
        }
        return (true);
    }

    if (jndiName != null) {
        try {
            Context ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup(jndiName);
            if (ds == null) {
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Could not get resolve DataSource \"" + jndiName + "\"");
            }
            cnx = ds.getConnection();
            if (cnx == null) {
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Could not get Connection from DataSource \"" + jndiName + "\"");
            }
            cnx.setAutoCommit(false);
            cnx.rollback();
        } catch (NamingException e) {
            cnx = null;
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "NamingException " + e.getMessage(), e);
        } catch (SQLException e) {
            cnx = null;
            inTransaction = false;
            throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
        }
        return (true);
    }

    throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
            "Neither configurationFile nor jndiName found, do not know how to connect to database");
}

From source file:net.certifi.audittablegen.GenericDMRTest.java

/**
 * Test of executeDBChangeList method, of class GenericDMR.
 *///from  w ww.ja  v  a2 s.  c om
@Test
public void testExecuteDBChangeList() throws SQLException {
    System.out.println("executeDBChangeList");

    DataSource realDs = HsqldbDMR.getRunTimeDataSource();
    GenericDMR instance = new GenericDMR(realDs);
    List<DBChangeUnit> op = new ArrayList<>();
    String tableName = "TESTTABLE1";
    String column1Name = "data";
    DBChangeUnit unit;
    unit = new DBChangeUnit(DBChangeType.begin);
    op.add(unit);
    unit = new DBChangeUnit(DBChangeType.createTable);
    unit.setTableName(tableName);
    op.add(unit);
    unit = new DBChangeUnit(DBChangeType.addColumn);
    unit.setTableName(tableName);
    unit.setColumnName(column1Name);
    unit.setTypeName("varchar");
    unit.setSize(255);
    op.add(unit);
    op.add(new DBChangeUnit(DBChangeType.end));

    instance.executeDBChangeList(op);

    //test if the table got created
    Connection conn = realDs.getConnection();
    Statement stmt = conn.createStatement();
    String verify = "select table_name from information_schema.system_tables" + " where table_name = '"
            + tableName + "'";
    ResultSet rs = stmt.executeQuery(verify);
    String result = "";
    while (rs.next()) {
        result = rs.getString(1);
    }

    assertEquals(tableName, result);

}