Transactional, how it's working?

[shoo]

In my first Spring application, i'm tried to use transactions. I'm using JdbcTemplate and i'm have 2 tables: messages and pictures.
Method used for adding message, looks like:
1) updating messages table and returning KeyHolder
2) updating pictures table, query based on KeyHolder

I need to rollback transaction if something goes wrong. I using next sequence:
mark method as @Transactional(propagation = Propagation.REQUIRED)
try {
1) updating messages table and returning KeyHolder
throw new Exception();
2) updating pictures table, query based on KeyHolder
} catch

I expect that, Spring will make (1) and exit from try block, without making (2). So, transaction will be rollbacked.
BUT
Spring adds new message, and don't adds new picture. So, i have message without picture, as result.

Please help me to use @Transactional correctly. Tnx!

[Marten Deinum]

Please post code and configuration and the code how you test all this.

[shoo]

In MessageService, method:

    
    @Transactional(propagation = Propagation.REQUIRED)
    public Message setMessage(String author, String text, Integer prev_id) {
        final String sql = "INSERT INTO `messages` (`author`, `text`, `prev_id`) VALUES (?,?,?)";
        
        Message addedMessage = new Message();
        addedMessage.setAuthor(author);
        addedMessage.setText(text);
        addedMessage.setPrev_id(prev_id);
        
        final Message finalMessage = addedMessage;
        
        KeyHolder keyHolder = new GeneratedKeyHolder();
        
        
        try {
                   
            jdbcTemplate.update(
                new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection cnctn) {
                        try {
                            PreparedStatement ps = cnctn.prepareStatement(
                                sql, 
                                new String[] {"id"});
                            ps.setString(1, finalMessage.getAuthor());
                            ps.setString(2, finalMessage.getText());
                            ps.setInt(3, finalMessage.getPrev_id());
                            return ps;
                        } catch(SQLException e) {
                            System.out.println("Message PreparedStatement error:" + e.getSQLState());
                            return null;
                        }
                        
                    }                    
                }
                , keyHolder);        
        
            addedMessage.setId(keyHolder.getKey().intValue());
        
            if (true) {
                throw new SQLException();
            }
            
            final String sql_picture = "INSERT INTO `pictures` (`message_id`) VALUES (?)";
            final Integer messageId = addedMessage.getId();
        
            
            jdbcTemplate.update(
                new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection cnctn) throws SQLException {
                        try {
                            PreparedStatement ps = cnctn.prepareStatement(
                                sql_picture, 
                                new String[] {"id"});
                            ps.setInt(1, messageId);
                            return ps;
                        } catch(SQLException e) {
                            System.out.println("Picture PreparedStatement error:" + e.getSQLState());
                            return null;
                        }
                    }                    
                }
                , keyHolder);
        
            addedMessage.setPicture_id(keyHolder.getKey().intValue());
            
            
        } catch (DataAccessException e) {
            System.out.println("Can't add.");
        } catch (Exception e) {
            System.out.println("Some erroe.");
        }

        return addedMessage;
    }

Test(yeah, it is really bad):

    @Test
    public void testSetMessage() {
        System.out.println("Testing setMessage.");
        Message message = new Message();
        message.setId(2);
        for (int i = -10; i < 10; i++) {
            message = messageService.setMessage("TESTER", "Text "+i, i-1);
            System.out.println(i + ": " + message.getId() + " : " + message.getPicture_id());
        }
    }

As result:

Testing setMessage.
Can't add.
-10: null : null
Can't add.
-9: null : null
Can't add.
-8: null : null
Can't add.
-7: null : null
Can't add.
-6: null : null
Can't add.
-5: null : null
Can't add.
-4: null : null
Can't add.
-3: null : null
Can't add.
-2: null : null
Can't add.
-1: null : null
Can't add.
0: null : null
Can't add.
1: null : null
Can't add.
2: null : null
Some error.
3: 684 : null
Some error.
4: 685 : null
Some error.
5: 686 : null
Some error.
6: 687 : null
Some error.
7: 688 : null
Some error.
8: 689 : null
Some error.
9: 690 : null

And i'm have 684, 685, 686, 687, 688, 689, 690 in base :/

[Marten Deinum]

For starters you ode is flawed NEVER catch the exception and swallow. The transaction management needs to see the exception else rollbacks don't happen, the same for the PreparedStatementCreator (which I wonder why are you using that ?!).

Next there is an if (true) block so anything after that is, well pretty much useless.

[shoo]

Next there is an if (true) block so anything after that is, well pretty much useless.

Yeah, i know. I use it for testing transaction rollback.

which I wonder why are you using that ?!

I'm using that becouse like that was in tutorial. PreparedStatementCreator is bad practice? Or.. What should i use in this case?

Now i'm using with DAO and it working!

Thanks for help, bro.