CODEWITHSUNDEEP
javaoracle-databasemybatis
canadiancreed
canadiancreed

Reputation: 1984

Performing a sub insert statement within an insert statement using mybatis

So I have an object that I'm trying to insert into a database that has the following structure

int id
String name
Array tags

and I'd want to insert the first two columns into the following table

CREATE TABLE foo (
id number(20) PRIMARY KEY,
name varchar2(50) NOT NULL
);

and the array into this table

CREATE TABLE fooTags (
id number(20) PRIMARY KEY,
fooId number(20), //foreign key to foo. I don't know what the sql is for that.
tagName varchar2(50)
);

How would performing a sub insert that would take the id created by the initial insert work? I'd assume a SELECT is needed, but I'm unsure as to how it would be ordered to get the information needed inserted into the proper areas for each object.

Upvotes: 0

Views: 859

Answers (2)

Gabriele Coletta
Gabriele Coletta

Reputation: 108

My solution: two insert query, the first for parent object (foo table), and the second for his tags (fooTags table):

<insert id="fooInsert" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO foo (name) VALUES (#{name})
</insert>

<insert id="fooTagsInsert">
    INSERT INTO fooTags ("fooId", "tagName") VALUES
    <foreach item="tag" collection="tags" separator=",">
        (#{id}, #{tag})
    </foreach>
</insert>

The attributes "useGeneratedKeys", "keyProperty" and "keyColumn" are used to reload newly generated keys from the database, if JDBC driver supports the getGeneratedKeys function. Alternatively we must reload the id using a select query. More info: http://www.mybatis.org/mybatis-3/sqlmap-xml.html#insert_update_and_delete

The tags insert use "foreach" to iterate through tag names (in this case I used a String array, but they could be objects). The "inner" insert refers to the "id" from the "foo" object and "tag", that is the iterating String. In case of an object, we can access inner fields with "tag.", i.e. "tag.name".

Usage in Java code:

Foo foo = new Foo();
foo.setName("James");
foo.setTags(new String[] {"one", "two", "three"});

fooMapper.fooInsert(foo);
fooMapper.fooTagsInsert(foo);

Tables definitions (tested with PostgreSQL):

CREATE TABLE public.foo (
    id numeric NOT NULL DEFAULT nextval('seq_foo_id'::regclass),
    "name" varchar NULL,
    CONSTRAINT foo_pk PRIMARY KEY (id)
)

CREATE TABLE public.footags (
    id varchar NOT NULL DEFAULT nextval('seq_foo_id'::regclass),
    "fooId" numeric NULL,
    "tagName" varchar NULL,
    CONSTRAINT footags_pk PRIMARY KEY (id),
    CONSTRAINT footags_foo_fk FOREIGN KEY ("fooId") REFERENCES public.foo(id)
)

Upvotes: 1

mehmet sahin
mehmet sahin

Reputation: 812

I wrote 2 procedure;

If you can learn your seq names for ids ;

create or replace procedure FOO_INSERT(foo_name in varchar2, FooTags_tagName in varchar2 )
is
foo_seq_val number;
footag_seq_val number;
begin 
select foo_seq.nextval into foo_seq_val from dual;    
insert into foo(id,name) values (foo_seq_val, foo_name); 
select footag_seq.nextval into footag_seq_val from dual;
insert into footags (id,fooid,tagName) values(footag_seq_val,foo_seq_val,FooTags_tagName);  
commit;
end;

If you can not learn your seq names for ids ;

create or replace procedure FOO_INSERT_T(foo_name in varchar2, FooTags_tagName in varchar2 )
is
foo_seq_val number;
begin 
insert into foo_T(name) values (foo_name);
select id into  foo_seq_val from FOO_T where name =foo_name;
insert into footags_T (fooid,tagName) values(foo_seq_val,FooTags_tagName);
commit;
end;

If you pass ids ;

insert into foo (id, name) values (123,'foo_name');
insert into footags  (id,fooid,tagname) select 444,id, 'tag_name' from foo ;
commit;

For second procedure I assume your foo_T.name values are unique or your other values for each row make your id unique. You can put in order end of your select with and ... and ..

You can see one COMMIT each method. Because if there is an error, transaction rollback all your inserts for foo_table and fooTags_table. This is accuracy.

Upvotes: 1

Related Questions