ide/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLStatementGenerator.java - netbeans - Git at Google

 /*
  * Licensed to the Apache Software Foundation (ASF) under one
  * or more contributor license agreements.  See the NOTICE file
  * distributed with this work for additional information
  * regarding copyright ownership.  The ASF licenses this file
  * to you under the Apache License, Version 2.0 (the
  * "License"); you may not use this file except in compliance
  * with the License.  You may obtain a copy of the License at
  *
  *   http://www.apache.org/licenses/LICENSE-2.0
  *
  * Unless required by applicable law or agreed to in writing,
  * software distributed under the License is distributed on an
  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  * KIND, either express or implied.  See the License for the
  * specific language governing permissions and limitations
  * under the License.
  */
 package org.netbeans.modules.db.dataview.output;

 import java.sql.Blob;
 import java.sql.Clob;
 import java.sql.SQLException;
 import java.sql.Types;
 import java.util.ArrayList;
 import java.util.HashSet;
 import java.util.List;
 import java.util.Map;
 import java.util.Set;
 import java.util.logging.Level;
 import java.util.logging.Logger;
 import javax.swing.SwingUtilities;
 import org.netbeans.modules.db.dataview.meta.DBColumn;
 import org.netbeans.modules.db.dataview.meta.DBException;
 import org.netbeans.modules.db.dataview.meta.DBMetaDataFactory;
 import org.netbeans.modules.db.dataview.meta.DBPrimaryKey;
 import org.netbeans.modules.db.dataview.meta.DBTable;
 import org.netbeans.modules.db.dataview.util.BinaryToStringConverter;
 import org.netbeans.modules.db.dataview.util.DataViewUtils;
 import org.openide.util.NbBundle;

 /**
  * Generates DML for editable resultset
  *
  * @author Ahimanikya Satapathy
  */
 class SQLStatementGenerator {
     private static final Logger LOG =
             Logger.getLogger(SQLStatementGenerator.class.getName());

     String generateInsertStatement(DBTable table, Object[] insertedRow) throws DBException {
         List<DBColumn> columns = table.getColumnList();

         StringBuilder insertSql = new StringBuilder();
         insertSql.append("INSERT INTO "); // NOI18N

         StringBuilder colNames = new StringBuilder(" ("); // NOI18N
         StringBuilder values = new StringBuilder();
         String commaStr = ", "; // NOI18N
         boolean comma = false;
         for (int i = 0; i < insertedRow.length; i++) {
             DBColumn dbcol = columns.get(i);
             Object val = insertedRow[i];

             if (dbcol.isGenerated()) { // NOI18N
                 continue;
             }

             if ((val == null || val.equals("<NULL>")) && !dbcol.isNullable()) { // NOI18N
                 throw new DBException(NbBundle.getMessage(SQLStatementGenerator.class, "MSG_nullable_check"));
             }

             if (comma) {
                 values.append(commaStr);
                 colNames.append(commaStr);
             } else {
                 comma = true;
             }

             // Check for Constant e.g <NULL>, <DEFAULT>, <CURRENT_TIMESTAMP> etc
             if (val instanceof SQLConstant) {
                 values.append(((SQLConstant) val).name());
             } else { // ELSE literals
                 values.append(val == null ? " NULL " : "?"); // NOI18N
             }
             colNames.append(dbcol.getQualifiedName(true));
         }

         colNames.append(")"); // NOI18N
         insertSql.append(table.getFullyQualifiedName(true));
         insertSql.append(colNames.toString());
         insertSql.append(" Values("); // NOI18N
         insertSql.append(values.toString());
         insertSql.append(")"); // NOI18N

         return insertSql.toString();
     }

     String generateRawInsertStatement(DBTable table, Object[] insertedRow) throws DBException {
         List<DBColumn> columns = table.getColumnList();

         StringBuilder rawInsertSql = new StringBuilder();
         rawInsertSql.append("INSERT INTO "); // NOI18N

         String rawcolNames = " ("; // NOI18N
         String rawvalues = "";  // NOI18N
         String commaStr = ", "; // NOI18N
         boolean comma = false;
         for (int i = 0; i < insertedRow.length; i++) {
             DBColumn dbcol = columns.get(i);
             Object val = insertedRow[i];

             if (dbcol.isGenerated()) { // NOI18N
                 continue;
             }

             if ((val == null || val.equals("<NULL>")) && !dbcol.isNullable()) { // NOI18N
                 throw new DBException(NbBundle.getMessage(SQLStatementGenerator.class, "MSG_nullable_check"));
             }

             if (comma) {
                 rawvalues += commaStr;
                 rawcolNames += commaStr;
             } else {
                 comma = true;
             }

             // Check for Constant e.g <NULL>, <DEFAULT>, <CURRENT_TIMESTAMP> etc
             if (val instanceof SQLConstant) {
                 rawvalues += (((SQLConstant) val).name());
             } else { // ELSE literals
                 rawvalues += getQualifiedValue(dbcol.getJdbcType(), insertedRow[i]);
             }
             rawcolNames += dbcol.getQualifiedName(false);
         }

         rawcolNames += ")"; // NOI18N
         rawInsertSql.append(table.getFullyQualifiedName(false));
         rawInsertSql.append(rawcolNames);
         rawInsertSql.append(" \n\tVALUES (");  // NOI18N
         rawInsertSql.append(rawvalues);
         rawInsertSql.append(")"); // NOI18N

         return rawInsertSql.toString();
     }

     String generateUpdateStatement(DBTable table, int row, Map<Integer, Object> changedRow, List<Object> values, List<Integer> types, DataViewTableUIModel tblModel) throws DBException {
         List<DBColumn> columns = table.getColumnList();

         StringBuilder updateStmt = new StringBuilder();
         updateStmt.append("UPDATE ").append(table.getFullyQualifiedName(true)).append(" SET "); // NOI18N
         String commaStr = ", "; // NOI18N
         boolean comma = false;
         for (Integer col : changedRow.keySet()) {
             DBColumn dbcol = columns.get(col);
             Object value = changedRow.get(col);
             int type = dbcol.getJdbcType();

             if ((value == null || value.equals("<NULL>")) && !dbcol.isNullable()) { // NOI18N
                 throw new DBException(NbBundle.getMessage(SQLStatementGenerator.class, "MSG_nullable_check"));
             }

             if (comma) {
                 updateStmt.append(commaStr);
             } else {
                 comma = true;
             }

             updateStmt.append(dbcol.getQualifiedName(true));
             // Check for Constant e.g <NULL>, <DEFAULT>, <CURRENT_TIMESTAMP> etc
             if (value instanceof SQLConstant) {
                 updateStmt.append(" = ").append(((SQLConstant) value).name());
             // NULL ist reported as an SQL constant, so treat it as such
             } else if ( value == null ) {
                 updateStmt.append(" = NULL"); // NOI18N
             } else { // ELSE literals
                 updateStmt.append(" = ?"); // NOI18N
                 values.add(value);
                 types.add(type);
             }
         }

         updateStmt.append(" WHERE "); // NOI18N
         generateWhereCondition(table, updateStmt, types, values, row, tblModel);
         return updateStmt.toString();
     }

     String generateUpdateStatement(DBTable table, int row, Map<Integer, Object> changedRow, DataViewTableUIModel tblModel) throws DBException {
         List<DBColumn> columns = table.getColumnList();

         StringBuilder rawUpdateStmt = new StringBuilder();
         rawUpdateStmt.append("UPDATE ").append(table.getFullyQualifiedName(false)).append(" SET "); // NOI18N

         String commaStr = ", "; // NOI18N
         boolean comma = false;
         for (Integer col : changedRow.keySet()) {
             DBColumn dbcol = columns.get(col);
             Object value = changedRow.get(col);
             int type = dbcol.getJdbcType();

             if ((value == null || value.equals("<NULL>")) && !dbcol.isNullable()) { // NOI18N
                 throw new DBException(NbBundle.getMessage(SQLStatementGenerator.class, "MSG_nullable_check"));
             }

             if (comma) {
                 rawUpdateStmt.append(commaStr);
             } else {
                 comma = true;
             }

             rawUpdateStmt.append(dbcol.getQualifiedName(true));
             // Check for Constant e.g <NULL>, <DEFAULT>, <CURRENT_TIMESTAMP> etc
             if (value instanceof SQLConstant) {
                 rawUpdateStmt.append(" = ").append(((SQLConstant) value).name());
             } else { // ELSE literals
                 rawUpdateStmt.append(" = ").append(getQualifiedValue(type, value).toString());
             }
         }

         rawUpdateStmt.append(" WHERE "); // NOI18N
         generateWhereCondition(table, rawUpdateStmt, row, tblModel);
         return rawUpdateStmt.toString();
     }

     String generateDeleteStatement(DBTable table, List<Integer> types, List<Object> values, int rowNum, DataViewTableUIModel tblModel) {
         StringBuilder deleteStmt = new StringBuilder();
         deleteStmt.append("DELETE FROM ").append(table.getFullyQualifiedName(true)).append(" WHERE "); // NOI18N

         generateWhereCondition(table, deleteStmt, types, values, rowNum, tblModel);
         return deleteStmt.toString();
     }

     String generateDeleteStatement(DBTable table, int rowNum, DataViewTableUIModel tblModel) {
         StringBuilder rawDeleteStmt = new StringBuilder();
         rawDeleteStmt.append("DELETE FROM ").append(table.getFullyQualifiedName(false)).append(" WHERE "); // NOI18N

         generateWhereCondition(table, rawDeleteStmt, rowNum, tblModel);
         return rawDeleteStmt.toString();
     }

     // TODO: Support for FK, and other constraint and Index recreation.
     String generateCreateStatement(DBTable table) throws DBException, Exception {
         boolean isdb2 = table.getParentObject().getDBType() == DBMetaDataFactory.DB2;

         StringBuilder sql = new StringBuilder();
         List<DBColumn> columns = table.getColumnList();
         sql.append("CREATE TABLE ").append(table.getQualifiedName(false)).append(" ("); // NOI18N
         int count = 0;
         for (DBColumn col : columns) {
             if (count++ > 0) {
                 sql.append(", "); // NOI18N
             }

             String typeName = col.getTypeName();
             sql.append(col.getQualifiedName(false)).append(" ");

             int scale = col.getScale();
             int precision = col.getPrecision();
             if (precision > 0 && DataViewUtils.isPrecisionRequired(col.getJdbcType(), isdb2)) {
                 if (typeName.contains("(")) { // Handle MySQL Binary Type // NOI18N
                     sql.append(typeName.replace("(", "(" + precision)); // NOI18N
                 } else {
                     sql.append(typeName).append("(").append(precision); // NOI18N
                     if (scale > 0 && DataViewUtils.isScaleRequired(col.getJdbcType())) {
                         sql.append(", ").append(scale).append(")"); // NOI18N
                     } else {
                         sql.append(")"); // NOI18N
                     }
                 }
             } else {
                 sql.append(typeName);
             }

             if (DataViewUtils.isBinary(col.getJdbcType()) && isdb2) {
                 sql.append("  FOR BIT DATA "); // NOI18N
             }

             if (col.hasDefault()) {
                 sql.append(" DEFAULT ").append(col.getDefaultValue()).append(" "); // NOI18N
             }

             if (!col.isNullable()) {
                 sql.append(" NOT NULL"); // NOI18N
             }

             if (col.isGenerated()) {
                 sql.append(" ").append(getAutoIncrementText(table.getParentObject().getDBType()));
             }
         }

         DBPrimaryKey pk = table.getPrimaryKey();
         if (pk != null) {
             count = 0;
             sql.append(", PRIMARY KEY ("); // NOI18N
             for (String col : pk.getColumnNames()) {
                 if (count++ > 0) {
                     sql.append(", "); // NOI18N
                 }
                 sql.append(table.getQuoter().quoteIfNeeded(col));
             }
             sql.append(")"); // NOI18N
         }
         sql.append(")"); // NOI18N

         return sql.toString();
     }

     private boolean addSeparator(boolean and, StringBuilder sql, String sep) {
         if (and) {
             sql.append(sep);
             return true;
         } else {
             return true;
         }
     }

     private void generateNameValue(DBColumn column, StringBuilder sql, Object value, List<Object> values, List<Integer> types) {
         sql.append(column.getQualifiedName(true));
         if (value != null) {
             values.add(value);
             types.add(column.getJdbcType());
             sql.append(" = ? "); // NOI18N
         } else { // Handle NULL value in where condition
             sql.append(" IS NULL "); // NOI18N
         }
     }

     private void generateNameValue(DBColumn column, StringBuilder sql, Object value) {
         String columnName = column.getQualifiedName(false);
         int type = column.getJdbcType();

         sql.append(columnName);
         if (value != null) {
             sql.append(" = ").append(getQualifiedValue(type, value)); // NOI18N
         } else { // Handle NULL value in where condition
             sql.append(" IS NULL"); // NOI18N
         }
     }

     void generateWhereCondition(DBTable table, StringBuilder result, List<Integer> types, List<Object> values, int rowNum, DataViewTableUIModel model) {
         assert SwingUtilities.isEventDispatchThread() : "Needs to be called on the EDT";

         DBPrimaryKey key = table.getPrimaryKey();
         Set<String> columnsSelected = new HashSet<>();
         boolean and = false;

         List<DBColumn> columns = table.getColumnList();

         StringBuilder pkSelect = new StringBuilder();
         List<Integer> pkTypes = new ArrayList<>();
         List<Object> pkObject = new ArrayList<>();

         if (key != null) {
             for (String keyName : key.getColumnNames()) {
                 for (int i = 0; i < model.getColumnCount(); i++) {
                     DBColumn dbcol = columns.get(i);
                     String columnName = dbcol.getName();
                     if (columnName.equals(keyName)) {
                         Object val = model.getOriginalValueAt(rowNum, i);
                         if (val != null) {
                             columnsSelected.add(columnName);
                             and = addSeparator(and, pkSelect, " AND "); // NOI18N
                             generateNameValue(dbcol, pkSelect, val, pkObject, pkTypes);
                             break;
                         }
                     }
                 }
             }
         }

         if (key != null && columnsSelected.equals(new HashSet<>(key.getColumnNames()))) {
             result.append(pkSelect);
             types.addAll(pkTypes);
             values.addAll(pkObject);
         } else {
             and = false;
             for (int i = 0; i < model.getColumnCount(); i++) {
                 DBColumn dbcol = columns.get(i);
                 Object val = model.getOriginalValueAt(rowNum, i);
                 and = addSeparator(and, result, " AND "); // NOI18N
                 generateNameValue(dbcol, result, val, values, types);
             }
         }
     }

     void generateWhereCondition(DBTable table, StringBuilder sql, int rowNum, DataViewTableUIModel model) {
         assert SwingUtilities.isEventDispatchThread() : "Needs to be called on the EDT";

         DBPrimaryKey key = table.getPrimaryKey();
         Set<String> columnsSelected = new HashSet<>();
         boolean and = false;

         List<DBColumn> columns = table.getColumnList();

         StringBuilder pkSelect = new StringBuilder();

         if (key != null) {
             for (String keyName : key.getColumnNames()) {
                 for (int i = 0; i < model.getColumnCount(); i++) {
                     DBColumn dbcol = columns.get(i);
                     String columnName = dbcol.getName();
                     if (columnName.equals(keyName)) {
                         Object val = model.getOriginalValueAt(rowNum, i);
                         if (val != null) {
                             columnsSelected.add(columnName);
                             and = addSeparator(and, pkSelect, " AND "); // NOI18N
                             generateNameValue(dbcol, pkSelect, val);
                             break;
                         }
                     }
                 }
             }
         }

         if (key != null && columnsSelected.equals(new HashSet<>(key.getColumnNames()))) {
             sql.append(pkSelect);
         } else {
             and = false;
             for (int i = 0; i < model.getColumnCount(); i++) {
                 DBColumn dbcol = columns.get(i);
                 Object val = model.getOriginalValueAt(rowNum, i);
                 and = addSeparator(and, sql, " AND "); // NOI18N
                 generateNameValue(dbcol, sql, val);
             }
         }
     }

     private Object getQualifiedValue(int type, Object val) {
         if (val == null) {
             return "NULL"; // NOI18N
         }
         if (type == Types.BIT && !(val instanceof Boolean)) {
             return "b'" + val + "'"; // NOI18N
         } else if (DataViewUtils.isNumeric(type)) {
             return val;
         } else if (val instanceof Clob) {
             try {
                 Clob lob = (Clob) val;
                 String result = lob.getSubString(1, (int) lob.length());
                 return "'" + result.replace("'", "''") + "'"; //NOI18N
             } catch (SQLException ex) {
                 LOG.log(Level.INFO, "Failed to read CLOB", ex); //NOI18N
             }
         } else if (val instanceof Blob) {
             try {
                 Blob lob = (Blob) val;
                 byte[] result = lob.getBytes(1, (int) lob.length());
                 return "x'" + BinaryToStringConverter.convertToString(
                         result, 16, false) + "'"; // NOI18N
             } catch (SQLException ex) {
                 LOG.log(Level.INFO, "Failed to read BLOB", ex); //NOI18N
             }
         }
         // Fallback if previous converts fail
         return "'" + val.toString().replace("'", "''") + "'"; //NOI18N
     }

     private String getAutoIncrementText(int dbType) throws Exception {
         switch (dbType) {
             case DBMetaDataFactory.MYSQL:
                 return "AUTO_INCREMENT"; // NOI18N

             case DBMetaDataFactory.PostgreSQL:
                 return "SERIAL"; // NOI18N

             case DBMetaDataFactory.SQLSERVER:
                 return "IDENTITY"; // NOI18N
             default:
                 return "GENERATED ALWAYS AS IDENTITY"; // NOI18N
         }
     }
 }
	/*
	* Licensed to the Apache Software Foundation (ASF) under one
	* or more contributor license agreements. See the NOTICE file
	* distributed with this work for additional information
	* regarding copyright ownership. The ASF licenses this file
	* to you under the Apache License, Version 2.0 (the
	* "License"); you may not use this file except in compliance
	* with the License. You may obtain a copy of the License at
	*
	* http://www.apache.org/licenses/LICENSE-2.0
	*
	* Unless required by applicable law or agreed to in writing,
	* software distributed under the License is distributed on an
	* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
	* KIND, either express or implied. See the License for the
	* specific language governing permissions and limitations
	* under the License.
	*/
	package org.netbeans.modules.db.dataview.output;

	import java.sql.Blob;
	import java.sql.Clob;
	import java.sql.SQLException;
	import java.sql.Types;
	import java.util.ArrayList;
	import java.util.HashSet;
	import java.util.List;
	import java.util.Map;
	import java.util.Set;
	import java.util.logging.Level;
	import java.util.logging.Logger;
	import javax.swing.SwingUtilities;
	import org.netbeans.modules.db.dataview.meta.DBColumn;
	import org.netbeans.modules.db.dataview.meta.DBException;
	import org.netbeans.modules.db.dataview.meta.DBMetaDataFactory;
	import org.netbeans.modules.db.dataview.meta.DBPrimaryKey;
	import org.netbeans.modules.db.dataview.meta.DBTable;
	import org.netbeans.modules.db.dataview.util.BinaryToStringConverter;
	import org.netbeans.modules.db.dataview.util.DataViewUtils;
	import org.openide.util.NbBundle;

	/**
	* Generates DML for editable resultset
	*
	* @author Ahimanikya Satapathy
	*/
	class SQLStatementGenerator {
	private static final Logger LOG =
	Logger.getLogger(SQLStatementGenerator.class.getName());

	String generateInsertStatement(DBTable table, Object[] insertedRow) throws DBException {
	List<DBColumn> columns = table.getColumnList();

	StringBuilder insertSql = new StringBuilder();
	insertSql.append("INSERT INTO "); // NOI18N

	StringBuilder colNames = new StringBuilder(" ("); // NOI18N
	StringBuilder values = new StringBuilder();
	String commaStr = ", "; // NOI18N
	boolean comma = false;
	for (int i = 0; i < insertedRow.length; i++) {
	DBColumn dbcol = columns.get(i);
	Object val = insertedRow[i];

	if (dbcol.isGenerated()) { // NOI18N
	continue;
	}

	if ((val == null \|\| val.equals("<NULL>")) && !dbcol.isNullable()) { // NOI18N
	throw new DBException(NbBundle.getMessage(SQLStatementGenerator.class, "MSG_nullable_check"));
	}

	if (comma) {
	values.append(commaStr);
	colNames.append(commaStr);
	} else {
	comma = true;
	}

	// Check for Constant e.g <NULL>, <DEFAULT>, <CURRENT_TIMESTAMP> etc
	if (val instanceof SQLConstant) {
	values.append(((SQLConstant) val).name());
	} else { // ELSE literals
	values.append(val == null ? " NULL " : "?"); // NOI18N
	}
	colNames.append(dbcol.getQualifiedName(true));
	}

	colNames.append(")"); // NOI18N
	insertSql.append(table.getFullyQualifiedName(true));
	insertSql.append(colNames.toString());
	insertSql.append(" Values("); // NOI18N
	insertSql.append(values.toString());
	insertSql.append(")"); // NOI18N

	return insertSql.toString();
	}

	String generateRawInsertStatement(DBTable table, Object[] insertedRow) throws DBException {
	List<DBColumn> columns = table.getColumnList();

	StringBuilder rawInsertSql = new StringBuilder();
	rawInsertSql.append("INSERT INTO "); // NOI18N

	String rawcolNames = " ("; // NOI18N
	String rawvalues = ""; // NOI18N
	String commaStr = ", "; // NOI18N
	boolean comma = false;
	for (int i = 0; i < insertedRow.length; i++) {
	DBColumn dbcol = columns.get(i);
	Object val = insertedRow[i];

	if (dbcol.isGenerated()) { // NOI18N
	continue;
	}

	if ((val == null \|\| val.equals("<NULL>")) && !dbcol.isNullable()) { // NOI18N
	throw new DBException(NbBundle.getMessage(SQLStatementGenerator.class, "MSG_nullable_check"));
	}

	if (comma) {
	rawvalues += commaStr;
	rawcolNames += commaStr;
	} else {
	comma = true;
	}

	// Check for Constant e.g <NULL>, <DEFAULT>, <CURRENT_TIMESTAMP> etc
	if (val instanceof SQLConstant) {
	rawvalues += (((SQLConstant) val).name());
	} else { // ELSE literals
	rawvalues += getQualifiedValue(dbcol.getJdbcType(), insertedRow[i]);
	}
	rawcolNames += dbcol.getQualifiedName(false);
	}

	rawcolNames += ")"; // NOI18N
	rawInsertSql.append(table.getFullyQualifiedName(false));
	rawInsertSql.append(rawcolNames);
	rawInsertSql.append(" \n\tVALUES ("); // NOI18N
	rawInsertSql.append(rawvalues);
	rawInsertSql.append(")"); // NOI18N

	return rawInsertSql.toString();
	}

	String generateUpdateStatement(DBTable table, int row, Map<Integer, Object> changedRow, List<Object> values, List<Integer> types, DataViewTableUIModel tblModel) throws DBException {
	List<DBColumn> columns = table.getColumnList();

	StringBuilder updateStmt = new StringBuilder();
	updateStmt.append("UPDATE ").append(table.getFullyQualifiedName(true)).append(" SET "); // NOI18N
	String commaStr = ", "; // NOI18N
	boolean comma = false;
	for (Integer col : changedRow.keySet()) {
	DBColumn dbcol = columns.get(col);
	Object value = changedRow.get(col);
	int type = dbcol.getJdbcType();

	if ((value == null \|\| value.equals("<NULL>")) && !dbcol.isNullable()) { // NOI18N
	throw new DBException(NbBundle.getMessage(SQLStatementGenerator.class, "MSG_nullable_check"));
	}

	if (comma) {
	updateStmt.append(commaStr);
	} else {
	comma = true;
	}

	updateStmt.append(dbcol.getQualifiedName(true));
	// Check for Constant e.g <NULL>, <DEFAULT>, <CURRENT_TIMESTAMP> etc
	if (value instanceof SQLConstant) {
	updateStmt.append(" = ").append(((SQLConstant) value).name());
	// NULL ist reported as an SQL constant, so treat it as such
	} else if ( value == null ) {
	updateStmt.append(" = NULL"); // NOI18N
	} else { // ELSE literals
	updateStmt.append(" = ?"); // NOI18N
	values.add(value);
	types.add(type);
	}
	}

	updateStmt.append(" WHERE "); // NOI18N
	generateWhereCondition(table, updateStmt, types, values, row, tblModel);
	return updateStmt.toString();
	}

	String generateUpdateStatement(DBTable table, int row, Map<Integer, Object> changedRow, DataViewTableUIModel tblModel) throws DBException {
	List<DBColumn> columns = table.getColumnList();

	StringBuilder rawUpdateStmt = new StringBuilder();
	rawUpdateStmt.append("UPDATE ").append(table.getFullyQualifiedName(false)).append(" SET "); // NOI18N

	String commaStr = ", "; // NOI18N
	boolean comma = false;
	for (Integer col : changedRow.keySet()) {
	DBColumn dbcol = columns.get(col);
	Object value = changedRow.get(col);
	int type = dbcol.getJdbcType();

	if ((value == null \|\| value.equals("<NULL>")) && !dbcol.isNullable()) { // NOI18N
	throw new DBException(NbBundle.getMessage(SQLStatementGenerator.class, "MSG_nullable_check"));
	}

	if (comma) {
	rawUpdateStmt.append(commaStr);
	} else {
	comma = true;
	}

	rawUpdateStmt.append(dbcol.getQualifiedName(true));
	// Check for Constant e.g <NULL>, <DEFAULT>, <CURRENT_TIMESTAMP> etc
	if (value instanceof SQLConstant) {
	rawUpdateStmt.append(" = ").append(((SQLConstant) value).name());
	} else { // ELSE literals
	rawUpdateStmt.append(" = ").append(getQualifiedValue(type, value).toString());
	}
	}

	rawUpdateStmt.append(" WHERE "); // NOI18N
	generateWhereCondition(table, rawUpdateStmt, row, tblModel);
	return rawUpdateStmt.toString();
	}

	String generateDeleteStatement(DBTable table, List<Integer> types, List<Object> values, int rowNum, DataViewTableUIModel tblModel) {
	StringBuilder deleteStmt = new StringBuilder();
	deleteStmt.append("DELETE FROM ").append(table.getFullyQualifiedName(true)).append(" WHERE "); // NOI18N

	generateWhereCondition(table, deleteStmt, types, values, rowNum, tblModel);
	return deleteStmt.toString();
	}

	String generateDeleteStatement(DBTable table, int rowNum, DataViewTableUIModel tblModel) {
	StringBuilder rawDeleteStmt = new StringBuilder();
	rawDeleteStmt.append("DELETE FROM ").append(table.getFullyQualifiedName(false)).append(" WHERE "); // NOI18N

	generateWhereCondition(table, rawDeleteStmt, rowNum, tblModel);
	return rawDeleteStmt.toString();
	}

	// TODO: Support for FK, and other constraint and Index recreation.
	String generateCreateStatement(DBTable table) throws DBException, Exception {
	boolean isdb2 = table.getParentObject().getDBType() == DBMetaDataFactory.DB2;

	StringBuilder sql = new StringBuilder();
	List<DBColumn> columns = table.getColumnList();
	sql.append("CREATE TABLE ").append(table.getQualifiedName(false)).append(" ("); // NOI18N
	int count = 0;
	for (DBColumn col : columns) {
	if (count++ > 0) {
	sql.append(", "); // NOI18N
	}

	String typeName = col.getTypeName();
	sql.append(col.getQualifiedName(false)).append(" ");

	int scale = col.getScale();
	int precision = col.getPrecision();
	if (precision > 0 && DataViewUtils.isPrecisionRequired(col.getJdbcType(), isdb2)) {
	if (typeName.contains("(")) { // Handle MySQL Binary Type // NOI18N
	sql.append(typeName.replace("(", "(" + precision)); // NOI18N
	} else {
	sql.append(typeName).append("(").append(precision); // NOI18N
	if (scale > 0 && DataViewUtils.isScaleRequired(col.getJdbcType())) {
	sql.append(", ").append(scale).append(")"); // NOI18N
	} else {
	sql.append(")"); // NOI18N
	}
	}
	} else {
	sql.append(typeName);
	}

	if (DataViewUtils.isBinary(col.getJdbcType()) && isdb2) {
	sql.append(" FOR BIT DATA "); // NOI18N
	}

	if (col.hasDefault()) {
	sql.append(" DEFAULT ").append(col.getDefaultValue()).append(" "); // NOI18N
	}

	if (!col.isNullable()) {
	sql.append(" NOT NULL"); // NOI18N
	}

	if (col.isGenerated()) {
	sql.append(" ").append(getAutoIncrementText(table.getParentObject().getDBType()));
	}
	}

	DBPrimaryKey pk = table.getPrimaryKey();
	if (pk != null) {
	count = 0;
	sql.append(", PRIMARY KEY ("); // NOI18N
	for (String col : pk.getColumnNames()) {
	if (count++ > 0) {
	sql.append(", "); // NOI18N
	}
	sql.append(table.getQuoter().quoteIfNeeded(col));
	}
	sql.append(")"); // NOI18N
	}
	sql.append(")"); // NOI18N

	return sql.toString();
	}

	private boolean addSeparator(boolean and, StringBuilder sql, String sep) {
	if (and) {
	sql.append(sep);
	return true;
	} else {
	return true;
	}
	}

	private void generateNameValue(DBColumn column, StringBuilder sql, Object value, List<Object> values, List<Integer> types) {
	sql.append(column.getQualifiedName(true));
	if (value != null) {
	values.add(value);
	types.add(column.getJdbcType());
	sql.append(" = ? "); // NOI18N
	} else { // Handle NULL value in where condition
	sql.append(" IS NULL "); // NOI18N
	}
	}

	private void generateNameValue(DBColumn column, StringBuilder sql, Object value) {
	String columnName = column.getQualifiedName(false);
	int type = column.getJdbcType();

	sql.append(columnName);
	if (value != null) {
	sql.append(" = ").append(getQualifiedValue(type, value)); // NOI18N
	} else { // Handle NULL value in where condition
	sql.append(" IS NULL"); // NOI18N
	}
	}

	void generateWhereCondition(DBTable table, StringBuilder result, List<Integer> types, List<Object> values, int rowNum, DataViewTableUIModel model) {
	assert SwingUtilities.isEventDispatchThread() : "Needs to be called on the EDT";

	DBPrimaryKey key = table.getPrimaryKey();
	Set<String> columnsSelected = new HashSet<>();
	boolean and = false;

	List<DBColumn> columns = table.getColumnList();

	StringBuilder pkSelect = new StringBuilder();
	List<Integer> pkTypes = new ArrayList<>();
	List<Object> pkObject = new ArrayList<>();

	if (key != null) {
	for (String keyName : key.getColumnNames()) {
	for (int i = 0; i < model.getColumnCount(); i++) {
	DBColumn dbcol = columns.get(i);
	String columnName = dbcol.getName();
	if (columnName.equals(keyName)) {
	Object val = model.getOriginalValueAt(rowNum, i);
	if (val != null) {
	columnsSelected.add(columnName);
	and = addSeparator(and, pkSelect, " AND "); // NOI18N
	generateNameValue(dbcol, pkSelect, val, pkObject, pkTypes);
	break;
	}
	}
	}
	}
	}

	if (key != null && columnsSelected.equals(new HashSet<>(key.getColumnNames()))) {
	result.append(pkSelect);
	types.addAll(pkTypes);
	values.addAll(pkObject);
	} else {
	and = false;
	for (int i = 0; i < model.getColumnCount(); i++) {
	DBColumn dbcol = columns.get(i);
	Object val = model.getOriginalValueAt(rowNum, i);
	and = addSeparator(and, result, " AND "); // NOI18N
	generateNameValue(dbcol, result, val, values, types);
	}
	}
	}

	void generateWhereCondition(DBTable table, StringBuilder sql, int rowNum, DataViewTableUIModel model) {
	assert SwingUtilities.isEventDispatchThread() : "Needs to be called on the EDT";

	DBPrimaryKey key = table.getPrimaryKey();
	Set<String> columnsSelected = new HashSet<>();
	boolean and = false;

	List<DBColumn> columns = table.getColumnList();

	StringBuilder pkSelect = new StringBuilder();

	if (key != null) {
	for (String keyName : key.getColumnNames()) {
	for (int i = 0; i < model.getColumnCount(); i++) {
	DBColumn dbcol = columns.get(i);
	String columnName = dbcol.getName();
	if (columnName.equals(keyName)) {
	Object val = model.getOriginalValueAt(rowNum, i);
	if (val != null) {
	columnsSelected.add(columnName);
	and = addSeparator(and, pkSelect, " AND "); // NOI18N
	generateNameValue(dbcol, pkSelect, val);
	break;
	}
	}
	}
	}
	}

	if (key != null && columnsSelected.equals(new HashSet<>(key.getColumnNames()))) {
	sql.append(pkSelect);
	} else {
	and = false;
	for (int i = 0; i < model.getColumnCount(); i++) {
	DBColumn dbcol = columns.get(i);
	Object val = model.getOriginalValueAt(rowNum, i);
	and = addSeparator(and, sql, " AND "); // NOI18N
	generateNameValue(dbcol, sql, val);
	}
	}
	}

	private Object getQualifiedValue(int type, Object val) {
	if (val == null) {
	return "NULL"; // NOI18N
	}
	if (type == Types.BIT && !(val instanceof Boolean)) {
	return "b'" + val + "'"; // NOI18N
	} else if (DataViewUtils.isNumeric(type)) {
	return val;
	} else if (val instanceof Clob) {
	try {
	Clob lob = (Clob) val;
	String result = lob.getSubString(1, (int) lob.length());
	return "'" + result.replace("'", "''") + "'"; //NOI18N
	} catch (SQLException ex) {
	LOG.log(Level.INFO, "Failed to read CLOB", ex); //NOI18N
	}
	} else if (val instanceof Blob) {
	try {
	Blob lob = (Blob) val;
	byte[] result = lob.getBytes(1, (int) lob.length());
	return "x'" + BinaryToStringConverter.convertToString(
	result, 16, false) + "'"; // NOI18N
	} catch (SQLException ex) {
	LOG.log(Level.INFO, "Failed to read BLOB", ex); //NOI18N
	}
	}
	// Fallback if previous converts fail
	return "'" + val.toString().replace("'", "''") + "'"; //NOI18N
	}

	private String getAutoIncrementText(int dbType) throws Exception {
	switch (dbType) {
	case DBMetaDataFactory.MYSQL:
	return "AUTO_INCREMENT"; // NOI18N

	case DBMetaDataFactory.PostgreSQL:
	return "SERIAL"; // NOI18N

	case DBMetaDataFactory.SQLSERVER:
	return "IDENTITY"; // NOI18N
	default:
	return "GENERATED ALWAYS AS IDENTITY"; // NOI18N
	}
	}
	}