Examples with QueryOutputToListOfMaps nl.nn.adapterframework.jdbc.transformer.QueryOutputToListOfMaps used on opensource projects

Example 1 with QueryOutputToListOfMaps

use of nl.nn.adapterframework.jdbc.transformer.QueryOutputToListOfMaps in project iaf by ibissource.

the class BrowseJdbcTable method execute.

@POST
@RolesAllowed({ "IbisDataAdmin", "IbisAdmin", "IbisTester" })
@Path("/jdbc/browse")
@Consumes(MediaType.APPLICATION_JSON)
@Produces(MediaType.APPLICATION_JSON)
public Response execute(LinkedHashMap<String, Object> json) throws ApiException {
    String datasource = null, tableName = null, where = "", order = "";
    Boolean numberOfRowsOnly = false;
    int minRow = 1, maxRow = 100;
    for (Entry<String, Object> entry : json.entrySet()) {
        String key = entry.getKey();
        if (key.equalsIgnoreCase("datasource")) {
            datasource = entry.getValue().toString();
        }
        if (key.equalsIgnoreCase("table")) {
            tableName = entry.getValue().toString();
        }
        if (key.equalsIgnoreCase("where")) {
            where = entry.getValue().toString();
        }
        if (key.equalsIgnoreCase("order")) {
            order = entry.getValue().toString();
        }
        if (key.equalsIgnoreCase("numberOfRowsOnly")) {
            numberOfRowsOnly = Boolean.parseBoolean(entry.getValue().toString());
        }
        if (key.equalsIgnoreCase("minRow")) {
            if (entry.getValue() != "") {
                minRow = Integer.parseInt(entry.getValue().toString());
                minRow = Math.max(minRow, 0);
            }
        }
        if (key.equalsIgnoreCase("maxRow")) {
            if (entry.getValue() != "") {
                maxRow = Integer.parseInt(entry.getValue().toString());
                maxRow = Math.max(maxRow, 1);
            }
        }
    }
    if (datasource == null || tableName == null) {
        throw new ApiException("datasource and/or tableName not defined.", 400);
    }
    if (maxRow < minRow)
        throw new ApiException("Rownum max must be greater than or equal to Rownum min", 400);
    if (maxRow - minRow >= 100) {
        throw new ApiException("Difference between Rownum max and Rownum min must be less than hundred", 400);
    }
    if (!readAllowed(permissionRules, tableName))
        throw new ApiException("Access to table (" + tableName + ") not allowed", 400);
    // We have all info we need, lets execute the query!
    Map<String, Object> fieldDef = new LinkedHashMap<>();
    String result = "";
    String query = null;
    DirectQuerySender qs;
    try {
        qs = getIbisContext().createBeanAutowireByName(DirectQuerySender.class);
    } catch (Exception e) {
        throw new ApiException("An error occured on creating or closing the connection!", e);
    }
    try {
        qs.setName("QuerySender");
        qs.setDatasourceName(datasource);
        qs.setQueryType("select");
        qs.setSqlDialect("Oracle");
        qs.setBlobSmartGet(true);
        qs.setIncludeFieldDefinition(true);
        qs.configure(true);
        qs.open();
        try (Connection conn = qs.getConnection()) {
            ResultSet rs = null;
            try {
                rs = conn.getMetaData().getColumns(null, null, tableName, null);
                if (!rs.isBeforeFirst()) {
                    rs.close();
                    rs = conn.getMetaData().getColumns(null, null, tableName.toUpperCase(), null);
                }
                StringBuilder fielddefinition = new StringBuilder("<fielddefinition>");
                String field = null;
                if (!numberOfRowsOnly) {
                    field = "<field name=\"" + rnumColumnName + "\" type=\"INTEGER\" />";
                    fielddefinition.append(field);
                    fieldDef.put(rnumColumnName, "INTEGER");
                    while (rs.next()) {
                        field = "<field name=\"" + rs.getString(COLUMN_NAME) + "\" type=\"" + DB2XMLWriter.getFieldType(rs.getInt(DATA_TYPE)) + "\" size=\"" + rs.getInt(COLUMN_SIZE) + "\"/>";
                        fielddefinition.append(field);
                        fieldDef.put(rs.getString(COLUMN_NAME), DB2XMLWriter.getFieldType(rs.getInt(DATA_TYPE)) + "(" + rs.getInt(COLUMN_SIZE) + ")");
                    }
                } else {
                    field = "<field name=\"" + countColumnName + "\" type=\"INTEGER\" />";
                    fielddefinition.append(field);
                    fieldDef.put(countColumnName, "INTEGER");
                    if (StringUtils.isNotEmpty(order)) {
                        rs = conn.getMetaData().getColumns(null, null, tableName, order);
                        while (rs.next()) {
                            field = "<field name=\"" + rs.getString(COLUMN_NAME) + "\" type=\"" + DB2XMLWriter.getFieldType(rs.getInt(DATA_TYPE)) + "\" size=\"" + rs.getInt(COLUMN_SIZE) + "\"/>";
                            fielddefinition.append(field);
                            fieldDef.put(rs.getString(COLUMN_NAME), DB2XMLWriter.getFieldType(rs.getInt(DATA_TYPE)) + "(" + rs.getInt(COLUMN_SIZE) + ")");
                        }
                    }
                }
                fielddefinition.append("</fielddefinition>");
                String browseJdbcTableExecuteREQ = "<browseJdbcTableExecuteREQ>" + "<dbmsName>" + qs.getDbmsSupport().getDbmsName() + "</dbmsName>" + "<countColumnName>" + countColumnName + "</countColumnName>" + "<rnumColumnName>" + rnumColumnName + "</rnumColumnName>" + "<tableName>" + tableName + "</tableName>" + "<where>" + XmlUtils.encodeChars(where) + "</where>" + "<numberOfRowsOnly>" + numberOfRowsOnly + "</numberOfRowsOnly>" + "<order>" + order + "</order>" + "<rownumMin>" + minRow + "</rownumMin>" + "<rownumMax>" + maxRow + "</rownumMax>" + fielddefinition + "<maxColumnSize>1000</maxColumnSize>" + "</browseJdbcTableExecuteREQ>";
                URL url = ClassUtils.getResourceURL(DB2XML_XSLT);
                if (url != null) {
                    Transformer t = XmlUtils.createTransformer(url);
                    query = XmlUtils.transformXml(t, browseJdbcTableExecuteREQ);
                }
                result = qs.sendMessage(new Message(query), null).asString();
            } finally {
                if (rs != null) {
                    rs.close();
                }
            }
        }
    } catch (Throwable t) {
        throw new ApiException("An error occured on executing jdbc query [" + query + "]", t);
    } finally {
        qs.close();
    }
    List<Map<String, String>> resultMap = null;
    if (XmlUtils.isWellFormed(result)) {
        try {
            resultMap = new QueryOutputToListOfMaps().parseString(result);
        } catch (IOException | SAXException e) {
            throw new ApiException("Query result could not be parsed.", e);
        }
    }
    if (resultMap == null)
        throw new ApiException("Invalid query result [null].", 400);
    Map<String, Object> resultObject = new HashMap<String, Object>();
    resultObject.put("table", tableName);
    resultObject.put("query", XmlUtils.encodeChars(query));
    resultObject.put("fielddefinition", fieldDef);
    resultObject.put("result", resultMap);
    return Response.status(Response.Status.CREATED).entity(resultObject).build();
}