Zulfi Zulfi - 5 months ago 68
SQL Question

Spring JDBCTemplate Mysql throwin ArrayIndxOutofBound from Mysql driver

I am trying to fetch a record using JDBCTemplate query() and passing the parameter bindings as Map. Mysql is throwing ArrayIndeOutOfBoundException . Below is the stacktrace.

Exception in thread "main" org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [select * from customers where cid= :cid];

Invalid argument value: java.lang.ArrayIndexOutOfBoundsException; nested exception is java.sql.SQLException: Invalid argument value:

java.lang.ArrayIndexOutOfBoundsException
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:108)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:645)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:680)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:777)
at com.jlcnindia.spring.jdbc.JdbcCustomerDAO.getCustomerByCid(JdbcCustomerDAO.java:32)
at com.jlcnindia.spring.jdbc.Lab58.main(Lab58.java:14)
Caused by: java.sql.SQLException: Invalid argument value: java.lang.ArrayIndexOutOfBoundsException
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)
at com.mysql.jdbc.PreparedStatement.setSerializableObject(PreparedStatement.java:3887)
at com.mysql.jdbc.PreparedStatement.setObject(PreparedStatement.java:3603)
at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:428)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:235)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:166)
at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.doSetValue(ArgumentPreparedStatementSetter.java:66)
at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.setValues(ArgumentPreparedStatementSetter.java:47)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:686)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629)
... 6 more
Caused by: java.lang.ArrayIndexOutOfBoundsException: 0
at com.mysql.jdbc.PreparedStatement.setBinaryStream(PreparedStatement.java:2871)
at com.mysql.jdbc.PreparedStatement.setSerializableObject(PreparedStatement.java:3884)
... 14 more


Here is my RowMapper class

package com.jlcnindia.spring.jdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;

public class CustomerRowMapper implements RowMapper<CustomerTO> {

@Override
public CustomerTO mapRow(ResultSet rs, int rn) throws SQLException {
System.out.println("mapping ");
CustomerTO cto=new CustomerTO();
cto.setCid(rs.getInt(1));
cto.setCname(rs.getString(2));
cto.setEmail(rs.getString(3));
cto.setPhone(rs.getLong(4));
cto.setCity(rs.getString(5));
return cto;

}


}

Dao Method that invokes the query

public CustomerTO getCustomerByCid(int cid) {
String sql="select * from customers where cid=:cid";

Map<String,Object> parameters=new HashMap<String, Object>();
parameters.put("cid", cid);

List<CustomerTO> list=jdbcTemp.query(sql, new CustomerRowMapper(),parameters);
if(null!=list && !list.isEmpty())
return list.get(0);
return null;
}


CustomerTO Class

package com.jlcnindia.spring.jdbc;

public class CustomerTO {
private int cid;
private String cname;
private String email;
private long phone;
private String city;


public CustomerTO(){}

public CustomerTO(String cname, String email, long phone, String city) {
super();
this.cname = cname;
this.email = email;
this.phone = phone;
this.city = city;
}




public int getCid() {
return cid;
}


public void setCid(int cid) {
this.cid = cid;
}


public String getCname() {
return cname;
}


public void setCname(String cname) {
this.cname = cname;
}


public String getEmail() {
return email;
}


public void setEmail(String email) {
this.email = email;
}


public long getPhone() {
return phone;
}


public void setPhone(long phone) {
this.phone = phone;
}


public String getCity() {
return city;
}


public void setCity(String city) {
this.city = city;
}

public String toString(){
return cid+"\t"+cname+"\t"+email+"\t"+phone+"\t"+city;
}

}


If i use ? instead of named parameter and pass the cid in place of paramaters in query as below

public CustomerTO getCustomerByCid(int cid) {
String sql="select * from customers where cid=?";
List<CustomerTO> list=jdbcTemp.query(sql, new CustomerRowMapper(),cid);
if(null!=list && !list.isEmpty())
return list.get(0);
return null;
}


it is working fine and giving me the result but if i pass the parameters map as above i get the exception .

Not able to understand why

UPDATE

Spring JBDC Template configuration

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost/jlcindiadb"/>
<property name="username" value="****"/>
<property name="password" value="***"/>
</bean>
<bean id="jdbcTemp" class="org.springframework.jdbc.core.JdbcTemplate" autowire="constructor">
<constructor-arg ref="dataSource"/>
</bean>
<bean id="jdbcCustomerDAO" class="com.jlcnindia.spring.jdbc.JdbcCustomerDAO"/>

Answer

JdbcTemplate doesn't support named parameters. You need... NamedParameterJdbcTemplate for that.

The method you're calling is that one:

public <T> List<T> query(String sql,
                         RowMapper<T> rowMapper,
                         Object... args)

which, as you see, takes parameter values as argument. So you're actually passing a Map as unique indexed parameter of the query, but the query doesn't have any parameter placeholder, hence the exception.