-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathDatabaseUtils.java
More file actions
227 lines (201 loc) · 7.4 KB
/
DatabaseUtils.java
File metadata and controls
227 lines (201 loc) · 7.4 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
package DB;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* Utility class for common database operations and constants.
* Provides helper methods for database operations across the application.
*/
public class DatabaseUtils {
// Common table names
public static final String TABLE_PRODUCT = "product";
public static final String TABLE_SALE = "Sale";
public static final String TABLE_SALE_PRODUCT = "SaleProduct";
public static final String TABLE_BRANCH = "branch";
public static final String TABLE_CASHIER = "cashier";
public static final String TABLE_BRANCH_MANAGER = "branch_manager";
public static final String TABLE_DATA_ENTRY_OPERATOR = "data_entry_operator";
public static final String TABLE_VENDOR = "vendor";
public static final String TABLE_SUPER_ADMIN = "super_admin";
// Common column names
public static final String COLUMN_ID = "id";
public static final String COLUMN_NAME = "name";
public static final String COLUMN_QUANTITY = "quantity";
public static final String COLUMN_DATE = "date";
public static final String COLUMN_BRANCH_CODE = "branchcode";
// SQL Keywords
public static final String SQL_SELECT = "SELECT";
public static final String SQL_INSERT = "INSERT";
public static final String SQL_UPDATE = "UPDATE";
public static final String SQL_DELETE = "DELETE";
public static final String SQL_FROM = "FROM";
public static final String SQL_WHERE = "WHERE";
public static final String SQL_AND = "AND";
public static final String SQL_OR = "OR";
public static final String SQL_ORDER_BY = "ORDER BY";
public static final String SQL_LIMIT = "LIMIT";
/**
* Safely close database resources
*
* @param resources resources to close
*/
public static void closeResources(AutoCloseable... resources) {
for (AutoCloseable resource : resources) {
if (resource != null) {
try {
resource.close();
} catch (Exception e) {
System.err.println("Error closing resource: " + e.getMessage());
}
}
}
}
/**
* Rollback transaction safely
*
* @param connection database connection
*/
public static void rollbackTransaction(Connection connection) {
if (connection != null) {
try {
connection.rollback();
} catch (SQLException e) {
System.err.println("Error rolling back transaction: " + e.getMessage());
}
}
}
/**
* Build a WHERE clause with multiple conditions
*
* @param conditions list of conditions
* @param operator operator to join conditions (AND/OR)
* @return formatted WHERE clause
*/
public static String buildWhereClause(List<String> conditions, String operator) {
if (conditions == null || conditions.isEmpty()) {
return "";
}
StringBuilder whereClause = new StringBuilder(SQL_WHERE + " ");
for (int i = 0; i < conditions.size(); i++) {
if (i > 0) {
whereClause.append(" ").append(operator).append(" ");
}
whereClause.append(conditions.get(i));
}
return whereClause.toString();
}
/**
* Build an ORDER BY clause
*
* @param columns list of columns to order by
* @param ascending true for ASC, false for DESC
* @return formatted ORDER BY clause
*/
public static String buildOrderByClause(List<String> columns, boolean ascending) {
if (columns == null || columns.isEmpty()) {
return "";
}
StringBuilder orderByClause = new StringBuilder(SQL_ORDER_BY + " ");
for (int i = 0; i < columns.size(); i++) {
if (i > 0) {
orderByClause.append(", ");
}
orderByClause.append(columns.get(i));
}
orderByClause.append(ascending ? " ASC" : " DESC");
return orderByClause.toString();
}
/**
* Build a LIMIT clause
*
* @param limit maximum number of rows
* @param offset offset for pagination
* @return formatted LIMIT clause
*/
public static String buildLimitClause(int limit, int offset) {
if (limit <= 0) {
return "";
}
StringBuilder limitClause = new StringBuilder(SQL_LIMIT + " " + limit);
if (offset > 0) {
limitClause.append(" OFFSET ").append(offset);
}
return limitClause.toString();
}
/**
* Check if a table exists
*
* @param connection database connection
* @param tableName name of the table to check
* @return true if table exists, false otherwise
*/
public static boolean tableExists(Connection connection, String tableName) {
try {
DatabaseMetaData metaData = connection.getMetaData();
ResultSet tables = metaData.getTables(null, null, tableName, new String[] { "TABLE" });
return tables.next();
} catch (SQLException e) {
System.err.println("Error checking if table exists: " + e.getMessage());
return false;
}
}
/**
* Get column names for a table
*
* @param connection database connection
* @param tableName name of the table
* @return list of column names
*/
public static List<String> getColumnNames(Connection connection, String tableName) {
List<String> columnNames = new ArrayList<>();
try {
DatabaseMetaData metaData = connection.getMetaData();
ResultSet columns = metaData.getColumns(null, null, tableName, null);
while (columns.next()) {
columnNames.add(columns.getString("COLUMN_NAME"));
}
} catch (SQLException e) {
System.err.println("Error getting column names: " + e.getMessage());
}
return columnNames;
}
/**
* Execute a simple query and return the first result as String
*
* @param connection database connection
* @param sql SQL query to execute
* @param params query parameters
* @return first result as String, or null if no results
*/
public static String executeSingleResultQuery(Connection connection, String sql, Object[] params) {
try (PreparedStatement stmt = connection.prepareStatement(sql)) {
if (params != null) {
for (int i = 0; i < params.length; i++) {
stmt.setObject(i + 1, params[i]);
}
}
try (ResultSet rs = stmt.executeQuery()) {
if (rs.next()) {
return rs.getString(1);
}
}
} catch (SQLException e) {
System.err.println("Error executing single result query: " + e.getMessage());
}
return null;
}
/**
* Format SQL query for logging (remove sensitive data)
*
* @param sql original SQL query
* @return formatted SQL query for logging
*/
public static String formatSqlForLogging(String sql) {
if (sql == null) {
return "null";
}
// Remove password-related patterns for security
return sql.replaceAll("(?i)password\\s*=\\s*['\"][^'\"]*['\"]", "password=***")
.replaceAll("(?i)pwd\\s*=\\s*['\"][^'\"]*['\"]", "pwd=***");
}
}