List of usage examples for java.sql Statement executeBatch
int[] executeBatch() throws SQLException;
From source file:org.freebxml.omar.server.persistence.rdb.PostalAddressDAO.java
/** * Does a bulk insert of a Collection of objects that match the type for this persister. * *//*from w w w. ja v a 2 s.co m*/ public void insert(String parentId, List postalAddresss) throws RegistryException { Statement stmt = null; log.debug(ServerResourceBundle.getInstance().getString("message.InsertingPostalAddresss", new Object[] { new Integer(postalAddresss.size()) })); if (postalAddresss.size() == 0) { return; } try { stmt = context.getConnection().createStatement(); Iterator iter = postalAddresss.iterator(); while (iter.hasNext()) { PostalAddressType postalAddress = (PostalAddressType) iter.next(); //Log.print(Log.TRACE, 8, "\tDATABASE EVENT: storing PostalAddress " ); String city = postalAddress.getCity(); if (city != null) { city = "'" + city + "'"; } String country = postalAddress.getCountry(); if (country != null) { country = "'" + country + "'"; } String postalCode = postalAddress.getPostalCode(); if (postalCode != null) { postalCode = "'" + postalCode + "'"; } String state = postalAddress.getStateOrProvince(); if (state != null) { state = "'" + state + "'"; } String street = postalAddress.getStreet(); if (street != null) { street = "'" + street + "'"; } String streetNum = postalAddress.getStreetNumber(); if (streetNum != null) { streetNum = "'" + streetNum + "'"; } String str = "INSERT INTO PostalAddress " + "VALUES( " + city + ", " + country + ", " + postalCode + ", " + state + ", " + street + ", " + streetNum + ", " + "'" + parentId + "' )"; log.trace("SQL = " + str); stmt.addBatch(str); } if (postalAddresss.size() > 0) { stmt.executeBatch(); } } catch (SQLException e) { RegistryException exception = new RegistryException(e); throw exception; } finally { closeStatement(stmt); } }
From source file:org.springframework.jdbc.core.JdbcTemplateTests.java
public void testBatchUpdate() throws Exception { final String[] sql = { "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1", "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 2" }; MockControl ctrlStatement = MockControl.createControl(Statement.class); Statement mockStatement = (Statement) ctrlStatement.getMock(); mockStatement.getConnection();//from w ww . jav a 2s. c o m ctrlStatement.setReturnValue(mockConnection); mockStatement.addBatch(sql[0]); ctrlStatement.setVoidCallable(); mockStatement.addBatch(sql[1]); ctrlStatement.setVoidCallable(); mockStatement.executeBatch(); ctrlStatement.setReturnValue(new int[] { 1, 1 }); if (debugEnabled) { mockStatement.getWarnings(); ctrlStatement.setReturnValue(null); } mockStatement.close(); ctrlStatement.setVoidCallable(); MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class); DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock(); mockDatabaseMetaData.getDatabaseProductName(); ctrlDatabaseMetaData.setReturnValue("MySQL"); mockDatabaseMetaData.supportsBatchUpdates(); ctrlDatabaseMetaData.setReturnValue(true); mockConnection.getMetaData(); ctrlConnection.setReturnValue(mockDatabaseMetaData, 2); mockConnection.createStatement(); ctrlConnection.setReturnValue(mockStatement); ctrlStatement.replay(); ctrlDatabaseMetaData.replay(); replay(); JdbcTemplate template = new JdbcTemplate(mockDataSource, false); int[] actualRowsAffected = template.batchUpdate(sql); assertTrue("executed 2 updates", actualRowsAffected.length == 2); ctrlStatement.verify(); ctrlDatabaseMetaData.verify(); }
From source file:it.cnr.icar.eric.server.persistence.rdb.PostalAddressDAO.java
/** * Does a bulk insert of a Collection of objects that match the type for this persister. * *//*from w w w .ja v a 2s .c om*/ public void insert(String parentId, List<?> postalAddresss) throws RegistryException { Statement stmt = null; log.debug(ServerResourceBundle.getInstance().getString("message.InsertingPostalAddresss", new Object[] { new Integer(postalAddresss.size()) })); if (postalAddresss.size() == 0) { return; } try { stmt = context.getConnection().createStatement(); Iterator<?> iter = postalAddresss.iterator(); while (iter.hasNext()) { PostalAddressType postalAddress = (PostalAddressType) iter.next(); //Log.print(Log.TRACE, 8, "\tDATABASE EVENT: storing PostalAddress " ); String city = postalAddress.getCity(); if (city != null) { city = "'" + city + "'"; } String country = postalAddress.getCountry(); if (country != null) { country = "'" + country + "'"; } String postalCode = postalAddress.getPostalCode(); if (postalCode != null) { postalCode = "'" + postalCode + "'"; } String state = postalAddress.getStateOrProvince(); if (state != null) { state = "'" + state + "'"; } String street = postalAddress.getStreet(); if (street != null) { street = "'" + street + "'"; } String streetNum = postalAddress.getStreetNumber(); if (streetNum != null) { streetNum = "'" + streetNum + "'"; } String str = "INSERT INTO PostalAddress " + "VALUES( " + city + ", " + country + ", " + postalCode + ", " + state + ", " + street + ", " + streetNum + ", " + "'" + parentId + "' )"; log.trace("stmt = " + str); stmt.addBatch(str); } if (postalAddresss.size() > 0) { stmt.executeBatch(); } } catch (SQLException e) { RegistryException exception = new RegistryException(e); throw exception; } finally { closeStatement(stmt); } }
From source file:org.apache.hive.beeline.BeeLine.java
void runBatch(List<String> statements) { try {/*from w w w . ja va 2 s . co m*/ Statement stmnt = createStatement(); try { for (Iterator<String> i = statements.iterator(); i.hasNext();) { stmnt.addBatch(i.next().toString()); } int[] counts = stmnt.executeBatch(); output(getColorBuffer().pad(getColorBuffer().bold("COUNT"), 8) .append(getColorBuffer().bold("STATEMENT"))); for (int i = 0; counts != null && i < counts.length; i++) { output(getColorBuffer().pad(counts[i] + "", 8).append(statements.get(i).toString())); } } finally { try { stmnt.close(); } catch (Exception e) { } } } catch (Exception e) { handleException(e); } }
From source file:org.apache.tajo.catalog.store.DBStore.java
private void createBaseTable() throws SQLException { wlock.lock();/*www . ja v a2s. com*/ try { // META Statement stmt = conn.createStatement(); String meta_ddl = "CREATE TABLE " + TB_META + " (version int NOT NULL)"; if (LOG.isDebugEnabled()) { LOG.debug(meta_ddl); } stmt.executeUpdate(meta_ddl); LOG.info("Table '" + TB_META + " is created."); // TABLES stmt = conn.createStatement(); String tables_ddl = "CREATE TABLE " + TB_TABLES + " (" + "TID int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), " + C_TABLE_ID + " VARCHAR(256) NOT NULL CONSTRAINT TABLE_ID_UNIQ UNIQUE, " + "path VARCHAR(1024), " + "store_type CHAR(16), " + "options VARCHAR(32672), " + "CONSTRAINT TABLES_PK PRIMARY KEY (TID)" + ")"; if (LOG.isDebugEnabled()) { LOG.debug(tables_ddl); } stmt.addBatch(tables_ddl); String idx_tables_tid = "CREATE UNIQUE INDEX idx_tables_tid on " + TB_TABLES + " (TID)"; if (LOG.isDebugEnabled()) { LOG.debug(idx_tables_tid); } stmt.addBatch(idx_tables_tid); String idx_tables_name = "CREATE UNIQUE INDEX idx_tables_name on " + TB_TABLES + "(" + C_TABLE_ID + ")"; if (LOG.isDebugEnabled()) { LOG.debug(idx_tables_name); } stmt.addBatch(idx_tables_name); stmt.executeBatch(); LOG.info("Table '" + TB_TABLES + "' is created."); // COLUMNS stmt = conn.createStatement(); String columns_ddl = "CREATE TABLE " + TB_COLUMNS + " (" + "TID INT NOT NULL REFERENCES " + TB_TABLES + " (TID) ON DELETE CASCADE, " + C_TABLE_ID + " VARCHAR(256) NOT NULL REFERENCES " + TB_TABLES + "(" + C_TABLE_ID + ") ON DELETE CASCADE, " + "column_id INT NOT NULL," + "column_name VARCHAR(256) NOT NULL, " + "data_type CHAR(16), " + "CONSTRAINT C_COLUMN_ID UNIQUE (" + C_TABLE_ID + ", column_name))"; if (LOG.isDebugEnabled()) { LOG.debug(columns_ddl); } stmt.addBatch(columns_ddl); String idx_fk_columns_table_name = "CREATE UNIQUE INDEX idx_fk_columns_table_name on " + TB_COLUMNS + "(" + C_TABLE_ID + ", column_name)"; if (LOG.isDebugEnabled()) { LOG.debug(idx_fk_columns_table_name); } stmt.addBatch(idx_fk_columns_table_name); stmt.executeBatch(); LOG.info("Table '" + TB_COLUMNS + " is created."); // OPTIONS stmt = conn.createStatement(); String options_ddl = "CREATE TABLE " + TB_OPTIONS + " (" + C_TABLE_ID + " VARCHAR(256) NOT NULL REFERENCES TABLES (" + C_TABLE_ID + ") " + "ON DELETE CASCADE, " + "key_ VARCHAR(256) NOT NULL, value_ VARCHAR(256) NOT NULL)"; if (LOG.isDebugEnabled()) { LOG.debug(options_ddl); } stmt.addBatch(options_ddl); String idx_options_key = "CREATE INDEX idx_options_key on " + TB_OPTIONS + " (" + C_TABLE_ID + ")"; if (LOG.isDebugEnabled()) { LOG.debug(idx_options_key); } stmt.addBatch(idx_options_key); String idx_options_table_name = "CREATE INDEX idx_options_table_name on " + TB_OPTIONS + "(" + C_TABLE_ID + ")"; if (LOG.isDebugEnabled()) { LOG.debug(idx_options_table_name); } stmt.addBatch(idx_options_table_name); stmt.executeBatch(); LOG.info("Table '" + TB_OPTIONS + " is created."); // INDEXES stmt = conn.createStatement(); String indexes_ddl = "CREATE TABLE " + TB_INDEXES + "(" + "index_name VARCHAR(256) NOT NULL PRIMARY KEY, " + C_TABLE_ID + " VARCHAR(256) NOT NULL REFERENCES TABLES (" + C_TABLE_ID + ") " + "ON DELETE CASCADE, " + "column_name VARCHAR(256) NOT NULL, " + "data_type VARCHAR(256) NOT NULL, " + "index_type CHAR(32) NOT NULL, " + "is_unique BOOLEAN NOT NULL, " + "is_clustered BOOLEAN NOT NULL, " + "is_ascending BOOLEAN NOT NULL)"; if (LOG.isDebugEnabled()) { LOG.debug(indexes_ddl); } stmt.addBatch(indexes_ddl); String idx_indexes_key = "CREATE UNIQUE INDEX idx_indexes_key ON " + TB_INDEXES + " (index_name)"; if (LOG.isDebugEnabled()) { LOG.debug(idx_indexes_key); } stmt.addBatch(idx_indexes_key); String idx_indexes_columns = "CREATE INDEX idx_indexes_columns ON " + TB_INDEXES + " (" + C_TABLE_ID + ", column_name)"; if (LOG.isDebugEnabled()) { LOG.debug(idx_indexes_columns); } stmt.addBatch(idx_indexes_columns); stmt.executeBatch(); LOG.info("Table '" + TB_INDEXES + "' is created."); String stats_ddl = "CREATE TABLE " + TB_STATISTICS + "(" + C_TABLE_ID + " VARCHAR(256) NOT NULL REFERENCES TABLES (" + C_TABLE_ID + ") " + "ON DELETE CASCADE, " + "num_rows BIGINT, " + "num_bytes BIGINT)"; if (LOG.isDebugEnabled()) { LOG.debug(stats_ddl); } stmt.addBatch(stats_ddl); String idx_stats_fk_table_name = "CREATE INDEX idx_stats_table_name ON " + TB_STATISTICS + " (" + C_TABLE_ID + ")"; if (LOG.isDebugEnabled()) { LOG.debug(idx_stats_fk_table_name); } stmt.addBatch(idx_stats_fk_table_name); stmt.executeBatch(); LOG.info("Table '" + TB_STATISTICS + "' is created."); } finally { wlock.unlock(); } }
From source file:com.intellectualcrafters.plot.database.SQLManager.java
/** * @return//from w w w . j a v a2 s. c o m */ @Override public LinkedHashMap<String, HashMap<PlotId, Plot>> getPlots() { final LinkedHashMap<String, HashMap<PlotId, Plot>> newplots = new LinkedHashMap<String, HashMap<PlotId, Plot>>(); try { final DatabaseMetaData data = connection.getMetaData(); ResultSet rs = data.getColumns(null, null, prefix + "plot", "plot_id"); final boolean execute = rs.next(); if (execute) { final Statement statement = connection.createStatement(); statement.addBatch("ALTER IGNORE TABLE `" + prefix + "plot` ADD `plot_id_x` int(11) DEFAULT 0"); statement.addBatch("ALTER IGNORE TABLE `" + prefix + "plot` ADD `plot_id_z` int(11) DEFAULT 0"); statement.addBatch("UPDATE `" + prefix + "plot` SET\n" + " `plot_id_x` = IF(" + " LOCATE(';', `plot_id`) > 0," + " SUBSTRING(`plot_id`, 1, LOCATE(';', `plot_id`) - 1)," + " `plot_id`" + " )," + " `plot_id_z` = IF(" + " LOCATE(';', `plot_id`) > 0," + " SUBSTRING(`plot_id`, LOCATE(';', `plot_id`) + 1)," + " NULL" + " )"); statement.addBatch("ALTER TABLE `" + prefix + "plot` DROP `plot_id`"); statement.addBatch( "ALTER IGNORE TABLE `" + prefix + "plot_settings` ADD `flags` VARCHAR(512) DEFAULT NULL"); statement.executeBatch(); statement.close(); } rs = data.getColumns(null, null, prefix + "plot_settings", "merged"); if (!rs.next()) { final Statement statement = connection.createStatement(); statement.addBatch("ALTER TABLE `" + prefix + "plot_settings` ADD `merged` int(11) DEFAULT NULL"); statement.executeBatch(); statement.close(); } } catch (final Exception e) { e.printStackTrace(); } final HashMap<Integer, Plot> plots = new HashMap<Integer, Plot>(); Statement stmt = null; try { Set<String> worlds = new HashSet<String>(); if (PlotMain.config.contains("worlds")) { worlds = PlotMain.config.getConfigurationSection("worlds").getKeys(false); } final HashMap<String, UUID> uuids = new HashMap<String, UUID>(); final HashMap<String, Integer> noExist = new HashMap<String, Integer>(); /* * Getting plots */ stmt = connection.createStatement(); ResultSet r = stmt.executeQuery( "SELECT `id`, `plot_id_x`, `plot_id_z`, `owner`, `world` FROM `" + prefix + "plot`"); PlotId plot_id; int id; Plot p; String o; UUID user; while (r.next()) { plot_id = new PlotId(r.getInt("plot_id_x"), r.getInt("plot_id_z")); id = r.getInt("id"); final String worldname = r.getString("world"); if (!worlds.contains(worldname)) { if (noExist.containsKey(worldname)) { noExist.put(worldname, noExist.get(worldname) + 1); } else { noExist.put(worldname, 1); } } o = r.getString("owner"); user = uuids.get(o); if (user == null) { user = UUID.fromString(o); uuids.put(o, user); } p = new Plot(plot_id, user, Biome.FOREST, new ArrayList<UUID>(), new ArrayList<UUID>(), new ArrayList<UUID>(), "", PlotHomePosition.DEFAULT, null, worldname, new boolean[] { false, false, false, false }); plots.put(id, p); } // stmt.close(); /* * Getting helpers */ // stmt = connection.createStatement(); r = stmt.executeQuery("SELECT `user_uuid`, `plot_plot_id` FROM `" + prefix + "plot_helpers`"); while (r.next()) { id = r.getInt("plot_plot_id"); o = r.getString("user_uuid"); user = uuids.get(o); if (user == null) { user = UUID.fromString(o); uuids.put(o, user); } final Plot plot = plots.get(id); if (plot != null) { plot.addHelper(user); } else { PlotMain.sendConsoleSenderMessage("&cPLOT " + id + " in plot_helpers does not exist. Please create the plot or remove this entry."); } } // stmt.close(); /* * Getting trusted */ // stmt = connection.createStatement(); r = stmt.executeQuery("SELECT `user_uuid`, `plot_plot_id` FROM `" + prefix + "plot_trusted`"); while (r.next()) { id = r.getInt("plot_plot_id"); o = r.getString("user_uuid"); user = uuids.get(o); if (user == null) { user = UUID.fromString(o); uuids.put(o, user); } final Plot plot = plots.get(id); if (plot != null) { plot.addTrusted(user); } else { PlotMain.sendConsoleSenderMessage("&cPLOT " + id + " in plot_trusted does not exist. Please create the plot or remove this entry."); } } // stmt.close(); /* * Getting denied */ // stmt = connection.createStatement(); r = stmt.executeQuery("SELECT `user_uuid`, `plot_plot_id` FROM `" + prefix + "plot_denied`"); while (r.next()) { id = r.getInt("plot_plot_id"); o = r.getString("user_uuid"); user = uuids.get(o); if (user == null) { user = UUID.fromString(o); uuids.put(o, user); } final Plot plot = plots.get(id); if (plot != null) { plot.addDenied(user); } else { PlotMain.sendConsoleSenderMessage("&cPLOT " + id + " in plot_denied does not exist. Please create the plot or remove this entry."); } } // stmt.close(); // stmt = connection.createStatement(); r = stmt.executeQuery("SELECT * FROM `" + prefix + "plot_settings`"); while (r.next()) { id = r.getInt("plot_plot_id"); final Plot plot = plots.get(id); if (plot != null) { final String b = r.getString("biome"); Biome biome = null; if (b != null) { for (final Biome mybiome : Biome.values()) { if (mybiome.toString().equalsIgnoreCase(b)) { biome = mybiome; break; } } } final String alias = r.getString("alias"); if (alias != null) { plot.settings.setAlias(alias); } final String pos = r.getString("position"); if (pos != null) { for (final PlotHomePosition plotHomePosition : PlotHomePosition.values()) { if (plotHomePosition.isMatching(pos)) { if (plotHomePosition != PlotHomePosition.DEFAULT) { plot.settings.setPosition(plotHomePosition); } break; } } } final Integer m = r.getInt("merged"); if (m != null) { final boolean[] merged = new boolean[4]; for (int i = 0; i < 4; i++) { merged[3 - i] = ((m) & (1 << i)) != 0; } plot.settings.setMerged(merged); } else { plot.settings.setMerged(new boolean[] { false, false, false, false }); } String[] flags_string; final String myflags = r.getString("flags"); if (myflags == null) { flags_string = new String[] {}; } else { flags_string = myflags.split(","); } final ArrayList<Flag> flags = new ArrayList<Flag>(); boolean exception = false; for (final String element : flags_string) { if (element.contains(":")) { final String[] split = element.split(":"); try { flags.add(new Flag(FlagManager.getFlag(split[0], true), split[1].replaceAll("\u00AF", ":").replaceAll("", ","))); } catch (final Exception e) { exception = true; } } else { flags.add(new Flag(FlagManager.getFlag(element, true), "")); } } if (exception) { PlotMain.sendConsoleSenderMessage( "&cPlot " + id + " had an invalid flag. A fix has been attempted."); setFlags(id, flags.toArray(new Flag[0])); } plot.settings.setFlags(flags.toArray(new Flag[0])); } else { PlotMain.sendConsoleSenderMessage("&cPLOT " + id + " in plot_settings does not exist. Please create the plot or remove this entry."); } } stmt.close(); for (final Plot plot : plots.values()) { final String world = plot.world; if (!newplots.containsKey(world)) { newplots.put(world, new HashMap<PlotId, Plot>()); } newplots.get(world).put(plot.id, plot); } boolean invalidPlot = false; for (final String worldname : noExist.keySet()) { invalidPlot = true; PlotMain.sendConsoleSenderMessage("&c[WARNING] Found " + noExist.get(worldname) + " plots in DB for non existant world; '" + worldname + "'."); } if (invalidPlot) { PlotMain.sendConsoleSenderMessage( "&c[WARNING] - Please create the world/s or remove the plots using the purge command"); } } catch (final SQLException e) { Logger.add(LogLevel.WARNING, "Failed to load plots."); e.printStackTrace(); } return newplots; }
From source file:com.edgenius.wiki.installation.DBLoader.java
public void resetTable(String dbType, ConnectionProxy con) throws SQLException, IOException { Statement dropStat = null;/* w ww .j ava2 s . co m*/ Statement stat = null; try { log.info("Creating tables..."); dropStat = con.createStatement(); stat = con.createStatement(); List<String> lines = loadSQLFile(dbType, dbType + ".ddl"); for (String sql : lines) { //need know if table already exist, if exist need run alter table ... drop index..., otherwise, skip that sql = sql.replaceAll("\n", " ").trim(); // Here is really a special patch for MYSQL 4 as I don't want to waste much time on this special issue // key size is over 1024 issue on MySQL 4 http://bugs.mysql.com/bug.php?id=4541 if (sql.toLowerCase().startsWith( "create index page_link_index on " + Constants.TABLE_PREFIX.toLowerCase() + "page_links")) { try { //only mysql4 may throw exception, ignore it. dropStat.execute(sql); } catch (Exception e) { log.error("Create page link index operation failed...."); } continue; } //1. don't detect table exist because it only check if role table has data, if partial create, isTableExist() won't work //2. put drop independent and try..catch b/c some DBs will report error if table not exist while drop if (sql.toLowerCase().startsWith("alter table ") && sql.toLowerCase().indexOf(" drop ") != -1 || sql.toLowerCase().startsWith("drop ")) { try { dropStat.execute(sql); } catch (Exception e) { log.error("Drop operation failed. It is OK for initial installation."); } continue; } stat.addBatch(sql); } stat.executeBatch(); log.info("Initialize data for system..."); lines = loadSQLFile(dbType, dbType + "-init-tables.sql"); for (String sql : lines) { sql = sql.replaceAll("\n", " ").trim(); stat.addBatch(sql); } stat.executeBatch(); log.info("Initialize quartz tables for system..."); lines = loadSQLFile(dbType, dbType + "-quartz.sql"); for (String sql : lines) { sql = sql.replaceAll("\n", " ").trim(); if (sql.toLowerCase().startsWith("drop ")) { try { dropStat.execute(sql); } catch (Exception e) { log.error("Drop operation failed...." + sql); } continue; } stat.addBatch(sql); } stat.executeBatch(); log.info("System all tables and initial data are ready"); } finally { if (stat != null) stat.close(); if (dropStat != null) dropStat.close(); } }
From source file:com.flexive.core.storage.genericSQL.GenericTreeStorageSpreaded.java
/** * {@inheritDoc}//from w w w . j av a 2s . c o m */ @Override public void activateNode(Connection con, SequencerEngine seq, ContentEngine ce, FxTreeMode mode, final long nodeId, boolean activateContents) throws FxApplicationException { if (mode == FxTreeMode.Live) //Live tree can not be activated! return; long ids[] = getIdChain(con, mode, nodeId); //all id's up to the root node acquireLocksForUpdate(con, mode, Arrays.asList(ArrayUtils.toObject(ids))); try { // lock node in live tree including all children (which *can* be removed if they were removed in the edit tree) acquireLocksForUpdate(con, FxTreeMode.Live, selectDirectChildNodeIds(con, FxTreeMode.Live, nodeId, true)); } catch (SQLException e) { throw new FxDbException(e); } for (long id : ids) { if (id == ROOT_NODE) continue; FxTreeNode srcNode = getNode(con, mode, id); //check if the node already exists in the live tree if (exists(con, FxTreeMode.Live, id)) { //Move and setData will not do anything if the node is already in its correct place and move(con, seq, FxTreeMode.Live, id, srcNode.getParentNodeId(), srcNode.getPosition()); setData(con, FxTreeMode.Live, id, srcNode.getData()); } else { createNode(con, seq, ce, FxTreeMode.Live, srcNode.getId(), srcNode.getParentNodeId(), srcNode.getName(), srcNode.getLabel(), srcNode.getPosition(), srcNode.getReference(), srcNode.getData(), activateContents); } // Remove all deleted direct child nodes Statement stmt = null; Statement stmt2 = null; try { stmt = con.createStatement(); stmt2 = con.createStatement(); if (StorageManager.isDisableIntegrityTransactional()) { stmt2.execute(StorageManager.getReferentialIntegrityChecksStatement(false)); } try { ResultSet rs = stmt.executeQuery( "SELECT DISTINCT tl.ID FROM " + getTable(FxTreeMode.Live) + " tl " + "LEFT JOIN " + getTable(FxTreeMode.Edit) + " te ON tl.ID=te.ID WHERE te.ID=null AND " + "te.PARENT=" + nodeId + " AND tl.PARENT=" + nodeId); while (rs != null && rs.next()) { long deleteId = rs.getLong(1); // System.out.println("==> deleted:"+deleteId); acquireLocksForUpdate(con, FxTreeMode.Live, Arrays.asList(deleteId)); stmt2.addBatch("DELETE FROM " + getTable(FxTreeMode.Live) + " WHERE ID=" + deleteId); } stmt2.addBatch("UPDATE " + getTable(FxTreeMode.Live) + " SET MODIFIED_AT=" + System.currentTimeMillis()); stmt2.executeBatch(); } finally { if (StorageManager.isDisableIntegrityTransactional()) { stmt2.execute(StorageManager.getReferentialIntegrityChecksStatement(true)); } } } catch (SQLException e) { throw new FxTreeException("ex.tree.activate.failed", nodeId, false, e.getMessage()); } finally { try { if (stmt != null) stmt.close(); } catch (Exception exc) { //ignore } try { if (stmt2 != null) stmt2.close(); } catch (Exception exc) { //ignore } } clearDirtyFlag(con, mode, nodeId); } }
From source file:com.flexive.core.storage.genericSQL.GenericTreeStorageSimple.java
/** * {@inheritDoc}/*w ww . j a v a2s .co m*/ */ @Override public void move(Connection con, SequencerEngine seq, FxTreeMode mode, long nodeId, long newParentId, int newPosition) throws FxApplicationException { FxTreeNodeInfo node = getTreeNodeInfo(con, mode, nodeId); final FxTreeNodeInfo parentNode = getTreeNodeInfo(con, mode, node.getParentId()); if (newPosition < 0) newPosition = 0; else if (newPosition > parentNode.getDirectChildCount()) newPosition = parentNode.getDirectChildCount() - 1; final boolean sameParent = parentNode.getId() == newParentId; if (sameParent && node.getPosition() == newPosition) return; //nothing to do int depthDelta = 0; FxTreeNodeInfo newParentNode; if (!sameParent) { newParentNode = getTreeNodeInfo(con, mode, newParentId); depthDelta = (newParentNode.getDepth() + 1) - node.getDepth(); } else newParentNode = parentNode; //make room for the node at destination position long destLeft, destRight; if (!newParentNode.hasChildren() || newPosition == 0) { //first destLeft = newParentNode.getLeft().longValue() + 1; } else if (newParentNode.getDirectChildCount() - 1 == newPosition) { //last destLeft = newParentNode.getRight().longValue()/* - 2*/; } else { //middle destLeft = getTreeNodeInfoAt(con, mode, newParentNode, newPosition).getLeft().longValue(); } //dest right = dest left + "width" of node final long nodeWidth = node.getRight().longValue() - node.getLeft().longValue() + 1; destRight = destLeft + nodeWidth - 2; PreparedStatement ps = null; Statement stmt = null; try { //open enough space to hold the node ps = con.prepareStatement("UPDATE " + getTable(mode) + " SET LFT=LFT+? WHERE LFT>=?"); ps.setLong(1, nodeWidth); ps.setLong(2, destLeft); ps.executeUpdate(); ps.close(); ps = con.prepareStatement("UPDATE " + getTable(mode) + " SET RGT=RGT+? WHERE RGT>=?"); ps.setLong(1, nodeWidth); ps.setLong(2, destRight); ps.executeUpdate(); ps.close(); //node may have been moved as well, refetch it FxTreeNodeInfo movedNode = getTreeNodeInfo(con, mode, nodeId); //move the node into the created gap final long delta = movedNode.getLeft().longValue() - destLeft; ps = con.prepareStatement( "UPDATE " + getTable(mode) + " SET LFT=LFT-(?), RGT=RGT-(?) WHERE LFT>=? AND RGT<=?"); ps.setLong(1, delta); ps.setLong(2, delta); ps.setLong(3, movedNode.getLeft().longValue()); ps.setLong(4, movedNode.getRight().longValue()); ps.executeUpdate(); //close the gap from the original node ps.close(); ps = con.prepareStatement("UPDATE " + getTable(mode) + " SET RGT=RGT-? WHERE RGT>?"); ps.setLong(1, nodeWidth); ps.setLong(2, movedNode.getRight().longValue()); ps.executeUpdate(); ps.close(); ps = con.prepareStatement("UPDATE " + getTable(mode) + " SET LFT=LFT-? WHERE LFT>?"); ps.setLong(1, nodeWidth); ps.setLong(2, movedNode.getRight().longValue()); ps.executeUpdate(); // Update the parent of the node stmt = con.createStatement(); stmt.addBatch("UPDATE " + getTable(mode) + " SET PARENT=" + newParentId + " WHERE ID=" + nodeId); if (mode != FxTreeMode.Live) stmt.addBatch("UPDATE " + getTable(mode) + " SET DIRTY=TRUE WHERE ID=" + nodeId); stmt.executeBatch(); stmt.close(); // Update the childcount of the new and old parent if needed + set dirty flag if (!sameParent) { FxTreeNodeInfo nodeOldParent = getTreeNodeInfo(con, mode, node.getParentId()); node = getTreeNodeInfo(con, mode, nodeId); stmt = con.createStatement(); stmt.addBatch("UPDATE " + getTable(mode) + " SET CHILDCOUNT=CHILDCOUNT+1 WHERE ID=" + newParentId); stmt.addBatch("UPDATE " + getTable(mode) + " SET CHILDCOUNT=CHILDCOUNT-1 WHERE ID=" + nodeOldParent.getId()); if (mode != FxTreeMode.Live) { stmt.addBatch("UPDATE " + getTable(mode) + " SET DIRTY=TRUE, DEPTH=DEPTH+(" + depthDelta + ") WHERE LFT>=" + node.getLeft().longValue() + " AND " + "RGT<=" + node.getRight().longValue()); stmt.addBatch("UPDATE " + getTable(mode) + " SET DIRTY=TRUE WHERE ID IN(" + node.getParentId() + "," + newParentId + ")"); } stmt.executeBatch(); stmt.close(); } } catch (SQLException e) { throw new FxTreeException(e, "ex.tree.move.failed", node.getId(), newParentId, newPosition, e.getMessage()); } finally { try { if (ps != null) ps.close(); } catch (SQLException e) { //ignore } try { if (stmt != null) stmt.close(); } catch (SQLException e) { //ignore } } }
From source file:thesis.GetData.java
public void get_data_from_web() { Connection conn = getConnectiontoDB(); String limit = "limit"; if (jCheckBox3.isSelected()) { System.out.println("limit fetch selected!"); limit = limit + " " + jTextField2.getText(); System.out.println("limit fetch " + limit); } else//from w w w . jav a 2 s .c om limit = ""; try { Statement stmt = conn.createStatement(); if (jCheckBox1.isSelected()) { String a = "TRUNCATE TABLE olx;"; String b = "TRUNCATE TABLE lazada"; String c = "TRUNCATE TABLE ebay"; String d = "TRUNCATE TABLE ads"; String e = "TRUNCATE TABLE ontology_system_final_table"; stmt.addBatch(a); stmt.addBatch(b); stmt.addBatch(c); stmt.addBatch(d); stmt.addBatch(e); } String f = "LOAD DATA LOCAL INFILE 'C:/Users/test/Documents/final ad crawler csv/ebay crawl.csv' INTO TABLE ebay FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES;\n"; String g = "LOAD DATA LOCAL INFILE 'C:/Users/test/Documents/final ad crawler csv/olx crawl.csv' INTO TABLE olx FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES;# 581 rows affected.\n"; String h = "LOAD DATA LOCAL INFILE 'C:/Users/test/Documents/final ad crawler csv/lazada crawl.csv' INTO TABLE lazada FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES;# 32 rows affected.\n"; String i = "INSERT INTO ads (ad_name, site, price, location, posted_by, description, image, link)\n" + "(SELECT link, g, h, cdf, i, asd, image, e FROM ebay " + limit + ")\n" + "union\n" + "(SELECT g, ad_name, h, dsf, cdf, zxcz, i, link FROM olx " + limit + ")\n" + "union\n" + "(SELECT g, ad_name, cxzc, cdf, zxcz, dsf, h, link FROM lazada " + limit + " )"; stmt.addBatch(f); stmt.addBatch(g); stmt.addBatch(h); stmt.addBatch(i); stmt.executeBatch(); } catch (SQLException sQLException) { System.err.println("sQLException = " + sQLException); } }