List of usage examples for java.sql Statement RETURN_GENERATED_KEYS
int RETURN_GENERATED_KEYS
To view the source code for java.sql Statement RETURN_GENERATED_KEYS.
Click Source Link
From source file:com.portfolio.data.provider.MysqlAdminProvider.java
@Override public Object postUser(String in, int userId) throws Exception { if (!credential.isAdmin(userId)) throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right"); String result = null;/*from w ww . j a v a2s. c o m*/ String login = null; String firstname = null; String lastname = null; String label = null; String password = null; String active = "1"; Integer uuid = 0; Integer newId = 0; //On prepare les requetes SQL PreparedStatement stInsert; String sqlInsert; //On recupere le body Document doc = DomUtils.xmlString2Document(in, new StringBuffer()); Element etu = doc.getDocumentElement(); //On verifie le bon format if (etu.getNodeName().equals("user")) { //On recupere les attributs try { if (etu.getAttributes().getNamedItem("uid") != null) { login = etu.getAttributes().getNamedItem("uid").getNodeValue(); if (getMysqlUserUid(login) != null) { uuid = Integer.parseInt(getMysqlUserUid(login)); } } } catch (Exception ex) { } try { if (etu.getAttributes().getNamedItem("firstname") != null) { firstname = etu.getAttributes().getNamedItem("firstname").getNodeValue(); } } catch (Exception ex) { } try { if (etu.getAttributes().getNamedItem("lastname") != null) { lastname = etu.getAttributes().getNamedItem("lastname").getNodeValue(); } } catch (Exception ex) { } try { if (etu.getAttributes().getNamedItem("label") != null) { label = etu.getAttributes().getNamedItem("label").getNodeValue(); } } catch (Exception ex) { } try { if (etu.getAttributes().getNamedItem("password") != null) { password = etu.getAttributes().getNamedItem("password").getNodeValue(); } } catch (Exception ex) { } try { if (etu.getAttributes().getNamedItem("active") != null) { active = etu.getAttributes().getNamedItem("active").getNodeValue(); } } catch (Exception ex) { } } else { result = "Erreur lors de la recuperation des attributs de l'utilisateur dans le XML"; } //On ajoute l'utilisateur dans la base de donnees if (etu.getAttributes().getNamedItem("firstname") != null && etu.getAttributes().getNamedItem("lastname") != null && etu.getAttributes().getNamedItem("label") == null) { sqlInsert = "REPLACE INTO credential(userid, login, display_firstname, display_lastname, password, active) VALUES (?, ?, ?, ?, UNHEX(SHA1(?)),?)"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "MERGE INTO credential d USING (SELECT ? userid,? login,? display_firstname,? display_lastname,crypt(?) password,? active FROM DUAL) s ON (d.userid=s.userid) WHEN MATCHED THEN UPDATE SET d.login=s.login, d.display_firstname = s.display_firstname, d.display_lastname = s.display_lastname, d.password = s.password, d.active = s.active WHEN NOT MATCHED THEN INSERT (d.userid, d.login, d.display_firstname, d.display_lastname, d.password, d.active) VALUES (s.userid, s.login, s.display_firstname, s.display_lastname, s.password, s.active)"; stInsert = connection.prepareStatement(sqlInsert, new String[] { "userid" }); } stInsert.setInt(1, uuid); stInsert.setString(2, login); stInsert.setString(3, firstname); stInsert.setString(4, lastname); stInsert.setString(5, password); stInsert.setString(6, active); stInsert.executeUpdate(); } else { sqlInsert = "REPLACE INTO credential(userid, login, display_firstname, display_lastname, password, active) VALUES (?, ?, ?, ?, UNHEX(SHA1(?)),?)"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "MERGE INTO credential d USING (SELECT ? userid,? login,? display_firstname,? display_lastname,crypt(?) password,? active FROM DUAL) s ON (d.userid=s.userid) WHEN MATCHED THEN UPDATE SET d.login=s.login, d.display_firstname = s.display_firstname, d.display_lastname = s.display_lastname, d.password = s.password, d.active = s.active WHEN NOT MATCHED THEN INSERT (d.userid, d.login, d.display_firstname, d.display_lastname, d.password, d.active) VALUES (s.userid, s.login, s.display_firstname, s.display_lastname, s.password, s.active)"; stInsert = connection.prepareStatement(sqlInsert, new String[] { "userid" }); } stInsert.setInt(1, uuid); stInsert.setString(2, login); stInsert.setString(3, " "); stInsert.setString(4, label); stInsert.setString(5, password); stInsert.setString(6, active); stInsert.executeUpdate(); } ResultSet rs = stInsert.getGeneratedKeys(); if (rs.next()) { newId = rs.getInt(1); } //On renvoie le body pour qu'il soit stock dans le log result = "<user "; result += DomUtils.getXmlAttributeOutput("uid", login) + " "; result += DomUtils.getXmlAttributeOutput("firstname", firstname) + " "; result += DomUtils.getXmlAttributeOutput("lastname", lastname) + " "; result += DomUtils.getXmlAttributeOutput("label", label) + " "; result += DomUtils.getXmlAttributeOutput("password", password) + " "; result += DomUtils.getXmlAttributeOutputInt("uuid", newId) + " "; result += ">"; result += "</user>"; return result; }
From source file:com.portfolio.data.provider.MysqlAdminProvider.java
@Override public String postUsers(String in, int userId) throws Exception { if (!credential.isAdmin(userId)) throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right"); String result = null;// w w w. j a v a 2 s. co m String username = null; String password = null; String firstname = null; String lastname = null; String email = null; String active = "1"; int id = 0; //On prepare les requetes SQL PreparedStatement stInsert; String sqlInsert; //On recupere le body Document doc; doc = DomUtils.xmlString2Document(in, new StringBuffer()); Element users = doc.getDocumentElement(); NodeList children = null; children = users.getChildNodes(); // On parcourt une premire fois les enfants pour rcuperer la liste crire en base //On verifie le bon format if (users.getNodeName().equals("users")) { for (int i = 0; i < children.getLength(); i++) { if (children.item(i).getNodeName().equals("user")) { NodeList children2 = null; children2 = children.item(i).getChildNodes(); for (int y = 0; y < children2.getLength(); y++) { if (children2.item(y).getNodeName().equals("username")) { username = DomUtils.getInnerXml(children2.item(y)); } if (children2.item(y).getNodeName().equals("password")) { password = DomUtils.getInnerXml(children2.item(y)); } if (children2.item(y).getNodeName().equals("firstname")) { firstname = DomUtils.getInnerXml(children2.item(y)); } if (children2.item(y).getNodeName().equals("lastname")) { lastname = DomUtils.getInnerXml(children2.item(y)); } if (children2.item(y).getNodeName().equals("email")) { email = DomUtils.getInnerXml(children2.item(y)); } if (children2.item(y).getNodeName().equals("active")) { active = DomUtils.getInnerXml(children2.item(y)); } } } } } else { result = "Erreur lors de la recuperation des attributs de l'utilisateur dans le XML"; } //On ajoute l'utilisateur dans la base de donnees try { sqlInsert = "REPLACE INTO credential(login, display_firstname, display_lastname,email, password, active) VALUES (?, ?, ?, ?, UNHEX(SHA1(?)),?)"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "INSERT INTO credential(login, display_firstname, display_lastname,email, password, active) VALUES (?, ?, ?, ?, crypt(?),?)"; stInsert = connection.prepareStatement(sqlInsert, new String[] { "userid" }); } stInsert.setString(1, username); if (firstname == null) { firstname = " "; stInsert.setString(2, firstname); } else { stInsert.setString(2, firstname); } if (lastname == null) { lastname = " "; stInsert.setString(3, lastname); } else { stInsert.setString(3, lastname); } if (email == null) { email = " "; stInsert.setString(4, email); } else { stInsert.setString(4, email); } stInsert.setString(5, password); if (active == null) { active = " "; stInsert.setString(6, active); } else { stInsert.setString(6, active); } stInsert.executeUpdate(); ResultSet rs = stInsert.getGeneratedKeys(); if (rs.next()) { id = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } //On renvoie le body pour qu'il soit stock dans le log result = "<users>"; result += "<user "; result += DomUtils.getXmlAttributeOutputInt("id", id); result += ">"; result += DomUtils.getXmlElementOutput("username", username); result += DomUtils.getXmlElementOutput("password", password); result += DomUtils.getXmlElementOutput("firstname", firstname); result += DomUtils.getXmlElementOutput("lastname", lastname); result += DomUtils.getXmlElementOutput("email", email); result += DomUtils.getXmlElementOutput("active", active); result += "</user>"; result += "</users>"; return result; }
From source file:com.portfolio.data.provider.MysqlAdminProvider.java
@Override public String postRoleUser(int userId, int grid, Integer userid2) throws SQLException { // TODO Auto-generated method stub if (!credential.isAdmin(userId)) throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right"); String label = null;//from w w w . j a va 2s .co m String portfolio_id; String change_rights; int owner = 0; int gid = 0; PreparedStatement st; String sql; ResultSet res; ResultSet res1; PreparedStatement stInsert; String sqlInsert; sql = "SELECT * FROM group_info WHERE grid = ?"; st = connection.prepareStatement(sql); st.setInt(1, grid); res = st.executeQuery(); if (!res.next()) { sql = "SELECT * FROM group_right_info WHERE grid = ?"; st = connection.prepareStatement(sql); st.setInt(1, grid); res1 = st.executeQuery(); if (res1.next()) { label = res1.getString("label"); portfolio_id = res1.getString("portfolio_id"); change_rights = res1.getString("change_rights"); owner = res1.getInt("owner"); } sqlInsert = "REPLACE INTO group_info(grid, owner, label) VALUES (?, ?, ?)"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "MERGE INTO group_info d using (SELECT ? grid,? owner,? label from dual) s ON (1=2) WHEN NOT MATCHED THEN INSERT (d.grid, d.owner, d.label) values (s.grid, s.owner, s.label)"; stInsert = connection.prepareStatement(sqlInsert, new String[] { "gid" }); } stInsert.setInt(1, grid); stInsert.setInt(2, owner); stInsert.setString(3, label); stInsert.executeUpdate(); ResultSet rs = stInsert.getGeneratedKeys(); if (rs.next()) { gid = rs.getInt(1); } sqlInsert = "REPLACE INTO group_user(gid, userid) VALUES (?, ?)"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "MERGE INTO group_user d using (SELECT ? gid,? userid FROM DUAL) s ON (d.gid=s.gid AND d.userid=s.userid) WHEN NOT MATCHED THEN INSERT (d.gid, d.userid) VALUES (s.gid, s.userid)"; stInsert = connection.prepareStatement(sqlInsert); } stInsert.setInt(1, gid); stInsert.setInt(2, userid2); stInsert.executeUpdate(); } else { gid = res.getInt("gid"); sqlInsert = "REPLACE INTO group_user(gid, userid) VALUES (?, ?)"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "MERGE INTO group_user d using (SELECT ? gid,? userid FROM DUAL) s ON (d.gid=s.gid AND d.userid=s.userid) WHEN NOT MATCHED THEN INSERT (d.gid, d.userid) VALUES (s.gid, s.userid)"; stInsert = connection.prepareStatement(sqlInsert); } stInsert.setInt(1, gid); stInsert.setInt(2, userid2); stInsert.executeUpdate(); } return "user " + userid2 + " rajout au groupd gid " + gid + " pour correspondre au groupRight grid " + grid; }
From source file:com.portfolio.data.provider.MysqlAdminProvider.java
@Override public Object putRole(String xmlRole, int userId, int roleId) throws Exception { // TODO Auto-generated method stub if (!credential.isAdmin(userId)) throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right"); String result = null;/*from ww w . ja v a 2 s .c o m*/ String username = null; String password = null; String firstname = null; String lastname = null; String email = null; String label = null; int id = 0; //On prepare les requetes SQL PreparedStatement stInsert; String sqlInsert; //On recupere le body Document doc; doc = DomUtils.xmlString2Document(xmlRole, new StringBuffer()); Element role = doc.getDocumentElement(); NodeList children = null; children = role.getChildNodes(); // On parcourt une premire fois les enfants pour rcuperer la liste crire en base //On verifie le bon format if (role.getNodeName().equals("role")) { for (int i = 0; i < children.getLength(); i++) { if (children.item(i).getNodeName().equals("label")) { label = DomUtils.getInnerXml(children.item(i)); } } } else { result = "Erreur lors de la recuperation des attributs de l'utilisateur dans le XML"; } //On ajoute l'utilisateur dans la base de donnees try { sqlInsert = "REPLACE INTO credential(login, display_firstname, display_lastname,email, password) VALUES (?, ?, ?, ?, UNHEX(SHA1(?)))"; stInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { sqlInsert = "INSERT INTO credential(login, display_firstname, display_lastname,email, password) VALUES (?, ?, ?, ?, crypt(?))"; stInsert = connection.prepareStatement(sqlInsert, new String[] { "userid" }); } stInsert.setString(1, username); stInsert.setString(2, firstname); stInsert.setString(3, lastname); stInsert.setString(4, email); stInsert.setString(5, password); stInsert.executeUpdate(); ResultSet rs = stInsert.getGeneratedKeys(); if (rs.next()) { id = rs.getInt(1); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } result = "" + id; return result; }
From source file:com.portfolio.data.provider.MysqlAdminProvider.java
@Override public String postAddNodeType(int userId, Integer type, Integer nodeid, Integer parentid, Integer instance, String data) {//from w w w .j a v a2s . com if (!credential.isAdmin(userId)) throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right"); /** * Format que l'on reoit: * <asm*> * <asmResource xsi_type='nodeRes'>{node_data}</asmResource> * <asmResource xsi_type='context'>{node_data}</asmResource> * <asmResource xsi_type='*'>{node_data}</asmResource> * </asm*> */ String sql = ""; PreparedStatement st; Integer output = 0; Integer parentId = 0; String asmtype = ""; String xsitype = ""; try { /// Prpare les donnes pour les requtes // Parse DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance(); DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder(); Document document = documentBuilder.parse(new ByteArrayInputStream(data.getBytes("UTF-8"))); // Traite le noeud racine des donnes, retourne l'identifiant du noeud racine Element nodeData = document.getDocumentElement(); asmtype = nodeData.getNodeName(); connection.setAutoCommit(true); // Utilise parentid si on rattache un autre groupe de noeud en dessous d'un noeud existant sql = "INSERT INTO definition_type(def_id,asm_type,parent_node,instance_rule) " + "VALUE(?,?,?,?)"; st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { st = connection.prepareStatement(sql, new String[] { "node_id" }); } st.setInt(1, type); st.setString(2, asmtype); if (parentid == null) st.setNull(3, Types.BIGINT); else st.setInt(3, parentid); if (instance == null) st.setNull(4, Types.BIGINT); else st.setInt(4, instance); output = st.executeUpdate(); ResultSet key = st.getGeneratedKeys(); // On rcupre l'identifiant du noeud 'racine' des donnes ajouts if (key.next()) parentId = key.getInt(1); st.close(); // Soit 2 ou 3 resources asmtype = "asmResource"; NodeList resources = document.getElementsByTagName("asmResource"); sql = "INSERT INTO definition_type(def_id,asm_type,xsi_type,parent_node,node_data,instance_rule) " + "VALUE(?,?,?,?,?,?)"; st = connection.prepareStatement(sql); st.setInt(1, type); st.setString(2, asmtype); st.setInt(4, parentId); for (int i = 0; i < resources.getLength(); ++i) { Element resource = (Element) resources.item(i); xsitype = resource.getAttribute("xsi_type"); String resContent = DomUtils.getInnerXml(resource); st.setString(3, xsitype); st.setString(5, resContent); if (instance == null) st.setNull(6, Types.BIGINT); else st.setInt(6, instance); // On ajoute les donnes des ressources restante output = st.executeUpdate(); } st.close(); } catch (Exception e) { try { connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } finally { try { connection.setAutoCommit(true); connection.close(); } catch (SQLException e) { e.printStackTrace(); } } return output.toString(); }
From source file:edu.ku.brc.specify.utilapps.BuildSampleDatabase.java
/** * @param conn/*from w w w . j a va2 s. co m*/ * @param stmt * @param name * @param txTreeDefId * @param tdi * @param parentId * @param fullName * @param levelNames * @return * @throws SQLException */ protected Pair<String, Integer> createTaxonNode(final Connection conn, final Statement stmt, final String name, final int txTreeDefId, final TaxonTreeDefItem tdi, final Integer parentId, final String fullName, final String[] levelNames) throws SQLException { gSQLStr.setLength(0); gSQLStr.append( "INSERT INTO taxon (Name, TaxonTreeDefID, FullName, TaxonTreeDefItemID, RankID, ParentID, TimestampCreated, Version, IsAccepted, IsHybrid"); addExtraColumns(gSQLStr, tdi.getRankId(), levelNames, true); gSQLStr.append(") VALUES ("); gSQLStr.append("\""); gSQLStr.append(name); gSQLStr.append("\","); gSQLStr.append(txTreeDefId); gSQLStr.append(","); gSQLStr.append("\""); if (StringUtils.isNotEmpty(fullName)) { gSQLStr.append(fullName); } else { gSQLStr.append(name); } gSQLStr.append("\""); gSQLStr.append(","); gSQLStr.append(tdi.getId()); gSQLStr.append(','); gSQLStr.append(tdi.getRankId()); gSQLStr.append(','); gSQLStr.append(parentId); gSQLStr.append(','); gSQLStr.append(nowStr); gSQLStr.append(",1,true,false"); addExtraColumns(gSQLStr, tdi.getRankId(), levelNames, false); gSQLStr.append(")"); //System.out.println(sb.toString()); stmt.executeUpdate(gSQLStr.toString(), Statement.RETURN_GENERATED_KEYS); Integer newId = BasicSQLUtils.getInsertedId(stmt); if (newId == null) { throw new RuntimeException("Couldn't get the Taxon's inserted ID"); } recCnt++; //System.out.println("rec: "+recCnt); if (recycler.size() > 0) { Pair<String, Integer> p = recycler.pop(); p.first = name; p.second = newId; return p; } return new Pair<String, Integer>(name, newId); }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * @param treeDef/* w w w . j a v a2s. c om*/ * @throws SQLException */ public void convertLithoStratGeneral(final LithoStratTreeDef treeDef, final LithoStrat earth, final TableWriter tblWriter, final String srcTableName) throws SQLException { Statement stmt = null; ResultSet rs = null; String s = ""; try { // get a Hibernate session for saving the new records Session localSession = HibernateUtil.getCurrentSession(); HibernateUtil.beginTransaction(); int count = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*) FROM " + srcTableName); if (count < 1) return; if (hasFrame) { setProcess(0, count); } // create an ID mapper for the geography table (mainly for use in converting localities) IdHashMapper lithoStratIdMapper = IdMapperMgr.getInstance().addHashMapper("stratigraphy_StratigraphyID", true); if (lithoStratIdMapper == null) { UIRegistry.showError("The lithoStratIdMapper was null."); return; } IdMapperIFace gtpIdMapper = IdMapperMgr.getInstance().get("geologictimeperiod", "GeologicTimePeriodID"); IdMapperIFace ceMapper = IdMapperMgr.getInstance().get("collectingevent", "CollectingEventID"); if (ceMapper == null) { ceMapper = IdMapperMgr.getInstance().addTableMapper("collectingevent", "CollectingEventID", null, false); } String sql = String.format( "SELECT s.StratigraphyID, s.SuperGroup, s.Group, s.Formation, s.Member, s.Bed, Remarks, " + "Text1, Text2, Number1, Number2, YesNo1, YesNo2, GeologicTimePeriodID FROM %s s " + "ORDER BY s.StratigraphyID", srcTableName); stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); rs = stmt.executeQuery(sql); Map<Integer, Pair<Integer, Integer>> stratHash = new HashMap<Integer, Pair<Integer, Integer>>(); int stratsWithNoGTP = 0; int stratsWithNoMappedGTP = 0; int missingCEMapping = 0; int lithoCnt = 0; int counter = 0; // for each old record, convert the record while (rs.next()) { if (counter % 500 == 0) { if (hasFrame) { setProcess(counter); } else { log.info("Converted " + counter + " Stratigraphy records"); } } // grab the important data fields from the old record int oldStratId = rs.getInt(1); // This is a one-to-one with CollectingEvent String superGroup = rs.getString(2); String lithoGroup = rs.getString(3); String formation = rs.getString(4); String member = rs.getString(5); String bed = rs.getString(6); String remarks = escapeStringLiterals(rs.getString(7)); String text1 = escapeStringLiterals(rs.getString(8)); String text2 = escapeStringLiterals(rs.getString(9)); Double number1 = rs.getObject(10) != null ? rs.getDouble(10) : null; Double number2 = rs.getObject(11) != null ? rs.getDouble(11) : null; Boolean yesNo1 = rs.getObject(12) != null ? rs.getBoolean(12) : null; Boolean yesNo2 = rs.getObject(13) != null ? rs.getBoolean(13) : null; Integer oldGTPId = rs.getObject(14) != null ? rs.getInt(14) : null; // Check to see if there is any Litho information OR an GTP Id // If both are missing then skip the record. boolean hasLithoFields = isNotEmpty(superGroup) || isNotEmpty(lithoGroup) || isNotEmpty(formation) || isNotEmpty(member); if (!hasLithoFields && oldGTPId == null) { continue; } Integer gtpId = null; if (oldGTPId != null) { gtpId = gtpIdMapper.get(oldGTPId); if (gtpId == null) { tblWriter.logError("Old GTPID[" + gtpId + "] in the Strat record could not be mapped for Old StratID[" + oldStratId + "]"); stratsWithNoMappedGTP++; } } else { stratsWithNoGTP++; } // There may not be any Litho information to add to the LithoStrat tree, // but it did have GTP Information if we got here Integer lithoStratID = null; if (hasLithoFields) { // create a new Geography object from the old data LithoStrat[] newStrats = convertOldStratRecord(superGroup, lithoGroup, formation, member, bed, remarks, text1, text2, number1, number2, yesNo1, yesNo2, earth, localSession); LithoStrat newStrat = getLastLithoStrat(newStrats); counter++; lithoCnt += newStrats.length; // Map Old LithoStrat ID to the new Tree Id //System.out.println(oldStratId + " " + newStrat.getLithoStratId()); if (newStrat != null) { lithoStratID = newStrat.getLithoStratId(); lithoStratIdMapper.put(oldStratId, newStrat.getLithoStratId()); } else { String msg = String.format("Strat Fields were all null for oldID", oldStratId); tblWriter.logError(msg); log.error(msg); missingCEMapping++; } } if (lithoStratID != null || gtpId != null) { Integer newCEId = ceMapper.get(oldStratId); if (newCEId == null) { String msg = String.format("No CE mapping for Old StratId %d, when they are a one-to-one.", oldStratId); tblWriter.logError(msg); log.error(msg); missingCEMapping++; } else { stratHash.put(newCEId, new Pair<Integer, Integer>(gtpId, lithoStratID)); } } } stmt.close(); System.out.println("lithoCnt: " + lithoCnt); if (hasFrame) { setProcess(counter); } else { log.info("Converted " + counter + " Stratigraphy records"); } TreeHelper.fixFullnameForNodeAndDescendants(earth); earth.setNodeNumber(1); fixNodeNumbersFromRoot(earth); HibernateUtil.commitTransaction(); log.info("Converted " + counter + " Stratigraphy records"); rs.close(); Statement updateStatement = newDBConn.createStatement(); int ceCnt = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT Count(CollectingEventID) FROM collectingevent"); int stratCnt = BasicSQLUtils.getCountAsInt(oldDBConn, String.format("SELECT Count(CollectingEventID) FROM collectingevent " + "INNER JOIN %s ON CollectingEventID = StratigraphyID", srcTableName)); String msg = String.format("There are %d CE->Strat and %d CEs. The diff is %d", stratCnt, ceCnt, (ceCnt - stratCnt)); tblWriter.log(msg); log.debug(msg); // Create a PaleoContext for each ColObj stmt = newDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); int processCnt = BasicSQLUtils .getCountAsInt("SELECT COUNT(*) FROM collectionobject WHERE CollectingEventID IS NOT NULL"); if (frame != null) { frame.setDesc("Converting PaleoContext..."); frame.setProcess(0, processCnt); } TreeSet<Integer> missingStratIds = new TreeSet<Integer>(); int missingStrat = 0; int missingGTP = 0; int coUpdateCnt = 0; int cnt = 0; sql = "SELECT CollectionObjectID, CollectingEventID FROM collectionobject WHERE CollectingEventID IS NOT NULL ORDER BY CollectionObjectID"; rs = stmt.executeQuery(sql); while (rs.next()) { int coId = rs.getInt(1); // New CO Id Integer ceId = rs.getInt(2); // New CE Id Pair<Integer, Integer> strat = stratHash.get(ceId); Integer newLithoId = null; Integer gtpId = null; if (strat != null) { gtpId = strat.getFirst(); newLithoId = strat.getSecond(); } if (newLithoId == null) { missingStrat++; missingStratIds.add(ceId); if (gtpId == null) continue; } try { String updateStr = "INSERT INTO paleocontext (TimestampCreated, TimestampModified, DisciplineID, Version, CreatedByAgentID, ModifiedByAgentID, LithoStratID, ChronosStratID) " + "VALUES ('" + nowStr + "','" + nowStr + "'," + getDisciplineId() + ", 0, " + getCreatorAgentId(null) + "," + getModifiedByAgentId(null) + "," + (newLithoId != null ? newLithoId : "NULL") + "," + (gtpId != null ? gtpId : "NULL") + ")"; updateStatement.executeUpdate(updateStr, Statement.RETURN_GENERATED_KEYS); Integer paleoContextID = getInsertedId(updateStatement); if (paleoContextID == null) { throw new RuntimeException("Couldn't get the Agent's inserted ID"); } String sqlUpdate = "UPDATE collectionobject SET PaleoContextID=" + paleoContextID + " WHERE CollectionObjectID = " + coId; updateStatement.executeUpdate(sqlUpdate); coUpdateCnt++; } catch (SQLException e) { e.printStackTrace(); log.error(e); showError(e.getMessage()); throw new RuntimeException(e); } processCnt++; if (frame != null && cnt % 100 == 0) frame.setProcess(cnt); } rs.close(); stmt.close(); if (frame != null) frame.setProcess(processCnt); msg = String.format("There are %d unmappable Strat Records and %d unmappable GTP records.", missingStrat, missingGTP); tblWriter.log(msg); log.debug(msg); msg = String.format("There are %d CO records updated.", coUpdateCnt); tblWriter.log(msg); log.debug(msg); updateStatement.close(); msg = String.format("No CE mapping for Old StratId Count: %d", missingCEMapping); tblWriter.logError(msg); log.error(msg); msg = String.format("Strats with No GTP Count: %d", stratsWithNoGTP); tblWriter.logError(msg); log.error(msg); msg = String.format("Strats with missing Mapping to GTP Count: %d", stratsWithNoMappedGTP); tblWriter.logError(msg); log.error(msg); msg = String.format("Number of Old StratIds mapped to a new Strat ID Count: %d", lithoStratIdMapper.size()); tblWriter.logError(msg); log.error(msg); StringBuilder sb = new StringBuilder(); sb.append("Missing New Strat: "); if (missingStratIds.size() == 0) sb.append("None"); for (Integer id : missingStratIds) { sb.append(String.format("%d, ", id)); } tblWriter.logError(sb.toString()); log.error(sb.toString()); } catch (Exception ex) { ex.printStackTrace(); } // Now in this Step we Add the PaleoContext to the Collecting Events }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * @param treeDef//ww w. ja va 2s. c o m * @throws SQLException */ public void convertLithoStratCustom(final LithoStratTreeDef treeDef, final LithoStrat earth, final TableWriter tblWriter, final String srcTableName, final boolean doMapGTPIds) throws SQLException { Statement stmt = null; ResultSet rs = null; String s = ""; try { // get a Hibernate session for saving the new records Session localSession = HibernateUtil.getCurrentSession(); HibernateUtil.beginTransaction(); int count = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*) FROM " + srcTableName); if (count < 1) return; if (hasFrame) { setProcess(0, count); } // create an ID mapper for the geography table (mainly for use in converting localities) IdHashMapper lithoStratIdMapper = IdMapperMgr.getInstance().addHashMapper("stratigraphy_StratigraphyID", true); if (lithoStratIdMapper == null) { UIRegistry.showError("The lithoStratIdMapper was null."); return; } IdTableMapper gtpIdMapper = IdMapperMgr.getInstance().addTableMapper("geologictimeperiod", "GeologicTimePeriodID", null, false); if (doMapGTPIds) { gtpIdMapper.clearRecords(); gtpIdMapper.mapAllIds(); } Hashtable<Integer, Integer> stratGTPIdHash = new Hashtable<Integer, Integer>(); //Hashtable<Integer, Integer> newCEIdToNewStratIdHash = new Hashtable<Integer, Integer>(); // stratigraphy2 goes here. IdHashMapper newCEIdToNewStratIdHash = IdMapperMgr.getInstance() .addHashMapper("stratigraphy_StratigraphyID_2", true); newCEIdToNewStratIdHash.setShowLogErrors(false); IdMapperIFace ceMapper = IdMapperMgr.getInstance().get("collectingevent", "CollectingEventID"); if (ceMapper == null) { ceMapper = IdMapperMgr.getInstance().addTableMapper("collectingevent", "CollectingEventID", null, false); } // get all of the old records // String sql = String.format("SELECT s.StratigraphyID, s.SuperGroup, s.Group, s.Formation, s.Member, s.Bed, Remarks, " + // "Text1, Text2, Number1, Number2, YesNo1, YesNo2, GeologicTimePeriodID FROM %s s " + // "WHERE s.SuperGroup IS NOT NULL OR s.Group IS NOT NULL OR s.Formation IS NOT NULL OR " + // "s.Member IS NOT NULL OR s.Bed IS NOT NULL ORDER BY s.StratigraphyID", srcTableName); String sql = String.format( "SELECT s.StratigraphyID, s.SuperGroup, s.Group, s.Formation, s.Member, s.Bed, Remarks, " + "Text1, Text2, Number1, Number2, YesNo1, YesNo2, GeologicTimePeriodID FROM %s s " + "ORDER BY s.StratigraphyID", srcTableName); stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); rs = stmt.executeQuery(sql); int stratsWithNoGTP = 0; int stratsWithNoMappedGTP = 0; int missingCEMapping = 0; int lithoCnt = 0; int counter = 0; // for each old record, convert the record while (rs.next()) { if (counter % 500 == 0) { if (hasFrame) { setProcess(counter); } else { log.info("Converted " + counter + " Stratigraphy records"); } } // grab the important data fields from the old record int oldStratId = rs.getInt(1); // This is a one-to-one with CollectingEvent String superGroup = rs.getString(2); String lithoGroup = rs.getString(3); String formation = rs.getString(4); String member = rs.getString(5); String bed = rs.getString(6); String remarks = escapeStringLiterals(rs.getString(7)); String text1 = escapeStringLiterals(rs.getString(8)); String text2 = escapeStringLiterals(rs.getString(9)); Double number1 = rs.getObject(10) != null ? rs.getDouble(10) : null; Double number2 = rs.getObject(11) != null ? rs.getDouble(11) : null; Boolean yesNo1 = rs.getObject(12) != null ? rs.getBoolean(12) : null; Boolean yesNo2 = rs.getObject(13) != null ? rs.getBoolean(13) : null; Integer oldGTPId = rs.getObject(14) != null ? rs.getInt(14) : null; // Check to see if there is any Litho information OR an GTP Id // If both are missing then skip the record. boolean hasLithoFields = isNotEmpty(superGroup) || isNotEmpty(lithoGroup) || isNotEmpty(formation) || isNotEmpty(member); if (!hasLithoFields && oldGTPId == null) { continue; } Integer gtpId = null; if (doMapGTPIds) { if (oldGTPId != null) { gtpId = oldGTPId; } } else { gtpId = oldStratId; } if (gtpId != null) { gtpId = gtpIdMapper.get(gtpId); if (gtpId == null) { tblWriter.logError("Old GTPID[" + gtpId + "] in the Strat record could not be mapped for Old StratID[" + oldStratId + "]"); stratsWithNoMappedGTP++; } } else { stratsWithNoGTP++; } // There may not be any Litho information to add to the LithoStrat tree, // but it did have GTP Information if we got here if (hasLithoFields) { // create a new Geography object from the old data LithoStrat[] newStrats = convertOldStratRecord(superGroup, lithoGroup, formation, member, bed, remarks, text1, text2, number1, number2, yesNo1, yesNo2, earth, localSession); LithoStrat newStrat = getLastLithoStrat(newStrats); counter++; lithoCnt += newStrats.length; // Map Old LithoStrat ID to the new Tree Id //System.out.println(oldStratId + " " + newStrat.getLithoStratId()); if (newStrat != null) { lithoStratIdMapper.put(oldStratId, newStrat.getLithoStratId()); // Convert Old CEId (StratID) to new CEId, then map the new CEId -> new StratId Integer newCEId = ceMapper.get(oldStratId); if (newCEId != null) { newCEIdToNewStratIdHash.put(newCEId, newStrat.getLithoStratId()); } else { String msg = String.format( "No CE mapping for Old StratId %d, when they are a one-to-one.", oldStratId); tblWriter.logError(msg); log.error(msg); missingCEMapping++; } // Map the New StratId to the new GTP Id if (gtpId != null && stratGTPIdHash.get(newStrat.getLithoStratId()) == null) { stratGTPIdHash.put(newStrat.getLithoStratId(), gtpId); // new ID to new ID } } else { String msg = String.format("Strat Fields were all null for oldID", oldStratId); tblWriter.logError(msg); log.error(msg); missingCEMapping++; } } } stmt.close(); System.out.println("lithoCnt: " + lithoCnt); if (hasFrame) { setProcess(counter); } else { log.info("Converted " + counter + " Stratigraphy records"); } TreeHelper.fixFullnameForNodeAndDescendants(earth); earth.setNodeNumber(1); fixNodeNumbersFromRoot(earth); HibernateUtil.commitTransaction(); log.info("Converted " + counter + " Stratigraphy records"); rs.close(); Statement updateStatement = newDBConn.createStatement(); //Hashtable<Integer, Integer> ceToPCHash = new Hashtable<Integer, Integer>(); int ceCnt = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT Count(CollectingEventID) FROM collectingevent"); int stratCnt = BasicSQLUtils.getCountAsInt(oldDBConn, String.format("SELECT Count(CollectingEventID) FROM collectingevent " + "INNER JOIN %s ON CollectingEventID = StratigraphyID", srcTableName)); String msg = String.format("There are %d CE->Strat and %d CEs. The diff is %d", stratCnt, ceCnt, (ceCnt - stratCnt)); tblWriter.log(msg); log.debug(msg); // Create a PaleoContext for each ColObj stmt = newDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); int processCnt = BasicSQLUtils .getCountAsInt("SELECT COUNT(*) FROM collectionobject WHERE CollectingEventID IS NOT NULL"); if (frame != null) { frame.setDesc("Converting PaleoContext..."); frame.setProcess(0, processCnt); } TreeSet<Integer> missingStratIds = new TreeSet<Integer>(); int missingStrat = 0; int missingGTP = 0; int coUpdateCnt = 0; int cnt = 0; sql = "SELECT CollectionObjectID, CollectingEventID FROM collectionobject WHERE CollectingEventID IS NOT NULL ORDER BY CollectionObjectID"; rs = stmt.executeQuery(sql); while (rs.next()) { int coId = rs.getInt(1); // New CO Id Integer ceId = rs.getInt(2); // New CE Id // Use the new CE ID to get the new Strat Id Integer newLithoId = newCEIdToNewStratIdHash.get(ceId); Integer gtpId = null; if (newLithoId == null) { missingStrat++; missingStratIds.add(ceId); Integer oldStratID = ceMapper.reverseGet(ceId); if (oldStratID != null) { sql = "SELECT GeologicTimePeriodID FROM stratigraphy WHERE StratigraphyID = " + oldStratID; Integer oldGTPId = BasicSQLUtils.getCount(oldDBConn, sql); if (oldGTPId != null) { gtpId = gtpIdMapper.get(oldGTPId); } } if (gtpId == null) continue; } // Use the new StratID to get the new GTP Id (ChronosStratigraphy) if (gtpId == null) { gtpId = stratGTPIdHash.get(newLithoId); if (gtpId == null) { missingGTP++; if (newLithoId == null) continue; } } try { String updateStr = "INSERT INTO paleocontext (TimestampCreated, TimestampModified, DisciplineID, Version, CreatedByAgentID, ModifiedByAgentID, LithoStratID, ChronosStratID) " + "VALUES ('" + nowStr + "','" + nowStr + "'," + getDisciplineId() + ", 0, " + getCreatorAgentId(null) + "," + getModifiedByAgentId(null) + "," + (newLithoId != null ? newLithoId : "NULL") + "," + (gtpId != null ? gtpId : "NULL") + ")"; updateStatement.executeUpdate(updateStr, Statement.RETURN_GENERATED_KEYS); Integer paleoContextID = getInsertedId(updateStatement); if (paleoContextID == null) { throw new RuntimeException("Couldn't get the Agent's inserted ID"); } String sqlUpdate = "UPDATE collectionobject SET PaleoContextID=" + paleoContextID + " WHERE CollectionObjectID = " + coId; updateStatement.executeUpdate(sqlUpdate); coUpdateCnt++; } catch (SQLException e) { e.printStackTrace(); log.error(e); showError(e.getMessage()); throw new RuntimeException(e); } processCnt++; if (frame != null && cnt % 100 == 0) frame.setProcess(cnt); } rs.close(); stmt.close(); if (frame != null) frame.setProcess(processCnt); msg = String.format("There are %d unmappable Strat Records and %d unmappable GTP records.", missingStrat, missingGTP); tblWriter.log(msg); log.debug(msg); msg = String.format("There are %d CO records updated.", coUpdateCnt); tblWriter.log(msg); log.debug(msg); updateStatement.close(); msg = String.format("No CE mapping for Old StratId Count: %d", missingCEMapping); tblWriter.logError(msg); log.error(msg); msg = String.format("Strats with No GTP Count: %d", stratsWithNoGTP); tblWriter.logError(msg); log.error(msg); msg = String.format("Strats with missing Mapping to GTP Count: %d", stratsWithNoMappedGTP); tblWriter.logError(msg); log.error(msg); msg = String.format("Number of Old StratIds mapped to a new Strat ID Count: %d", lithoStratIdMapper.size()); tblWriter.logError(msg); log.error(msg); StringBuilder sb = new StringBuilder(); sb.append("Missing New Strat: "); if (missingStratIds.size() == 0) sb.append("None"); for (Integer id : missingStratIds) { sb.append(String.format("%d, ", id)); } tblWriter.logError(sb.toString()); log.error(sb.toString()); } catch (Exception ex) { ex.printStackTrace(); } // Now in this Step we Add the PaleoContext to the Collecting Events }