I keep getting the error org.hibernate.QueryException: Named parameter not bound : item when I start my transaction in JPA using EntityManager createNativeQuery. I have my code below utilizing the entity manager, along with my embeddedID class (for the composite key) and my persistence entity bean. Is there a problem in my query syntax? I am not sure as I've tried multiple ways of formatting the sql (coming from a properties file where there resides multiple sqls used throughout the project, and attempting to persist data to an oracle db). I am not sure why I keep falling on this error. I want to persist this data to my oracle database but this error keeps preventing that.
Query from query.properties file:
insertPromoData =INSERT INTO TEST.U_USER_PROMO (ITEM, LOC, WK_START, NUMBER_OF_WEEKS, TYPE, FCSTID, QTY, U_TIMESTAMP) VALUES (:item, :location, :wkStart, :numberOfWeeks, :type, :fcstId, :quantity, SYSDATE)
Embeddedable Class for establishing composite primary key on the target table:
@Embeddable
public class PromoID implements Serializable {
@Column(name = "ITEM")
private String item;
@Column(name = "LOC")
private String loc;
@Column(name = "WK_START")
private Date weekStart;
@Column(name = "TYPE")
private int type;
@Column(name = "FCSTID")
private String forecastId;
@Column(name = "U_TIMESTAMP")
private Timestamp insertTS;
public PromoID() {
}
public PromoID (String item, String loc, Date weekStart, int type, String forecastId, Timestamp insertTS) {
this.item = item;
this.loc = loc;
this.weekStart = weekStart;
this.type = type;
this.forecastId = forecastId;
this.insertTS = insertTS;
}
public String getItem() {
return item;
}
public void setItem(String item) {
this.item = item;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
public Date getWeekStart() {
return weekStart;
}
public void setWeekStart(Date weekStart) {
this.weekStart = weekStart;
}
public int getType() {
return type;
}
public void setType(int type) {
this.type = type;
}
public String getForecastId() {
return forecastId;
}
public void setForecastId(String forecastId) {
this.forecastId = forecastId;
}
public Timestamp getInsertTS() {
return insertTS;
}
public void setInsertTS(Timestamp insertTS) {
this.insertTS = insertTS;
}
//removed hashcode and equals methods for simplicity
Persistence Entity Bean:
@Entity
@Table(name = "U_USER_PROMO")
public class InsertPromoData {
@EmbeddedId
private PromoID id;
/*@Column(name="BATCH_ID")
String batchID;*/
@Column(name="ITEM")
String item;
@Column(name="LOC")
String loc;
@Column(name="WK_START")
String weekStart;
@Column(name="TYPE")
String type;
@Column(name="FCSTID")
String forecastId;
@Column(name="U_TIMESTAMP")
String insertTS;
@Column(name="NUMBER_OF_WEEKS")
String numberOfWeeks;
@Column(name="QTY")
String qty;
@Id
@AttributeOverrides(
{
@AttributeOverride(name = "item",column = @Column(name="ITEM")),
@AttributeOverride(name = "loc", column = @Column(name="LOC")),
@AttributeOverride(name = "weekStart", column = @Column(name="WK_START")),
@AttributeOverride(name = "type", column = @Column(name="TYPE")),
@AttributeOverride(name = "forecastId", column = @Column(name="FCSTID"))
}
)
public PromoID getId() {
return id;
}
public void setId(PromoID id) {
this.id = id;
}
public String getItem() {
return item;
}
public void setItem(String item) {
this.item = item;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
public String getWeekStart() {
return weekStart;
}
public void setWeekStart(String weekStart) {
this.weekStart = weekStart;
}
public String getNumberOfWeeks() {
return numberOfWeeks;
}
public void setNumberOfWeeks(String numberOfWeeks) {
this.numberOfWeeks = numberOfWeeks;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getForecastId() {
return forecastId;
}
public void setForecastId(String forecastId) {
this.forecastId = forecastId;
}
public String getQty() {
return qty;
}
public void setQty(String qty) {
this.qty = qty;
}
public String getInsertTS() {
return insertTS;
}
public void setInsertTS(String insertTS) {
this.insertTS = insertTS;
}
}
My dao OracleImpl.java using EntityManager for persisting:
public void insertPromoData(List<InsertPromoData> insertData) {
logger.debug("Execution of method insertPromoData in Dao started");
EntityManager em = emf.createEntityManager();
try {
em.getTransaction().begin();
System.out.println("Beginning transaction for insertPromoData");
Query query = em.createNativeQuery(env.getProperty("insertPromoData"));
for (InsertPromoData promoData : insertData) {
query.setParameter("item", promoData.getItem());
query.setParameter("location", promoData.getLoc());
query.setParameter("wkStart", promoData.getWeekStart());
query.setParameter("numberOfWeeks", promoData.getNumberOfWeeks());
query.setParameter("type", promoData.getType());
query.setParameter("fcstId", promoData.getForecastId());
query.setParameter("quantity", Double.valueOf(promoData.getQty()));
}
query.executeUpdate();
System.out.println("Data for promo persisted");
em.getTransaction().commit();
}
catch(Exception e) {
logger.error("Exception in beginning transaction");
e.printStackTrace();
}
finally {
em.clear();
em.close();
}
logger.debug("Execution of method insertPromoData in Dao ended");
}
PromoValidator.java class:
List <InsertPromoData> insertPromos = new ArrayList<>();
promo.forEach(record -> {
if (record.getErrorList().size() == 0) {
rowsSuccessful++;
record.setItem(record.getItem());
record.setLoc(record.getLoc());
record.setNumber_Of_Weeks(record.getNumber_Of_Weeks());
record.setForecast_ID(record.getForecast_ID());
record.setType(record.getType());
record.setUnits(record.getUnits());
record.setWeek_Start_Date(record.getWeek_Start_Date());
insertPromos = (List<InsertPromoData>) new InsertPromoData();
for (InsertPromoData insertPromoData : insertPromos) {
insertPromoData.setItem(record.getItem());
insertPromoData.setLoc(record.getLoc());
insertPromoData.setWeekStart(LocalDate.parse(record.getWeek_Start_Date()));
insertPromoData.setNumberOfWeeks(Integer.parseInt(record.getNumber_Of_Weeks()));
insertPromoData.setType(Integer.parseInt(record.getType()));
insertPromoData.setForecastId(record.getForecast_ID());
insertPromoData.setQty(Double.parseDouble(record.getUnits()));
}
} else {
if (rowsFailure == 0) {
Util.writeHeaderToFile(templateCd, errorFile);
}
rowsFailure++;
Util.writeErrorToFile(templateCd, errorFile, record, record.getErrorList());
}
});
errorFile.close();
successFile.close();
OracleImpl.insertPromoData(insertPromos);
