CODEWITHSUNDEEP
javamysqlhibernatejakarta-ee
Olzhas
Olzhas

Reputation: 235

MYSQL SQL syntax error while i use hibernate

I am using hibernate as a persistence layer, here is the sample of my code, but here my hql queries:

Query q = s.createQuery("from Login where name=:user and passw =:passw");
  q.setParameter("user",username);
  q.setParameter("passw", passw);
Query q = s.createSQLQuery("SELECT * from Good where Good.supplier =:supl AND Good.name =:gname AND Good.dates >=:sdate and Good.dates <=:fdate").addEntity(Good.class);
   q.setParameter("supl",sup);
   q.setParameter("gname", gname);
   q.setParameter("sdate", sdate);
   q.setParameter("fdate",fdate);

sdate and fdate parametrs are string, are they ok in this case? it throws this exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.login login0_ where login0_.name='Tom'' at line 1

Login.hbm.xml

<hibernate-mapping>
<class name="kz.bimash.FoodSec.model.Login" table="login" catalog="foodsec">
    <id name="id" type="java.lang.Integer">
        <column name="Id" />
        <generator class="increment" />
    </id>
    <property name="name" type="string">
        <column name="name" length="50" not-null="true" unique="true" />
    </property>
    <property name="passw" type="string">
        <column name="passw" length="50" not-null="true" />
    </property>
    <property name="type" type="string">
        <column name="type" length="45" not-null="true" />
    </property>
    <property name="userId" type="int">
        <column name="userId" not-null="true" />
    </property>
</class>

Login pojo class

public class Login  implements java.io.Serializable {


 private Integer id;
 private String name;
 private String passw;
 private String type;
 private int userId;

public Login() {
}

public Login(String username, String password, String type, int userId) {
   this.name = username;
   this.passw = password;
   this.type = type;
   this.userId = userId;
}

public Integer getId() {
    return this.id;
}

public void setId(Integer id) {
    this.id = id;
}
public String getName() {
    return this.name;
}

public void setName(String username) {
    this.name = username;
}
public String getPassw() {
    return this.passw;
}

public void setPassw(String password) {
    this.passw = password;
}
public String getType() {
    return this.type;
}

public void setType(String type) {
    this.type = type;
}
public int getUserId() {
    return this.userId;
}

public void setUserId(int userId) {
    this.userId = userId;
}

} Login DAO class

@Repository
public class LoginDAO {
@Autowired
private SessionFactory sf;
@SuppressWarnings("empty-statement")
public String[] Authorise(String username, String passw){
  Session s = sf.getCurrentSession();
  s.beginTransaction();
  Query q = s.createQuery("from Login where name=:user and passw =:passw");
  q.setParameter("user",username);
  q.setParameter("passw", passw);
  q.setMaxResults(1);
  String[] str = null;
  Login login = null;
  for(Iterator it = q.iterate(); it.hasNext();){
   login = (Login)it.next();

  }
  if(login != null){
      str = new String[]{login.getType(), String.valueOf(login.getUserId())};

  }
        s.getTransaction().commit();
    return str;
}
public boolean checkLogin(String username){
    Session s = sf.getCurrentSession();
    s.beginTransaction();
   // String s_sql ="SELECT * FROM Login WHERE NAME="+username;
    Query q = s.createQuery("from Login where name = :usern");
    q.setParameter("usern", username);
   // Query q=s.createSQLQuery(s_sql);
    List<Login> logins =null;
    logins= (List<Login>)q.list();
    s.getTransaction().commit();
    if(logins !=null)
        return true;
    else
        return false;
}

}

Upvotes: 0

Views: 1362

Answers (1)

RAS
RAS

Reputation: 8158

There's a problem with the following segment of the code:

Query q = s.createSQLQuery("SELECT * from Good where Good.supplier =:supl AND Good.name =:gname AND Good.dates >=:sdate and Good.dates <=:fdate").addEntity(Good.class);

You are writing an SQL & not an HQL. That is why you have used session.createSQLQuery(...) and not session.createQuery(...). createSQLQuery(...) always returns a reference of org.hibernate.SQLQuery, while you have assigned it to a variable of Query.

I'm surprised why didn't you get a Compile-time error.

Try to assign it to a SQLQuery variable & check whether it's working or not.

Upvotes: 1

Related Questions