SQL With Groovy

Transcript

1. Connecting to a Database with Groovy & JDBC
2. JDBC import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCExample1 { public static void main(String[] args) { Connection con = null; Statement stmt = null; ResultSet rs = null; try{ Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost:3306/words", "words", "words"); stmt = con.createStatement(); rs = stmt.executeQuery("select * from word"); while (rs.next()) { System.out.println("word id: " + rs.getLong(1) + " spelling: " + rs.getString(2) + " part of speech: " + rs.getString(3)); } }catch(SQLException e){ e.printStackTrace(); }catch(ClassNotFoundException e){ e.printStackTrace(); }finally{ try{rs.close();}catch(Exception e){} try{stmt.close();}catch(Exception e){} try{con.close();}catch(Exception e){} } } }
3. JDBC try{ Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost: 3306/words", "words", "words"); stmt = con.createStatement(); rs = stmt.executeQuery("select * from word"); while (rs.next()) { System.out.println("word id: " + rs.getLong(1) + " spelling: " + rs.getString(2) + " part of speech: " + rs.getString(3)); } }
4. JDBC catch(SQLException e){ e.printStackTrace(); }catch(ClassNotFoundException e){ e.printStackTrace(); }finally{ try{rs.close();}catch(Exception e){} try{stmt.close();}catch(Exception e){} try{con.close();}catch(Exception e){} }
5. Groovy SQL import groovy.sql.Sql class GroovySqlExample1{ static void main(String[] args) { def sql = Sql.newInstance("jdbc:mysql: //localhost:3306/words", "words", "words", "com.mysql.jdbc.Driver") sql.eachRow("select * from word"){ row -> println row.word_id + " " + row.spelling + " " + row.part_of_speech } } }
6. Groovy SQL - Create a Connection Sql connection = Sql.newInstance(url, user, password, driver) E.g. Sql.newInstance("jdbc:mysql://localhost: 3306/dbName", "aUser", "secretPassword", "com.mysql.jdbc.Driver")
7. Groovy Sql - Connection Pool Sql connection = new Sql(dataSource) Example of a DataSource Type: http://commons.apache.org/proper/commons- dbcp/apidocs/org/apache/commons/dbcp/Basic DataSource.html
8. Groovy SQL - Query eachRow("Select ...."){row-> //Do something } The fields will be properties of 'row'.
9. Groovy SQL - Query Named Parameters sql.eachRow('select * from PROJECT where name=:foo', [foo:'Gradle']) {row-> // process row } 'select * from PROJECT where name=:foo', [foo:'Gradle']
10. Groovy SQL - Insert Rows sql.executeInsert("Insert into ....")
11. Groovy SQL - Updates & Deletes sql.executeUpdate("Update ....") sql.executeDelete("Delete ....")
12. Reference API Documentation: http://groovy.codehaus.org/gapi/groovy/sql/Sql. html