List of usage examples for javax.sql DataSource getConnection
Connection getConnection() throws SQLException;
Attempts to establish a connection with the data source that this DataSource object represents.
From source file:fll.web.api.SubjectiveScoresServlet.java
@SuppressFBWarnings(value = {
"SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING" }, justification = "columns and category are dynamic")
@Override//www . j a v a 2s . c o m
protected final void doPost(final HttpServletRequest request, final HttpServletResponse response)
throws IOException, ServletException {
int numModified = 0;
final ObjectMapper jsonMapper = new ObjectMapper();
final ServletContext application = getServletContext();
final ChallengeDescription challengeDescription = ApplicationAttributes
.getChallengeDescription(application);
Connection connection = null;
PreparedStatement deletePrep = null;
PreparedStatement noShowPrep = null;
PreparedStatement insertPrep = null;
try {
final DataSource datasource = ApplicationAttributes.getDataSource(application);
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 Map<String, Map<String, Map<Integer, SubjectiveScore>>> allScores = jsonMapper.readValue(reader,
ScoresTypeInfo.INSTANCE);
for (final Map.Entry<String, Map<String, Map<Integer, SubjectiveScore>>> catEntry : allScores
.entrySet()) {
final String category = catEntry.getKey();
final ScoreCategory categoryDescription = challengeDescription
.getSubjectiveCategoryByName(category);
deletePrep = connection.prepareStatement("DELETE FROM " + category //
+ " WHERE TeamNumber = ?" //
+ " AND Tournament = ?" //
+ " AND Judge = ?" //
);
deletePrep.setInt(2, currentTournament);
noShowPrep = connection.prepareStatement("INSERT INTO " + category //
+ "(TeamNumber, Tournament, Judge, NoShow) VALUES(?, ?, ?, ?)");
noShowPrep.setInt(2, currentTournament);
noShowPrep.setBoolean(4, true);
final int NUM_COLUMNS_BEFORE_GOALS = 6;
insertPrep = createInsertStatement(connection, categoryDescription);
insertPrep.setInt(2, currentTournament);
insertPrep.setBoolean(4, false);
for (final Map.Entry<String, Map<Integer, SubjectiveScore>> judgeEntry : catEntry.getValue()
.entrySet()) {
final String judgeId = judgeEntry.getKey();
deletePrep.setString(3, judgeId);
noShowPrep.setString(3, judgeId);
insertPrep.setString(3, judgeId);
for (final Map.Entry<Integer, SubjectiveScore> teamEntry : judgeEntry.getValue().entrySet()) {
final int teamNumber = teamEntry.getKey();
final SubjectiveScore score = teamEntry.getValue();
if (score.getModified()) {
deletePrep.setInt(1, teamNumber);
noShowPrep.setInt(1, teamNumber);
insertPrep.setInt(1, teamNumber);
insertPrep.setString(5, score.getNote());
++numModified;
if (score.getDeleted()) {
if (LOGGER.isTraceEnabled()) {
LOGGER.trace("Deleting team: " + teamNumber + " judge: " + judgeId
+ " category: " + category);
}
deletePrep.executeUpdate();
} else if (score.getNoShow()) {
if (LOGGER.isTraceEnabled()) {
LOGGER.trace("NoShow team: " + teamNumber + " judge: " + judgeId + " category: "
+ category);
}
deletePrep.executeUpdate();
noShowPrep.executeUpdate();
} else {
if (LOGGER.isTraceEnabled()) {
LOGGER.trace("scores for team: " + teamNumber + " judge: " + judgeId
+ " category: " + category);
}
int goalIndex = 0;
for (final AbstractGoal goalDescription : categoryDescription.getGoals()) {
if (!goalDescription.isComputed()) {
final String goalName = goalDescription.getName();
if (goalDescription.isEnumerated()) {
final String value = score.getEnumSubScores().get(goalName);
if (null == value) {
insertPrep.setNull(goalIndex + NUM_COLUMNS_BEFORE_GOALS,
Types.VARCHAR);
} else {
insertPrep.setString(goalIndex + NUM_COLUMNS_BEFORE_GOALS,
value.trim());
}
} else {
final Double value = score.getStandardSubScores().get(goalName);
if (null == value) {
insertPrep.setNull(goalIndex + NUM_COLUMNS_BEFORE_GOALS,
Types.DOUBLE);
} else {
insertPrep.setDouble(goalIndex + NUM_COLUMNS_BEFORE_GOALS, value);
}
}
++goalIndex;
} // not computed
} // end for
deletePrep.executeUpdate();
insertPrep.executeUpdate();
}
} // is modified
} // foreach team score
} // foreach judge
SQLFunctions.close(deletePrep);
deletePrep = null;
SQLFunctions.close(noShowPrep);
noShowPrep = null;
SQLFunctions.close(insertPrep);
insertPrep = null;
} // foreach category
UploadSubjectiveData.removeNullSubjectiveRows(connection, currentTournament, challengeDescription);
final Tournament tournament = Tournament.findTournamentByID(connection, currentTournament);
tournament.recordSubjectiveModified(connection);
final UploadResult result = new UploadResult(true, "Successfully uploaded scores", numModified);
response.reset();
response.setContentType("application/json");
final PrintWriter writer = response.getWriter();
jsonMapper.writeValue(writer, result);
} catch (final SQLException sqle) {
LOGGER.error("Error uploading scores", sqle);
final UploadResult result = new UploadResult(false, sqle.getMessage(), numModified);
response.reset();
response.setContentType("application/json");
final PrintWriter writer = response.getWriter();
jsonMapper.writeValue(writer, result);
} finally {
SQLFunctions.close(deletePrep);
SQLFunctions.close(noShowPrep);
SQLFunctions.close(insertPrep);
SQLFunctions.close(connection);
}
}
From source file:com.redhat.lightblue.rest.crud.ITCaseCrudResourceRDBMSTest.java
@Test public void testInsert() throws IOException, ClassNotFoundException, NoSuchMethodException, InvocationTargetException, InstantiationException, IllegalAccessException, URISyntaxException, JSONException { try {//from w w w . j a v a 2 s . c o m Context initCtx = new InitialContext(); DataSource ds = (DataSource) initCtx.lookup("java:/mydatasource"); Connection conn = ds.getConnection(); Statement stmt = conn.createStatement(); stmt.execute( "CREATE TABLE Country ( name varchar(255), iso2code varchar(255), iso3code varchar(255) );"); stmt.close(); conn.close(); Assert.assertNotNull("CrudResource was not injected by the container", cutCrudResource); RestConfiguration.setDatasources(new DataSourcesConfiguration( JsonUtils.json(readConfigFile(RestConfiguration.DATASOURCE_FILENAME)))); RestConfiguration.setFactory(new LightblueFactory(RestConfiguration.getDatasources())); String expectedCreated = readFile("expectedCreated.json"); String metadata = readFile("metadata.json").replaceAll("XXY", "INSERT INTO Country (NAME,ISO2CODE,ISO3CODE) VALUES (:name,:iso2code,:iso3code);"); EntityMetadata em = RestConfiguration.getFactory().getJSONParser() .parseEntityMetadata(JsonUtils.json(metadata)); RestConfiguration.getFactory().getMetadata().createNewMetadata(em); EntityMetadata em2 = RestConfiguration.getFactory().getMetadata().getEntityMetadata("country", "1.0.0"); String resultCreated = RestConfiguration.getFactory().getJSONParser().convert(em2).toString(); JSONAssert.assertEquals(expectedCreated, resultCreated, false); String expectedInserted = readFile("expectedInserted.json"); String resultInserted = cutCrudResource.insert("country", "1.0.0", readFile("resultInserted.json")) .getEntity().toString(); System.err.println("!!!!!!!!!!!!!!!!!" + resultInserted); ds = (DataSource) initCtx.lookup("java:/mydatasource"); conn = ds.getConnection(); stmt = conn.createStatement(); stmt.execute("SELECT * FROM Country;"); ResultSet resultSet = stmt.getResultSet(); resultSet.next(); Assert.assertEquals("Canad", resultSet.getString("name")); Assert.assertEquals("CA", resultSet.getString("iso2code")); Assert.assertEquals("CAN", resultSet.getString("iso3code")); JSONAssert.assertEquals(expectedInserted, resultInserted, false); } catch (NamingException ex) { throw new IllegalStateException(ex); } catch (SQLException ex) { throw new IllegalStateException(ex); } }
From source file:com.redhat.lightblue.rest.crud.ITCaseCrudResourceRDBMSTest.java
@Test public void testUpdate() throws IOException, ClassNotFoundException, NoSuchMethodException, InvocationTargetException, InstantiationException, IllegalAccessException, URISyntaxException, JSONException { try {//from w w w . ja va 2 s . c o m Context initCtx = new InitialContext(); DataSource ds = (DataSource) initCtx.lookup("java:/mydatasource"); Connection conn = ds.getConnection(); Statement stmt = conn.createStatement(); stmt.execute( "CREATE TABLE Country ( name varchar(255), iso2code varchar(255), iso3code varchar(255) );"); stmt.execute("INSERT INTO Country (name,iso2code,iso3code) VALUES ('a','CA','c');"); stmt.close(); conn.close(); Assert.assertNotNull("CrudResource was not injected by the container", cutCrudResource); RestConfiguration.setDatasources(new DataSourcesConfiguration( JsonUtils.json(readConfigFile(RestConfiguration.DATASOURCE_FILENAME)))); RestConfiguration.setFactory(new LightblueFactory(RestConfiguration.getDatasources())); String expectedCreated = readFile("expectedCreated.json"); String metadata = readFile("metadata.json").replaceAll("ZZY", " UPDATE Country SET NAME=:name WHERE ISO2CODE=:ISO2CODE;"); EntityMetadata em = RestConfiguration.getFactory().getJSONParser() .parseEntityMetadata(JsonUtils.json(metadata)); RestConfiguration.getFactory().getMetadata().createNewMetadata(em); EntityMetadata em2 = RestConfiguration.getFactory().getMetadata().getEntityMetadata("country", "1.0.0"); String resultCreated = RestConfiguration.getFactory().getJSONParser().convert(em2).toString(); JSONAssert.assertEquals(expectedCreated, resultCreated, false); String expectedUpdated = readFile("expectedUpdated.json"); String resultUpdated = cutCrudResource.update("country", "1.0.0", readFile("resultUpdated.json")) .getEntity().toString(); System.err.println("!!!!!!!!!!!!!!!!!" + resultUpdated); ds = (DataSource) initCtx.lookup("java:/mydatasource"); conn = ds.getConnection(); stmt = conn.createStatement(); stmt.execute("SELECT * FROM Country;"); ResultSet resultSet = stmt.getResultSet(); resultSet.next(); Assert.assertEquals("Canada", resultSet.getString("name")); Assert.assertEquals("CA", resultSet.getString("iso2code")); Assert.assertEquals("c", resultSet.getString("iso3code")); JSONAssert.assertEquals(expectedUpdated, resultUpdated, false); } catch (NamingException | SQLException ex) { throw new IllegalStateException(ex); } mongo.dropDatabase(DB_NAME); }
From source file:com.silverpeas.workflow.engine.model.ProcessModelManagerImpl.java
/** * @return the DB connection// ww w .j a v a2 s.c o m */ private Connection getConnection() throws WorkflowException { Connection con = null; try { // con = DBUtil.makeConnection(dbName); Context ctx = new InitialContext(); DataSource src = (DataSource) ctx.lookup(dbName); con = src.getConnection(); return con; } catch (NamingException e) { // throw new UtilException("Schema.Schema", SilverpeasException.ERROR, // "root.EX_DATASOURCE_NOT_FOUND", e); // the JNDI name have not been found in the current context // The caller is not takes place in any context (web application nor ejb // container) // So lookup operation cannot find JNDI properties ! // This is absolutly normal according to the j2ee specification // Unfortunately, only BES takes care about this spec. This exception // doesn't appear with orion or BEA ! try { // Get the initial Context Context ctx = new InitialContext(); // Look up the datasource directly without JNDI access DataSource dataSource = (DataSource) ctx.lookup(JNDINames.DIRECT_DATASOURCE); // Create a connection object con = dataSource.getConnection(); return con; } catch (NamingException ne) { throw new WorkflowException("ProcessModelManagerImpl.getConnection", "root.EX_DATASOURCE_NOT_FOUND", "Data source " + JNDINames.DIRECT_DATASOURCE + " not found", ne); } catch (SQLException se) { throw new WorkflowException("ProcessModelManagerImpl.getConnection", "can't get connection for dataSource " + JNDINames.DIRECT_DATASOURCE, se); } } catch (SQLException se) { throw new WorkflowException("ProcessModelManagerImpl.getConnection()", "root.EX_CONNECTION_OPEN_FAILED", se); } }
From source file:com.tacitknowledge.util.migration.jdbc.SqlScriptMigrationTaskTest.java
/** * Test that sybase database patches are committed when illegal multi * statement transaction commands are used. * //w ww. ja v a 2 s. c om * @throws IOException * if an unexpected error occurs * @throws MigrationException * if an unexpected error occurs * @throws SQLException * if an unexpected error occurs */ public void testSybasePatchesCommitsOnEveryStatement() throws IOException, MigrationException, SQLException { InputStream is = getClass().getResourceAsStream("test/sybase_tsql.sql"); assertNotNull(is); task = new SqlScriptMigrationTask("sybase_tsql.sql", 1, is); MockDatabaseType dbType = new MockDatabaseType("sybase"); dbType.setMultipleStatementsSupported(false); context.setDatabaseType(dbType); int numStatements = task.getSqlStatements(context).size(); // setup mocks to verify commits are called MockControl dataSourceControl = MockControl.createControl(DataSource.class); DataSource dataSource = (DataSource) dataSourceControl.getMock(); context.setDataSource(dataSource); MockControl connectionControl = MockControl.createControl(Connection.class); Connection connection = (Connection) connectionControl.getMock(); dataSourceControl.expectAndReturn(dataSource.getConnection(), connection); MockControl statementControl = MockControl.createControl(Statement.class); Statement statement = (Statement) statementControl.getMock(); statement.execute(""); statementControl.setMatcher(MockControl.ALWAYS_MATCHER); statementControl.setReturnValue(true, MockControl.ONE_OR_MORE); statement.close(); statementControl.setVoidCallable(MockControl.ONE_OR_MORE); connectionControl.expectAndReturn(connection.isClosed(), false, MockControl.ONE_OR_MORE); connectionControl.expectAndReturn(connection.createStatement(), statement, numStatements); connectionControl.expectAndReturn(connection.getAutoCommit(), false, MockControl.ONE_OR_MORE); connection.commit(); /* * Magic Number 4 derived from the assumption that the fixture sql * contains only one statement that is not allowed in a multi statement * transaction: commit at beginning of migrate method commit prior to * running the command not allowed in multi statement transaction to * clear the transaction state. commit after running the multi statement * transaction to clear transaction state for upcoming statements. * commit at end of migrate method once all statements executed. * * Therefore, if you add more illegal statements to the fixture, add 2 * more commit call's for each illegal statement. */ connectionControl.setVoidCallable(4); dataSourceControl.replay(); connectionControl.replay(); statementControl.replay(); // run tests task.migrate(context); dataSourceControl.verify(); connectionControl.verify(); }
From source file:grails.plugin.quartz2.LocalDataSourceJobStore.java
@Override public void initialize(ClassLoadHelper loadHelper, SchedulerSignaler signaler) throws SchedulerConfigException { // Absolutely needs thread-bound DataSource to initialize. this.dataSource = QuartzFactoryBean.getConfigTimeDataSource(); if (this.dataSource == null) { throw new SchedulerConfigException("No local DataSource found for configuration - " + "'dataSource' property must be set on SchedulerFactoryBean"); }//from w w w. j a v a 2s . c o m // Configure transactional connection settings for Quartz. setDataSource(TX_DATA_SOURCE_PREFIX + getInstanceName()); setDontSetAutoCommitFalse(true); // Register transactional ConnectionProvider for Quartz. DBConnectionManager.getInstance().addConnectionProvider(TX_DATA_SOURCE_PREFIX + getInstanceName(), new ConnectionProvider() { public Connection getConnection() throws SQLException { // Return a transactional Connection, if any. return DataSourceUtils.doGetConnection(dataSource); } public void shutdown() { // Do nothing - a Spring-managed DataSource has its own lifecycle. } }); // Configure non-transactional connection settings for Quartz. setNonManagedTXDataSource(NON_TX_DATA_SOURCE_PREFIX + getInstanceName()); final DataSource nonTxDataSourceToUse = this.dataSource; // Register non-transactional ConnectionProvider for Quartz. DBConnectionManager.getInstance().addConnectionProvider(NON_TX_DATA_SOURCE_PREFIX + getInstanceName(), new ConnectionProvider() { public Connection getConnection() throws SQLException { // Always return a non-transactional Connection. return nonTxDataSourceToUse.getConnection(); } public void shutdown() { // Do nothing - a Spring-managed DataSource has its own lifecycle. } }); // No, if HSQL is the platform, we really don't want to use locks try { String productName = JdbcUtils.extractDatabaseMetaData(dataSource, "getDatabaseProductName").toString(); productName = JdbcUtils.commonDatabaseName(productName); if (productName != null && productName.toLowerCase().contains("hsql")) { setUseDBLocks(false); setLockHandler(new SimpleSemaphore()); } } catch (MetaDataAccessException e) { logWarnIfNonZero(1, "Could not detect database type. Assuming locks can be taken."); } super.initialize(loadHelper, signaler); }
From source file:org.netxilia.api.impl.storage.DataSourceConfigurationServiceImpl.java
@Override public synchronized void test(DataSourceConfigurationId id) throws StorageException, NotFoundException, SQLException { init();// w ww . j a v a2 s. c o m DataSourceConfiguration cfg = load(id); DataSource dataSource = buildSimpleDataSource(cfg); Connection connection = null; try { connection = dataSource.getConnection(); } finally { if (connection != null) { try { connection.close(); } catch (SQLException e) { // quiet } } } }
From source file:org.apache.cayenne.dbsync.reverse.dbimport.DefaultDbImportAction.java
@Override public void execute(DbImportConfiguration config) throws Exception { if (logger.isDebugEnabled()) { logger.debug("DB connection: " + config.getDataSourceInfo()); logger.debug(config);/*from w ww .java 2 s .com*/ } boolean hasChanges = false; DataNodeDescriptor dataNodeDescriptor = config.createDataNodeDescriptor(); DataSource dataSource = dataSourceFactory.getDataSource(dataNodeDescriptor); DbAdapter adapter = adapterFactory.createAdapter(dataNodeDescriptor, dataSource); DataMap sourceDataMap; try (Connection connection = dataSource.getConnection()) { sourceDataMap = load(config, adapter, connection); } DataMap targetDataMap = existingTargetMap(config); if (targetDataMap == null) { String path = config.getTargetDataMap() == null ? "null" : config.getTargetDataMap().getAbsolutePath() + "'"; logger.info(""); logger.info("Map file does not exist. Loaded db model will be saved into '" + path); hasChanges = true; targetDataMap = newTargetDataMap(config); } // transform source DataMap before merging transformSourceBeforeMerge(sourceDataMap, targetDataMap, config); MergerTokenFactory mergerTokenFactory = mergerTokenFactoryProvider.get(adapter); DbLoaderConfiguration loaderConfig = config.getDbLoaderConfig(); Collection<MergerToken> tokens = DataMapMerger.builder(mergerTokenFactory) .filters(loaderConfig.getFiltersConfig()).skipPKTokens(loaderConfig.isSkipPrimaryKeyLoading()) .skipRelationshipsTokens(loaderConfig.isSkipRelationshipsLoading()).build() .createMergeTokens(targetDataMap, sourceDataMap); tokens = log(sort(reverse(mergerTokenFactory, tokens))); hasChanges |= syncDataMapProperties(targetDataMap, config); hasChanges |= applyTokens(targetDataMap, tokens, config); hasChanges |= syncProcedures(targetDataMap, sourceDataMap, loaderConfig.getFiltersConfig()); if (hasChanges) { saveLoaded(targetDataMap); } }
From source file:org.dashbuilder.dataprovider.backend.sql.SQLDataSetProvider.java
public int getRowCount(SQLDataSetDef def) throws Exception { DataSource ds = dataSourceLocator.lookup(def); Connection conn = ds.getConnection(); try {// ww w. j a v a 2 s . co m return _getRowCount(def, conn); } finally { conn.close(); } }
From source file:fll.web.api.SubjectiveScoresServlet.java
@SuppressFBWarnings(value = {
"SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING" }, justification = "columns and category are dynamic")
@Override/*from w ww. j ava 2 s. com*/
protected final void doGet(final HttpServletRequest request, final HttpServletResponse response)
throws IOException, ServletException {
final ServletContext application = getServletContext();
final DataSource datasource = ApplicationAttributes.getDataSource(application);
Connection connection = null;
PreparedStatement prep = null;
ResultSet rs = null;
try {
connection = datasource.getConnection();
final int currentTournament = Queries.getCurrentTournament(connection);
// category->judge->teamNumber->score
final Map<String, Map<String, Map<Integer, SubjectiveScore>>> allScores = new HashMap<String, Map<String, Map<Integer, SubjectiveScore>>>();
final ChallengeDescription challengeDescription = ApplicationAttributes
.getChallengeDescription(application);
for (final ScoreCategory sc : challengeDescription.getSubjectiveCategories()) {
// judge->teamNumber->score
final Map<String, Map<Integer, SubjectiveScore>> categoryScores = new HashMap<String, Map<Integer, SubjectiveScore>>();
prep = connection.prepareStatement("SELECT * FROM " + sc.getName() + " WHERE Tournament = ?");
prep.setInt(1, currentTournament);
rs = prep.executeQuery();
while (rs.next()) {
final SubjectiveScore score = new SubjectiveScore();
score.setScoreOnServer(true);
final String judge = rs.getString("Judge");
final Map<Integer, SubjectiveScore> judgeScores;
if (categoryScores.containsKey(judge)) {
judgeScores = categoryScores.get(judge);
} else {
judgeScores = new HashMap<Integer, SubjectiveScore>();
categoryScores.put(judge, judgeScores);
}
score.setTeamNumber(rs.getInt("TeamNumber"));
score.setJudge(judge);
score.setNoShow(rs.getBoolean("NoShow"));
score.setNote(rs.getString("note"));
final Map<String, Double> standardSubScores = new HashMap<String, Double>();
final Map<String, String> enumSubScores = new HashMap<String, String>();
for (final AbstractGoal goal : sc.getGoals()) {
if (goal.isEnumerated()) {
final String value = rs.getString(goal.getName());
enumSubScores.put(goal.getName(), value);
} else {
final double value = rs.getDouble(goal.getName());
standardSubScores.put(goal.getName(), value);
}
}
score.setStandardSubScores(standardSubScores);
score.setEnumSubScores(enumSubScores);
judgeScores.put(score.getTeamNumber(), score);
}
allScores.put(sc.getName(), categoryScores);
SQLFunctions.close(rs);
rs = null;
SQLFunctions.close(prep);
prep = null;
}
final ObjectMapper jsonMapper = new ObjectMapper();
response.reset();
response.setContentType("application/json");
final PrintWriter writer = response.getWriter();
jsonMapper.writeValue(writer, allScores);
} catch (final SQLException e) {
throw new RuntimeException(e);
} finally {
SQLFunctions.close(rs);
SQLFunctions.close(prep);
SQLFunctions.close(connection);
}
}