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:i5.las2peer.services.loadStoreGraphService.LoadStoreGraphService.java
/** * /*from w ww . j ava 2s.c o m*/ * storeGraph * * @param graph a JSONObject * * @return HttpResponse * */ @POST @Path("/") @Produces(MediaType.TEXT_PLAIN) @Consumes(MediaType.APPLICATION_JSON) @ApiResponses(value = { @ApiResponse(code = HttpURLConnection.HTTP_INTERNAL_ERROR, message = "internalError"), @ApiResponse(code = HttpURLConnection.HTTP_CREATED, message = "graphStored") }) @ApiOperation(value = "storeGraph", notes = "") public HttpResponse storeGraph(@ContentParam String graph) { JSONObject graph_JSON = (JSONObject) JSONValue.parse(graph); String insertQuery = ""; int id = (int) graph_JSON.get("graphId"); if (id == -1) { id = 0; // in case of a new graph } String description = (String) graph_JSON.get("description"); JSONArray array = (JSONArray) graph_JSON.get("nodes"); String nodes = array.toJSONString(); array = (JSONArray) graph_JSON.get("links"); String links = array.toJSONString(); Connection conn = null; PreparedStatement stmnt = null; try { conn = dbm.getConnection(); // formulate statement insertQuery = "INSERT INTO graphs ( graphId, description, nodes, links ) " + "VALUES ('" + id + "', '" + description + "', '" + nodes + "', '" + links + "') ON DUPLICATE KEY UPDATE " + "description = + '" + description + "', " + "nodes = + '" + nodes + "', " + "links = + '" + links + "';"; stmnt = conn.prepareStatement(insertQuery, Statement.RETURN_GENERATED_KEYS); // execute query stmnt.executeUpdate(); ResultSet genKeys = stmnt.getGeneratedKeys(); if (genKeys.next()) { int newId = genKeys.getInt(1); // return HTTP response on success with new id String r = newId + ""; HttpResponse graphStored = new HttpResponse(r, HttpURLConnection.HTTP_CREATED); return graphStored; } // return HTTP response on success with id of updated graph String r = id + ""; HttpResponse graphStored = new HttpResponse(r, HttpURLConnection.HTTP_CREATED); return graphStored; } catch (Exception e) { String er = "Internal error: " + e.getMessage(); HttpResponse internalError = new HttpResponse(er, HttpURLConnection.HTTP_INTERNAL_ERROR); return internalError; } finally { // free resources if (stmnt != null) { try { stmnt.close(); } catch (Exception e) { String er = "Internal error: " + e.getMessage(); HttpResponse internalError = new HttpResponse(er, HttpURLConnection.HTTP_INTERNAL_ERROR); return internalError; } } if (conn != null) { try { conn.close(); } catch (Exception e) { Context.logError(this, e.getMessage()); String er = "Internal error: " + e.getMessage(); HttpResponse internalError = new HttpResponse(er, HttpURLConnection.HTTP_INTERNAL_ERROR); return internalError; } } } }
From source file:massbank.DatabaseManager.java
public DatabaseManager(String dbName) throws SQLException { this.databaseName = dbName; this.connectUrl = "jdbc:mariadb://" + dbHostName + "/" + databaseName + "?rewriteBatchedStatements=true"; this.openConnection(); statementAC_CHROMATOGRAPHY = this.con.prepareStatement(sqlAC_CHROMATOGRAPHY); statementAC_MASS_SPECTROMETRY = this.con.prepareStatement(sqlAC_MASS_SPECTROMETRY); statementCH_LINK = this.con.prepareStatement(sqlCH_LINK); statementCOMMENT = this.con.prepareStatement(sqlCOMMENT); statementCOMPOUND = this.con.prepareStatement(sqlCOMPOUND); statementCOMPOUND_CLASS = this.con.prepareStatement(sqlCOMPOUND_CLASS); statementCOMPOUND_COMPOUND_CLASS = this.con.prepareStatement(sqlCOMPOUND_COMPOUND_CLASS); statementCOMPOUND_NAME = this.con.prepareStatement(sqlCOMPOUND_NAME); statementINSTRUMENT = this.con.prepareStatement(sqlINSTRUMENT); statementMS_DATA_PROCESSING = this.con.prepareStatement(sqlMS_DATA_PROCESSING); statementMS_FOCUSED_ION = this.con.prepareStatement(sqlMS_FOCUSED_ION); statementNAME = this.con.prepareStatement(sqlNAME); statementPEAK = this.con.prepareStatement(sqlPEAK); statementPK_NUM_PEAK = this.con.prepareStatement(sqlPK_NUM_PEAK); statementRECORD = this.con.prepareStatement(sqlRECORD); statementSAMPLE = this.con.prepareStatement(sqlSAMPLE); statementSP_LINK = this.con.prepareStatement(sqlSP_LINK); statementSP_SAMPLE = this.con.prepareStatement(sqlSP_SAMPLE); statementANNOTATION_HEADER = this.con.prepareStatement(sqlANNOTATION_HEADER); statementGetContributorFromAccession = this.con.prepareStatement(sqlGetContributorFromAccession); statementInsertCompound = this.con.prepareStatement(insertCompound, Statement.RETURN_GENERATED_KEYS); statementInsertCompound_Class = this.con.prepareStatement(insertCompound_Class, Statement.RETURN_GENERATED_KEYS); statementInsertCompound_Compound_Class = this.con.prepareStatement(insertCompound_Compound_Class); statementInsertName = this.con.prepareStatement(insertName, Statement.RETURN_GENERATED_KEYS); statementInsertCompound_Name = this.con.prepareStatement(insertCompound_Name); statementInsertCH_LINK = this.con.prepareStatement(insertCH_LINK); statementInsertSAMPLE = this.con.prepareStatement(insertSAMPLE, Statement.RETURN_GENERATED_KEYS); statementInsertSP_LINK = this.con.prepareStatement(insertSP_LINK); statementInsertSP_SAMPLE = this.con.prepareStatement(insertSP_SAMPLE); statementInsertINSTRUMENT = this.con.prepareStatement(insertINSTRUMENT, Statement.RETURN_GENERATED_KEYS); statementInsertRECORD = this.con.prepareStatement(insertRECORD); statementInsertCOMMENT = this.con.prepareStatement(insertCOMMENT); statementInsertAC_MASS_SPECTROMETRY = this.con.prepareStatement(insertAC_MASS_SPECTROMETRY); statementInsertAC_CHROMATOGRAPHY = this.con.prepareStatement(insertAC_CHROMATOGRAPHY); statementInsertMS_FOCUSED_ION = this.con.prepareStatement(insertMS_FOCUSED_ION); statementInsertMS_DATA_PROCESSING = this.con.prepareStatement(insertMS_DATA_PROCESSING); statementInsertPEAK = this.con.prepareStatement(insertPEAK); // statementUpdatePEAK = this.con.prepareStatement(updatePEAK); statementUpdatePEAKs = this.con.prepareStatement(updatePEAKs); statementInsertANNOTATION_HEADER = this.con.prepareStatement(insertANNOTATION_HEADER); }
From source file:edu.ku.brc.specify.conversion.ConvertMiscData.java
/** * @param oldDBConn// www. ja va 2s . c o m * @param newDBConn * @param disciplineID * @return */ public static boolean convertKUInvertsObsData(final Connection oldDBConn, final Connection newDBConn) { Timestamp now = new Timestamp(System.currentTimeMillis()); IdMapperMgr.getInstance().setDBs(oldDBConn, newDBConn); IdMapperIFace coMapper = IdMapperMgr.getInstance().addTableMapper("collectionobjectcatalog", "CollectionObjectCatalogID", false); PreparedStatement pStmt1 = null; PreparedStatement pStmt2 = null; PreparedStatement pStmt3 = null; try { pStmt1 = newDBConn.prepareStatement( "INSERT INTO collectionobjectattribute (Remarks, Text1, Number1, CollectionMemberID, TimestampCreated, TimestampModified, Version) VALUES(?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS); pStmt2 = newDBConn.prepareStatement( "UPDATE collectionobjectattribute SET Remarks=?, Text1=?, Number1=? WHERE CollectionObjectAttributeID = ?"); pStmt3 = newDBConn.prepareStatement( "UPDATE collectionobject SET CollectionObjectAttributeID=? WHERE CollectionObjectID = ?"); int cnt = 0; String sql = " SELECT BiologicalObjectID, Remarks, Description, Count, TimestampCreated, TimestampModified FROM observation WHERE (Remarks IS NOT NULL) OR (Description IS NOT NULL) OR (Count IS NOT NULL)"; Statement stmt = oldDBConn.createStatement(); ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int oldCOId = rs.getInt(1); Integer newCOId = coMapper.get(oldCOId); if (newCOId != null) { sql = "SELECT CollectionObjectAttributeID, CollectionMemberID FROM collectionobject WHERE CollectionObjectID = " + newCOId; Object[] row = BasicSQLUtils.getRow(sql); if (row == null || row.length == 0) { log.error("Couldn't get record for newCOId " + newCOId); continue; } Integer newCOAId = (Integer) row[0]; Integer collMemId = (Integer) row[1]; if (newCOAId != null) // Do Update { pStmt2.setString(1, rs.getString(2)); pStmt2.setString(2, rs.getString(3)); pStmt2.setInt(3, rs.getInt(4)); pStmt2.setInt(4, newCOAId); int rv = pStmt2.executeUpdate(); if (rv == 0) { System.err.println("Error updating newCOAId " + newCOAId); } } else // Do Insert { Timestamp ts = rs.getTimestamp(5); if (ts == null) { ts = now; } pStmt1.setString(1, rs.getString(2)); pStmt1.setString(2, rs.getString(3)); pStmt1.setInt(3, rs.getInt(4)); pStmt1.setInt(4, collMemId); pStmt1.setTimestamp(5, ts); pStmt1.setTimestamp(6, rs.getTimestamp(6)); pStmt1.setInt(7, 1); int rv = pStmt1.executeUpdate(); newCOAId = BasicSQLUtils.getInsertedId(pStmt1); if (rv == 0) { System.err.println("Error inserting newCOAId " + newCOAId); } } pStmt3.setInt(1, newCOAId); pStmt3.setInt(2, newCOId); int rv = pStmt3.executeUpdate(); if (rv == 0) { System.err.println("Error updating newCOId " + newCOId); } cnt++; } else { log.error("No mapped CO for Obs.BiologicalObjectID " + oldCOId); } } rs.close(); stmt.close(); System.out.println(String.format("Updated %d ColObj Records", cnt)); return true; } catch (Exception ex) { ex.printStackTrace(); } finally { try { if (pStmt1 != null) pStmt1.close(); if (pStmt2 != null) pStmt2.close(); if (pStmt3 != null) pStmt3.close(); } catch (Exception ex) { } } return false; }
From source file:com.zuoxiaolong.dao.ArticleDao.java
public Integer saveOrUpdate(String resourceId, String subject, String createDate, Status status, String username, Integer accessTimes, Integer goodTimes, String html, String content, Type articleType) {//from w ww.j a va 2s .c om return execute((TransactionalOperation<Integer>) connection -> { String selectSql = "select id,status from articles where resource_id=?"; String insertSql = "insert into articles (resource_id,username,icon,create_date," + "access_times,good_times,subject,html,content,status,type) values (?,?,?,?,?,?,?,?,?,?,?)"; String updateSql = "update articles set subject=?,html=?,content=?,icon=?,status=?,create_date=?,type=? where resource_id=? "; try { PreparedStatement statement = connection.prepareStatement(selectSql); statement.setString(1, resourceId); ResultSet resultSet = statement.executeQuery(); Boolean exists = false; Status currentStatus = Status.draft; Integer id = null; if (resultSet.next()) { exists = true; currentStatus = Status.valueOf(resultSet.getInt("status")); id = resultSet.getInt("id"); } PreparedStatement saveOrUpdate = null; if (!exists) { saveOrUpdate = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS); saveOrUpdate.setString(1, resourceId); saveOrUpdate.setString(2, username); saveOrUpdate.setString(3, ImageUtil.randomArticleImage(subject, articleType)); saveOrUpdate.setString(4, createDate); saveOrUpdate.setInt(5, accessTimes); saveOrUpdate.setInt(6, goodTimes); saveOrUpdate.setString(7, subject); saveOrUpdate.setString(8, html); saveOrUpdate.setString(9, content); saveOrUpdate.setInt(10, status.getIntValue()); saveOrUpdate.setInt(11, articleType.getIntValue()); } else { saveOrUpdate = connection.prepareStatement(updateSql); saveOrUpdate.setString(1, subject); saveOrUpdate.setString(2, html); saveOrUpdate.setString(3, content); saveOrUpdate.setString(4, ImageUtil.randomArticleImage(subject, articleType)); saveOrUpdate.setInt(5, currentStatus == Status.published ? currentStatus.getIntValue() : status.getIntValue()); saveOrUpdate.setString(6, createDate); saveOrUpdate.setInt(7, articleType.getIntValue()); saveOrUpdate.setString(8, resourceId); } int result = saveOrUpdate.executeUpdate(); if (!exists && result > 0) { ResultSet keyResultSet = saveOrUpdate.getGeneratedKeys(); if (keyResultSet.next()) { id = keyResultSet.getInt(1); } } return id; } catch (SQLException e) { throw new RuntimeException(e); } }); }
From source file:edu.ku.brc.web.ParsePaleo.java
/** * //w w w.j ava2 s . com */ private void process() { Pattern p = Pattern.compile("\"([^\"]*)\""); try { pw = new PrintWriter("filedownload.sh"); Connection conn = dbConn.getConnection(); createStrats(); createAges(); String pStr = "INSERT INTO data (citation, georange, paleodist, remarks, taxonId) VALUES(?,?,?,?,?)"; PreparedStatement pStmt = dbS3Conn.prepareStatement(pStr, Statement.RETURN_GENERATED_KEYS); String pStrStrat = "INSERT INTO data_strat (dataId, stratId, formation) VALUES(?,?,?)"; PreparedStatement pStmtStrat = dbS3Conn.prepareStatement(pStrStrat); String pStrAges = "INSERT INTO data_ages (dataId, stratId) VALUES(?,?)"; PreparedStatement pStmtAges = dbS3Conn.prepareStatement(pStrAges); String pStrAttach = "INSERT INTO attach (imgname, url, caption, type) VALUES(?,?,?,?)"; PreparedStatement pStmtAttach = dbS3Conn.prepareStatement(pStrAttach); String pStrTaxonAttach = "INSERT INTO taxon_attach (taxonId, attachId) VALUES(?,?)"; PreparedStatement pStmtTaxonAttach = dbS3Conn.prepareStatement(pStrTaxonAttach); Statement stmt = conn.createStatement(); Statement stmt2 = conn.createStatement(); HashMap<String, String> values = new HashMap<String, String>(); String sql = "SELECT p.ID FROM wp_xuub_posts p where p.post_type LIKE '%_page'"; Vector<Integer> ids = BasicSQLUtils.queryForInts(sql); for (int recId : ids) { values.clear(); sql = String.format( "SELECT pm.meta_key, pm.meta_value FROM wp_xuub_posts p INNER JOIN wp_xuub_postmeta pm ON p.ID = pm.post_id WHERE ID = %d AND (NOT (pm.meta_key LIKE '\\_%c'))", recId, '%'); Vector<Object[]> data = BasicSQLUtils.query(sql); for (Object[] row : data) { if (row[1] != null) { values.put(row[0].toString(), row[1].toString()); } } System.out.println(values); if (values.size() == 0) { System.out.println(sql); continue; } String phylum = values.get("phylum"); String clazz = values.get("class"); String family = values.get("family"); String genus = values.get("genus"); String species = values.get("species"); String[] names = { phylum, clazz, family, genus, species }; int len = 0; while (len < names.length && names[len] != null) { len++; } TreeNode node = getTreeNode(rootNode, names, 0, len); if (node == null) { node = getTreeNode(rootNode, names, 0, len); throw new RuntimeException("Could find tree node" + names); } int i = 1; pStmt.setString(i++, getStrValue(values, "cite", recId)); pStmt.setString(i++, getStrValue(values, "geo_range", recId)); pStmt.setString(i++, getStrValue(values, "paleo_dist", recId)); pStmt.setString(i++, getStrValue(values, "remarks", recId)); pStmt.setInt(i++, node.recId); if (pStmt.executeUpdate() == 0) { System.err.println("Error inserting record."); } Integer dataId = BasicSQLUtils.getInsertedId(pStmt); String agesStr = values.get("ages"); if (isNotEmpty(agesStr)) { Matcher m = p.matcher(agesStr); while (m.find()) { String age = replace(m.group(), "\"", ""); Integer stratId = ages.get(age); if (stratId == null) { System.out.println(String.format("[%s][%s]", m.group(), age)); stratId = 0; } pStmtAges.setInt(1, dataId); pStmtAges.setInt(2, stratId); if (pStmtAges.executeUpdate() == 0) { System.err.println("Error inserting record."); } } } int index = 0; for (String stratKey : stratKeys) { String stratStr = values.get(stratKey); if (isNotEmpty(stratStr) && index > 0) { Matcher m = p.matcher(stratStr); while (m.find()) { String strat = replace(m.group(), "\"", ""); pStmtStrat.setInt(1, dataId); pStmtStrat.setInt(2, index); pStmtStrat.setString(3, strat); if (pStmtStrat.executeUpdate() == 0) { System.err.println("Error inserting record."); } } } index++; } sql = String.format( "SELECT p.ID, post_title, p.post_name, p.post_type, pm.meta_value FROM wp_xuub_posts p " + "INNER JOIN wp_xuub_postmeta pm ON p.ID = pm.post_id " + "WHERE post_parent = %d AND post_type = 'attachment' AND NOT (pm.meta_value LIKE 'a:%c')", recId, '%', '%'); System.out.println(sql); ResultSet rsId = stmt2.executeQuery(sql); while (rsId.next()) { int pId = rsId.getInt(1); String title = rsId.getString(2); if (title.contains("2000px")) continue; sql = "SELECT p.ID, p.post_title, pm.meta_value, p.guid FROM wp_xuub_posts p INNER JOIN wp_xuub_postmeta pm ON p.ID = pm.post_id WHERE pm.meta_key = '_wp_attached_file' AND p.ID = " + pId; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { String fileName = rs.getString(3); String url = rs.getString(4); if (fileName.contains("Early") || fileName.contains("Middle") || fileName.contains("Late")) { continue; } String path = "/Users/rods/Documents/XCodeProjects/DigitalAtlasAcientLife/DigitalAtlasAcientLife/Resources/images/" + fileName; File file = new File(path); if (!file.exists()) { pw.println(String.format("curl %s > %s", url, fileName)); } //String captKey = String.format("%s_%dc", nm, j); //String captVal = values.get(captKey); pStmtAttach.setString(1, fileName); pStmtAttach.setString(2, url); pStmtAttach.setString(3, ""); pStmtAttach.setInt(4, 0); // Type if (pStmtAttach.executeUpdate() == 0) { System.err.println("Error inserting record."); } Integer attachId = BasicSQLUtils.getInsertedId(pStmtAttach); if (attachId == null) { throw new RuntimeException("Error saving attachment record."); } pStmtTaxonAttach.setInt(1, node.recId); pStmtTaxonAttach.setInt(2, attachId); if (pStmtTaxonAttach.executeUpdate() == 0) { System.err.println("Error inserting record."); } } rs.close(); } rsId.close(); /* for (int k=0;k<1;k++) { String nm = k == 0 ? "photo" : "map"; for (int j=1;j<9;j++) { String key = String.format("%s_%dd", nm, j); String val = values.get(key); if (isNotEmpty(val)) { System.out.println(String.format("%d [%s][%s]", recId, val, key)); sql = "SELECT p.ID, p.post_title, pm.meta_value, p.guid FROM wp_xuub_posts p INNER JOIN wp_xuub_postmeta pm ON p.ID = pm.post_id WHERE pm.meta_key = '_wp_attached_file' AND p.ID = " + val; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { //int pId = rs.getInt(1); //String title = rs.getString(2); String fileName = rs.getString(3); String url = rs.getString(4); pw.println(String.format("curl %s > %s", url, fileName)); String captKey = String.format("%s_%dc", nm, j); String captVal = values.get(captKey); pStmtAttach.setString(1, fileName); pStmtAttach.setString(2, url); pStmtAttach.setString(3, k == 0 ? captVal : ""); pStmtAttach.setInt(4, k); // Type if (pStmtAttach.executeUpdate() == 0) { System.err.println("Error inserting record."); } Integer attachId = BasicSQLUtils.getInsertedId(pStmtAttach); if (attachId == null) { throw new RuntimeException("Error saving attachment record."); } pStmtTaxonAttach.setInt(1, node.recId); pStmtTaxonAttach.setInt(2, attachId); if (pStmtTaxonAttach.executeUpdate() == 0) { System.err.println("Error inserting record."); } } } } }*/ // a:5:{s:5:"width";i:3300;s:6:"height";i:2550;s:4:"file";s:38:"Polygona_maxwelli_EarlyPleistocene.jpg";s:5:"sizes"; // a:2:{s:9:"thumbnail"; // a:4:{s:4:"file";s:46:"Polygona_maxwelli_EarlyPleistocene-250x250.jpg";s:5:"width";i:250;s:6:"height";i:250;s:9:"mime-type";s:10:"image/jpeg";} // s:6:"medium";a:4:{s:4:"file";s:48:"Polygona_maxwelli_EarlyPleistocene-2000x1545.jpg";s:5:"width";i:2000;s:6:"height";i:1545;s:9:"mime-type";s:10:"image/jpeg";}} //s:10:"image_meta";a:10:{s:8:"aperture";i:0;s:6:"credit";s:0:"";s:6:"camera";s:0:"";s:7:"caption";s:0:"";s:17:"created_timestamp";i:0;s:9:"copyright";s:0:"";s:12:"focal_length";i:0;s:3:"iso";i:0;s:13:"shutter_speed";i:0;s:5:"title";s:0:"";}} // sql = "SELECT p.ID, p.post_title, pm.meta_value, p.guid FROM wp_xuub_posts p " + // "INNER JOIN wp_xuub_postmeta pm ON p.ID = pm.post_id WHERE pm.meta_key = '_wp_attached_file' AND p.ID = " + recId; // ResultSet rs = stmt.executeQuery(sql); // while (rs.next()) // { // int pId = rs.getInt(1); // String title = rs.getString(2); // String fileName = rs.getString(3); // String url = rs.getString(4); // } } dbS3Conn.commit(); //recurseForAttachments(pStmtTaxonAttach, rootNode); stmt.close(); stmt2.close(); pStmt.close(); pStmtStrat.close(); pStmtAges.close(); pStmtAttach.close(); pStmtTaxonAttach.close(); stmt.close(); dbS3Conn.commit(); pw.close(); System.out.println("Done"); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:com.adanac.module.blog.dao.ArticleDao.java
public Integer saveOrUpdate(String resourceId, String subject, String createDate, Status status, String username, Integer accessTimes, Integer goodTimes, String html, String content) { return execute((TransactionalOperation<Integer>) connection -> { String selectSql = "select id,status from articles where resource_id=?"; String insertSql = "insert into articles (resource_id,username,icon,create_date," + "access_times,good_times,subject,html,content,status) values (?,?,?,?,?,?,?,?,?,?)"; String updateSql = "update articles set subject=?,html=?,content=?,icon=?,status=? where resource_id=? and type=0 "; try {// w ww. ja v a 2s .co m PreparedStatement statement = connection.prepareStatement(selectSql); statement.setString(1, resourceId); ResultSet resultSet = statement.executeQuery(); Boolean exists = false; Status currentStatus = Status.draft; Integer id = null; if (resultSet.next()) { exists = true; currentStatus = Status.valueOf(resultSet.getInt("status")); id = resultSet.getInt("id"); } PreparedStatement saveOrUpdate = null; if (!exists) { saveOrUpdate = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS); saveOrUpdate.setString(1, resourceId); saveOrUpdate.setString(2, username); saveOrUpdate.setString(3, ImageUtil.randomArticleImage(subject) == null ? "" : ImageUtil.randomArticleImage(subject)); saveOrUpdate.setString(4, createDate); saveOrUpdate.setInt(5, accessTimes); saveOrUpdate.setInt(6, goodTimes); saveOrUpdate.setString(7, subject); saveOrUpdate.setString(8, html); saveOrUpdate.setString(9, content); saveOrUpdate.setInt(10, status.getIntValue()); } else { saveOrUpdate = connection.prepareStatement(updateSql); saveOrUpdate.setString(1, subject); saveOrUpdate.setString(2, html); saveOrUpdate.setString(3, content); saveOrUpdate.setString(4, ImageUtil.randomArticleImage(subject)); saveOrUpdate.setInt(5, currentStatus == Status.published ? currentStatus.getIntValue() : status.getIntValue()); saveOrUpdate.setString(6, resourceId); } int result = saveOrUpdate.executeUpdate(); if (!exists && result > 0) { ResultSet keyResultSet = saveOrUpdate.getGeneratedKeys(); if (keyResultSet.next()) { id = keyResultSet.getInt(1); } } return id; } catch (SQLException e) { throw new RuntimeException(e); } }); }
From source file:utils.DBManager.java
public int insertPost(int userid, int groupid, String post) throws SQLException { Date d = new Date(); String aux = "" + d.getTime(); System.err.println(post);// www . ja v a2s . c om String sql = "INSERT INTO post(groupid,ownerid,date,content) VALUES (?,?,?,?)"; PreparedStatement stm = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); stm.setInt(1, groupid); stm.setInt(2, userid); stm.setString(3, aux); stm.setString(4, post); stm.executeUpdate(); stm.close(); PreparedStatement stmaux = con.prepareStatement("SELECT last_insert_rowid()"); int postid = -1; ResultSet res = stmaux.executeQuery(); if (res.next()) { postid = res.getInt(1); } res.close(); stm.close(); return postid; }
From source file:com.jabyftw.lobstercraft.world.CityStructure.java
/** * Create a house for the citizens.<br> * Note: this should run asynchronously. * * @param blockLocation house location given by the city manager * @return a response for the CommandSender */// w w w . j a va 2s .c o m public HouseCreationResponse createHouse(@NotNull final BlockLocation blockLocation) throws SQLException { // Check if there are more houses than current number of citizens + 1 (if level isn't the maximum) if (cityHouses.size() >= getMaximumNumberOfCitizens() + (cityLevel == MAXIMUM_CITY_LEVEL ? 0 : 2)) return HouseCreationResponse.TOO_MANY_HOUSES_REGISTERED; // Check minimum height if (blockLocation.getY() - BlockProtectionType.CITY_HOUSES.getProtectionDistance() < WorldService.MINIMUM_PROTECTION_HEIGHT) return HouseCreationResponse.HOUSE_COORDINATE_Y_TOO_LOW; // Check minimum and maximum distance between city center // Note: this should use the protection distance of the CITY_BLOCKS because this would make the center on the "same height" as the block if checkY is enabled on // CITY_HOUSES but not on CITY_BLOCKS // Note: the corner of the house should be the corner of current protection range if (BlockProtectionType.CITY_BLOCKS.protectionDistanceSquared(blockLocation, centerLocation) < getProtectionRangeSquared() - BlockProtectionType.CITY_HOUSES.getProtectionDistanceSquared()) return HouseCreationResponse.TOO_FAR_FROM_CENTER; else if (BlockProtectionType.CITY_BLOCKS.protectionDistanceSquared(blockLocation, centerLocation) < BlockProtectionType.CITY_HOUSES.getProtectionDistanceSquared()) return HouseCreationResponse.TOO_CLOSE_TO_THE_CENTER; // Check minimum distance between other houses for (BlockLocation existingBlockLocation : cityHouses.keySet()) if (BlockProtectionType.CITY_HOUSES.protectionDistanceSquared(blockLocation, existingBlockLocation) <= BlockProtectionType.CITY_HOUSES.getProtectionDistanceSquared()) return HouseCreationResponse.TOO_CLOSE_TO_OTHER_HOUSE; int houseId; // Insert to database { Connection connection = LobsterCraft.dataSource.getConnection(); // Prepare statement PreparedStatement preparedStatement = connection.prepareStatement( "INSERT INTO `minecraft`.`city_house_locations` (`city_cityId`, `worlds_worldId`, `centerChunkX`, `centerChunkZ`, `centerX`, `centerY`, `centerZ`) " + "VALUES (?, ?, ?, ?, ?, ?, ?);", Statement.RETURN_GENERATED_KEYS); // Set variables preparedStatement.setInt(1, cityId); preparedStatement.setByte(2, blockLocation.getChunkLocation().getWorldId()); preparedStatement.setInt(3, blockLocation.getChunkLocation().getChunkX()); preparedStatement.setInt(4, blockLocation.getChunkLocation().getChunkZ()); preparedStatement.setByte(5, blockLocation.getRelativeX()); preparedStatement.setShort(6, blockLocation.getY()); preparedStatement.setByte(7, blockLocation.getRelativeZ()); // Execute statement, get generated key preparedStatement.execute(); ResultSet generatedKeys = preparedStatement.getGeneratedKeys(); // Check if id exists if (!generatedKeys.next()) throw new SQLException("Generated key not generated!"); // Get house key houseId = generatedKeys.getInt("houseId"); if (houseId <= 0) throw new SQLException("House id must be greater than 0"); } // Create variable CityHouse cityHouse = new CityHouse(houseId, cityId, blockLocation); // Insert house and return cityHouses.put(cityHouse, cityHouse); return HouseCreationResponse.SUCCESSFULLY_CREATED_HOUSE; }
From source file:org.apache.sqoop.repository.common.CommonRepositoryHandler.java
/** * Helper method to insert the configs from the into the * repository.//from w w w . j av a 2 s .co m * * @param mDriver The driver instance to use to upgrade. * @param conn JDBC link to use for updating the configs */ private void insertConfigsForDriver(MDriver mDriver, Connection conn) { long driverId = mDriver.getPersistenceId(); PreparedStatement baseConfigStmt = null; PreparedStatement baseInputStmt = null; try { baseConfigStmt = conn.prepareStatement(crudQueries.getStmtInsertIntoConfig(), Statement.RETURN_GENERATED_KEYS); baseInputStmt = conn.prepareStatement(crudQueries.getStmtInsertIntoInput(), Statement.RETURN_GENERATED_KEYS); // Register a driver config as a job type with no direction registerConfigs(driverId, null, mDriver.getDriverConfig().getConfigs(), MConfigType.JOB.name(), baseConfigStmt, baseInputStmt, conn); } catch (SQLException ex) { throw new SqoopException(CommonRepositoryError.COMMON_0011, mDriver.toString(), ex); } finally { closeStatements(baseConfigStmt, baseInputStmt); } }
From source file:com.hs.mail.imap.dao.MySqlMessageDao.java
private long addHeaderName(final String headerName) { KeyHolder keyHolder = new GeneratedKeyHolder(); getJdbcTemplate().update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection con) throws SQLException { String sql = "INSERT INTO headername (headername) VALUES(?)"; PreparedStatement pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); pstmt.setString(1, headerName); return pstmt; }//from ww w . j a v a 2s .c o m }, keyHolder); return keyHolder.getKey().longValue(); }