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:FacultyAdvisement.StudentRepository.java

public static void delete(DataSource ds, Student student) throws SQLException {
    Connection conn = ds.getConnection();
    if (conn == null) {
        throw new SQLException("conn is null; Can't get db connection");
    }/*w  ww .ja va2s.  c  o m*/
    try {

        PreparedStatement ps;
        ps = conn.prepareStatement("Delete from STUDENT where EMAIL=?");
        ps.setString(1, student.getUsername());
        ps.executeUpdate();
        ps = conn.prepareStatement("Delete from USERTABLE where USERNAME=?");
        ps.setString(1, student.getUsername());
        ps.executeUpdate();
        ps = conn.prepareStatement("Delete from GROUPTABLE where USERNAME=?");
        ps.setString(1, student.getUsername());
        ps.executeUpdate();
    } finally {
        conn.close();
    }

    //students = (HashMap<String, StudentPOJO>) readAll(); // reload the updated info
}

From source file:org.apache.beam.sdk.io.jdbc.JdbcIOTest.java

/** Create test data that is consistent with that generated by TestRow. */
private static void addInitialData(DataSource dataSource, String tableName) throws SQLException {
    try (Connection connection = dataSource.getConnection()) {
        connection.setAutoCommit(false);
        try (PreparedStatement preparedStatement = connection
                .prepareStatement(String.format("insert into %s values (?,?)", tableName))) {
            for (int i = 0; i < EXPECTED_ROW_COUNT; i++) {
                preparedStatement.clearParameters();
                preparedStatement.setInt(1, i);
                preparedStatement.setString(2, TestRow.getNameForSeed(i));
                preparedStatement.executeUpdate();
            }/*from w ww .  j  a va  2 s  .com*/
        }
        connection.commit();
    }
}

From source file:org.dbmaintain.util.SQLTestUtils.java

/**
 * Returns the long extracted from the result of the given query. If no value is found, a {@link DbMaintainException}
 * is thrown.//from w  w w  . j av a  2  s  . c om
 *
 * @param sql        The sql string for retrieving the items
 * @param dataSource The data source, not null
 * @return The long item value
 */
public static long getItemAsLong(String sql, DataSource dataSource) {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        connection = dataSource.getConnection();
        statement = connection.createStatement();
        resultSet = statement.executeQuery(sql);
        if (resultSet.next()) {
            return resultSet.getLong(1);
        }
    } catch (Exception e) {
        throw new DbMaintainException("Error while executing statement: " + sql, e);
    } finally {
        closeQuietly(connection, statement, resultSet);
    }

    // in case no value was found, throw an exception
    throw new DbMaintainException("No item value found: " + sql);
}

From source file:org.dbmaintain.util.SQLTestUtils.java

/**
 * Returns the value extracted from the result of the given query. If no value is found, a {@link DbMaintainException}
 * is thrown./*from   w  w w . j av  a 2  s .  c  om*/
 *
 * @param sql        The sql string for retrieving the items
 * @param dataSource The data source, not null
 * @return The string item value
 */
public static String getItemAsString(String sql, DataSource dataSource) {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        connection = dataSource.getConnection();
        statement = connection.createStatement();
        resultSet = statement.executeQuery(sql);
        if (resultSet.next()) {
            return resultSet.getString(1);
        }
    } catch (Exception e) {
        throw new DbMaintainException("Error while executing statement: " + sql, e);
    } finally {
        closeQuietly(connection, statement, resultSet);
    }

    // in case no value was found, throw an exception
    throw new DbMaintainException("No item value found: " + sql);
}

From source file:org.dbmaintain.util.SQLTestUtils.java

/**
 * Returns the items extracted from the result of the given query.
 *
 * @param sql        The sql string for retrieving the items
 * @param dataSource The data source, not null
 * @return The items, not null/*from  w ww. j  a v  a  2s  . c om*/
 */
public static Set<String> getItemsAsStringSet(String sql, DataSource dataSource) {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        connection = dataSource.getConnection();
        statement = connection.createStatement();
        resultSet = statement.executeQuery(sql);
        Set<String> result = new HashSet<String>();
        while (resultSet.next()) {
            result.add(resultSet.getString(1));
        }
        return result;

    } catch (Exception e) {
        throw new DbMaintainException("Error while executing statement: " + sql, e);
    } finally {
        closeQuietly(connection, statement, resultSet);
    }
}

From source file:FacultyAdvisement.StudentRepository.java

public static String getPicture(DataSource ds, String key) throws SQLException {

    Blob image = null;//ww  w .ja  v  a 2  s  . c  om

    Connection conn = ds.getConnection();
    if (conn == null) {
        throw new SQLException("conn is null; Can't get db connection");
    }
    try {
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM USERTABLE WHERE USERNAME = ?");
        ps.setString(1, key);
        ResultSet result = ps.executeQuery();
        while (result.next()) {
            image = result.getBlob("IMAGE");
        }
    } finally {
        conn.close();
    }

    if (image != null) {
        return "ImageServlet?username=" + key;
    } else {
        return "/resources/default-image.png";
    }
}

From source file:org.cloudfoundry.identity.uaa.scim.test.TestUtils.java

public static void runScript(DataSource dataSource, String stem) throws Exception {
    ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
    String packageName = ClassUtils.getPackageName(TestUtils.class).replace(".", "/");
    populator.addScript(new ClassPathResource(
            packageName.substring(0, packageName.lastIndexOf("/")) + "/" + stem + "-" + platform + ".sql"));
    Connection connection = dataSource.getConnection();
    try {//from  w w  w . j a  v a2s.c o  m
        populator.populate(connection);
    } catch (ScriptStatementFailedException e) {
        // ignore
    } finally {
        DataSourceUtils.releaseConnection(connection, dataSource);
    }
}

From source file:FacultyAdvisement.StudentRepository.java

public static void adminUpdate(DataSource ds, Student student, String oldUsername) throws SQLException {
    Connection conn = ds.getConnection();
    if (conn == null) {
        throw new SQLException("conn is null; Can't get db connection");
    }//from   w  w  w .  j  a  va 2 s  .  c o  m
    try {

        PreparedStatement ps;
        ps = conn.prepareStatement(
                "Update STUDENT set EMAIL=?, FIRSTNAME=?, LASTNAME=?, MAJORCODE=?, PHONE=?, ADVISED=? where STUID=?");
        ps.setString(1, student.getUsername());
        ps.setString(2, student.getFirstName());
        ps.setString(3, student.getLastName());
        ps.setString(4, student.getMajorCode());
        ps.setString(5, student.getPhoneNumber());
        if (student.isAdvised()) {
            ps.setString(6, "true");
        } else {
            ps.setString(6, "false");
        }
        ps.setString(7, student.getId());
        ps.executeUpdate();

        ps = conn.prepareStatement("Update USERTABLE set USERNAME=? where USERNAME=?");
        ps.setString(1, student.getUsername());
        ps.setString(2, oldUsername);
        ps.executeUpdate();

        ps = conn.prepareStatement("Update GROUPTABLE set USERNAME=? where USERNAME=?");
        ps.setString(1, student.getUsername());
        ps.setString(2, oldUsername);
        ps.executeUpdate();

        if (student.isResetPassword()) {
            String newPassword = UUID.randomUUID().toString();
            String encryptedPassword = SHA256Encrypt.encrypt(newPassword);
            ps = conn.prepareStatement("Update USERTABLE set PASSWORD=? where USERNAME=?");
            ps.setString(1, encryptedPassword);
            ps.setString(2, student.getUsername());
            ps.executeUpdate();

            Email email = new HtmlEmail();
            email.setHostName("smtp.googlemail.com");
            email.setSmtpPort(465);
            email.setAuthenticator(new DefaultAuthenticator("uco.faculty.advisement", "!@#$1234"));
            email.setSSLOnConnect(true);
            email.setFrom("uco.faculty.advisement@gmail.com");
            email.setSubject("UCO Faculty Advisement Password Change");
            email.setMsg("<font size=\"3\">An admin has resetted your password, your new password is \""
                    + newPassword + "\"." + "\n<p align=\"center\">UCO Faculty Advisement</p></font>");
            email.addTo(student.getUsername());
            email.send();

        }

    } catch (EmailException ex) {
        Logger.getLogger(StudentRepository.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        conn.close();
    }

    //students = (HashMap<String, StudentPOJO>) readAll(); // reload the updated info
}

From source file:com.opentable.db.postgres.embedded.PreparedDbProvider.java

private static void create(final DataSource connectDb, @Nonnull final String dbName,
        @Nonnull final String userName) throws SQLException {
    Preconditions.checkArgument(dbName != null, "the database name must not be null!");
    Preconditions.checkArgument(userName != null, "the user name must not be null!");

    try (Connection c = connectDb.getConnection();
            PreparedStatement stmt = c.prepareStatement(
                    String.format("CREATE DATABASE %s OWNER %s ENCODING = 'utf8'", dbName, userName))) {
        stmt.execute();//  w w  w . j a  v a  2  s .  co m
    }
}

From source file:org.wso2.carbon.metrics.data.service.MetricsDataServiceTest.java

public static Test suite() {
    return new TestSetup(new TestSuite(MetricsDataServiceTest.class)) {

        protected void setUp() throws Exception {
            DataSource dataSource = JdbcConnectionPool.create("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", "sa", "");
            template = new JdbcTemplate(dataSource);
            ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
            populator.addScript(new ClassPathResource("dbscripts/h2.sql"));
            populator.populate(dataSource.getConnection());

            // Create initial context
            System.setProperty(Context.INITIAL_CONTEXT_FACTORY, "org.apache.naming.java.javaURLContextFactory");
            System.setProperty(Context.URL_PKG_PREFIXES, "org.apache.naming");
            InitialContext ic = new InitialContext();
            ic.createSubcontext("jdbc");
            ic.bind("jdbc/WSO2MetricsDB", dataSource);
        }/*  w ww  .j a  v  a 2 s.c  om*/

        protected void tearDown() throws Exception {
            InitialContext ic = new InitialContext();
            ic.unbind("jdbc/WSO2MetricsDB");
            ic.unbind("jdbc");
        }
    };
}