Iterating list and array by passing as a parameterClass in iBATIS sql map

A list/map can be passed as a parameterClass to an iBATIS sql map to iterate. A IN clause is the basic example of iteration over a list and create comma separated query. For example, if a list have 3 elements (10,20,30). It is passed as parameterClass in the query for a IN clause, the syntax is like following:

<select id="getAllScore" resultClass="score" parameterClass="java.util.List">
    SELECT * FROM score WHERE id IN
        <iterate open="(" close=")" conjunction=",">
            #value[]#
        </iterate>
</select>


You can define a list in the parameterClass. But what about a array? You cannot define array like int[] in the parameter class. Most surprising thing is, for an array you do not have to define a parameterClass in iBATIS. If nothing is defined as parameterClass then it is considered as Array.

If you want to iterate over an array with then the following code works fine. Every array elements will be printed in the code #[]#.

<select id="getAllScore" resultClass="score">
    SELECT * FROM score WHERE id IN
        <iterate open="(" close=")" conjunction=",">
            #[]#
        </iterate>
</select>

 

By: Md. Shahjalal

Spring JdbcTemplate to retrieve List and Map

org.springframework.jdbc.core.JdbcTemplate used to perform query for specific result through out some query parameters. Some of the basic queries like query, queryForList, QueryForObject as the common understandings of the methods name. A query or queryForList method returns a list of desired rows but queryForMap returns only a single row where all the column names are key of the Map.

To retrieve a list, org.springframework.jdbc.core.RowMapper can be used along with java.sql.ResultSet. A sample code is like,


List sampleClassList = jdbcTemplate.query(query, new Object[]{parameters.....},
new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
SampleClass sampleClass = new SampleClass();
sampleClass.setValue1(rs.getString("column1"));
sampleClass.setValue2(rs.getString("column2"));
return sampleClass;
}
});


In most of the cases developers convert a retrieved list to their desired Map later by manipulating the list. The basic reason behind is a map will not contain duplicate values as key but to a list data can be redundant. Its also possible to get a Map where one column as key and other as value of the map. org.springframework.jdbc.core.ResultSetExtractor can be used for customize results like Map. The following sample code can do this thing. Its really a very good option if one do not want to have duplicate value in a specific column. Its easier to make that thing a key.


Map map = (Map)jdbcTemplate.query(query, new Object[]{parameters....},
new ResultSetExtractor() {
public Object extractData(ResultSet rs) throws SQLException {
Map map = new LinkedHashMap();
while (rs.next()) {
String col1 = rs.getString("col1");
String col2 = rs.getString("col2");
map.put(col1, col2);
}
return map;
};
});


By: Md. Shahjalal

java.sql.SQLException: ORA-02291: integrity constraint

One of the most unusual and frustrating error of Oracle database. May happen because of no reference of a column in a table. Suppose you refer a column ca of table a for a column cb of table b. Your table definition is as like

foreign key (ca) references b(cb)

When you want to insert data on table a with such value for column ca with value that doesn’t exits in column cb of table b. You are just identified to have such error message.

by: Md. Shahjalal