List of usage examples for java.sql PreparedStatement toString
public String toString()
From source file:org.jumpmind.vaadin.ui.sqlexplorer.TabularResultLayout.java
protected void followTo(ForeignKey foreignKey) { Collection<Object> selectedRows = grid.getSelectedRows(); if (selectedRows.size() > 0) { log.info("Following foreign key to " + foreignKey.getForeignTableName()); if (queryPanel == null) { if (explorer != null) { queryPanel = explorer.openQueryWindow(db); } else { log.error("Failed to find current or create new query tab"); }//from ww w .j av a 2s.c o m } Table foreignTable = foreignKey.getForeignTable(); if (foreignTable == null) { foreignTable = db.getPlatform().getTableFromCache(foreignKey.getForeignTableName(), false); } Reference[] references = foreignKey.getReferences(); for (Reference ref : references) { if (ref.getForeignColumn() == null) { ref.setForeignColumn(foreignTable.getColumnWithName(ref.getForeignColumnName())); } } String sql = createFollowSql(foreignTable, references, selectedRows.size()); try { PreparedStatement ps = ((DataSource) db.getPlatform().getDataSource()).getConnection() .prepareStatement(sql); int i = 1; for (Object row : selectedRows) { for (Reference ref : references) { Object targetObject = grid.getContainerDataSource().getItem(row) .getItemProperty(ref.getLocalColumnName()).getValue(); int targetType = ref.getForeignColumn().getMappedTypeCode(); ps.setObject(i, targetObject, targetType); i++; } } sql = ps.toString().substring(ps.toString().indexOf("select ")); queryPanel.executeSql(sql, false); } catch (SQLException e) { log.error("Failed to follow foreign key", e); } } }
From source file:org.sakaiproject.webservices.SakaiReport.java
protected String getQueryAsString(String query, Object[] args, int rowCount, String type) { Connection conn = null;/*from w w w .j a va2 s . c om*/ PreparedStatement ps = null; ResultSet rs = null; try { conn = sqlService.borrowConnection(); conn.setReadOnly(true); ps = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); if (rowCount > 0) { ps.setMaxRows(rowCount); } for (int i = 0; i < args.length; i++) { if (args[i] instanceof String) { ps.setString(i + 1, (String) args[i]); } else if (args[i] instanceof java.util.Date) { // select * from sakai_event where event_date between to_date('2001-12-12 12:12','YYYY-MM-DD HH24:MI') and to_date('2017-12-12 12:12','YYYY-MM-DD HH24:MI') if (sqlService.getVendor().equals("oracle")) { ps.setString(i + 1, df.format(args[i])); // select * from sakai_event where event_date between '2001-12-12 12:12' and '2017-12-12 12:12'; } else { ps.setString(i + 1, df.format(args[i])); } } } LOG.info("preparing query: " + ps.toString()); rs = ps.executeQuery(); //return toJsonString(rs); if (type == TYPE_CSV) { return stripInvalidXmlCharacters(toCsvString(rs)); } if (type == TYPE_CSV_WITH_HEADER_ROW) { return stripInvalidXmlCharacters(toCsvString(rs, true)); } if (type == TYPE_JSON) { return stripInvalidXmlCharacters(toJsonString(rs)); } return Xml.writeDocumentToString(toDocument(rs)); } catch (Exception e) { LOG.error(e.getMessage(), e); throw new RuntimeException(e.getMessage(), e); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { } } if (ps != null) { try { ps.close(); } catch (SQLException e) { } } if (conn != null) { try { conn.close(); } catch (SQLException e) { } } } }
From source file:com.amazonbird.announce.ProductMgrImpl.java
public ArrayList<Product> selectProducts(String where, String orderBy) { Connection connection = null; PreparedStatement ps = null; ResultSet rs = null;// ww w . jav a 2 s.com ArrayList<Product> productList = new ArrayList<Product>(); String whereStr = (Util.stringIsValid(where)) ? where : ""; String orderbyStr = (Util.stringIsValid(orderBy)) ? orderBy : ""; try { connection = dbMgr.getConnection(); ps = connection.prepareStatement(LOAD_PRODUCTS + " " + whereStr + " " + orderbyStr); rs = ps.executeQuery(); while (rs.next()) { Product product = new Product(); product.getDataFromResultSet(rs); productList.add(product); } for (Product product : productList) { ps = connection.prepareStatement(LOAD_PRODUCT_PICTURES); ps.setLong(1, product.getId()); rs = ps.executeQuery(); ArrayList<String> pictureUrlList = new ArrayList<String>(); while (rs.next()) { pictureUrlList.add(rs.getString("imageurl")); } String[] urlArr = new String[pictureUrlList.size()]; int i = 0; for (String url : pictureUrlList) { urlArr[i] = FileUtil.getInstance().getFilePathLogical() + url; i++; } product.setPictureUrls(urlArr); } logger.debug(DBConstants.QUERY_EXECUTION_SUCC + ps.toString()); } catch (SQLException ex) { logger.error(DBConstants.QUERY_EXECUTION_FAIL + ps.toString(), ex); } finally { dbMgr.closeResources(connection, ps, rs); } return productList; }
From source file:de.ingrid.importer.udk.strategy.v1.IDCStrategy1_0_2_clean.java
protected void importDefaultCatalogData() throws Exception { if (log.isInfoEnabled()) { log.info("Creating default catalog..."); }/*from ww w . j av a2 s . c om*/ pSqlStr = "INSERT INTO t03_catalogue (id, cat_uuid, cat_name, partner_name , provider_name, country_code," + "workflow_control, expiry_duration, create_time, mod_uuid, mod_time, language_code) VALUES " + "( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; PreparedStatement p = jdbc.prepareStatement(pSqlStr); sqlStr = "DELETE FROM t03_catalogue"; jdbc.executeUpdate(sqlStr); String currentTime = IDCStrategyHelper.transDateTime(new Date()); int cnt = 1; dataProvider.setId(dataProvider.getId() + 1); p.setLong(cnt++, dataProvider.getId()); // id p.setString(cnt++, UuidGenerator.getInstance().generateUuid()); // cat_uuid p.setString(cnt++, getImportDescriptor().getIdcCatalogueName()); // cat_name p.setString(cnt++, getImportDescriptor().getIdcPartnerName()); // partner_name p.setString(cnt++, getImportDescriptor().getIdcProviderName()); // provider_name p.setString(cnt++, getImportDescriptor().getIdcCatalogueCountry()); // country_code p.setString(cnt++, "N"); // workflow_control p.setNull(cnt++, Types.INTEGER); // expiry_duration p.setString(cnt++, currentTime); // create_time String modUuid = null; String modTime = null; String sql = "SELECT adr_uuid FROM t02_address"; Statement st = jdbc.createStatement(); ResultSet rs = jdbc.executeQuery(sql, st); if (rs.next()) { modUuid = rs.getString("adr_uuid"); if (modUuid != null) { modTime = currentTime; } } rs.close(); st.close(); p.setString(cnt++, modUuid); // mod_uuid, p.setString(cnt++, modTime); // mod_time p.setString(cnt++, getCatalogLanguageFromDescriptor()); // language_code try { p.executeUpdate(); } catch (Exception e) { log.error("Error executing SQL: " + p.toString(), e); throw e; } if (log.isInfoEnabled()) { log.info("Creating default catalog... done."); } }
From source file:com.chiorichan.database.DatabaseEngine.java
public ResultSet query(String query, boolean retried, Object... args) throws SQLException { PreparedStatement stmt = null; ResultSet result = null;//from w ww . ja v a2 s .c o m if (con == null) throw new SQLException("The SQL connection is closed or was never opened."); try { stmt = con.prepareStatement(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); int x = 0; for (Object s : args) try { x++; Loader.getLogger().debug(x + " -> " + ObjectUtil.castToString(s)); stmt.setString(x, ObjectUtil.castToString(s)); } catch (SQLException e) { if (!e.getMessage().startsWith("Parameter index out of range")) throw e; } result = stmt.executeQuery(); Loader.getLogger() .fine("SQL Query `" + stmt.toString() + "` returned " + getRowCount(result) + " rows!"); } catch (CommunicationsException | MySQLNonTransientConnectionException e) { if (!retried && reconnect()) return query(query, true, args); else { throw e; } } catch (Throwable t) { t.printStackTrace(); throw t; } return result; }
From source file:sopho.Ofeloumenoi.AddOfeloumenoiController.java
@FXML public void Save(ActionEvent event) { if (barcode.getText().isEmpty() || onoma.getText().isEmpty() || eponimo.getText().isEmpty() || patronimo.getText().isEmpty()) { //checking if the user has filled the required fields sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? ? . ? ? Barcode, , ? ? ? ?", "error"); cm.showAndWait();// w w w. j a v a 2 s .c om } else if (!NumberUtils.isNumber(barcode.getText()) && !barcode.getText().isEmpty()) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " barcode ? ? ??. ? ? .", "error"); cm.showAndWait(); } else if (!NumberUtils.isNumber(eisodima.getText()) && !eisodima.getText().isEmpty()) { sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? ? ??. ? ? .", "error"); cm.showAndWait(); } else {//the user has filled the required fields. We can proceed. sopho.DBClass db = new sopho.DBClass(); Connection conn = null; PreparedStatement pst = null; ResultSet rs = null; String teknaDB = ""; //we create a var to push data to db. for (int i = 0; i < tekna.getItems().size(); i++) {//we are converting the table rows to a single comma separated string to push it to the database in a single entry. tableManager tbl = (tableManager) tekna.getItems().get(i); if (!tbl.getEtos().equals("? ")) { //we are checking if the user has actually entered a number teknaDB += tbl.getEtos() + ","; //we have to call getEtos from the tableManager class to get the actual value. We add the value to teknaDB and seperate with comma. arithmosTeknon++; } } if (arithmosTeknon > 0) {// we need to catch the case that the user has not added any data to the table. teknaDB = teknaDB.substring(0, teknaDB.length() - 1); // we have to remove the last comma. } conn = db.ConnectDB(); //Now we will check if the user has already registered this ofeloumenos String sql = "SELECT * FROM ofeloumenoi WHERE barcode =?"; try { pst = conn.prepareStatement(sql); pst.setString(1, barcode.getText()); System.out.println("the query is:" + pst.toString()); rs = pst.executeQuery(); rs.last(); //i go to the last line of the result to find out the number of the line if (rs.getRow() > 0) {// ofeloumenos is already registered to the database sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", "? ? barcode. ? ? . Barcode:" + rs.getString("barcode") + " : " + rs.getString("eponimo") + " : " + rs.getString("onoma") + " ?: " + rs.getString("patronimo"), "error"); cm.showAndWait(); } else { // we can push the data to database... sql = "INSERT INTO ofeloumenoi (barcode, eponimo, onoma, patronimo, mitronimo, imGennisis, dieuthinsi, dimos, tilefono, anergos, epaggelma, eisodima, eksartiseis, photoID, afm, tautotita, ethnikotita, metanastis, roma, oikKatastasi, hasTekna, arithmosTeknon, ilikiesTeknon, politeknos, monogoneiki, mellousaMama, amea, asfForeas, xronios, pathisi, anoTon60, monaxikos, emfiliVia, spoudastis, anenergos, loipa, registerDate) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; pst = conn.prepareStatement(sql); //now we will set the values to the sql statement pst.setString(1, barcode.getText()); pst.setString(2, eponimo.getText()); pst.setString(3, onoma.getText()); pst.setString(4, patronimo.getText()); pst.setString(5, mitronimo.getText()); //now we have to convert the imGennisis to a suitable format to be able to push it to the database if (imGennisis.getValue() != null) { Date date = Date .from(imGennisis.getValue().atStartOfDay(ZoneId.systemDefault()).toInstant()); java.sql.Date sqlDate = new java.sql.Date(date.getTime()); pst.setDate(6, sqlDate); } else { pst.setDate(6, null); } pst.setString(7, dieuthinsi.getText()); pst.setString(8, dimos.getText()); pst.setString(9, tilefono.getText()); pst.setInt(10, anergos.isSelected() ? 1 : 0); //set 1 if selected and 0 if not. We will use this method for all the checkboxes. pst.setString(11, epaggelma.getText()); pst.setString(12, eisodima.getText()); pst.setString(13, eksartiseis.getText()); pst.setString(14, PhotoID); pst.setString(15, afm.getText()); pst.setString(16, tautotita.getText()); pst.setString(17, ethnikotita.getText()); pst.setInt(18, metanastis.isSelected() ? 1 : 0); pst.setInt(19, roma.isSelected() ? 1 : 0); pst.setInt(20, (int) oikKatastasi.getSelectionModel().getSelectedIndex());//we are pushing to database the selected index pst.setInt(21, arithmosTeknon > 0 ? 1 : 0); //checking number of tekna. if >0 has tekna gets 1 pst.setInt(22, arithmosTeknon); pst.setString(23, teknaDB); //here we use the converted to comma separated values variable in order to save the tableView data using only one field in database. pst.setInt(24, politeknos.isSelected() ? 1 : 0); pst.setInt(25, monogoneiki.isSelected() ? 1 : 0); pst.setInt(26, mellousaMama.isSelected() ? 1 : 0); pst.setInt(27, amea.isSelected() ? 1 : 0); pst.setInt(28, (int) asfForeas.getSelectionModel().getSelectedIndex());//we are pushing to database the selected index pst.setInt(29, xronios.isSelected() ? 1 : 0); pst.setString(30, pathisi.getText()); pst.setInt(31, monaxiko.isSelected() ? 1 : 0); pst.setInt(32, anoTon60.isSelected() ? 1 : 0); pst.setInt(33, emfiliVia.isSelected() ? 1 : 0); pst.setInt(34, spoudastis.isSelected() ? 1 : 0); pst.setInt(35, anenergos.isSelected() ? 1 : 0); pst.setString(36, loipa.getText()); //insert today's date as registerDate LocalDate now = LocalDate.now(); java.sql.Date sqlToday = java.sql.Date.valueOf(now); pst.setDate(37, sqlToday); System.out.println("the query is:" + pst.toString()); int linesAffected = pst.executeUpdate(); //checking if the data were inserted to the database successfully if (linesAffected > 0) { Stage stage = (Stage) barcode.getScene().getWindow(); try { sl.StageLoad("/sopho/Ofeloumenoi/AddMore.fxml", stage, false, true); //resizable false, utility true } catch (IOException ex) { Logger.getLogger(AddOfeloumenoiController.class.getName()).log(Level.SEVERE, null, ex); } } else {//problem inserting data... sopho.Messages.CustomMessageController cm = new sopho.Messages.CustomMessageController(null, "?!", " ? ? ? . ? ...", "error"); cm.showAndWait(); } } } catch (SQLException e) { System.out.println( "? ? ? !" + e); } } }
From source file:com.flexoodb.engines.FlexJAXBDBDataEngine2.java
public Collection<Object> runQuery(String query, Class c, boolean usedefaultimplementation) throws Exception { Vector v = new Vector(); Connection conn = null;/*w ww . j a v a 2 s .c o m*/ try { conn = (Connection) _pool.getConnection(); String tablename = query.split("\\s")[3]; // always search the index! if (checkTable(tablename, conn, false)) { StringBuffer q = new StringBuffer("where "); if (query.toUpperCase().indexOf("WHERE") > 0) { String sub = query.substring(query.toUpperCase().indexOf("WHERE") + 5); sub = sub.replaceAll("<=", " <eq; "); sub = sub.replaceAll(">=", " >eq; "); sub = sub.replaceAll("<>", " &nteq; "); sub = sub.replaceAll("=", " = "); sub = sub.replaceAll(">", " > "); sub = sub.replaceAll("<", " < "); sub = sub.replaceAll("<eq;", "<="); sub = sub.replaceAll(">eq;", ">="); sub = sub.replaceAll("&nteq;", "<>").trim(); //System.out.println("from:"+sub); boolean done = false; boolean id = false; int seq = 0; String col = null; String condition = null; while (!done) { int x = sub.indexOf(" "); String word = sub.substring(0, x < 0 ? sub.length() : x); int wlen = word.length(); if (word.startsWith("'")) { word = sub.substring(1, sub.indexOf("'", 1)); wlen = word.length() + 2; } //System.out.println("w:"+word+"< "+wlen+" wl:"+word.length()); // check if its a predicate if (":like:=:>:<:<=:>=:<>:".indexOf(":" + word.toLowerCase() + ":") > -1) { condition = word; seq = 2; } else if (":and:or:not:".indexOf(":" + word.toLowerCase() + ":") > -1) { q.append(" " + word.trim() + " "); seq = 0; } else if (seq == 0)// it must be a field! { seq = 1; // fields sequence if (word.trim().equalsIgnoreCase("parentid") || word.trim().equalsIgnoreCase("id")) { q.append(" " + word.trim()); id = true; } else if (word.trim().equalsIgnoreCase("order")) { String[] order = sub.split("\\s"); if (!order[2].equalsIgnoreCase("id") && !order[2].equalsIgnoreCase("parentid")) { // get the 3rd word -- ie the field if (!q.toString().toUpperCase().endsWith("WHERE")) { q.append(" and "); } q.append(" (element='" + order[2] + "')"); q.append(" " + order[0] + " by value " + sub.substring(sub.indexOf(order[2]) + order[2].length()).trim()); } else { q.append(" " + sub); } done = true; } else if (word.trim().equalsIgnoreCase("element") || word.trim().equalsIgnoreCase("limit") || word.trim().equalsIgnoreCase("desc") || word.trim().equalsIgnoreCase("asc")) { q.append(" " + sub); done = true; } else { word = word.replaceAll("'", "\'").trim(); //q.append(" (element='"+word.trim().replaceAll("'","")+"'"); q.append(" (element='" + word + "'"); //col = word.trim().replaceAll("'",""); col = word; } } else if (seq == 2) { //word = word.replaceAll("'"," "); word = word.replaceAll("'", "\'"); if (id) { q.append("" + condition + "'" + word.trim() + "'"); } else { boolean valchanged = false; try { // we look for dates! if (col != null) { Method met = c.getMethod( "get" + col.substring(0, 1).toUpperCase() + col.substring(1), (Class[]) null); Class c1 = (Class) met.getGenericReturnType(); if (c1.getSimpleName().equalsIgnoreCase("XMLGregorianCalendar") && !word.isEmpty()) { //q.append(" and str_to_date(value,\"%Y-%m-%d\") "+condition+" '"+word.trim().replaceAll("'","")+"')"); q.append(" and str_to_date(value,\"%Y-%m-%d\") " + condition + " '" + word.trim() + "')"); valchanged = true; } } } catch (Exception e) { e.printStackTrace(); } if (!valchanged) { //q.append(" and value "+condition+" '"+word.trim().replaceAll("'","")+"')"); q.append(" and value " + condition + " '" + word.trim() + "')"); } col = null; } seq = 0; condition = null; id = false; } sub = sub.substring(wlen).trim(); if (x < 0 || sub.length() == 0) { done = true; } } } else { int tl = tablename.length(); q = new StringBuffer(query.substring(query.indexOf(tablename) + tl)); } PreparedStatement ps = null; boolean searchindex = false; if (!usedefaultimplementation) { ps = (PreparedStatement) conn.prepareStatement( "select distinct id from " + tablename.toLowerCase() + " " + q.toString()); } else { ps = (PreparedStatement) conn.prepareStatement( "select distinct id from " + tablename.toLowerCase() + "_index " + q.toString()); searchindex = true; } System.out.println(">>>Query:" + ps.toString() + "<<< " + usedefaultimplementation); ResultSet rec = ps.executeQuery(); // check if a record was found while (rec != null && !rec.isClosed() && rec.next()) { String id = rec.getString("id"); try { Object o = null; PreparedStatement ps2 = (PreparedStatement) conn .prepareStatement("select id,parentid,content from " + tablename.toLowerCase() + " where id='" + id + "'"); ResultSet res = ps2.executeQuery(); // check if a record was found if (res != null && res.next()) { String i = res.getString("id"); String p = res.getString("parentid"); o = new FlexContainer(_flexutils.getObject(res.getString("content"), c)); ((FlexContainer) o).setId(i); ((FlexContainer) o).setParentId(p); ps2.close(); } else { ps2.close(); if (searchindex) { // then the values found must be orphans! we delete the index contents removeValues(id, tablename, conn); } } if (o != null) { v.add(o); Enumeration en = v.elements(); while (en.hasMoreElements()) { en.nextElement(); } } } catch (Exception g) { throw g; } } } } catch (Exception f) { throw f; } finally { try { if (conn != null) { _pool.releaseConnection(conn); } } catch (Exception g) { } } return v; }
From source file:org.forgerock.openidm.repo.jdbc.impl.query.TableQueries.java
public Integer command(final String type, Map<String, Object> params, Connection con) throws ResourceException { Integer result = null;//from www. j a v a 2 s . c o m params.put(ServerConstants.RESOURCE_NAME, type); String queryExpression = (String) params.get("commandExpression"); String queryId = (String) params.get("commandId"); if (queryId == null && queryExpression == null) { throw new BadRequestException("Either " + "commandId" + " or " + "commandExpression" + " to identify/define a query must be passed in the parameters. " + params); } final PreparedStatement foundQuery; try { if (queryExpression != null) { foundQuery = resolveInlineQuery(con, queryExpression, params); } else if (commands.queryIdExists(queryId)) { foundQuery = commands.getQuery(con, queryId, type, params); } else { throw new BadRequestException("The passed command identifier " + queryId + " does not match any configured commands on the JDBC repository service."); } } catch (SQLException ex) { throw new InternalServerErrorException("DB reported failure preparing command: " + (queryExpression != null ? queryExpression : commands.getQueryInfo(queryId).getQueryString()) + " with params: " + params + " error code: " + ex.getErrorCode() + " sqlstate: " + ex.getSQLState() + " message: " + ex.getMessage(), ex); } Name eventName = getEventName(queryId); EventEntry measure = Publisher.start(eventName, foundQuery, null); ResultSet rs = null; try { result = foundQuery.executeUpdate(); measure.setResult(result); } catch (SQLException ex) { throw new InternalServerErrorException("DB reported failure executing query " + foundQuery.toString() + " with params: " + params + " error code: " + ex.getErrorCode() + " sqlstate: " + ex.getSQLState() + " message: " + ex.getMessage(), ex); } finally { CleanupHelper.loggedClose(rs); CleanupHelper.loggedClose(foundQuery); measure.end(); } return result; }
From source file:de.ingrid.importer.udk.strategy.v1.IDCStrategyDefault1_0_2.java
protected void postProcess_generic() throws Exception { if (log.isInfoEnabled()) { log.info("Post processing ..."); }/*from ww w . jav a 2s.c o m*/ // set the correct obj_node_id to the object index table // this is necessary, because the node_id is not yet known, when the index is created // --------------------------------------------- if (log.isInfoEnabled()) { log.info("update obj_node_id in object index ..."); } for (Iterator<Row> i = dataProvider.getRowIterator("t01_object"); i.hasNext();) { Row row = i.next(); if (row.get("mod_type") != null && !invalidModTypes.contains(row.get("mod_type"))) { String sql = "SELECT id FROM object_node WHERE obj_id=" + row.getInteger("primary_key"); Statement st = jdbc.createStatement(); ResultSet rs = jdbc.executeQuery(sql, st); if (rs.next()) { jdbc.executeUpdate("UPDATE full_index_obj SET obj_node_id = " + rs.getLong("id") + " WHERE obj_node_id=" + row.getInteger("primary_key")); } rs.close(); st.close(); } } // set the correct addr_node_id to the address index table // this is necessary, because the node_id is not yet known, when the index is created // --------------------------------------------- if (log.isInfoEnabled()) { log.info("update addr_node_id in address index ..."); } for (Iterator<Row> i = dataProvider.getRowIterator("t02_address"); i.hasNext();) { Row row = i.next(); if (row.get("mod_type") != null && !invalidModTypes.contains(row.get("mod_type"))) { String sql = "SELECT id FROM address_node WHERE addr_id=" + row.getInteger("primary_key"); Statement st = jdbc.createStatement(); ResultSet rs = jdbc.executeQuery(sql, st); if (rs.next()) { jdbc.executeUpdate("UPDATE full_index_addr SET addr_node_id = " + rs.getLong("id") + " WHERE addr_node_id=" + row.getInteger("primary_key")); } rs.close(); st.close(); } } // set responsible user to cat-admin in entities // --------------------------------------------- if (log.isInfoEnabled()) { log.info("set responsible_uuid in entities to catadmin ..."); } String catAdminUuid = null; String sql = "SELECT addr_uuid FROM idc_user WHERE idc_role=" + ROLE_CATALOG_ADMINISTRATOR; Statement st = jdbc.createStatement(); ResultSet rs = jdbc.executeQuery(sql, st); if (rs.next()) { catAdminUuid = rs.getString("addr_uuid"); } rs.close(); st.close(); if (catAdminUuid == null) { if (log.isInfoEnabled()) { log.info("Couldn't find addr_uuid of CATALOG_ADMINISTRATOR !!!!!!!!!!!!! sql = '" + sql + "'"); } } jdbc.executeUpdate("UPDATE t01_object SET responsible_uuid = '" + catAdminUuid + "'"); jdbc.executeUpdate("UPDATE t02_address SET responsible_uuid = '" + catAdminUuid + "'"); // set entities mod-user to cat-admin if address non existent (in objects, addresses, catalogue) // ----------------------------------------------------------------------- if (log.isInfoEnabled()) { log.info("set mod_uuid in entities to catadminUuid(" + catAdminUuid + ") if mod_uuid not found ..."); } // OBJECTS sql = "select distinct obj.obj_uuid, obj.id, obj.mod_uuid " + "from t01_object obj left outer join address_node aNode on obj.mod_uuid = aNode.addr_uuid " + "where aNode.addr_uuid is null " + "ORDER BY obj.obj_uuid, obj.id, obj.mod_uuid"; st = jdbc.createStatement(); rs = jdbc.executeQuery(sql, st); while (rs.next()) { long objId = rs.getLong("id"); log.info("Invalid entry in t01_object found: mod_uuid not found, we set catadmin as mod_uuid !!! " + "objId('" + objId + "'), obj_uuid('" + rs.getString("obj_uuid") + "'), invalid mod_uuid('" + rs.getString("mod_uuid") + "')."); jdbc.executeUpdate("UPDATE t01_object SET mod_uuid = '" + catAdminUuid + "' where id=" + objId); } rs.close(); st.close(); // ADDRESSES sql = "select distinct addr.adr_uuid, addr.id, addr.mod_uuid " + "from t02_address addr left outer join address_node aNode on addr.mod_uuid = aNode.addr_uuid " + "where aNode.addr_uuid is null " + "ORDER BY addr.adr_uuid, addr.id, addr.mod_uuid"; st = jdbc.createStatement(); rs = jdbc.executeQuery(sql, st); while (rs.next()) { long addrId = rs.getLong("id"); log.info("Invalid entry in t02_address found: mod_uuid not found, we set catadmin as mod_uuid !!! " + "addrId('" + addrId + "'), adr_uuid('" + rs.getString("adr_uuid") + "'), invalid mod_uuid('" + rs.getString("mod_uuid") + "')."); jdbc.executeUpdate("UPDATE t02_address SET mod_uuid = '" + catAdminUuid + "' where id=" + addrId); } rs.close(); st.close(); // CATALOGUE sql = "select distinct cat.cat_uuid, cat.id, cat.mod_uuid " + "from t03_catalogue cat left outer join address_node aNode on cat.mod_uuid = aNode.addr_uuid " + "where aNode.addr_uuid is null " + "ORDER BY cat.cat_uuid"; st = jdbc.createStatement(); rs = jdbc.executeQuery(sql, st); while (rs.next()) { long catId = rs.getLong("id"); log.info("Invalid entry in t03_catalogue found: mod_uuid not found, we set catadmin as mod_uuid !!! " + "cat_uuid('" + rs.getString("cat_uuid") + "'), invalid mod_uuid('" + rs.getString("mod_uuid") + "')."); jdbc.executeUpdate("UPDATE t03_catalogue SET mod_uuid = '" + catAdminUuid + "' where id=" + catId); } rs.close(); st.close(); // set default object "Themenkategorie" if none set // ------------------------------------------------ if (defaultThemenkategorieEntryId != -1) { if (log.isInfoEnabled()) { log.info("set default \"Themenkategorie\" in objects not categorized ..."); } pSqlStr = "INSERT INTO t011_obj_topic_cat (id, obj_id, line, topic_category) VALUES ( ?, ?, ?, ?)"; PreparedStatement p = jdbc.prepareStatement(pSqlStr); sql = "select distinct obj.id " + "from t01_object obj left outer join t011_obj_topic_cat topicCat on obj.id = topicCat.obj_id " + "where topicCat.obj_id is null " + "ORDER BY obj.id"; st = jdbc.createStatement(); rs = jdbc.executeQuery(sql, st); while (rs.next()) { long objId = rs.getLong("id"); log.info("No \"Themenkategorie\" set for t01_object, we set default category entryid(" + defaultThemenkategorieEntryId + "): objId('" + objId + "')."); int cnt = 1; dataProvider.setId(dataProvider.getId() + 1); p.setLong(cnt++, dataProvider.getId()); // id p.setLong(cnt++, objId); // obj_id p.setInt(cnt++, 1); // line JDBCHelper.addInteger(p, cnt++, defaultThemenkategorieEntryId); // topic_category try { p.executeUpdate(); } catch (Exception e) { log.error("Error executing SQL: " + p.toString(), e); throw e; } } p.close(); rs.close(); st.close(); } // set default entries in sys_lists // -------------------------------- if (log.isInfoEnabled()) { log.info("set default entries in sys_lists ..."); } // set default language of metadata entities (=default entry in sys_list 99999999) // first check whether defaults set -> ignore localization ! st = jdbc.createStatement(); rs = jdbc.executeQuery("SELECT id FROM sys_list WHERE lst_id=99999999 AND is_default = 'Y'", st); boolean hasDefaults = rs.next(); rs.close(); st.close(); if (!hasDefaults) { // default is german (=121) ! set in all localized versions as default (lang_id='de' -> "Deutsch", lang_id='en' -> "German", ...) jdbc.executeUpdate("UPDATE sys_list SET is_default = 'Y' WHERE lst_id=99999999 AND entry_id=121"); } // set default publication condition INTERNET (=default entry in sys_list 3571) // first check whether defaults set -> ignore localization ! st = jdbc.createStatement(); rs = jdbc.executeQuery("SELECT id FROM sys_list WHERE lst_id=3571 AND is_default = 'Y'", st); hasDefaults = rs.next(); rs.close(); st.close(); if (!hasDefaults) { // default is Internet (=1) ! set in all localized versions as default jdbc.executeUpdate("UPDATE sys_list SET is_default = 'Y' WHERE lst_id=3571 AND entry_id=1"); } if (log.isInfoEnabled()) { log.info("Post processing ... done."); } }
From source file:dk.netarkivet.harvester.datamodel.extendedfield.ExtendedFieldDBDAO.java
@Override public synchronized void create(ExtendedField aExtendedField) { ArgumentNotValid.checkNotNull(aExtendedField, "aExtendedField"); Connection connection = HarvestDBConnection.get(); if (aExtendedField.getExtendedFieldID() != null) { log.warn("The extendedFieldID for this extended Field is " + "already set. This should probably never happen."); } else {//from www .j a va2s. co m aExtendedField.setExtendedFieldID(generateNextID(connection)); } log.debug("Creating " + aExtendedField.toString()); PreparedStatement statement = null; try { connection.setAutoCommit(false); statement = connection .prepareStatement("" + "INSERT INTO extendedfield " + " (extendedfield_id, " + " extendedfieldtype_id, " + " name, " + " format, " + " defaultvalue, " + " options, " + " datatype, " + " mandatory, " + " sequencenr, " + " maxlen) " + "VALUES (?, " + " ?, " + " ?, " + " ?, " + " ?, " + " ?, " + " ?, " + " ?, " + " ?, " + " ?) "); statement.setLong(1, aExtendedField.getExtendedFieldID()); statement.setLong(2, aExtendedField.getExtendedFieldTypeID()); statement.setString(3, aExtendedField.getName()); statement.setString(4, aExtendedField.getFormattingPattern()); statement.setString(5, aExtendedField.getDefaultValue()); statement.setString(6, aExtendedField.getOptions()); statement.setInt(7, aExtendedField.getDatatype()); // the following conversion from boolean to int is necessary, // because the database column 'mandatory' is a integer field // and not a boolean (NAS-2127) statement.setInt(8, aExtendedField.isMandatory() ? 1 : 0); statement.setInt(9, aExtendedField.getSequencenr()); statement.setInt(10, aExtendedField.getMaxlen()); //TODO log.debug(statement.toString()); statement.executeUpdate(); connection.commit(); } catch (SQLException e) { String message = "SQL error creating extended field " + aExtendedField + " in database" + "\n" + ExceptionUtils.getSQLExceptionCause(e); log.warn(message, e); throw new IOFailure(message, e); } finally { DBUtils.closeStatementIfOpen(statement); DBUtils.rollbackIfNeeded(connection, "create extended field", aExtendedField); HarvestDBConnection.release(connection); } }