// QueryController.java
@AuthMethod(hasAuth = CommonUtil.UserAuth.ALL)
@RequestMapping(value = "/executeQuery")
@Transactional(readOnly = true, timeout = 30)
public ModelAndView executeQuery(@RequestBody Query query) throws GlobalException {
int query_timeout = 10;
List<TypeCode> timeoutCodeValue = commonService.selectCodeList("ddlb.selectCodeList", new TypeCode("query_timeout"));
if (timeoutCodeValue != null && timeoutCodeValue.size() >0) {
query_timeout = Integer.parseInt( timeoutCodeValue.get(0).getCodeName().toString() );
}
query.setTimeout(query_timeout);
List<Map<String, Object>> rows = null;
rows = queryService.executeQuery(query);
return CommonUtil.getResJsonView(rows, query);
}
// application-dev.properties
----------------------------------------------------------------------
# AWS Secrets Manager Configuration
#----------------------------------------------------------------------
spring.master.datasource.driverClassName=net.sf.log4jdbc.sql.jdbcapi.DriverSpy
#### Dev
aws.secretsmanager.secretname.master.sppcon = arn:aws:secretsmanager:ap-northeast-2:095592
aws.secretsmanager.secretname.worker.sppcon = arn:aws:secretsmanager:ap-northeast-2:095592
aws.secretsmanager.secretname.master.spscon = arn:aws:secretsmanager:ap-northeast-2:095592
aws.secretsmanager.secretname.worker.spscon = arn:aws:secretsmanager:ap-northeast-2:095592
aws.secretsmanager.region = ap-northeast-2
--------------------------------------------------------
@Value("${spring.master.datasource.driverClassName}")
private String masterClass;
@Value("${aws.secretsmanager.secretname.master.sppcon}")
private String masterSecName;
@Value("${aws.secretsmanager.region}")
private String masterRegName;
// QueryService.java
@Transactional(readOnly = true, timeout = 30)
public List<Map<String,Object>> executeQuery(Query query) throws GlobalException {
HashMap<String, Object> mResult = new HashMap<String, Object>();
java.util.List<Map<String,Object>> rows = new ArrayList<>();
query.setSql(org.apache.commons.lang3.StringEscapeUtils.unescapeHtml4(query.getSql()));
String sql = query.getSql();
sql = "SELECT aql.* FROM ( " + sql + "\n) as aql";
if (!query.getIsAll()) {
query.setPageFechNo((query.getPageNo() - 1) * query.getPageSize());
sql = sql + "\nlimit " + query.getPageSize() + " offset " + query.getPageFechNo() ;
}
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
if (!location.equals("LOCAL")) {
getPassword(masterClass, masterSecName, masterRegName);
} else {
getLocalPropertiesValue();
}
if (jdbcUrl == null || username == null || password == null) {
throw new GlobalException("DB접속정보가 없습니다.", true);
}
con = DriverManager.getConnection(jdbcUrl,username,password);
stmt = con.createStatement();
query.setRecordsTotal(0);
stmt.setQueryTimeout(query.getTimeout()); // 초단위
// 건수 조회
rs = stmt.executeQuery("SELECT COUNT(*) FROM ("+query.getSql()+"\n)");
rs.next();
int resultCnt = rs.getInt(1);
query.setRecordsFiltered(resultCnt);
query.setRecordsTotal(resultCnt);
if (resultCnt > 100 && query.getIsAll() && !query.getIsReconfirm()) {
logger.warn("====================== resultCnt : " + resultCnt);
return null;
}
// 목록 조회
rs = stmt.executeQuery(sql);
ResultSetMetaData metaData = rs.getMetaData();
int columnSize = metaData.getColumnCount();
while(rs.next()) {
Map<String, Object> row = new LinkedHashMap<>();
for(int i=1;i<=columnSize;i++) {
String columnName = metaData.getColumnName(i);
Object value = rs.getObject(columnName);
if(value instanceof Long) {
int numValue = (int) (long) value;
row.put(columnName, numValue);
} else if(value instanceof Timestamp) {
String dateValue = value.toString();
dateValue = dateValue.substring(0, dateValue.length()-2);
row.put(columnName, dateValue);
} else {
row.put(columnName, value);
}
}
rows.add(row);
}
} catch(Exception e) {
logger.debug(e.getMessage());
String errMsg = "";
if (e.getMessage() != null && e.getMessage().equals("ERROR: canceling statement due to user request")) {
errMsg += "\nQueryTimeout 발생";
}
throw new GlobalException(e.getMessage()+errMsg, true);
} finally {
try {
if (stmt != null && !stmt.isClosed()) stmt.close();
if (rs != null && !rs.isClosed()) rs.close();
if (con != null && !con.isClosed()) con.close();
} catch (SQLException e) {
logger.debug(e.getMessage());
throw new GlobalException(e.getMessage(), true);
}
}
return rows;
}
public String getValue(AWSSecretsManager secretsClient, String secretName) {
String secret, decodedBinarySecret;
GetSecretValueRequest getSecretValueRequest = new GetSecretValueRequest()
.withSecretId(secretName);
GetSecretValueResult getSecretValueResult = null;
try {
getSecretValueResult = secretsClient.getSecretValue(getSecretValueRequest);
secret = getSecretValueResult.getSecretString();
//System.out.println(secret);
} catch (DecryptionFailureException e) {
// Secrets Manager can't decrypt the protected secret text using the provided KMS key.
// Deal with the exception here, and/or rethrow at your discretion.
//throw e;
logger.error("DecryptionFailureException = " + e.getMessage());
} catch (InternalServiceErrorException e) {
// An error occurred on the server side.
// Deal with the exception here, and/or rethrow at your discretion.
//throw e;
logger.error("InternalServiceErrorException = " + e.getMessage());
} catch (InvalidParameterException e) {
// You provided an invalid value for a parameter.
// Deal with the exception here, and/or rethrow at your discretion.
//throw e;
logger.error("InvalidParameterException = " + e.getMessage());
} catch (InvalidRequestException e) {
// You provided a parameter value that is not valid for the current state of the resource.
// Deal with the exception here, and/or rethrow at your discretion.
//throw e;
logger.error("InvalidRequestException = " + e.getMessage());
} catch (ResourceNotFoundException e) {
// We can't find the resource that you asked for.
// Deal with the exception here, and/or rethrow at your discretion.
//throw e;
logger.error("ResourceNotFoundException = " + e.getMessage());
} catch (Exception e) {
logger.error("Exception = " + e.getMessage());
}
if (getSecretValueResult.getSecretString() != null) {
secret = getSecretValueResult.getSecretString();
return secret;
}
else {
decodedBinarySecret = new String(Base64.getDecoder().decode(getSecretValueResult.getSecretBinary()).array());
return decodedBinarySecret;
}
}
// AWS 에서 username, password 가져오기
DataSource getPassword(String className, String secName, String regName) {
AWSSecretsManager client = AWSSecretsManagerClientBuilder.standard()
.withRegion(regName)
.build();
String jsonStr = getValue(client, secName);
try {
JSONParser parser = new JSONParser();
Object obj = parser.parse(jsonStr);
JSONObject jsonObj = (JSONObject) obj;
//String password = (String) jsonObj.get("password");
jdbcUrl = "jdbc:log4jdbc:postgresql://" + (String) jsonObj.get("host") + ":" + (String) jsonObj.get("port") + "/" + (String) jsonObj.get("dbname");
username = (String) jsonObj.get("username");
password = (String) jsonObj.get("password");
return DataSourceBuilder
.create()
.username((String) jsonObj.get("username"))
.password((String) jsonObj.get("password"))
.url("jdbc:log4jdbc:postgresql://" + (String) jsonObj.get("host") + ":" + (String) jsonObj.get("port") + "/" + (String) jsonObj.get("dbname"))
.driverClassName(className)
.build();
} catch (ParseException e) {
// TODO Auto-generated catch block
}
return null;
}
private void getLocalPropertiesValue() {
String resource = "application-local.properties";
Properties properties = new Properties();
try {
Reader reader = Resources.getResourceAsReader(resource);
properties.load(reader);
jdbcUrl = properties.getProperty("maindb.jdbcUrl");
username = properties.getProperty("maindb.username");
password = properties.getProperty("maindb.password");
} catch (IOException e) {
e.printStackTrace();
}
}
