Error: org.hibernate.hql.internal.ast.QuerySyntaxException, Help me?

Question

I have a problem with my project. When i browser http://localhost:8080/user/form to fill information of user after direct http://localhost:8080/user/list to display list user then occur following error:

Hibernate: insert into userdat (password, birthday, age, gender, username) value s (?, ?, ?, ?, ?) Mar 25, 2016 5:05:04 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper$Standar dWarningHandler logWarning WARN: SQL Warning Code: 10000, SQLState: 01J01 Mar 25, 2016 5:05:04 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper$Standar dWarningHandler logWarning WARN: Database 'D:\PROJECTSPRING\userdb' not created, connection made to existin g database instead. Mar 25, 2016 5:05:04 AM org.hibernate.hql.internal.QueryTranslatorFactoryInitiat or initiateService INFO: HHH000397: Using ASTQueryTranslatorFactory Hibernate: select user0_.username as username1_0_, user0_.password as password2_ 0_, user0_.birthday as birthday3_0_, user0_.age as age4_0_, user0_.gender as gen der5_0_ from userdat user0_ Mar 25, 2016 5:05:04 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExce ptions WARN: SQL Error: 20000, SQLState: 22005 Mar 25, 2016 5:05:04 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExce ptions ERROR: An attempt was made to get a data value of type 'byte[]' from a data valu e of type 'VARCHAR'. [WARNING] org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.DataException: could not execute que ry at org.springframework.web.servlet.FrameworkServlet.processRequest(Frame workServlet.java:973) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServl et.java:852) at javax.servlet.http.HttpServlet.service(HttpServlet.java:687) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkSer

Here file CustomContextLoaderListener.java

public class CustomContextLoaderListener extends ContextLoaderListener{

    @Override
    public void contextDestroyed(ServletContextEvent event) {
        // TODO Auto-generated method stub
        System.out.println("hibernate shutdown database");
        try {
            DriverManager.getConnection("jdbc:derby:D:/PROJECTSPRING/userdb;shutdown=true");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        System.out.println("\n Spring-MVC application destroyed \n");
        super.contextDestroyed(event);

    }

    @Override
    public void contextInitialized(ServletContextEvent event) {
        // TODO Auto-generated method stub
        System.out.println("\n Spring-MVC application inited \n");
        try {
            createTable();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        super.contextInitialized(event);
    }

    public void createTable() throws SQLException{
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
            Connection connection = DriverManager.getConnection("jdbc:derby:D:/PROJECTSPRING/userdb;create=true");
            createTableNotExist(connection,"userdat", "create table userdat"
                    + "(username varchar(1000) primary key,"
                    + "password varchar(1000),birthday date,"
                    + "age integer,gender varchar(100))");
//          createTableNotExist(connection,"subject","create table subject"
//                  + "(id bigint primary key generated always as identity(start with 1,increment by 1),"
//                  + "title varchar(1000),student integer,score integer)" );
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
    public void createTableNotExist(Connection connection,
            String tableName,String createTableSQL) throws SQLException{
        DatabaseMetaData dbmd = connection.getMetaData();
        ResultSet rs = dbmd.getTables(null, null,tableName.toUpperCase(), null);
        if (rs.next()){
            System.out.println("Table" + rs.getString("TABLE_NAME") + "already exists");
            return;

        }
        System.out.println("anh yeu em");
        Statement statement = connection.createStatement();
        statement.execute(createTableSQL);
        System.out.println("\n\n executed" + createTableSQL + "\n\n");
        statement.close();
    }


}

Here file UserRestServiceController.java

@Controller
public class UserRestServiceController {
    @Autowired
    private UserDao userDao;
    @Autowired
    private View jsonView;
    @RequestMapping(value="/user/form",method = RequestMethod.GET)
    public ModelAndView user() {
//      System.out.println("anh yeu em ");
        ModelAndView model = new ModelAndView("UserForm", "User", new User());

        model.addObject("allgender", Gender.values());

        return model;
    }

    @RequestMapping(value="/user/save", method = RequestMethod.POST)
    public ModelAndView saveUser(@ModelAttribute("User") User user,BindingResult result){

        ModelAndView model = new ModelAndView();
        System.out.println("anh yeu em ");
        if (result.hasErrors()){
            model = new ModelAndView("UserForm","User",user);
            model.addObject("errors",result);
            return model;
        }
        userDao.save(user);
        model = new ModelAndView("redirect:/user/list");
        return model;

    }

    @RequestMapping(value="/user/list",method = RequestMethod.GET)
    public ModelAndView listUser(){
        ModelAndView model = new ModelAndView();
        List<User> listUser = userDao.listUsers();
        model = new ModelAndView("UserList","Users",listUser);
        return model;
    }
    @RequestMapping(value="/user/json/{username}")
    public ModelAndView loadUser(@PathVariable("username")String name){
        return new ModelAndView(jsonView,"data",userDao.loadUser(name));
    }
    @InitBinder
    public void initBinder(WebDataBinder binder) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

        binder.registerCustomEditor(Date.class, new CustomDateEditor(dateFormat, true));

    }


}

Here file UserDaoImpl.java

@Repository
public class UserDaoImpl implements UserDao{
    @Autowired
    public LocalSessionFactoryBean sessionFactory;


    @Override
    public void save(User user) {
        // TODO Auto-generated method stub
        Session session = sessionFactory.getObject().openSession();
        session.save(user);
        session.flush();
        session.close();

    }

    @Override
    public void update(User user) {
        // TODO Auto-generated method stub

    }

    @Override
    public List<User> listUsers() {
        // TODO Auto-generated method stub
        Session session = sessionFactory.getObject().openSession();
        Query query = session.createQuery("from User");
        return (List<User>)query.list();
    }

    @Override
    public void delete(User user) {
        // TODO Auto-generated method stub
        Session session = sessionFactory.getObject().openSession();
        session.delete(user);

    }

    @Override
    public User loadUser(String username) {
        // TODO Auto-generated method stub
        Session session = sessionFactory.getObject().openSession();
        Query query = session.createQuery("from User "
                + "where username = :user_name");
        query.setParameter("user_name",username);
        return (User) query.uniqueResult();
    }


}

Here file User.java

@Entity
//@Table(name = "user",uniqueConstraints={@UniqueConstraint(columnNames="username")})
public class User {
//  @Column(name = "gender", nullable = false)
//  @Enumerated(EnumType.STRING)
    public Gender getGender() {
        return gender;
    }
    public void setGender(Gender gender) {
        this.gender = gender;
    }
//  @Id
//  @GeneratedValue(strategy = GenerationType.IDENTITY)
//  @Column(name = "username", unique = true, nullable = false)
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
//  @Column(name = "password", nullable = false)
    public String getPassWord() {
        return passWord;
    }
    public void setPassWord(String passWord) {
        this.passWord = passWord;
    }
//  @Column(name = "birthday", nullable = false)
    public Date getBirthDay() {
        return birthDay;
    }

    public void setBirthDay(Date birthDay) {
        this.birthDay = birthDay;
    }
//  @Column(name="age", nullable = false)
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    private String userName;
    private String passWord;
    private Date birthDay;
    private Integer age;
    private Gender gender;


}

Here file Gender.java

public enum Gender {
    MALE("Male"),
    FEMALE("Female"),
    OTHER("Other");
    private String name;

    private Gender(String name) {
        this.name = name;
    }

    public String getGender(){
        return this.name;
    }

Here file User.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="edu.java.spring.service.user.model.User" table="userdat">
        <id name="userName" column="username"/>
        <property name="passWord" column="password"/>
        <property name="birthDay"  column="birthday"/>
        <property name="age"  column="age"/>
        <property name="gender" column="gender" />

    </class>
</hibernate-mapping>

Answer 1

When you write an select statement with Hibernate then you write it in HQL but not in SQL. One of the main difference between them is, that in HQL you write class names and field names, in sql you would use table names and column names.

It is likely that the class name of your entity is User and the table name is userdat. Therefore the correct HQL Statement would be: from User (uppercase U!) instead of from userdat

@Override
public List<User> listUsers() {
    // TODO Auto-generated method stub
    Session session = sessionFactory.getObject().openSession();
    Query query = session.createQuery("from User");
    return (List<User>)query.list();
}

Btw it is the same for public User loadUser(String username)

---

According to your modified question (btw. next time rise a new question instead of changing the old one if this modification change the nature of the question)

First let me state, that I wondering that nowadays somebody still use hbm.xml files instead of annotation, but maybe you have some good reason. ...

When I compare the hbm.xml with the (commented out) annotations, there is only one thing that maybe cause some issue like your once: it is the enum. In the annotations, the enum is mapped via its name (string/varchar) but in hbm.xml it is mapped via its ordinal (integer). So give it a try. Check that the column used for the enum is an integer column, if not make it one. If you can/want not change the column type you need to change the mapping. According to some google research, mapping an enum via its name by xml is strange: (the important point it the 12, it is the internal representation for varchar!)

<property name="gender" length="30">
   <type name="org.hibernate.type.EnumType">
      <param name="enumClass">full.qualifiey.name.for.Gender</param>
      <param name="type">12</param>
   </type>
</property>

Answer 2

You need to map your table userdat to your User entity in order to a HQL query. Once you have mapped it, you can then do

Query query = session.createQuery("from User");

Have a look at this example, and Hibernate documentation section about mappings.