manticore-projects / MJdbcUtils / #4

Committed 12 Dec 2023 02:07PM CUT coverage: 70.112% (+8.4%) from 61.736%

Build # #4

Build Type

push

github

Committed by

manticore-projects

Commit Message

style: fix Q/A exceptions

Run Details

0 of 2 new or added lines in 1 file covered. (0.0%)

47 existing lines in 1 file now uncovered.

312 of 445 relevant lines covered (70.11%)

0.7 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

73.28

/src/main/java/com/manticore/jdbc/MJdbcTools.java

/**
 * Copyright (C) 2023 manticore-projects Co. Ltd. <support@manticore-projects.com>
 * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 * <p>
 * This program is free software; you can redistribute it and/or modify it under the terms of the
 * GNU General Public License as published by the Free Software Foundation; either version 2 of the
 * License, or (at your option) any later version.
 * <p>
 * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without
 * even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * General Public License for more details.
 * <p>
 * You should have received a copy of the GNU General Public License along with this program; if
 * not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
 * 02111-1307 USA.
 * <p>
 * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 */
package com.manticore.jdbc;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.JdbcNamedParameter;
import net.sf.jsqlparser.expression.JdbcParameter;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.util.deparser.ExpressionDeParser;
import net.sf.jsqlparser.util.deparser.SelectDeParser;
import net.sf.jsqlparser.util.deparser.StatementDeParser;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.nio.charset.Charset;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.Format;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Map;
import java.util.TreeMap;
import java.util.TreeSet;

public class MJdbcTools {
    public static final DateTimeFormatter SQL_DATE_FORMAT =
            DateTimeFormatter.ofPattern("yyyy-MM-dd");
    public static final DateTimeFormatter SQL_TIMESTAMP_FORMAT =
            DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");

    public static java.sql.Date getSQLDate(Calendar cal) {
        return new java.sql.Date(cal.getTimeInMillis());
    }

    public static java.sql.Date getSQLDate(Date d) {
        return new java.sql.Date(d.getTime());
    }

    public static java.sql.Timestamp getSQLTimestamp(Calendar cal) {
        return new java.sql.Timestamp(cal.getTimeInMillis());
    }

    public static java.sql.Timestamp getSQLTimestamp(Date d) {
        return new java.sql.Timestamp(d.getTime());
    }

    public static String getSQLDateStr(Date d) {
        LocalDate localDate = Instant
                .ofEpochMilli(d.getTime())
                .atZone(ZoneId.systemDefault())
                .toLocalDate();
        return "{d '" + MJdbcTools.SQL_DATE_FORMAT.format(localDate) + "'}";
    }

    public static String getSQLDateStr(Calendar c) {
        return getSQLDateStr(c.getTime());
    }

    public static String getSQLDateTimeStr(Date d) {
        LocalDateTime localDateTime = d.toInstant()
                .atZone(ZoneId.systemDefault())
                .toLocalDateTime();
        return "{ts '" + MJdbcTools.SQL_TIMESTAMP_FORMAT.format(localDateTime) + "'}";
    }

    public static String getSQLDateTimeStr(java.sql.Timestamp ts) {
        LocalDateTime localDateTime = ts.toInstant()
                .atZone(ZoneId.systemDefault())
                .toLocalDateTime();
        return "{ts '" + MJdbcTools.SQL_TIMESTAMP_FORMAT.format(localDateTime) + "'}";
    }

    public static String getSQLDateTimeStr(Calendar c) {
        return getSQLDateTimeStr(c.getTime());
    }

    public static String getSQLHash(String sqlStr)
            throws JSQLParserException, NoSuchAlgorithmException {
        MessageDigest messageDigest = MessageDigest.getInstance("SHA-256");

        net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(sqlStr);

        messageDigest.update(statement.toString().getBytes(Charset.defaultCharset()));
        String hashStr = new String(messageDigest.digest(), Charset.defaultCharset());

        return hashStr;
    }

    private static String getParameterStr(Object o) {
        if (o == null) {
            return "NULL";
        } else if (o instanceof java.sql.Date) {
            return "{d '" + o + "'}";
        } else if (o instanceof Date) {
            Calendar cal = GregorianCalendar.getInstance();
            cal.setTime((Date) o);

            if (cal.get(Calendar.MILLISECOND) == 0
                    && cal.get(Calendar.SECOND) == 0
                    && cal.get(Calendar.MINUTE) == 0
                    && cal.get(Calendar.HOUR_OF_DAY) == 0) {
                LocalDate localDate = Instant
                        .ofEpochMilli(((Date) o).getTime())
                        .atZone(ZoneId.systemDefault())
                        .toLocalDate();

                return "{d '" + SQL_DATE_FORMAT.format(localDate) + "'}";
            } else {
                LocalDateTime localDateTime = ((Date) o).toInstant()
                        .atZone(ZoneId.systemDefault())
                        .toLocalDateTime();
                return "{ts '" + SQL_TIMESTAMP_FORMAT.format(localDateTime) + "'}";
            }
        } else if (o instanceof Calendar) {
            Calendar cal = (Calendar) o;
            if (cal.get(Calendar.MILLISECOND) == 0
                    && cal.get(Calendar.SECOND) == 0
                    && cal.get(Calendar.MINUTE) == 0
                    && cal.get(Calendar.HOUR_OF_DAY) == 0) {
                LocalDate localDate = Instant
                        .ofEpochMilli(cal.getTimeInMillis())
                        .atZone(ZoneId.systemDefault())
                        .toLocalDate();

                return "{d '" + SQL_DATE_FORMAT.format(localDate) + "'}";
            } else {
                LocalDateTime localDateTime = cal.toInstant()
                        .atZone(ZoneId.systemDefault())
                        .toLocalDateTime();
                return "{ts '" + SQL_TIMESTAMP_FORMAT.format(localDateTime) + "'}";
            }
        } else if (o instanceof Long) {
            return ((Long) o).toString();
        } else if (o instanceof Integer) {
            return ((Integer) o).toString();
        } else if (o instanceof Short) {
            return ((Short) o).toString();
        } else if (o instanceof Byte) {
            return ((Byte) o).toString();
        } else if (o instanceof Double) {
            return o.toString();
        } else if (o instanceof Float) {
            return o.toString();
        } else if (o instanceof BigInteger) {
            return o.toString();
        } else if (o instanceof BigDecimal) {
            return ((BigDecimal) o).toPlainString();
        } else if (o instanceof String) {
            String s = (String) o;
            s = s.replace("'", "''");
            s = s.replace("&", "' || chr(38) || '");
            return "'" + s + "'";
        } else {
            return "'" + o + "'";
        }
    }

    public static String rewriteStatementWithNamedParameters(String sqlStr,
            Map<String, Object> parameters) throws Exception {
        net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(sqlStr);
        StringBuilder builder = new StringBuilder();
        ExpressionDeParser expressionDeParser = new ExpressionDeParser() {
            public void visit(JdbcNamedParameter parameter) {
                buffer.append(getParameterStr(parameters.get(parameter.getName())));
            }
        };

        SelectDeParser selectDeParser = new SelectDeParser(expressionDeParser, builder);
        expressionDeParser.setSelectVisitor(selectDeParser);
        expressionDeParser.setBuffer(builder);

        StatementDeParser statementDeParser =
                new StatementDeParser(expressionDeParser, selectDeParser, builder);
        statement.accept(statementDeParser);

        return builder.toString();
    }

    public static String rewriteStatementWithNamedParameters(String sqlStr, Object... parameters)
            throws Exception {
        net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(sqlStr);
        StringBuilder builder = new StringBuilder();
        ExpressionDeParser expressionDeParser = new ExpressionDeParser() {
            int i = 0;

            public void visit(JdbcParameter parameter) {
                buffer.append(getParameterStr(parameters[i]));
                i++;
            }

            public void visit(JdbcNamedParameter parameter) {
                buffer.append(getParameterStr(parameters[i]));
                i++;
            }
        };

        SelectDeParser selectDeParser = new SelectDeParser(expressionDeParser, builder);
        expressionDeParser.setSelectVisitor(selectDeParser);
        expressionDeParser.setBuffer(builder);

        StatementDeParser statementDeParser =
                new StatementDeParser(expressionDeParser, selectDeParser, builder);
        statement.accept(statementDeParser);

        return builder.toString();
    }

    public enum AggregateFunction {
        SUM, COUNT
    }

    private static TreeMap<Object, Object> emptyMapFromKeys(TreeSet<Object> keys) {
        TreeMap<Object, Object> map = new TreeMap<>();
        for (Object k : keys) {
            map.put(k, null);
        }
        return map;
    }

    private static class ObjectArrayKey implements Comparable<ObjectArrayKey> {
        private final Comparable[] keys;

        public ObjectArrayKey(Object[] keys) {
            this.keys = new Comparable[keys.length];
            for (int i = 0; i < keys.length; i++) {
                this.keys[i] = (Comparable<?>) keys[i];
            }
        }

        @Override
        public boolean equals(Object o) {
            if (this == o) {
                return true;
            } else if (o == null || getClass() != o.getClass()) {
                return false;
            }
            ObjectArrayKey that = (ObjectArrayKey) o;

            return Arrays.deepEquals(keys, that.keys);
        }

        @Override
        public int hashCode() {
            return Arrays.hashCode(keys);
        }

        @Override
        public int compareTo(ObjectArrayKey other) {
            // Assuming both arrays have the same length
            for (int i = 0; i < keys.length; i++) {
                int elementComparison = keys[i].compareTo(other.keys[i]);
                if (elementComparison != 0) {
                    return elementComparison;
                }
            }

            // If all elements are equal, the arrays are considered equal
            return 0;
        }
    }

    /**
     * The getPivotFromQuery function takes a ResultSet and converts it into a pivot table. The
     * function is designed to create columns for each key of the Category Column and to aggregate
     * the values of the Aggregate Column for each Category.
     *
     * @param rs The ResultSet holding the source data with the category values in rows
     * @param function Determine what type of aggregate function to use (SUM, COUNT, ...)
     * @param aggregateColumnName Specify the column name of the aggregate value
     * @param categoryColumnName Identify the column that will be transformed into separate Value
     *        Columns
     * @param categoryFormat Format the key values into column labels
     * @param buildTotals If to insert Total rows below and column on the right side
     * @param repeatHeader If to insert the header repeatedly before each category
     * @return A 2-dimensional array holding the transformed Column Names and the Data
     */
    @SuppressWarnings({"PMD.ExcessiveMethodLength"})
    public static Object[][] getPivotFromQuery(ResultSet rs, AggregateFunction function,
            String aggregateColumnName, String categoryColumnName, Format categoryFormat,
            boolean buildTotals, boolean repeatHeader) throws SQLException {
        ArrayList<String> columnNames = new ArrayList<>();
        int categoryColumnIndex = -1;
        Class<?> categoryClass = null;
        int aggregateColumnIndex = -1;
        Class<?> aggregateClass = null;

        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            if (columnName.equalsIgnoreCase(categoryColumnName)) {
                try {
                    categoryColumnIndex = i;
                    categoryClass = Class.forName(metaData.getColumnClassName(i));
                } catch (ClassNotFoundException ignore) {
                    // do nothing
                }
            } else if (columnName.equalsIgnoreCase(aggregateColumnName)) {
                try {
                    aggregateColumnIndex = i;
                    aggregateClass = Class.forName(metaData.getColumnClassName(i));
                } catch (ClassNotFoundException ignore) {
                    // do nothing
                }
            } else {
                columnNames.add(columnName);
            }
        }

        if (categoryClass == null || aggregateClass == null) {
            throw new RuntimeException("Unable to determine the Aggregate or Category Classes.");
        }


        TreeSet<Object> keys = new TreeSet<>();
        TreeMap<ObjectArrayKey, TreeMap<Object, Object>> data = new TreeMap<>();
        ArrayList<Object> columnValues = new ArrayList<>();
        Object keyValue;
        Object aggregateValue;
        while (rs.next()) {
            columnValues.clear();
            keyValue = null;
            aggregateValue = null;

            for (int i = 1; i <= columnCount; i++) {
                Object value = rs.getObject(i);

                if (i == categoryColumnIndex) {
                    keyValue = value;
                    // if we meet the key first time, we need to register it and extend the
                    // aggregated data maps
                    if (!keys.contains(keyValue)) {
                        keys.add(keyValue);
                        for (TreeMap<Object, Object> aggregatedData : data.values()) {
                            aggregatedData.put(keyValue, null);
                        }
                    }
                } else if (i == aggregateColumnIndex) {
                    aggregateValue = value;
                } else {
                    columnValues.add(rs.getObject(i));
                }
            }

            ObjectArrayKey objectKey = new ObjectArrayKey(columnValues.toArray());

            final TreeMap<Object, Object> rowData =
                    data.getOrDefault(objectKey, emptyMapFromKeys(keys));
            if (aggregateClass.equals(BigDecimal.class)) {
                BigDecimal a = (BigDecimal) rowData.get(keyValue);
                BigDecimal b = (BigDecimal) aggregateValue;

                if (function == AggregateFunction.SUM) {
                    if (a == null) {
                        rowData.put(keyValue, b);
                    } else if (b != null) {
                        rowData.put(keyValue, a.add(b));
                    }
                } else {
                    throw new UnsupportedOperationException(
                            "Only SUM is supported right now, sorry!");
                }
            } else {
                throw new UnsupportedOperationException(
                        "Only BigDecimals are supported right now, sorry!");
            }

            data.put(objectKey, rowData);
        }

        // build the final data cube
        for (Object k : keys) {
            columnNames.add(categoryFormat != null ? categoryFormat.format(k) : k.toString());
        }
        if (buildTotals) {
            columnNames.add("Total");
        }

        ArrayList<Object[]> resultData = new ArrayList<>();
        BigDecimal[] subTotals = new BigDecimal[keys.size() + 1];
        Arrays.fill(subTotals, BigDecimal.ZERO);

        int r = 0;
        Object mainCategory = null;
        for (Map.Entry<ObjectArrayKey, TreeMap<Object, Object>> e : data.entrySet()) {
            Object[] rowData = new Object[columnNames.size()];

            int c = 0;
            for (Object k : e.getKey().keys) {
                rowData[c] = k;
                c++;
            }

            if (buildTotals && !rowData[0].equals(mainCategory)) {
                if (mainCategory != null) {
                    Object[] totalRowData = new Object[columnNames.size()];
                    totalRowData[0] = mainCategory;
                    totalRowData[e.getKey().keys.length - 2] = "Total";

                    System.arraycopy(subTotals, 0, totalRowData, e.getKey().keys.length,
                            keys.size() + 1);
                    resultData.add(totalRowData);
                    resultData.add(new Object[columnNames.size()]);
                }

                mainCategory = rowData[0];
                Arrays.fill(subTotals, BigDecimal.ZERO);
            }

            BigDecimal rowTotal = BigDecimal.ZERO;
            int c1 = 0;
            for (Object k : keys) {
                rowData[c] = e.getValue().get(k);

                if (buildTotals && rowData[c] != null) {
                    rowTotal = rowTotal.add((BigDecimal) rowData[c]);
                    subTotals[c1] = subTotals[c1].add((BigDecimal) rowData[c]);
                }
                c++;
                c1++;
            }
            if (buildTotals) {
                rowData[c] = rowTotal;
                subTotals[c1] = subTotals[c1].add(rowTotal);
            }

            resultData.add(rowData);
            r++;

            // totals after the last row
            if (buildTotals && r == data.size()) {
                Object[] totalRowData = new Object[columnNames.size()];
                totalRowData[0] = mainCategory;
                totalRowData[e.getKey().keys.length - 2] = "Total";
                System.arraycopy(subTotals, 0, totalRowData, e.getKey().keys.length,
                        keys.size() + 1);
                resultData.add(totalRowData);
            }
        }

        return new Object[][] {
                columnNames.toArray(new String[columnNames.size()]),
                resultData.toArray(new Object[resultData.size()][])
        };
    }

}

1	/**
2	* Copyright (C) 2023 manticore-projects Co. Ltd. <support@manticore-projects.com>
3	* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4	* <p>
5	* This program is free software; you can redistribute it and/or modify it under the terms of the
6	* GNU General Public License as published by the Free Software Foundation; either version 2 of the
7	* License, or (at your option) any later version.
8	* <p>
9	* This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without
10	* even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
11	* General Public License for more details.
12	* <p>
13	* You should have received a copy of the GNU General Public License along with this program; if
14	* not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
15	* 02111-1307 USA.
16	* <p>
17	* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
18	*/
19	package com.manticore.jdbc;
20
21	import net.sf.jsqlparser.JSQLParserException;
22	import net.sf.jsqlparser.expression.JdbcNamedParameter;
23	import net.sf.jsqlparser.expression.JdbcParameter;
24	import net.sf.jsqlparser.parser.CCJSqlParserUtil;
25	import net.sf.jsqlparser.util.deparser.ExpressionDeParser;
26	import net.sf.jsqlparser.util.deparser.SelectDeParser;
27	import net.sf.jsqlparser.util.deparser.StatementDeParser;
28
29	import java.math.BigDecimal;
30	import java.math.BigInteger;
31	import java.nio.charset.Charset;
32	import java.security.MessageDigest;
33	import java.security.NoSuchAlgorithmException;
34	import java.sql.ResultSet;
35	import java.sql.ResultSetMetaData;
36	import java.sql.SQLException;
37	import java.text.Format;
38	import java.time.Instant;
39	import java.time.LocalDate;
40	import java.time.LocalDateTime;
41	import java.time.ZoneId;
42	import java.time.format.DateTimeFormatter;
43	import java.util.ArrayList;
44	import java.util.Arrays;
45	import java.util.Calendar;
46	import java.util.Date;
47	import java.util.GregorianCalendar;
48	import java.util.Map;
49	import java.util.TreeMap;
50	import java.util.TreeSet;
51
UNCOV 52	public class MJdbcTools {	×
53	public static final DateTimeFormatter SQL_DATE_FORMAT =	1✔
54	DateTimeFormatter.ofPattern("yyyy-MM-dd");	1✔
55	public static final DateTimeFormatter SQL_TIMESTAMP_FORMAT =	1✔
56	DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");	1✔
57
58	public static java.sql.Date getSQLDate(Calendar cal) {
59	return new java.sql.Date(cal.getTimeInMillis());	1✔
60	}
61
62	public static java.sql.Date getSQLDate(Date d) {
63	return new java.sql.Date(d.getTime());	1✔
64	}
65
66	public static java.sql.Timestamp getSQLTimestamp(Calendar cal) {
67	return new java.sql.Timestamp(cal.getTimeInMillis());	1✔
68	}
69
70	public static java.sql.Timestamp getSQLTimestamp(Date d) {
71	return new java.sql.Timestamp(d.getTime());	1✔
72	}
73
74	public static String getSQLDateStr(Date d) {
75	LocalDate localDate = Instant	×
UNCOV 76	.ofEpochMilli(d.getTime())	×
UNCOV 77	.atZone(ZoneId.systemDefault())	×
UNCOV 78	.toLocalDate();	×
UNCOV 79	return "{d '" + MJdbcTools.SQL_DATE_FORMAT.format(localDate) + "'}";	×
80	}
81
82	public static String getSQLDateStr(Calendar c) {
UNCOV 83	return getSQLDateStr(c.getTime());	×
84	}
85
86	public static String getSQLDateTimeStr(Date d) {
87	LocalDateTime localDateTime = d.toInstant()	1✔
88	.atZone(ZoneId.systemDefault())	1✔
89	.toLocalDateTime();	1✔
90	return "{ts '" + MJdbcTools.SQL_TIMESTAMP_FORMAT.format(localDateTime) + "'}";	1✔
91	}
92
93	public static String getSQLDateTimeStr(java.sql.Timestamp ts) {
UNCOV 94	LocalDateTime localDateTime = ts.toInstant()	×
UNCOV 95	.atZone(ZoneId.systemDefault())	×
UNCOV 96	.toLocalDateTime();	×
UNCOV 97	return "{ts '" + MJdbcTools.SQL_TIMESTAMP_FORMAT.format(localDateTime) + "'}";	×
98	}
99
100	public static String getSQLDateTimeStr(Calendar c) {
UNCOV 101	return getSQLDateTimeStr(c.getTime());	×
102	}
103
104	public static String getSQLHash(String sqlStr)
105	throws JSQLParserException, NoSuchAlgorithmException {
UNCOV 106	MessageDigest messageDigest = MessageDigest.getInstance("SHA-256");	×
107
UNCOV 108	net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(sqlStr);	×
109
110	messageDigest.update(statement.toString().getBytes(Charset.defaultCharset()));	×
UNCOV 111	String hashStr = new String(messageDigest.digest(), Charset.defaultCharset());	×
112
UNCOV 113	return hashStr;	×
114	}
115
116	private static String getParameterStr(Object o) {
117	if (o == null) {	1✔
118	return "NULL";	×
119	} else if (o instanceof java.sql.Date) {	1✔
120	return "{d '" + o + "'}";	×
121	} else if (o instanceof Date) {	1✔
122	Calendar cal = GregorianCalendar.getInstance();	1✔
123	cal.setTime((Date) o);	1✔
124
125	if (cal.get(Calendar.MILLISECOND) == 0	1✔
126	&& cal.get(Calendar.SECOND) == 0	×
UNCOV 127	&& cal.get(Calendar.MINUTE) == 0	×
UNCOV 128	&& cal.get(Calendar.HOUR_OF_DAY) == 0) {	×
UNCOV 129	LocalDate localDate = Instant	×
UNCOV 130	.ofEpochMilli(((Date) o).getTime())	×
UNCOV 131	.atZone(ZoneId.systemDefault())	×
UNCOV 132	.toLocalDate();	×
133
134	return "{d '" + SQL_DATE_FORMAT.format(localDate) + "'}";	×
135	} else {
136	LocalDateTime localDateTime = ((Date) o).toInstant()	1✔
137	.atZone(ZoneId.systemDefault())	1✔
138	.toLocalDateTime();	1✔
139	return "{ts '" + SQL_TIMESTAMP_FORMAT.format(localDateTime) + "'}";	1✔
140	}
141	} else if (o instanceof Calendar) {	1✔
142	Calendar cal = (Calendar) o;	×
UNCOV 143	if (cal.get(Calendar.MILLISECOND) == 0	×
144	&& cal.get(Calendar.SECOND) == 0	×
UNCOV 145	&& cal.get(Calendar.MINUTE) == 0	×
146	&& cal.get(Calendar.HOUR_OF_DAY) == 0) {	×
147	LocalDate localDate = Instant	×
148	.ofEpochMilli(cal.getTimeInMillis())	×
149	.atZone(ZoneId.systemDefault())	×
UNCOV 150	.toLocalDate();	×
151
152	return "{d '" + SQL_DATE_FORMAT.format(localDate) + "'}";	×
153	} else {
UNCOV 154	LocalDateTime localDateTime = cal.toInstant()	×
UNCOV 155	.atZone(ZoneId.systemDefault())	×
156	.toLocalDateTime();	×
UNCOV 157	return "{ts '" + SQL_TIMESTAMP_FORMAT.format(localDateTime) + "'}";	×
158	}
159	} else if (o instanceof Long) {	1✔
160	return ((Long) o).toString();	×
161	} else if (o instanceof Integer) {	1✔
162	return ((Integer) o).toString();	1✔
163	} else if (o instanceof Short) {	1✔
164	return ((Short) o).toString();	×
165	} else if (o instanceof Byte) {	1✔
166	return ((Byte) o).toString();	×
167	} else if (o instanceof Double) {	1✔
UNCOV 168	return o.toString();	×
169	} else if (o instanceof Float) {	1✔
UNCOV 170	return o.toString();	×
171	} else if (o instanceof BigInteger) {	1✔
UNCOV 172	return o.toString();	×
173	} else if (o instanceof BigDecimal) {	1✔
UNCOV 174	return ((BigDecimal) o).toPlainString();	×
175	} else if (o instanceof String) {	1✔
176	String s = (String) o;	1✔
177	s = s.replace("'", "''");	1✔
178	s = s.replace("&", "' \|\| chr(38) \|\| '");	1✔
179	return "'" + s + "'";	1✔
180	} else {
UNCOV 181	return "'" + o + "'";	×
182	}
183	}
184
185	public static String rewriteStatementWithNamedParameters(String sqlStr,
186	Map<String, Object> parameters) throws Exception {
187	net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(sqlStr);	1✔
188	StringBuilder builder = new StringBuilder();	1✔
189	ExpressionDeParser expressionDeParser = new ExpressionDeParser() {	1✔
190	public void visit(JdbcNamedParameter parameter) {
191	buffer.append(getParameterStr(parameters.get(parameter.getName())));	1✔
192	}	1✔
193	};
194
195	SelectDeParser selectDeParser = new SelectDeParser(expressionDeParser, builder);	1✔
196	expressionDeParser.setSelectVisitor(selectDeParser);	1✔
197	expressionDeParser.setBuffer(builder);	1✔
198
199	StatementDeParser statementDeParser =	1✔
200	new StatementDeParser(expressionDeParser, selectDeParser, builder);
201	statement.accept(statementDeParser);	1✔
202
203	return builder.toString();	1✔
204	}
205
206	public static String rewriteStatementWithNamedParameters(String sqlStr, Object... parameters)
207	throws Exception {
208	net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(sqlStr);	1✔
209	StringBuilder builder = new StringBuilder();	1✔
210	ExpressionDeParser expressionDeParser = new ExpressionDeParser() {	1✔
211	int i = 0;	1✔
212
213	public void visit(JdbcParameter parameter) {
UNCOV 214	buffer.append(getParameterStr(parameters[i]));	×
UNCOV 215	i++;	×
UNCOV 216	}	×
217
218	public void visit(JdbcNamedParameter parameter) {
219	buffer.append(getParameterStr(parameters[i]));	1✔
220	i++;	1✔
221	}	1✔
222	};
223
224	SelectDeParser selectDeParser = new SelectDeParser(expressionDeParser, builder);	1✔
225	expressionDeParser.setSelectVisitor(selectDeParser);	1✔
226	expressionDeParser.setBuffer(builder);	1✔
227
228	StatementDeParser statementDeParser =	1✔
229	new StatementDeParser(expressionDeParser, selectDeParser, builder);
230	statement.accept(statementDeParser);	1✔
231
232	return builder.toString();	1✔
233	}
234
235	public enum AggregateFunction {	1✔
236	SUM, COUNT	1✔
237	}
238
239	private static TreeMap<Object, Object> emptyMapFromKeys(TreeSet<Object> keys) {
240	TreeMap<Object, Object> map = new TreeMap<>();	1✔
241	for (Object k : keys) {	1✔
242	map.put(k, null);	1✔
243	}	1✔
244	return map;	1✔
245	}
246
247	private static class ObjectArrayKey implements Comparable<ObjectArrayKey> {
248	private final Comparable[] keys;
249
250	public ObjectArrayKey(Object[] keys) {	1✔
251	this.keys = new Comparable[keys.length];	1✔
252	for (int i = 0; i < keys.length; i++) {	1✔
253	this.keys[i] = (Comparable<?>) keys[i];	1✔
254	}
255	}	1✔
256
257	@Override
258	public boolean equals(Object o) {
NEW 259	if (this == o) {	×
UNCOV 260	return true;	×
NEW 261	} else if (o == null \|\| getClass() != o.getClass()) {	×
UNCOV 262	return false;	×
263	}
UNCOV 264	ObjectArrayKey that = (ObjectArrayKey) o;	×
265
UNCOV 266	return Arrays.deepEquals(keys, that.keys);	×
267	}
268
269	@Override
270	public int hashCode() {
UNCOV 271	return Arrays.hashCode(keys);	×
272	}
273
274	@Override
275	public int compareTo(ObjectArrayKey other) {
276	// Assuming both arrays have the same length
277	for (int i = 0; i < keys.length; i++) {	1✔
278	int elementComparison = keys[i].compareTo(other.keys[i]);	1✔
279	if (elementComparison != 0) {	1✔
280	return elementComparison;	1✔
281	}
282	}
283
284	// If all elements are equal, the arrays are considered equal
285	return 0;	1✔
286	}
287	}
288
289	/**
290	* The getPivotFromQuery function takes a ResultSet and converts it into a pivot table. The
291	* function is designed to create columns for each key of the Category Column and to aggregate
292	* the values of the Aggregate Column for each Category.
293	*
294	* @param rs The ResultSet holding the source data with the category values in rows
295	* @param function Determine what type of aggregate function to use (SUM, COUNT, ...)
296	* @param aggregateColumnName Specify the column name of the aggregate value
297	* @param categoryColumnName Identify the column that will be transformed into separate Value
298	* Columns
299	* @param categoryFormat Format the key values into column labels
300	* @param buildTotals If to insert Total rows below and column on the right side
301	* @param repeatHeader If to insert the header repeatedly before each category
302	* @return A 2-dimensional array holding the transformed Column Names and the Data
303	*/
304	@SuppressWarnings({"PMD.ExcessiveMethodLength"})
305	public static Object[][] getPivotFromQuery(ResultSet rs, AggregateFunction function,
306	String aggregateColumnName, String categoryColumnName, Format categoryFormat,
307	boolean buildTotals, boolean repeatHeader) throws SQLException {
308	ArrayList<String> columnNames = new ArrayList<>();	1✔
309	int categoryColumnIndex = -1;	1✔
310	Class<?> categoryClass = null;	1✔
311	int aggregateColumnIndex = -1;	1✔
312	Class<?> aggregateClass = null;	1✔
313
314	ResultSetMetaData metaData = rs.getMetaData();	1✔
315	int columnCount = metaData.getColumnCount();	1✔
316	for (int i = 1; i <= columnCount; i++) {	1✔
317	String columnName = metaData.getColumnName(i);	1✔
318	if (columnName.equalsIgnoreCase(categoryColumnName)) {	1✔
319	try {
320	categoryColumnIndex = i;	1✔
321	categoryClass = Class.forName(metaData.getColumnClassName(i));	1✔
UNCOV 322	} catch (ClassNotFoundException ignore) {	×
323	// do nothing
324	}	1✔
325	} else if (columnName.equalsIgnoreCase(aggregateColumnName)) {	1✔
326	try {
327	aggregateColumnIndex = i;	1✔
328	aggregateClass = Class.forName(metaData.getColumnClassName(i));	1✔
UNCOV 329	} catch (ClassNotFoundException ignore) {	×
330	// do nothing
331	}	1✔
332	} else {
333	columnNames.add(columnName);	1✔
334	}
335	}
336
337	if (categoryClass == null \|\| aggregateClass == null) {	1✔
UNCOV 338	throw new RuntimeException("Unable to determine the Aggregate or Category Classes.");	×
339	}
340
341
342	TreeSet<Object> keys = new TreeSet<>();	1✔
343	TreeMap<ObjectArrayKey, TreeMap<Object, Object>> data = new TreeMap<>();	1✔
344	ArrayList<Object> columnValues = new ArrayList<>();	1✔
345	Object keyValue;
346	Object aggregateValue;
347	while (rs.next()) {	1✔
348	columnValues.clear();	1✔
349	keyValue = null;	1✔
350	aggregateValue = null;	1✔
351
352	for (int i = 1; i <= columnCount; i++) {	1✔
353	Object value = rs.getObject(i);	1✔
354
355	if (i == categoryColumnIndex) {	1✔
356	keyValue = value;	1✔
357	// if we meet the key first time, we need to register it and extend the
358	// aggregated data maps
359	if (!keys.contains(keyValue)) {	1✔
360	keys.add(keyValue);	1✔
361	for (TreeMap<Object, Object> aggregatedData : data.values()) {	1✔
362	aggregatedData.put(keyValue, null);	1✔
363	}	1✔
364	}
365	} else if (i == aggregateColumnIndex) {	1✔
366	aggregateValue = value;	1✔
367	} else {
368	columnValues.add(rs.getObject(i));	1✔
369	}
370	}
371
372	ObjectArrayKey objectKey = new ObjectArrayKey(columnValues.toArray());	1✔
373
374	final TreeMap<Object, Object> rowData =	1✔
375	data.getOrDefault(objectKey, emptyMapFromKeys(keys));	1✔
376	if (aggregateClass.equals(BigDecimal.class)) {	1✔
377	BigDecimal a = (BigDecimal) rowData.get(keyValue);	1✔
378	BigDecimal b = (BigDecimal) aggregateValue;	1✔
379
380	if (function == AggregateFunction.SUM) {	1✔
381	if (a == null) {	1✔
382	rowData.put(keyValue, b);	1✔
UNCOV 383	} else if (b != null) {	×
UNCOV 384	rowData.put(keyValue, a.add(b));	×
385	}
386	} else {
UNCOV 387	throw new UnsupportedOperationException(	×
388	"Only SUM is supported right now, sorry!");
389	}
390	} else {	1✔
UNCOV 391	throw new UnsupportedOperationException(	×
392	"Only BigDecimals are supported right now, sorry!");
393	}
394
395	data.put(objectKey, rowData);	1✔
396	}	1✔
397
398	// build the final data cube
399	for (Object k : keys) {	1✔
400	columnNames.add(categoryFormat != null ? categoryFormat.format(k) : k.toString());	1✔
401	}	1✔
402	if (buildTotals) {	1✔
403	columnNames.add("Total");	1✔
404	}
405
406	ArrayList<Object[]> resultData = new ArrayList<>();	1✔
407	BigDecimal[] subTotals = new BigDecimal[keys.size() + 1];	1✔
408	Arrays.fill(subTotals, BigDecimal.ZERO);	1✔
409
410	int r = 0;	1✔
411	Object mainCategory = null;	1✔
412	for (Map.Entry<ObjectArrayKey, TreeMap<Object, Object>> e : data.entrySet()) {	1✔
413	Object[] rowData = new Object[columnNames.size()];	1✔
414
415	int c = 0;	1✔
416	for (Object k : e.getKey().keys) {	1✔
417	rowData[c] = k;	1✔
418	c++;	1✔
419	}
420
421	if (buildTotals && !rowData[0].equals(mainCategory)) {	1✔
422	if (mainCategory != null) {	1✔
423	Object[] totalRowData = new Object[columnNames.size()];	1✔
424	totalRowData[0] = mainCategory;	1✔
425	totalRowData[e.getKey().keys.length - 2] = "Total";	1✔
426
427	System.arraycopy(subTotals, 0, totalRowData, e.getKey().keys.length,	1✔
428	keys.size() + 1);	1✔
429	resultData.add(totalRowData);	1✔
430	resultData.add(new Object[columnNames.size()]);	1✔
431	}
432
433	mainCategory = rowData[0];	1✔
434	Arrays.fill(subTotals, BigDecimal.ZERO);	1✔
435	}
436
437	BigDecimal rowTotal = BigDecimal.ZERO;	1✔
438	int c1 = 0;	1✔
439	for (Object k : keys) {	1✔
440	rowData[c] = e.getValue().get(k);	1✔
441
442	if (buildTotals && rowData[c] != null) {	1✔
443	rowTotal = rowTotal.add((BigDecimal) rowData[c]);	1✔
444	subTotals[c1] = subTotals[c1].add((BigDecimal) rowData[c]);	1✔
445	}
446	c++;	1✔
447	c1++;	1✔
448	}	1✔
449	if (buildTotals) {	1✔
450	rowData[c] = rowTotal;	1✔
451	subTotals[c1] = subTotals[c1].add(rowTotal);	1✔
452	}
453
454	resultData.add(rowData);	1✔
455	r++;	1✔
456
457	// totals after the last row
458	if (buildTotals && r == data.size()) {	1✔
459	Object[] totalRowData = new Object[columnNames.size()];	1✔
460	totalRowData[0] = mainCategory;	1✔
461	totalRowData[e.getKey().keys.length - 2] = "Total";	1✔
462	System.arraycopy(subTotals, 0, totalRowData, e.getKey().keys.length,	1✔
463	keys.size() + 1);	1✔
464	resultData.add(totalRowData);	1✔
465	}
466	}	1✔
467
468	return new Object[][] {	1✔
469	columnNames.toArray(new String[columnNames.size()]),	1✔
470	resultData.toArray(new Object[resultData.size()][])	1✔
471	};
472	}
473
474	}

manticore-projects / MJdbcUtils / #4

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous