CODEWITHSUNDEEP
jdbctemplate
Bopsi
Bopsi

Reputation: 2436

How to use RowMapper to insert record into table?

How to insert data into table using row mapper?

I am trying this:

Employee user1 = jtemplate.queryForObject("INSERT INTO employee(id, name,salary) VALUES(10,'ABC',12333);",new BeanPropertyRowMapper<Employee>(Employee.class));

It gives me bad SQL grammar error.

But query works in SQL developer. What I am doing wrong?

Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [INSERT INTO employee(id, name,salary) VALUES(99,'ABC',12333)]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:411)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:466)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:476)
    at com.cts.orm.rowmapper.Test.main(Test.java:29)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
    at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:600)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
    at oracle.jdbc.driver.T4C8Odscrarr.doODNY(T4C8Odscrarr.java:98)
    at oracle.jdbc.driver.T4CStatement.doDescribe(T4CStatement.java:805)
    at oracle.jdbc.driver.OracleStatement.describe(OracleStatement.java:3978)
    at oracle.jdbc.driver.OracleResultSetMetaData.<init>(OracleResultSetMetaData.java:55)
    at oracle.jdbc.driver.OracleResultSetImpl.getMetaData(OracleResultSetImpl.java:175)
    at org.springframework.jdbc.core.BeanPropertyRowMapper.mapRow(BeanPropertyRowMapper.java:240)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60)
    at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:455)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:400)
    ... 3 more

Upvotes: 4

Views: 8693

Answers (3)

piepera
piepera

Reputation: 2113

NamedParameterJdbcTemplate does what you want:

namedParameterJdbcTemplate.update(
    "INSERT INTO employee(id, name, salary) VALUES (:id, :name, :salary)",
    new BeanPropertySqlParameterSource(Employee.class));

Upvotes: 4

Zain Aftab
Zain Aftab

Reputation: 701

removing the semi colon at the end of the statement should solve the problem use it like this. when using the jdbc templates it is not good to use semicolon at the end of sql statements

Employee user1 = jtemplate.queryForObject("INSERT INTO employee(id, name,salary) VALUES(10,'ABC',12333)",new BeanPropertyRowMapper<Employee>(Employee.class));

try using it like

    jtemplate.update("INSERT INTO Employee(ID, NAME, Salary) VALUES (?, ?, ?)",
     new Object[] { employee.getId(), employee.getName(), employee.getSalary() });

i am using this and it works correctly and displays no errors

Upvotes: 0

Anatoly Deyneka
Anatoly Deyneka

Reputation: 1258

Try this: 

jdbcTemplate.update("INSERT INTO employee(id, name,salary) VALUES(?,?,?)", 10, "ABC", 12333);

Upvotes: -1

Related Questions