4

I want to insert the default value from database when my field is null. I use an Oracle Database.

CREATE TABLE "EMPLOYEE" 
   ("COL1" VARCHAR2(800) NOT NULL ENABLE, 
    "COL2" VARCHAR2(100) DEFAULT NOT NULL 'toto', 
    CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY ("COL1")

with a simple SQL request, we can write:

insert into EMPLOYEE(COL1,COL2) values ('titi', default)

How can i do this with annotations MyBatis in Spring? I must create an HandlerType?

duffy356
  • 3,678
  • 3
  • 32
  • 47
lecogiteur
  • 307
  • 1
  • 7
  • 16
  • Yeah. it is better if like spring could specify default value, e.g. `@Value("${mongodb.url:127.0.0.1}")` – zhuguowei Jul 26 '16 at 07:51

2 Answers2

3

In mapper XML, build dynamically the SQL (add the col2 column and value when not null):

insert into employee (col1<if test="col2 != null">, col2</if>) 
values (#{COL1}<if test="col2 != null">, #{col2}</if>)

EDIT: since value in annotation must be constant, I used to think dynamic SQl was not possible in annotation, but there is a trick I have found here: How to use dynamic SQL query in MyBatis with annotation(how to use selectProvider)? and checked it myself.

To use dynamic SQL this into an annotation, surround it with "script" tags:

@Insert("<script>insert into employee (col1<if test='col2 != null'>, col2</if>) 
    values (#{COL1}<if test='col2 != null'>, #{col2}</if>)</script>")

In tests, just escape double quotes " or replace them with simple quotes '

Community
  • 1
  • 1
blackwizard
  • 2,034
  • 1
  • 9
  • 21
  • 1
    Please read the question clearly, the OP asks the solution using annotations while you have posted for XML. – Lucky Oct 27 '16 at 12:32
2

It should work if you omit the COL2 in your colum definition of the insert statement. Because the DB recognizes, that there is no value for the new row and it will apply the default value from the create table statement.

Have you tried something like this?

public interface EmployeeDAO {
   String INSERT = "insert into employee (col1) values (#{COL1})";

   @Insert(INSERT)
   public int insertDefault(PersonDO p) throws Exception;
}
duffy356
  • 3,678
  • 3
  • 32
  • 47
  • Hello. I'm agree with you. But what do you do if my column COL2 is not null. How do you do your insertion? In fact the solution must manage the two cases: insert value of COL2 (if not null) or set the default value of COL2 (if null). – lecogiteur Jun 24 '15 at 08:09
  • if COL2 is not null, you would have to call an insert statement, which uses COL2 and the value, or you need to create a typehandler. In the typehandler you handle the default value, if the appropriate value in the *DO is null. – duffy356 Jun 24 '15 at 08:21