Examples with ParamSetter com.cubrid.common.ui.spi.util.paramSetter.ParamSetter used on opensource projects

Example 1 with ParamSetter

use of com.cubrid.common.ui.spi.util.paramSetter.ParamSetter in project cubrid-manager by CUBRID.

the class QueryExecuter method updateValues.

/**
	 * Update values on the query result editor.
	 *
	 * @param queryConn Connection
	 * @return
	 * @throws SQLException
	 * @throws ParamSetException
	 */
private Map<String, Map<String, CellValue>> updateValues() throws SQLException, ParamSetException {
    Map<String, Map<String, CellValue>> successedMap = new HashMap<String, Map<String, CellValue>>();
    ParamSetter paramSetter = new ParamSetter();
    if (oldValues == null || oldValues.size() == 0) {
        return successedMap;
    }
    if (newValues == null || newValues.size() == 0) {
        return successedMap;
    }
    Connection conn = connection.checkAndConnect();
    try {
        String tableName = UIQueryUtil.getTableNameFromQuery(conn, query);
        String escapedTable = QuerySyntax.escapeKeyword(tableName);
        if (tableName == null) {
            CommonUITool.openErrorBox(Messages.errModifiedOneTable);
            return successedMap;
        }
        List<ColumnInfo> colInfoList = getAllColumnList();
        PreparedStatement pstmt = null;
        for (String key : oldValues.keySet()) {
            try {
                Map<String, CellValue> oldValueMap = oldValues.get(key);
                Map<String, CellValue> newValueMap = newValues.get(key);
                if (oldValueMap == null || oldValueMap.size() == 0 || newValueMap == null || newValueMap.size() == 0) {
                    continue;
                }
                StringBuilder updateSQLBuffer = new StringBuilder();
                List<ColumnInfo> updatedColInfoList = new ArrayList<ColumnInfo>();
                List<CellValue> newValueList = new ArrayList<CellValue>();
                for (int i = 0; i < colInfoList.size(); i++) {
                    ColumnInfo colInfo = colInfoList.get(i);
                    CellValue newValue = newValueMap.get(colInfo.getIndex());
                    CellValue oldValue = oldValueMap.get(colInfo.getIndex());
                    if ((oldValue == null && newValue != null) || (newValue == null && oldValue != null)) {
                        newValueList.add(newValue);
                        updatedColInfoList.add(colInfo);
                    } else if (oldValue != null && newValue != null && !oldValue.equals(newValue)) {
                        newValueList.add(newValue);
                        updatedColInfoList.add(colInfo);
                    }
                }
                if (updatedColInfoList.isEmpty()) {
                    continue;
                }
                updateSQLBuffer.append("UPDATE ").append(escapedTable).append(" SET ");
                StringBuilder setSQLBf = new StringBuilder();
                List<PstmtParameter> pstmtParaList = new ArrayList<PstmtParameter>();
                int valueParamIndex = 1;
                for (int i = 0; i < updatedColInfoList.size(); i++) {
                    ColumnInfo colInfo = updatedColInfoList.get(i);
                    CellValue newValue = newValueMap.get(colInfo.getIndex());
                    String colName = colInfo.getName();
                    if (queryEditor.isIgnoreType(colInfo.getType())) {
                        continue;
                    }
                    if (setSQLBf.length() > 0) {
                        setSQLBf.append(", ");
                    }
                    CellValue cellValue = newValueMap.get(colInfo.getIndex());
                    if (DataType.DATATYPE_NATIONAL_CHARACTER.equalsIgnoreCase(colInfo.getType()) || DataType.DATATYPE_NCHAR_VARYING.equalsIgnoreCase(colInfo.getType()) || DataType.DATATYPE_NCHAR.equalsIgnoreCase(colInfo.getType())) {
                        String dataType = DataType.makeType(colInfo.getType(), colInfo.getChildElementType(), colInfo.getPrecision(), colInfo.getScale());
                        String charset = getDatabaseInfo() != null ? getDatabaseInfo().getCharSet() : null;
                        FormatDataResult result = DBAttrTypeFormatter.format(dataType, cellValue.getStringValue(), null, false, charset, false);
                        if (result.isSuccess()) {
                            setSQLBf.append(QuerySyntax.escapeKeyword(colName));
                            setSQLBf.append(" = ").append(result.getFormatedString());
                        } else {
                            throw new ParamSetException("Format data \"" + cellValue.getStringValue() + "\"error for data type " + dataType);
                        }
                    } else if ((DataType.DATATYPE_BIT.equalsIgnoreCase(colInfo.getType()) || DataType.DATATYPE_BIT_VARYING.equalsIgnoreCase(colInfo.getType())) && newValue.getValue() instanceof String) {
                        String dataType = DataType.makeType(colInfo.getType(), colInfo.getChildElementType(), colInfo.getPrecision(), colInfo.getScale());
                        String charset = getDatabaseInfo() != null ? getDatabaseInfo().getCharSet() : null;
                        FormatDataResult result = DBAttrTypeFormatter.format(dataType, cellValue.getStringValue(), null, false, charset, false);
                        setSQLBf.append(QuerySyntax.escapeKeyword(colName));
                        setSQLBf.append(" = ").append(result.getFormatedString());
                    } else {
                        setSQLBf.append(QuerySyntax.escapeKeyword(colName)).append(" = ?");
                        PstmtParameter pstmtParameter = new PstmtParameter(colInfo.getName(), valueParamIndex++, colInfo.getComleteType(), cellValue.getValue());
                        pstmtParaList.add(pstmtParameter);
                    }
                }
                if (setSQLBf.length() < 1) {
                    continue;
                }
                updateSQLBuffer.append(setSQLBf);
                updateSQLBuffer.append(" WHERE ");
                List<String> pkList = UIQueryUtil.getPkList(getDatabaseInfo(), tableName);
                int pkParamIndex = 0;
                for (int i = 0; i < newValueMap.size(); i++) {
                    ColumnInfo colInfo = ((ColumnInfo) getAllColumnList().get(i));
                    String col = colInfo.getName();
                    if (!pkList.contains(col)) {
                        continue;
                    }
                    if (queryEditor.isIgnoreType(colInfo.getType())) {
                        continue;
                    }
                    if (pkParamIndex > 0) {
                        updateSQLBuffer.append(" AND ");
                    }
                    updateSQLBuffer.append(QuerySyntax.escapeKeyword(col)).append(" = ?");
                    CellValue object = oldValueMap.get(colInfo.getIndex());
                    PstmtParameter pstmtParameter = new PstmtParameter(colInfo.getName(), valueParamIndex++, colInfo.getComleteType(), object.getValue());
                    pstmtParaList.add(pstmtParameter);
                    pkParamIndex++;
                }
                pstmt = conn.prepareStatement(updateSQLBuffer.toString());
                for (PstmtParameter pstmtParameter : pstmtParaList) {
                    paramSetter.handle(pstmt, pstmtParameter);
                }
                pstmt.executeUpdate();
                successedMap.put(key, newValueMap);
                if (!connection.isAutoCommit() && queryEditor.getConnection() == connection) {
                    queryEditor.setHaveActiveTransaction(true);
                }
                for (ColumnInfo colInfo : updatedColInfoList) {
                    CellValue newValue = newValueMap.get(colInfo.getIndex());
                    CellValue oldValue = oldValueMap.get(colInfo.getIndex());
                    if (newValue != null && oldValue != null) {
                        oldValue.setValue(newValue.getValue());
                    }
                }
            } catch (SQLException e) {
                if (successedMap.containsKey(key)) {
                    successedMap.remove(key);
                }
                LOGGER.error("", e);
                logMessageText.setText(e.getLocalizedMessage());
                throw e;
            } finally {
                QueryUtil.freeQuery(pstmt);
            }
        }
    } finally {
        if (connection != null && connection.isAutoClosable()) {
            connection.commit();
            connection.close();
        }
    }
    return successedMap;
}

Example 2 with ParamSetter

use of com.cubrid.common.ui.spi.util.paramSetter.ParamSetter in project cubrid-manager by CUBRID.

the class QueryExecuter method insertValues.

/**
	 * Insert values on the query result editor.
	 *
	 * @return
	 * @throws SQLException
	 */
private Map<String, Map<String, CellValue>> insertValues() throws SQLException, ParamSetException {
    Map<String, Map<String, CellValue>> successedMap = new HashMap<String, Map<String, CellValue>>();
    if (insValues == null || insValues.size() == 0) {
        return successedMap;
    }
    ParamSetter paramSetter = new ParamSetter();
    NumberFormat nf = NumberFormat.getInstance();
    nf.setMaximumFractionDigits(3);
    Connection conn = connection.checkAndConnect();
    try {
        String tableName = UIQueryUtil.getTableNameFromQuery(conn, query);
        String escapedTable = QuerySyntax.escapeKeyword(tableName);
        if (tableName == null) {
            CommonUITool.openErrorBox(Messages.errModifiedOneTable);
            return successedMap;
        }
        PreparedStatement pstmt = null;
        List<ColumnInfo> allColumnList = getAllColumnList();
        for (String key : insValues.keySet()) {
            Map<String, CellValue> valuesMap = insValues.get(key);
            if (valuesMap == null) {
                continue;
            }
            try {
                List<ColumnInfo> colInfoList = new ArrayList<ColumnInfo>();
                List<ColumnInfo> unPColInfoList = new ArrayList<ColumnInfo>();
                for (int i = 0; i < allColumnList.size(); i++) {
                    ColumnInfo colInfo = allColumnList.get(i);
                    if (queryEditor.isIgnoreType(colInfo.getType())) {
                        continue;
                    }
                    CellValue value = valuesMap.get(colInfo.getIndex());
                    if (value == null || value.getValue() == null) {
                        continue;
                    }
                    if (DataType.DATATYPE_NATIONAL_CHARACTER.equalsIgnoreCase(colInfo.getType()) || DataType.DATATYPE_NCHAR_VARYING.equalsIgnoreCase(colInfo.getType()) || DataType.DATATYPE_NCHAR.equalsIgnoreCase(colInfo.getType())) {
                        unPColInfoList.add(colInfo);
                        continue;
                    }
                    if ((DataType.DATATYPE_BIT.equalsIgnoreCase(colInfo.getType()) || DataType.DATATYPE_BIT_VARYING.equalsIgnoreCase(colInfo.getType())) && value.getValue() instanceof String) {
                        unPColInfoList.add(colInfo);
                        continue;
                    }
                    colInfoList.add(colInfo);
                }
                StringBuilder sqlBuffer = new StringBuilder();
                sqlBuffer.append("INSERT INTO ").append(escapedTable).append(" (");
                int paramCount = 0;
                for (ColumnInfo columnInfo : colInfoList) {
                    if (paramCount > 0) {
                        sqlBuffer.append(",");
                    }
                    sqlBuffer.append(QuerySyntax.escapeKeyword(columnInfo.getName()));
                    paramCount++;
                }
                for (ColumnInfo columnInfo : unPColInfoList) {
                    if (paramCount > 0) {
                        sqlBuffer.append(",");
                    }
                    sqlBuffer.append(QuerySyntax.escapeKeyword(columnInfo.getName()));
                    paramCount++;
                }
                sqlBuffer.append(") VALUES (");
                int dataIndex = 1;
                List<PstmtParameter> pstmtParaList = new ArrayList<PstmtParameter>();
                for (ColumnInfo columnInfo : colInfoList) {
                    if (dataIndex > 1) {
                        sqlBuffer.append(",");
                    }
                    sqlBuffer.append("?");
                    CellValue value = valuesMap.get(columnInfo.getIndex());
                    PstmtParameter pstmtParameter = new PstmtParameter(columnInfo.getName(), dataIndex, columnInfo.getComleteType(), value.getValue());
                    pstmtParaList.add(pstmtParameter);
                    dataIndex++;
                }
                String charset = getDatabaseInfo() != null ? getDatabaseInfo().getCharSet() : null;
                for (ColumnInfo columnInfo : unPColInfoList) {
                    if (dataIndex > 1) {
                        sqlBuffer.append(",");
                    }
                    CellValue value = valuesMap.get(columnInfo.getIndex());
                    String dataType = DataType.makeType(columnInfo.getType(), columnInfo.getChildElementType(), columnInfo.getPrecision(), columnInfo.getScale());
                    FormatDataResult result = DBAttrTypeFormatter.format(dataType, value.getStringValue(), null, false, charset, false);
                    if (result.isSuccess()) {
                        sqlBuffer.append(result.getFormatedString());
                    } else {
                        throw new ParamSetException("Format data \"" + value.getStringValue() + "\"error for data type " + dataType);
                    }
                }
                sqlBuffer.append(")");
                pstmt = conn.prepareStatement(sqlBuffer.toString());
                for (PstmtParameter pstmtParameter : pstmtParaList) {
                    paramSetter.handle(pstmt, pstmtParameter);
                }
                pstmt.executeUpdate();
                successedMap.put(key, valuesMap);
                if (!connection.isAutoCommit() && queryEditor.getConnection() == connection) {
                    queryEditor.setHaveActiveTransaction(true);
                }
            } catch (SQLException e) {
                if (successedMap.containsKey(key)) {
                    successedMap.remove(key);
                }
                LOGGER.error("", e);
                logMessageText.setText(e.getLocalizedMessage());
                throw e;
            } finally {
                QueryUtil.freeQuery(pstmt);
            }
        }
    } finally {
        if (connection != null && connection.isAutoClosable()) {
            connection.commit();
            connection.close();
        }
    }
    return successedMap;
}

Example 3 with ParamSetter

use of com.cubrid.common.ui.spi.util.paramSetter.ParamSetter in project cubrid-manager by CUBRID.

the class QueryExecuter method deleteValues.

/**
	 * Delete values on the query result editor.
	 *
	 * @param queryConn Connection
	 * @throws SQLException the exception
	 */
private Map<String, Map<String, CellValue>> deleteValues() throws SQLException, ParamSetException {
    ParamSetter paramSetter = new ParamSetter();
    Map<String, Map<String, CellValue>> successedMap = new HashMap<String, Map<String, CellValue>>();
    Connection conn = connection.checkAndConnect();
    try {
        String tableName = UIQueryUtil.getTableNameFromQuery(conn, query);
        String escapedTable = QuerySyntax.escapeKeyword(tableName);
        if (tableName == null) {
            CommonUITool.openErrorBox(Messages.errModifiedOneTable);
            return successedMap;
        }
        PreparedStatement pstmt = null;
        List<ColumnInfo> allColumnList = getAllColumnList();
        for (String key : delValues.keySet()) {
            Map<String, CellValue> valuesMap = delValues.get(key);
            if (valuesMap == null) {
                continue;
            }
            try {
                List<ColumnInfo> colInfoList = new ArrayList<ColumnInfo>();
                List<ColumnInfo> unPColInfoList = new ArrayList<ColumnInfo>();
                for (int i = 0; i < allColumnList.size(); i++) {
                    ColumnInfo colInfo = allColumnList.get(i);
                    if (queryEditor.isIgnoreType(colInfo.getType())) {
                        continue;
                    }
                    CellValue value = valuesMap.get(colInfo.getIndex());
                    if (value == null || value.getValue() == null) {
                        continue;
                    }
                    if (DataType.DATATYPE_NATIONAL_CHARACTER.equalsIgnoreCase(colInfo.getType()) || DataType.DATATYPE_NCHAR_VARYING.equalsIgnoreCase(colInfo.getType()) || DataType.DATATYPE_NCHAR.equalsIgnoreCase(colInfo.getType())) {
                        unPColInfoList.add(colInfo);
                        continue;
                    }
                    if ((DataType.DATATYPE_BIT.equalsIgnoreCase(colInfo.getType()) || DataType.DATATYPE_BIT_VARYING.equalsIgnoreCase(colInfo.getType())) && value.getValue() instanceof String) {
                        unPColInfoList.add(colInfo);
                        continue;
                    }
                    colInfoList.add(colInfo);
                }
                StringBuilder sqlBuffer = new StringBuilder();
                sqlBuffer.append("DELETE FROM ").append(QuerySyntax.escapeKeyword(escapedTable)).append(" WHERE ");
                List<PstmtParameter> pstmtParaList = new ArrayList<PstmtParameter>();
                int paramCount = 1;
                for (ColumnInfo columnInfo : colInfoList) {
                    if (paramCount > 1) {
                        sqlBuffer.append(" AND ");
                    }
                    sqlBuffer.append(QuerySyntax.escapeKeyword(columnInfo.getName())).append(" = ? ");
                    CellValue value = valuesMap.get(columnInfo.getIndex());
                    PstmtParameter pstmtParameter = new PstmtParameter(columnInfo.getName(), paramCount, columnInfo.getComleteType(), value.getValue());
                    pstmtParaList.add(pstmtParameter);
                    paramCount++;
                }
                for (ColumnInfo columnInfo : unPColInfoList) {
                    if (paramCount > 1) {
                        sqlBuffer.append(" AND ");
                    }
                    sqlBuffer.append(QuerySyntax.escapeKeyword(columnInfo.getName())).append("=");
                    CellValue cellValue = valuesMap.get(columnInfo.getIndex());
                    String dataType = DataType.makeType(columnInfo.getType(), columnInfo.getChildElementType(), columnInfo.getPrecision(), columnInfo.getScale());
                    FormatDataResult result = DBAttrTypeFormatter.format(dataType, cellValue.getStringValue(), null, false, charset, false);
                    if (result.isSuccess()) {
                        sqlBuffer.append(result.getFormatedString());
                    } else {
                        throw new ParamSetException("Format data \"" + cellValue.getStringValue() + "\"error for data type " + dataType);
                    }
                    paramCount++;
                }
                pstmt = conn.prepareStatement(sqlBuffer.toString());
                for (PstmtParameter pstmtParameter : pstmtParaList) {
                    paramSetter.handle(pstmt, pstmtParameter);
                }
                pstmt.executeUpdate();
                successedMap.put(key, valuesMap);
                if (!connection.isAutoCommit() && queryEditor.getConnection() == connection) {
                    queryEditor.setHaveActiveTransaction(true);
                }
            } catch (SQLException ex) {
                if (successedMap.containsKey(key)) {
                    successedMap.remove(key);
                }
                LOGGER.error("", ex);
                throw ex;
            } finally {
                QueryUtil.freeQuery(pstmt);
            }
        }
    } finally {
        if (connection != null && connection.isAutoClosable()) {
            connection.commit();
            connection.close();
        }
    }
    return successedMap;
}