Database Guide

MySQL Developer Guide

Security Policy

• MySQL credentials MUST NEVER be committed to git
• Credentials stored in separate folder outside the project directory
• Windows: C:\Credentials\credentials.txt
• Linux/Mac: ~/.config/hmis/credentials.txt

Common Database Operations

Structure Investigation

SHOW TABLES LIKE '%bill%';
DESCRIBE bill;
SHOW CREATE TABLE bill;
SELECT COUNT(*) FROM bill WHERE retired = 0;

JPQL Query Patterns

// 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 ...";

Performance Tips

• Always include b.retired = false in queries
• Use indexed columns in WHERE clauses
• Use DTOs instead of full entities for display
• Limit result sets with pagination

Persistence Configuration

• Development: Use hardcoded JNDI (jdbc/rhDS)
• Before push: Must use environment variables (${JDBC_DATASOURCE})

For complete reference, read developer_docs/database/mysql-developer-guide.md.