My aim is to learn JavaEE and what better way to do so than to work on a project. So I set out to create a Stock Market simulation web application. Naturally a person owns some stock identified by a company ticker (company_id) and an associated number of shares owned. So I put these in a Map.
Here are the mysql ddl statements; For the users table
CREATE TABLE `users` (
`user_id` bigint(20) NOT NULL AUTO_INCREMENT,
`firstName` varchar(30) NOT NULL,
`lastName` varchar(30) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
For the portfolios table,
CREATE TABLE `portfolios_tb` (
`user_id` bigint(20) NOT NULL,
`company_id` varchar(4) NOT NULL,
`shares_owned` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`user_id`,`company_id`),
KEY `company_id` (`company_id`),
CONSTRAINT `company_id` FOREIGN KEY (`company_id`) REFERENCES `stocks` (`company_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `uid` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
The Stock entity
import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
/**
*
*/
@Entity
@Table(name = "stocks")
public class Stock implements Serializable {
@Id
@GeneratedValue
@Column(name = "company_id")
String stockId;
@NotNull @Column(name="company_name")
String companyName;
@NotNull @Column(name="shares_listed")
BigInteger sharesListed;
@Column(name="par_value")
BigDecimal parValue;
@Column(name="current_Price")
BigDecimal currentPrice;
public Stock(){
}
public Stock(String stockId, String companyName, BigInteger sharesListed){
this.companyName = companyName;
this.stockId = stockId;
this.sharesListed = sharesListed;
this.parValue = BigDecimal.ZERO;
this.currentPrice = BigDecimal.ZERO;
}
public String getStockId() {
return stockId;
}
public void setStockId(String stockId) {
this.stockId = stockId;
}
public String getCompanyName() {
return companyName;
}
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
public BigInteger getSharesListed() {
return sharesListed;
}
public void setSharesListed(BigInteger sharesListed) {
this.sharesListed = sharesListed;
}
public BigDecimal getParValue() {
return parValue;
}
public void setParValue(BigDecimal parValue) {
this.parValue = parValue;
}
public BigDecimal getCurrentPrice() {
return currentPrice;
}
public void setCurrentPrice(BigDecimal currentPrice) {
this.currentPrice = currentPrice;
}
}
The User entity
import java.io.Serializable;
import java.math.BigInteger;
import java.util.HashMap;
import java.util.Map;
import javax.persistence.CollectionTable;
import javax.persistence.Column;
import javax.persistence.ElementCollection;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.MapKeyColumn;
import javax.persistence.Table;
@Entity
@Table(name = "users")
public class User implements Serializable {
@Id
@GeneratedValue
private String user_id; //I know its not convention, was experimenting.
@Column(name = "firstName")
private String firstName;
@Column(name = "lastName")
private String lastName;
@ElementCollection
@CollectionTable(name = "portfolios_tb")
@MapKeyColumn(name = "company_id")
@Column(name = "shares_owned")
Map<String, BigInteger> stocksOwned = new HashMap<>();
public User() {
}
public User(String firstName, String lastName) {
this.stocksOwned = new HashMap<>();
this.firstName = firstName;
this.lastName = lastName;
}
public String getUser_id() {
return user_id;
}
public void setUser_id(String user_id) {
this.user_id = user_id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public Map<String, BigInteger> getStocksOwned() {
return stocksOwned;
}
public void setStocksOwned(Map<String, BigInteger> stocksOwned) {
this.stocksOwned = stocksOwned;
}
}
Here's the main class
public class Main {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("hisaMarket_Version2PU");
EntityManager em = emf.createEntityManager();
public static void main(String[] args) {
Main main = new Main();
main.getUsers();
}
public void getUsers(){
EntityTransaction tx = em.getTransaction();
tx.begin();
TypedQuery<User> query = em.createQuery("SELECT u FROM User u", User.class);
List<User> users = query.getResultList();
for(User user : users){
System.out.print(user.getFirstName() + " "+ user.getLastName() +" owns ");
Map<String,BigInteger> stocks = user.getStocksOwned();
Set<String> keys = stocks.keySet();
//planning to display Map key and corresponding value
System.out.println();
}
tx.commit();
em.close();
emf.close();
}
When I run it I get this message from eclipselink
Error Code: 1054
Call: SELECT t0.shares_owned, t0.company_id FROM portfolios_tb t0 WHERE (t0.User_USER_ID = ?)
bind => [1 parameter bound]
Query: DataReadQuery(name="stocksOwned" sql="SELECT t0.shares_owned, t0.company_id FROM portfolios_tb t0 WHERE (t0.User_USER_ID = ?)")
Why is eclipselink concatenating the entityname (User) and the enitityId (user_id) to give this t0.User_USER_ID = ? instead of this "....to.user_id"
That's because you didn't specify @JoinColumn for that mapping, so JPA's default mechanism is generating the join column name like <entity_name>_<id_column_name>.
Just add @JoinColumn(name = "user_id") attribute on @CollectionTable map mapping and it should work.
@ElementCollection
@CollectionTable(name = "portfolios_tb", joinColumns = @JoinColumn(name = "user_id"))
@MapKeyColumn(name = "company_id")
@Column(name = "shares_owned")
Map<String, BigInteger> stocksOwned = new HashMap<>();