can't get prepared statement to work from spring mysql

Question

I've created a prepared statement from within a service in spring. It's very simple, but quite long. Initially, it was working, but is not longer.

Here's the code:

        String sql = "insert into myData.myTable " +                    
            "   (version_id, "+
            "   Category, "+
            "   my_name, "+
            "   my_id,  "+
                ...bunch of fields ....
            "   lastField   "+
            "   )                   "+
            "   select              "+
            "   ?,              "+
            "   Category,       "+
            "   ?,              "+
            "   lastField       "+
            "   from myData.myOtherFile where my_id = ?";

        Connection conn = dataSource.getConnection();
        PreparedStatement  ps = conn.prepareStatement(sql);
        ps.setLong(1, version.getId());
        ps.setString(2, pp.getName());
        ps.setLong(3, pp.getMyId());

        ps.executeUpdate();
        ps.close();

Now, it's not working. No exceptions or anything - it just doesn't insert. I also tried using JdbcTemplate, and it doesn't work, although when I executed the SQL statement shown by hibernate - it worked.

Any ideas?

My first impression is that the WHERE clause fetches no rows. To confirm it, you'd better get the number of affected rows returned by `executeUpdate()`, and trace it. — Little Santi, Jul 26 '15 at 18:49
It may be similar to this: http://stackoverflow.com/q/14650288/4472840 — Yosef Weiner, Jul 27 '15 at 13:03

score 0 · Answer 1 · answered Jul 29 '15 at 10:50

I moved on from the JDBC solution and created a stored procedure to solve this. I still have no idea why it didn't work, but I had a pretty good idea that the stored procedure would. Why? Because it eliminates the JDBC layer, where the problems apparently were occurring. There is a caveat, though - I had to call the stored procedure in a separate transaction, otherwise some of the other table's weren't being correctly inserted.

can't get prepared statement to work from spring mysql

1 Answers1