1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 package net.sf.emarket.order.repository;
21
22 import java.sql.ResultSet;
23 import java.sql.SQLException;
24 import java.util.List;
25
26 import javax.sql.DataSource;
27
28 import net.sf.emarket.order.domain.Order;
29 import net.sf.emarket.utils.SequenceNumberGenerator;
30
31 import org.springframework.beans.factory.annotation.Autowired;
32 import org.springframework.beans.factory.annotation.Qualifier;
33 import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
34 import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
35 import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
36 import org.springframework.stereotype.Repository;
37
38 @Repository
39 public class JdbcOrderDaoImpl implements IOrderDao{
40
41 private SimpleJdbcTemplate jdbcTemplate;
42
43 @Autowired
44 public void createTemplate(@Qualifier("jdbcDataSource") DataSource jdbcDatasource){
45 this.jdbcTemplate = new SimpleJdbcTemplate(jdbcDatasource);
46 }
47
48 public Order addOrder(Order order) {
49
50 String insertOrderSql =
51 "INSERT INTO EM_ORDER "+
52 "( em_order_id, em_acct_id, em_symbol, em_exchange, em_quantity, " +
53 " em_order_type, em_order_term, em_price_type, em_limit_price, em_order_status, " +
54 " em_quote_id, EM_COMMISSION, EM_TOTAL_COST, EM_CREATION_TIME, em_time ) "+
55 "values (?,?,?,?,?, ?,?,?,?,?, ?,?,?,CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)";
56
57 jdbcTemplate.update( insertOrderSql,
58 order.getId(),
59 order.getAcctId(),
60 order.getSymbol(),
61 order.getExchange(),
62 order.getQuantity(),
63
64 order.getOrderType(),
65 order.getOrderTerm(),
66 order.getPriceType(),
67 order.getLimitPrice(),
68 order.getStatus(),
69
70 order.getQuoteId(),
71 order.getCommission(),
72 order.getTotalCost()
73 );
74
75 return order;
76 }
77
78
79 /***
80 * Updates an order based on "orderId", which maps to "EM_ORDER_ID" column.
81 * Only some columns can be updated.
82 * updated fields are,
83 * <ul>
84 * <li>quantity</li>
85 * <li>commission</li>
86 * <li>status</li>
87 * </ul>
88 *
89 * @param order
90 * @return
91 */
92 public Order updateOrder( Order order ){
93
94 String updateOrderSql =
95 "UPDATE EM_ORDER "+
96 "SET em_filled_quantity = ? , em_order_status = ? , em_commission = ? , em_time = CURRENT_TIMESTAMP " +
97 "WHERE em_order_id = ? ";
98
99 int affected = jdbcTemplate.update( updateOrderSql,
100 order.getFilledQuantity(),
101 order.getStatus(),
102 order.getCommission(),
103 order.getId()
104 );
105
106 if( affected ==1 ){
107
108 }else{
109 }
110
111 return order;
112 }
113
114 /***
115 *
116 */
117 public Order getOrderById(String orderId) {
118
119 String queryOrderSql =
120 "SELECT " +
121 "em_order_id, em_acct_id, em_symbol, em_exchange, em_quantity, em_filled_quantity, " +
122 "em_order_type, em_order_term, em_price_type, em_limit_price, em_order_status, " +
123 "EM_QUOTE_ID, EM_COMMISSION, EM_TOTAL_COST, EM_CREATION_TIME, em_time "+
124 "FROM EM_ORDER "+
125 "WHERE EM_ORDER_ID = :ORDER_ID ";
126
127 MapSqlParameterSource mapSql = new MapSqlParameterSource();
128 mapSql.addValue("ORDER_ID", orderId );
129
130 Order order = jdbcTemplate.queryForObject( queryOrderSql,
131 new OrderMapper(),
132 mapSql);
133 return order;
134 }
135
136 /***
137 *
138 */
139 public List<Order> getOrdersForAcctIdWithStatus(String acctId,
140 String orderStatus) {
141
142 String queryOrderSql =
143 "SELECT " +
144 "em_order_id, em_acct_id, em_symbol, em_exchange, em_quantity, em_filled_quantity, " +
145 "em_order_type, em_order_term, em_price_type, em_limit_price, em_order_status, " +
146 "EM_QUOTE_ID, EM_COMMISSION, EM_TOTAL_COST, EM_CREATION_TIME, em_time "+
147 "FROM EM_ORDER "+
148 "WHERE em_acct_id = :ACCT_ID AND em_order_status = :STATUS ";
149
150 MapSqlParameterSource mapSql = new MapSqlParameterSource();
151 mapSql.addValue("ACCT_ID", acctId );
152 mapSql.addValue("STATUS", orderStatus );
153
154 List<Order> orders = jdbcTemplate.query( queryOrderSql,
155 new OrderMapper(),
156 mapSql);
157 return orders;
158 }
159
160 public List<Order> getOrdersForAcctIdWithStatusAndSymbol(String acctId, String orderStatus, String symbol) {
161
162 String queryOrderSql =
163 "SELECT " +
164 "em_order_id, em_acct_id, em_symbol, em_exchange, em_quantity, em_filled_quantity, " +
165 "em_order_type, em_order_term, em_price_type, em_limit_price, em_order_status, " +
166 "EM_QUOTE_ID, EM_COMMISSION, EM_TOTAL_COST, EM_CREATION_TIME, em_time "+
167 "FROM EM_ORDER "+
168 "WHERE em_acct_id = :ACCT_ID AND em_order_status = :STATUS AND em_symbol =:SYMBOL";
169
170 MapSqlParameterSource mapSql = new MapSqlParameterSource();
171 mapSql.addValue("ACCT_ID", acctId );
172 mapSql.addValue("STATUS", orderStatus );
173 mapSql.addValue("SYMBOL", symbol );
174
175 List<Order> orders = jdbcTemplate.query( queryOrderSql,
176 new OrderMapper(),
177 mapSql);
178 return orders;
179 }
180
181 public long generateOrderId(){
182
183 long orderId = 0;
184
185
186
187
188
189
190 orderId = SequenceNumberGenerator.getNext("EM_ORDER_ID_SEQ", jdbcTemplate);
191
192 return orderId;
193 }
194
195
196 private static final String ORDER_ID = "EM_ORDER_ID";
197 private static final String ACCT_ID = "EM_ACCT_ID";
198 private static final String SYMBOL = "EM_SYMBOL";
199 private static final String EXCHANGE = "EM_EXCHANGE";
200 private static final String QUANTITY = "EM_QUANTITY";
201 private static final String FILLED_QUANTITY = "EM_FILLED_QUANTITY";
202 private static final String ORDER_TYPE = "EM_ORDER_TYPE";
203 private static final String ORDER_TERM = "EM_ORDER_TERM";
204 private static final String PRICE_TYPE = "EM_PRICE_TYPE";
205 private static final String LIMIT_PRICE = "EM_LIMIT_PRICE";
206 private static final String ORDER_STATUS = "EM_ORDER_STATUS";
207 private static final String QUOTE_ID = "EM_QUOTE_ID";
208 private static final String COMMISSION = "EM_COMMISSION";
209 private static final String TOTAL_COST = "EM_TOTAL_COST";
210 private static final String CREATION_TIME = "EM_CREATION_TIME";
211 private static final String TIME = "EM_TIME";
212
213 private static class OrderMapper implements ParameterizedRowMapper<Order> {
214
215 public Order mapRow(ResultSet rs, int rowNum) throws SQLException {
216
217 Order order = new Order();
218
219 order.setId( rs.getString( ORDER_ID) );
220 order.setAcctId( rs.getString(ACCT_ID));
221 order.setSymbol( rs.getString(SYMBOL));
222 order.setExchange( rs.getString(EXCHANGE));
223 order.setQuantity(rs.getInt(QUANTITY));
224 order.setFilledQuantity(rs.getInt(FILLED_QUANTITY));
225 order.setOrderTerm(rs.getString(ORDER_TERM));
226 order.setOrderType(rs.getString(ORDER_TYPE));
227 order.setPriceType(rs.getString(PRICE_TYPE));
228 order.setLimitPrice(rs.getFloat(LIMIT_PRICE));
229 order.setStatus(rs.getString(ORDER_STATUS));
230 order.setQuoteId( rs.getInt(QUOTE_ID) );
231 order.setCommission( rs.getFloat(COMMISSION));
232 order.setTotalCost( rs.getFloat(TOTAL_COST ));
233 order.setCreationTime( rs.getTimestamp(CREATION_TIME));
234 order.setTime(rs.getTimestamp(TIME));
235
236 return order;
237 }
238 }
239
240
241 }