Helper classes database connection

This is a code example of iText PDF, discover more.

10th October 2015
admin-marketing

Switch code for this example

DatabaseConnection.java
  1. /*
  2.  * This class is part of the book "iText in Action - 2nd Edition"
  3.  * written by Bruno Lowagie (ISBN: 9781935182610)
  4.  * For more info, go to: http://itextpdf.com/examples/
  5.  * This example only works with the AGPL version of iText.
  6.  */
  7.  
  8. package com.lowagie.database;
  9.  
  10. import java.sql.Connection;
  11. import java.sql.PreparedStatement;
  12. import java.sql.SQLException;
  13. import java.sql.Statement;
  14.  
  15. /**
  16.  * This is a helper class to access the database
  17.  * that comes with the book samples.
  18.  */
  19.  
  20. public abstract class DatabaseConnection {
  21.  
  22.     /** our connection to the db. */
  23.     protected Connection connection;
  24.    
  25.     /**
  26.      * Closes the connection to the database.
  27.      */
  28.     public void close() throws SQLException {
  29.         connection.close();
  30.     }
  31.  
  32.     /**
  33.      * Creates a statement.
  34.      * @return    a statement
  35.      * @throws SQLException
  36.      */
  37.     public Statement createStatement() throws SQLException {
  38.         return connection.createStatement();
  39.     }
  40.  
  41.     /**
  42.      * Creates a prepated statement using a query.
  43.      * @param    query    the query that will be used to create
  44.      * a prepared statement.
  45.      * @return    a statement
  46.      * @throws SQLException
  47.      */
  48.     public PreparedStatement createPreparedStatement(String query)
  49.         throws SQLException {
  50.         return connection.prepareStatement(query);
  51.     }
  52.    
  53.     /**
  54.      * Performs an update in the database.
  55.      * @param    expression    an SQL expression
  56.      * (CREATE, DROP, INSERT, UPDATE)
  57.      */
  58.     public void update(String expression) throws SQLException {
  59.         Statement st = createStatement();
  60.         int i = st.executeUpdate(expression);
  61.         st.close();
  62.         if (i == -1) {
  63.             throw new SQLException("db error : " + expression);
  64.         }
  65.     }
  66. }
HsqldbConnection.java
  1. /*
  2.  * This class is part of the book "iText in Action - 2nd Edition"
  3.  * written by Bruno Lowagie (ISBN: 9781935182610)
  4.  * For more info, go to: http://itextpdf.com/examples/
  5.  * This example only works with the AGPL version of iText.
  6.  */
  7.  
  8. package com.lowagie.database;
  9.  
  10. import java.sql.DriverManager;
  11. import java.sql.SQLException;
  12. import java.sql.Statement;
  13.  
  14. /**
  15.  * This is a helper class to access an hsqldb database.
  16.  * The database scripts are supposed to be present in directory:
  17.  * resources/db
  18.  */
  19. public class HsqldbConnection extends DatabaseConnection {
  20.  
  21.     /**
  22.      * Creates the connection.
  23.      * @param db_file_name_prefix the database name,
  24.      * which is the prefix of the database file
  25.      * @throws SQLException
  26.      */
  27.     public HsqldbConnection(String db_file_name_prefix)
  28.         throws SQLException {
  29.         try {
  30.             Class.forName("org.hsqldb.jdbcDriver");
  31.         } catch (ClassNotFoundException e) {
  32.             throw new SQLException("HSQLDB database driver not found");
  33.         }
  34.         connection = DriverManager.getConnection(
  35.             "jdbc:hsqldb:resources/db/" + db_file_name_prefix, "SA", "");
  36.     }
  37.    
  38.     /**
  39.      * Shuts down the database and closes the connection.
  40.      */
  41.     public void close() throws SQLException {
  42.         Statement st = createStatement();
  43.         st.execute("SHUTDOWN");
  44.         super.close();
  45.     }
  46. }
CreateHsqldbTables.java
  1. /*
  2.  * This class is part of the book "iText in Action - 2nd Edition"
  3.  * written by Bruno Lowagie (ISBN: 9781935182610)
  4.  * For more info, go to: http://itextpdf.com/examples/
  5.  * This example only works with the AGPL version of iText.
  6.  */
  7.  
  8. package com.lowagie.database;
  9.  
  10. import java.io.BufferedReader;
  11. import java.io.FileReader;
  12. import java.io.IOException;
  13. import java.io.UnsupportedEncodingException;
  14. import java.sql.SQLException;
  15.  
  16.  
  17. /**
  18.  * Creates the hsqldatabase using SQL scripts containing
  19.  * CREATE statements with the table definitions, and
  20.  * INSERT statements with the data.
  21.  */
  22. public class CreateHsqldbTables {
  23.  
  24.     /**
  25.      * Imports a number of SQL scripts into an HSQLDB database.
  26.      * @param    args    no arguments needed
  27.      * @throws SQLException
  28.      * @throws IOException
  29.      * @throws UnsupportedEncodingException
  30.      */
  31.     public static void main(String[] args)
  32.         throws SQLException, UnsupportedEncodingException, IOException {
  33.         DatabaseConnection conn = new HsqldbConnection("filmfestival");
  34.         BufferedReader in;
  35.         String line;
  36.         conn.update("SET IGNORECASE TRUE");
  37.         in = new BufferedReader(
  38.                 new FileReader("resources/scripts/filmfestival_hsqldb.sql"));
  39.         while( (line = in.readLine()) != null) {
  40.             conn.update(line);
  41.         }
  42.         in = new BufferedReader(
  43.                 new FileReader("resources/scripts/filmfestival.sql"));
  44.         while( (line = in.readLine()) != null) {
  45.             conn.update(line);
  46.         }
  47.         conn.close();
  48.     }
  49. }
MySqlConnection.java
  1. /*
  2.  * This class is part of the book "iText in Action - 2nd Edition"
  3.  * written by Bruno Lowagie (ISBN: 9781935182610)
  4.  * For more info, go to: http://itextpdf.com/examples/
  5.  * This example only works with the AGPL version of iText.
  6.  */
  7.  
  8. package com.lowagie.database;
  9.  
  10. import java.io.FileInputStream;
  11. import java.io.IOException;
  12. import java.sql.DriverManager;
  13. import java.sql.SQLException;
  14. import java.util.Properties;
  15.  
  16. /**
  17.  * This is a helper class to access a mySql database.
  18.  * The username and password of the database are supposed to be present
  19.  * in a properties file mysql.props (in the working directory)
  20.  */
  21. public class MySqlConnection extends DatabaseConnection {
  22.  
  23.     /**
  24.      * Creates the connection.
  25.      * @param db_file_name_prefix the database name,
  26.      * which is the prefix of the database file
  27.      * @throws SQLException
  28.      */
  29.     public MySqlConnection(String database) throws SQLException {
  30.         try {
  31.             Class.forName("com.mysql.jdbc.Driver");
  32.         } catch (ClassNotFoundException e) {
  33.             throw new SQLException("mySql database driver not found");
  34.         }
  35.         Properties credentials = new Properties();
  36.         try {
  37.             credentials.load(new FileInputStream("mysql.props"));
  38.         } catch (IOException e) {
  39.             throw new SQLException("Can't read mysql.props.");
  40.         }
  41.         connection = DriverManager.getConnection(
  42.                 "jdbc:mysql://localhost/" + database,
  43.                 credentials.getProperty("username"),
  44.                 credentials.getProperty("password"));
  45.     }
  46.  
  47. }
AdoDB.cs
  1. /*
  2.  * ###########################################################################
  3.  * SQLite is fast, low overhead, and so easy to use...
  4.  * ###########################################################################
  5.  * System.Data.SQLite:
  6.  * http://sqlite.phxsoftware.com/
  7.  *
  8.  * [1] drop the System.Data.SQLite.dll into ~/bin
  9.  * [2] copy iTextInAction2Ed.db3 database file to ~/app_data
  10.  *
  11.  * database is READ-ONLY (under web context) unless configured otherwise
  12.  * ###########################################################################
  13.  * System.Data.SQLite is the ADO.NET 2.0/3.5 provider for the
  14.  * SQLite database engine:
  15.  * http://www.sqlite.org/
  16.  * ###########################################################################
  17.  *
  18.  * !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
  19.  * if you want to use a different provider see the
  20.  * README.txt file. the change requires minimumal effort.
  21.  * !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
  22. */
  23. using System;
  24. using System.Collections.Generic;
  25. using System.Data;
  26. using System.Data.Common;
  27. using System.IO;
  28. using System.Web.Configuration;
  29.  
  30. namespace kuujinbo.iTextInAction2Ed.Intro_1_2 {
  31.   public class AdoDB {
  32. // ===========================================================================
  33.     public static readonly string CS;
  34.     public static readonly DbProviderFactory Provider;
  35.     static AdoDB() {
  36.       CS = WebConfigurationManager.ConnectionStrings["iTextEx"] != null
  37. // web context        
  38.           ? WebConfigurationManager.ConnectionStrings["iTextEx"]
  39.               .ConnectionString
  40. /*
  41.  * command-line, change to use SQL Server/other ADO.NET data provider
  42.  */
  43.           : string.Format("Data Source={0};", new Uri(
  44.               new Uri(Utility.BaseDirectory),
  45.               "./app_data/iTextInAction2Ed.db3"
  46.             ).LocalPath
  47.           );
  48. /*
  49.  * ADO.NET data provider
  50.  * to use SQL Server ADO.NET data provider replace below with:
  51. : "System.Data.SqlClient"
  52.  *
  53. */          
  54.       Provider = DbProviderFactories.GetFactory("System.Data.SQLite");  
  55.     }
  56. // ---------------------------------------------------------------------------
  57.     /** SQL statement to get all the movies of the festival. */
  58.     public const String MOVIES =
  59. @"SELECT m.id, m.title, m.original_title, m.imdb, m.year, m.duration,
  60. e.year, c.name, c.keyword, c.color
  61. FROM film_movietitle m, festival_entry e, festival_category c
  62.  WHERE m.id = e.film_id AND e.category_id = c.id
  63. ORDER BY m.title";
  64. // ---------------------------------------------------------------------------
  65.     /** SQL statement to get the directors of a specific movie. */
  66.     public const String DIRECTORS =
  67. @"SELECT d.name, d.given_name
  68. FROM film_director d, film_movie_director md
  69.  WHERE md.film_id = @film_id AND md.director_id = d.id";
  70. // ---------------------------------------------------------------------------  
  71.     /** SQL statement to get the movies of a specific director. */
  72.     public const String MOVIEDIRECTORS =
  73. @"SELECT m.id, m.title, m.original_title, m.imdb, m.year, m.duration
  74. FROM film_movietitle m, film_movie_director md
  75.  WHERE md.director_id = @director_id AND md.film_id = m.id
  76. ORDER BY m.title";
  77. // ---------------------------------------------------------------------------
  78.     /** SQL statement to get the countries of a specific movie. */
  79.     public const String COUNTRIES =
  80. @"SELECT c.country
  81. FROM film_country c, film_movie_country mc
  82.  WHERE mc.film_id = @film_id AND mc.country_id = c.id";
  83. // ---------------------------------------------------------------------------  
  84.     /** SQL statement to get the movies from a specific country. */
  85.     public const String MOVIECOUNTRIES =
  86. @"SELECT m.id, m.title, m.original_title, m.imdb, m.year, m.duration
  87. FROM film_movietitle m, film_movie_country mc
  88.  WHERE mc.country_id = @country_id AND mc.film_id = m.id
  89. ORDER BY m.title";
  90. // ---------------------------------------------------------------------------
  91.     /** SQL statement to get all the days of the festival. */
  92.     public const String DAYS =
  93.         "SELECT DISTINCT day FROM festival_screening ORDER BY day";
  94. // ---------------------------------------------------------------------------        
  95.     /** SQL statament to get all the locations at the festival */
  96.     public const String LOCATIONS =
  97.         "SELECT DISTINCT location FROM festival_screening ORDER by location";
  98. // ---------------------------------------------------------------------------        
  99.     /** SQL statement to get screenings. */
  100.     public const String SCREENINGS =
  101. @"SELECT m.title, m.original_title, m.imdb, m.year, m.duration,
  102. s.day, s.time, s.location, s.press,
  103. e.year, c.name, c.keyword, c.color, m.id
  104. FROM festival_screening s, film_movietitle m,
  105. festival_entry e, festival_category c
  106. WHERE day = @day AND s.film_id = m.id
  107. AND m.id = e.film_id AND e.category_id = c.id";
  108. // ---------------------------------------------------------------------------
  109.     /** SQL statement to get screenings. */
  110.     public const String MOVIESCREENINGS =
  111. @"SELECT s.day, s.time, s.location, s.press
  112. FROM festival_screening s
  113. WHERE s.film_id = @film_id";
  114. // ---------------------------------------------------------------------------
  115.     /** SQL statement to get screenings. */
  116.     public const String PRESS =
  117. @"SELECT m.title, m.original_title, m.imdb, m.year, m.duration,
  118. s.day, s.time, s.location, s.press,
  119. e.year, c.name, c.keyword, c.color, m.id
  120. FROM festival_screening s, film_movietitle m,
  121. festival_entry e, festival_category c
  122. WHERE s.press=1 AND s.film_id = m.id
  123. AND m.id = e.film_id AND e.category_id = c.id
  124. ORDER BY day, time ASC";
  125. // ===========================================================================  
  126.   }
  127. }
Contact

Still have questions? 

We're happy to answer your questions. Reach out to us and we'll get back to you shortly.

Contact us
Stay updated

Join 11,000+ subscribers and become an iText PDF expert by staying up to date with our new products, updates, tips, technical solutions and happenings.

Subscribe Now