();
try
{
pstmt = DB.prepareStatement(sql, trxName);
setParameters(pstmt, params);
rs = pstmt.executeQuery();
while (rs.next())
{
list.add(rs.getInt(1));
}
}
catch (SQLException e)
{
throw new DBException(e, sql);
}
finally
{
close(rs, pstmt);
rs= null;
pstmt = null;
}
// Convert to array
int[] retValue = new int[list.size()];
for (int i = 0; i < retValue.length; i++)
{
retValue[i] = list.get(i);
}
return retValue;
} // getIDsEx
/**
* Is Sales Order Trx.
* - include in ' (single quotes)
* - replace ' with ''
*
* @param txt String with text
* @param maxLength Maximum Length of content or 0 to ignore
* @return escaped string for sql statement (NULL if null)
*/
public static String TO_STRING (String txt, int maxLength)
{
if (txt == null || txt.length() == 0)
return "NULL";
// Length
String text = txt;
if (maxLength != 0 && text.length() > maxLength)
text = txt.substring(0, maxLength);
// copy characters (we need to look through anyway)
StringBuilder out = new StringBuilder();
out.append(QUOTE); // '
for (int i = 0; i < text.length(); i++)
{
char c = text.charAt(i);
if (c == QUOTE)
out.append("''");
else
out.append(c);
}
out.append(QUOTE); // '
//
return out.toString();
} // TO_STRING
/**
* Return string as JSON object for INSERT statements with correct precision
* @param value
* @return value as json
*/
public static String TO_JSON (String value)
{
return s_cc.getDatabase().TO_JSON(value);
}
/**
* @return string with right casting for JSON inserts
*/
public static String getJSONCast()
{
return s_cc.getDatabase().getJSONCast();
}
/**
* Convenient method to close result set
* @param rs
*/
public static void close( ResultSet rs) {
try {
if (rs!=null) rs.close();
} catch (SQLException e) {
;
}
}
/**
* Convenient method to close statement
* @param st
*/
public static void close( Statement st) {
try {
if (st!=null) st.close();
} catch (SQLException e) {
;
}
}
/**
* Convenient method to close result set and statement
* @param rs result set
* @param st statement
* @see #close(ResultSet)
* @see #close(Statement)
*/
public static void close(ResultSet rs, Statement st) {
close(rs);
close(st);
}
/**
* Convenient method to close a {@link POResultSet}
* @param rs result set
* @see POResultSet#close()
*/
public static void close(POResultSet rs) {
if (rs != null)
rs.close();
}
/**
* Try to get the SQLException from Exception
* @param e Exception
* @return SQLException if found or provided exception elsewhere
*/
public static Exception getSQLException(Exception e)
{
Throwable e1 = e;
while (e1 != null)
{
if (e1 instanceof SQLException)
return (SQLException)e1;
e1 = e1.getCause();
}
return e;
}
/** Quote */
private static final char QUOTE = '\'';
// Following methods are kept for BeanShell compatibility.
// See BF [ 2030233 ] Remove duplicate code from DB class
// TODO: remove this when BeanShell will support varargs methods
public static int getSQLValue (String trxName, String sql)
{
return getSQLValue(trxName, sql, new Object[]{});
}
public static int getSQLValue (String trxName, String sql, int int_param1)
{
return getSQLValue(trxName, sql, new Object[]{int_param1});
}
public static int getSQLValue (String trxName, String sql, int int_param1, int int_param2)
{
return getSQLValue(trxName, sql, new Object[]{int_param1, int_param2});
}
public static int getSQLValue (String trxName, String sql, String str_param1)
{
return getSQLValue(trxName, sql, new Object[]{str_param1});
}
public static int getSQLValue (String trxName, String sql, int int_param1, String str_param2)
{
return getSQLValue(trxName, sql, new Object[]{int_param1, str_param2});
}
public static String getSQLValueString (String trxName, String sql, int int_param1)
{
return getSQLValueString(trxName, sql, new Object[]{int_param1});
}
public static BigDecimal getSQLValueBD (String trxName, String sql, int int_param1)
{
return getSQLValueBD(trxName, sql, new Object[]{int_param1});
}
//End BeanShell compatibility.
/**
* Get Array of ValueNamePair items.
* Example:
* String sql = "SELECT Name, Description FROM AD_Ref_List WHERE AD_Reference_ID=?";
* ValueNamePair[] list = DB.getValueNamePairs(sql, false, params);
*
* @param sql SELECT Value_Column, Name_Column FROM ...
* @param optional if {@link ValueNamePair#EMPTY} is added
* @param params query parameters
* @return array of {@link ValueNamePair} or empty array
* @throws DBException if there is any SQLException
*/
public static ValueNamePair[] getValueNamePairs(String sql, boolean optional, List params)
{
PreparedStatement pstmt = null;
ResultSet rs = null;
ArrayList list = new ArrayList();
if (optional)
{
list.add (ValueNamePair.EMPTY);
}
try
{
pstmt = DB.prepareStatement(sql, null);
setParameters(pstmt, params);
rs = pstmt.executeQuery();
while (rs.next())
{
list.add(new ValueNamePair(rs.getString(1), rs.getString(2)));
}
}
catch (SQLException e)
{
throw new DBException(e, sql);
}
finally
{
close(rs, pstmt);
rs = null; pstmt = null;
}
return list.toArray(new ValueNamePair[list.size()]);
}
/**
* Get Array of KeyNamePair items.
* Example:
* String sql = "SELECT C_City_ID, Name FROM C_City WHERE C_City_ID=?";
* KeyNamePair[] list = DB.getKeyNamePairs(sql, false, params);
*
* @param sql SELECT ID_Column, Name_Column FROM ...
* @param optional if {@link ValueNamePair#EMPTY} is added
* @param params query parameters
* @return array of {@link KeyNamePair} or empty array
* @throws DBException if there is any SQLException
*/
public static KeyNamePair[] getKeyNamePairs(String sql, boolean optional, List params)
{
PreparedStatement pstmt = null;
ResultSet rs = null;
ArrayList list = new ArrayList();
if (optional)
{
list.add (KeyNamePair.EMPTY);
}
try
{
pstmt = DB.prepareStatement(sql, null);
setParameters(pstmt, params);
rs = pstmt.executeQuery();
while (rs.next())
{
list.add(new KeyNamePair(rs.getInt(1), rs.getString(2)));
}
}
catch (SQLException e)
{
throw new DBException(e, sql);
}
finally
{
close(rs, pstmt);
rs = null; pstmt = null;
}
return list.toArray(new KeyNamePair[list.size()]);
}
/**
* Insert selection into T_Selection table. selection, String trxName)
{
StringBuilder insert = new StringBuilder();
insert.append("INSERT INTO T_SELECTION(AD_PINSTANCE_ID, T_SELECTION_ID) ");
int counter = 0;
for(Integer selectedId : selection)
{
counter++;
if (counter > 1)
insert.append(" UNION ");
insert.append("SELECT ");
insert.append(AD_PInstance_ID);
insert.append(", ");
insert.append(selectedId);
insert.append(" FROM DUAL ");
if (counter >= 1000)
{
DB.executeUpdateEx(insert.toString(), trxName);
insert = new StringBuilder();
insert.append("INSERT INTO T_SELECTION(AD_PINSTANCE_ID, T_SELECTION_ID) ");
counter = 0;
}
}
if (counter > 0)
{
DB.executeUpdateEx(insert.toString(), trxName);
}
}
/**
* Insert selection into T_Selection table. saveKeys, String trxName) {
Collection saveKeysNP = new ArrayList();
for (NamePair saveKey : saveKeys)
saveKeysNP.add(saveKey);
createT_SelectionNewNP(AD_PInstance_ID, saveKeysNP, trxName);
}
/**
* Insert selection into T_Selection table. saveKeys, String trxName)
{
String initialInsert = "INSERT INTO T_SELECTION(AD_PINSTANCE_ID, T_SELECTION_ID, T_SELECTION_UU, ViewID) ";
StringBuilder insert = new StringBuilder(initialInsert);
int counter = 0;
for(NamePair saveKey : saveKeys)
{
Object selectedId;
if (saveKey instanceof KeyNamePair)
selectedId = ((KeyNamePair)saveKey).getKey();
else if (saveKey instanceof ValueNamePair)
selectedId = ((ValueNamePair)saveKey).getValue();
else
throw new AdempiereException("NamePair type not allowed in DB.createT_SelectionNewNP, just KeyNamePair or ValueNamePair are allowed");
counter++;
if (counter > 1)
insert.append(" UNION ");
insert.append("SELECT ");
insert.append(AD_PInstance_ID);
insert.append(", ");
if (selectedId instanceof Integer) {
insert.append((Integer)selectedId);
insert.append(", ' '");
} else {
insert.append("0, ");
insert.append(DB.TO_STRING(selectedId.toString()));
}
insert.append(", ");
String viewIDValue = saveKey.getName();
// when no process have viewID or this process have no viewID or value of viewID is null
if (viewIDValue == null){
insert.append("NULL");
}else{
insert.append(DB.TO_STRING(viewIDValue));
}
insert.append(" FROM DUAL ");
if (counter >= 1000)
{
DB.executeUpdateEx(insert.toString(), trxName);
insert.delete(0, insert.length());
insert.append(initialInsert);
counter = 0;
}
}
if (counter > 0)
{
DB.executeUpdateEx(insert.toString(), trxName);
}
}
private static boolean m_isUUIDVerified = false;
private static boolean m_isUUIDSupported = false;
/**
* Is DB support generate_uuid function
* @return true if current db have working generate_uuid function. generate_uuid doesn't work on 64 bit postgresql
* on windows yet.
*/
public static boolean isGenerateUUIDSupported() {
if (! m_isUUIDVerified) {
String uuidTest = null;
try {
uuidTest = getSQLValueStringEx(null, "SELECT Generate_UUID() FROM Dual");
} catch (Exception e) {}
m_isUUIDSupported = uuidTest != null && uuidTest.trim().length() == 36;
m_isUUIDVerified = true;
}
return m_isUUIDSupported;
}
/**
* Throw DBException if trxName doesn't return an existing Trx instance.
* @param trxName
*/
private static void verifyTrx(String trxName) {
if (trxName != null && Trx.get(trxName, false) == null) {
// Using a trx that was previously closed or never opened
// probably timed out - throw Exception (IDEMPIERE-644)
String msg = "Transaction closed or never opened ("+trxName+") => (maybe timed out)";
log.severe(msg); // severe
throw new DBException(msg);
}
}
/**
* Is table or view exists
* @param tableName
* @return true if table or view with name=tableName exists in db
*/
public static boolean isTableOrViewExists(String tableName) {
Connection conn = getConnection();
ResultSet rs = null;
try {
DatabaseMetaData metadata = conn.getMetaData();
String tblName;
if (metadata.storesUpperCaseIdentifiers())
tblName = tableName.toUpperCase();
else if (metadata.storesLowerCaseIdentifiers())
tblName = tableName.toLowerCase();
else
tblName = tableName;
rs = metadata.getTables(null, null, tblName, null);
if (rs.next()) {
return true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DB.close(rs);
try {
conn.close();
} catch (SQLException e) {}
}
return false;
}
/**
* Get a list of objects from sql (one per each column in the select clause), column indexing starts with 0
* @param trxName optional transaction name
* @param sql
* @param params array of parameters
* @return null if not found
* @throws DBException if there is any SQLException
*/
public static List getSQLValueObjectsEx(String trxName, String sql, Object... params) {
List retValue = new ArrayList();
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection conn = null;
if (trxName == null)
conn = DB.createConnection(true, Connection.TRANSACTION_READ_COMMITTED);
try
{
if (conn != null)
{
conn.setAutoCommit(false);
conn.setReadOnly(true);
}
if (conn != null)
pstmt = prepareStatement(conn, sql);
else
pstmt = prepareStatement(sql, trxName);
setParameters(pstmt, params);
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
if (rs.next()) {
for (int i=1; i<=rsmd.getColumnCount(); i++) {
Object obj = rs.getObject(i);
if (rs.wasNull())
retValue.add(null);
else
retValue.add(obj);
}
} else {
retValue = null;
}
}
catch (SQLException e)
{
if (conn != null)
{
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
throw new DBException(e, sql);
}
finally
{
close(rs, pstmt);
rs = null; pstmt = null;
if (conn != null)
{
closeAndResetReadonlyConnection(conn);
}
}
return retValue;
}
/**
* Get a list of object list from sql (one object list per each row, and in the object list, one object per each column in the select clause),
* column indexing starts with 0.> getSQLArrayObjectsEx(String trxName, String sql, Object... params) {
List> rowsArray = new ArrayList>();
PreparedStatement pstmt = null;
ResultSet rs = null;
Connection conn = null;
if (trxName == null)
conn = DB.createConnection(true, Connection.TRANSACTION_READ_COMMITTED);
try
{
if (conn != null)
{
conn.setAutoCommit(false);
conn.setReadOnly(true);
}
if (conn != null)
pstmt = prepareStatement(conn, sql);
else
pstmt = prepareStatement(sql, trxName);
setParameters(pstmt, params);
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
while (rs.next()) {
List retValue = new ArrayList();
for (int i=1; i<=rsmd.getColumnCount(); i++) {
Object obj = rs.getObject(i);
if (rs.wasNull())
retValue.add(null);
else
retValue.add(obj);
}
rowsArray.add(retValue);
}
}
catch (SQLException e)
{
if (conn != null)
{
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
throw new DBException(e, sql);
}
finally
{
close(rs, pstmt);
rs = null; pstmt = null;
if (conn != null)
{
closeAndResetReadonlyConnection(conn);
}
}
if (rowsArray.size() == 0)
return null;
return rowsArray;
}
/**
* Create Read Replica Prepared Statement proxy
* @param sql
* @param trxName transaction
* @return Prepared Statement (from replica if possible, otherwise normal statement)
*/
public static PreparedStatement prepareNormalReadReplicaStatement(String sql, String trxName) {
return prepareNormalReadReplicaStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, trxName);
}
/**
* Create Read Replica Prepared Statement proxy
* @param sql
* @param resultSetType - ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_SENSITIVE
* @param resultSetConcurrency - ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE
* @param trxName transaction name
* @return Prepared Statement (from replica if possible, otherwise normal statement)
*/
private static PreparedStatement prepareNormalReadReplicaStatement(String sql, int resultSetType, int resultSetConcurrency, String trxName) {
if (sql == null || sql.length() == 0)
throw new IllegalArgumentException("No SQL");
boolean useReadReplica = MSysConfig.getValue(MSysConfig.DB_READ_REPLICA_URLS) != null;
if ( trxName == null
&& useReadReplica
&& resultSetType == ResultSet.TYPE_FORWARD_ONLY
&& resultSetConcurrency == ResultSet.CONCUR_READ_ONLY) {
// this is a candidate for a read replica connection (read-only, forward-only, no-trx), try to obtain one, otherwise fallback to normal
CPreparedStatement stmt = ProxyFactory.newReadReplicaPreparedStatement(resultSetType, resultSetConcurrency, sql);
if (stmt != null) {
return stmt;
}
}
//
return ProxyFactory.newCPreparedStatement(resultSetType, resultSetConcurrency, sql, trxName);
}
/**
* Create IN clause for csv value
* @param columnName
* @param csv comma separated value
* @return IN clause
*/
public static String inClauseForCSV(String columnName, String csv)
{
return inClauseForCSV(columnName, csv, false);
}
/**
* Create IN clause for csv value
* @param columnName
* @param csv comma separated value
* @param isNotClause true to append NOT before IN
* @return IN clause
*/
public static String inClauseForCSV(String columnName, String csv, boolean isNotClause)
{
StringBuilder builder = new StringBuilder();
builder.append(columnName);
if(isNotClause)
builder.append(" NOT ");
builder.append(" IN (");
String[] values = csv.split("[,]");
for(int i = 0; i < values.length; i++)
{
if (i > 0)
builder.append(",");
String key = values[i];
if (columnName.endsWith("_ID"))
{
builder.append(key);
}
else
{
if (key.startsWith("\"") && key.endsWith("\""))
{
key = key.substring(1, key.length()-1);
}
builder.append(TO_STRING(key));
}
}
builder.append(")");
return builder.toString();
}
/**
* Create subset clause for csv value (i.e columnName is a subset of the csv value set)
* @param columnName
* @param csv
* @return subset sql clause
*/
public static String subsetClauseForCSV(String columnName, String csv)
{
return getDatabase().subsetClauseForCSV(columnName, csv);
}
/**
* Create intersect clause for csv value (i.e columnName is an intersect with the csv value set)
* @param columnName
* @param csv
* @return intersect sql clause
*/
public static String intersectClauseForCSV(String columnName, String csv)
{
return intersectClauseForCSV(columnName, csv, false);
}
/**
* Create intersect clause for csv value (i.e columnName is an intersect with the csv value set)
* @param columnName
* @param csv
* @param isNotClause true to append NOT before the intersect clause
* @return intersect sql clause
*/
public static String intersectClauseForCSV(String columnName, String csv, boolean isNotClause)
{
return getDatabase().intersectClauseForCSV(columnName, csv, isNotClause);
}
/**
* Is sql a SELECT statement
* @param sql
* @return true if it is a SELECT statement
*/
public static boolean isSelectStatement(String sql) {
String removeComments = "/\\*(?:.|[\\n\\r])*?\\*/";
String removeQuotedStrings = "'(?:.|[\\n\\r])*?'";
String removeLeadingSpaces = "^\\s+";
String cleanSql = sql.toLowerCase().replaceAll(removeComments, "").replaceAll(removeQuotedStrings, "").replaceFirst(removeLeadingSpaces, "");
if(cleanSql.matches("^select\\s.*$") && !cleanSql.contains(";"))
return true;
else
return false;
}
} // DB