window.ENTITIES={'/api/snippets/java/spring%20boot%202%20-%20jdbctemplate%20insert%20query%20example%20to%20mysql%20database':[{"result":true,"message":null,"batch":{"type":"java","name":"spring boot 2 - jdbctemplate insert query example to mysql database","items":[{"id":"DZExr1","type":"java","name":"Spring Boot 2 - JdbcTemplate INSERT query example to MySQL database","content":"package com.example.demo;\n\nimport org.springframework.beans.factory.annotation.Autowired;\nimport org.springframework.http.MediaType;\nimport org.springframework.jdbc.core.JdbcTemplate;\nimport org.springframework.jdbc.core.PreparedStatementCreator;\nimport org.springframework.jdbc.support.GeneratedKeyHolder;\nimport org.springframework.jdbc.support.KeyHolder;\nimport org.springframework.stereotype.Controller;\nimport org.springframework.web.bind.annotation.PostMapping;\nimport org.springframework.web.bind.annotation.RequestBody;\nimport org.springframework.web.bind.annotation.ResponseBody;\n\nimport java.sql.*;\nimport java.util.Collections;\n\n@Controller\npublic class UsersController {\n\n    @Autowired\n    private JdbcTemplate jdbcTemplate;\n\n    // POST http://localhost:8080/api/user/create\n    // echo '{\"name\":\"john\",\"email\":\"john@email.com\"}' | curl -X POST -H \"Content-Type: application/json\" -d @- http://localhost:8080/api/user/create\n    //\n    @PostMapping(\n            value = \"/api/user/create\",\n            consumes = MediaType.APPLICATION_JSON_VALUE,\n            produces = MediaType.APPLICATION_JSON_VALUE\n    )\n    @ResponseBody\n    public Object createUser(@RequestBody UserEntity userEntity) throws SQLException {\n        String query = \"INSERT INTO `users`\\n\" +\n                       \"\\t(`name`, `email`)\\n\" +\n                       \"VALUES\\n\" +\n                       \"\\t(?, ?)\";\n        KeyHolder keyHolder = new GeneratedKeyHolder();\n        PreparedStatementCreator statementCreator = (Connection connection) -> {\n            PreparedStatement preparedStatement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);\n            preparedStatement.setString(1, userEntity.getName());\n            preparedStatement.setString(2, userEntity.getEmail());\n            return preparedStatement;\n        };\n        int updatesCount = this.jdbcTemplate.update(statementCreator, keyHolder);\n        if (updatesCount == 1) {\n            Number generatedKey = keyHolder.getKey();\n            if (generatedKey == null) {\n                throw new SQLException(\"Getting user id error.\");\n            }\n            long userId = generatedKey.longValue(); // inserted user id\n            return Collections.singletonMap(\"userId\", userId);\n        }\n        throw new SQLException(\"Expected one row insert.\"); // should never happen\n    }\n}","source":"https://dirask.com/posts/Spring-Boot-2-JdbcTemplate-INSERT-query-example-to-MySQL-database-jMAVaj","author":{"id":"Ro42dD","name":"Creg","avatar":"1667335677213__Ro42dD__w40px_h40px.jpg","points":9600,"role":"ADMIN"},"creationTime":1652571443000,"updateTime":1652571495000,"removalTime":null},{"id":"D9WrVj","type":"java","name":"Spring Boot 2 - JdbcTemplate INSERT query example to MySQL database","content":"package com.example.demo;\n\nimport org.springframework.beans.factory.annotation.Autowired;\nimport org.springframework.http.MediaType;\nimport org.springframework.jdbc.core.JdbcTemplate;\nimport org.springframework.jdbc.core.PreparedStatementCreator;\nimport org.springframework.jdbc.core.RowMapper;\nimport org.springframework.jdbc.support.GeneratedKeyHolder;\nimport org.springframework.jdbc.support.KeyHolder;\nimport org.springframework.stereotype.Controller;\nimport org.springframework.web.bind.annotation.PostMapping;\nimport org.springframework.web.bind.annotation.RequestBody;\nimport org.springframework.web.bind.annotation.ResponseBody;\n\nimport java.sql.*;\nimport java.util.List;\n\n@Controller\npublic class UsersController {\n\n    @Autowired\n    private JdbcTemplate jdbcTemplate;\n\n    // --------------------------------------------------------------------\n\n    // POST http://localhost:8080/api/user/create\n    // echo '{\"name\":\"john\",\"email\":\"john@email.com\"}' | curl -X POST -H \"Content-Type: application/json\" -d @- http://localhost:8080/api/user/create\n    //\n    @PostMapping(\n            value = \"/api/user/create\",\n            consumes = MediaType.APPLICATION_JSON_VALUE,\n            produces = MediaType.APPLICATION_JSON_VALUE\n    )\n    @ResponseBody\n    public UserEntity createUser(@RequestBody UserEntity userEntity) throws SQLException {\n        long userId = this.insertUser(userEntity);\n        return this.fetchUser(userId);\n    }\n\n    // --------------------------------------------------------------------\n  \n    private long insertUser(UserEntity userEntity) throws SQLException {\n        String query = \"INSERT INTO `users`\\n\" +\n                       \"\\t(`name`, `email`)\\n\" +\n                       \"VALUES\\n\" +\n                       \"\\t(?, ?)\";\n        KeyHolder keyHolder = new GeneratedKeyHolder();\n        PreparedStatementCreator statementCreator = (Connection connection) -> {\n            PreparedStatement preparedStatement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);\n            preparedStatement.setString(1, userEntity.getName());\n            preparedStatement.setString(2, userEntity.getEmail());\n            return preparedStatement;\n        };\n        int updatesCount = this.jdbcTemplate.update(statementCreator, keyHolder);\n        if (updatesCount == 1) {\n            Number generatedKey = keyHolder.getKey();\n            if (generatedKey == null) {\n                throw new SQLException(\"Getting user id error.\");\n            }\n            return generatedKey.longValue();\n        }\n        throw new SQLException(\"Expected one row insert.\"); // should never happen\n    }\n\n    private UserEntity fetchUser(long userId) {\n        String query = \"SELECT `id`, `name`, `email`\\n\" +\n                       \"FROM `users`\\n\" +\n                       \"WHERE `id` = ?\\n\" +\n                       \"LIMIT 1\";\n        PreparedStatementCreator statementCreator = (Connection connection) -> {\n            PreparedStatement preparedStatement = connection.prepareStatement(query);\n            preparedStatement.setLong(1, userId);\n            return preparedStatement;\n        };\n        List<UserEntity> users = this.jdbcTemplate.query(statementCreator, new UserRowMapper());\n        if (users.isEmpty()) {\n            return null;\n        }\n        return users.get(0);\n    }\n\n    // --------------------------------------------------------------------\n\n    private class UserRowMapper implements RowMapper<UserEntity> {\n        @Override\n        public UserEntity mapRow(ResultSet resultSet, int rowNumber) throws SQLException {\n            UserEntity user = new UserEntity();\n            user.setId(resultSet.getLong(\"id\"));\n            user.setName(resultSet.getString(\"name\"));\n            user.setEmail(resultSet.getString(\"email\"));\n            return user;\n        }\n    }\n}","source":"https://dirask.com/posts/Spring-Boot-2-JdbcTemplate-INSERT-query-example-to-MySQL-database-jMAVaj","author":{"id":"Ro42dD","name":"Creg","avatar":"1667335677213__Ro42dD__w40px_h40px.jpg","points":9600,"role":"ADMIN"},"creationTime":1652571457000,"updateTime":1653875337000,"removalTime":null}]}}]};