List of usage examples for java.sql Connection rollback
void rollback() throws SQLException;
Connection
object. From source file:org.apache.eagle.storage.jdbc.entity.impl.JdbcEntityWriterImpl.java
@Override public List<String> write(List<E> entities) throws Exception { List<String> keys = new ArrayList<String>(); if (LOG.isDebugEnabled()) LOG.debug("Writing " + entities.size() + " entities"); StopWatch stopWatch = new StopWatch(); stopWatch.start();//from www .j a v a 2s . c o m Connection connection = ConnectionManagerFactory.getInstance().getConnection(); // set auto commit false and commit by hands for 3x~5x better performance connection.setAutoCommit(false); try { TorqueStatementPeerImpl<E> peer = connectionManager .getStatementExecutor(this.jdbcEntityDefinition.getJdbcTableName()); for (E entity : entities) { entity.setEncodedRowkey(peer.getPrimaryKeyBuilder().build(entity)); ColumnValues columnValues = JdbcEntitySerDeserHelper.buildColumnValues(entity, this.jdbcEntityDefinition); ObjectKey key = null; try { // TODO: implement batch insert for better performance key = peer.delegate().doInsert(columnValues, connection); if (key != null) { keys.add((String) key.getValue()); } else { keys.add(entity.getEncodedRowkey()); } } catch (ClassCastException ex) { assert key != null; throw new RuntimeException( "Key is not in type of String (VARCHAR) , but JdbcType (java.sql.Types): " + key.getJdbcType() + ", value: " + key.getValue(), ex); } catch (ConstraintViolationException e) { //this message will be different in each DB type ...using duplicate keyword to catch for broader set of DBs. moreover we are already inside ConstraintViolationException exception, do we even need this check? if (e.getMessage().toLowerCase().contains("duplicate")) { String primaryKey = entity.getEncodedRowkey(); if (primaryKey == null) { primaryKey = ConnectionManagerFactory.getInstance().getStatementExecutor() .getPrimaryKeyBuilder().build(entity); entity.setEncodedRowkey(primaryKey); } PrimaryKeyCriteriaBuilder pkBuilder = new PrimaryKeyCriteriaBuilder( Collections.singletonList(primaryKey), this.jdbcEntityDefinition.getJdbcTableName()); Criteria selectCriteria = pkBuilder.build(); if (LOG.isDebugEnabled()) LOG.debug("Updating by query: " + SqlBuilder.buildQuery(selectCriteria).getDisplayString()); peer.delegate().doUpdate(selectCriteria, columnValues, connection); keys.add(primaryKey); } } } // Why not commit in finally: give up all if any single entity throws exception to make sure consistency guarantee if (LOG.isDebugEnabled()) { LOG.debug("Committing writing"); } connection.commit(); } catch (Exception ex) { LOG.error("Failed to write records, rolling back", ex); connection.rollback(); throw ex; } finally { stopWatch.stop(); if (LOG.isDebugEnabled()) LOG.debug("Closing connection"); connection.close(); } LOG.info(String.format("Wrote %s records in %s ms (table: %s)", keys.size(), stopWatch.getTime(), this.jdbcEntityDefinition.getJdbcTableName())); return keys; }
From source file:es.juntadeandalucia.panelGestion.negocio.utiles.JDBCConnector.java
/** * Method for all tables of a database schema * @param schema// w ww. j a v a2 s .c o m * @return list of tables * @return list tables * @throws Exception */ public List<Table> tablesByShema(Schema schema) throws Exception { Exception error = null; List<Table> tablesBySchema = new LinkedList<Table>(); Connection connection = null; PreparedStatement preparedStmnt = null; try { DataSource dataSource = poolDataSources.get(schemaId); connection = dataSource.getConnection(); connection.setAutoCommit(false); //Se actualiza la vista geometry_columns para hacer la consulta con datos actualizados /* try { PreparedStatement preparedStmntActualizeGC = null; preparedStmntActualizeGC = connection.prepareStatement("SELECT Populate_Geometry_Columns(true)"); preparedStmntActualizeGC.execute(); } catch (Exception oops) { log.info("No se a podido actualizar la tabla geometry_columns"); }*/ //Se seleccionan todas las tablas de la base de datos distinguiendo de las vistas y las tablas propias del sistema. preparedStmnt = connection .prepareStatement("SELECT * FROM geometry_columns WHERE f_table_name IN (SELECT table_name" + " FROM information_schema.columns" + " WHERE udt_name = 'geometry' AND table_name IN (SELECT table_name FROM information_schema.tables )) AND f_geometry_column <> 'extent';");//AND srid > 0 ResultSet rs = preparedStmnt.executeQuery(); while (rs.next()) { Table table = new Table(); table.setName(rs.getString("f_table_name")); table.setCreationDate(new Date()); table.setGeomField(rs.getString("f_geometry_column")); table.setEpsg("EPSG:".concat(rs.getString("srid"))); table.setModificationDate(null); table.setTableXservices(null); table.setTasks(null); table.setSchema(schema); tablesBySchema.add(table); } } catch (SQLException e) { error = e; } finally { if (preparedStmnt != null) { try { preparedStmnt.close(); } catch (SQLException se2) { log.warn("No se pudo cerrar el statment: ".concat(se2.getLocalizedMessage())); } } if (connection != null) { try { if (error != null) { connection.rollback(); } } catch (SQLException se) { log.warn("Se produjo un error al manejar la conexin: ".concat(se.getLocalizedMessage())); } try { connection.close(); } catch (SQLException se) { log.warn("Se produjo un error al intentar cerrar la conexin: " .concat(se.getLocalizedMessage())); } } } if (error != null) { throw error; } return tablesBySchema; }
From source file:org.opennms.ng.services.capsd.BroadcastEventProcessor.java
/** * Handle a deleteInterface Event. Here we process the event by marking all * the appropriate data rows as deleted. * * @param event The event indicating what interface to delete * @throws org.opennms.netmgt.capsd.InsufficientInformationException if the required information is not part of the event * @throws org.opennms.netmgt.capsd.FailedOperationException if any. *//* w ww. j av a2 s. c o m*/ @EventHandler(uei = EventConstants.DELETE_INTERFACE_EVENT_UEI) public void handleDeleteInterface(Event event) throws InsufficientInformationException, FailedOperationException { // validate event EventUtils.checkEventId(event); EventUtils.checkInterfaceOrIfIndex(event); EventUtils.checkNodeId(event); int ifIndex = -1; if (event.hasIfIndex()) { ifIndex = event.getIfIndex(); } if (isXmlRpcEnabled()) { EventUtils.requireParm(event, EventConstants.PARM_TRANSACTION_NO); } // log the event LOG.debug( "handleDeleteInterface: Event\nuei\t\t{}\neventid\t\t{}\nnodeId\t\t{}\nipaddr\t\t{}\nifIndex\t\t{}\neventtime\t{}", event.getUei(), event.getDbid(), event.getNodeid(), (event.getInterface() != null ? event.getInterface() : "N/A"), (ifIndex > -1 ? ifIndex : "N/A"), (event.getTime() != null ? event.getTime() : "<null>")); long txNo = EventUtils.getLongParm(event, EventConstants.PARM_TRANSACTION_NO, -1L); // update the database Connection dbConn = null; List<Event> eventsToSend = null; try { dbConn = getConnection(); dbConn.setAutoCommit(false); String source = (event.getSource() == null ? "OpenNMS.Capsd" : event.getSource()); eventsToSend = doDeleteInterface(dbConn, source, event.getNodeid(), event.getInterface(), ifIndex, txNo); } catch (SQLException ex) { LOG.error( "handleDeleteInterface: Database error deleting interface on node {} with ip address {} and ifIndex {}", event.getNodeid(), (event.getInterface() != null ? event.getInterface() : "null"), (event.hasIfIndex() ? event.getIfIndex() : "null"), ex); throw new FailedOperationException("database error: " + ex.getMessage(), ex); } finally { if (dbConn != null) { try { if (eventsToSend != null) { dbConn.commit(); for (Event e : eventsToSend) { EventUtils.sendEvent(e, event.getUei(), txNo, isXmlRpcEnabled()); } } else { dbConn.rollback(); } } catch (SQLException ex) { LOG.error("handleDeleteInterface: Exception thrown during commit/rollback: ", ex); throw new FailedOperationException("exeption processing delete interface: " + ex.getMessage(), ex); } finally { if (dbConn != null) { try { dbConn.close(); } catch (SQLException ex) { LOG.error("handleDeleteInterface: Exception thrown closing connection: ", ex); } } } } } }
From source file:nl.b3p.viewer.util.databaseupdate.ScriptRunner.java
/** * Runs an SQL script (read in using the Reader parameter) using the * connection passed in// w ww. j a v a 2 s . co m * * @param conn - the connection to use for the script * @param reader - the source of the script * @throws SQLException if any SQL errors occur * @throws IOException if there is an error reading from the Reader */ private void runScript(Connection conn, Reader reader) throws IOException, SQLException { StringBuffer command = null; try { LineNumberReader lineReader = new LineNumberReader(reader); String line = null; while ((line = lineReader.readLine()) != null) { if (command == null) { command = new StringBuffer(); } String trimmedLine = line.trim(); if (trimmedLine.startsWith("--")) { log.debug(trimmedLine); } else if (trimmedLine.length() < 1 || trimmedLine.startsWith("//")) { // Do nothing } else if (trimmedLine.length() < 1 || trimmedLine.startsWith("--")) { // Do nothing } else if (!fullLineDelimiter && trimmedLine.endsWith(getDelimiter()) || fullLineDelimiter && trimmedLine.equals(getDelimiter())) { command.append(line.substring(0, line.lastIndexOf(getDelimiter()))); command.append(" "); Statement statement = conn.createStatement(); log.debug(command); boolean hasResults = false; if (stopOnError) { hasResults = statement.execute(command.toString()); } else { try { statement.execute(command.toString()); } catch (SQLException e) { e.fillInStackTrace(); log.error("Error executing: " + command, e); } } if (autoCommit && !conn.getAutoCommit()) { conn.commit(); } ResultSet rs = statement.getResultSet(); if (hasResults && rs != null) { ResultSetMetaData md = rs.getMetaData(); int cols = md.getColumnCount(); for (int i = 0; i < cols; i++) { String name = md.getColumnLabel(i); log.debug(name + "\t"); } while (rs.next()) { for (int i = 0; i < cols; i++) { String value = rs.getString(i); log.debug(value + "\t"); } } } command = null; try { statement.close(); } catch (Exception e) { // Ignore to workaround a bug in Jakarta DBCP } Thread.yield(); } else { command.append(line); command.append(" "); } } if (!autoCommit) { conn.commit(); } } catch (SQLException e) { e.fillInStackTrace(); log.error("Error executing: " + command, e); throw e; } catch (IOException e) { e.fillInStackTrace(); log.error("Error executing: " + command, e); throw e; } finally { if (!this.autoCommit) { conn.rollback(); } } }
From source file:com.concursive.connect.web.modules.documents.dao.FileItem.java
/** * Description of the Method//from w w w.java 2 s.c o m * * @param db Description of Parameter * @return Description of the Returned Value * @throws SQLException Description of Exception */ public int update(Connection db) throws SQLException { if (this.getId() == -1) { throw new SQLException("ID was not specified"); } if (!isValid()) { return -1; } int resultCount = 0; boolean commit = db.getAutoCommit(); try { if (commit) { db.setAutoCommit(false); } int i = 0; // NOTE: Do not update the "modified" field because it is used for file paths PreparedStatement pst = db.prepareStatement("UPDATE project_files " + "SET subject = ?, client_filename = ?, default_file = ?, comment = ?, featured_file = ? " + "WHERE item_id = ? "); pst.setString(++i, subject); pst.setString(++i, clientFilename); pst.setBoolean(++i, defaultFile); pst.setString(++i, comment); pst.setBoolean(++i, featuredFile); pst.setInt(++i, this.getId()); resultCount = pst.executeUpdate(); pst.close(); // Set default if (defaultFile) { updateDefaultRecord(db, linkModuleId, linkItemId, id); } // Retrieve any versions this.buildVersionList(db); // Update version info for the corresponding file item version for (FileItemVersion latestVersion : versionList) { if (Double.toString(this.version).equals(Double.toString(latestVersion.getVersion()))) { latestVersion.setClientFilename(this.getClientFilename()); latestVersion.setSubject(this.getSubject()); latestVersion.setComment(this.getComment()); latestVersion.update(db); break; } } } catch (Exception e) { if (commit) { db.rollback(); } throw new SQLException(e.getMessage()); } finally { if (commit) { db.setAutoCommit(true); } } return resultCount; }
From source file:com.raulexposito.alarife.sqlexecutor.SQLExecutor.java
/** * Connects with the database and performs some operations with it * @param commandType enumeration with the operation to perform * @param databaseType enumeration with the database type * @param propertiesFile propertiesFile name of the properties file where you can read the database configuration * @param nextVersion version you are migrating to in upgrades * @param initialVersionNumber version to start the database in regenerations * @return initially a Version object/*from ww w .j av a 2 s. c om*/ * @throws com.raulexposito.alarife.exception.DatabaseException if something goes worng */ private Version performSQLAction(final SQLExecutorCommandType commandType, final DatabaseType databaseType, final ApplicationMode applicationMode, final String propertiesFile, final List<Version> versionList, final String initialVersionNumber) throws DatabaseException { Connection con = null; Statement st = null; boolean rollbackConnection = false; try { // read the database properties file if it's needed if (dpru == null) { dpru = new DatabasePropertiesReaderUtil(propertiesFile); final String driverClassName = dpru.getDriverClassName(); // load the driver Class.forName(driverClassName); log.info("database driver loaded: '" + driverClassName + "'"); } if (dcfspr == null) { dcfspr = new DatabaseCreatorFromScratchPropertiesReader(propertiesFile); } // connection to the database using an instance, an username and password con = DriverManager.getConnection(dpru.getInstance(), dpru.getUsername(), dpru.getPassword()); con.setAutoCommit(false); // creation of a statements to execute commands st = con.createStatement(); // actions to be performed if (commandType.equals(SQLExecutorCommandType.CREATE_FROM_SCRATCH)) { regenerateDatabaseFromScratch(propertiesFile, databaseType, st, initialVersionNumber); } else if (commandType.equals(SQLExecutorCommandType.RECOVER_CURRENT_VERSION)) { return recoverCurrentVersion(propertiesFile, databaseType, st); } else if (commandType.equals(SQLExecutorCommandType.UPDATE_TO_VERSION)) { Version currentVersion = this.recoverCurrentVersion(databaseType, applicationMode, propertiesFile); final Version latestVersion = DatabaseUpgrader.recoverLatestVersion(versionList); while (DatabaseUpgrader.upgradeIsNeeded(currentVersion, latestVersion)) { log.info("the current version '" + currentVersion + "' is older than the latest version '" + latestVersion + "', so an upgrade is needed"); final Version nextVersion = DatabaseUpgrader.getNextVersion(currentVersion, versionList); updateToVersion(databaseType, applicationMode, st, nextVersion); currentVersion = recoverCurrentVersion(databaseType, applicationMode, propertiesFile); } } // commit the changes in database con.commit(); } catch (IOException ex) { log.error("There is a problem reading the configuration file"); rollbackConnection = true; throw new DatabaseException(ex.getMessage(), ex); } catch (SQLException ex) { log.error("There is a problem with a SQL command"); rollbackConnection = true; throw new DatabaseException(ex.getMessage(), ex); } catch (DatabaseException ex) { log.error("There is a problem updating the database"); rollbackConnection = true; throw new DatabaseException(ex.getMessage(), ex); } catch (ClassNotFoundException ex) { log.error("The database driver cannot be loaded"); rollbackConnection = true; throw new DatabaseException(ex.getMessage(), ex); } finally { if (rollbackConnection && (con != null)) { try { con.rollback(); } catch (Exception e) { log.warn("cannot rollback the connection: " + e); } } if (con != null) { try { con.close(); } catch (Exception e) { log.warn("cannot close the connection: " + e); } } if (st != null) { try { st.close(); } catch (Exception e) { log.warn("cannot close the statement: " + e); } } } return null; }
From source file:org.gsoft.admin.ScriptRunner.java
/** * Runs an SQL script (read in using the Reader parameter) using the * connection passed in/*www .java2 s .co m*/ * * @param conn * - the connection to use for the script * @param reader * - the source of the script * @throws SQLException * if any SQL errors occur * @throws IOException * if there is an error reading from the Reader */ private void runScript(Connection conn, Reader reader) throws IOException, SQLException { StringBuffer command = null; try { LineNumberReader lineReader = new LineNumberReader(reader); String line = null; while ((line = lineReader.readLine()) != null) { if (command == null) { command = new StringBuffer(); } String trimmedLine = line.trim(); if (trimmedLine.startsWith("--")) { println(trimmedLine); } else if (trimmedLine.length() < 1 || trimmedLine.startsWith("//")) { // Do nothing } else if (trimmedLine.length() < 1 || trimmedLine.startsWith("--")) { // Do nothing } else if (!fullLineDelimiter && trimmedLine.endsWith(getDelimiter()) || fullLineDelimiter && trimmedLine.equals(getDelimiter())) { command.append(line.substring(0, line.lastIndexOf(getDelimiter()))); command.append(" "); Statement statement = conn.createStatement(); println(command); boolean hasResults = false; if (stopOnError) { hasResults = statement.execute(command.toString()); } else { try { statement.execute(command.toString()); } catch (SQLException e) { e.fillInStackTrace(); printlnError("Error executing: " + command); printlnError(e); } } if (autoCommit && !conn.getAutoCommit()) { conn.commit(); } ResultSet rs = statement.getResultSet(); if (hasResults && rs != null) { ResultSetMetaData md = rs.getMetaData(); int cols = md.getColumnCount(); for (int i = 0; i < cols; i++) { String name = md.getColumnLabel(i); print(name + "\t"); } println(""); while (rs.next()) { for (int i = 0; i < cols; i++) { String value = rs.getString(i); print(value + "\t"); } println(""); } } command = null; try { statement.close(); } catch (Exception e) { // Ignore to workaround a bug in Jakarta DBCP } Thread.yield(); } else { command.append(line); command.append(" "); } } if (!autoCommit) { conn.commit(); } } catch (SQLException e) { e.fillInStackTrace(); printlnError("Error executing: " + command); printlnError(e); throw e; } catch (IOException e) { e.fillInStackTrace(); printlnError("Error executing: " + command); printlnError(e); throw e; } finally { conn.rollback(); flush(); } }
From source file:mom.trd.opentheso.bdd.helper.ConceptHelper.java
/** * Cette fonction permet de dplacer une Branche * * @param ds//from w ww.java2 s .c o m * @param idConcept * @param idOldConceptBT * @param idNewConceptBT * @param idThesaurus * @param idUser * @return true or false */ public boolean moveBranch(HikariDataSource ds, String idConcept, String idOldConceptBT, String idNewConceptBT, String idThesaurus, int idUser) { try { Connection conn = ds.getConnection(); conn.setAutoCommit(false); if (!new RelationsHelper().deleteRelationBT(conn, idConcept, idThesaurus, idOldConceptBT, idUser)) { conn.rollback(); conn.close(); return false; } if (!new RelationsHelper().addRelationBT(conn, idConcept, idThesaurus, idNewConceptBT, idUser)) { conn.rollback(); conn.close(); return false; } conn.commit(); conn.close(); return true; } catch (SQLException ex) { Logger.getLogger(ConceptHelper.class.getName()).log(Level.SEVERE, null, ex); } return false; }
From source file:mom.trd.opentheso.bdd.helper.ConceptHelper.java
/** * Cette fonction permet d'ajouter une traduction un TopConcept * * @param ds//from w w w . jav a 2 s.c o m * @param term * @param idUser * @return null si le term existe ou si erreur, sinon le numero de Concept */ public boolean addTopConceptTraduction(HikariDataSource ds, Term term, int idUser) { TermHelper termHelper = new TermHelper(); // controle si le term existe avant de rajouter un concept if (termHelper.isTermExist(ds, term.getLexical_value(), term.getId_thesaurus(), term.getLang())) { return false; } Connection conn = null; try { conn = ds.getConnection(); conn.setAutoCommit(false); if (!termHelper.addTermTraduction(conn, term, idUser)) { conn.rollback(); conn.close(); return false; } conn.commit(); conn.close(); // cette fonction permet de remplir la table Permute termHelper.splitConceptForPermute(ds, term.getId_concept(), getGroupIdOfConcept(ds, term.getId_concept(), term.getId_thesaurus()), term.getId_thesaurus(), term.getLang(), term.getLexical_value()); return true; } catch (SQLException ex) { Logger.getLogger(ConceptHelper.class.getName()).log(Level.SEVERE, null, ex); try { if (conn != null) { conn.close(); } } catch (SQLException ex1) { } } return false; }
From source file:mom.trd.opentheso.bdd.helper.ConceptHelper.java
/** * Cette fonction permet de supprimer un Concept avec ses relations et * traductions, notes, alignements, ... pas de controle s'il a des fils, * c'est une suppression dfinitive/*from ww w . j ava 2 s . c om*/ * * @param ds * @param idConcept * @param idThesaurus * @param idUser * @return boolean */ public boolean deleteConceptForced(HikariDataSource ds, String idConcept, String idThesaurus, int idUser) { TermHelper termHelper = new TermHelper(); RelationsHelper relationsHelper = new RelationsHelper(); String idTerm = new TermHelper().getIdTermOfConcept(ds, idConcept, idThesaurus); if (idTerm == null) { /// c'est dire que le concept n'a aucune traduction (cas de concept corrompu) // return false; } // suppression du term avec les traductions et les synonymes // gestion du Rollback en cas d'erreur Connection conn = null; try { conn = ds.getConnection(); conn.setAutoCommit(false); if (!termHelper.deleteTerm(conn, idTerm, idThesaurus, idUser)) { conn.rollback(); conn.close(); return false; } if (!relationsHelper.deleteAllRelationOfConcept(conn, idConcept, idThesaurus, idUser)) { conn.rollback(); conn.close(); return false; } if (!deleteConceptFromTable(conn, idConcept, idThesaurus, idUser)) { conn.rollback(); conn.close(); return false; } conn.commit(); conn.close(); return true; } catch (SQLException ex) { Logger.getLogger(ConceptHelper.class.getName()).log(Level.SEVERE, null, ex); if (conn != null) { try { conn.rollback(); conn.close(); } catch (SQLException ex1) { Logger.getLogger(ConceptHelper.class.getName()).log(Level.SEVERE, null, ex1); } } return false; } }