使用NamedParameterJdbcTemplate将数据发送到数据库

问题描述：

package com.techm.template;
import java.sql.Types;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class InsertRecordInDatabaseWithJdbcTemplate {

    private static final String driverClassName = "oracle.jdbc.driver.OracleDriver";

    private static final String url = "jdbc:oracle:thin:@localhost:1521:xe";

    private static final String dbUsername = "SYSTEM";

    private static final String dbPassword = "9848451415";

    private static final String sql = "INSERT INTO employee(firstname,lastname,username,password) VALUES(?,?,?,?)";


    private static DataSource dataSource;



    public static void main(String[] args) throws Exception {

        dataSource = getDataSource();

        Employee e = new Employee();
        e.setFirstname("Surya Teja");
        e.setLastname("Tammana");
        e.setUsername("surya_kvm");
        e.setPassword("9848451415");

        int result = saveRecord(e);
        if(result != 0){
            System.out.println("Success");
        }
        else{
            System.out.println("Failure");
        }

    }



   public static int saveRecord(Employee e) {

       NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);

       SqlParameterSource params = new MapSqlParameterSource();

       Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("firstnames", e.getFirstname());
        parameters.put("lastname", e.getLastname());
        parameters.put("username", e.getUsername());
        parameters.put("password", e.getPassword());

        int result = template.update(sql, parameters);
        return result;
   }



 public static DriverManagerDataSource getDataSource() {

 DriverManagerDataSource dataSource = new DriverManagerDataSource();
 dataSource.setDriverClassName(driverClassName);
 dataSource.setUrl(url);
 dataSource.setUsername(dbUsername);
 dataSource.setPassword(dbPassword);
 return dataSource;

    }
}

Employee.java

package com.techm.template;

public class Employee {

private String firstname;
private String lastname;
private String username;
private String password;
public String getFirstname() {
    return firstname;
}
public void setFirstname(String firstname) {
    this.firstname = firstname;
}
public String getLastname() {
    return lastname;
}
public void setLastname(String lastname) {
    this.lastname = lastname;
}
public String getUsername() {
    return username;
}
public void setUsername(String username) {
    this.username = username;
}
public String getPassword() {
    return password;
}
public void setPassword(String password) {
    this.password = password;
}




}

控制台

2017年1月22日上午12:25:24 org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName 信息:加载的JDBC驱动程序:oracle.jdbc.driver.OracleDriver 线程主" org.springframework.dao.InvalidDataAccessApiUsageException中的异常:SQL [INSERT INTO employee(firstname，lastname，username，password)VALUES(?，?，?，?)]:给定4个参数，但预期为0 在org.springframework.jdbc.core.PreparedStatementCreatorFactory $ PreparedStatementCreatorImpl.(PreparedStatementCreatorFactory.java:221)处在org.springframework.jdbc.core.PreparedStatementCreatorFactory $ PreparedStatementCreatorImpl.处(PreparedStatementCreatorFactory.java:199) 在org.springframework.jdbc.core.PreparedStatementCreatorFactory.newPreparedStatementCreator(PreparedStatementCreatorFactory.java:173)处在org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator(NamedParameterJdbcTemplate.java:351)处在org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:287) 在org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:292) 在com.techm.template.InsertRecordInDatabaseWithJdbcTemplate.saveRecord(InsertRecordInDatabaseWithJdbcTemplate.java:64) 在com.techm.template.InsertRecordInDatabaseWithJdbcTemplate.main(InsertRecordInDatabaseWithJdbcTemplate.java:40)

Jan 22, 2017 12:25:24 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName INFO: Loaded JDBC driver: oracle.jdbc.driver.OracleDriver Exception in thread "main" org.springframework.dao.InvalidDataAccessApiUsageException: SQL [INSERT INTO employee(firstname,lastname,username,password) VALUES(?,?,?,?)]: given 4 parameters but expected 0 at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.(PreparedStatementCreatorFactory.java:221) at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.(PreparedStatementCreatorFactory.java:199) at org.springframework.jdbc.core.PreparedStatementCreatorFactory.newPreparedStatementCreator(PreparedStatementCreatorFactory.java:173) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator(NamedParameterJdbcTemplate.java:351) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:287) at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:292) at com.techm.template.InsertRecordInDatabaseWithJdbcTemplate.saveRecord(InsertRecordInDatabaseWithJdbcTemplate.java:64) at com.techm.template.InsertRecordInDatabaseWithJdbcTemplate.main(InsertRecordInDatabaseWithJdbcTemplate.java:40)

答

您正在SQL查询中使用位置参数.这不起作用，因为您使用的是NamedParameterJdbcTemplate，它需要命名参数.只需修改查询以使用类似这样的命名参数即可:

You are using positional parameters in your SQL query. This doesn't work because you are using the NamedParameterJdbcTemplate which requires named parameters. Just modify your query to use named parameters like that:

"INSERT INTO employee(firstname, lastname, username, password) VALUES(:firstname, :lastname, :username, :password)"

还可以将Employee对象用作参数源:

Also you could use the Employee object as a parameter source:

public static int saveRecord(Employee e) {
        NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);

        SqlParameterSource params = new BeanPropertySqlParameterSource(e);

        int result = template.update(sql, params);
        return result;
   }

BeanPropertySqlParameterSource将使用雇员"属性来设置参数.

The BeanPropertySqlParameterSourcewill use Employees properties to set the parameters.

使用NamedParameterJdbcTemplate将数据发送到数据库

相关推荐