· 8 years ago · Jul 06, 2018, 10:30 AM
1CREATE TABLE SINGER (
2 ID INT NOT NULL AUTO_INCREMENT,
3 FIRST_NAME VARCHAR(60) NOT NULL,
4 LAST_NAME VARCHAR(60) NOT NULL,
5 BIRTH_DATE DATE,
6
7 CONSTRAINT UQ_SINGER UNIQUE (FIRST_NAME, LAST_NAME),
8 PRIMARY KEY (ID)
9);
10
11CREATE TABLE ALBUM(
12 ID INT NOT NULL AUTO_INCREMENT,
13 SINGER_ID INT,
14 TITLE VARCHAR(100) NOT NULL,
15 RELEASE_DATE DATE,
16
17 CONSTRAINT UQ_ALBUM UNIQUE (TITLE),
18 CONSTRAINT FK_ALBUM FOREIGN KEY (SINGER_ID) REFERENCES SINGER (ID),
19 PRIMARY KEY (ID)
20);
21
22INSERT INTO SINGER (FIRST_NAME, LAST_NAME, BIRTH_DATE) VALUES ('John', 'Mayer', '1977-10-16');
23INSERT INTO SINGER (FIRST_NAME, LAST_NAME, BIRTH_DATE) VALUES ('Eric', 'Clapton', '1945-03-30');
24INSERT INTO SINGER (FIRST_NAME, LAST_NAME, BIRTH_DATE) VALUES ('Jorn', 'Butler', '1975-04-01');
25
26INSERT INTO ALBUM (SINGER_ID, TITLE, RELEASE_DATE) VALUES (1, 'The Search For Everything', '2017-01-20');
27INSERT INTO ALBUM (SINGER_ID, TITLE, RELEASE_DATE) VALUES (1, 'Battle Studies', '2009-11-17');
28INSERT INTO ALBUM (SINGER_ID, TITLE, RELEASE_DATE) VALUES (2, 'From The Cradle', '1994-09-13');
29
30@Bean
31public DataSource dataSource() {
32 try {
33 EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
34 return builder.setType(EmbeddedDatabaseType.H2)
35 .addScripts("db/schema.sql", "db/data.sql")
36 .build();
37 } catch (Exception ex) {
38 log.error("Cannot create DataSource", ex);
39 return null;
40 }
41}
42
43Hibernate:
44
45 drop table album if exists
46Hibernate:
47
48 drop table singer if exists
49Hibernate:
50
51 create table album (
52 ID bigint generated by default as identity,
53 RELEASE_DATE date,
54 TITLE varchar(255) not null,
55 SINGER_ID bigint,
56 primary key (ID)
57 )
58Hibernate:
59
60 create table singer (
61 ID bigint generated by default as identity,
62 BIRTH_DATE date,
63 FIRST_NAME varchar(255) not null,
64 LAST_NAME varchar(255) not null,
65 primary key (ID)
66 )
67Hibernate:
68
69 alter table album
70 add constraint UK_bx46927jc5a08o95nc83a2vol unique (TITLE)
71Hibernate:
72
73 alter table singer
74 add constraint UK_94vyhfkt7d0wlwu5bs2yn576g unique (FIRST_NAME)
75Hibernate:
76
77 alter table singer
78 add constraint UK_fki20hi2rpq7ij7wr83vg0nkt unique (LAST_NAME)
79Hibernate:
80
81 alter table album
82 add constraint FKsu76te7e01esmhbxtsudsdnql
83 foreign key (SINGER_ID)
84 references singer
85Hibernate:
86 /* Singer.findAll */ select
87 singer0_.ID as ID1_1_,
88 singer0_.BIRTH_DATE as BIRTH_DA2_1_,
89 singer0_.FIRST_NAME as FIRST_NA3_1_,
90 singer0_.LAST_NAME as LAST_NAM4_1_
91 from
92 singer singer0_
93Hibernate:
94
95 drop table album if exists
96Hibernate:
97
98 drop table singer if exists
99
100Hibernate:
101
102 drop table album if exists
103Hibernate:
104
105 drop table singer if exists
106Hibernate:
107
108 create table album (
109 ID bigint generated by default as identity,
110 RELEASE_DATE date,
111 TITLE varchar(255) not null,
112 SINGER_ID bigint,
113 primary key (ID)
114 )
115Hibernate:
116
117 create table singer (
118 ID bigint generated by default as identity,
119 BIRTH_DATE date,
120 FIRST_NAME varchar(255) not null,
121 LAST_NAME varchar(255) not null,
122 primary key (ID)
123 )
124Hibernate:
125
126 alter table album
127 add constraint UK_bx46927jc5a08o95nc83a2vol unique (TITLE)
128Hibernate:
129
130 alter table singer
131 add constraint UK_94vyhfkt7d0wlwu5bs2yn576g unique (FIRST_NAME)
132Hibernate:
133
134 alter table singer
135 add constraint UK_fki20hi2rpq7ij7wr83vg0nkt unique (LAST_NAME)
136Hibernate:
137
138 alter table album
139 add constraint FKsu76te7e01esmhbxtsudsdnql
140 foreign key (SINGER_ID)
141 references singer
142Hibernate:
143 /* insert ua.savenkodenys.db.entity.Singer
144 */ insert
145 into
146 singer
147 (ID, BIRTH_DATE, FIRST_NAME, LAST_NAME)
148 values
149 (null, ?, ?, ?)
150Hibernate:
151 /* insert ua.savenkodenys.db.entity.Album
152 */ insert
153 into
154 album
155 (ID, RELEASE_DATE, SINGER_ID, TITLE)
156 values
157 (null, ?, ?, ?)
158Hibernate:
159 /* insert ua.savenkodenys.db.entity.Album
160 */ insert
161 into
162 album
163 (ID, RELEASE_DATE, SINGER_ID, TITLE)
164 values
165 (null, ?, ?, ?)
166Hibernate:
167 /* Singer.findAll */ select
168 singer0_.ID as ID1_1_,
169 singer0_.BIRTH_DATE as BIRTH_DA2_1_,
170 singer0_.FIRST_NAME as FIRST_NA3_1_,
171 singer0_.LAST_NAME as LAST_NAM4_1_
172 from
173 singer singer0_
174Hibernate:
175 select
176 albums0_.SINGER_ID as SINGER_I4_0_0_,
177 albums0_.ID as ID1_0_0_,
178 albums0_.ID as ID1_0_1_,
179 albums0_.RELEASE_DATE as RELEASE_2_0_1_,
180 albums0_.SINGER_ID as SINGER_I4_0_1_,
181 albums0_.TITLE as TITLE3_0_1_
182 from
183 album albums0_
184 where
185 albums0_.SINGER_ID=?
186INFO [main] ua.savenkodenys.db.orm.ORMSingerRepositoryImplTest:91 - Singer(id=1, firstName=BB, lastName=King, birthDate=1940-09-16, albums=[])
187Hibernate:
188
189 drop table album if exists
190Hibernate:
191
192 drop table singer if exists
193
194INFO [main] org.springframework.jdbc.datasource.init.ScriptUtils:510 - Executed SQL script from class path resource [db/schema.sql] in 47 ms.
195INFO [main] org.springframework.jdbc.datasource.init.ScriptUtils:444 - Executing SQL script from class path resource [db/data.sql]
196DEBUG [main] org.springframework.jdbc.datasource.init.ScriptUtils:476 - 1 returned as update count for SQL: INSERT INTO SINGER (FIRST_NAME, LAST_NAME, BIRTH_DATE) VALUES ('John', 'Mayer', '1977-10-16')
197DEBUG [main] org.springframework.jdbc.datasource.init.ScriptUtils:476 - 1 returned as update count for SQL: INSERT INTO SINGER (FIRST_NAME, LAST_NAME, BIRTH_DATE) VALUES ('Eric', 'Clapton', '1945-03-30')
198DEBUG [main] org.springframework.jdbc.datasource.init.ScriptUtils:476 - 1 returned as update count for SQL: INSERT INTO SINGER (FIRST_NAME, LAST_NAME, BIRTH_DATE) VALUES ('Jorn', 'Butler', '1975-04-01')
199DEBUG [main] org.springframework.jdbc.datasource.init.ScriptUtils:476 - 1 returned as update count for SQL: INSERT INTO ALBUM (SINGER_ID, TITLE, RELEASE_DATE) VALUES (1, 'The Search For Everything', '2017-01-20')
200DEBUG [main] org.springframework.jdbc.datasource.init.ScriptUtils:476 - 1 returned as update count for SQL: INSERT INTO ALBUM (SINGER_ID, TITLE, RELEASE_DATE) VALUES (1, 'Battle Studies', '2009-11-17')
201DEBUG [main] org.springframework.jdbc.datasource.init.ScriptUtils:476 - 1 returned as update count for SQL: INSERT INTO ALBUM (SINGER_ID, TITLE, RELEASE_DATE) VALUES (2, 'From The Cradle', '1994-09-13')
202INFO [main] org.springframework.jdbc.datasource.init.ScriptUtils:510 - Executed SQL script from class path resource [db/data.sql] in 16 ms.
203DEBUG [main] org.springframework.jdbc.datasource.DataSourceUtils:340 - Returning JDBC Connection to DataSource