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.
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.