| // The LOWERPRICE procedure determines which items are selling poorly (that |
| // is, have less than 50 orders) and lowers the price of these items in the |
| // database by 10 percent. |
| // |
| // See http://trafodion.apache.org/docs/spj_guide/index.html#lowerprice-procedure |
| // for more documentation. |
| public static void lowerPrice() throws SQLException |
| { |
| Connection conn = |
| DriverManager.getConnection( "jdbc:default:connection" ) ; |
| |
| PreparedStatement getParts = |
| conn.prepareStatement( "SELECT p.partnum, " |
| + "SUM(qty_ordered) AS qtyOrdered " |
| + "FROM trafodion.sales.parts p " |
| + "LEFT JOIN trafodion.sales.odetail o " |
| + "ON p.partnum = o.partnum " |
| + "GROUP BY p.partnum" |
| ) ; |
| |
| PreparedStatement updateParts = |
| conn.prepareStatement( "UPDATE trafodion.sales.parts " |
| + "SET price = price * 0.9 " |
| + "WHERE partnum = ?" |
| ) ; |
| |
| ResultSet rs = getParts.executeQuery() ; |
| while ( rs.next() ) |
| { |
| BigDecimal qtyOrdered = rs.getBigDecimal( 2 ) ; |
| |
| if (( qtyOrdered == null ) || ( qtyOrdered.intValue() < 50 ) ) |
| { |
| BigDecimal partnum = rs.getBigDecimal( 1 ) ; |
| updateParts.setBigDecimal( 1, partnum ) ; |
| updateParts.executeUpdate() ; |
| } |
| } |
| |
| rs.close() ; |
| conn.close() ; |
| |
| } |
| |