After more than three months, I finally remember that I still have a blog. Actually, I don't forget it. I just don't have time to write a blog when I'm busy at work. So I'll make time tonight to record a problem I encountered in my last job and share it with my garden friends so as to avoid going into a hole.

Last week, JdbcTemplate executed a select * from table where id in (?,??) and name =? SQL in my work. Everyone knows what this SQL means, so I have to assign values to these'?', no problem.But an exception was reported to me during execution: java.sql.SQLException: No value specified for parameter 3 * Nani?That's a little strange. What do you mean?Because the jdbcTemplate object does not support both question marks in parentheses and question marks outside parentheses, what does it mean?That is, where id in (?) is correct where name =? Is also correct, but where id in (?) and name =? Is not supported; you need to replace the jdbcTemplate object with NamedParameterJdbcTemplate, so I'll paste the code below, create a SpringBoot project, import the dependencies of the Web, MySQL, Jdbc, Lombok, and then we open itStart code:

Let's first look at the data in the database:

Here are the table-building statements, so comrades in need can copy the past:

CREATE TABLE `t_book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `isbn` varchar(255) DEFAULT NULL,
  `publish` varchar(255) DEFAULT NULL,
  `publisher` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

The code for the RowMapper implementation class of the entity class and the table mapping is not pasted, just build one according to the table field, skip this step

The following is the code for controller s and dao s:

@RestController
@RequestMapping("/api/v1/book")
public class BookRestController {

    @Resource
    private ObjectMapper jsonMapper;

    // This is omitted for convenience service Layer, directly adds dao Injected in
    @Resource
    private BookDAO bookDAO;

    @GetMapping("/list")
    public JsonNode list(){
        ObjectNode respJson = jsonMapper.createObjectNode();
        List<Integer> idList = Arrays.asList(1, 2, 3);
        String name = "mybatis From Beginning to Proficiency";
        List<TBook> bookList = bookDAO.selectByDynamicParams(idList, name);
        return respJson.putPOJO("data",bookList);
    }
}

@Component("bookDAO")
public class BookDAOImpl implements BookDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<TBook> selectByDynamicParams(List<Integer> idList, String name) {
        StringBuffer sql = new StringBuffer();
        sql.append("select * from t_book where id in ( ");
        // Assembly?
        List<String> stringList = new ArrayList<>();
        for (int i = 0;i < idList.size();i++){
            stringList.add("?");
        }
        sql.append(String.join(",",stringList));
        sql.append(" ) and name = ?");

        System.err.println(sql.toString());

        return jdbcTemplate.query(sql.toString(),new BookMapper(),idList,name);

    }
}

Well, execute one, and take a look at the results. Visit localhost:8080/api/v1/book/list, and the console guarantees the following errors:

You can see that there is no problem with the SQL statement, so let's try a different way of writing it, using NamedParameter JdbcTemplate. Because spring loads jdbcTemplate by default, the NamedParameter JdbcTemplate object needs to be configured by itself, either by creating a configuration class or by starting a class configuration directly. Starting a class is actually a configuration class.Line configuration as follows:

// From the container dataSource This object is injected in
@Autowired
private DataSource dataSource;

@Bean
public NamedParameterJdbcTemplate namedParameterJdbcTemplate(){
    // To construct NamedParameterJdbcTemplate Object needs dependency dataSource
    return new NamedParameterJdbcTemplate(dataSource);
}

The dao layer injects the NamedParameterJdbcTemplate object in and executes SQL:

@Component("bookDAO")
public class BookDAOImpl implements BookDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public List<TBook> selectByDynamicParams(List<Integer> idList, String name) {
        String sql = "select * from t_book where id in (:idList) and name = :bookName";
        // In a collection key Be sure to work with sql The parameter variables are the same as those defined in the, where the parameters in the set do not need to be handled manually, but simply pass in the set as a parameter object
        Map<String,Object> params = new HashMap<>(2);
        params.put("idList",idList);
        params.put("bookName",name);
        return namedParameterJdbcTemplate.query(sql,params,new BookMapper());
    }
}

Take another look at the results this time:

This time, the execution was successful and the console did not make a mistake. It is very comfortable. I remember that when I used native JDBC to query, I used sqlserver database and wrote a page-by-page SQL. There were also question marks at that time. Unexpectedly, spring's JDBC is as pitted as native jdbc. There is no end to learning about technology.

The revolution has not succeeded yet, comrades still need to work hard!