![\"\"](\"http:\/\/www.alphastar.net.au\/blog\/wp-content\/uploads\/2012\/01\/rows-initial.jpg\" "\\"rows-initial\\"")
<\/a><\/p>\nThe following code will insert a replicated row with department value changing for row 3.<\/p>\n
Usage: new ReplicateRow().replicateQuery(table_query,tmpTableName, originalTableForFinalInsert, hm);<\/p>\n
1=query<\/p>\n
2=temporary table<\/p>\n
3=table to re-insert row into (same table as specified in 1)<\/p>\n
4=Map of columns and values to update<\/p>\n
[code lang=”Java”]<\/p>\n
package alphastar;<\/p>\n
import java.sql.PreparedStatement;
\nimport java.sql.ResultSet;
\nimport java.sql.Statement;
\nimport java.sql.Connection;
\nimport java.sql.DriverManager;
\nimport java.sql.SQLException;
\nimport java.util.HashMap;
\nimport java.util.Iterator;
\nimport java.util.Map;
\nimport java.util.Set;<\/p>\n
public class ReplicateRow {<\/p>\n
public Connection conn;<\/p>\n
\/**
\n* @param args
\n*\/
\npublic static void main(String[] args) {
\nnew ReplicateRow().process();
\n}<\/p>\n
public void getConnection() {
\ntry {
\nClass.forName(“oracle.jdbc.driver.OracleDriver”);
\nString url = “jdbc:oracle:thin:@\/\/192.168.0.104:1521\/XE”;
\nconn = DriverManager.getConnection(url,”replicate”,”password”);
\nif (conn!=null) System.out.println(“have connection!”); else System.out.println(“don’t have connection”);
\n} catch (Exception e) {
\ne.printStackTrace();
\n}
\n}<\/p>\n
public void process() {
\ngetConnection();
\nString employee_query= “select e.* ”
\n+ “from employee e ”
\n+ “where ”
\n+ “1=1 ”
\n+ “and salary=122000 “;
\nString tmpTableName = “temptable”;
\nString originalTableForFinalInsert = “employee”;
\nMap<String,String> hm = new HashMap<String,String>();<\/p>\n
\/\/ build up the columns to change
\nhm.put(“id”, “999”); \/\/ really should do this properly. ie get 1 + last value in DB
\nhm.put(“department”,”marketing”);<\/p>\n
replicateQuery(employee_query,tmpTableName, originalTableForFinalInsert, hm);
\n}<\/p>\n
private boolean replicateQuery(
\nString queryToTmpTable,
\nString tmpTableName,
\nString originalTableToReInsert,
\nMap columnChangesMap) {<\/p>\n
int updateQuery = 0;<\/p>\n
try {
\nPreparedStatement ps = conn.prepareStatement(queryToTmpTable);
\nResultSet rs = ps.executeQuery();
\nStatement stmt = conn.createStatement();<\/p>\n
boolean tableExists = checkTableExists(tmpTableName);
\nif (tableExists) {
\nString flushToEmpty = “delete from “+tmpTableName;
\nupdateQuery = stmt.executeUpdate(flushToEmpty); \/\/ if hanging, commit in benthic
\nSystem.out.println(updateQuery+” table exists”);
\n} else {
\nString cs = copyStructure(queryToTmpTable, tmpTableName);
\nSystem.out.println(cs);
\nupdateQuery = stmt.executeUpdate(cs);
\nSystem.out.println(updateQuery+” table doen’t exist. create it”);
\n}
\n\/\/ insert data to temp table
\nString insert=”INSERT into “+tmpTableName+” “+queryToTmpTable;
\nint res = stmt.executeUpdate(insert);
\nSystem.out.println(res+” done insertion to “+tmpTableName+”\\n”);<\/p>\n
doUpdateOfColsOnTempTable(tmpTableName, columnChangesMap);<\/p>\n
String queryToTable = “select * from “+tmpTableName;
\ninsertRowToOriginalTable(originalTableToReInsert, queryToTable); \/\/ e.g. txn_event,xyz_new<\/p>\n
stmt.close();
\nconn.close();
\nreturn true;<\/p>\n
} catch (SQLException ex) {
\nSystem.err.println(ex);
\nreturn false;
\n}<\/p>\n
}<\/p>\n
\/**
\n*
\n* @param table
\n* @return true if the table exists. false otherwise
\n* @throws SQLException
\n*\/
\nprivate boolean checkTableExists(String table) throws SQLException {
\nString tableUC = table.toUpperCase();
\nboolean status = false;<\/p>\n
String checkExists = “”
\n+ “SELECT COUNT(1) ”
\n+ “FROM USER_TABLES ”
\n+ “WHERE TABLE_NAME = ‘”+tableUC+”‘ “; \/\/ must be UC
\nStatement stmt = conn.createStatement();
\nResultSet rs = stmt.executeQuery(checkExists);
\nboolean rowFound = rs.next();
\nif (rowFound== false) status = false;
\nString countVal = rs.getString(1);<\/p>\n
if (countVal.equals(“1”)) {
\nstatus = true;
\n} else if (countVal.contains(“0”)) {
\nstatus = false;
\n} else {
\n\/\/ shouldn’t get here
\nstatus = false;
\n}
\nreturn status;
\n}<\/p>\n
private boolean doUpdateOfColsOnTempTable(String tableName, Map colsAndVals) throws SQLException {
\nSet entrySet = colsAndVals.entrySet();
\nIterator i = entrySet.iterator();
\nMap.Entry me = null;
\nStringBuffer updateQuery = new StringBuffer();
\nupdateQuery.append(“update “+tableName+” “);
\nint counter = 0;
\nwhile (i.hasNext()) {
\nme = (Map.Entry) i.next();
\nif (counter == 0) updateQuery.append(“set “);
\nif (counter >= 1) updateQuery.append(“,”);
\nupdateQuery.append(me.getKey()+”='”+me.getValue()+”‘”);
\nSystem.out.println(“key = “+me.getKey());
\nSystem.out.println(“val = “+me.getValue()+”\\n”);
\ncounter++;
\n}
\nif (counter == 0) return false;<\/p>\n
Statement stmt = conn.createStatement();
\nint res = stmt.executeUpdate(updateQuery.toString());
\nstmt.close();
\nSystem.out.println(res+” done the set statement with keyset”);
\nreturn true;
\n}<\/p>\n
private String copyStructure(String in, String tmpTableName) {
\n\/\/ 1=0 will ensure that only the structure is copied. not data
\nString newTable = “create table “+tmpTableName+” as “+in+” and 1=0″;
\nreturn newTable;
\n}<\/p>\n
\/**
\n* This will insert the newly modified row back to the original table
\n*\/
\nprivate boolean insertRowToOriginalTable(String origTable, String queryToTable) throws SQLException {
\nString insert = “INSERT INTO “+origTable+” “+queryToTable;
\nStatement stmt = conn.createStatement();
\nint res = stmt.executeUpdate(insert);
\nSystem.out.println(res+” done insert into Original table\\n”);
\nif (res==1) return true;
\nelse return false;
\n}<\/p>\n
}<\/p>\n
[\/code]<\/p>\n","protected":false},"excerpt":{"rendered":"
This is a method of duplicating a row in an Oracle with a few specified column values to be changed\/updated. I needed to find an easy way to do this… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-87","post","type-post","status-publish","format-standard","hentry","category-coding"],"_links":{"self":[{"href":"https:\/\/alphastar.net.au\/weblog\/wp-json\/wp\/v2\/posts\/87","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/alphastar.net.au\/weblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/alphastar.net.au\/weblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/alphastar.net.au\/weblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/alphastar.net.au\/weblog\/wp-json\/wp\/v2\/comments?post=87"}],"version-history":[{"count":29,"href":"https:\/\/alphastar.net.au\/weblog\/wp-json\/wp\/v2\/posts\/87\/revisions"}],"predecessor-version":[{"id":112,"href":"https:\/\/alphastar.net.au\/weblog\/wp-json\/wp\/v2\/posts\/87\/revisions\/112"}],"wp:attachment":[{"href":"https:\/\/alphastar.net.au\/weblog\/wp-json\/wp\/v2\/media?parent=87"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/alphastar.net.au\/weblog\/wp-json\/wp\/v2\/categories?post=87"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/alphastar.net.au\/weblog\/wp-json\/wp\/v2\/tags?post=87"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}