24722773057

Committed 21 Apr 2026 12:38PM UTC coverage: 65.217% (-0.01%) from 65.229%

Build # 24722773057

Build Type

push

github

Committed by

web-flow

Commit Message

TRUNK-6481: Backport native support for MariaDB  (#5648)

Coverage Stats

2 of 14 new or added lines in 4 files covered. (14.29%)

8 existing lines in 5 files now uncovered.

23613 of 36207 relevant lines covered (65.22%)

0.65 hits per line

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

60.98

/api/src/main/java/org/openmrs/util/DatabaseUtil.java

/**
 * This Source Code Form is subject to the terms of the Mozilla Public License,
 * v. 2.0. If a copy of the MPL was not distributed with this file, You can
 * obtain one at http://mozilla.org/MPL/2.0/. OpenMRS is also distributed under
 * the terms of the Healthcare Disclaimer located at http://openmrs.org/license.
 *
 * Copyright (C) OpenMRS Inc. OpenMRS is a registered trademark and the OpenMRS
 * graphic logo is a trademark of OpenMRS Inc.
 */
package org.openmrs.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.hibernate.Session;
import org.openmrs.api.db.DAOException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

/**
 * Utility class that provides database related methods
 *
 * @since 1.6
 */
public class DatabaseUtil {

        private DatabaseUtil() {
        }
        
        private static final Logger log = LoggerFactory.getLogger(DatabaseUtil.class);

        public static final String ORDER_ENTRY_UPGRADE_SETTINGS_FILENAME = "order_entry_upgrade_settings.txt";

        /**
         * Executes the passed SQL query, enforcing select only if that parameter is set Load the jdbc
         * driver class for the database which is specified by the connectionUrl and connectionDriver
         * parameters <br>
         * <br>
         * This is only needed when loading up a jdbc connection manually for the first time. This is
         * not needed by most users and development practices with the openmrs API.
         *
         * @param connectionUrl the connection url for the database, such as
         * "jdbc:mysql://localhost:3306/..."
         * @param connectionDriver the database driver class name, such as "com.mysql.cj.jdbc.Driver"
         * @throws ClassNotFoundException
         */
        public static String loadDatabaseDriver(String connectionUrl, String connectionDriver) throws ClassNotFoundException {
                if (StringUtils.hasText(connectionDriver)) {
                        Class.forName(connectionDriver);
                        log.debug("set user defined Database driver class: " + connectionDriver);
                } else {
                        if (connectionUrl.contains("jdbc:mysql")) {
                                Class.forName("com.mysql.cj.jdbc.Driver");
                                connectionDriver = "com.mysql.cj.jdbc.Driver";
                        } else if (connectionUrl.contains("jdbc:mariadb")) {
                                Class.forName("org.mariadb.jdbc.Driver");
                                connectionDriver = "org.mariadb.jdbc.Driver";
                        } else if (connectionUrl.contains("jdbc:hsqldb")) {
                                Class.forName("org.hsqldb.jdbcDriver");
                                connectionDriver = "org.hsqldb.jdbcDriver";
                        } else if (connectionUrl.contains("jdbc:postgresql")) {
                                Class.forName("org.postgresql.Driver");
                                connectionDriver = "org.postgresql.Driver";
                        } else if (connectionUrl.contains("jdbc:oracle")) {
                                Class.forName("oracle.jdbc.driver.OracleDriver");
                                connectionDriver = "oracle.jdbc.driver.OracleDriver";
                        } else if (connectionUrl.contains("jdbc:jtds")) {
                                Class.forName("net.sourceforge.jtds.jdbc.Driver");
                                connectionDriver = "net.sourceforge.jtds.jdbc.Driver";
                        } else if (connectionUrl.contains("sqlserver")) {
                                Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
                                connectionDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
                        }
                }
                log.info("Set database driver class as " + connectionDriver);
                return connectionDriver;
        }
        
        /**
         * Executes the passed SQL query, enforcing select only if that parameter is set for given Session
         */
        public static List<List<Object>> executeSQL(Session session, String sql, boolean selectOnly) throws DAOException {
                sql = sql.trim();
                boolean dataManipulation = checkQueryForManipulationCommands(sql, selectOnly);
                
                final List<List<Object>> result = new ArrayList<>();
                final String query = sql;
                final boolean sessionDataManipulation = dataManipulation;
                
                session.doWork(conn -> populateResultsFromSQLQuery(conn, query, sessionDataManipulation, result));
                
                return result;
        }
        
        /**
         * Executes the passed SQL query, enforcing select only if that parameter is set for given Connection
         */
        public static List<List<Object>> executeSQL(Connection conn, String sql, boolean selectOnly) throws DAOException {
                sql = sql.trim();
                boolean dataManipulation = checkQueryForManipulationCommands(sql, selectOnly);
                List<List<Object>> result = new ArrayList<>();
                populateResultsFromSQLQuery(conn, sql, dataManipulation, result);
                return result;
        }
        
        private static boolean checkQueryForManipulationCommands(String sql, boolean selectOnly) {
                boolean dataManipulation = false;
                
                String sqlLower = sql.toLowerCase();
                if (sqlLower.startsWith("insert") || sqlLower.startsWith("update") || sqlLower.startsWith("delete")
                        || sqlLower.startsWith("alter") || sqlLower.startsWith("drop") || sqlLower.startsWith("create")
                        || sqlLower.startsWith("rename")) {
                        dataManipulation = true;
                }
                
                if (selectOnly && dataManipulation) {
                        throw new IllegalArgumentException("Illegal command(s) found in query string");
                }
                return dataManipulation;
        }
        
        private static void populateResultsFromSQLQuery(Connection conn, String sql, boolean dataManipulation,
                List<List<Object>> results) {
                PreparedStatement ps = null;
                try {
                        ps = conn.prepareStatement(sql);
                        if (dataManipulation) {
                                Integer i = ps.executeUpdate();
                                List<Object> row = new ArrayList<>();
                                row.add(i);
                                results.add(row);
                        } else {
                                ResultSet resultSet = ps.executeQuery();
                                
                                ResultSetMetaData rmd = resultSet.getMetaData();
                                int columnCount = rmd.getColumnCount();
                                
                                while (resultSet.next()) {
                                        List<Object> rowObjects = new ArrayList<>();
                                        for (int x = 1; x <= columnCount; x++) {
                                                rowObjects.add(resultSet.getObject(x));
                                        }
                                        results.add(rowObjects);
                                }
                        }
                }
                catch (Exception e) {
                        log.debug("Error while running sql: " + sql, e);
                        throw new DAOException("Error while running sql: " + sql + " . Message: " + e.getMessage(), e);
                }
                finally {
                        if (ps != null) {
                                try {
                                        ps.close();
                                }
                                catch (SQLException e) {
                                        log.error("Error generated while closing statement", e);
                                }
                        }
                }
        }
        
        /**
         * Gets all unique values excluding nulls in the specified column and table
         *
         * @param columnName the column
         * @param tableName  the table
         * @param connection
         * @return set of unique values
         * @throws Exception
         */
        public static <T> Set<T> getUniqueNonNullColumnValues(String columnName, String tableName, Class<T> type,
                Connection connection) throws Exception {
                Set<T> uniqueValues = new HashSet<>();
                final String alias = "unique_values";
                String select = "SELECT DISTINCT " + columnName + " AS " + alias + " FROM " + tableName + " WHERE " + columnName
                        + " IS NOT NULL";
                List<List<Object>> rows = DatabaseUtil.executeSQL(connection, select, true);
                for (List<Object> row : rows) {
                        //There can only be one column since we are selecting one
                        uniqueValues.add((T) row.get(0));
                }
                
                return uniqueValues;
        }
}

1	/**
2	* This Source Code Form is subject to the terms of the Mozilla Public License,
3	* v. 2.0. If a copy of the MPL was not distributed with this file, You can
4	* obtain one at http://mozilla.org/MPL/2.0/. OpenMRS is also distributed under
5	* the terms of the Healthcare Disclaimer located at http://openmrs.org/license.
6	*
7	* Copyright (C) OpenMRS Inc. OpenMRS is a registered trademark and the OpenMRS
8	* graphic logo is a trademark of OpenMRS Inc.
9	*/
10	package org.openmrs.util;
11
12	import java.sql.Connection;
13	import java.sql.PreparedStatement;
14	import java.sql.ResultSet;
15	import java.sql.ResultSetMetaData;
16	import java.sql.SQLException;
17	import java.util.ArrayList;
18	import java.util.HashSet;
19	import java.util.List;
20	import java.util.Set;
21
22	import org.hibernate.Session;
23	import org.openmrs.api.db.DAOException;
24	import org.slf4j.Logger;
25	import org.slf4j.LoggerFactory;
26	import org.springframework.util.StringUtils;
27
28	/**
29	* Utility class that provides database related methods
30	*
31	* @since 1.6
32	*/
33	public class DatabaseUtil {
34
35	private DatabaseUtil() {
36	}
37
38	private static final Logger log = LoggerFactory.getLogger(DatabaseUtil.class);	1✔
39
40	public static final String ORDER_ENTRY_UPGRADE_SETTINGS_FILENAME = "order_entry_upgrade_settings.txt";
41
42	/**
43	* Executes the passed SQL query, enforcing select only if that parameter is set Load the jdbc
44	* driver class for the database which is specified by the connectionUrl and connectionDriver
45	* parameters <br>
46	* <br>
47	* This is only needed when loading up a jdbc connection manually for the first time. This is
48	* not needed by most users and development practices with the openmrs API.
49	*
50	* @param connectionUrl the connection url for the database, such as
51	* "jdbc:mysql://localhost:3306/..."
52	* @param connectionDriver the database driver class name, such as "com.mysql.cj.jdbc.Driver"
53	* @throws ClassNotFoundException
54	*/
55	public static String loadDatabaseDriver(String connectionUrl, String connectionDriver) throws ClassNotFoundException {
56	if (StringUtils.hasText(connectionDriver)) {	×
57	Class.forName(connectionDriver);	×
58	log.debug("set user defined Database driver class: " + connectionDriver);	×
59	} else {
NEW 60	if (connectionUrl.contains("jdbc:mysql")) {	×
61	Class.forName("com.mysql.cj.jdbc.Driver");	×
62	connectionDriver = "com.mysql.cj.jdbc.Driver";	×
NEW 63	} else if (connectionUrl.contains("jdbc:mariadb")) {	×
NEW 64	Class.forName("org.mariadb.jdbc.Driver");	×
NEW 65	connectionDriver = "org.mariadb.jdbc.Driver";	×
NEW 66	} else if (connectionUrl.contains("jdbc:hsqldb")) {	×
67	Class.forName("org.hsqldb.jdbcDriver");	×
68	connectionDriver = "org.hsqldb.jdbcDriver";	×
NEW 69	} else if (connectionUrl.contains("jdbc:postgresql")) {	×
70	Class.forName("org.postgresql.Driver");	×
71	connectionDriver = "org.postgresql.Driver";	×
NEW 72	} else if (connectionUrl.contains("jdbc:oracle")) {	×
73	Class.forName("oracle.jdbc.driver.OracleDriver");	×
74	connectionDriver = "oracle.jdbc.driver.OracleDriver";	×
NEW 75	} else if (connectionUrl.contains("jdbc:jtds")) {	×
76	Class.forName("net.sourceforge.jtds.jdbc.Driver");	×
77	connectionDriver = "net.sourceforge.jtds.jdbc.Driver";	×
78	} else if (connectionUrl.contains("sqlserver")) {	×
79	Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");	×
80	connectionDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";	×
81	}
82	}
83	log.info("Set database driver class as " + connectionDriver);	×
84	return connectionDriver;	×
85	}
86
87	/**
88	* Executes the passed SQL query, enforcing select only if that parameter is set for given Session
89	*/
90	public static List<List<Object>> executeSQL(Session session, String sql, boolean selectOnly) throws DAOException {
91	sql = sql.trim();	1✔
92	boolean dataManipulation = checkQueryForManipulationCommands(sql, selectOnly);	1✔
93
94	final List<List<Object>> result = new ArrayList<>();	1✔
95	final String query = sql;	1✔
96	final boolean sessionDataManipulation = dataManipulation;	1✔
97
98	session.doWork(conn -> populateResultsFromSQLQuery(conn, query, sessionDataManipulation, result));	1✔
99
100	return result;	1✔
101	}
102
103	/**
104	* Executes the passed SQL query, enforcing select only if that parameter is set for given Connection
105	*/
106	public static List<List<Object>> executeSQL(Connection conn, String sql, boolean selectOnly) throws DAOException {
107	sql = sql.trim();	1✔
108	boolean dataManipulation = checkQueryForManipulationCommands(sql, selectOnly);	1✔
109	List<List<Object>> result = new ArrayList<>();	1✔
110	populateResultsFromSQLQuery(conn, sql, dataManipulation, result);	1✔
111	return result;	1✔
112	}
113
114	private static boolean checkQueryForManipulationCommands(String sql, boolean selectOnly) {
115	boolean dataManipulation = false;	1✔
116
117	String sqlLower = sql.toLowerCase();	1✔
118	if (sqlLower.startsWith("insert") \|\| sqlLower.startsWith("update") \|\| sqlLower.startsWith("delete")	1✔
119	\|\| sqlLower.startsWith("alter") \|\| sqlLower.startsWith("drop") \|\| sqlLower.startsWith("create")	1✔
120	\|\| sqlLower.startsWith("rename")) {	1✔
121	dataManipulation = true;	1✔
122	}
123
124	if (selectOnly && dataManipulation) {	1✔
125	throw new IllegalArgumentException("Illegal command(s) found in query string");	×
126	}
127	return dataManipulation;	1✔
128	}
129
130	private static void populateResultsFromSQLQuery(Connection conn, String sql, boolean dataManipulation,
131	List<List<Object>> results) {
132	PreparedStatement ps = null;	1✔
133	try {
134	ps = conn.prepareStatement(sql);	1✔
135	if (dataManipulation) {	1✔
136	Integer i = ps.executeUpdate();	1✔
137	List<Object> row = new ArrayList<>();	1✔
138	row.add(i);	1✔
139	results.add(row);	1✔
140	} else {	1✔
141	ResultSet resultSet = ps.executeQuery();	1✔
142
143	ResultSetMetaData rmd = resultSet.getMetaData();	1✔
144	int columnCount = rmd.getColumnCount();	1✔
145
146	while (resultSet.next()) {	1✔
147	List<Object> rowObjects = new ArrayList<>();	1✔
148	for (int x = 1; x <= columnCount; x++) {	1✔
149	rowObjects.add(resultSet.getObject(x));	1✔
150	}
151	results.add(rowObjects);	1✔
152	}	1✔
153	}
154	}
155	catch (Exception e) {	×
156	log.debug("Error while running sql: " + sql, e);	×
157	throw new DAOException("Error while running sql: " + sql + " . Message: " + e.getMessage(), e);	×
158	}
159	finally {
160	if (ps != null) {	1✔
161	try {
162	ps.close();	1✔
163	}
164	catch (SQLException e) {	×
165	log.error("Error generated while closing statement", e);	×
166	}	1✔
167	}
168	}
169	}	1✔
170
171	/**
172	* Gets all unique values excluding nulls in the specified column and table
173	*
174	* @param columnName the column
175	* @param tableName the table
176	* @param connection
177	* @return set of unique values
178	* @throws Exception
179	*/
180	public static <T> Set<T> getUniqueNonNullColumnValues(String columnName, String tableName, Class<T> type,
181	Connection connection) throws Exception {
182	Set<T> uniqueValues = new HashSet<>();	1✔
183	final String alias = "unique_values";	1✔
184	String select = "SELECT DISTINCT " + columnName + " AS " + alias + " FROM " + tableName + " WHERE " + columnName	1✔
185	+ " IS NOT NULL";
186	List<List<Object>> rows = DatabaseUtil.executeSQL(connection, select, true);	1✔
187	for (List<Object> row : rows) {	1✔
188	//There can only be one column since we are selecting one
189	uniqueValues.add((T) row.get(0));	1✔
190	}	1✔
191
192	return uniqueValues;	1✔
193	}
194	}

openmrs / openmrs-core / 24722773057

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