Attachment #132731 for bug #227588

This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.

View | Details | Raw Unified | Return to bug 227588
Collapse All | Expand All

Lines 102-108 Link Here

(-)a/db.dataview/src/org/netbeans/modules/db/dataview/output/DataView.java (-2 / +2 lines)
102	dv.dataPage = new DataViewPageContext(pageSize);	102	dv.dataPage = new DataViewPageContext(pageSize);
103	dv.execHelper = new SQLExecutionHelper(dv);	103	dv.execHelper = new SQLExecutionHelper(dv);
104	dv.execHelper.initialDataLoad();	104	dv.execHelper.initialDataLoad();
105	dv.stmtGenerator = new SQLStatementGenerator(dv);	105	dv.stmtGenerator = new SQLStatementGenerator();
106	} catch (Exception ex) {	106	} catch (Exception ex) {
107	dv.setErrorStatusText(ex);	107	dv.setErrorStatusText(ex);
108	}	108	}
Lines 240-246 Link Here
240		240
241	SQLStatementGenerator getSQLStatementGenerator() {	241	SQLStatementGenerator getSQLStatementGenerator() {
242	if (stmtGenerator == null) {	242	if (stmtGenerator == null) {
243	stmtGenerator = new SQLStatementGenerator(this);	243	stmtGenerator = new SQLStatementGenerator();
244	}	244	}
245	return stmtGenerator;	245	return stmtGenerator;
246	}	246	}

Lines 440-446 Link Here

(-)a/db.dataview/src/org/netbeans/modules/db/dataview/output/DataViewTableUI.java (-4 / +11 lines)
440	for (int j = 0; j < rows.length; j++) {	440	for (int j = 0; j < rows.length; j++) {
441	int modelIndex = convertRowIndexToModel(rows[j]);	441	int modelIndex = convertRowIndexToModel(rows[j]);
442	Object[] insertRow = getModel().getRowData(modelIndex);	442	Object[] insertRow = getModel().getRowData(modelIndex);
443	String sql = dataView.getSQLStatementGenerator().generateRawInsertStatement(insertRow);	443	// @todo make table configurable
		444	DBTable table = dataView.getDataViewDBTable().getTable(0);
		445	String sql = dataView.getSQLStatementGenerator()
		446	.generateRawInsertStatement(table, insertRow);
444	insertSQL += sql + ";\n"; // NOI18N	447	insertSQL += sql + ";\n"; // NOI18N
445	}	448	}
446	ShowSQLDialog dialog = new ShowSQLDialog();	449	ShowSQLDialog dialog = new ShowSQLDialog();
Lines 465-471 Link Here
465	for (int j = 0; j < rows.length; j++) {	468	for (int j = 0; j < rows.length; j++) {
466	SQLStatementGenerator generator = dataView.getSQLStatementGenerator();	469	SQLStatementGenerator generator = dataView.getSQLStatementGenerator();
467	int modelIndex = convertRowIndexToModel(rows[j]);	470	int modelIndex = convertRowIndexToModel(rows[j]);
468	final String deleteStmt = generator.generateDeleteStatement(modelIndex, getModel());	471	// @todo make table configurable
		472	DBTable table = dataView.getDataViewDBTable().getTable(0);
		473	final String deleteStmt = generator.generateDeleteStatement(table, modelIndex, getModel());
469	rawDeleteStmt += deleteStmt + ";\n"; // NOI18N	474	rawDeleteStmt += deleteStmt + ";\n"; // NOI18N
470	}	475	}
471	ShowSQLDialog dialog = new ShowSQLDialog();	476	ShowSQLDialog dialog = new ShowSQLDialog();
Lines 483-493 Link Here
483	public void actionPerformed(ActionEvent e) {	488	public void actionPerformed(ActionEvent e) {
484	String rawUpdateStmt = "";	489	String rawUpdateStmt = "";
485	SQLStatementGenerator generator = dataView.getSQLStatementGenerator();	490	SQLStatementGenerator generator = dataView.getSQLStatementGenerator();
486		491	// @todo make table configurable
		492	DBTable table = dataView.getDataViewDBTable().getTable(0);
		493
487	try {	494	try {
488	for (Integer row : getModel().getUpdateKeys()) {	495	for (Integer row : getModel().getUpdateKeys()) {
489	Map<Integer, Object> changedData = getModel().getChangedData(row);	496	Map<Integer, Object> changedData = getModel().getChangedData(row);
490	rawUpdateStmt += generator.generateUpdateStatement(row, changedData, getModel()) + ";\n"; // NOI18N	497	rawUpdateStmt += generator.generateUpdateStatement(table, row, changedData, getModel()) + ";\n"; // NOI18N
491	}	498	}
492	ShowSQLDialog dialog = new ShowSQLDialog();	499	ShowSQLDialog dialog = new ShowSQLDialog();
493	dialog.setLocationRelativeTo(WindowManager.getDefault().getMainWindow());	500	dialog.setLocationRelativeTo(WindowManager.getDefault().getMainWindow());




    <Property name="background" type="java.awt.Color" editor="org.netbeans.beaninfo.editors.ColorEditor">
      <Color blue="ff" green="ff" id="white" palette="1" red="ff" type="palette"/>
    </Property>
    <Property name="font" type="java.awt.Font" editor="org.netbeans.beaninfo.editors.FontEditor">
      <Font name="Dialog" size="12" style="0"/>
    </Property>
    <Property name="foreground" type="java.awt.Color" editor="org.netbeans.beaninfo.editors.ColorEditor">
      <Color blue="0" green="0" id="black" palette="1" red="0" type="palette"/>
    </Property>
    <Property name="locationByPlatform" type="boolean" value="true"/>
    <Property name="modal" type="boolean" value="true"/>
  </Properties>

Lines 322-333 Link Here

(-)a/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLExecutionHelper.java (-2 / +10 lines)
322	}	322	}
323		323
324	void executeDeleteRow(final DataViewTableUI rsTable) {	324	void executeDeleteRow(final DataViewTableUI rsTable) {
		325	// @todo enhance to be able to work with more than one table
		326	assert dataView.getDataViewDBTable().getTableCount() == 1 : "Deletes only supported for single table in resultset";
		327
325	String title = NbBundle.getMessage(SQLExecutionHelper.class, "LBL_sql_delete");	328	String title = NbBundle.getMessage(SQLExecutionHelper.class, "LBL_sql_delete");
326	final int[] rows = rsTable.getSelectedRows();	329	final int[] rows = rsTable.getSelectedRows();
327	for(int i = 0; i < rows.length; i++) {	330	for(int i = 0; i < rows.length; i++) {
328	rows[i] = rsTable.convertRowIndexToModel(rows[i]);	331	rows[i] = rsTable.convertRowIndexToModel(rows[i]);
329	}	332	}
330	Arrays.sort(rows);	333	Arrays.sort(rows);
		334	final DBTable table = dataView.getDataViewDBTable().getTable(0);
331	SQLStatementExecutor executor = new SQLStatementExecutor(dataView, title, "") {	335	SQLStatementExecutor executor = new SQLStatementExecutor(dataView, title, "") {
332		336
333	@Override	337	@Override
Lines 346-352 Link Here
346	final List<Integer> types = new ArrayList<Integer>();	350	final List<Integer> types = new ArrayList<Integer>();
347		351
348	SQLStatementGenerator generator = dataView.getSQLStatementGenerator();	352	SQLStatementGenerator generator = dataView.getSQLStatementGenerator();
349	final String deleteStmt = generator.generateDeleteStatement(types, values, rowNum, tblModel);	353	final String deleteStmt = generator.generateDeleteStatement(table, types, values, rowNum, tblModel);
350	PreparedStatement pstmt = conn.prepareStatement(deleteStmt);	354	PreparedStatement pstmt = conn.prepareStatement(deleteStmt);
351	try {	355	try {
352	int pos = 1;	356	int pos = 1;
Lines 388-393 Link Here
388	}	392	}
389		393
390	void executeUpdateRow(final DataViewTableUI rsTable, final boolean selectedOnly) {	394	void executeUpdateRow(final DataViewTableUI rsTable, final boolean selectedOnly) {
		395	// @todo enhance to be able to work with more than one table
		396	assert dataView.getDataViewDBTable().getTableCount() == 1 : "Updates only supported for single table in resultset";
		397
		398	final DBTable table = dataView.getDataViewDBTable().getTable(0);
391	final DataViewTableUIModel dataViewTableUIModel = rsTable.getModel();	399	final DataViewTableUIModel dataViewTableUIModel = rsTable.getModel();
392	String title = NbBundle.getMessage(SQLExecutionHelper.class, "LBL_sql_update");	400	String title = NbBundle.getMessage(SQLExecutionHelper.class, "LBL_sql_update");
393	SQLStatementExecutor executor = new SQLStatementExecutor(dataView, title, "") {	401	SQLStatementExecutor executor = new SQLStatementExecutor(dataView, title, "") {
Lines 432-438 Link Here
432		440
433	List<Object> values = new ArrayList<Object>();	441	List<Object> values = new ArrayList<Object>();
434	List<Integer> types = new ArrayList<Integer>();	442	List<Integer> types = new ArrayList<Integer>();
435	String updateStmt = generator.generateUpdateStatement(key, dataViewTableUIModel.getChangedData(key), values, types, rsTable.getModel());	443	String updateStmt = generator.generateUpdateStatement(table, key, dataViewTableUIModel.getChangedData(key), values, types, rsTable.getModel());
436		444
437	pstmt = conn.prepareStatement(updateStmt);	445	pstmt = conn.prepareStatement(updateStmt);
438	int pos = 1;	446	int pos = 1;




 * The Original Software is NetBeans. The Initial Developer of the Original
 * Software is Sun Microsystems, Inc. Portions Copyright 1997-2007 Sun
 * Microsystems, Inc. All Rights Reserved.
 *a
 * If you wish your version of this file to be governed by only the CDDL
 * or only the GPL Version 2, indicate your decision by adding
 * "[Contributor] elects to include this software in this distribution

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
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 org.netbeans.modules.db.dataview.meta.DBColumn;

class SQLStatementGenerator {
    private static final Logger LOG =
            Logger.getLogger(SQLStatementGenerator.class.getName());
    
    private DataView dataView;

    public SQLStatementGenerator(DataView dataView) {
        this.dataView = dataView;
        this.tblMeta = dataView.getDataViewDBTable();
    }

    // TODO: Generated by default, can be overwitten by user, allow that
    String generateInsertStatement(DBTable table, Object[] insertedRow) throws DBException {
        List<DBColumn> columns = table.getColumnList();
        
        StringBuilder insertSql = new StringBuilder();
        insertSql.append("INSERT INTO "); // 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

        }

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

        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 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

        }

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

        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();


                comma = true;
            }

            updateStmt.append(dbcol.getQualifiedName(true));
            // Check for Constant e.g <NULL>, <DEFAULT>, <CURRENT_TIMESTAMP> etc
            if (value != null && DataViewUtils.isSQLConstantString(value, dbcol)) {
                String constStr = ((String) value).substring(1, ((String) value).length() - 1);

        }

        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();


                comma = true;
            }

            rawUpdateStmt.append(dbcol.getQualifiedName(true));
            // Check for Constant e.g <NULL>, <DEFAULT>, <CURRENT_TIMESTAMP> etc
            if (value != null && DataViewUtils.isSQLConstantString(value, dbcol)) {
                String constStr = ((String) value).substring(1, ((String) value).length() - 1);

        }

        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 {

        Connection conn = DBConnectionFactory.getInstance().getConnection(dataView.getDatabaseConnection());
        String msg = "";
        if (conn == null) {
            Throwable ex = DBConnectionFactory.getInstance().getLastException();
            if (ex != null) {
                msg = ex.getMessage();
            } else {
                msg = NbBundle.getMessage(SQLExecutionHelper.class, "MSG_connection_failure", dataView.getDatabaseConnection());
            }
            dataView.setErrorStatusText(new DBException(msg));
            throw new DBException(msg);
        }

        boolean isdb2 = table.getParentObject().getDBType() == DBMetaDataFactory.DB2 ? true : false;

        StringBuilder sql = new StringBuilder();

        return sql.toString();
    }

    static String getCountSQLQuery(String queryString) {
        // User may type "FROM" in either lower, upper or mixed case
        String[] splitByFrom = queryString.toUpperCase().split("FROM"); // NOI18N
        queryString = queryString.substring(splitByFrom[0].length());

        String[] splitByOrderBy = queryString.toUpperCase().split("ORDER BY"); // NOI18N
        queryString = queryString.substring(0, splitByOrderBy[0].length());
        return "SELECT COUNT(*) " + queryString; // NOI18N
    }

    static String getCountAsSubQuery(String queryString) {
        String[] splitByOrderBy = queryString.toUpperCase().split("ORDER BY"); // NOI18N
        queryString = queryString.substring(0, splitByOrderBy[0].length());
        return "SELECT COUNT(*) FROM (" + queryString + ") C2668"; // NOI18N
    }

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

        }
    }

    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) {

        }
    }

    private void generateWhereCondition(DBTable table, StringBuilder result, List<Integer> types, List<Object> values, int rowNum, DataViewTableUIModel model) {
        DBPrimaryKey key = table.getPrimaryKey();
        boolean keySelected = false;
        boolean and = false;

        List<DBColumn> columns = table.getColumnList();
        
        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) {
                            keySelected = true;
                            and = addSeparator(and, result, " AND "); // NOI18N
                            generateNameValue(dbcol, result, val, values, types);
                            break;
                        }
                    }


        if (key == null || !keySelected) {
            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);
            }
        }
    }

    private void generateWhereCondition(DBTable table, StringBuilder sql, int rowNum, DataViewTableUIModel model) {
        DBPrimaryKey key = table.getPrimaryKey();
        boolean keySelected = false;
        boolean and = false;
        
        List<DBColumn> columns = table.getColumnList();

        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) {
                            keySelected = true;
                            and = addSeparator(and, sql, " AND "); // NOI18N
                            generateNameValue(dbcol, sql, val);
                            break;
                        }
                    }


        if (key == null || !keySelected) {
            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);
            }
        }
    }




        assertNotNull(instance);
        assertNotNull(result);
    }

    /**
     * Test of getSQLStatementGenerator method, of class DataView.
     */
    public void testGetSQLStatementGenerator() {
        String sqlStr = "select * from simpletable";
        int pageSize = 5;
        DataView instance = DataView.create(dbconn, sqlStr, pageSize);
        SQLStatementGenerator expResult = new SQLStatementGenerator(instance);
        SQLStatementGenerator result = instance.getSQLStatementGenerator();
        assertEquals(SQLStatementGenerator.getCountSQLQuery(sqlStr), SQLStatementGenerator.getCountSQLQuery(sqlStr));
    }
}

Return to bug 227588

Lines 206-221 Link Here

(-)a/db.dataview/test/unit/src/org/netbeans/modules/db/dataview/output/DataViewTest.java (-12 lines)
206	assertNotNull(instance);	206	assertNotNull(instance);
207	assertNotNull(result);	207	assertNotNull(result);
208	}	208	}
209
210	/**
211	* Test of getSQLStatementGenerator method, of class DataView.
212	*/
213	public void testGetSQLStatementGenerator() {
214	String sqlStr = "select * from simpletable";
215	int pageSize = 5;
216	DataView instance = DataView.create(dbconn, sqlStr, pageSize);
217	SQLStatementGenerator expResult = new SQLStatementGenerator(instance);
218	SQLStatementGenerator result = instance.getSQLStatementGenerator();
219	assertEquals(SQLStatementGenerator.getCountSQLQuery(sqlStr), SQLStatementGenerator.getCountSQLQuery(sqlStr));
220	}
221	}	209	}

Lines 9-20 Link Here

(-)a/db.dataview/src/org/netbeans/modules/db/dataview/output/InsertRecordDialog.form (-6 lines)
9	<Property name="background" type="java.awt.Color" editor="org.netbeans.beaninfo.editors.ColorEditor">	9	<Property name="background" type="java.awt.Color" editor="org.netbeans.beaninfo.editors.ColorEditor">
10	<Color blue="ff" green="ff" id="white" palette="1" red="ff" type="palette"/>	10	<Color blue="ff" green="ff" id="white" palette="1" red="ff" type="palette"/>
11	</Property>	11	</Property>
12	<Property name="font" type="java.awt.Font" editor="org.netbeans.beaninfo.editors.FontEditor">
13	<Font name="Dialog" size="12" style="0"/>
14	</Property>
15	<Property name="foreground" type="java.awt.Color" editor="org.netbeans.beaninfo.editors.ColorEditor">
16	<Color blue="0" green="0" id="black" palette="1" red="0" type="palette"/>
17	</Property>
18	<Property name="locationByPlatform" type="boolean" value="true"/>	12	<Property name="locationByPlatform" type="boolean" value="true"/>
19	<Property name="modal" type="boolean" value="true"/>	13	<Property name="modal" type="boolean" value="true"/>
20	</Properties>	14	</Properties>

Lines 80-85 Link Here

(-)a/db.dataview/src/org/netbeans/modules/db/dataview/output/InsertRecordDialog.java (-3 / +8 lines)
80	import org.netbeans.modules.db.dataview.meta.DBException;	80	import org.netbeans.modules.db.dataview.meta.DBException;
81	import org.openide.text.CloneableEditorSupport;	81	import org.openide.text.CloneableEditorSupport;
82	import org.netbeans.modules.db.dataview.meta.DBColumn;	82	import org.netbeans.modules.db.dataview.meta.DBColumn;
		83	import org.netbeans.modules.db.dataview.meta.DBTable;
83	import org.netbeans.modules.db.dataview.table.ResultSetTableModel;	84	import org.netbeans.modules.db.dataview.table.ResultSetTableModel;
84	import org.netbeans.modules.db.dataview.util.DBReadWriteHelper;	85	import org.netbeans.modules.db.dataview.util.DBReadWriteHelper;
85	import org.netbeans.modules.db.dataview.util.DataViewUtils;	86	import org.netbeans.modules.db.dataview.util.DataViewUtils;
Lines 98-103 Link Here
98	*/	99	*/
99	class InsertRecordDialog extends javax.swing.JDialog {	100	class InsertRecordDialog extends javax.swing.JDialog {
100	private final ResultSetTableModel insertDataModel;	101	private final ResultSetTableModel insertDataModel;
		102	private final DBTable insertTable;
101	private final DataView dataView;	103	private final DataView dataView;
102	InsertRecordTableUI insertRecordTableUI;	104	InsertRecordTableUI insertRecordTableUI;
103	private JXTableRowHeader rowHeader;	105	private JXTableRowHeader rowHeader;
Lines 106-113 Link Here
106	super(WindowManager.getDefault().getMainWindow(), true);	108	super(WindowManager.getDefault().getMainWindow(), true);
107	this.dataView = dataView;	109	this.dataView = dataView;
108		110
		111	// @todo Don't directly choose first table for insert, but let it be passed in
		112	insertTable = dataView.getDataViewDBTable().getTable(0);
		113
109	insertDataModel = new ResultSetTableModel(	114	insertDataModel = new ResultSetTableModel(
110	dataView.getDataViewDBTable().getColumns().toArray(new DBColumn[0])	115	insertTable.getColumnList().toArray(new DBColumn[0])
111	);	116	);
112	insertDataModel.setEditable(true);	117	insertDataModel.setEditable(true);
113		118
Lines 384-390 Link Here
384	boolean wasException = false;	389	boolean wasException = false;
385	try {	390	try {
386	Object[] insertedRow = getInsertValues(i);	391	Object[] insertedRow = getInsertValues(i);
387	insertSQL = stmtBldr.generateInsertStatement(insertedRow);	392	insertSQL = stmtBldr.generateInsertStatement(insertTable, insertedRow);
388	RequestProcessor.Task task = execHelper.executeInsertRow(insertSQL, insertedRow);	393	RequestProcessor.Task task = execHelper.executeInsertRow(insertSQL, insertedRow);
389	task.waitFinished();	394	task.waitFinished();
390	wasException = dataView.hasExceptions();	395	wasException = dataView.hasExceptions();
Lines 426-432 Link Here
426	if (jSplitPane1.getBottomComponent() != null) {	431	if (jSplitPane1.getBottomComponent() != null) {
427	SQLStatementGenerator stmtBldr = dataView.getSQLStatementGenerator();	432	SQLStatementGenerator stmtBldr = dataView.getSQLStatementGenerator();
428	for (int i = 0; i < insertRecordTableUI.getRowCount(); i++) {	433	for (int i = 0; i < insertRecordTableUI.getRowCount(); i++) {
429	String sql = stmtBldr.generateRawInsertStatement(getInsertValues(i));	434	String sql = stmtBldr.generateRawInsertStatement(insertTable, getInsertValues(i));
430	sqlText = sqlText + sql + "\n";	435	sqlText = sqlText + sql + "\n";
431	}	436	}
432	jEditorPane1.setEditorKit(CloneableEditorSupport.getEditorKit("text/x-sql")); // NOI18N	437	jEditorPane1.setEditorKit(CloneableEditorSupport.getEditorKit("text/x-sql")); // NOI18N

Lines 29-35 Link Here

(-)a/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLStatementGenerator.java (-77 / +58 lines)
29	* The Original Software is NetBeans. The Initial Developer of the Original	29	* The Original Software is NetBeans. The Initial Developer of the Original
30	* Software is Sun Microsystems, Inc. Portions Copyright 1997-2007 Sun	30	* Software is Sun Microsystems, Inc. Portions Copyright 1997-2007 Sun
31	* Microsystems, Inc. All Rights Reserved.	31	* Microsystems, Inc. All Rights Reserved.
32	*	32	*a
33	* If you wish your version of this file to be governed by only the CDDL	33	* If you wish your version of this file to be governed by only the CDDL
34	* or only the GPL Version 2, indicate your decision by adding	34	* or only the GPL Version 2, indicate your decision by adding
35	* "[Contributor] elects to include this software in this distribution	35	* "[Contributor] elects to include this software in this distribution
Lines 48-55 Link Here
48	import java.sql.Connection;	48	import java.sql.Connection;
49	import java.sql.SQLException;	49	import java.sql.SQLException;
50	import java.sql.Types;	50	import java.sql.Types;
		51	import java.util.HashSet;
51	import java.util.List;	52	import java.util.List;
52	import java.util.Map;	53	import java.util.Map;
		54	import java.util.Set;
53	import java.util.logging.Level;	55	import java.util.logging.Level;
54	import java.util.logging.Logger;	56	import java.util.logging.Logger;
55	import org.netbeans.modules.db.dataview.meta.DBColumn;	57	import org.netbeans.modules.db.dataview.meta.DBColumn;
Lines 70-85 Link Here
70	class SQLStatementGenerator {	72	class SQLStatementGenerator {
71	private static final Logger LOG =	73	private static final Logger LOG =
72	Logger.getLogger(SQLStatementGenerator.class.getName());	74	Logger.getLogger(SQLStatementGenerator.class.getName());
73	private DataViewDBTable tblMeta;	75
74	private DataView dataView;
75
76	public SQLStatementGenerator(DataView dataView) {
77	this.dataView = dataView;
78	this.tblMeta = dataView.getDataViewDBTable();
79	}
80
81	// TODO: Generated by default, can be overwitten by user, allow that	76	// TODO: Generated by default, can be overwitten by user, allow that
82	String generateInsertStatement(Object[] insertedRow) throws DBException {	77	String generateInsertStatement(DBTable table, Object[] insertedRow) throws DBException {
		78	List<DBColumn> columns = table.getColumnList();
		79
83	StringBuilder insertSql = new StringBuilder();	80	StringBuilder insertSql = new StringBuilder();
84	insertSql.append("INSERT INTO "); // NOI18N	81	insertSql.append("INSERT INTO "); // NOI18N
85		82
Lines 88-94 Link Here
88	String commaStr = ", "; // NOI18N	85	String commaStr = ", "; // NOI18N
89	boolean comma = false;	86	boolean comma = false;
90	for (int i = 0; i < insertedRow.length; i++) {	87	for (int i = 0; i < insertedRow.length; i++) {
91	DBColumn dbcol = tblMeta.getColumn(i);	88	DBColumn dbcol = columns.get(i);
92	Object val = insertedRow[i];	89	Object val = insertedRow[i];
93		90
94	if (dbcol.isGenerated()) { // NOI18N	91	if (dbcol.isGenerated()) { // NOI18N
Lines 117-123 Link Here
117	}	114	}
118		115
119	colNames.append(")"); // NOI18N	116	colNames.append(")"); // NOI18N
120	insertSql.append(tblMeta.getFullyQualifiedName(0, true));	117	insertSql.append(table.getFullyQualifiedName(true));
121	insertSql.append(colNames.toString());	118	insertSql.append(colNames.toString());
122	insertSql.append(" Values("); // NOI18N	119	insertSql.append(" Values("); // NOI18N
123	insertSql.append(values.toString());	120	insertSql.append(values.toString());
Lines 126-132 Link Here
126	return insertSql.toString();	123	return insertSql.toString();
127	}	124	}
128		125
129	String generateRawInsertStatement(Object[] insertedRow) throws DBException {	126	String generateRawInsertStatement(DBTable table, Object[] insertedRow) throws DBException {
		127	List<DBColumn> columns = table.getColumnList();
		128
130	StringBuilder rawInsertSql = new StringBuilder();	129	StringBuilder rawInsertSql = new StringBuilder();
131	rawInsertSql.append("INSERT INTO "); // NOI18N	130	rawInsertSql.append("INSERT INTO "); // NOI18N
132		131
Lines 135-141 Link Here
135	String commaStr = ", "; // NOI18N	134	String commaStr = ", "; // NOI18N
136	boolean comma = false;	135	boolean comma = false;
137	for (int i = 0; i < insertedRow.length; i++) {	136	for (int i = 0; i < insertedRow.length; i++) {
138	DBColumn dbcol = tblMeta.getColumn(i);	137	DBColumn dbcol = columns.get(i);
139	Object val = insertedRow[i];	138	Object val = insertedRow[i];
140		139
141	if (dbcol.isGenerated()) { // NOI18N	140	if (dbcol.isGenerated()) { // NOI18N
Lines 164-170 Link Here
164	}	163	}
165		164
166	rawcolNames += ")"; // NOI18N	165	rawcolNames += ")"; // NOI18N
167	rawInsertSql.append(tblMeta.getFullyQualifiedName(0, false));	166	rawInsertSql.append(table.getFullyQualifiedName(false));
168	rawInsertSql.append(rawcolNames);	167	rawInsertSql.append(rawcolNames);
169	rawInsertSql.append(" \n\tVALUES ("); // NOI18N	168	rawInsertSql.append(" \n\tVALUES ("); // NOI18N
170	rawInsertSql.append(rawvalues);	169	rawInsertSql.append(rawvalues);
Lines 173-185 Link Here
173	return rawInsertSql.toString();	172	return rawInsertSql.toString();
174	}	173	}
175		174
176	String generateUpdateStatement(int row, Map<Integer, Object> changedRow, List<Object> values, List<Integer> types, DataViewTableUIModel tblModel) throws DBException {	175	String generateUpdateStatement(DBTable table, int row, Map<Integer, Object> changedRow, List<Object> values, List<Integer> types, DataViewTableUIModel tblModel) throws DBException {
		176	List<DBColumn> columns = table.getColumnList();
		177
177	StringBuilder updateStmt = new StringBuilder();	178	StringBuilder updateStmt = new StringBuilder();
178	updateStmt.append("UPDATE ").append(tblMeta.getFullyQualifiedName(0, true)).append(" SET "); // NOI18N	179	updateStmt.append("UPDATE ").append(table.getFullyQualifiedName(true)).append(" SET "); // NOI18N
179	String commaStr = ", "; // NOI18N	180	String commaStr = ", "; // NOI18N
180	boolean comma = false;	181	boolean comma = false;
181	for (Integer col : changedRow.keySet()) {	182	for (Integer col : changedRow.keySet()) {
182	DBColumn dbcol = tblMeta.getColumn(col);	183	DBColumn dbcol = columns.get(col);
183	Object value = changedRow.get(col);	184	Object value = changedRow.get(col);
184	int type = dbcol.getJdbcType();	185	int type = dbcol.getJdbcType();
185		186
Lines 193-199 Link Here
193	comma = true;	194	comma = true;
194	}	195	}
195		196
196	updateStmt.append(tblMeta.getQualifiedName(col, true));	197	updateStmt.append(dbcol.getQualifiedName(true));
197	// Check for Constant e.g <NULL>, <DEFAULT>, <CURRENT_TIMESTAMP> etc	198	// Check for Constant e.g <NULL>, <DEFAULT>, <CURRENT_TIMESTAMP> etc
198	if (value != null && DataViewUtils.isSQLConstantString(value, dbcol)) {	199	if (value != null && DataViewUtils.isSQLConstantString(value, dbcol)) {
199	String constStr = ((String) value).substring(1, ((String) value).length() - 1);	200	String constStr = ((String) value).substring(1, ((String) value).length() - 1);
Lines 209-226 Link Here
209	}	210	}
210		211
211	updateStmt.append(" WHERE "); // NOI18N	212	updateStmt.append(" WHERE "); // NOI18N
212	generateWhereCondition(updateStmt, types, values, row, tblModel);	213	generateWhereCondition(table, updateStmt, types, values, row, tblModel);
213	return updateStmt.toString();	214	return updateStmt.toString();
214	}	215	}
215		216
216	String generateUpdateStatement(int row, Map<Integer, Object> changedRow, DataViewTableUIModel tblModel) throws DBException {	217	String generateUpdateStatement(DBTable table, int row, Map<Integer, Object> changedRow, DataViewTableUIModel tblModel) throws DBException {
		218	List<DBColumn> columns = table.getColumnList();
		219
217	StringBuilder rawUpdateStmt = new StringBuilder();	220	StringBuilder rawUpdateStmt = new StringBuilder();
218	rawUpdateStmt.append("UPDATE ").append(tblMeta.getFullyQualifiedName(0, false)).append(" SET "); // NOI18N	221	rawUpdateStmt.append("UPDATE ").append(table.getFullyQualifiedName(false)).append(" SET "); // NOI18N
219		222
220	String commaStr = ", "; // NOI18N	223	String commaStr = ", "; // NOI18N
221	boolean comma = false;	224	boolean comma = false;
222	for (Integer col : changedRow.keySet()) {	225	for (Integer col : changedRow.keySet()) {
223	DBColumn dbcol = tblMeta.getColumn(col);	226	DBColumn dbcol = columns.get(col);
224	Object value = changedRow.get(col);	227	Object value = changedRow.get(col);
225	int type = dbcol.getJdbcType();	228	int type = dbcol.getJdbcType();
226		229
Lines 234-240 Link Here
234	comma = true;	237	comma = true;
235	}	238	}
236		239
237	rawUpdateStmt.append(tblMeta.getQualifiedName(col, true));	240	rawUpdateStmt.append(dbcol.getQualifiedName(true));
238	// Check for Constant e.g <NULL>, <DEFAULT>, <CURRENT_TIMESTAMP> etc	241	// Check for Constant e.g <NULL>, <DEFAULT>, <CURRENT_TIMESTAMP> etc
239	if (value != null && DataViewUtils.isSQLConstantString(value, dbcol)) {	242	if (value != null && DataViewUtils.isSQLConstantString(value, dbcol)) {
240	String constStr = ((String) value).substring(1, ((String) value).length() - 1);	243	String constStr = ((String) value).substring(1, ((String) value).length() - 1);
Lines 245-286 Link Here
245	}	248	}
246		249
247	rawUpdateStmt.append(" WHERE "); // NOI18N	250	rawUpdateStmt.append(" WHERE "); // NOI18N
248	generateWhereCondition(rawUpdateStmt, row, tblModel);	251	generateWhereCondition(table, rawUpdateStmt, row, tblModel);
249	return rawUpdateStmt.toString();	252	return rawUpdateStmt.toString();
250	}	253	}
251		254
252	String generateDeleteStatement(List<Integer> types, List<Object> values, int rowNum, DataViewTableUIModel tblModel) {	255	String generateDeleteStatement(DBTable table, List<Integer> types, List<Object> values, int rowNum, DataViewTableUIModel tblModel) {
253	StringBuilder deleteStmt = new StringBuilder();	256	StringBuilder deleteStmt = new StringBuilder();
254	deleteStmt.append("DELETE FROM ").append(tblMeta.getFullyQualifiedName(0, true)).append(" WHERE "); // NOI18N	257	deleteStmt.append("DELETE FROM ").append(table.getFullyQualifiedName(true)).append(" WHERE "); // NOI18N
255		258
256	generateWhereCondition(deleteStmt, types, values, rowNum, tblModel);	259	generateWhereCondition(table, deleteStmt, types, values, rowNum, tblModel);
257	return deleteStmt.toString();	260	return deleteStmt.toString();
258	}	261	}
259		262
260	String generateDeleteStatement(int rowNum, DataViewTableUIModel tblModel) {	263	String generateDeleteStatement(DBTable table, int rowNum, DataViewTableUIModel tblModel) {
261	StringBuilder rawDeleteStmt = new StringBuilder();	264	StringBuilder rawDeleteStmt = new StringBuilder();
262	rawDeleteStmt.append("DELETE FROM ").append(tblMeta.getFullyQualifiedName(0, false)).append(" WHERE "); // NOI18N	265	rawDeleteStmt.append("DELETE FROM ").append(table.getFullyQualifiedName(false)).append(" WHERE "); // NOI18N
263		266
264	generateWhereCondition(rawDeleteStmt, rowNum, tblModel);	267	generateWhereCondition(table, rawDeleteStmt, rowNum, tblModel);
265	return rawDeleteStmt.toString();	268	return rawDeleteStmt.toString();
266	}	269	}
267		270
268	// TODO: Support for FK, and other constraint and Index recreation.	271	// TODO: Support for FK, and other constraint and Index recreation.
269	String generateCreateStatement(DBTable table) throws DBException, Exception {	272	String generateCreateStatement(DBTable table) throws DBException, Exception {
270
271	Connection conn = DBConnectionFactory.getInstance().getConnection(dataView.getDatabaseConnection());
272	String msg = "";
273	if (conn == null) {
274	Throwable ex = DBConnectionFactory.getInstance().getLastException();
275	if (ex != null) {
276	msg = ex.getMessage();
277	} else {
278	msg = NbBundle.getMessage(SQLExecutionHelper.class, "MSG_connection_failure", dataView.getDatabaseConnection());
279	}
280	dataView.setErrorStatusText(new DBException(msg));
281	throw new DBException(msg);
282	}
283
284	boolean isdb2 = table.getParentObject().getDBType() == DBMetaDataFactory.DB2 ? true : false;	273	boolean isdb2 = table.getParentObject().getDBType() == DBMetaDataFactory.DB2 ? true : false;
285		274
286	StringBuilder sql = new StringBuilder();	275	StringBuilder sql = new StringBuilder();
Lines 346-367 Link Here
346	return sql.toString();	335	return sql.toString();
347	}	336	}
348		337
349	static String getCountSQLQuery(String queryString) {
350	// User may type "FROM" in either lower, upper or mixed case
351	String[] splitByFrom = queryString.toUpperCase().split("FROM"); // NOI18N
352	queryString = queryString.substring(splitByFrom[0].length());
353
354	String[] splitByOrderBy = queryString.toUpperCase().split("ORDER BY"); // NOI18N
355	queryString = queryString.substring(0, splitByOrderBy[0].length());
356	return "SELECT COUNT(*) " + queryString; // NOI18N
357	}
358
359	static String getCountAsSubQuery(String queryString) {
360	String[] splitByOrderBy = queryString.toUpperCase().split("ORDER BY"); // NOI18N
361	queryString = queryString.substring(0, splitByOrderBy[0].length());
362	return "SELECT COUNT(*) FROM (" + queryString + ") C2668"; // NOI18N
363	}
364
365	private boolean addSeparator(boolean and, StringBuilder sql, String sep) {	338	private boolean addSeparator(boolean and, StringBuilder sql, String sep) {
366	if (and) {	339	if (and) {
367	sql.append(sep);	340	sql.append(sep);
Lines 371-390 Link Here
371	}	344	}
372	}	345	}
373		346
374	private void generateNameValue(int i, StringBuilder sql, Object value, List<Object> values, List<Integer> types) {	347	private void generateNameValue(DBColumn column, StringBuilder sql, Object value, List<Object> values, List<Integer> types) {
375	sql.append(tblMeta.getQualifiedName(i, true));	348	sql.append(column.getQualifiedName(true));
376	if (value != null) {	349	if (value != null) {
377	values.add(value);	350	values.add(value);
378	types.add(tblMeta.getColumnType(i));	351	types.add(column.getJdbcType());
379	sql.append(" = ? "); // NOI18N	352	sql.append(" = ? "); // NOI18N
380	} else { // Handle NULL value in where condition	353	} else { // Handle NULL value in where condition
381	sql.append(" IS NULL "); // NOI18N	354	sql.append(" IS NULL "); // NOI18N
382	}	355	}
383	}	356	}
384		357
385	private void generateNameValue(int i, StringBuilder sql, Object value) {	358	private void generateNameValue(DBColumn column, StringBuilder sql, Object value) {
386	String columnName = tblMeta.getQualifiedName(i, false);	359	String columnName = column.getQualifiedName(false);
387	int type = tblMeta.getColumnType(i);	360	int type = column.getJdbcType();
388		361
389	sql.append(columnName);	362	sql.append(columnName);
390	if (value != null) {	363	if (value != null) {
Lines 394-414 Link Here
394	}	367	}
395	}	368	}
396		369
397	private void generateWhereCondition(StringBuilder result, List<Integer> types, List<Object> values, int rowNum, DataViewTableUIModel model) {	370	private void generateWhereCondition(DBTable table, StringBuilder result, List<Integer> types, List<Object> values, int rowNum, DataViewTableUIModel model) {
398	DBPrimaryKey key = tblMeta.getTable(0).getPrimaryKey();	371	DBPrimaryKey key = table.getPrimaryKey();
399	boolean keySelected = false;	372	boolean keySelected = false;
400	boolean and = false;	373	boolean and = false;
401		374
		375	List<DBColumn> columns = table.getColumnList();
		376
402	if (key != null) {	377	if (key != null) {
403	for (String keyName : key.getColumnNames()) {	378	for (String keyName : key.getColumnNames()) {
404	for (int i = 0; i < model.getColumnCount(); i++) {	379	for (int i = 0; i < model.getColumnCount(); i++) {
405	String columnName = tblMeta.getColumnName(i);	380	DBColumn dbcol = columns.get(i);
		381	String columnName = dbcol.getName();
406	if (columnName.equals(keyName)) {	382	if (columnName.equals(keyName)) {
407	Object val = model.getOriginalValueAt(rowNum, i);	383	Object val = model.getOriginalValueAt(rowNum, i);
408	if (val != null) {	384	if (val != null) {
409	keySelected = true;	385	keySelected = true;
410	and = addSeparator(and, result, " AND "); // NOI18N	386	and = addSeparator(and, result, " AND "); // NOI18N
411	generateNameValue(i, result, val, values, types);	387	generateNameValue(dbcol, result, val, values, types);
412	break;	388	break;
413	}	389	}
414	}	390	}
Lines 418-445 Link Here
418		394
419	if (key == null \|\| !keySelected) {	395	if (key == null \|\| !keySelected) {
420	for (int i = 0; i < model.getColumnCount(); i++) {	396	for (int i = 0; i < model.getColumnCount(); i++) {
		397	DBColumn dbcol = columns.get(i);
421	Object val = model.getOriginalValueAt(rowNum, i);	398	Object val = model.getOriginalValueAt(rowNum, i);
422	and = addSeparator(and, result, " AND "); // NOI18N	399	and = addSeparator(and, result, " AND "); // NOI18N
423	generateNameValue(i, result, val, values, types);	400	generateNameValue(dbcol, result, val, values, types);
424	}	401	}
425	}	402	}
426	}	403	}
427		404
428	private void generateWhereCondition(StringBuilder sql, int rowNum, DataViewTableUIModel model) {	405	private void generateWhereCondition(DBTable table, StringBuilder sql, int rowNum, DataViewTableUIModel model) {
429	DBPrimaryKey key = tblMeta.getTable(0).getPrimaryKey();	406	DBPrimaryKey key = table.getPrimaryKey();
430	boolean keySelected = false;	407	boolean keySelected = false;
431	boolean and = false;	408	boolean and = false;
		409
		410	List<DBColumn> columns = table.getColumnList();
432		411
433	if (key != null) {	412	if (key != null) {
434	for (String keyName : key.getColumnNames()) {	413	for (String keyName : key.getColumnNames()) {
435	for (int i = 0; i < model.getColumnCount(); i++) {	414	for (int i = 0; i < model.getColumnCount(); i++) {
436	String columnName = tblMeta.getColumnName(i);	415	DBColumn dbcol = columns.get(i);
		416	String columnName = dbcol.getName();
437	if (columnName.equals(keyName)) {	417	if (columnName.equals(keyName)) {
438	Object val = model.getOriginalValueAt(rowNum, i);	418	Object val = model.getOriginalValueAt(rowNum, i);
439	if (val != null) {	419	if (val != null) {
440	keySelected = true;	420	keySelected = true;
441	and = addSeparator(and, sql, " AND "); // NOI18N	421	and = addSeparator(and, sql, " AND "); // NOI18N
442	generateNameValue(i, sql, val);	422	generateNameValue(dbcol, sql, val);
443	break;	423	break;
444	}	424	}
445	}	425	}
Lines 449-457 Link Here
449		429
450	if (key == null \|\| !keySelected) {	430	if (key == null \|\| !keySelected) {
451	for (int i = 0; i < model.getColumnCount(); i++) {	431	for (int i = 0; i < model.getColumnCount(); i++) {
		432	DBColumn dbcol = columns.get(i);
452	Object val = model.getOriginalValueAt(rowNum, i);	433	Object val = model.getOriginalValueAt(rowNum, i);
453	and = addSeparator(and, sql, " AND "); // NOI18N	434	and = addSeparator(and, sql, " AND "); // NOI18N
454	generateNameValue(i, sql, val);	435	generateNameValue(dbcol, sql, val);
455	}	436	}
456	}	437	}
457	}	438	}