Creating and Populating a Database With Apache ANT

I recreated a very useful ANT script I originally created 15 or 16 years ago. The ANT script containing the database creation commands can be versioned making sharing, reuse and modification simple and easy.

Preliminary Setup

1.) install Apache ANT
   a.) download the zip file
   b.) open the zip and copy the contents to the clipboard
   c.) paste the content of the clipboard in a folder
   d.) create an ANT_HOME environment variable and give it the value of the path of the location of the ANT files
   e.) edit the PATH environment variable and add ANT_HOME to it
2.) download the H2 DBMS jar file and place it in the lib folder
3.) write the sql tasks in the ANT build.xml file.

Here is the build.xml file that contains the SQL commands that ANT will run:

C:\Users\javap\dev\src\java\h2Db>ant -v sqlcreate
Apache Ant(TM) version 1.10.10 compiled on April 12 2021
Trying the default build file: build.xml
Buildfile: C:\Users\javap\dev\src\java\h2Db\build.xml
Detected Java version: 11 in: C:\Program Files\Java\jdk-11.0.1
Detected OS: Windows 10
parsing buildfile C:\Users\javap\dev\src\java\h2Db\build.xml with URI = file:/C:/Users/javap/dev/src/java/h2Db/build.xml
Project base dir set to: C:\Users\javap\dev\src\java\h2Db
parsing buildfile jar:file:/C:/Users/javap/dev/bin/apache-ant/lib/ant.jar!/org/apache/tools/ant/antlib.xml with URI = 
jar:file:/C:/Users/javap/dev
/bin/apache-ant/lib/ant.jar!/org/apache/tools/ant/antlib.xml from a zip file
Build sequence for target(s) `sqlcreate' is [sqlcreate]
Complete build sequence is [sqlcreate, sqlpopulate, echo, sqlschema, sqldrop, sqlselect, ]

sqlcreate:
      [sql] connecting to jdbc:h2:~/h2db
      [sql] Loading org.h2.Driver using AntClassLoader with classpath C:\users\javap\dev\lib\h2-1.4.200.jar
      [sql] Executing commands
      [sql] SQL:  create table Project (ID INT AUTO_INCREMENT, name varchar(30))
      [sql] 0 rows affected
      [sql] 0 rows affected
      [sql] SQL:  create index Project_idx on Project (name)
      [sql] 0 rows affected
      [sql] 0 rows affected
      [sql] SQL:  create table Programmer (ID INT AUTO_INCREMENT, full_name varchar(100))
      [sql] 0 rows affected
      [sql] 0 rows affected
      [sql] SQL:  create index Programmer_idx on Programmer (full_name)
      [sql] 0 rows affected
      [sql] 0 rows affected
      [sql] SQL:  create table ProjectProgrammer (project_id INTEGER, programmer_id INTEGER)
      [sql] 0 rows affected
      [sql] 0 rows affected
      [sql] SQL:  create view ProjProg as select Project.name, Programmer.full_name from Programmer 
      join ProjectProgrammer on Programmer.id = ProjectProgrammer.programmer_id 
      join Project on ProjectProgrammer.project_id = Project.id
      [sql] 0 rows affected
      [sql] 0 rows affected
      [sql] Committing transaction
      [sql] 6 of 6 SQL statements executed successfully

BUILD SUCCESSFUL
Total time: 0 seconds

C:\Users\javap\dev\src\java\h2Db>ant -v sqlpopulate
Apache Ant(TM) version 1.10.10 compiled on April 12 2021
Trying the default build file: build.xml
Buildfile: C:\Users\javap\dev\src\java\h2Db\build.xml
Detected Java version: 11 in: C:\Program Files\Java\jdk-11.0.1
Detected OS: Windows 10
parsing buildfile C:\Users\javap\dev\src\java\h2Db\build.xml with URI = file:/C:/Users/javap/dev/src/java/h2Db/build.xml
Project base dir set to: C:\Users\javap\dev\src\java\h2Db
parsing buildfile jar:file:/C:/Users/javap/dev/bin/apache-ant/lib/ant.jar!/org/apache/tools/ant/antlib.xml with 
URI = jar:file:/C:/Users/javap/dev
/bin/apache-ant/lib/ant.jar!/org/apache/tools/ant/antlib.xml from a zip file
Build sequence for target(s) `sqlpopulate' is [sqlpopulate]
Complete build sequence is [sqlpopulate, sqlcreate, echo, sqlschema, sqldrop, sqlselect, ]

sqlpopulate:
      [sql] connecting to jdbc:h2:~/h2db
      [sql] Loading org.h2.Driver using AntClassLoader with classpath C:\users\javap\dev\lib\h2-1.4.200.jar
      [sql] Executing commands
      [sql] SQL:  insert into Programmer (full_name) values('Greg Helton')
      [sql] 1 rows affected
      [sql] 1 rows affected
      [sql] SQL:  insert into Programmer (full_name) values('Bill Gates')
      [sql] 1 rows affected
      [sql] 1 rows affected
      [sql] SQL:  insert into Programmer (full_name) values('Steve Jobs')
      [sql] 1 rows affected
      [sql] 1 rows affected
      [sql] SQL:  insert into Project (name) values('Workplace assignments')
      [sql] 1 rows affected
      [sql] 1 rows affected
      [sql] SQL:  insert into ProjectProgrammer values(1,1)
      [sql] 1 rows affected
      [sql] 1 rows affected
      [sql] SQL:  insert into ProjectProgrammer values(1,2)
      [sql] 1 rows affected
      [sql] 1 rows affected
      [sql] SQL:  insert into ProjectProgrammer values(1,3)
      [sql] 1 rows affected
      [sql] 1 rows affected
      [sql] Committing transaction
      [sql] 7 of 7 SQL statements executed successfully

BUILD SUCCESSFUL
Total time: 0 seconds

C:\Users\javap\dev\src\java\h2Db>ant -v sqlselect
Apache Ant(TM) version 1.10.10 compiled on April 12 2021
Trying the default build file: build.xml
Buildfile: C:\Users\javap\dev\src\java\h2Db\build.xml
Detected Java version: 11 in: C:\Program Files\Java\jdk-11.0.1
Detected OS: Windows 10
parsing buildfile C:\Users\javap\dev\src\java\h2Db\build.xml with URI = file:/C:/Users/javap/dev/src/java/h2Db/build.xml
Project base dir set to: C:\Users\javap\dev\src\java\h2Db
parsing buildfile jar:file:/C:/Users/javap/dev/bin/apache-ant/lib/ant.jar!/org/apache/tools/ant/antlib.xml with 
URI = jar:file:/C:/Users/javap/dev
/bin/apache-ant/lib/ant.jar!/org/apache/tools/ant/antlib.xml from a zip file
Build sequence for target(s) `sqlselect' is [sqlselect]
Complete build sequence is [sqlselect, sqlpopulate, sqlcreate, echo, sqlschema, sqldrop, ]

sqlselect:
      [sql] connecting to jdbc:h2:~/h2db
      [sql] Loading org.h2.Driver using AntClassLoader with classpath C:\users\javap\dev\lib\h2-1.4.200.jar
      [sql] Executing commands
      [sql] SQL:  select * from Project
      [sql] Processing new result set.
      [sql] ID,NAME
      [sql] 1,Workplace assignments
      [sql]
      [sql] 0 rows affected
      [sql] 0 rows affected
      [sql] SQL:   select * from Programmer
      [sql] Processing new result set.
      [sql] ID,FULL_NAME
      [sql] 1,Greg Helton
      [sql] 2,Bill Gates
      [sql] 3,Steve Jobs
      [sql]
      [sql] 0 rows affected
      [sql] 0 rows affected
      [sql] SQL:   select * from Programmer join ProjectProgrammer on Programmer.id = ProjectProgrammer.programmer_id 
      join Project on ProjectProgrammer.project_id = Project.id
      [sql] Processing new result set.
      [sql] ID,FULL_NAME,PROJECT_ID,PROGRAMMER_ID,ID,NAME
      [sql] 1,Greg Helton,1,1,1,Workplace assignments
      [sql] 2,Bill Gates,1,2,1,Workplace assignments
      [sql] 3,Steve Jobs,1,3,1,Workplace assignments
      [sql]
      [sql] 0 rows affected
      [sql] 0 rows affected
      [sql] SQL:   select * from ProjProg
      [sql] Processing new result set.
      [sql] NAME,FULL_NAME
      [sql] Workplace assignments,Greg Helton
      [sql] Workplace assignments,Bill Gates
      [sql] Workplace assignments,Steve Jobs
      [sql]
      [sql] 0 rows affected
      [sql] 0 rows affected
      [sql] Committing transaction
      [sql] 4 of 4 SQL statements executed successfully

BUILD SUCCESSFUL
Total time: 0 seconds

C:\Users\javap\dev\src\java\h2Db>