how can I convert postgresql array to java array in getJdbcTemplate().query

Question

I am using spring to interact with postgresql. Here is my problem.

Person have three attributes:

name(String)
hobbies(String[])
gender(boolean)

If I try to get a list of person from database using the method below

List<Person> person=  getJdbcTemplate().query("select * from person where name=?",new BeanPropertyRowMapper<person>(Person.class),name);

The compiler prompts

Failed to convert property value of type 'org.postgresql.jdbc.PgArray' to required type 'java.lang.String[]' for property 'hobbies';

Is there any way to map pgarray to java array?

Dmitriy Popov · Accepted Answer · 2023-07-28 09:48:32Z

1

By implementing our own RowMapper

public class PersonRowMapper implements RowMapper<Person> {

     Person mapRow(ResultSet rs, int rowNum) throws SQLException {
          Person person = new Person();
          person.setName("nameColumnName");
          person.setGender(rs.getBoolean("genderColumnName"));

          String[] hobbies = (String[]) rs.getArray("hobbies");
          person.setHobbies(hobbies);

          return person;
     }

}

Using the above code, we can manually cast the result of rs.getArray() into String[].

RowMapper helps to provide custom logic to parse the result set into our own object. Use it in place of BeanPropertyRowMapper like below:

List<Person> person = getJdbcTemplate().query("select * from person where name = ?", new PersonRowMapper(), name);

edited Jul 28, 2023 at 9:48

Dmitriy Popov

2,3703 gold badges29 silver badges40 bronze badges

answered Oct 12, 2019 at 16:33

MICKY

631 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Manu Viswam Over a year ago

Please add more explanation

c.sankhala Over a year ago

@ManuViswam added full example and description. Thanks for pointing out.

Suraj Rao · Accepted Answer · 2023-04-17 05:53:50Z

0

I didn't find how to solve the problem with jdbctemplate + pgArray. So I wrote some fix.
You can use own ColumnMapRowMapper.

public class ColumnRawMapper extends ColumnMapRowMapper {
        @Override
        protected Object getColumnValue(ResultSet rs, int index) throws SQLException {
            Object obj = rs.getObject(index);
            if (obj instanceof PgArray) {
                return ((PgArray) obj).getArray();
            }
            return super.getColumnValue(rs, index);
        }
    }

Map<String, Object> ggWp = jdbcTemplate.queryForObject("select * from temp where ss='3'", new ColumnRawMapper());

edited Apr 17, 2023 at 5:53

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Apr 17, 2023 at 5:31

Zhenya

796 bronze badges

Collectives™ on Stack Overflow

how can I convert postgresql array to java array in getJdbcTemplate().query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related