/*
* This software is distributed under the terms of the FSF
* Gnu Lesser General Public License (see lgpl.txt).
*
* This program is distributed WITHOUT ANY WARRANTY. See the
* GNU General Public License for more details.
*/
package com.scooterframework.orm.sqldataexpress.service;
import java.util.List;
import java.util.Map;
import com.scooterframework.orm.sqldataexpress.exception.BaseSQLException;
import com.scooterframework.orm.sqldataexpress.object.OmniDTO;
import com.scooterframework.orm.sqldataexpress.object.RowData;
import com.scooterframework.orm.sqldataexpress.object.TableData;
import com.scooterframework.orm.sqldataexpress.processor.DataProcessorTypes;
/**
* SqlServiceClient class has helper methods on using SqlService.
*
* @author (Fei) John Chen
*/
public class SqlServiceClient {
/**
* Retrieves TableData from database based on the SQL input.
*
* @param sql a valid SQL statement string
* @return a TableData instance returned from the database.
*/
public static TableData retrieveTableDataBySQL(String sql) {
return retrieveTableDataBySQL(sql, null);
}
/**
* Retrieves TableData from database based on the SQL input.
*
* @param sql a valid SQL statement string
* @param inputs a map of name and value pairs
* @return a TableData instance returned from the database.
*/
public static TableData retrieveTableDataBySQL(String sql, Map<String, Object> inputs) {
TableData td = null;
try {
OmniDTO returnTO =
getSqlService().execute(inputs, DataProcessorTypes.DIRECT_SQL_STATEMENT_PROCESSOR, sql);
if ( returnTO != null ) {
td = returnTO.getTableData(sql);
}
}
catch (Exception ex) {
throw new BaseSQLException(ex);
}
return td;
}
/**
* Retrieves TableData from database based on the SQL query related to
* the SQL key.
*
* @param sqlKey key to a SQL statement
* @return a TableData instance returned from the database.
*/
public static TableData retrieveTableDataBySQLKey(String sqlKey) {
return retrieveTableDataBySQLKey(sqlKey, null);
}
/**
* Retrieves TableData from database based on the SQL query related to
* the SQL key.
*
* @param sqlKey key to a SQL statement
* @param inputs a map of name and value pairs
* @return a TableData instance returned from the database.
*/
public static TableData retrieveTableDataBySQLKey(String sqlKey, Map<String, Object> inputs) {
TableData td = null;
try {
OmniDTO returnTO =
getSqlService().execute(inputs, DataProcessorTypes.NAMED_SQL_STATEMENT_PROCESSOR, sqlKey);
if ( returnTO != null ) {
td = returnTO.getTableData(sqlKey);
}
}
catch (Exception ex) {
throw new BaseSQLException(ex);
}
return td;
}
/**
* Retrieves RowData list from database based on the SQL query related to
* the SQL key.
*
* @param sqlKey key to a SQL statement
* @return a list of RowData objects returned from the database.
*/
public static List<RowData> retrieveRowsBySQLKey(String sqlKey) {
return retrieveRowsBySQLKey(sqlKey, null);
}
/**
* Retrieves RowData list from database based on the SQL query related to
* the SQL key.
*
* @param sqlKey key to a SQL statement
* @param inputs a map of name and value pairs
* @return a lList of RowData objects returned from the database.
*/
public static List<RowData> retrieveRowsBySQLKey(String sqlKey, Map<String, Object> inputs) {
TableData td = retrieveTableDataBySQLKey(sqlKey, inputs);
if (td != null) return td.getAllRows();
return null;
}
/**
* Retrieves RowData list from database based on the SQL input.
*
* @param sql a SQL statement
* @return a list of RowData objects returned from the database.
*/
public static List<RowData> retrieveRowsBySQL(String sql) {
return retrieveRowsBySQL(sql, null);
}
/**
* Retrieves RowData list from database based on the SQL input.
*
* @param sql a SQL statement
* @param inputs a map of name and value pairs
* @return a list of RowData objects returned from the database.
*/
public static List<RowData> retrieveRowsBySQL(String sql, Map<String, Object> inputs) {
TableData td = retrieveTableDataBySQL(sql, inputs);
if (td != null) return td.getAllRows();
return null;
}
/**
* Retrieves one RowData from database based on the SQL query related to
* the SQL key. The first row of the query result is returned.
*
* @param sqlKey key to a SQL statement
* @return a RowData object returned from the database.
*/
public static RowData retrieveOneRowsBySQLKey(String sqlKey) {
return retrieveOneRowBySQLKey(sqlKey, null);
}
/**
* Retrieves one RowData from database based on the SQL query related to
* the SQL key. The first row of the query result is returned.
*
* @param sqlKey key to a SQL statement
* @param inputs a map of name and value pairs
* @return a RowData object returned from the database.
*/
public static RowData retrieveOneRowBySQLKey(String sqlKey, Map<String, Object> inputs) {
TableData td = retrieveTableDataBySQLKey(sqlKey, inputs);
if (td != null) return td.getFirstRow();
return null;
}
/**
* Retrieves one RowData from database based on the SQL input.
* The first row of the query result is returned.
*
* @param sql a SQL statement
* @return a RowData object returned from the database.
*/
public static RowData retrieveOneRowBySQL(String sql) {
return retrieveOneRowBySQL(sql, null);
}
/**
* Retrieves one RowData from database based on the SQL input.
* The first row of the query result is returned.
*
* @param sql a SQL statement
* @param inputs a map of name and value pairs
* @return a RowData object returned from the database.
*/
public static RowData retrieveOneRowBySQL(String sql, Map<String, Object> inputs) {
TableData td = retrieveTableDataBySQL(sql, inputs);
if (td != null) return td.getFirstRow();
return null;
}
/**
* Retrieves one object from database based on the SQL input. The object
* of the first column of the first row is returned.
*
* @param sql a SQL statement
* @return an Object value returned from the database.
*/
public static Object retrieveObjectBySQL(String sql) {
return retrieveObjectBySQL(sql, null);
}
/**
* Retrieves one object from database based on the SQL input. The object
* of the first column of the first row is returned.
*
* @param sql a SQL statement
* @param inputs a map of name and value pairs
* @return an Object value returned from the database.
*/
public static Object retrieveObjectBySQL(String sql, Map<String, Object> inputs) {
Object returnObj = null;
try {
OmniDTO returnTO =
getSqlService().execute(inputs, DataProcessorTypes.DIRECT_SQL_STATEMENT_PROCESSOR, sql);
returnObj = processResult(returnTO, sql);
}
catch (Exception ex) {
throw new BaseSQLException(ex);
}
return returnObj;
}
/**
* Retrieves an object from database based on the SQL query related to
* the SQL key. The object of the first column of the first row is returned.
*
* @param sqlKey key to a SQL statement
* @return an Object value returned from the database.
*/
public static Object retrieveObjectBySQLKey(String sqlKey) {
return retrieveObjectBySQLKey(sqlKey, null);
}
/**
* Retrieves an object from database based on the SQL query related to
* the sql key. The object of the first column of the first row is returned.
*
* @param sqlKey key to a SQL statement
* @param inputs a map of name and value pairs
* @return an Object value returned from the database.
*/
public static Object retrieveObjectBySQLKey(String sqlKey, Map<String, Object> inputs) {
Object returnObj = null;
try {
OmniDTO returnTO =
getSqlService().execute(inputs, DataProcessorTypes.NAMED_SQL_STATEMENT_PROCESSOR, sqlKey);
returnObj = processResult(returnTO, sqlKey);
}
catch (Exception ex) {
throw new BaseSQLException(ex);
}
return returnObj;
}
/**
* Executes a non-select SQL statement and returns count of updated rows.
*
* @param sql a SQL statement
* @return updated row count
*/
public static int executeSQL(String sql) {
return executeSQL(sql, null);
}
/**
* Executes a non-select SQL statement and returns count of updated rows.
*
* @param sql a SQL statement
* @param inputs a map of name and value pairs
* @return updated row count
*/
public static int executeSQL(String sql, Map<String, Object> inputs) {
int rowCount = -1;
try {
OmniDTO returnTO =
getSqlService().execute(inputs, DataProcessorTypes.DIRECT_SQL_STATEMENT_PROCESSOR, sql);
rowCount = returnTO.getUpdatedRowCount();
}
catch (Exception ex) {
throw new BaseSQLException(ex);
}
return rowCount;
}
/**
* Retrieves an object from database based on the SQL query related to
* the SQL key.
*
* @param sqlKey key to a SQL statement
* @return updated row count
*/
public static int executeSQLByKey(String sqlKey) {
return executeSQLByKey(sqlKey, null);
}
/**
* Executes a non-select SQL statement corresponding to a name key and
* returns count of updated rows.
*
* @param sqlKey key to a SQL statement
* @param inputs a map of name and value pairs
* @return updated row count
*/
public static int executeSQLByKey(String sqlKey, Map<String, Object> inputs) {
int rowCount = -1;
try {
OmniDTO returnTO =
getSqlService().execute(inputs, DataProcessorTypes.NAMED_SQL_STATEMENT_PROCESSOR, sqlKey);
rowCount = returnTO.getUpdatedRowCount();
}
catch (Exception ex) {
throw new BaseSQLException(ex);
}
return rowCount;
}
/**
* Returns SqlService
*
* @return SqlService
*/
public static SqlService getSqlService() {
return SqlServiceConfig.getSqlService();
}
private static Object processResult(OmniDTO returnTO, String name) {
Object returnObj = null;
if ( returnTO != null ) {
TableData rt = returnTO.getTableData(name);
if (rt != null) {
returnObj = rt.getFirstObject();
}
}
return returnObj;
}
}