| Mapping to single inheritance strategy fails "Conversion failed when converting the nvarchar va [message #1801752] |
Fri, 25 January 2019 16:09 |
 Sarah Kay
Messages: 1
Registered: January 2019 |
Junior Member |
|
|
This is how my database looks like:
- Audit Table: Holds audit information regarding user.
- Audit_id is PK key and has a discriminator column.
- If discriminator value is 1, then it's Address details and if 2, it's Phone details.
- One user can have more than one audit records.
- User Table -> user_id is PK.
- It has a column called CURRENT_STREET column. Instead of storing string directly, it holds audit id. The audit id points to most recent change.
User id 100 has 2 audit rows for phone number. I couldn't get collection of results.
**Parent Class:** </br>
```
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@Entity
@DiscriminatorColumn(name = "type")
@Table(name = "audit")
@NamedQuery(name = "Audit.findAll", query = "SELECT m FROM Audit m")
public class Audit implements Serializable
{
@Id
@GeneratedValue(strategy = GenerationType.TABLE)
@Column(name = "audit_id")
private int auditId;
@Column(name = "user_id")
private int userId;
@OneToMany(mappedBy = "addressAudit1")
private List<User> User;
//Getters and Setters are defined.
}
```
**Child Class 1:**
```
@Entity
@DiscriminatorValue("1")
public class Address extends Audit
{
@Column(name = "street_addr1")
private String streetAddr1;
@Column(name = "street_addr2")
private String streetAddr2;
private String zip;
//Getters and Setters are defined.
}
```
**Child Class 2:**
```
@Entity
@DiscriminatorValue("2")
public class Phone extends Audit
{
@Column(name = "home_phone")
private String homePhone;
@Column(name = "home_phone_ext")
private String homePhoneExt;
@Column(name = "work_phone")
private String workPhone;
@Column(name = "work_phone_ext")
private String workPhoneExt;
//Getters and Setters are defined.
}
```
**User Class**
```
@Entity
@Table(name = "user")
@NamedQuery(name = "User.findAll", query = "SELECT m FROM User m")
public class User implements Serializable
{
@Id
@GeneratedValue(strategy = GenerationType.TABLE)
@Column(name = "user_id")
private int UserId;
*//Below code is causing issue*
@ManyToOne
@JoinColumn(name = "CURRENT_STREET")
private Audit addressAudit1;
//Getters and Setters are defined.
}
```
**UseCase**
1. I need to search an user based on ID. Here is how I search
```
public static User getUserForId(long userID, EntityManagerFactory emf) throws Exception
{
User user = null;
EntityManager em = null;
try
{
em = emf.createEntityManager();
TypedQuery<User> q = em.createQuery("select u from " + User.class.getName() + " u where u.userId = :userIndex", User.class);
q.setParameter("userIndex", userID);
List<User> userList = q.getResultList();
if (userList.size() > 0)
{
Iterator<User> userItr = userList.iterator();
if (userItr.hasNext())
{
user = userItr.next();
}
}
}
catch (Exception e)
{
logger.error(e.getMessage());
throw new Exception(e.getMessage());
}
finally
{
em.close();
}
return user;
}
```
Exception is thrown at q.getResultList().
```
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'Audit' to data type int.
Error Code: 245
Call: SELECT audit_id, type, JDOVERSION, user_id FROM audit WHERE ((audit_id = ?) AND (type = ?))
bind => [2 parameters bound]
Query: ReadObjectQuery(name="addressAudit1" referenceClass=Audit )
```
NOTE: The same works when I use @embedded and @embeddable annotations.I gave up on it because, I'm unable to fetch collection of audits.
|
|
|
Powered by
FUDForum. Page generated in 0.02371 seconds
] 
Search
Help
Register
Login
Home
