How do I create an alter table statement with MyBatis? MySQLSyntaxErrorException: You have an error in your SQL syntax

I have the following

@Mapper
public interface StatMapper {
    @Update("alter table stats reorganize partition #{pFirst},#{pSecond} into ( "
            + "partition #{pSecond} values less than (#{dSecond}) "
            + ");")
    public void updateFirstPartition(@Param("pFirst")String pFirst, @Param("pSecond")String pSecond, @Param("dSecond")LocalDate dSecond);

It gives the following error

2019-09-30 21:58:23.067 DEBUG 13728 --- [ restartedMain] c.s.s.m.StatMapper.updateFirstPartition : ==> Preparing: alter table stats reorganize partition ?,? into ( partition ? values less than (?) );
2019-09-30 21:58:23.093 DEBUG 13728 --- [ restartedMain] c.s.s.m.StatMapper.updateFirstPartition : ==> Parameters: p20180901(String), p20181001(String), p20181001(String), 2018-10-01(Date)

Caused by: org.springframework.jdbc.BadSqlGrammarException: ### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''p20180901','p20181001' into ( partition 'p20181001' values less than ('2018-10-' at line 1

How can I issue this alter table statement using MyBatis?

The statement should look like this (p0 and p1 substituted by p20180901 and p20181001):

alter table stats reorganize partition p0,p1 into (
    partition p1 values less than ('2018-10-01')
);

Solution

${} is a text substitution and #{} is a placeholder in a java.sql.PreparedStatement (see the FAQ for the details). So, with your code, MyBatis generates a prepared statement as follows...

PreparedStatement ps = connection.prepareStatement(
  "alter table stats reorganize partition ?,? into (partition ? values less than (?))");

...and it fails because you cannot use a placeholder for a partition name.

The following should work.

@Update("alter table stats reorganize partition ${pFirst},${pSecond} into ( "
  + "partition ${pSecond} values less than (#{dSecond}) "
  + ")")