2j0dhTZ4

1@Entity
2@Table
3@XmlRootElement
4public class Account implements Serializable{
5    @Id
6    private int accountNo;
7    private int cId;
8    private int balance;
9    private int sortCode;
10
11    public Account(int accountNo, int cId, int balance, int sortCode) {
12        this.accountNo = accountNo;
13        this.cId = cId;
14        this.balance = balance;
15        this.sortCode = sortCode;
16    }
17
18    public int getAccountNo() {
19        return accountNo;
20    }
21
22    public void setAccountNo(int accountNo) {
23        this.accountNo = accountNo;
24    }
25
26    public int getcId() {
27        return cId;
28    }
29
30    public void setcId(int cId) {
31        this.cId = cId;
32    }
33
34    public int getBalance() {
35        return balance;
36    }
37
38    public void setBalance(int balance) {
39        this.balance = balance;
40    }
41
42    public int getSortCode() {
43        return sortCode;
44    }
45
46    public void setSortCode(int sortCode) {
47        this.sortCode = sortCode;
48    }
49
50
51}
52	
53public class AccountService {
54
55    PersistenceManager pm;
56
57    public AccountService() {
58        pm = new PersistenceManager();
59
60    }
61
62    public List<Account> getBalances(int custId) {
63        CriteriaBuilder cb = pm.getBuilder();
64        CriteriaQuery cq = cb.createQuery(Account.class);
65        Root<Account> rootEntry = cq.from(Account.class);
66        CriteriaQuery<Account> all = cq.select(rootEntry);
67        all.where(cb.equal(rootEntry.get("cId"), custId));
68        TypedQuery<Account> allQuery = pm.getEntityManager().createQuery(cq);
69        return allQuery.getResultList();
70    }
71
72    public Account getBalance(int accNo) {
73        Account account = pm.getEntityManager().find(Account.class, accNo);
74        pm.closeTransaction();
75        return account;
76    }
77
78    public void deleteAccount(int accountNo){
79        Account account = pm.getEntityManager().find(Account.class, accountNo);
80        if(account != null){
81            pm.startTransaction();
82            pm.remove(account);
83            pm.commit();
84            pm.closeTransaction();
85        }
86    }
87
88    public Account createAccount(Account account) {
89        Account acc = pm.getEntityManager().find(Account.class, account.getAccountNo());
90        if (acc == null) {
91            pm.startTransaction();
92            pm.persist(acc);
93            pm.commit();
94            pm.closeTransaction();
95        }
96        return account;
97    }
98
99    public Account updateBalance(Account newAccount) {
100        Account account = pm.getEntityManager().find(Account.class, newAccount.getAccountNo());
101        if (account != null) {
102            pm.startTransaction();
103            account.setBalance(newAccount.getBalance());
104            pm.commit();
105            pm.closeTransaction();
106        }
107        return account;
108    }
109}
110	
111@Path("/accounts")
112@Produces({MediaType.APPLICATION_JSON, MediaType.APPLICATION_XML})
113public class AccountResource {
114    AccountService accounts = new AccountService();
115
116    //Getting balance
117    @GET
118    @Path("/balance/{custId}")
119    public List<Account> getBalance(@PathParam("custId") int id){
120        return accounts.getBalances(id);
121    }
122
123    //Getting balance from account
124    @GET
125    @Path("/balance/{custId}/{accountNo}")
126    public Account getBalance(@PathParam("custId") int id,@PathParam("accountNo") int accountNo){
127        return accounts.getBalance(accountNo);
128    }
129
130    //Delete accounts
131    @DELETE
132    @Path("/{accountNo")
133    public void deleteAccount(@PathParam("accountNo") int id){
134        accounts.deleteAccount(id);
135    }
136
137    @POST
138    @Consumes({MediaType.APPLICATION_XML, MediaType.APPLICATION_JSON})
139    public Account addAccount(Account account){
140        return accounts.createAccount(account);
141    }
142}
143	
144CREATE SCHEMA Banking;
145
146
147/* Customer Table */
148
149DROP TABLE IF EXISTS Banking.Customer;
150
151CREATE TABLE Banking.Customer(
152    custId  int(11)     AUTO_INCREMENT,
153    name        varchar(50)     NOT NULL,
154    address     varchar(300) NOT NULL,
155    email       varchar(100) NOT NULL,
156    phone       int(11) DEFAULT NULL,
157    PRIMARY KEY (custId)
158)   ENGINE=InnoDB AUTO_INCREMENT=210590 DEFAULT CHARSET=utf8;
159
160DROP TABLE IF EXISTS Banking.Account;
161
162CREATE TABLE Banking.Account (
163    accountNo int(11) NOT NULL AUTO_INCREMENT,
164    cId       int(11) NOT NULL,
165    balance   int(11) NOT NULL,
166    sortCode  int(11) NOT NULL,
167    PRIMARY KEY (accountNo),
168    KEY cId (cId),
169    KEY sortCode (sortCode),
170    CONSTRAINT account_ibfk_1 
171    FOREIGN KEY (cId) REFERENCES Banking.CUSTOMER(custId)
172)   ENGINE=InnoDB AUTO_INCREMENT=816410 DEFAULT CHARSET=utf8;
173
174CREATE TABLE Banking.Transaction (
175  _id           int(11)         NOT NULL AUTO_INCREMENT,
176  accountNo     int(11)         NOT NULL,
177  amount        int(11)         NOT NULL,
178  postBalance   int(11)         NOT NULL,
179  type          varchar(255)    DEFAULT NULL,
180  PRIMARY KEY   (_id),
181  KEY accountNo (accountNo),
182  CONSTRAINT transaction_ibfk_2 
183  FOREIGN KEY (accountNo) REFERENCES Account (accountNo)
184) ENGINE=InnoDB AUTO_INCREMENT=229377 DEFAULT CHARSET=utf8;