时间戳

首页 > 解决方案 > 检查和删除数据库中的 2 个值时出现休眠 JPA 错误

java - 检查和删除数据库中的 2 个值时出现休眠 JPA 错误

问题描述

检查特定表中是否存在值以及是否存在要删除的内容时出现问题。

我想检查santa_name 或 reciving_participant_name 是否等于 value = Oscar

并检查santa_last_name 或 reciving_participant_last_name是否等于 = Martinez

使用：Java 8 和 MySql 8.0

我在 db 中的表是：

+----+------------+-----------------+---------------------------+--------------------------------+
| id | santa_name | santa_last_name | reciving_participant_name | reciving_participant_last_name |
+----+------------+-----------------+---------------------------+--------------------------------+
|  2 | Oscar      | Martinez        | Andy                      | Bernard                        |
+----+------------+-----------------+---------------------------+--------------------------------+

我的课：

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="santa_connections")
public class SantaConnections {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id")
    private int id;

    @Column(name="santa_name")
    private String santaName;

    @Column(name="santa_last_name")
    private String santaLastName;

    @Column(name="reciving_participant_name")
    private String userName;

    @Column(name="reciving_participant_last_name")
    private String userLastName;

    public SantaConnections() {

    }

    public SantaConnections(String santaName, String santaLastName, String userName, String userLastName) {
        super();
        this.santaName = santaName;
        this.santaLastName = santaLastName;
        this.userName = userName;
        this.userLastName = userLastName;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getSantaName() {
        return santaName;
    }

    public void setSantaName(String santaName) {
        this.santaName = santaName;
    }

    public String getSantaLastName() {
        return santaLastName;
    }

    public void setSantaLastName(String santaLastName) {
        this.santaLastName = santaLastName;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserLastName() {
        return userLastName;
    }

    public void setUserLastName(String userLastName) {
        this.userLastName = userLastName;
    }

}

和 DAO 类：

public void deleteFromSantaConnections(String name, String lastName) {

    Transaction transaction = null;

    try (Session session = HibernateUtil.getSessionFactory().openSession()) {

        transaction = session.beginTransaction();

        try {

            SantaConnections toRemove = (SantaConnections) session.createNativeQuery(
                    "SELECT * From SantaConnections s WHERE s.santaName = '" + name + "' AND s.santaLastName = '" + lastName + "' OR s.userName = '" + name + "' AND s.userLastName '" + lastName + "' ").getSingleResult();

            session.remove(toRemove);

            transaction.commit();

        } catch (Exception e) {
            System.out.println("User " + name + " " + lastName + " is no in other DB");
            transaction.rollback();
            e.printStackTrace();
        }

    } catch (Exception e) {
        if (transaction != null) {
            transaction.rollback();
        }
        e.printStackTrace();
    }

}

删除时出错：

   Hibernate: SELECT * From SantaConnections s WHERE s.santaName = 'Oscar' AND s.santaLastName = 'Martinez' OR s.userName = 'Oscar' AND s.userLastName 'Martinez' 
    gru 19, 2019 2:22:10 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
    WARN: SQL Error: 1064, SQLState: 42000
    gru 19, 2019 2:22:10 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
    ERROR: 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 ''Martinez'' at line 1

标签： javamysqlsqlhibernatejpa

解决方案

s.userLastName您只是在此行之后忘记了等号：

SELECT * From SantaConnections s WHERE s.santaName = '" + name + "' AND s.santaLastName = '" + lastName + "' OR s.userName = '" + name + "' AND s.userLastName '" + lastName + "' ").getSingleResult();

它应该是：

SELECT * From SantaConnections s WHERE s.santaName = '" + name + "' AND s.santaLastName = '" + lastName + "' OR s.userName = '" + name + "' AND s.userLastName = '" + lastName + "' ").getSingleResult();

推荐阅读