how to catch sqlException or signal message from mysql trigger

Question

I want to catch message of a trigger from mysql database and print it via my thymeleaft template but I came across a problem with catching message of that trigger

part of pet repository

 void save(Pet pet) throws SQLException;

this is part of a controller, method of catching doesnt work and I don't know how to do it properly

   @PostMapping("/pets/new")
    public String processCreationForm(Owner owner, @Valid Pet pet, BindingResult result, ModelMap model) {
        if (StringUtils.hasLength(pet.getName()) && pet.isNew() && owner.getPet(pet.getName(), true) != null){
            result.rejectValue("name", "duplicate", "already exists");
        }
        owner.addPet(pet);
        if (result.hasErrors()) {
            model.put("pet", pet);
            return VIEWS_PETS_CREATE_OR_UPDATE_FORM;
        } else {
            try {
                this.pets.save(pet);
            }catch (SQLException e){


                System.out.println("mamamamam"+e.getSQLState());
            }
            return "redirect:/owners/{ownerId}";
        }
    }

errors:

2018-11-19 23:26:24.025  WARN 4532 --- [nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1644, SQLState: 45000
    2018-11-19 23:26:24.025 ERROR 4532 --- [nio-8080-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper   : Za duzo zwierzat
    2018-11-19 23:26:24.040 ERROR 4532 --- [nio-8080-exec-6] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not execute statement; nested exception is org.hibernate.exception.GenericJDBCException: could not execute statement] with root cause

    java.sql.SQLException: Za duzo zwierzat

this is relevant part of mysql trigger (trigger works)

if (ilosc_zwierzat>1)
     then 
     signal sqlstate '45000' SET MESSAGE_TEXT = 'Za duzo zwierzat';          

     end if;

I tried to do it this way but it doesn't work ( I've got a lot of text with errors on my page.)

 @ExceptionHandler(SQLException.class)
    public ModelAndView handleError(HttpServletRequest req, Exception ex) {

        System.out.println("WIADMOSCcccccccccccccccccc"+ex.getMessage());
        ModelAndView mav = new ModelAndView("error2");
        mav.addObject("er", ex.getMessage());
        mav.addObject("url", req.getRequestURL());
        mav.setViewName("error2");
        return mav;
    }

Evidently the posted code doesn't catch the exception, the stacktrace doesn't seem to list the controller. is this using open-session-in-view? where are the transaction boundaries? remember Hibernate uses transactional write-behind so the SQL doesn't fire until a flush or commit happens. Consider using services, see [Service layer and controller: who takes care of what?](https://stackoverflow.com/questions/3885675/service-layer-and-controller-who-takes-care-of-what/3885783#3885783) — Nathan Hughes, Nov 19 '18 at 22:50

score 1 · Accepted Answer · answered Oct 26 '19 at 16:21

1

Well since it is old qustion I know the answer. I simple should Spring JDBC to get more control over sqlexception handling, If I use JPA it handle error for me, so not what I wanted

answered Oct 26 '19 at 16:21

wwww

760
1
11
20

how to catch sqlException or signal message from mysql trigger

1 Answers1