public void editar(Guia objeto, Usuario usuario, String xml) throws Exception {
try {
cnn = Conexion.getConexion();
CallableStatement cs = null;
cs = cnn.prepareCall("call uspUpdGuia(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
cs.setInt(1, objeto.getIdGuia());
cs.setString(2, objeto.getPeriodo());
cs.setString(3, objeto.getNumero());
cs.setString(4, objeto.getFecha());
cs.setDouble(5, objeto.getTipoCambio());
cs.setString(6, objeto.getSerie());
cs.setString(7, objeto.getNumeroDocumento());
cs.setInt(8, objeto.getIdTipoMovimiento());
cs.setString(9, objeto.getReferencia());
cs.setInt(10, objeto.getIdOrdenProduccion());
cs.setInt(11, objeto.getIdClienteProveedor());
cs.setInt(12, objeto.getIdAlmacen());
cs.setString(13, objeto.getObservaciones());
cs.setString(14, usuario.getNick());
cs.setString(15, xml);
cs.execute();
cnn.close();
cs.close();
} catch (SQLException ex) {
throw ex;
}
}
@SuppressWarnings("deprecation")
protected void spSalFactD(Map params) {
try {
Connection conn = getSession().connection();
CallableStatement cs =
conn.prepareCall("{call SP_HRSAL_FACTDT(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
int branchId = ((Integer) params.get("branch.id")).intValue();
String hdNo = (String) params.get("fact.no");
int no = ((Integer) params.get("no")).intValue();
int itemId = ((Integer) params.get("item.id")).intValue();
int departId = ((Integer) params.get("depart.id")).intValue();
int personId = ((Integer) params.get("person.id")).intValue();
double amount = ((Double) params.get("amount")).doubleValue();
Date date = new Date(ObjectUtil.toCalendar(params.get("date")).getTimeInMillis());
String workerId = (String) params.get("person.workerId");
String type = (String) params.get("type");
int index = 1;
cs.setInt(index++, branchId);
cs.setString(index++, hdNo);
cs.setInt(index++, no);
cs.setInt(index++, itemId);
cs.setInt(index++, departId);
cs.setInt(index++, personId);
cs.setDouble(index++, amount);
cs.setDate(index++, date);
cs.setString(index++, workerId);
cs.setString(index++, type);
cs.execute();
} catch (Exception e) {
throw new HibernateException(e);
}
}
/**
* Control de transacción en el procedimiento almacenado
*
* @param prod
*/
@Override
public void create2(Producto prod) {
Connection cn = null;
try {
cn = AccesoDB.getConnection();
cn.setAutoCommit(true);
String query = "{call usp_crea_producto(?,?,?,?,?)}";
CallableStatement cstm = cn.prepareCall(query);
cstm.registerOutParameter(1, Types.INTEGER);
cstm.setInt(2, prod.getIdcat());
cstm.setString(3, prod.getNombre());
cstm.setDouble(4, prod.getPrecio());
cstm.setInt(5, prod.getStock());
cstm.executeUpdate();
prod.setIdprod(cstm.getInt(1));
cstm.close();
} catch (SQLException e) {
throw new RuntimeException(e.getMessage());
} catch (Exception e) {
throw new RuntimeException("No se puede crear el producto.");
} finally {
try {
cn.close();
} catch (Exception e) {
}
}
}
public static int insertarVenta(Venta v) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
Venta ven = null;
try {
cs = connection.prepareCall("{ call insertVenta(?, ?, ?, ?) }");
cs.setInt(1, v.getUsuarioVenta().getId());
cs.setDouble(2, v.getSubtotal());
cs.setInt(3, v.getPagoVenta().getIdPago());
cs.setInt(4, v.getUsuarioVenta().getSucursal().getIdSucursal());
rs = cs.executeQuery();
while (rs.next()) {
ven = new Venta(rs.getInt("idVenta"));
}
return ven.getIdVenta();
} catch (Exception ex) {
ex.printStackTrace();
return 0;
} finally {
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public boolean insertarAlquiler(
int idtrabajador,
int idCliente,
ArrayList<Detalle_Alquiler> lista_detalle,
int identificador) {
boolean resultado = false;
Connection cnn = null;
CallableStatement cstmt = null;
CallableStatement cstm1 = null;
CallableStatement cstm2 = null;
int id_alquiler = 0;
try {
cnn = BD.getConnection();
cnn.setAutoCommit(false);
String sql = "call spI_Alquiler (?,?,?,?);";
cstmt = cnn.prepareCall(sql);
cstmt.setInt(1, 2);
cstmt.setInt(2, idCliente);
cstmt.setInt(3, idtrabajador);
cstmt.setInt(4, identificador);
ResultSet rs = cstmt.executeQuery();
if (rs.next()) {
id_alquiler = rs.getInt("int_id");
}
for (int i = 0; i < lista_detalle.size(); i++) {
String sql1 = "call spI_DetalleAlquiler(?,?,?,?,?,?,?);";
cstm1 = cnn.prepareCall(sql1);
cstm1.setInt(1, id_alquiler);
cstm1.setInt(2, lista_detalle.get(i).getMaterial_id());
cstm1.setInt(3, lista_detalle.get(i).getInt_cantidad());
cstm1.setDouble(4, lista_detalle.get(i).getDec_monto());
cstm1.setTimestamp(5, lista_detalle.get(i).getDat_fechfin());
cstm1.setTimestamp(6, lista_detalle.get(i).getDat_fechinicio());
cstm1.setInt(7, lista_detalle.get(i).getInt_horas());
cstm1.execute();
}
// Registrar Pagos
String sql2 = "call spI_Pagos_ByAlquiler(?,?);";
cstm2 = cnn.prepareCall(sql2);
cstm2.setInt(1, 1); // es el codigo del usuario cambiar despues
cstm2.setInt(2, id_alquiler);
cstm2.execute();
cnn.commit();
resultado = true;
} catch (SQLException s) {
try {
cnn.rollback();
} catch (SQLException b) {
}
System.out.println("aquí es :/ " + s);
} finally {
try {
cstmt.close();
cnn.close();
} catch (SQLException ex) {
}
}
return resultado;
}
public void setDouble(String parameterName, double x) throws SQLException {
checkOpen();
try {
((CallableStatement) _stmt).setDouble(parameterName, x);
} catch (SQLException e) {
handleException(e);
}
}
public void setDouble(String parameterName, double x) throws SQLException {
checkOpen();
try {
_stmt.setDouble(parameterName, x);
} catch (SQLException e) {
handleException(e);
}
}
public void ingresar(DetalleFactura objeto, Usuario usuario) throws Exception {
try {
cnn = Conexion.getConexion();
CallableStatement cs = null;
cs = cnn.prepareCall("call uspInsDetalleFactura(?,?,?,?,?,?)");
cs.setInt(1, objeto.getIdProducto());
cs.setInt(2, objeto.getCantidad());
cs.setDouble(3, objeto.getPrecio());
cs.setDouble(4, objeto.getSubTotal());
cs.setInt(5, objeto.getIdFactura());
cs.setString(6, usuario.getNick());
cs.execute();
cnn.close();
cs.close();
} catch (SQLException ex) {
throw ex;
}
}
@Override
public boolean deleteOrder(Order order) {
boolean completedDelete = false;
try {
cs = bd.getConn().prepareCall("{call deleteOrder(?, ?, ?)}");
cs.setDate(1, (Date) order.getDate());
cs.setDouble(2, order.getSubtotal());
cs.setDouble(3, order.getTotal());
cs.executeUpdate();
completedDelete = true;
} catch (Exception e) {
System.out.println(e);
}
return completedDelete;
}
void test() throws Exception {
sql = "?= CALL MY_SQRT(?)";
CallableStatement cs = conn.prepareCall(sql);
cs.registerOutParameter(1, Types.DOUBLE); // sqlType其实被忽略了，所以设什么都没用
cs.setDouble(2, 4.0);
cs.execute();
assertEquals(2.0, cs.getDouble(1));
cs.close();
}
@Override
public boolean updateOrder(
Order order, java.util.Date oldDate, Double oldSubTotal, Double oldTotal) {
boolean updateOrder = false;
try {
cs = bd.getConn().prepareCall("{call updateOrder(?, ?, ?, ?, ?, ?)}");
cs.setDate(1, (Date) order.getDate());
cs.setDouble(2, order.getSubtotal());
cs.setDouble(3, order.getTotal());
cs.setDate(4, (Date) oldDate);
cs.setDouble(5, oldSubTotal);
cs.setDouble(6, oldTotal);
cs.executeUpdate();
updateOrder = true;
} catch (Exception e) {
System.out.println(e);
}
return updateOrder;
}
public boolean insertarMovimiento(Movimiento m) {
boolean resultado = true;
Connection cnn = null;
CallableStatement cstmt = null;
try {
cnn = BD.getConnection();
cnn.setAutoCommit(false);
String sql = "call spI_Movimiento(?,?,?,?,?,?,?,?,?);";
cstmt = cnn.prepareCall(sql);
cstmt.setInt(1, m.getUsuario_id());
cstmt.setString(2, m.getVar_concepto());
cstmt.setDouble(3, m.getDec_monto());
cstmt.setInt(4, m.getInt_tipoOperacion());
cstmt.setTimestamp(5, m.getDat_fecregistro());
cstmt.setInt(6, m.getInt_tipoComprobante());
cstmt.setString(7, m.getVar_numeroComprobante());
cstmt.setDouble(8, m.getDec_cantidad());
cstmt.setInt(9, m.getInt_proveedor());
cstmt.execute();
cnn.commit();
} catch (SQLException a) {
try {
cnn.rollback();
} catch (SQLException b) {
System.out.println("" + b.toString());
} finally {
resultado = false;
}
System.out.println("error al registrar movimiento " + a.toString());
} finally {
try {
cstmt.close();
cnn.close();
} catch (SQLException ex) {
System.out.println("" + ex.getMessage());
}
}
return resultado;
}
public void save(Connection conn, AccountingArMgr accMgr, int rowIndex) throws SQLException {
CallableStatement cstmt = null;
String verbEntryMode = accMgr.getEntryMode().split("-")[0];
try {
this.mCode = 0;
String sql =
"begin FM_AR_PAYMENT_TERM_DETAIL_PKG.SAVE(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11); end;";
cstmt = conn.prepareCall(sql);
DeBug.print(sql);
cstmt.setLong(1, Long.parseLong(accMgr.getCurrentMiscellaneousInvoice().getInvoiceId()));
cstmt.setLong(2, Long.parseLong(this.getPaymentTerm().getAttributeCode()));
cstmt.setDouble(3, Double.parseDouble(this.getPercentage()));
cstmt.setString(
4, Decoder.convertSetString(accMgr.getCurrentMiscellaneousInvoice().getStartDate()));
cstmt.setNull(5, Types.VARCHAR);
cstmt.setNull(6, Types.VARCHAR);
cstmt.setString(
7, Decoder.convertSetString(accMgr.getCurrentMiscellaneousInvoice().getSourceType()));
cstmt.setString(8, Decoder.convertSetString(verbEntryMode));
cstmt.setLong(9, rowIndex);
cstmt.registerOutParameter(10, Types.INTEGER);
cstmt.registerOutParameter(11, Types.VARCHAR);
cstmt.execute();
int returnCode = cstmt.getInt(10);
String returnMessage = cstmt.getString(11);
if (returnCode == -2) {
// Violate unique constraint in database
this.mCode = UNIQUE_VIOLATION;
throw new SQLException(returnMessage);
} else if (returnCode != 0) {
// Non-manageable error.
this.mCode = SAVE_ERROR;
throw new SQLException(returnMessage);
}
} catch (Exception e) {
this.mCode = SAVE_ERROR;
e.printStackTrace();
throw new SQLException(e.getMessage());
} finally {
if (cstmt != null) {
cstmt.close();
}
cstmt = null;
}
}
/** Sets the double */
@Override
public void setDouble(String parameterName, double x) throws SQLException {
try {
_cstmt.setDouble(parameterName, x);
} catch (SQLException e) {
onSqlException(e);
throw e;
} catch (RuntimeException e) {
onRuntimeException(e);
throw e;
}
}
@SuppressWarnings("deprecation")
protected void spFixOnline(Map params) {
try {
int branchId = ((Integer) params.get("branch.id")).intValue();
String departNo = (String) params.get("depart.no");
String type = (String) params.get("type");
String personNo = (String) params.get("person.no");
String itemNo = (String) params.get("item.no");
String date =
CommonUtil.formatCalendar("yyyy/MM/dd", ObjectUtil.toCalendar(params.get("date")));
double value = ((Double) params.get("value")).doubleValue();
String doPerson = (String) params.get("doPerson");
String doDate =
CommonUtil.formatCalendar("yyyy/MM/dd", ObjectUtil.toCalendar(params.get("doDate")));
Connection conn = getSession().connection();
CallableStatement cs =
conn.prepareCall("{call SP_HRSAL_FIXONLINE(?, ?, ?, ?, ?, ?, ?, ?, ?)}");
int index = 1;
cs.setInt(index++, branchId);
cs.setString(index++, departNo);
cs.setString(index++, type);
cs.setString(index++, personNo);
cs.setString(index++, itemNo);
cs.setString(index++, date);
cs.setDouble(index++, value);
cs.setString(index++, doPerson);
cs.setString(index++, doDate);
cs.execute();
} catch (Exception e) {
throw new HibernateException(e);
}
/*
Query q = getSession().getNamedQuery("HRSAL_FIXONLINE")
.setParameter("branchId", params.get("branch.id"))
.setParameter("departNo", params.get("depart.no"))
.setParameter("type", params.get("type"))
.setParameter("personNo", params.get("person.no"))
.setParameter("itemNo", params.get("item.no"))
.setParameter("date", CommonUtil.formatCalendar("yyyy/MM/dd", ObjectUtil.toCalendar(params.get("date"))))
.setParameter("value", params.get("value"))
.setParameter("doPerson", params.get("doPerson"))
.setParameter("doDate", CommonUtil.formatCalendar("yyyy/MM/dd", ObjectUtil.toCalendar(params.get("doDate"))));
q.uniqueResult();
*/
}
public boolean executeCall(String sql, Object... params) {
CallableStatement cs = null;
try {
cs = connection.prepareCall(sql);
for (int i = 1; i <= params.length; i++) {
Object o = params[i - 1];
if (o instanceof Integer) {
cs.setInt(i, (int) o);
} else if (o instanceof String) {
cs.setString(i, (String) o);
} else if (o instanceof Double) {
cs.setDouble(i, (double) o);
} else if (o instanceof BufferedImage) {
InputStream is = null;
if (o != null) {
ByteArrayOutputStream os = new ByteArrayOutputStream();
ImageIO.write((BufferedImage) o, "png", os);
is = new ByteArrayInputStream(os.toByteArray());
}
cs.setBlob(4, is);
} else {
cs.setObject(i, o);
}
}
cs.executeUpdate();
return true;
} catch (Exception e) {
return false;
} finally {
try {
cs.close();
} catch (Exception e) {
logger.info(e.getMessage());
}
;
}
}
public void savePaymentTermDetail(Connection conn, String receiptId) throws SQLException {
CallableStatement cstmt = null;
try {
this.mCode = 0;
String sql = "begin PUR_MISC_RECEIPT_PKG.SAVE_PAYMENT_TERM(:1,:2,:3,:4,:5); end;";
cstmt = conn.prepareCall(sql);
DeBug.print(sql);
cstmt.setInt(1, Integer.parseInt(receiptId));
cstmt.setInt(2, Integer.parseInt(this.getPaymentTerm().getAttributeId()));
cstmt.setDouble(3, Double.parseDouble(getPercentage()));
cstmt.registerOutParameter(4, Types.INTEGER);
cstmt.registerOutParameter(5, Types.VARCHAR);
cstmt.execute();
int returnCode = cstmt.getInt(4);
String returnMessage = cstmt.getString(5);
DeBug.print(String.valueOf(returnCode), "(in Save : the returnCode )");
DeBug.print(returnMessage, "(in Save : the returnMessage )");
if (returnCode == -2) {
// Violate unique constraint in database
this.mCode = UNIQUE_VIOLATION;
throw new SQLException(returnMessage);
} else if (returnCode != 0) {
// Non-manageable error.
this.mCode = SAVE_ERROR;
throw new SQLException(returnMessage);
}
} catch (Exception e) {
this.mCode = SAVE_ERROR;
throw new SQLException(e.getMessage());
} finally {
if (cstmt != null) {
cstmt.close();
}
cstmt = null;
}
}
public void testUpdateDecimal() throws Throwable {
try {
Statement stmt = con.createStatement();
stmt.execute(createDecimalTab);
boolean ret = stmt.execute(createUpdateFloat);
stmt.close();
PreparedStatement pstmt = con.prepareStatement("insert into decimal_tab values (?,?)");
// note these are reversed on purpose
pstmt.setDouble(1, doubleValues[1]);
pstmt.setDouble(2, doubleValues[0]);
pstmt.executeUpdate();
pstmt.close();
} catch (Exception ex) {
fail(ex.getMessage());
throw ex;
}
try {
CallableStatement cstmt = con.prepareCall("{ call updatefloat_proc(?,?) }");
cstmt.setDouble(1, doubleValues[0]);
cstmt.setDouble(2, doubleValues[1]);
cstmt.executeUpdate();
cstmt.close();
ResultSet rs = con.createStatement().executeQuery("select * from decimal_tab");
assertTrue(rs.next());
assertTrue(rs.getDouble(1) == doubleValues[0]);
assertTrue(rs.getDouble(2) == doubleValues[1]);
rs.close();
} catch (Exception ex) {
fail(ex.getMessage());
} finally {
try {
Statement dstmt = con.createStatement();
dstmt.execute("drop function updatefloat_proc(float, float)");
} catch (Exception ex) {
}
}
}
/*
* prepareStatement
* This method follows the logic of preparedDataCommon.prepareStatement() except that since it is only
* called for CFPROCPARAM's it doesn't need to iterate over the data VectorArrayList. Instead it only
* needs to extract the one value.
*/
private void prepareStatement(String paramName, CallableStatement CallStatmt, Connection _conn)
throws dataNotSupportedException, cfmRunTimeException, SQLException {
// Map the CFML type to a JDBC type
int jType = getJdbcType(CallStatmt, cfSqlType);
paramName = paramName.replace("@", "");
if (passAsNull) {
// JDBC drivers don't recognize ORACLE_NCLOB so we need to pass it in as a Types.CHAR
if (jType == ORACLE_NCLOB) CallStatmt.setNull(paramName, Types.CHAR);
else CallStatmt.setNull(paramName, jType);
return;
}
// Get the value associated with this CFPROCPARAM
cfData _data = data.get(0);
switch (jType) {
// for MS SQL Server via JDBC-ODBC Bridge, if you try to use setString()
// instead of setObject(), it will pad VARCHAR columns when it shouldn't
case Types.CHAR:
case Types.VARCHAR:
CallStatmt.setObject(paramName, _data.getString(), jType);
break;
case Types.LONGVARCHAR:
CallStatmt.setObject(paramName, _data.getString(), jType);
break;
case ORACLE_NCLOB:
CallStatmt.setObject(paramName, _data.getString(), jType);
break;
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
CallStatmt.setObject(paramName, ((cfBinaryData) _data).getByteArray(), jType);
break;
case Types.TINYINT:
case Types.SMALLINT:
case Types.INTEGER:
if (_data.getNumber().isInt()) {
CallStatmt.setInt(paramName, _data.getInt());
break;
}
// if not an int, fall through to next case
case Types.BIGINT:
double d = _data.getDouble();
if (d <= Long.MAX_VALUE) {
if (isSetLongSupported(_conn)) {
CallStatmt.setLong(paramName, _data.getLong());
} else {
CallStatmt.setDouble(paramName, d);
}
} else {
CallStatmt.setDouble(paramName, d);
}
break;
case Types.DECIMAL:
case Types.NUMERIC:
try {
// NOTE: if a customer is complaining about losing decimal places then make sure they
// are setting the scale properly in cfqueryparam. The default value for scale
// is 0 which causes all decimal places to be removed.
CallStatmt.setBigDecimal(
paramName,
new BigDecimal(_data.getDouble()).setScale(scale, BigDecimal.ROUND_HALF_UP));
break;
} catch (Exception e) {
// fall through to next case
}
case Types.FLOAT:
case Types.DOUBLE:
CallStatmt.setDouble(paramName, _data.getDouble());
break;
case Types.REAL:
CallStatmt.setFloat(paramName, new Float(_data.getDouble()).floatValue());
break;
case Types.DATE:
long date =
(_data.getDataType() == cfData.CFDATEDATA
? _data.getLong()
: _data.getDateData().getLong());
try {
CallStatmt.setDate(paramName, new java.sql.Date(date));
} catch (SQLException e) { // JDBC-ODBC Bridge doesn't support setDate() for MS SQL Server
CallStatmt.setString(paramName, com.nary.util.Date.formatDate(date, "dd-MMM-yy"));
}
break;
case Types.TIME:
long time =
(_data.getDataType() == cfData.CFDATEDATA
? _data.getLong()
: _data.getDateData().getLong());
try {
CallStatmt.setTime(paramName, new java.sql.Time(time));
} catch (SQLException e) { // JDBC-ODBC Bridge doesn't support setTime() for MS SQL Server
CallStatmt.setString(paramName, com.nary.util.Date.formatDate(time, "hh:mm aa"));
}
break;
case Types.TIMESTAMP:
long ts =
(_data.getDataType() == cfData.CFDATEDATA
? _data.getLong()
: _data.getDateData().getLong());
CallStatmt.setTimestamp(paramName, new java.sql.Timestamp(ts));
break;
case Types.BIT:
CallStatmt.setBoolean(paramName, _data.getBoolean());
break;
case Types.NULL:
CallStatmt.setNull(paramName, getJdbcType(CallStatmt, cfSqlType));
break;
default:
throw newRunTimeException("Unsupported CFSQLTYPE: " + sqlType);
}
}
public void setDouble(String parameterName, double x) throws SQLException {
passThru.setDouble(parameterName, x);
}
static void task3()
throws FileNotFoundException, IOException, InterruptedException, SQLException {
System.out.println("Task3 Started..");
BufferedReader br = new BufferedReader(new FileReader(inputFile));
br.readLine();
br.readLine();
br.readLine();
String task3Input = br.readLine();
br.close();
double supportPercent =
Double.parseDouble(
task3Input.split(":")[1].split(",")[0].split("=")[1].split("%")[0].trim());
int size = Integer.parseInt(task3Input.split(":")[1].split(",")[1].split("=")[1].trim());
if (supportPercent >= 0 && size > 0) {
System.out.println("Task3 Size : " + size);
System.out.println("Task3 Support Percent: " + supportPercent);
BufferedWriter bw = new BufferedWriter(new FileWriter("system.out.3"));
for (int i = 1; i <= size; i++) {
CallableStatement cstmt = con.prepareCall("{CALL GenerateFI(?,?)}");
cstmt.setInt(1, i);
cstmt.setDouble(2, supportPercent / 100);
cstmt.executeQuery();
String sqlTask3 =
"select i.itemname,f.percent from FISet f ,items i where f.itemid = i.itemid order by f.percent,f.isetid";
// String sqlTask3 = "select i.itemname,f.percent from FISet f ,items i where f.itemid =
// i.itemid order by f.isetid";
PreparedStatement selTask3 = con.prepareStatement(sqlTask3);
ResultSet rsTask3 = selTask3.executeQuery(sqlTask3);
int j = 1;
String res = "";
j = 1;
while (rsTask3.next()) {
if (j == 1) {
res += "{";
}
res = res + rsTask3.getString(1);
if (j == i) {
res += "}, s=" + rsTask3.getDouble(2) + "%";
bw.write(res);
bw.newLine();
j = 1;
res = "";
} else {
res += ", ";
j++;
}
}
rsTask3.close();
selTask3.close();
}
bw.close();
System.out.println("Task3 Completed...\n");
} else {
System.out.println(
"Support percent should a postive number and size should be a positive integer.");
}
}
static void task4()
throws FileNotFoundException, IOException, InterruptedException, SQLException {
System.out.println("Task4 Started...");
BufferedReader br = new BufferedReader(new FileReader(inputFile));
br.readLine();
br.readLine();
br.readLine();
br.readLine();
String task4Input = br.readLine();
br.close();
double supportPercent =
Double.parseDouble(
task4Input.split(":")[1].split(",")[0].split("=")[1].split("%")[0].trim());
double confidence =
Double.parseDouble(
task4Input.split(":")[1].split(",")[1].split("=")[1].split("%")[0].trim());
int size = Integer.parseInt(task4Input.split(":")[1].split(",")[2].split("=")[1].trim());
if (supportPercent >= 0 && size >= 2 && confidence >= 0) {
System.out.println("Task4 Confidence Percent: " + confidence);
System.out.println("Task4 Support Percent: " + supportPercent);
System.out.println("Task4 Size : " + size);
BufferedWriter bw = new BufferedWriter(new FileWriter("system.out.4"));
for (int i = 2; i <= size; i++) {
CallableStatement cstmt = con.prepareCall("{CALL GenerateAR(?,?,?)}");
cstmt.setDouble(1, confidence / 100);
cstmt.setInt(2, i);
cstmt.setDouble(3, supportPercent / 100);
cstmt.executeQuery();
String sqlTask4 = "select * from artable order by ruleid,confidence";
PreparedStatement selTask4 = con.prepareStatement(sqlTask4);
ResultSet rsTask4 = selTask4.executeQuery(sqlTask4);
String leftSet = "";
String rightSet = "";
Boolean resultSetExhausted = false;
String itemName;
String isLeft;
Double support;
Double conf;
if (rsTask4.next()) {
while (true && !resultSetExhausted) {
leftSet = "{";
rightSet = "{";
for (int j = 0; j < i; j++) {
itemName = rsTask4.getString(3);
isLeft = rsTask4.getString(5);
// support = rsTask4.getBigDecimal(6);
// conf = rsTask4.getBigDecimal(4);
if (j == 0) {
if (isLeft.equals("Y")) leftSet = leftSet + itemName;
else rightSet = rightSet + itemName;
if (!rsTask4.next()) {
resultSetExhausted = true;
break;
}
}
if (j == i - 1) {
if (isLeft.equals("Y")) {
if (leftSet.equals("{")) leftSet = leftSet + itemName;
else leftSet = leftSet + "," + itemName;
} else {
if (rightSet.equals("{")) rightSet = rightSet + itemName;
else rightSet = rightSet + ", " + itemName;
}
leftSet = leftSet + "}";
rightSet = rightSet + "}";
String resultString =
"{"
+ leftSet
+ " - > "
+ rightSet
+ "} s="
+ rsTask4.getDouble(6)
+ "%, c="
+ rsTask4.getDouble(4)
+ "%";
bw.write(resultString);
bw.newLine();
leftSet = "{";
rightSet = "{";
if (!rsTask4.next()) {
resultSetExhausted = true;
break;
}
}
if (j > 0 && j < i - 1) {
if (isLeft.equals("Y")) {
if (leftSet.equals("{")) leftSet = leftSet + itemName;
else leftSet = leftSet + ", " + itemName;
} else {
if (rightSet.equals("{")) rightSet = rightSet + itemName;
else rightSet = rightSet + ", " + itemName;
}
if (!rsTask4.next()) {
resultSetExhausted = true;
break;
}
}
}
}
}
}
bw.close();
System.out.println("Task4 Completed...\n");
} else
System.out.println(
"For Task4, supportPercent and confidence should be a positive numbers and size should be greater than or equal to 2");
}
/** @see java.sql.PreparedStatement#setDouble(int, double) */
public void setDouble(int parameterIndex, double x) throws SQLException {
original.setDouble(parameterIndex, x);
}
/** @see java.sql.CallableStatement#setDouble(java.lang.String, double) */
public void setDouble(String parameterName, double x) throws SQLException {
original.setDouble(parameterName, x);
}
private static void testMisc(Connection conn) throws Throwable {
System.out.println("==============================================");
System.out.println("TESTING BOUNDARY CONDITIONS");
System.out.println("==============================================\n");
Statement scp = conn.createStatement();
scp.execute(
"CREATE PROCEDURE takesString(OUT P1 VARCHAR(40), IN P2 INT) "
+ "EXTERNAL NAME '"
+ CLASS_NAME
+ "takesString'"
+ " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
CallableStatement cs = conn.prepareCall("call takesString(?,?)");
// register a normal int as an output param, should fail
boolean failed = false;
try {
cs.registerOutParameter(2, Types.INTEGER);
} catch (SQLException se) {
failed = true;
System.out.println("Expected exception " + se);
}
if (!failed) {
System.out.println("registerOutParameter on non-output didn't fail");
}
// invalid param number
failed = false;
try {
cs.registerOutParameter(9, Types.INTEGER);
} catch (SQLException se) {
failed = true;
System.out.println("Expected exception " + se);
}
if (!failed) {
System.out.println("registerOutParameter on bad value didn't fail");
}
// invalid param number
failed = false;
try {
cs.registerOutParameter(0, Types.INTEGER);
} catch (SQLException se) {
failed = true;
System.out.println("Expected exception " + se);
}
if (!failed) {
System.out.println("registerOutParameter on bad value didn't fail");
}
// set before register, bad type, should fail as is output parameter.
try {
cs.setDouble(1, 1);
System.out.println("FAIL setDouble() on takesString() accepted");
} catch (SQLException se) {
System.out.println("Expected exception " + se);
}
// set before register, should fail as is output parameter.
try {
cs.setString(1, "hello");
System.out.println("FAIL setString() on takesString() accepted");
} catch (SQLException se) {
System.out.println("Expected exception " + se);
}
cs.registerOutParameter(1, Types.CHAR);
cs.setInt(2, Types.INTEGER);
try {
cs.execute();
} catch (SQLException se) {
System.out.println("cs.execute() got unexpected exception: " + se);
}
// shouldn't have to reregister the type, and shouldn't
// need to set the output parameters
cs.clearParameters();
cs.setInt(2, Types.INTEGER);
try {
cs.execute();
} catch (SQLException se) {
System.out.println("cs.execute() got unexpected exception: " + se);
}
cs.close();
scp.execute("DROP PROCEDURE takesString");
scp.execute(
"CREATE FUNCTION returnsString(P2 INT) RETURNS VARCHAR(40) "
+ "EXTERNAL NAME '"
+ CLASS_NAME
+ "returnsString'"
+ " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
// return output params -- cannot do set on return output param
cs = conn.prepareCall("? = call returnsString(?)");
try {
cs.setString(1, new String("test"));
System.out.println("ERROR: setString() on return output parameter succeeded");
} catch (SQLException se) {
System.out.println("Expected exception on setString() on a return output param: " + se);
}
cs.close();
scp.execute("DROP FUNCTION returnsString");
// lets try ? = call syntax on a call that doesn't return anything
scp.execute(
"CREATE PROCEDURE returnsNothing() "
+ "EXTERNAL NAME '"
+ CLASS_NAME
+ "returnsNothing'"
+ " NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");
try {
cs = conn.prepareCall("? = call returnsNothing()");
System.out.println("ERROR: no exception on prepare of '? = call returnsNothing()");
} catch (SQLException se) {
System.out.println("Expected exception on prepare of '? = call returnsNothing()': " + se);
}
scp.execute("DROP PROCEDURE returnsNothing");
}
private static void callSetMethod(CallableStatement cs, int arg, int type, StringBuilder strbuf)
throws Throwable {
switch (type) {
case Types.BIT:
case Types.BOOLEAN:
strbuf.append("setBoolean(" + arg + ", true)");
cs.setBoolean(arg, true);
break;
case Types.TINYINT:
strbuf.append("setByte(" + arg + ", 6)");
cs.setByte(arg, (byte) 6);
break;
case Types.SMALLINT:
strbuf.append("setShort(" + arg + ", 66)");
cs.setShort(arg, (short) 66);
break;
case Types.INTEGER:
strbuf.append("setInt(" + arg + ", 666)");
cs.setInt(arg, 666);
break;
case Types.BIGINT:
strbuf.append("setLong(" + arg + ", 666)");
cs.setLong(arg, 666);
break;
case Types.FLOAT:
case Types.REAL:
strbuf.append("setFLoat(" + arg + ", 666)");
cs.setFloat(arg, 666);
break;
case Types.DOUBLE:
strbuf.append("setDouble(" + arg + ", 666)");
cs.setDouble(arg, 666);
break;
case Types.DECIMAL:
case Types.NUMERIC:
strbuf.append("setBigDecimal(" + arg + ", 666.666)");
BigDecimalHandler.setBigDecimalString(cs, arg, "666.666");
break;
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
strbuf.append("setString(" + arg + ", \"Set via setString()\")");
cs.setString(arg, "Set via setString()");
break;
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
strbuf.append("setBytes(" + arg + ", byte[])");
byte[] myarray = new byte[16];
myarray[0] = (byte) 255;
cs.setBytes(arg, myarray);
break;
case Types.DATE:
strbuf.append("setDate(" + arg + ", Date.valueOf(1999-09-09))");
cs.setDate(arg, Date.valueOf("1999-09-09"));
break;
case Types.TIME:
strbuf.append("setTime(" + arg + ", Time.valueOf(09:09:09))");
cs.setTime(arg, Time.valueOf("09:09:09"));
break;
case Types.TIMESTAMP:
strbuf.append("setTimestamp(" + arg + ", Timestamp.valueOf(1999-09-09 09:09:09.999))");
cs.setTimestamp(arg, Timestamp.valueOf("1999-09-09 09:09:09.999"));
break;
case Types.OTHER:
strbuf.append("setObject(" + arg + ", new BigInteger(666))");
cs.setObject(arg, new BigInteger("666"));
break;
default:
throw new Throwable("TEST ERROR: unexpected type " + type);
}
}
