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

75.52

/src/main/java/com/manticore/jdbc/MPreparedStatement.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 org.apache.commons.collections4.map.CaseInsensitiveMap;

import java.io.ByteArrayInputStream;
import java.io.Closeable;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Calendar;
import java.util.Comparator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

public final class MPreparedStatement implements Closeable {
    private final static int DEFAULT_BATCH_SIZE = 24;
    private final PreparedStatement statement;
    private final ParameterMetaData parameterMetaData;
    private final String sqlStr;
    private final CaseInsensitiveMap<String, MNamedParameter> parameters =
            new CaseInsensitiveMap<>();

    private long recordCount = 0;
    private final int batchSize;

    private String rewriteSqlStr(String sqlStr) throws JSQLParserException {
        net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(sqlStr);
        StringBuilder builder = new StringBuilder();
        ExpressionDeParser expressionDeParser = new ExpressionDeParser() {
            int i = 1;

            @Override
            public <S> StringBuilder visit(JdbcParameter parameter, S context) {
                String id = ":" + (i + 1);
                if (!parameters.containsKey(id)) {
                    parameters.put(id, new MNamedParameter(id, i));
                } else {
                    parameters.get(id).add(i);
                }

                buffer.append("?");
                i++;
                return buffer;
            }

            @Override
            public <S> StringBuilder visit(JdbcNamedParameter parameter, S context) {
                String id = parameter.getName();
                if (!parameters.containsKey(id)) {
                    parameters.put(id, new MNamedParameter(id, i));
                } else {
                    parameters.get(id).add(i);
                }

                buffer.append("?");
                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();
    }

    private LinkedList<Object> getParamArr(Map<String, Object> parameterValues) {
        LinkedList<Object> objects = new LinkedList<>();
        for (MNamedParameter p : parameters.values()) {
            for (Integer position : p.getPositions()) {
                while (objects.size() < position) {
                    objects.add(null);
                }
                objects.set(position - 1, parameterValues.get(p.getId()));
            }
        }
        return objects;
    }

    public MPreparedStatement(Connection conn, String sqlStr, int batchSize)
            throws SQLException, JSQLParserException {
        this.sqlStr = rewriteSqlStr(sqlStr);
        this.batchSize = batchSize;
        statement = conn.prepareStatement(this.sqlStr);
        parameterMetaData = statement.getParameterMetaData();

        setParameterTypes();
    }

    public MPreparedStatement(Connection conn, String sqlStr)
            throws SQLException, JSQLParserException {
        this(conn, sqlStr, DEFAULT_BATCH_SIZE);
    }

    private void setParameters(Map<String, Object> parameterValues) throws SQLException {
        setParameters(getParamArr(parameterValues).toArray());
    }

    private void setParameters(Object... parameterValues) throws SQLException {
        statement.clearParameters();
        int parameterIndex = 0;
        for (Object o : parameterValues) {
            parameterIndex++;
            try {
                int parameterType = parameterMetaData.getParameterType(parameterIndex);
                switch (parameterType) {
                    case Types.TIMESTAMP:
                        if (o instanceof java.util.Date) {
                            java.util.Date date = (java.util.Date) o;
                            statement.setTimestamp(parameterIndex,
                                    MJdbcTools.getSQLTimestamp(date));
                        } else if (o instanceof Calendar) {
                            Calendar calendar = (Calendar) o;
                            statement.setTimestamp(parameterIndex,
                                    MJdbcTools.getSQLTimestamp(calendar));
                        } else {
                            statement.setObject(parameterIndex, o);
                        }
                        break;
                    case Types.DATE:
                        if (o instanceof java.util.Date) {
                            java.util.Date date = (java.util.Date) o;
                            statement.setDate(parameterIndex, MJdbcTools.getSQLDate(date));
                        } else if (o instanceof Calendar) {
                            Calendar calendar = (Calendar) o;
                            statement.setDate(parameterIndex, MJdbcTools.getSQLDate(calendar));
                        } else {
                            statement.setObject(parameterIndex, o);
                        }
                        break;
                    case Types.BINARY:
                    case Types.BLOB:
                    case Types.LONGVARBINARY:
                        if (o instanceof byte[]) {
                            byte[] bytes = (byte[]) o;
                            statement.setBinaryStream(parameterIndex,
                                    new ByteArrayInputStream(bytes), bytes.length);
                        } else {
                            statement.setObject(parameterIndex, o);
                        }
                        break;

                    // @todo: add more SQLType Mappings
                    default:
                        statement.setObject(parameterIndex, o);
                }
            } catch (Exception ignore) {
                statement.setObject(parameterIndex, o);
            }
        }
    }

    public boolean execute(Map<String, Object> parameterValues) throws SQLException {
        setParameters(parameterValues);
        return statement.execute();
    }

    public boolean execute(Object... parameterValues) throws SQLException {
        setParameters(parameterValues);
        return statement.execute();
    }

    public int executeUpdate(Map<String, Object> parameterValues) throws SQLException {
        setParameters(parameterValues);
        return statement.executeUpdate();
    }

    public int executeUpdate(Object... parameterValues) throws SQLException {
        setParameters(parameterValues);
        return statement.executeUpdate();
    }

    public ResultSet executeQuery(Map<String, Object> parameterValues) throws SQLException {
        setParameters(parameterValues);
        return statement.executeQuery();
    }

    public ResultSet executeQuery(Object... parameterValues) throws SQLException {
        setParameters(parameterValues);
        return statement.executeQuery();
    }

    public void close() {
        try {
            statement.close();
        } catch (Exception ignore) {
            // nothing we can do here
        }
    }

    public void cancel() throws SQLException {
        statement.cancel();
    }


    public void addBatch(Map<String, Object> parameterValues) throws SQLException {
        statement.clearParameters();
        setParameters(parameterValues);
        statement.addBatch();

        recordCount++;
    }

    public void addBatch(Object... parameterValues) throws SQLException {
        statement.clearParameters();
        setParameters(parameterValues);
        statement.addBatch();
    }

    public int[] executeBatch() throws SQLException {
        recordCount = 0;
        return statement.executeBatch();
    }

    public int[] addAndExecuteBatch(Map<String, Object> parameterValues) throws SQLException {
        addBatch(parameterValues);

        if (recordCount % batchSize == 0) {
            return executeBatch();
        } else {
            return new int[0];
        }
    }

    public void clearBatch() throws SQLException {
        recordCount = 0;
        statement.clearBatch();
    }

    public ResultSet getResultSet() throws SQLException {
        return statement.getResultSet();
    }

    public int getUpdateCount() throws SQLException {
        return statement.getUpdateCount();
    }

    private void setParameterTypes() throws SQLException {
        for (int i = 1; i <= parameterMetaData.getParameterCount(); i++) {
            for (MNamedParameter p : parameters.values()) {
                if (p.getPositions().first() == i) {
                    int type = parameterMetaData.getParameterType(i);
                    String typeName = parameterMetaData.getParameterTypeName(i);
                    int precision = parameterMetaData.getPrecision(i);
                    int scale = parameterMetaData.getScale(i);
                    int nullable = parameterMetaData.isNullable(i);
                    String className = parameterMetaData.getParameterClassName(i);

                    p.setType(type, typeName, className, precision, scale, nullable);
                }
            }
        }
    }

    public List<MNamedParameter> getNamedParametersByAppearance() {
        LinkedList<MNamedParameter> values = new LinkedList<>(parameters.values());
        Comparator<MNamedParameter> comparator = new Comparator<MNamedParameter>() {
            @Override
            public int compare(MNamedParameter o1, MNamedParameter o2) {
                return Integer.compare(o1.getPositions().first(), o2.getPositions().first());
            }
        };
        values.sort(comparator);
        return values;
    }

    public List<MNamedParameter> getNamedParametersByName() {
        LinkedList<MNamedParameter> values = new LinkedList<>(parameters.values());
        Comparator<MNamedParameter> comparator = new Comparator<MNamedParameter>() {
            @Override
            public int compare(MNamedParameter o1, MNamedParameter o2) {
                return o1.getId().compareToIgnoreCase(o2.getId());
            }
        };
        values.sort(comparator);
        return values;
    }
}

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	import org.apache.commons.collections4.map.CaseInsensitiveMap;
29
30	import java.io.ByteArrayInputStream;
31	import java.io.Closeable;
32	import java.sql.Connection;
33	import java.sql.ParameterMetaData;
34	import java.sql.PreparedStatement;
35	import java.sql.ResultSet;
36	import java.sql.SQLException;
37	import java.sql.Types;
38	import java.util.Calendar;
39	import java.util.Comparator;
40	import java.util.LinkedList;
41	import java.util.List;
42	import java.util.Map;
43
44	public final class MPreparedStatement implements Closeable {
45	private final static int DEFAULT_BATCH_SIZE = 24;
46	private final PreparedStatement statement;
47	private final ParameterMetaData parameterMetaData;
48	private final String sqlStr;
49	private final CaseInsensitiveMap<String, MNamedParameter> parameters =	1✔
50	new CaseInsensitiveMap<>();
51
52	private long recordCount = 0;	1✔
53	private final int batchSize;
54
55	private String rewriteSqlStr(String sqlStr) throws JSQLParserException {
56	net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(sqlStr);	1✔
57	StringBuilder builder = new StringBuilder();	1✔
58	ExpressionDeParser expressionDeParser = new ExpressionDeParser() {	1✔
59	int i = 1;	1✔
60
61	@Override
62	public <S> StringBuilder visit(JdbcParameter parameter, S context) {
63	String id = ":" + (i + 1);	×
64	if (!parameters.containsKey(id)) {	×
65	parameters.put(id, new MNamedParameter(id, i));	×
66	} else {
67	parameters.get(id).add(i);	×
68	}
69
70	buffer.append("?");	×
71	i++;	×
NEW 72	return buffer;	×
73	}
74
75	@Override
76	public <S> StringBuilder visit(JdbcNamedParameter parameter, S context) {
77	String id = parameter.getName();	1✔
78	if (!parameters.containsKey(id)) {	1✔
79	parameters.put(id, new MNamedParameter(id, i));	1✔
80	} else {
81	parameters.get(id).add(i);	×
82	}
83
84	buffer.append("?");	1✔
85	i++;	1✔
86
87	return buffer;	1✔
88	}
89	};
90
91	SelectDeParser selectDeParser = new SelectDeParser(expressionDeParser, builder);	1✔
92	expressionDeParser.setSelectVisitor(selectDeParser);	1✔
93	expressionDeParser.setBuffer(builder);	1✔
94
95	StatementDeParser statementDeParser =	1✔
96	new StatementDeParser(expressionDeParser, selectDeParser, builder);
97	statement.accept(statementDeParser);	1✔
98
99	return builder.toString();	1✔
100	}
101
102	private LinkedList<Object> getParamArr(Map<String, Object> parameterValues) {
103	LinkedList<Object> objects = new LinkedList<>();	1✔
104	for (MNamedParameter p : parameters.values()) {	1✔
105	for (Integer position : p.getPositions()) {	1✔
106	while (objects.size() < position) {	1✔
107	objects.add(null);	1✔
108	}
109	objects.set(position - 1, parameterValues.get(p.getId()));	1✔
110	}	1✔
111	}	1✔
112	return objects;	1✔
113	}
114
115	public MPreparedStatement(Connection conn, String sqlStr, int batchSize)
116	throws SQLException, JSQLParserException {	1✔
117	this.sqlStr = rewriteSqlStr(sqlStr);	1✔
118	this.batchSize = batchSize;	1✔
119	statement = conn.prepareStatement(this.sqlStr);	1✔
120	parameterMetaData = statement.getParameterMetaData();	1✔
121
122	setParameterTypes();	1✔
123	}	1✔
124
125	public MPreparedStatement(Connection conn, String sqlStr)
126	throws SQLException, JSQLParserException {
127	this(conn, sqlStr, DEFAULT_BATCH_SIZE);	1✔
128	}	1✔
129
130	private void setParameters(Map<String, Object> parameterValues) throws SQLException {
131	setParameters(getParamArr(parameterValues).toArray());	1✔
132	}	1✔
133
134	private void setParameters(Object... parameterValues) throws SQLException {
135	statement.clearParameters();	1✔
136	int parameterIndex = 0;	1✔
137	for (Object o : parameterValues) {	1✔
138	parameterIndex++;	1✔
139	try {
140	int parameterType = parameterMetaData.getParameterType(parameterIndex);	1✔
141	switch (parameterType) {	1✔
142	case Types.TIMESTAMP:
143	if (o instanceof java.util.Date) {	1✔
144	java.util.Date date = (java.util.Date) o;	1✔
145	statement.setTimestamp(parameterIndex,	1✔
146	MJdbcTools.getSQLTimestamp(date));	1✔
147	} else if (o instanceof Calendar) {	1✔
148	Calendar calendar = (Calendar) o;	1✔
149	statement.setTimestamp(parameterIndex,	1✔
150	MJdbcTools.getSQLTimestamp(calendar));	1✔
151	} else {	1✔
152	statement.setObject(parameterIndex, o);	1✔
153	}
154	break;	1✔
155	case Types.DATE:
156	if (o instanceof java.util.Date) {	1✔
157	java.util.Date date = (java.util.Date) o;	1✔
158	statement.setDate(parameterIndex, MJdbcTools.getSQLDate(date));	1✔
159	} else if (o instanceof Calendar) {	1✔
160	Calendar calendar = (Calendar) o;	1✔
161	statement.setDate(parameterIndex, MJdbcTools.getSQLDate(calendar));	1✔
162	} else {	1✔
163	statement.setObject(parameterIndex, o);	1✔
164	}
165	break;	1✔
166	case Types.BINARY:
167	case Types.BLOB:
168	case Types.LONGVARBINARY:
169	if (o instanceof byte[]) {	×
170	byte[] bytes = (byte[]) o;	×
171	statement.setBinaryStream(parameterIndex,	×
172	new ByteArrayInputStream(bytes), bytes.length);
173	} else {	×
174	statement.setObject(parameterIndex, o);	×
175	}
176	break;	×
177
178	// @todo: add more SQLType Mappings
179	default:
180	statement.setObject(parameterIndex, o);	1✔
181	}
182	} catch (Exception ignore) {	×
183	statement.setObject(parameterIndex, o);	×
184	}	1✔
185	}
186	}	1✔
187
188	public boolean execute(Map<String, Object> parameterValues) throws SQLException {
189	setParameters(parameterValues);	1✔
190	return statement.execute();	1✔
191	}
192
193	public boolean execute(Object... parameterValues) throws SQLException {
194	setParameters(parameterValues);	×
195	return statement.execute();	×
196	}
197
198	public int executeUpdate(Map<String, Object> parameterValues) throws SQLException {
199	setParameters(parameterValues);	×
200	return statement.executeUpdate();	×
201	}
202
203	public int executeUpdate(Object... parameterValues) throws SQLException {
204	setParameters(parameterValues);	×
205	return statement.executeUpdate();	×
206	}
207
208	public ResultSet executeQuery(Map<String, Object> parameterValues) throws SQLException {
209	setParameters(parameterValues);	1✔
210	return statement.executeQuery();	1✔
211	}
212
213	public ResultSet executeQuery(Object... parameterValues) throws SQLException {
214	setParameters(parameterValues);	×
215	return statement.executeQuery();	×
216	}
217
218	public void close() {
219	try {
220	statement.close();	1✔
221	} catch (Exception ignore) {	×
222	// nothing we can do here
223	}	1✔
224	}	1✔
225
226	public void cancel() throws SQLException {
227	statement.cancel();	×
228	}	×
229
230
231	public void addBatch(Map<String, Object> parameterValues) throws SQLException {
232	statement.clearParameters();	1✔
233	setParameters(parameterValues);	1✔
234	statement.addBatch();	1✔
235
236	recordCount++;	1✔
237	}	1✔
238
239	public void addBatch(Object... parameterValues) throws SQLException {
240	statement.clearParameters();	×
241	setParameters(parameterValues);	×
242	statement.addBatch();	×
243	}	×
244
245	public int[] executeBatch() throws SQLException {
246	recordCount = 0;	1✔
247	return statement.executeBatch();	1✔
248	}
249
250	public int[] addAndExecuteBatch(Map<String, Object> parameterValues) throws SQLException {
251	addBatch(parameterValues);	1✔
252
253	if (recordCount % batchSize == 0) {	1✔
254	return executeBatch();	1✔
255	} else {
256	return new int[0];	1✔
257	}
258	}
259
260	public void clearBatch() throws SQLException {
261	recordCount = 0;	×
262	statement.clearBatch();	×
263	}	×
264
265	public ResultSet getResultSet() throws SQLException {
266	return statement.getResultSet();	×
267	}
268
269	public int getUpdateCount() throws SQLException {
270	return statement.getUpdateCount();	1✔
271	}
272
273	private void setParameterTypes() throws SQLException {
274	for (int i = 1; i <= parameterMetaData.getParameterCount(); i++) {	1✔
275	for (MNamedParameter p : parameters.values()) {	1✔
276	if (p.getPositions().first() == i) {	1✔
277	int type = parameterMetaData.getParameterType(i);	1✔
278	String typeName = parameterMetaData.getParameterTypeName(i);	1✔
279	int precision = parameterMetaData.getPrecision(i);	1✔
280	int scale = parameterMetaData.getScale(i);	1✔
281	int nullable = parameterMetaData.isNullable(i);	1✔
282	String className = parameterMetaData.getParameterClassName(i);	1✔
283
284	p.setType(type, typeName, className, precision, scale, nullable);	1✔
285	}
286	}	1✔
287	}
288	}	1✔
289
290	public List<MNamedParameter> getNamedParametersByAppearance() {
291	LinkedList<MNamedParameter> values = new LinkedList<>(parameters.values());	1✔
292	Comparator<MNamedParameter> comparator = new Comparator<MNamedParameter>() {	1✔
293	@Override
294	public int compare(MNamedParameter o1, MNamedParameter o2) {
295	return Integer.compare(o1.getPositions().first(), o2.getPositions().first());	1✔
296	}
297	};
298	values.sort(comparator);	1✔
299	return values;	1✔
300	}
301
302	public List<MNamedParameter> getNamedParametersByName() {
303	LinkedList<MNamedParameter> values = new LinkedList<>(parameters.values());	1✔
304	Comparator<MNamedParameter> comparator = new Comparator<MNamedParameter>() {	1✔
305	@Override
306	public int compare(MNamedParameter o1, MNamedParameter o2) {
307	return o1.getId().compareToIgnoreCase(o2.getId());	1✔
308	}
309	};
310	values.sort(comparator);	1✔
311	return values;	1✔
312	}
313	}

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