1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 package net.sf.emarket.account.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.account.domain.AcctPosition;
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 JdbcAccountPositionDaoImpl implements IAccountPositionDao {
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
49 public AcctPosition addAccountPosition(AcctPosition position) {
50
51 String insertSql = "insert into EM_ACCT_POSITION"
52 + "( EM_ACCT_POSITION_ID, EM_ACCT_ID, EM_ORDER_ID, EM_FILL_ID, EM_SYMBOL, "
53 + " EM_SYMBOL_TYPE , EM_QUANTITY, EM_PURCHASE_PRICE, EM_POSITION_TYPE, EM_TOTAL_PRICE, EM_TIME)"
54 + " values( ?,?,?,?,?, ?,?,?,?,?, CURRENT_TIMESTAMP)";
55
56 jdbcTemplate.update(insertSql,
57 position.getId(),
58 position.getAcctId(),
59 position.getOrderId(),
60 position.getFillId(),
61 position.getSymbol(),
62
63 position.getSymbolType(),
64 position.getQuantity(),
65 position.getPurchasePrice(),
66 position.getType(),
67 position.getTotalPrice()
68 );
69
70 return position;
71 }
72
73 public long generateAcctPositionId() {
74
75 long acctPositionId;
76
77
78
79
80
81 acctPositionId = SequenceNumberGenerator.getNext("EM_ACCT_POSITION_ID_SEQ", jdbcTemplate);
82
83 return acctPositionId;
84 }
85
86 public AcctPosition getAccountPosition(long acctPositionId) {
87
88 String querySql = "select EM_ACCT_POSITION_ID, EM_ACCT_ID, EM_ORDER_ID, EM_FILL_ID, EM_SYMBOL, " +
89 "EM_SYMBOL_TYPE , EM_QUANTITY, EM_PURCHASE_PRICE, EM_POSITION_TYPE, EM_TOTAL_PRICE, EM_TIME " +
90 "from EM_ACCT_POSITION where EM_ACCT_POSITION_ID = :ACCT_POS_ID";
91
92 MapSqlParameterSource mapSql = new MapSqlParameterSource();
93 mapSql.addValue("ACCT_POS_ID", acctPositionId );
94
95 AcctPosition position = jdbcTemplate.queryForObject( querySql,
96 new AcctPositionMapper(),
97 mapSql);
98
99 return position;
100 }
101
102 public List<AcctPosition> getAccountPositions(String acctId) {
103
104 String querySql = "select EM_ACCT_POSITION_ID, EM_ACCT_ID, EM_ORDER_ID, EM_FILL_ID, EM_SYMBOL, " +
105 "EM_SYMBOL_TYPE , EM_QUANTITY, EM_PURCHASE_PRICE, EM_POSITION_TYPE, EM_TOTAL_PRICE, EM_TIME " +
106 "from EM_ACCT_POSITION where EM_ACCT_ID = :ACCT_ID";
107
108 MapSqlParameterSource mapSql = new MapSqlParameterSource();
109 mapSql.addValue("ACCT_ID", acctId );
110
111 List<AcctPosition> positions = jdbcTemplate.query( querySql,
112 new AcctPositionMapper(),
113 mapSql);
114
115 return positions;
116 }
117
118 public List<AcctPosition> getAccountPositions(String acctId, String symbol) {
119
120 String querySql = "select EM_ACCT_POSITION_ID, EM_ACCT_ID, EM_ORDER_ID, EM_FILL_ID, EM_SYMBOL, " +
121 "EM_SYMBOL_TYPE , EM_QUANTITY, EM_PURCHASE_PRICE, EM_POSITION_TYPE, EM_TOTAL_PRICE, EM_TIME " +
122 "from EM_ACCT_POSITION where EM_ACCT_ID = :ACCT_ID and EM_SYMBOL = :SYMBOL";
123
124 MapSqlParameterSource mapSql = new MapSqlParameterSource();
125 mapSql.addValue("ACCT_ID", acctId );
126 mapSql.addValue("SYMBOL", symbol );
127
128 List<AcctPosition> positions = jdbcTemplate.query( querySql,
129 new AcctPositionMapper(),
130 mapSql);
131
132 return positions;
133 }
134
135 public void deleteAccountPosition( AcctPosition position ){
136
137 String deleteSql = "delete from EM_ACCT_POSITION where EM_ACCT_POSITION_ID = ? ";
138
139 int affected = jdbcTemplate.update( deleteSql, position.getId());
140
141 if( affected != 1 ){
142
143 }
144 }
145
146 public AcctPosition updateAccountPosition(AcctPosition position) {
147
148 String updateSql = "update EM_ACCT_POSITION set EM_QUANTITY = ? , EM_TIME = CURRENT_TIMESTAMP "+
149 "where EM_ACCT_POSITION_ID = ? ";
150
151 int affected = jdbcTemplate.update(updateSql,
152 position.getQuantity(),
153 position.getId()
154 );
155 if( affected != 1 ){
156
157 }
158
159 return position;
160 }
161
162 private static final String ACCT_POSITION_ID = "EM_ACCT_POSITION_ID";
163 private static final String ACCT_ID = "EM_ACCT_ID";
164 private static final String ORDER_ID = "EM_ORDER_ID";
165 private static final String FILL_ID = "EM_FILL_ID";
166 private static final String SYMBOL = "EM_SYMBOL";
167 private static final String SYMBOL_TYPE = "EM_SYMBOL_TYPE";
168 private static final String QUANTITY = "EM_QUANTITY";
169 private static final String PURCHASE_PRICE = "EM_PURCHASE_PRICE";
170 private static final String POSITION_TYPE = "EM_POSITION_TYPE";
171 private static final String TOTAL_PRICE = "EM_TOTAL_PRICE";
172 private static final String TIME = "EM_TIME";
173
174 private static class AcctPositionMapper implements ParameterizedRowMapper<AcctPosition> {
175
176 public AcctPosition mapRow(ResultSet rs, int rowNum) throws SQLException {
177
178 AcctPosition position = new AcctPosition();
179
180 position.setId( rs.getLong(ACCT_POSITION_ID));
181 position.setAcctId( rs.getString( ACCT_ID ) );
182 position.setOrderId(rs.getString(ORDER_ID));
183 position.setFillId( rs.getLong(FILL_ID));
184 position.setSymbol(rs.getString(SYMBOL));
185 position.setSymbolType(rs.getString(SYMBOL_TYPE));
186 position.setQuantity(rs.getLong(QUANTITY));
187 position.setPurchasePrice(rs.getFloat(PURCHASE_PRICE));
188 position.setType( rs.getString(POSITION_TYPE) );
189 position.setTotalPrice(rs.getFloat(TOTAL_PRICE));
190 position.setLastUpdated(rs.getTimestamp( TIME));
191
192 return position;
193 }
194 }
195 }