MySQL database development guide for the HMIS project. Use when working with database queries, SQL debugging, database schema investigation, MySQL performance tuning, credential management, or database connection troubleshooting.
C:\Credentials\credentials.txt~/.config/hmis/credentials.txtSHOW TABLES LIKE '%bill%';
DESCRIBE bill;
SHOW CREATE TABLE bill;
SELECT COUNT(*) FROM bill WHERE retired = 0;
// Standard entity query
String jpql = "SELECT b FROM Bill b WHERE b.retired = false AND b.department = :dept";
// DTO constructor query (use findLightsByJpql)
String jpql = "SELECT new com.divudi.core.data.dto.MyDTO(b.id, b.name) FROM Bill b WHERE ...";
List<MyDTO> results = (List<MyDTO>) facade.findLightsByJpql(jpql, params, TemporalType.TIMESTAMP);
// Aggregate query
String jpql = "SELECT SUM(b.netTotal) FROM Bill b WHERE ...";
b.retired = false in queriesEach developer machine may have different Payara JDBC pool names and JNDI names. Common local databases:
rh — primary dev database (JNDI typically jdbc/rhDS)ruhunu — useful as a testing environment with richer data coverageWhen a restored database has lowercase table names, it must be converted to uppercase before the app can use it. Generate and run the rename script:
SELECT CONCAT('RENAME TABLE `', table_name, '` TO `temp_', table_name, '`; ',
'RENAME TABLE `temp_', table_name, '` TO `', UPPER(table_name), '`;')
FROM information_schema.tables
WHERE table_schema = '<db_name>' AND table_name != UPPER(table_name);
Then pipe the output into mysql for that database. See src/main/webapp/resources/sql/UpperCase.sql for reference (note: that file targets a specific schema — always generate fresh from the actual database).
${JDBC_DATASOURCE})For complete reference, read developer_docs/database/mysql-developer-guide.md.