Suppose say abc is a advertising company and it allows its’ visitors to test their advertisements in test environments before making them live. We need to implement such a requirement by copying the the records of one table into the another table under 2 different databases. I have written the java code to copy contents of a database table to another.

If ever you have some requirement or need to copy a contents of a table in a database to another, you might find the below example useful. In our below example we have 2 tables CM_ADS_METADATA_TEST and CM_ADS_METADATA under 2 different schemas dptest and dplive schemas.

For just a sample, we have copied a complex table with clob data and copied a record from test to live table. We did a select query to fetch a record,copied it to the file and again read that file and inserted the record into the live table.

CopyDataIntoDifDBs.java

package com.developprojects.main;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.xdb.XMLType;

import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;

/**
* @author Swarup Reddy
*
*/
public class CopyDataIntoDifDBs {
private static TransformerFactory transformerFactory = null;

public static void main(String[] args) throws SQLException,
IllegalArgumentException, TransformerConfigurationException,
TransformerException, IOException {

transformerFactory = TransformerFactory.newInstance();
Connection connection = null;
PreparedStatement preparedStatement = null;
OraclePreparedStatement stmt = null;
String sql = null;
ResultSet resultSet = null;
OracleResultSet orset = null;
XMLType poxml;
Document podoc;
final String clmnSprtr = ",,,";
final String rowSprtr = "###";
String contentToFile;
try {
System.out.println("Creating Connection..");
Class.forName("oracle.jdbc.driver.OracleDriver");
connection = DriverManager.getConnection("jdbc:oracle:thin:@Localhost:1521:TestDB", "TestUser", "TestPassword");
System.out.println("Creating Connection Successfully");
sql = "SELECT * FROM dptest.CM_ADS_METADATA_TEST WHERE CM_TRMT_ID = 50131";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
orset = (OracleResultSet) resultSet;
while (orset.next()) {
Long CM_TRMT_ID = orset.getLong(1);
System.out.println("CM_TRMT_ID " + CM_TRMT_ID.toString());
String ISO_ALPHA_LANG_CD = orset.getString(2).trim();
System.out.println("ISO_ALPHA_LANG_CD " + ISO_ALPHA_LANG_CD);
String MKT_CD = orset.getString(3).trim();
System.out.println("MKT_CD " + MKT_CD);
String PG_ID = orset.getString(4).trim();
System.out.println("PG_ID " + PG_ID);
poxml = XMLType.createXML(orset.getOPAQUE(5));
podoc = (Document) poxml.getDOM();
String TRMT_DTL_DA = getXMLFromDOM(podoc);
System.out.println("TRMT_DTL_DA ::" + TRMT_DTL_DA);
String PZN_DCSN_ENGINE_ABRV_NM = orset.getString(6).trim();
System.out.println("PZN_DCSN_ENGINE_ABRV_NM ::"
+ PZN_DCSN_ENGINE_ABRV_NM);
poxml = XMLType.createXML(orset.getOPAQUE(7));
podoc = (Document) poxml.getDOM();
String DCSN_ENGINE_CONV_TRMT_DA = getXMLFromDOM(podoc);
System.out.println("DCSN_ENGINE_CONV_TRMT_DA ::"
+ DCSN_ENGINE_CONV_TRMT_DA);
String LST_UPDT_USER_ID = orset.getString(8).trim();
System.out.println("LST_UPDT_USER_ID ::" + LST_UPDT_USER_ID);
Timestamp LST_UPDT_TS = orset.getTimestamp(9);
System.out.println("LST_UPDT_TS :: " + LST_UPDT_TS.toString());
contentToFile = CM_TRMT_ID.toString() + clmnSprtr
+ ISO_ALPHA_LANG_CD + clmnSprtr + MKT_CD + clmnSprtr
+ PG_ID + clmnSprtr + TRMT_DTL_DA + clmnSprtr
+ PZN_DCSN_ENGINE_ABRV_NM + clmnSprtr
+ DCSN_ENGINE_CONV_TRMT_DA + clmnSprtr
+ LST_UPDT_USER_ID + clmnSprtr + LST_UPDT_TS.toString()
+ rowSprtr;
InsertClob.writeToFile(contentToFile);
System.out.println("Content to file: \n" + contentToFile);

sql = "INSERT INTO dplive.CM_ADS_METADATA VALUES (?,?,?,?,?,?,?,?,?)";
stmt = (OraclePreparedStatement) connection
.prepareStatement(sql);
String insertData = InsertClob.readFromFile();
System.out.println("");
System.out.println("");
System.out.println("");
System.out.println("InsertString is: " + insertData);
System.out.println("");
System.out.println("");
System.out.println("");
String[] splitRows = insertData.split(rowSprtr);
System.out.println("Row value: " + splitRows[0]);
System.out.println("");
System.out.println("");
System.out.println("");
for (int i = 0; i < splitRows.length - 1; i++) {
System.out.println("splitRows.length::" + splitRows.length);
String[] splitColumns = splitRows[i].split(clmnSprtr);
System.out.println("splitColumns::" + splitColumns[0]);
stmt.setLong(1, Long.parseLong(splitColumns[0]));
stmt.setString(2, splitColumns[1]);
stmt.setString(3, splitColumns[2]);
stmt.setString(4, splitColumns[3]);

poxml = XMLType.createXML(connection, splitColumns[4]);
stmt.setObject(5, poxml);

stmt.setString(6, splitColumns[5]);

poxml = XMLType.createXML(connection, splitColumns[6]);
stmt.setObject(7, poxml);

stmt.setString(8, splitColumns[7]);
stmt.setTimestamp(9, Timestamp.valueOf(splitColumns[8]));
stmt.execute();

}
}
resultSet.close();
orset.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

}

// This method will convert a Document to String Object
public static String getXMLFromDOM(Document requestXMLDoc)
throws IllegalArgumentException, TransformerConfigurationException,
TransformerException {

if (requestXMLDoc == null) {
System.out.println("Supplied document object is null");
throw new IllegalArgumentException("Document cannot be null");
}
DOMSource source = new DOMSource(requestXMLDoc);
StreamResult result = new StreamResult(new StringWriter());
Transformer transformer = transformerFactory.newTransformer();
transformer.transform(source, result);
String returnString = result.getWriter().toString();

return returnString;
}

public static void writeToFile(String content) throws IOException {

File file = new File("D:\\Sample Files\\offer.txt");
if (!file.exists()) {
file.createNewFile();
}
FileWriter fw = new FileWriter(file.getAbsoluteFile(), true);
BufferedWriter bw = new BufferedWriter(fw);
bw.write(content);
bw.close();

}

public static String readFromFile() throws IOException {

File file = new File("D:\\Sample Files\\offer.txt");
if (!file.exists()) {
file.createNewFile();
}
FileReader fr = new FileReader(file.getAbsoluteFile());
BufferedReader br = new BufferedReader(fr);
StringBuffer content = new StringBuffer();
String temp = null;
int count = 0;
while ((temp = br.readLine()) != null) {
content.append(temp).append("\n");
count++;
}
System.out.println(count);
// br.reset();
fr.close();
br.close();
return content.toString();
}
}

Generated offer.txt File


50131,,,SV,,,SE,,,MYCASOA,,,<?xml version = '1.0'?>
<Treatment xmlns="http://www.americanexpress.com/galaxy/master/rule/v1/0/"><TreatmentMetadata><Attribute name="Phase">Draft</Attribute><Attribute name="ConversionTrackingId"/><Attribute name="Language">SV</Attribute><Attribute name="ReviewerEmail">Kalyan.A.Singirikonda@aexp.com</Attribute><Attribute name="Page">MYCASOA</Attribute><Attribute name="PointerValidator">https://cms.americanexpress.com/Render/docs/en/PZN/sv_SE/MYCA/TEXT/test.xml</Attribute><Attribute name="TreatmentStartDate">2014-09-03 16:00:00</Attribute><Attribute name="Notes"/><Attribute name="Priority">40</Attribute><Attribute name="Level2Description">Internal Acquisition_Cross-Sell</Attribute><Attribute name="SuppressionImpressionValue">9999</Attribute><Attribute name="Placement">TopLink</Attribute><Attribute name="ContentPointer">test</Attribute><Attribute name="Compliance">No</Attribute><Attribute name="TreatmentEndDate">2015-09-04 15:59:59</Attribute><Attribute name="RequesterEmail">Surya.P.Appisetty@aexp.com</Attribute><Attribute name="ReportingName">SESVChargeCorporateCardAcquisitionSwedenTestTrmt1234300c</Attribute><Attribute name="useDefaultOrOverrideInSuppression">Use Default</Attribute><Attribute name="Level1Description">Charge Corporate Card</Attribute><Attribute name="TargettingLevel">Customer</Attribute><Attribute name="OfferContentName">Sweden Test Trmt 1234</Attribute><Attribute name="ContentType">Text</Attribute><Attribute name="PagePlacementDimension">300c</Attribute><Attribute name="ApprovedDate"/><Attribute name="BusinessUnit">Charge</Attribute><Attribute name="ApprovedBy"/><Attribute name="Level0Description">Business Card</Attribute><Attribute name="CampaignObjective">Internal Acquisition_Cross-Sell</Attribute><Attribute name="FillerIndicator">KN</Attribute><Attribute name="Device"/><Attribute name="Weighting">99</Attribute><Attribute name="LastUpdated">2014-09-04 23:25:32</Attribute><Attribute name="Channel"/><Attribute name="CorpOnly">N</Attribute><Attribute name="TargettingType">Fixed</Attribute></TreatmentMetadata><RuleSet LogicalOperation="AND"><Rule><Expression><Category>Demographics</Category><LeftTerm TargetingLevel="Customer">PostCdZones</LeftTerm><Operator>includes</Operator><RightTerm><RightTermValue1>1234,6789</RightTermValue1></RightTerm></Expression></Rule></RuleSet></Treatment>,,,DEL,,,<?xml version = '1.0'?>
<PZNTreatment><PZNTreatmentMetaData><Treatment><Phase>Draft</Phase><ConversionTrackingId/><Language>SV</Language><Revieweremail>Kalyan.A.Singirikonda@aexp.com</Revieweremail><StartTs>2014-09-03 16:00:00</StartTs><Notes/><Priority>40</Priority><LegalApproval>No</LegalApproval><EndTs>2015-09-04 15:59:59</EndTs><Requestoremail>Surya.P.Appisetty@aexp.com</Requestoremail><ReportingName>SESVChargeCorporateCardAcquisitionSwedenTestTrmt1234300c</ReportingName><Lvl1Desc>Charge Corporate Card</Lvl1Desc><LowestTargetingLvl>Customer</LowestTargetingLvl><ContentNm>SwedenTestTrmt1234</ContentNm><ContentType>Text</ContentType><PlacementSize>300c</PlacementSize><ApprovedDate/><BusinessUnit>Charge</BusinessUnit><ApprovedBy/><Lvl0Desc>Business Card</Lvl0Desc><CampaignObjective>Internal Acquisition_Cross-Sell</CampaignObjective><FillerInd>KN</FillerInd><Device/><Weighting>99</Weighting><LastUpdated>2014-09-04 23:25:32</LastUpdated><Channel/><CorpOnly>N</CorpOnly><TargetingType>Fixed</TargetingType><DecisionEngine>DEL_SP</DecisionEngine><Client>AMEX</Client><RegionCd/><RptPageNm>MYCA SOA</RptPageNm><Lvl2Desc>Acquisition</Lvl2Desc><PlacementNm>TX300</PlacementNm><RptPlacementNm>TOPLINK</RptPlacementNm><ContentPointer>/sv_SE/MYCA/TEXT/test.xml</ContentPointer></Treatment></PZNTreatmentMetaData><PZNRuleMetaData><Rule><Targeting><CustProfile><PostCdZones><UIName>PostalCodeZones</UIName><include>1234,6789</include></PostCdZones></CustProfile></Targeting><EventCapping><MaxImpressionCt>9999</MaxImpressionCt></EventCapping><ControlGrp><Group2><Min>0</Min><Max>4</Max></Group2><Group3><Min>5</Min><Max>99</Max></Group3></ControlGrp></Rule></PZNRuleMetaData></PZNTreatment>,,,sappiset,,,2014-09-04 15:25:33.0###
Inserted Record into the Database


CM_TRMT_ID                  50131

ISO_ALPHA_LANG_CD           SV

MKT_CD                      SE

PG_ID                       MYCASOA

TRMT_DTL_DA                 <?xml version="1.0" encoding="UTF-8" standalone="yes"?><Treatment ><TreatmentMetadata><Attribute name="Phase">Draft</Attribute><Attribute name="ConversionTrackingId"></TreatmentMetadata></Treatment>

PZN_DCSN_ENGINE_ABRV_NM     DEL

DCSN_ENGINE_CONV_TRMT_DA    <PZNTreatment><PZNTreatmentMetaData><Treatment><Phase>Draft</Phase><ConversionTrackingId/><Language>SV</Language></Treatment></PZNRuleMetaData></PZNTreatment>

LST_UPDT_USER_ID             sappiset

LST_UPDT_TS                  2014-09-04 15:25:33.0


Swarup Reddy (13 Posts)

Founder of Developprojects.com. He is an Oracle certified expert java developer. He is proficient in end-to-end development right from requirement analysis to system study, designing, coding, testing, de- bugging, documentation and implementation. His areas of interests are Information Security, Programming, compiler designing and automation.