MySQL查询语法错误，对应于您的MySQL服务器版本的正确语法，同时按名称排序表

Question

下面是我的代码..我应用了 Spring 框架和 hibernate JPA，除了一个按名称查询列的函数之外，我的所有代码都可以工作。一些类似的东西通过 id 订购效果很好..我不知道哪里错了...

这是我的代码

@Component
public class MakananDao extends BaseDaoHibernate<Makanan>{ 
    @Autowired private SessionFactory sessionFactory;
    @Override
    public Makanan save(Makanan makan){
        sessionFactory.getCurrentSession().saveOrUpdate(makan);
        return makan;
    }
    @Override
    public Makanan delete(Makanan makan){
        sessionFactory.getCurrentSession().delete(makan);
        return makan;
    }
    @Override
    public Long count(){
        return (Long) sessionFactory.getCurrentSession()
        .createQuery("select count(*) from Makanan p")
        .uniqueResult();
    }
    @Override
    public Makanan getById(Long id){
        return (Makanan) sessionFactory.getCurrentSession()
        .createQuery("from Makanan p where p.id=:id")
        .setParameter("id", id)
        .uniqueResult();
    }
    @Override
    public Makanan getByName(String name){
        return (Makanan) sessionFactory.getCurrentSession()
        .createSQLQuery("from Makanan p where p.name=:name")
        .setParameter("name", name)
        .uniqueResult();
    }
    @Override
    public List<Makanan> getAll(){
        return sessionFactory.getCurrentSession()
        .createQuery("from Makanan p")
        .list();
    }
    @Override
    public List<Makanan> getAll(int start, int num){
        return sessionFactory.getCurrentSession()
        .createQuery("from Makanan p")
        .setFirstResult(start)
        .setFetchSize(num)
        .list();
    }
}

这里是我的jdbc

public class MakananDaoJdbc {

    private Connection connection;
    private PreparedStatement insertStatement;
    private PreparedStatement updateStatement;
    private PreparedStatement deleteStatement;
    private PreparedStatement getByIdStatement;
    private PreparedStatement getAllStatement;
    private PreparedStatement getByNameStatement;
    private final String insertQuery = "insert into T_MAKANAN(name,description ,price) " +
    " values(?,?)";
    private final String updateQuery = "update T_MAKANAN set name=?, " + "description=? whare id=?"+
    " price=? where id=?";
    private final String deleteQuery = "delete from T_MAKANAN where id=?";
    private final String getByIdQuery = "select * from T_MAKANAN where id =?";
    private final String getByNameQuery = "select * from T_MAKANAN where name=?";
    private final String getAllQuery = "select * from T_MAKANAN";
    public void setConnection(Connection connection) throws SQLException{
    this.connection = connection;
    insertStatement = this.connection.prepareStatement(insertQuery,
    Statement.RETURN_GENERATED_KEYS);
    updateStatement = this.connection.prepareStatement(updateQuery);
    deleteStatement = this.connection.prepareStatement(deleteQuery);
    getByNameStatement = this.connection.prepareStatement(getByNameQuery);
    getByIdStatement = this.connection.prepareStatement(getByIdQuery);
    getAllStatement = this.connection.prepareStatement(getAllQuery);
    }
    public Makanan save(Makanan makan) throws SQLException{
        if (makan.getId() == 0) {
            insertStatement.setString(1, makan.getName());
            insertStatement.setString(2, makan.getDescription());
            insertStatement.setBigDecimal(3, makan.getHarga());
            long id = insertStatement.executeUpdate();
            makan.setId(id);
        } else {
            updateStatement.setString(1, makan.getName());
            updateStatement.setString(2, makan.getDescription());
            updateStatement.setBigDecimal(3, makan.getHarga());
            updateStatement.setLong(4, makan.getId());
            updateStatement.executeUpdate();
        }
        return makan;
    }
    public Makanan delete(Makanan person) throws SQLException{
        deleteStatement.setLong(1, person.getId());
        deleteStatement.executeUpdate();
        return person;
    }
    public Makanan getById(Long id) throws SQLException{
        getByIdStatement.setLong(1, id);
        ResultSet rs = getByIdStatement.executeQuery();
        //proses mapping dari relational ke object
        if (rs.next()) {
            Makanan makan = new Makanan();
            makan.setId(rs.getLong("id"));
            makan.setName(rs.getString("name"));
            makan.setHarga(rs.getBigDecimal("price"));
            makan.setName(rs.getString("description"));
            return makan;
        }
        return null;
    }
    public Makanan getByName(String name) throws SQLException{
        getByNameStatement.setString(1, name);
        ResultSet rs = getByNameStatement.executeQuery();
        if(rs.next()){
            Makanan makan = new Makanan();
            makan.setId(rs.getLong("id"));
            makan.setName(rs.getString("name"));
            makan.setHarga(rs.getBigDecimal("price"));
            makan.setName(rs.getString("description"));
            return makan;
        }
        return null;
    }
    public List<Makanan> getAll() throws SQLException{
        List<Makanan> makans = 
        new ArrayList<Makanan>();
        ResultSet rs = getAllStatement.executeQuery();
        while(rs.next()){
            Makanan makan = new Makanan();
            makan.setId(rs.getLong("id"));
            makan.setName(rs.getString("name"));
            makan.setHarga(rs.getBigDecimal("price"));
            makan.setDescription(rs.getString("description"));
            makans.add(makan);
        }
        return makans;
    }
}

这是我的 Dao hibernate 基础

public class BaseDaoHibernate<T> {
    @SuppressWarnings("unchecked")
    protected Class domainClass;
    @Autowired
    protected SessionFactory sessionFactory;
    @SuppressWarnings("unchecked")
    public BaseDaoHibernate() {
        this.domainClass = (Class) ((ParameterizedType) 
        getClass().getGenericSuperclass())
        .getActualTypeArguments()[0];
    }
    public T save(T domain) {
    sessionFactory.getCurrentSession().saveOrUpdate(domain);
        return domain;
    }
    @SuppressWarnings("unchecked")
    public T getByName(String name) {
        return (T) sessionFactory.getCurrentSession().get(domainClass, name);
    }

    @SuppressWarnings("unchecked")
    public T getById(Long id) {
        return (T) sessionFactory.getCurrentSession().get(domainClass, id);
    }
    public T delete(T domain) {
        sessionFactory.getCurrentSession().delete(domain);
        return domain;
    }
    @SuppressWarnings("unchecked")
        public Long count() {
        List list = sessionFactory.getCurrentSession().createQuery(
        "select count(*) from " + domainClass.getName() + " x").list();
        Long count = (Long) list.get(0);
        return count;
    }
    @SuppressWarnings("unchecked")
    public List<T> getAll() {
        return sessionFactory.getCurrentSession().createQuery("from " + 
        domainClass.getName())
        .list();
    }
    @SuppressWarnings("unchecked")
    public List<T> getAll(int start, int num) {
        return sessionFactory.getCurrentSession().createQuery("from " + 
        domainClass.getName())
        .setFirstResult(start).setMaxResults(num)
        .list();
    }
}

错误

Caused by: 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 'from Makanan p where p.name='sego kucing'' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

在此代码上

@Override
    public Makanan getByName(String name){
        return (Makanan) sessionFactory.getCurrentSession()
        .createSQLQuery("from Makanan p where p.name=:name")
        .setParameter("name", name)
        .uniqueResult();
    }

有一个类似的功能，可以按 id 排序，正常工作没有问题..

非常感谢您的关注..:)

Answer 1

.createQuery 和 .createSQLQuery 之间有什么区别？

使用 createSQLQuery 时，查询应以 SELECT * FROM... 开头？

你的getbyname使用createSQLQuery而getbyid使用createQuery？