manticore-projects / MJdbcUtils / #5

Committed 18 Sep 2024 04:37AM UTC coverage: 70.246% (+0.1%) from 70.112%

Build # #5

Build Type

push

github

Committed by

manticore-projects

Commit Message

feat: Adopt JSQLParser 5 features

Run Details

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

17 existing lines in 1 file now uncovered.

314 of 447 relevant lines covered (70.25%)

0.7 hits per line

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

73.49

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

/**
 * Copyright (C) 2024 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 Boolean) {
            return (Boolean) o ? "TRUE" : "FALSE";
        } 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() {
            @Override
            public <S> StringBuilder visit(JdbcNamedParameter parameter, S context) {
                buffer.append(getParameterStr(parameters.get(parameter.getName())));
                return buffer;
            }
        };

        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;

            @Override
            public <S> StringBuilder visit(JdbcParameter parameter, S context) {
                buffer.append(getParameterStr(parameters[i]));
                i++;
                return buffer;
            }

            @Override
            public <S> StringBuilder visit(JdbcNamedParameter parameter, S context) {
                buffer.append(getParameterStr(parameters[i]));
                i++;
                return buffer;
            }
        };

        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) 2024 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
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	×
76	.ofEpochMilli(d.getTime())	×
77	.atZone(ZoneId.systemDefault())	×
78	.toLocalDate();	×
79	return "{d '" + MJdbcTools.SQL_DATE_FORMAT.format(localDate) + "'}";	×
80	}
81
82	public static String getSQLDateStr(Calendar c) {
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) {
94	LocalDateTime localDateTime = ts.toInstant()	×
95	.atZone(ZoneId.systemDefault())	×
96	.toLocalDateTime();	×
97	return "{ts '" + MJdbcTools.SQL_TIMESTAMP_FORMAT.format(localDateTime) + "'}";	×
98	}
99
100	public static String getSQLDateTimeStr(Calendar c) {
101	return getSQLDateTimeStr(c.getTime());	×
102	}
103
104	public static String getSQLHash(String sqlStr)
105	throws JSQLParserException, NoSuchAlgorithmException {
106	MessageDigest messageDigest = MessageDigest.getInstance("SHA-256");	×
107
108	net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(sqlStr);	×
109
110	messageDigest.update(statement.toString().getBytes(Charset.defaultCharset()));	×
111	String hashStr = new String(messageDigest.digest(), Charset.defaultCharset());	×
112
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	×
127	&& cal.get(Calendar.MINUTE) == 0	×
128	&& cal.get(Calendar.HOUR_OF_DAY) == 0) {	×
129	LocalDate localDate = Instant	×
130	.ofEpochMilli(((Date) o).getTime())	×
131	.atZone(ZoneId.systemDefault())	×
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;	×
143	if (cal.get(Calendar.MILLISECOND) == 0	×
144	&& cal.get(Calendar.SECOND) == 0	×
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())	×
150	.toLocalDate();	×
151
152	return "{d '" + SQL_DATE_FORMAT.format(localDate) + "'}";	×
153	} else {
154	LocalDateTime localDateTime = cal.toInstant()	×
155	.atZone(ZoneId.systemDefault())	×
156	.toLocalDateTime();	×
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✔
168	return o.toString();	×
169	} else if (o instanceof Float) {	1✔
170	return o.toString();	×
171	} else if (o instanceof BigInteger) {	1✔
172	return o.toString();	×
173	} else if (o instanceof BigDecimal) {	1✔
174	return ((BigDecimal) o).toPlainString();	×
175	} else if (o instanceof Boolean) {	1✔
176	return (Boolean) o ? "TRUE" : "FALSE";	1✔
177	} else if (o instanceof String) {	1✔
178	String s = (String) o;	1✔
179	s = s.replace("'", "''");	1✔
180	s = s.replace("&", "' \|\| chr(38) \|\| '");	1✔
181	return "'" + s + "'";	1✔
182	} else {
UNCOV 183	return "'" + o + "'";	×
184	}
185	}
186
187	public static String rewriteStatementWithNamedParameters(String sqlStr,
188	Map<String, Object> parameters) throws Exception {
189	net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(sqlStr);	1✔
190	StringBuilder builder = new StringBuilder();	1✔
191	ExpressionDeParser expressionDeParser = new ExpressionDeParser() {	1✔
192	@Override
193	public <S> StringBuilder visit(JdbcNamedParameter parameter, S context) {
194	buffer.append(getParameterStr(parameters.get(parameter.getName())));	1✔
195	return buffer;	1✔
196	}
197	};
198
199	SelectDeParser selectDeParser = new SelectDeParser(expressionDeParser, builder);	1✔
200	expressionDeParser.setSelectVisitor(selectDeParser);	1✔
201	expressionDeParser.setBuffer(builder);	1✔
202
203	StatementDeParser statementDeParser =	1✔
204	new StatementDeParser(expressionDeParser, selectDeParser, builder);
205	statement.accept(statementDeParser);	1✔
206
207	return builder.toString();	1✔
208	}
209
210	public static String rewriteStatementWithNamedParameters(String sqlStr, Object... parameters)
211	throws Exception {
212	net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(sqlStr);	1✔
213	StringBuilder builder = new StringBuilder();	1✔
214	ExpressionDeParser expressionDeParser = new ExpressionDeParser() {	1✔
215	int i = 0;	1✔
216
217	@Override
218	public <S> StringBuilder visit(JdbcParameter parameter, S context) {
UNCOV 219	buffer.append(getParameterStr(parameters[i]));	×
UNCOV 220	i++;	×
UNCOV 221	return buffer;	×
222	}
223
224	@Override
225	public <S> StringBuilder visit(JdbcNamedParameter parameter, S context) {
226	buffer.append(getParameterStr(parameters[i]));	1✔
227	i++;	1✔
228	return buffer;	1✔
229	}
230	};
231
232	SelectDeParser selectDeParser = new SelectDeParser(expressionDeParser, builder);	1✔
233	expressionDeParser.setSelectVisitor(selectDeParser);	1✔
234	expressionDeParser.setBuffer(builder);	1✔
235
236	StatementDeParser statementDeParser =	1✔
237	new StatementDeParser(expressionDeParser, selectDeParser, builder);
238	statement.accept(statementDeParser);	1✔
239
240	return builder.toString();	1✔
241	}
242
243	public enum AggregateFunction {	1✔
244	SUM, COUNT	1✔
245	}
246
247	private static TreeMap<Object, Object> emptyMapFromKeys(TreeSet<Object> keys) {
248	TreeMap<Object, Object> map = new TreeMap<>();	1✔
249	for (Object k : keys) {	1✔
250	map.put(k, null);	1✔
251	}	1✔
252	return map;	1✔
253	}
254
255	private static class ObjectArrayKey implements Comparable<ObjectArrayKey> {
256	private final Comparable[] keys;
257
258	public ObjectArrayKey(Object[] keys) {	1✔
259	this.keys = new Comparable[keys.length];	1✔
260	for (int i = 0; i < keys.length; i++) {	1✔
261	this.keys[i] = (Comparable<?>) keys[i];	1✔
262	}
263	}	1✔
264
265	@Override
266	public boolean equals(Object o) {
UNCOV 267	if (this == o) {	×
UNCOV 268	return true;	×
UNCOV 269	} else if (o == null \|\| getClass() != o.getClass()) {	×
UNCOV 270	return false;	×
271	}
UNCOV 272	ObjectArrayKey that = (ObjectArrayKey) o;	×
273
UNCOV 274	return Arrays.deepEquals(keys, that.keys);	×
275	}
276
277	@Override
278	public int hashCode() {
UNCOV 279	return Arrays.hashCode(keys);	×
280	}
281
282	@Override
283	public int compareTo(ObjectArrayKey other) {
284	// Assuming both arrays have the same length
285	for (int i = 0; i < keys.length; i++) {	1✔
286	int elementComparison = keys[i].compareTo(other.keys[i]);	1✔
287	if (elementComparison != 0) {	1✔
288	return elementComparison;	1✔
289	}
290	}
291
292	// If all elements are equal, the arrays are considered equal
293	return 0;	1✔
294	}
295	}
296
297	/**
298	* The getPivotFromQuery function takes a ResultSet and converts it into a pivot table. The
299	* function is designed to create columns for each key of the Category Column and to aggregate
300	* the values of the Aggregate Column for each Category.
301	*
302	* @param rs The ResultSet holding the source data with the category values in rows
303	* @param function Determine what type of aggregate function to use (SUM, COUNT, ...)
304	* @param aggregateColumnName Specify the column name of the aggregate value
305	* @param categoryColumnName Identify the column that will be transformed into separate Value
306	* Columns
307	* @param categoryFormat Format the key values into column labels
308	* @param buildTotals If to insert Total rows below and column on the right side
309	* @param repeatHeader If to insert the header repeatedly before each category
310	* @return A 2-dimensional array holding the transformed Column Names and the Data
311	*/
312	@SuppressWarnings({"PMD.ExcessiveMethodLength"})
313	public static Object[][] getPivotFromQuery(ResultSet rs, AggregateFunction function,
314	String aggregateColumnName, String categoryColumnName, Format categoryFormat,
315	boolean buildTotals, boolean repeatHeader) throws SQLException {
316	ArrayList<String> columnNames = new ArrayList<>();	1✔
317	int categoryColumnIndex = -1;	1✔
318	Class<?> categoryClass = null;	1✔
319	int aggregateColumnIndex = -1;	1✔
320	Class<?> aggregateClass = null;	1✔
321
322	ResultSetMetaData metaData = rs.getMetaData();	1✔
323	int columnCount = metaData.getColumnCount();	1✔
324	for (int i = 1; i <= columnCount; i++) {	1✔
325	String columnName = metaData.getColumnName(i);	1✔
326	if (columnName.equalsIgnoreCase(categoryColumnName)) {	1✔
327	try {
328	categoryColumnIndex = i;	1✔
329	categoryClass = Class.forName(metaData.getColumnClassName(i));	1✔
UNCOV 330	} catch (ClassNotFoundException ignore) {	×
331	// do nothing
332	}	1✔
333	} else if (columnName.equalsIgnoreCase(aggregateColumnName)) {	1✔
334	try {
335	aggregateColumnIndex = i;	1✔
336	aggregateClass = Class.forName(metaData.getColumnClassName(i));	1✔
UNCOV 337	} catch (ClassNotFoundException ignore) {	×
338	// do nothing
339	}	1✔
340	} else {
341	columnNames.add(columnName);	1✔
342	}
343	}
344
345	if (categoryClass == null \|\| aggregateClass == null) {	1✔
UNCOV 346	throw new RuntimeException("Unable to determine the Aggregate or Category Classes.");	×
347	}
348
349
350	TreeSet<Object> keys = new TreeSet<>();	1✔
351	TreeMap<ObjectArrayKey, TreeMap<Object, Object>> data = new TreeMap<>();	1✔
352	ArrayList<Object> columnValues = new ArrayList<>();	1✔
353	Object keyValue;
354	Object aggregateValue;
355	while (rs.next()) {	1✔
356	columnValues.clear();	1✔
357	keyValue = null;	1✔
358	aggregateValue = null;	1✔
359
360	for (int i = 1; i <= columnCount; i++) {	1✔
361	Object value = rs.getObject(i);	1✔
362
363	if (i == categoryColumnIndex) {	1✔
364	keyValue = value;	1✔
365	// if we meet the key first time, we need to register it and extend the
366	// aggregated data maps
367	if (!keys.contains(keyValue)) {	1✔
368	keys.add(keyValue);	1✔
369	for (TreeMap<Object, Object> aggregatedData : data.values()) {	1✔
370	aggregatedData.put(keyValue, null);	1✔
371	}	1✔
372	}
373	} else if (i == aggregateColumnIndex) {	1✔
374	aggregateValue = value;	1✔
375	} else {
376	columnValues.add(rs.getObject(i));	1✔
377	}
378	}
379
380	ObjectArrayKey objectKey = new ObjectArrayKey(columnValues.toArray());	1✔
381
382	final TreeMap<Object, Object> rowData =	1✔
383	data.getOrDefault(objectKey, emptyMapFromKeys(keys));	1✔
384	if (aggregateClass.equals(BigDecimal.class)) {	1✔
385	BigDecimal a = (BigDecimal) rowData.get(keyValue);	1✔
386	BigDecimal b = (BigDecimal) aggregateValue;	1✔
387
388	if (function == AggregateFunction.SUM) {	1✔
389	if (a == null) {	1✔
390	rowData.put(keyValue, b);	1✔
391	} else if (b != null) {	×
UNCOV 392	rowData.put(keyValue, a.add(b));	×
393	}
394	} else {
UNCOV 395	throw new UnsupportedOperationException(	×
396	"Only SUM is supported right now, sorry!");
397	}
398	} else {	1✔
UNCOV 399	throw new UnsupportedOperationException(	×
400	"Only BigDecimals are supported right now, sorry!");
401	}
402
403	data.put(objectKey, rowData);	1✔
404	}	1✔
405
406	// build the final data cube
407	for (Object k : keys) {	1✔
408	columnNames.add(categoryFormat != null ? categoryFormat.format(k) : k.toString());	1✔
409	}	1✔
410	if (buildTotals) {	1✔
411	columnNames.add("Total");	1✔
412	}
413
414	ArrayList<Object[]> resultData = new ArrayList<>();	1✔
415	BigDecimal[] subTotals = new BigDecimal[keys.size() + 1];	1✔
416	Arrays.fill(subTotals, BigDecimal.ZERO);	1✔
417
418	int r = 0;	1✔
419	Object mainCategory = null;	1✔
420	for (Map.Entry<ObjectArrayKey, TreeMap<Object, Object>> e : data.entrySet()) {	1✔
421	Object[] rowData = new Object[columnNames.size()];	1✔
422
423	int c = 0;	1✔
424	for (Object k : e.getKey().keys) {	1✔
425	rowData[c] = k;	1✔
426	c++;	1✔
427	}
428
429	if (buildTotals && !rowData[0].equals(mainCategory)) {	1✔
430	if (mainCategory != null) {	1✔
431	Object[] totalRowData = new Object[columnNames.size()];	1✔
432	totalRowData[0] = mainCategory;	1✔
433	totalRowData[e.getKey().keys.length - 2] = "Total";	1✔
434
435	System.arraycopy(subTotals, 0, totalRowData, e.getKey().keys.length,	1✔
436	keys.size() + 1);	1✔
437	resultData.add(totalRowData);	1✔
438	resultData.add(new Object[columnNames.size()]);	1✔
439	}
440
441	mainCategory = rowData[0];	1✔
442	Arrays.fill(subTotals, BigDecimal.ZERO);	1✔
443	}
444
445	BigDecimal rowTotal = BigDecimal.ZERO;	1✔
446	int c1 = 0;	1✔
447	for (Object k : keys) {	1✔
448	rowData[c] = e.getValue().get(k);	1✔
449
450	if (buildTotals && rowData[c] != null) {	1✔
451	rowTotal = rowTotal.add((BigDecimal) rowData[c]);	1✔
452	subTotals[c1] = subTotals[c1].add((BigDecimal) rowData[c]);	1✔
453	}
454	c++;	1✔
455	c1++;	1✔
456	}	1✔
457	if (buildTotals) {	1✔
458	rowData[c] = rowTotal;	1✔
459	subTotals[c1] = subTotals[c1].add(rowTotal);	1✔
460	}
461
462	resultData.add(rowData);	1✔
463	r++;	1✔
464
465	// totals after the last row
466	if (buildTotals && r == data.size()) {	1✔
467	Object[] totalRowData = new Object[columnNames.size()];	1✔
468	totalRowData[0] = mainCategory;	1✔
469	totalRowData[e.getKey().keys.length - 2] = "Total";	1✔
470	System.arraycopy(subTotals, 0, totalRowData, e.getKey().keys.length,	1✔
471	keys.size() + 1);	1✔
472	resultData.add(totalRowData);	1✔
473	}
474	}	1✔
475
476	return new Object[][] {	1✔
477	columnNames.toArray(new String[columnNames.size()]),	1✔
478	resultData.toArray(new Object[resultData.size()][])	1✔
479	};
480	}
481
482	}

manticore-projects / MJdbcUtils / #5

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