Hibernate QueryException Named parameter not bound error

Viewed 12822

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);
2 Answers

One of the reason this can happen is when insertData List you are passing is empty.

If I use below code ( please note that I have removed few columns to simplify it on my test environment with H2 database) - I get the error you described if empty list is passed and that's because there is indeed nothing to bind for the name parameter as the loop is not executed.

try {
            em.getTransaction().begin();
            System.out.println("Beginning transaction for insertPromoData");
            
            Query query = em.createNativeQuery(
                    "INSERT INTO U_USER_PROMO (ITEM, LOC, WK_START, NUMBER_OF_WEEKS, TYPE, FCSTID, QTY) VALUES (:item, :location, :wkStart, :numberOfWeeks, :type, :fcstId, :quantity)");
            for (InsertPromoData promoData : insertData) {
                query.setParameter("item", promoData.getId().getItem());
                query.setParameter("location", promoData.getId().getLoc());
                query.setParameter("wkStart", promoData.getId().getWeekStart());
                query.setParameter("numberOfWeeks", promoData.getNumberOfWeeks());
                query.setParameter("type", promoData.getId().getType());
                query.setParameter("fcstId", promoData.getId().getForecastId());
                query.setParameter("quantity", Double.valueOf(promoData.getQty()));
            }
            query.executeUpdate();
            System.out.println("Data for promo persisted");
            em.getTransaction().commit();
        } catch (Exception e) {

            e.printStackTrace();
        } finally {
            em.clear();
            em.close();
        }

The error I get is

org.hibernate.QueryException: Named parameter not bound : item
    at org.hibernate.query.internal.QueryParameterBindingsImpl.verifyParametersBound(QueryParameterBindingsImpl.java:210)
    at org.hibernate.query.internal.AbstractProducedQuery.beforeQuery(AbstractProducedQuery.java:1425)
    at org.hibernate.query.internal.NativeQueryImpl.beforeQuery(NativeQueryImpl.java:249)
    at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1610)
    at com.danielme.blog.nativesql.dao.UserDao.insertPromoData(UserDao.java:99)

However - if I pass non-empty list - this works as expected

SQLCustomQuery:72 - Starting processing of sql query [INSERT INTO U_USER_PROMO (ITEM, LOC, WK_START, NUMBER_OF_WEEKS, TYPE, FCSTID, QTY) VALUES (:item, :location, :wkStart, :numberOfWeeks, :type, :fcstId, :quantity)]
AbstractFlushingEventListener:74 - Flushing session

I also encountered same problem. I noticed that I was defining the parameter in the query but I was not setting its value in:

query.setParameter(parameterToBeSet,parameterValue)

For example,

String hql = "FROM COLLEGE FETCH ALL PROPERTIES WHERE studentId = :studentId AND departmentId = :departmentId AND studentName = :studentName";

DBConnectionManager.getInstance().invokeCallableOnSessionMasterDB(session -> {
    Query<CollegeEntity> query = session.createQuery(hql);
    query.setParameter("studentId", studentId);
    query.setParameter("departmentId", departmentId);
    values.addAll(query.list());
});

As you can see the student name value was not set. So, after adding:

query.setParameter("studentName", studentName);

It got solved.

Related