Good day to all, please help to deal with the error: org.hibernate.exception.GenericJDBCException: Invalid column name

The request itself looks like this:

Session session = HibernateUtil.getSessionFactory().openSession(); session.beginTransaction(); String sql = "select nkl,psum,lsum,ltl,tzav, lth,dv, dn,nv,nvp from"+ "(select nkl,psum,lsum,ltl, tzav, lth,dv, dn ,nv,nvp from"+ "(select nkl, tzav, psum,lsum,ltl, lth, dv, dn,nv,nvp from"+ "(select nkl, tzav, psum,lsum,ltl, lth, ROUND(Diam,2) dv, ROUND(Ddiam,2) dn,nv,nvp from zxp.valpr"+ " where (nkl,tzav) in (select nkl,MAX(tzav) from zxp.valpr where tviv is null and pvn=0 and rop=0 group by nkl ) and pvn=0 and rop=0)))order by 1"; SQLQuery query = session.createSQLQuery(sql); //query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); query.addEntity(workval.class); List res = query.list(); request.getRequestDispatcher("4kl/test.jsp").forward(request, response); session.getTransaction().commit();

workval.java:

 package four_mill; import java.io.Serializable; import java.util.Date; public class workval implements Serializable { private static final long serialVersionUID = 1L; private Integer nkl; private Integer ukey; private Integer psum; private Integer lsum; private Integer ltl; private Date tzav; private Date tviv; private Integer lth; private Integer dv; private Integer dn; private Integer nv; private Integer nvp; private Integer pvn; private Integer rop; public workval(Integer nkl, Integer ukey, Integer psum, Integer lsum, Integer ltl, Integer lth, Integer dv, Integer dn, Integer nv, Integer nvp,Integer pvn,Integer rop, Date tzav, Date tviv) { this.nkl = nkl; this.ukey = ukey; this.psum = psum; this.lsum = lsum; this.ltl = ltl; this.lth = lth; this.dv = dv; this.dn = dn; this.nv = nv; this.nvp = nvp; this.pvn = pvn; this.rop = rop; this.tzav = tzav; this.tviv = tviv; } public workval(){ } public Integer getNkl() { return nkl; } public void setNkl(Integer nkl) { this.nkl = nkl; } public Integer getUkey() { return ukey; } public void setUkey(Integer ukey) { this.ukey = ukey; } public Integer getPsum() { return psum; } public void setPsum(Integer psum) { this.psum = psum; } public Integer getLsum() { return lsum; } public void setLsum(Integer lsum) { this.lsum = lsum; } public Integer getLtl() { return ltl; } public void setLtl(Integer ltl) { this.ltl = ltl; } public Date getTzav() { return tzav; } public void setTzav(Date tzav) { this.tzav = tzav; } public Date getTviv() { return tviv; } public void setTviv(Date tviv) { this.tviv = tviv; } public Integer getLth() { return lth; } public void setLth(Integer lth) { this.lth = lth; } public Integer getDv() { return dv; } public void setDv(Integer dv) { this.dv = dv; } public Integer getDn() { return dn; } public void setDn(Integer dn) { this.dn = dn; } public Integer getNv() { return nv; } public void setNv(Integer nv) { this.nv = nv; } public Integer getNvp() { return nvp; } public void setNvp(Integer nvp) { this.nvp = nvp; } public Integer getPvn() { return pvn; } public void setPvn(Integer pvn) { this.pvn = pvn; } public Integer getRop() { return rop; } public void setRop(Integer rop) { this.rop = rop; } }

workv.hbm.xml

  <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping> <class name="four_mill.workval" table="valpr" catalog="ZXP"> <id name="ukey" type="java.lang.Integer"> <column name="UKEY" /> <generator class="increment" /> </id> <property name="nkl" type="java.lang.Integer"> <column name="NKL" not-null="false" unique="true" /> </property> <property name="psum" type="java.lang.Integer"> <column name="PSUM" not-null="false" unique="true" /> </property> <property name="lsum" type="java.lang.Integer"> <column name="LSUM" not-null="false" unique="true" /> </property> <property name="ltl" type="java.lang.Integer"> <column name="LTL" not-null="false" unique="true" /> </property> <property name="lth" type="java.lang.Integer"> <column name="LTH" not-null="false" unique="true" /> </property> <property name="dv" type="java.lang.Integer"> <column name="DV" not-null="false" unique="true" /> </property> <property name="dn" type="java.lang.Integer"> <column name="DN" not-null="false" unique="true" /> </property> <property name="nv" type="java.lang.Integer"> <column name="NV" not-null="false" unique="true" /> </property> <property name="nvp" type="java.lang.Integer"> <column name="NVP" not-null="false" unique="true" /> </property> <property name="pvn" type="java.lang.Integer"> <column name="PVN" not-null="false" unique="true" /> </property> <property name="rop" type="java.lang.Integer"> <column name="ROP" not-null="false" unique="true" /> </property> <property name="tzav" type="date"> <column name="TZAV" not-null="false" unique="true" /> </property> <property name="tviv" type="date"> <column name="TVIV" not-null="false" unique="true" /> </property> </class> </hibernate-mapping>

The same query in DBeaver: enter image description here

What could be the problem ? Thank you all very much for your help and advice.

If you make a request to select all data in the database, the error is the same

 String sql = "select * from zxp.valpr";

Achieved an output at Select * from zxp.valpr It was a mistake, and it’s not even a mistake:

 ROUND(Diam,2) dv, ROUND(Ddiam,2) dn

In the database there are Diab and Ddiam but there is no dv and dn. Replacing the names and config hbm Error when executing my main query remained the same. Is Hibernate so harmful to use? can refuse this framework? Advise how to be, requests from the database for my project are the main task, and there are such unpleasant nuances, I don’t know how to be?

I request query, the problem is not solved:

 String sqll = "select nkl, tzav, psum,lsum,ltl, lth, diam, ddiam,nv,nvp from zxp.valpr where (nkl,tzav) in (select nkl,MAX(tzav) from zxp.valpr where tviv is null and pvn=0 and rop=0 group by nkl ) and pvn=0 and rop=0 order by 1";
  • Well, if you make a request with a simple string, try to conduct it directly in the oracle. Swears or not? - Riĥard Brugekĥaim
  • I wanted to lay out a screen that directly in orakle request works, even works through jdbc driver directly: - Ethernets
  • Hibernate hints to you that everyone who assigns such unreadable names to variables should suffer. :) - enzo
  • I'm sorry, but I did not form the database, but I renamed the variables in java because of hopelessness. - Ethernets
  • Well, if the request is correct, then an error in workv.hbm.xml. Looks like you are sealed in the name of a column. And by the way it is strange, he must probably indicate what kind of name is incorrect. - Riĥard Brugekĥaim

1 answer 1

I want to share my solution to this problem, in order for our query to work, you need to decide how we will access the database, which is important in Hibernate, when requesting this type:

 Session session = HibernateUtil.getSessionFactory().openSession(); session.beginTransaction(); String sqll = "select nkl, tzav, psum,lsum,ltl, lth, diam, ddiam,nv,nvp from zxp.valpr where (nkl,tzav) in (select nkl,MAX(tzav) from zxp.valpr where tviv is null and pvn=0 and rop=0 group by nkl ) and pvn=0 and rop=0 order by 1"; SQLQuery query = session.createSQLQuery(sql); //query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); query.addEntity(workval.class); List res = query.list(); request.getRequestDispatcher("4kl/test.jsp").forward(request, response); session.getTransaction().commit();

I made the following error, namely, in order to get a list of scalars (values) from one or several tables, we give the following syntax:

 Session session = HibernateUtil.getSessionFactory().openSession(); session.beginTransaction(); String sqll = "select nkl, tzav, psum,lsum,ltl, lth, diam, ddiam,nv,nvp from zxp.valpr where (nkl,tzav) in (select nkl,MAX(tzav) from zxp.valpr where tviv is null and pvn=0 and rop=0 group by nkl ) and pvn=0 and rop=0 order by 1"; SQLQuery query = session.createSQLQuery(sql); query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); //query.addEntity(workval.class); List res = query.list(); request.getRequestDispatcher("4kl/test.jsp").forward(request, response); session.getTransaction().commit();

For clarity, I left a commented line. And now you can check, but indeed our query has worked Test: [{LTH = 8.5021533203125, DDIAM = 492, NKL = 1, PSUM = 247.512298583984, TZAV = 2016-03-21 10: 59: 55.0, LTL = 0, DIAM = 492, NV = 86, NVP = 92, LSUM = 8.5021533203125} If you need to perform a query using query.addEntity (workval.class); Then you need to execute the query as follows:

 String sqll = "select * from zxp.valpr where (nkl,tzav) in (select nkl,MAX(tzav) from zxp.valpr where tviv is null and pvn=0 and rop=0 group by nkl ) and pvn=0 and rop=0 order by 1";

Thank you all who helped me deal with the problem.