Announcement Announcement Module
Collapse
No announcement yet.
jdbcTemplate insert in h2 Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • jdbcTemplate insert in h2

    hi

    i use spring 3.2.3 and h2 database.

    my user object only have string attribute.

    my class test
    Code:
    @RunWith(SpringJUnit4ClassRunner.class)
    @TransactionConfiguration(transactionManager = "transactionManager", defaultRollback = true)
    @Transactional
    @ContextConfiguration(locations = {"classpath:applicationContext-test.xml"})
    public class UserDaoTest extends AbstractTransactionalJUnit4SpringContextTests {
    
        @Autowired
        private UserDao userDao;
    
        @Test
        public void getUser() {
            User user = userDao.findByUserId(1);
            assertNotNull(user);
        }
    
        @Test
        public void createUser(){
            User user = new User();
            user.setFirstName("Yvan");
            user.setLastName("Dubois");
            user.setEmail("[email protected]");
            user = userDao.createUser(user);
            assertTrue(user.getId()>0);
        }
        
        @Before
        public void setUp() {
            
        }
        
        @After
        public void tearDown(){
            
        }
    }
    i try to insert an user
    Code:
    public User createUser(User user) {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            int userId = getJdbcTemplate().update("INSERT INTO USERS (FIRSTNAME,LASTNAME, EMAIL) VALUES(?, ?, ?)", new Object[]{user.getFirstName(), user.getLastName(), user.getEmail()}, keyHolder);
            
            if (userId > 0) {
                user.setId(keyHolder.getKey().intValue());
            }
            return user;
        }
    i get this error message

    Code:
      PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO USERS (FIRSTNAME,LASTNAME, EMAIL) VALUES(?, ?, ?)]; SQL state [90026]; error code [90026]; Serialization failed, cause: "java.io.NotSerializableException: org.springframework.jdbc.support.GeneratedKeyHolder" [90026-172]; nested exception is org.h2.jdbc.JdbcSQLException: Serialization failed, cause: "java.io.NotSerializableException: org.springframework.jdbc.support.GeneratedKeyHolder" [90026-172]
    if i use this code, that work

    Code:
    public User createUser(final User user) {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            getJdbcTemplate().update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
    
                    PreparedStatement ps = connection.prepareStatement("INSERT INTO USERS (ID, FIRSTNAME,LASTNAME, EMAIL) VALUES(default, ?, ?, ?)",
                            new String[]{"id"});
    
                    ps.setString(1, user.getFirstName());
                    ps.setString(2, user.getLastName());
                    ps.setString(3, user.getEmail());
                    return ps;
                }
            }, keyHolder);
    
            if(keyHolder.getKey()!=null){
                 user.setId(keyHolder.getKey().intValue());
            }
            return user;
        }
    i would like to avoid to write all this code... any idea?

  • #2
    The only way is to use the PreparedStatementCreator, no other update method supports the GeneratedKeyHolder. The reason is that one needs to specify the column-names or indexes to be returned, that can only be done when preparing the statement.

    If you want other update methods to support this, you might wan to enter an issue into JIRA.

    Comment


    • #3
      ok thanks a lot for this information

      Comment


      • #4
        Did you look at SimpleJdbcInsert? http://static.springsource.org/sprin...-jdbc-insert-2

        That's a lot easier to use than JdbcTemplate and a KeyHolder.

        Comment

        Working...
        X