mybatisspring-mybatismybatis-generatormybatis-mappermybatis-sql

Mybatis Insert PK manually


I am trying to single insert data into table with assigned PK. Manually assiging PK.

XML file

<insert id = "insertStd" parameterType = "com.org.springboot.dao.StudentEntity" useGeneratedKeys = "false" keyProperty = "insertStd.id", keyColumn = "id">
      INSERT INTO STUDENT (ID, NAME, BRANCH, PERCENTAGE, PHONE, EMAIL ) 
      VALUES (ID=#{insertStd.id}, NAME=#{insertStd.name}, BRANCH=#{insertStd.branch}, PERCENTAGE=#{insertStd.percentage}, PHONE=#{insertStd.phone}, EMAIL =#{insertStd.email});

   </insert>

Service call method

public boolean saveStudent(Student student){
    LOGGER.info("Student object save");
    int savedId= studentMapper.insertStd(student);
}

Log file

org.springframework.jdbc.badsqlgrammarexception
### Error updating database Causes: cause org.postgresql.util.psqlexception error column id does not exist
HINT: There is a column named "id" in the table "student" but it can't be referenced from this part of the query. 
Position 200
### Error may exist in file [c:\.....\StudentMapper.xml]
### Error may involve in com.org.springboot.dao.StudentMapper.insertStd-InLine
### The error occurred while setting parameters
### SQL INSERT INTO STUDENT (ID, NAME, BRANCH, PERCENTAGE, PHONE, EMAIL ) 
  VALUES (ID=?, NAME=?,BRANCH=?, PERCENTAGE=?, PHONE=?, EMAIL=?);
### cause org.postgresql.util.psqlexception ERROR column "id" doesn't exist. //It did worked with JPA id assigned manually.
### There is a column named "ID" in the table "STUDENT", Bbut it cannot be referenced from the part of the query. 

Solution

  • The INSERT statement of malformed. The VALUES clause should not include the column names.

    Also, since there's no primary auto-generation, you can remove all the other attributes. Just leave the mapper id.

    Note: if you want to manually assign the PK value, you need to make sure the table does not have a GENERATED ALWAYS clause for the column. If this is the case, the table will ignore the value you are providing and will use its own rules to generate the PK.

    Use:

    <insert id="insertStd">
      INSERT INTO STUDENT (ID, NAME, BRANCH, PERCENTAGE, PHONE, EMAIL)
      VALUES (
        #{insertStd.id}, #{insertStd.name}, #{insertStd.branch},
        #{insertStd.percentage}, #{insertStd.phone}, #{insertStd.email}
      );
    </insert>
    

    Your error is easily reproduceable:

    create table t (a int, b varchar(10));
    
    insert into t (a, b) values (123, 'ABC'); -- succeeds
            
    insert into t (a, b) values (a=123, b='ABC'); -- fails!
    

    error: column "a" does not exist

    See the Fiddle.