public void renderTable(HashMap<String, String> tipeKolom) {
// tipeKolom = 1#Z#HCODE
// tipeKolom = 1#D#NONE
// tipeKolom = 1#N#FORMAT
for (int i = 0; i < tipeKolom.size(); i++) {
String temp = tipeKolom.get(i + "");
String data[] = temp.split("#");
int no = 0;
try {
no = Integer.parseInt(data[0].trim());
} catch (Exception e) {
no = 0;
}
if (data[1].trim().equals("Z")) {
Function.setColumnRender(this, data[2], no);
} else if (data[1].trim().equals("D")) {
Function.setColumnRenderDate(this, data[2], no);
} else if (data[1].trim().equals("N")) {
Function.setColumnRenderNumeric(this, no);
}
}
}
public void InsertDataTable() {
for (int i = 0; i < dataBaru.size(); i++) {
try {
rowSet.moveToInsertRow();
for (int ii = 0; ii < insertKolom.size(); ii++) {
String temp = insertKolom.get(ii + "");
String data[] = temp.split("#");
int no = 0;
boolean constanta = false;
if (data[1].trim().toLowerCase().equals("c")) {
constanta = true;
} else {
try {
no = Integer.parseInt(data[1].trim());
} catch (Exception e) {
no = 0;
}
}
if (data[0].trim().toLowerCase().contains("i")) {
if (data[2].trim().toLowerCase().equals("boolean")) {
String value = "";
if (data[4].trim().toLowerCase().equals("int")) {
if (getValueAt(
new Integer(dataBaru.get(i).toString()), Integer.parseInt(data[1].trim()))
.toString()
.equals("true")) {
if (data[5].trim().equals("1") || data[5].trim().equals("0")) {
value = "1";
} else {
value = "Y";
}
} else {
if (data[5].trim().equals("1") || data[5].trim().equals("0")) {
value = "0";
} else {
value = "N";
}
}
try {
rowSet.updateInt(data[3].trim().toString(), Integer.parseInt(value));
} catch (Exception e) {
e.printStackTrace();
}
} else {
if (getValueAt(
new Integer(dataBaru.get(i).toString()), Integer.parseInt(data[1].trim()))
.toString()
.equals("true")) {
if (data[5].trim().equals("1") || data[5].trim().equals("0")) {
value = "1";
} else {
value = "Y";
}
} else {
if (data[5].trim().equals("1") || data[5].trim().equals("0")) {
value = "0";
} else {
value = "N";
}
}
try {
rowSet.updateString(data[3].trim().toString(), value.trim());
} catch (Exception e) {
e.printStackTrace();
}
}
} else if (data[2].trim().toLowerCase().equals("date")) {
String value = "";
java.util.Date dd =
(java.util.Date) getValueAt(new Integer(dataBaru.get(i).toString()), no);
if (data[4].equals("dd-MM-yyyy")) {
value = Function.dateToString(dd);
} else if (data[4].equals("MM-yyyy")) {
value = Function.monthToString(dd).trim();
} else if (data[4].equals("yyyy")) {
// value = Function.yearToDate(sql);
JOptionPane.showMessageDialog(null, "Belom Support");
}
// JOptionPane.showMessageDialog(null, value.length());
try {
rowSet.updateString(data[3].trim().toString(), value.trim());
} catch (Exception e) {
e.printStackTrace();
}
} else if (data[2].trim().toLowerCase().equals("string")
|| data[2].trim().toLowerCase().equals("numeric")) {
String value = "";
if (constanta) {
value = data[6].trim();
} else {
try {
value = getValueAt(new Integer(dataBaru.get(i).toString()), no).toString();
} catch (Exception e) {
try {
value = getValueAt(new Integer(dataBaru.get(i).toString()), no).toString();
} catch (Exception ee) {
value = "";
}
}
}
if (data[4].trim().toLowerCase().equals("int")) {
try {
if (value.trim().equals("")) {
value = "0";
}
rowSet.updateLong(data[3].trim().toString(), Long.parseLong(value.trim()));
} catch (Exception e) {
e.printStackTrace();
}
} else {
try {
rowSet.updateString(data[3].trim().toString(), value.trim());
} catch (Exception e) {
e.printStackTrace();
}
}
} else if (data[2].trim().toLowerCase().equals("combo")) {
String value = "";
String valueKolom = "";
try {
valueKolom =
getValueAt(new Integer(dataBaru.get(i).toString()), no).toString().trim();
} catch (Exception e) {
valueKolom = "";
}
try {
value = Function.getValueFromCell(data[5].trim(), valueKolom);
} catch (Exception e) {
value = "";
}
if (data[4].trim().toLowerCase().equals("int")) {
try {
rowSet.updateString(data[3].trim().toString(), value.trim());
} catch (Exception e) {
e.printStackTrace();
}
} else {
try {
rowSet.updateString(data[3].trim().toString(), value.trim());
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
// JOptionPane.showMessageDialog(null, rowSet.getCommand());
rowSet.insertRow();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public void updateDataTable() {
for (int i = 0; i < dataAsli.size(); i++) {
try {
rowSet.absolute(new Integer(dataAsli.elementAt(i).toString()));
if (rowSet.next()) {
for (int ii = 0; ii < updateKolom.size(); ii++) {
String temp = updateKolom.get(ii + "");
String data[] = temp.split("#");
int no = 0;
try {
no = Integer.parseInt(data[1].trim());
} catch (Exception e) {
no = 0;
}
if (data[0].trim().toLowerCase().contains("u")) {
if (data[2].trim().toLowerCase().equals("boolean")) {
String value = "";
if (data[4].trim().toLowerCase().equals("int")) {
if (getValueAt(
new Integer(dataAsli.get(i).toString()), Integer.parseInt(data[1].trim()))
.toString()
.equals("true")) {
if (data[5].trim().equals("1") || data[5].trim().equals("0")) {
value = "1";
} else {
value = "Y";
}
} else {
if (data[5].trim().equals("1") || data[5].trim().equals("0")) {
value = "0";
} else {
value = "N";
}
}
try {
rowSet.updateInt(data[3].trim().toString(), Integer.parseInt(value));
} catch (Exception e) {
e.printStackTrace();
}
} else {
if (getValueAt(
new Integer(dataAsli.get(i).toString()), Integer.parseInt(data[1].trim()))
.toString()
.equals("true")) {
if (data[5].trim().equals("1") || data[5].trim().equals("0")) {
value = "1";
} else {
value = "Y";
}
} else {
if (data[5].trim().equals("1") || data[5].trim().equals("0")) {
value = "0";
} else {
value = "N";
}
}
try {
rowSet.updateString(data[3].trim().toString(), value.trim());
} catch (Exception e) {
e.printStackTrace();
}
}
} else if (data[2].trim().toLowerCase().equals("date")) {
String value = "";
java.util.Date dd =
(java.util.Date) getValueAt(new Integer(dataAsli.get(i).toString()), no);
if (data[4].equals("dd-MM-yyyy")) {
value = Function.dateToString(dd);
} else if (data[4].equals("MM-yyyy")) {
value = Function.monthToString(dd);
} else if (data[4].equals("yyyy")) {
// value = Function.yearToDate(sql);
JOptionPane.showMessageDialog(null, "Belom Support");
}
try {
rowSet.updateString(data[3].trim().toString(), value.trim());
} catch (Exception e) {
e.printStackTrace();
}
} else if (data[2].trim().toLowerCase().equals("string")
|| data[2].trim().toLowerCase().equals("numeric")) {
String value = "";
String data1 = data[1].trim();
if (data1 == null || data1.equals("")) {
data1 = "0";
}
try {
value = getValueAt(new Integer(dataAsli.get(i).toString()), no).toString();
} catch (Exception e) {
// e.printStackTrace();
try {
value = getValueAt(new Integer(dataAsli.get(i).toString()), no).toString();
} catch (Exception ee) {
value = "";
// e.printStackTrace();
}
}
if (data[4].trim().toLowerCase().equals("int")) {
try {
if (value.trim().equals("")) {
value = "0";
}
rowSet.updateLong(data[3].trim().toString(), Long.parseLong(value.trim()));
} catch (Exception e) {
e.printStackTrace();
}
} else {
try {
rowSet.updateString(data[3].trim().toString(), value.trim());
} catch (Exception e) {
e.printStackTrace();
}
}
} else if (data[2].trim().toLowerCase().equals("combo")) {
String value = "";
value =
Function.getValueFromCell(
data[5].trim(),
getValueAt(new Integer(dataAsli.get(i).toString()), no).toString().trim());
if (data[4].trim().toLowerCase().equals("int")) {
try {
rowSet.updateString(data[3].trim().toString(), value.trim());
} catch (Exception e) {
e.printStackTrace();
}
} else {
try {
rowSet.updateString(data[3].trim().toString(), value.trim());
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
rowSet.updateRow();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
// </editor-fold>
// <editor-fold defaultstate="collapsed" desc="Select Table">
public void selectTabel(DefaultTableModel tableM) {
setModelTable(tableM);
try {
dataAsli = new Vector();
rowSet.setCommand(sql);
rowSet.execute();
DefaultTableModel dtm = (DefaultTableModel) getModel();
int i = 0;
// tipeKolom =
// untukASIU#noKolomJtable#tipeKolomJTable#namaKolomDB#tipeKolom#atributKolom#nilaiKolom
// tipeKolom = boolean, Date, String, combo, Int, Numeric
while (rowSet.next()) {
Object[] dede = new Object[selectKolom.size()];
for (int ii = 0; ii < selectKolom.size(); ii++) {
String temp = selectKolom.get(ii + "");
String data[] = temp.split("#");
int no = 0;
try {
no = Integer.parseInt(data[1].trim());
} catch (Exception e) {
no = 0;
}
if (data[0].trim().toLowerCase().contains("s")) {
if (data[2].trim().toLowerCase().equals("boolean")) {
boolean value = false;
if (data[4].trim().toLowerCase().equals("int")) {
if (rowSet.getInt(data[3].trim()) == Integer.parseInt(data[5].trim())) {
value = true;
} else {
value = false;
}
} else {
if (rowSet.getString(data[3].trim()).equals(data[5].trim())) {
value = true;
} else {
value = false;
}
}
dede[no] = value;
} else if (data[2].trim().toLowerCase().equals("date")) {
java.util.Date value = new java.util.Date();
if (data[4].equals("dd-MM-yyyy")) {
value = Function.dateStringToDate(rowSet.getString(data[3].trim()));
} else if (data[4].equals("MM-yyyy")) {
value = Function.stringToMonth(rowSet.getString(data[3].trim()));
} else if (data[4].equals("yyyy")) {
value = Function.yearToDate(rowSet.getString(data[3].trim()));
}
dede[no] = value;
} else if (data[2].trim().toLowerCase().equals("string")
|| data[2].trim().toLowerCase().equals("combo")
|| data[2].trim().toLowerCase().equals("numeric")) {
String value = "";
if (data[4].trim().toLowerCase().equals("int")) {
value = rowSet.getLong(data[3].trim()) + "";
} else {
value = rowSet.getString(data[3].trim());
}
dede[no] = value;
}
}
}
dtm.addRow(dede);
dataAsli.add(i);
i++;
}
} catch (Exception e) {
e.printStackTrace();
}
}
public void runReplicate() throws InterruptedException {
if (verbose) log.append("Started at " + Calendar.getInstance().getTime() + "\r\n");
Connection connectionFrom = null;
Connection connectionTo = null;
try {
connectionFrom = providerI.getConnection(dbFrom);
connectionTo = providerI.getConnection(dbTo);
PreparedStatement preparedStatementFrom = null;
PreparedStatement preparedStatementTo = null;
ResultSet resultSet = null;
for (Iterator<HashMap<String, String>> HashMapIterator = tables.iterator();
HashMapIterator.hasNext(); ) {
try {
HashMap<String, String> h = HashMapIterator.next();
String name = h.get("name");
String name2 = h.get("name2");
String where1 = h.get("where1");
String where2 = h.get("where2");
if (verbose)
log.append("Replicating " + dbFrom + "/" + name + " to " + dbTo + "/" + name2 + "\r\n");
preparedStatementFrom =
connectionFrom.prepareStatement("select * from " + name + " WHERE 1=1 " + where1);
boolean b = preparedStatementFrom.execute();
HashMap meta = new HashMap();
// 1. find all ids from DB1
ArrayList db1 = new ArrayList();
String nullMark = "____NULL";
if (b) {
resultSet = preparedStatementFrom.getResultSet();
ResultSetMetaData setMetaData = resultSet.getMetaData();
for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
meta.put(
setMetaData.getColumnLabel(i).toLowerCase(), setMetaData.getColumnClassName(i));
}
while (resultSet.next()) {
HashMap results = new HashMap();
for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
String columnName = setMetaData.getColumnLabel(i).toLowerCase();
Object o = resultSet.getObject(i);
if (o != null) {
results.put(columnName, o);
} else {
results.put(columnName + nullMark, nullMark);
}
}
db1.add(results);
}
resultSet.close();
} else {
log.append("Couldn't execute select from " + dbFrom + "/" + name + " /r/n");
return;
}
StringBuffer allIds = new StringBuffer();
HashMap records1 = new HashMap();
HashMap toInsert = new HashMap();
HashMap toUpdate = new HashMap();
for (Iterator iterator = db1.iterator(); iterator.hasNext(); ) {
HashMap record = (HashMap) iterator.next();
if (allIds.length() != 0) {
allIds.append(",");
}
allIds.append(record.get("id"));
records1.put(record.get("id"), record);
}
toInsert.putAll(records1);
// 2. find all ids to delete in DB2;
if (allIds.length() > 0) {
preparedStatementTo =
connectionTo.prepareStatement(
"delete from "
+ name2
+ " where id not in ("
+ allIds.toString()
+ ")"
+ where2);
if (verbose)
log.append(
"deleted from "
+ dbTo
+ "/"
+ name2
+ " "
+ preparedStatementTo.executeUpdate()
+ " records;\r\n");
} else {
if (verbose)
log.append(
"No records in "
+ dbFrom
+ "/"
+ name
+ ", nothing to delete in "
+ dbTo
+ "/"
+ name2
+ " ;\r\n");
}
// 3. find all ids from DB2;
preparedStatementTo =
connectionTo.prepareStatement("select * from " + name2 + " WHERE 1=1 " + where2);
b = preparedStatementTo.execute();
HashMap meta2 = new HashMap();
ArrayList db2 = new ArrayList();
if (b) {
resultSet = preparedStatementTo.getResultSet();
ResultSetMetaData setMetaData = resultSet.getMetaData();
for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
meta2.put(
setMetaData.getColumnLabel(i).toLowerCase(), setMetaData.getColumnClassName(i));
}
while (resultSet.next()) {
HashMap results = new HashMap();
for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
String columnName = setMetaData.getColumnLabel(i).toLowerCase();
Object o = resultSet.getObject(i);
if (o != null) {
results.put(columnName, o);
} else {
results.put(columnName + nullMark, nullMark);
}
}
db2.add(results);
}
} else {
log.append("Couldn't execute select from " + dbTo + "/" + name2 + " /r/n");
return;
}
// compare meta-data;
{
HashMap temp = new HashMap();
{
temp.putAll(meta);
Set set = meta2.keySet();
for (Iterator iteratorSet = set.iterator(); iteratorSet.hasNext(); ) {
Object o = iteratorSet.next();
if (meta.containsKey(o)) {
if (meta.get(o).equals(meta2.get(o))) {
temp.remove(o);
}
} else {
log.append("ERROR: Meta data not equals! \r\n");
log.append(o + "\t" + temp.get(o) + " not present in table " + name + "\r\n");
}
}
}
if (!temp.isEmpty()) {
log.append("ERROR: Meta data not equals! \r\n");
Set set = temp.keySet();
for (Iterator iteratorSet = set.iterator(); iteratorSet.hasNext(); ) {
Object o = iteratorSet.next();
log.append(o + "\t" + temp.get(o) + " != " + meta2.get(o) + "\r\n");
}
return;
}
}
for (Iterator iterator = db2.iterator(); iterator.hasNext(); ) {
HashMap db2Record = (HashMap) iterator.next();
if (toInsert.containsKey(db2Record.get("id"))) {
HashMap db1Record = (HashMap) toInsert.get(db2Record.get("id"));
boolean equal = true;
Set set = meta2.keySet();
for (Iterator iteratorSet = set.iterator(); equal && iteratorSet.hasNext(); ) {
String columnName2 = (String) iteratorSet.next();
if (db2Record.containsKey(columnName2 + nullMark)
|| db1Record.containsKey(columnName2 + nullMark)) {
equal =
db2Record.containsKey(columnName2 + nullMark)
&& db1Record.containsKey(columnName2 + nullMark);
} else {
// checking not-null;
equal = equalRecords(db1Record.get(columnName2), db2Record.get(columnName2));
}
}
if (!equal) {
toUpdate.put(db2Record.get("id"), toInsert.get(db2Record.get("id")));
}
toInsert.remove(db2Record.get("id"));
} else {
// this case shouldn't happen at all, since we've deleted all such records
}
}
log.append(
"Found "
+ toUpdate.size()
+ " to update, and "
+ toInsert.size()
+ " to insert.\r\n");
int totalUpdated = 0;
// 4. calculate all to update in DB2
if (!toUpdate.isEmpty()) {
Set set = toUpdate.keySet();
for (Iterator iteratorSet = set.iterator(); iteratorSet.hasNext(); ) {
StringBuffer sql = new StringBuffer();
Object id = iteratorSet.next();
HashMap r = (HashMap) toUpdate.get(id);
sql.append("UPDATE " + name2 + " SET ");
StringBuffer values = new StringBuffer();
Set en = meta2.keySet();
for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
Object o = iteratorSetEn.next();
if (!o.equals("id")) {
if (values.length() != 0) {
values.append(",");
}
Object quote = dbQuotes.get(dbToType);
if (quote == null) {
quote = "";
}
values.append(quote).append(o).append(quote);
values.append(" = ? ");
}
}
values.append(" WHERE id = '" + r.get("id") + "';");
PreparedStatement statement =
connectionTo.prepareStatement(sql.toString() + values.toString());
en = meta2.keySet();
int i = 0;
for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
Object o = iteratorSetEn.next();
if (!o.equals("id")) {
i++;
statement.setObject(i, r.get(o));
}
}
try {
totalUpdated += statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
log.append("Error occured: " + e + "\r\n");
}
}
}
if (verbose) log.append("Updated " + totalUpdated + " records.\r\n");
// 4. calculate all to insert to DB2
if (!toInsert.isEmpty()) {
StringBuffer header = new StringBuffer();
if (header.length() == 0) {
header.append(" INSERT INTO " + name2 + " (");
StringBuffer columns = new StringBuffer();
Set en = meta2.keySet();
for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
Object o = iteratorSetEn.next();
if (columns.length() != 0) {
columns.append(",");
}
Object quote = dbQuotes.get(dbToType);
if (quote == null) {
quote = "";
}
columns.append(quote).append(o).append(quote);
}
header.append(columns.toString());
header.append(") VALUES ");
}
Set enumeration = toInsert.keySet();
for (Iterator iteratorSetX = enumeration.iterator(); iteratorSetX.hasNext(); ) {
Object id = iteratorSetX.next();
HashMap r = (HashMap) toInsert.get(id);
StringBuffer values = new StringBuffer();
if (values.length() != 0) {
values.append(",");
}
values.append("(");
StringBuffer columns = new StringBuffer();
Set en = meta2.keySet();
for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
Object o = iteratorSetEn.next();
if (columns.length() != 0) {
columns.append(",");
}
columns.append(" ? ");
}
values.append(columns.toString());
values.append(");");
PreparedStatement statement =
connectionTo.prepareStatement(header.toString() + values.toString());
en = meta2.keySet();
int i = 0;
for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
Object o = iteratorSetEn.next();
i++;
statement.setObject(i, r.get(o));
}
statement.execute();
}
}
if (verbose) log.append("Replication finished OK.\r\n");
} catch (Exception e) {
log.append("Some error occured: " + e + "\r\n");
log.append(e.getMessage() + "\r\n");
e.printStackTrace();
}
}
} catch (Exception e) {
log.append("Error with query = " + e);
log.append(e.getMessage());
return;
} finally {
try {
if (connectionFrom != null && !connectionFrom.isClosed()) {
connectionFrom.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (connectionTo != null && !connectionTo.isClosed()) {
connectionTo.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
if (verbose) log.append("Ended at " + Calendar.getInstance().getTime());
}