Блог

Главная - Вопросы по программированию - HTTP Status 500 — Сбой обработки запроса; вложенным исключением является org.hibernate.exception.ConstraintViolationException: не удалось выполнить запрос на обновление

HTTP Status 500 — Сбой обработки запроса; вложенным исключением является org.hibernate.exception.ConstraintViolationException: не удалось выполнить запрос на обновление

#mysql #spring #jsp #spring-mvc #hibernate-annotations

#mysql #весна #jsp #spring-mvc #переход в спящий режим-аннотации

Вопрос:

Привет, в моем приложении аннотаций Spring mvc для гибернации у меня есть 3 таблицы: Сотрудник, команда, платформа.Я использую выпадающий список, чтобы выбрать название команды в Platform.jsp и Employee.jsp.Когда я пытаюсь удалить команду, не удаляя ссылку на соответствующие ссылки из таблицы employee и таблицы platform, я получаю следующую ошибку.

 HTTP Status 500 - Request processing failed; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute update query`

Трассировка стека

 type Exception report

message Request processing failed; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute update query

description The server encountered an internal error that prevented it from fulfilling this request.

exception 

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute update query
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:659)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:552)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)


root cause 

org.hibernate.exception.ConstraintViolationException: could not execute update query
    org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
    org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:84)
    org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396)
    org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
    org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)
    org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
    com.resource.ResourceDao.DeviceDaoImpl.deleteDevice(DeviceDaoImpl.java:39)
    com.resource.ResourceService.DeviceServiceImpl.deleteDevice(DeviceServiceImpl.java:43)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:601)
    org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
    org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:108)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    $Proxy21.deleteDevice(Unknown Source)
    com.resource.ResourceController.DeviceController.deleteDevice(DeviceController.java:61)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:601)
    org.springframework.web.bind.annotation.support.HandlerMethodInvoker.doInvokeMethod(HandlerMethodInvoker.java:710)
    org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:167)
    org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:414)
    org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:402)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:771)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:716)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:647)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:552)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)


root cause 

com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`employee`, CONSTRAINT `FK4AFD4ACE1BA063F9` FOREIGN KEY (`teamId`) REFERENCES `team` (`teamId`))
    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
    com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
    com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
    com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
    com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
    com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
    com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1541)
    com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1455)
    com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1440)
    org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75)
    org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396)
    org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
    org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)
    org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
    com.resource.ResourceDao.DeviceDaoImpl.deleteDevice(DeviceDaoImpl.java:39)
    com.resource.ResourceService.DeviceServiceImpl.deleteDevice(DeviceServiceImpl.java:43)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:601)
    org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
    org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:108)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    $Proxy21.deleteDevice(Unknown Source)
    com.resource.ResourceController.DeviceController.deleteDevice(DeviceController.java:61)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:601)
    org.springframework.web.bind.annotation.support.HandlerMethodInvoker.doInvokeMethod(HandlerMethodInvoker.java:710)
    org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:167)
    org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:414)
    org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:402)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:771)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:716)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:647)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:552)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

Моя база данных
Таблица сотрудников

 CREATE TABLE `Employee` (  `empId` int(11) NOT NULL,  
 `empName` varchar(255) DEFAULT NULL,  `empExp` int(11) NOT NULL, 
 `empTeam`      varchar(255) DEFAULT NULL, `teamId` int(11) DEFAULT NULL, 
  PRIMARY KEY (`empId`)),KEY `FK21` (`teamId`),  
CONSTRAINT `FK21` FOREIGN KEY (`teamId`) REFERENCES `Team` (`teamId`));

Командная таблица

  CREATE TABLE `Team` (  `teamId` int(11) NOT NULL,  
    `teamName` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`teamId`));

Стол — платформа

 CREATE TABLE `Platform` (  `pId` int(11) NOT NULL,  
  `Pname` varchar(255) DEFAULT NULL,`teamId` int(11) DEFAULT NULL,
PRIMARY KEY (`pId`),KEY `FK22` (`teamId`),  CONSTRAINT `FK22` FOREIGN KEY (`teamId`) REFERENCES `Team` (`teamId`));

Resource.java

 @Entity
@Table(name="Employee")
public class Resource implements Serializable {

    private static final long serialVersionUID = -723583058586873479L;

    @Id
    @Column(name="empid")
    private Integer empId;

    @Column(name="empname")
    private String empName;

    @Column(name="empexp")
    private Integer empExp;



    @Column(name="empteam")
    private String empTeam;

      @ManyToOne(cascade = CascadeType.ALL)
     @JoinColumn(name="teamId")
     private AddTeam teams;



    public Integer getEmpId(){
        return empId;
    }
    public void setEmpId(Integer empId){
        this.empId=empId;
    }


    public String getEmpName(){
        return empName;
    }
    public void setEmpName(String empName){
        this.empName=empName;
    }

    public Integer getEmpExp(){
        return empExp;
    }
    public void setEmpExp(Integer empExp){
        this.empExp=empExp;
    }



    public String getEmpTeam(){
        return empTeam;
    }
    public void setEmpTeam(String empTeam){
        this.empTeam=empTeam;
    }


    public AddTeam getTeams() {
          return teams;
         }

         public void setTeams(AddTeam teams) {
          this.teams = teams;
         }

}

AddTeam.java

 @Entity
@Table(name="Team")

public class AddTeam {


    @Id
    @Column(name="teamId")



    private Integer teamId;

    @Column(name="teamName")
    private String teamName;

    public Integer getTeamId() {
          return teamId;
         }

         public void setTeamId(Integer teamId) {
          this.teamId = teamId;
         }

         public String getTeamName() {
          return teamName;
         }

         public void setTeamName(String teamName) {
          this.teamName = teamName;
         }
              }

Device.java

 @Entity
@Table(name="Platform")
public class Device implements Serializable{

    private static final long serialVersionUID = -723583058586873479L;
    @Id
    @Column(name="pId")
    private Integer pId;

    @Column(name="Pname")
    private String pname;



     @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name="teamId")
    private AddTeam teams;


    public Integer getpId(){
        return pId;
    }
    public void setpId(Integer pId){
        this.pId=pId;
    }


    public String getpname(){
        return pname;
    }
    public void setpname(String pname){
        this.pname=pname;
    }

    public AddTeam getTeams(){
        return teams;
    }
    public void setTeams(AddTeam teams){
        this.teams=teams;
    }
}

and this is the query used in ResourceDaoImpl.java

 @Override
    public void deleteResource(int resourceid) {
        // TODO Auto-generated method stub
        sessionfactory.getCurrentSession().createQuery("DELETE FROM Resource WHERE empid="  resourceid).executeUpdate();

        }

Query used to delete in AddTeamDaoImpl.java

 @Override
    public void deleteTeams(int teamid) {
        // TODO Auto-generated method stub
        sessiofactory.getCurrentSession().createQuery("DELETE FROM AddTeam WHERE teamid=" teamid).executeUpdate();

    }

Query used to delete DeviceDaoImpl.java

 @Override
    public void deleteDevice(int deviceid) {
        // TODO Auto-generated method stub
        sessionfactory.getCurrentSession().createQuery("DELETE FROM AddTeam WHERE teamid=" deviceid).executeUpdate();
    }

deletefunction in ResourceController.java

 @RequestMapping(value="/delete",method=RequestMethod.GET)
    public ModelAndView deleteResourceDetails(@ModelAttribute("command")  Resource resource,
            BindingResult result){
        resourceServices.deleteResource(resource.getEmpId());
        Map<String, Object> model = new HashMap<String, Object>();
        model.put("resourcekey", resourceServices.listResources());
        model.put("teamKey", addteamServices.listTeams());
        return new  ModelAndView("EditTeam",model);
    }

delete function in DeviceController.java

 @RequestMapping(value="/deletePlatform", method = RequestMethod.GET)
    public ModelAndView deleteDevice(@ModelAttribute("command") Device device, BindingResult result){
         deviceService.deleteDevice(device.getpId());
         Map<String, Object> model = new HashMap<String, Object>();
         model.put("platformKey", deviceService.listDevice());
         model.put("teamKey", teamservice.listTeams());
         return new ModelAndView("Device",model);
        }

EmployeeTeam.jsp

 <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>

    <%@taglib uri="http://www.springframework.org/tags/form" prefix="form"%>
     <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Resource Manager</title>
</head>
<body>

<center>
<h2>Add Resources</h2>
<form:form method="POST" action="save.html">
<table>
  <tr>
    <td><form:label path="empId">Employee Id</form:label></td>
    <td><form:input path="empId" id="demo" value= "${resource.empId }"/></td>
  </tr>
  <tr>
    <td><form:label path="empName">Name</form:label></td>
    <td><form:input path="empName" value="${resource.empName }"/></td>
  </tr>

  <tr>
    <td><form:label path="empExp">Experience</form:label></td>
    <td><form:input path="empExp" value="${resource.empExp }"/></td>
  </tr>


   <tr>
    <td><form:label path="empTeam">Team</form:label></td>
    <td><form:input path="empTeam" value="${resource.empTeam}"/></td>
  </tr>


    <tr>
                    <td>
                        <form:label path="teams.teamId">Team Name</form:label>
                    </td>
                    <td>
                        <form:select path="teams.teamId" cssStyle="width: 150px;">    
                            <option value="-1">Select a type</option>
                            <c:forEach items="${teamKey}" var="teams">
                            <option value="${teams.teamId}">${teams.teamName}</option>
                            </c:forEach>
                        </form:select>
                    </td>
                </tr>
                <tr>


   <td colspan="2"><input type="submit"value="Submit"></td>

    </tr>

 </table>
 </form:form>
<br/>

 <c:if test="${!empty resourcekey}">
    <table align="center" border="1">
        <tr>
            <th> Emp ID</th>
            <th>Emp Name</th>
            <th>Emp Exp</th>
            <th>Emp Team</th>
            <th>Emp TeamNmae</th>
            <th>Emp skills</th>
            <th>Options</th>
        </tr>

        <c:forEach items="${resourcekey}" var="resource">
            <tr>
                <td><c:out value="${resource.empId}"/></td>
                <td><c:out value="${resource.empName}"/></td>
                <td><c:out value="${resource.empExp}"/></td>
                <td><c:out value="${resource.empTeam}"/></td>
                <td><c:out value="${resource.teams.teamName}"/></td>



                <td align="center"><a href="editPublication.html?empId=${resource.empId}">Edit</a> | <a href="deleteResource.html?empId=${resource.empId}">Delete</a></td>
            </tr>
        </c:forEach>
    </table>
</c:if>

    <h2><a href="addTeam.html">Adding Category</a></h2>
</center>
</body>
</html>

Team.jsp

 <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
      <%@taglib uri="http://www.springframework.org/tags/form" prefix="form"%>

    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Emoloyee details</title>

<center>
<h2>Add Team Details</h2>
<form:form method="POST" action="Team.html">
   <table>
                <tr>
                    <td><form:label path="teamId">Team ID:</form:label></td>
                    <td><form:input path="teamId" id="demo" value="${team.teamId}"/></td>
                </tr>
                <tr>
                    <td><form:label path="teamName">Team Name:</form:label></td>
                    <td><form:input path="teamName" value="${team.teamName}"/></td>
                </tr>

                <tr>
                <tr>
                    <td>amp;nbsp;</td>
                  <td><input type="submit" value="SAVE"/></td>
                </tr>
            </table> 
        </form:form>
        <br/>
  <c:if test="${!empty teamKey}">
    <table align="center" border="1">
        <tr>
            <th>Category ID</th>
            <th>Category Name</th>
            <th>Options</th>
        </tr>

        <c:forEach items="${teamKey}" var="team">
            <tr>
                <td><c:out value="${team.teamId}"/></td>
                <td><c:out value="${team.teamName}"/></td>
                <td align="center"><a href="editTeam.html?teamId=${team.teamId}">Edit</a> |
                 <a href="deleteTeam.html?teamId=${team.teamId}">Delete</a></td>
            </tr>
        </c:forEach>
    </table>
</c:if>
<h2><a href="updateresource.html">Adding Publication</a></h2>


</center>



</body>
</html>

Platform.jsp

 <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>

    <%@taglib uri="http://www.springframework.org/tags/form" prefix="form"%>
     <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Platform</title>
</head>
<body>

<form:form method="POST" action="savedevice.html">
<table>
 <tr>
   <td><form:label path="pId">Platform Id</form:label></td>
   <td><form:input path="pId" value="${platform.pId}"/></td>
 </tr>

 <tr>
   <td><form:label path="pname">Platform Name</form:label></td>
   <td><form:input path="pname" value="${platform.pname}"/></td>
 </tr>


 <tr>
   <td><form:label path="teams.teamId">Team Name</form:label></td>
   <td><form:select path="teams.teamId" cssStyle="width: 150px;">
             <option value="-1"> select Team</option>
             <c:forEach items="${teamKey}" var="teams">
             <option value="${teams.teamId}">${teams.teamName}</option>
             </c:forEach>
       </form:select>
       </td>
 </tr>

 <tr>
   <td colspan="2"><input type="submit"value="Submit"></td>

    </tr>
</table>


</form:form>
<c:if test="${!empty platformKey }">
  <table align="center" border="1">
   <tr>
    <th>Platform Id</th>
    <th>Platform Name</th>
    <th>Team Name</th>

   </tr>

       <c:forEach items="${platformKey}" var="platform">
        <tr>
          <td><c:out value="${platform.pId }"/></td>
          <td><c:out value="${platform.pname }"/></td>
          <td><c:out value="${platform.teams.teamName}"/></td>
          <td><a href="editPlatform.html?pId=${platform.pId}">Edit</a> | <a href="deletePlatform.html?pId=${platform.pId}">Delete</a></td>


        </tr>


       </c:forEach>


  </table>


</c:if>
</body>
</html>

Пожалуйста, помогите, заранее спасибо

Ответ №1:

Ваш запрос в DeviceDaoImpl выглядит неправильно:

 "DELETE FROM AddTeam WHERE teamid=" deviceid

В качестве общего намека: опубликованный вами stacktrace мог бы направить вас прямо к проблеме.

 com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: ...
    ...
    com.resource.ResourceDao.DeviceDaoImpl.deleteDevice(DeviceDaoImpl.java:39)
    ...

Ответ №2:

Чтобы решить эту проблему, родительский файл должен быть сначала удален:

http://www.baeldung.com/spring-dataIntegrityviolationexception