Monday, October 26, 2009

Running SQL Statements From ANT Tasks 

<project name="projectmgmt" basedir="." default="sqlshow">

 <property name="mysql.jar" 
     value="C:\DOCUME~1\NBKT8F4\lib\mysql-connector-java-5.1.10\mysql-connector-java-5.1.10-bin.jar"/>
 <property name="url" value="jdbc:mysql://localhost:3306/"/>
 <property name="driver" value="com.mysql.jdbc.Driver"/>
 <property name="userid" value="root"/>
 <property name="password" value="root"/>

<target name="sqldrop">
 <sql
  driver="${driver}"
  url="${url}"
  userid="${userid}"
  password="${password}"
  print="yes"
  classpath="${mysql.jar}">
   drop database ${ant.project.name};
 </sql>
</target>

<target name="sqlcreatedb" depends="sqldrop">
 <sql
  driver="${driver}"
  url="${url}"
  userid="${userid}"
  password="${password}"
  print="yes"
  classpath="${mysql.jar}">
   create database projectmgmt;
  </sql>
</target>

<target name="sqlcreatetables" depends="sqlcreatedb">
 <sql
  driver="${driver}"
  url="${url}${ant.project.name}"
  userid="${userid}"
  password="${password}"
  print="yes"
  classpath="${mysql.jar}">
   create table projects (id INTEGER AUTO_INCREMENT PRIMARY KEY, name varchar(100));
   create table programmers (id INTEGER AUTO_INCREMENT PRIMARY KEY, name varchar(100));
   create table projectsprogrammers (project_id INTEGER, programmer_id INTEGER);
   alter table projectsprogrammers add constraint project_fkey foreign key 
        (project_id) references projects (id);
   alter table projectsprogrammers add constraint programmer_fkey foreign key 
        (programmer_id) references programmers (id);
 </sql>
</target>

<target name="sqlshow" depends="sqlcreatetables">
 <sql
  driver="${driver}"
  url="${url}${ant.project.name}"
  userid="${userid}"
  password="${password}"
  print="yes"
  classpath="${mysql.jar}">
   insert into programmers (name) values('Greg Helton');
   insert into programmers (name) values('Bob Martin');
   insert into projects (name) values('Making Homes Affordable');
   insert into projectsprogrammers (select programmers.id, projects.id 
        from programmers, projects where programmers.name='Greg Helton' and 
        projects.name='Making Homes Affordable');
   select projects.name, programmers.name from projects 
        join projectsprogrammers on projects.id = projectsprogrammers.project_id 
        join programmers on programmers.id = projectsprogrammers.programmer_id;
 </sql>
</target>
</project>

at