📄 db.java
字号:
catch (SQLException e)
{
s_log.error("prepareCall (" + sql + ")", e);
}
return null;
} // prepareCall
/*************************************************************************/
/**
* Prepare Read Only Statement
* @param RO_SQL sql (RO)
* @return Prepared Statement
*/
public static PreparedStatement prepareStatement(String RO_SQL)
{
return prepareStatement(RO_SQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
} // prepareStatement
/**
* Prepare Statement
*
* @param SQL sql
* @param type - ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_SENSITIVE
* @param concur - ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE
* @return Prepared Statement r/o or r/w depending on concur
*/
public static PreparedStatement prepareStatement(String SQL, int type, int concur)
{
if (SQL == null || SQL.length() == 0)
throw new IllegalArgumentException("DB.prepareStatement - required parameter missing - " + SQL);
//
String sql = getDatabase().convertStatement(SQL);
try
{
Connection conn = null;
if (concur == ResultSet.CONCUR_UPDATABLE)
conn = getConnectionRW();
else
conn = getConnectionRO();
return conn.prepareStatement (sql, type, concur);
}
catch (SQLException e)
{
s_log.error("prepareStatement (" + sql + ")", e);
}
return null;
} // prepareStatement
/**
* Create Read Only Statement
* @return Statement
*/
public static Statement createStatement()
{
return createStatement (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
} // createStatement
/**
* Create Statement
*
* @param type - ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.TYPE_SCROLL_SENSITIVE
* @param concur - ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE
* @return Statement - either r/w ir r/o depending on concur
*/
public static Statement createStatement(int type, int concur)
{
try
{
Connection conn = null;
if (concur == ResultSet.CONCUR_UPDATABLE)
conn = getConnectionRW();
else
conn = getConnectionRO();
return conn.createStatement(type, concur);
}
catch (SQLException e)
{
s_log.error("createStatement", e);
}
return null;
} // createStatement
/**
* Execute Update.
* saves "DBExecuteError" in Log
* @param SQL sql
* @return number of rows updated
*/
public static int executeUpdate (String SQL)
{
if (SQL == null || SQL.length() == 0)
throw new IllegalArgumentException("DB.executeUpdate - required parameter missing - " + SQL);
//
String sql = getDatabase().convertStatement(SQL);
int no = 0;
Statement stmt = null;
try
{
Connection conn = getConnectionRW();
stmt = conn.createStatement();
no = stmt.executeUpdate(sql);
}
catch (SQLException e)
{
s_log.error("executeUpdate - " + sql, e);
Log.saveError("DBExecuteError", e.getLocalizedMessage());
}
finally
{
// Always close cursor
try
{
stmt.close();
}
catch (SQLException e2)
{
s_log.error("executeUpdate - cannot close statement");
}
}
return no;
} // execute Update
/**
* Commit - commit on RW connection.
* Is not required as RW connection is AutoCommit
* @param throwException if true, re-throws exception
* @return true if not needed or success
* @throws SQLException
*/
public static boolean commit (boolean throwException) throws SQLException
{
try
{
Connection conn = getConnectionRW();
// if (!conn.getAutoCommit())
conn.commit();
}
catch (SQLException e)
{
s_log.error("commit", e);
if (throwException)
throw e;
return false;
}
return true;
} // commit
/**
* Get next number for Key column = 0 is Error.
* @param ctx context
* @param WindowNo window
* @param TableName table
* @return next no
*/
public static int getKeyNextNo (Properties ctx, int WindowNo, String TableName)
{
if (ctx == null || TableName == null || TableName.length() == 0)
throw new IllegalArgumentException("DB.getKeyNextNo - required parameter missing");
String CompiereSys = Env.getContext(ctx, "#CompiereSys");
int AD_Client_ID = Env.getContextAsInt(ctx, WindowNo, "AD_Client_ID");
//
return getKeyNextNo (AD_Client_ID, CompiereSys, TableName);
} // getKeyNextNo
/**
* Get next number for Key column = 0 is Error.
* @param AD_Client_ID client
* @param CompiereSys compiere sys
* @param TableName table name
* @return next no
*/
public static int getKeyNextNo (int AD_Client_ID, String CompiereSys, String TableName)
{
if (TableName == null || CompiereSys == null || TableName.length() == 0)
throw new IllegalArgumentException("DB.getKeyNextNo - required parameter missing");
int retValue = 0;
// Check CompiereSys
// if (AD_Client_ID == 0 && !CompiereSys.equals("Y"))
// throw new UnsupportedOperationException("DB.getKeyNextNo - Cannot add System records");
//
try
{
String SQL = "{CALL AD_Sequence_Next(?,?,?)}";
CallableStatement cstmt = prepareCall(SQL);
cstmt.setString(1, TableName);
cstmt.setInt(2, AD_Client_ID);
cstmt.registerOutParameter(3, Types.NUMERIC);
cstmt.executeUpdate();
retValue = cstmt.getInt(3);
cstmt.close();
}
catch(SQLException e)
{
s_log.error("getKeyNextNo - Table=" + TableName + ")", e);
}
return retValue;
} // getKeyNextNo
/**
* Get Document Number for current document.
* <br>
* - first search for DocType based Document No
* - then Search for DocumentNo based on TableName
* @param ctx context
* @param WindowNo window
* @param TableName table
* @param onlyDocType Do not search for document no based on TableName
* @return DocumentNo or null, if no doc number defined
*/
public static String getDocumentNo (Properties ctx, int WindowNo, String TableName, boolean onlyDocType)
{
if (ctx == null || TableName == null || TableName.length() == 0)
throw new IllegalArgumentException("DB.getDocumentNo - required parameter missing");
String CompiereSys = Env.getContext(ctx, "#CompiereSys");
int AD_Client_ID = Env.getContextAsInt(ctx, WindowNo, "AD_Client_ID");
// Get C_DocType_ID from context - NO Defaults -
int C_DocType_ID = Env.getContextAsInt(ctx, WindowNo + "|C_DocTypeTarget_ID");
if (C_DocType_ID == 0)
C_DocType_ID = Env.getContextAsInt(ctx, WindowNo + "|C_DocType_ID");
if (C_DocType_ID == 0)
{
s_log.debug("getDocumentNo - for Window=" + WindowNo
+ " - Target=" + Env.getContextAsInt(ctx, WindowNo + "|C_DocTypeTarget_ID") + "/" + Env.getContextAsInt(ctx, WindowNo, "C_DocTypeTarget_ID")
+ " - Actual=" + Env.getContextAsInt(ctx, WindowNo + "|C_DocType_ID") + "/" + Env.getContextAsInt(ctx, WindowNo, "C_DocType_ID"));
return getDocumentNo (AD_Client_ID, CompiereSys, TableName);
}
// Check CompiereSys
if (AD_Client_ID == 0 && !CompiereSys.equals("Y"))
throw new UnsupportedOperationException("DB.getDocumentNo - Cannot add System records");
//
String retValue = getDocumentNo(AD_Client_ID, C_DocType_ID);
if (!onlyDocType && retValue == null)
return getDocumentNo (AD_Client_ID, CompiereSys, TableName);
return retValue;
} // getDocumentNo
/**
* Get Document Number for current document.
* @param AD_Client_ID client
* @param C_DocType_ID (target) document type
* @return DocumentNo or null, if no doc number defined
*/
public static String getDocumentNo (int AD_Client_ID, int C_DocType_ID)
{
String retValue = null;
try
{
String SQL = "{CALL AD_Sequence_DocType(?,?,?)}";
CallableStatement cstmt = prepareCall(SQL);
cstmt.setInt(1, C_DocType_ID);
cstmt.setInt(2, AD_Client_ID);
cstmt.registerOutParameter(3, Types.VARCHAR);
cstmt.executeUpdate();
retValue = cstmt.getString(3);
cstmt.close();
}
catch(SQLException e)
{
s_log.error("getDocumentNo - DocType=" + C_DocType_ID, e);
}
s_log.info("getDocumentNo - DocType=" + C_DocType_ID + " -> " + retValue);
return retValue;
} // getDocumentNo
/**
* Get Next Document No
* @param AD_Client_ID client
* @param CompiereSys system
* @param TableName table name
* @return DocumentNo
*/
public static String getDocumentNo (int AD_Client_ID, String CompiereSys, String TableName)
{
if (CompiereSys == null || TableName == null || TableName.length() == 0)
throw new IllegalArgumentException("DB.getDocumentNo - required parameter missing");
// Check CompiereSys
// if (AD_Client_ID == 0 && !CompiereSys.equals("Y"))
// throw new UnsupportedOperationException("DB.getDocumentNo - Cannot add System records");
//
String retValue = null;
try
{
String SQL = "{CALL AD_Sequence_Doc(?,?,?)}";
CallableStatement cstmt = prepareCall(SQL);
cstmt.setString(1, "DocumentNo_" + TableName);
cstmt.setInt(2, AD_Client_ID);
cstmt.registerOutParameter(3, Types.VARCHAR);
cstmt.executeUpdate();
retValue = cstmt.getString(3);
cstmt.close();
}
catch(SQLException e)
{
s_log.error("getDocumentNo TableName=" + TableName, e);
}
s_log.info("getDocumentNo - TableName=" + TableName + " -> " + retValue);
return retValue;
} // getDocumentNo
/**
* Get Value from sql
* @param sql sql
* @return first value or -1
*/
public static int getSQLValue (String sql)
{
int retValue = -1;
PreparedStatement pstmt = null;
try
{
pstmt = prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
if (rs.next())
retValue = rs.getInt(1);
else
s_log.warn("getSQLValue - No Value " + sql);
rs.close();
pstmt.close();
pstmt = null;
}
catch (Exception e)
{
s_log.error("getSQLValue - " + sql, e);
}
finally
{
try
{
if (pstmt != null)
pstmt.close ();
}
catch (Exception e)
{}
pstmt = null;
}
return retValue;
} // getSQLValue
/**
* Get Value from sql
* @param sql sql
* @param int_param1 parameter 1
* @return first value or -1
*/
public static int getSQLValue (String sql, int int_param1)
{
int retValue = -1;
PreparedStatement pstmt = null;
try
{
pstmt = prepareStatement(sql);
pstmt.setInt(1, int_param1);
ResultSet rs = pstmt.executeQuery();
if (rs.next())
retValue = rs.getInt(1);
else
s_log.warn("getSQLValue - No Value " + sql + " - Param1=" + int_param1);
rs.close();
pstmt.close();
pstmt = null;
}
catch (Exception e)
{
s_log.error("getSQLValue - " + sql + " - Param1=" + int_param1, e);
}
finally
{
try
{
if (pstmt != null)
pstmt.close ();
}
catch (Exception e)
{}
pstmt = null;
}
return retValue;
} // getSQLValue
/**
* Get Value from sql
* @param sql sql
* @param int_param1 parameter 1
* @param s_param2 parameter 2
* @return first value or -1
*/
public static int getSQLValue (String sql, int int_param1, String s_param2)
{
int retValue = -1;
PreparedStatement pstmt = null;
try
{
pstmt = prepareStatement(sql);
pstmt.setInt(1, int_param1);
pstmt.setString(2, s_param2);
ResultSet rs = pstmt.executeQuery();
if (rs.next())
retValue = rs.getInt(1);
else
s_log.warn("getSQLValue - No Value: " + sql + " - Param1=" + int_param1 + ",Param2=" + s_param2);
rs.close();
pstmt.close();
pstmt = null;
}
catch (Exception e)
{
s_log.error("getSQLValue - " + sql + " - Param1=" + int_param1 + ",Param2=" + s_param2, e);
}
finally
{
try
{
if (pstmt != null)
pstmt.close ();
}
catch (Exception e)
{}
pstmt = null;
}
return retValue;
} // getSQLValue
/**
* Convert an amount with today's spot rate
* @param CurFrom_ID The C_Currency_ID FROM
* @param CurTo_ID The C_Currency_ID TO
* @param Amt The amount to be converted
* @param AD_Client_ID client
* @param AD_Org_ID organization
* @return converted amount
*/
public static BigDecimal getConvertedAmt (BigDecimal Amt, int CurFrom_ID, int CurTo_ID, int AD_Client_ID, int AD_Org_ID)
{
return getConvertedAmt (Amt, CurFrom_ID, CurTo_ID, null, null, AD_Client_ID, AD_Org_ID);
} // getConvertedAmt
/**
* Convert an amount
* @param CurFrom_ID The C_Currency_ID FROM
* @param CurTo_ID The C_Currency_ID TO
* @param ConvDate The Conversion date - if null - use current date
* @param RateType The Conversion rate type - if null/empty - use Spot
* @param Amt The amount to be converted
* @param AD_Client_ID client
* @param AD_Org_ID organization
* @return converted amount
*/
public static BigDecimal getConvertedAmt (BigDecimal Amt, int CurFrom_ID, int CurTo_ID,
Timestamp ConvDate, String RateType, int AD_Client_ID, int AD_Org_ID)
{
if (Amt == null)
throw new IllegalArgumentException("DB.getConvertedAmt - required parameter missing - Amt");
BigDecimal retValue = null;
if (CurFrom_ID == CurTo_ID || Amt.equals(Env.ZERO))
return Amt;
//
try
{
String sql = "{? = call C_Currency_Convert(?,?,?,?,?, ?,?) }";
CallableStatement cstmt = prepareCall(sql);
//
cstmt.registerOutParameter(1, Types.NUMERIC);
//
cstmt.setBigDecimal(2, Amt); // Amount IN NUMBER
cstmt.setInt(3, CurFrom_ID); // CurFrom IN NUMBER
cstmt.setInt(4, CurTo_ID); // CurTo IN NUMBER
if (ConvDate == null)
ConvDate = new Timestamp (System.currentTimeMillis());
cstmt.setTimestamp(5, ConvDate); // ConvDate IN DATE
if (RateType == null || RateType.equals(""))
RateType = "S";
cstmt.setString(6, RateType); // RateType IN CHAR
cstmt.setInt(7, AD_Client_ID);
cstmt.setInt(8, AD_Org_ID);
//
cstmt.executeUpdate();
retValue = cstmt.getBigDecimal(1);
cstmt.close();
}
catch(SQLException e)
{
s_log.error("getConvertedAmt", e);
}
if (retValue == null)
s_log.info("getConvertedAmt - not found - CurFrom=" + CurFrom_ID + ", CurTo=" + CurTo_ID
+ ", " + ConvDate + ", " + RateType + ", Client=" + AD_Client_ID + ", Org=" + AD_Org_ID);
return retValue;
} // getConvertedAmt
/**
* Get Currency Rate
* @param CurFrom_ID The C_Currency_ID FROM
* @param CurTo_ID The C_Currency_ID TO
* @param ConvDate The Conversion date - if null - use current date
* @param RateType The Conversion rate type - if null/empty - use Spot
* @param AD_Client_ID client
* @param AD_Org_ID organization
* @return currency Rate
*/
public static BigDecimal getConvesionRate (int CurFrom_ID, int CurTo_ID,
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -