Simple jdbcTemplate.query() is supposed to return a list, but isn't

Question

I'm using spring 4.0 (boot), and here is the code:

        String sql = "SELECT emp_id,name,role FROM employees";
        List<Employee> employees = jdbcTemplate.query(sql,(rs, rowNum)-> 
        new Employee(rs.getInt("emp_id"), rs.getString("name"),
                rs.getString("role")));         
        employees.forEach(employee -> {log.info(employee.toString());
            log.info("part a");});

The code looks so simple and straightforward, but the problem is that employees is not returning anything at all in the logs. Doesn't this mean that everything in employees is null?

There should be no problem with the Logger itself because the logging before this code works.

The database gets properly inputted. When I query through mysql client, I can see them there.

What kind of Java mistake am I making (if it is one)?

EDIT: this is the full function which I am performing simple database queries in and from which the above code comes from:

    @Override
    public void run(String... args) throws Exception {
        log.info("Creating tables");

        jdbcTemplate.execute("DROP TABLE IF EXISTS employee");
        jdbcTemplate.execute("CREATE TABLE employee (emp_id int, name varchar(100), role varchar(100))");

        log.info("Inserting Baggins Hopkins");
        int rowsAffected = jdbcTemplate.update("INSERT INTO EMPLOYEE(EMP_ID, NAME, ROLE)"
                + " VALUES(1,'Baggins Hopkins','thief')");
        log.info("rows affected: "+ Integer.toString(rowsAffected));
        int rowsAffected1 = jdbcTemplate.update("INSERT INTO EMPLOYEE(EMP_ID, NAME, ROLE)"
                + " VALUES(2,'Doolous Hopkins','robber')");
        log.info("rows affected: "+ Integer.toString(rowsAffected1));
        log.info("Querying for employee");
        String sql = "SELECT emp_id,name,role FROM employee";
        List<Employee> employees = jdbcTemplate.query(sql,(rs, rowNum)-> 
        new Employee(rs.getInt("emp_id"), rs.getString("name"),
                rs.getString("role")));
        log.info("Part A:");
        log.info(String.valueOf(employees.isEmpty()));
//      List<Employee> employees = new ArrayList<Employee>();
//      final List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
//      for(final Map row : rows) {
//          Employee employee = new Employee();
//          employee.setId((int)row.get("emp_id"));
//          employee.setName((String)row.get("name"));
//          employee.setRole((String)row.get("role"));
//      };

        employees.forEach(employee -> {log.info(employee.toString());
            log.info("part a");});

    }

This is the log that came from when there was an error:

 :: Spring Boot ::        (v2.1.0.RELEASE)

2018-11-07 22:08:36.599  INFO 5476 --- [           main] c.j.jdbctest1.JdbcTest1Application       : Starting JdbcTest1Application on KitKat with PID 5476 (C:\Users\Nano\Downloads\jdbc-test1\jdbc-test1\target\classes started by Nano in C:\Users\Nano\Downloads\jdbc-test1\jdbc-test1)
2018-11-07 22:08:36.599  INFO 5476 --- [           main] c.j.jdbctest1.JdbcTest1Application       : No active profile set, falling back to default profiles: default
2018-11-07 22:08:37.458  INFO 5476 --- [           main] c.j.jdbctest1.JdbcTest1Application       : Started JdbcTest1Application in 1.156 seconds (JVM running for 2.08)
2018-11-07 22:08:37.474  INFO 5476 --- [           main] c.j.jdbctest1.JdbcTest1Application       : Creating tables
2018-11-07 22:08:37.474  INFO 5476 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2018-11-07 22:08:37.614  INFO 5476 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2018-11-07 22:08:38.873  INFO 5476 --- [           main] c.j.jdbctest1.JdbcTest1Application       : Inserting Baggins Hopkins
2018-11-07 22:08:39.042  INFO 5476 --- [           main] c.j.jdbctest1.JdbcTest1Application       : rows affected: 1
2018-11-07 22:08:39.183  INFO 5476 --- [           main] c.j.jdbctest1.JdbcTest1Application       : rows affected: 1
2018-11-07 22:08:39.183  INFO 5476 --- [           main] c.j.jdbctest1.JdbcTest1Application       : Querying for employee
2018-11-07 22:08:39.214  INFO 5476 --- [           main] c.j.jdbctest1.JdbcTest1Application       : Part A:
2018-11-07 22:08:39.214  INFO 5476 --- [           main] c.j.jdbctest1.JdbcTest1Application       : true
2018-11-07 22:08:39.230  INFO 5476 --- [       Thread-1] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2018-11-07 22:08:39.230  INFO 5476 --- [       Thread-1] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

I did get if you see null instead of list of employees? Or just logging show you nothing? — Evgeny Ignatik, Nov 07 '18 at 13:03
@Evgeny Ignatik the logging shows me nothing. I didn't explicitly check if the List, employees, returns null. EDIT: I tried checking with the isEmpty() method, and it returned true. — DP Park, Nov 07 '18 at 13:06
I would sugges you to try “queryForList” method instead. — Evgeny Ignatik, Nov 07 '18 at 13:12
You are inserting into a table called **EMPLOYEE** whilst querying a table called **EMPLOYEES**... — M. Deinum, Nov 07 '18 at 13:17
@Evgeny Ignatik I tried using it but to no avail. To be specific, I declared a variable that returns a List> after processing queryForList. Then I did a for loop around that List. — DP Park, Nov 07 '18 at 13:21
@M. Deinum I fixed it. I changed all mentions of 'employees' in sql queries to employee. However it still will not work. I thought it would work though; I am confounded. — DP Park, Nov 07 '18 at 13:24
Correct. You are working with different table, aren’t you? — Evgeny Ignatik, Nov 07 '18 at 13:24
Try to return List how it was, and then try with queryForList — Evgeny Ignatik, Nov 07 '18 at 13:25
note: I edited the post to reflect the update I made to my code just now. — DP Park, Nov 07 '18 at 13:27
@Evgeny Ignatik It works now! The additional code for List> returns null. But, the former code I had which returns List now properly logs out the employees. Thank you Evgeny and Deinum, you have helped me fix this problem! — DP Park, Nov 07 '18 at 13:34
Please don't add a question status like "solved" to the question title. When the question is answered, then accept the answer which helped you to solve the issue. — Tom, Nov 07 '18 at 14:00

score 2 · Answer 1 · answered Nov 07 '18 at 13:43

2

You may use another way to get list of employees:

private static final RowMapper<Employee> ROW_MAPPER = BeanPropertyRowMapper.newInstance(Employee.class);

String sql = "SELECT emp_id,name,role FROM employees";
List<Employee> employees = jdbcTemplate.query(sql, ROW_MAPPER);

or

List<Employee> employees = jdbcTemplate.queryForList(sql, Employee.class);

answered Nov 07 '18 at 13:43

Kirill Shiryaev

339
2
8

1

thank you; your version of getting the list looks much more simple and straightforward. I will use it next time for my code! – DP Park Nov 07 '18 at 13:48

score 0 · Accepted Answer · answered Nov 07 '18 at 13:46

Answer by Original Poster:

It was a simple syntax error. As the commentators pointed out, I had been inserting into the wrong table... my syntax in my sql queries were wrong.

Previously, I was inserting into 'employees,' but I should have been inserting into 'employee.'

The queryForList() that returns a List containing Map, however, still returns null. I commented that out in the post above. I don't know what's wrong with that (I haven't tried using List> before). But the issue is solved.

Simple jdbcTemplate.query() is supposed to return a list, but isn't

2 Answers2