1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
/*
 * This class is part of the book "iText in Action - 2nd Edition"
 * written by Bruno Lowagie (ISBN: 9781935182610)
 * For more info, go to: http://itextpdf.com/examples/
 * This example only works with the AGPL version of iText.
 */
 
package com.lowagie.database;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
 
/**
 * This is a helper class to access the database
 * that comes with the book samples.
 */
 
public abstract class DatabaseConnection {
 
    /** our connection to the db. */
    protected Connection connection;
    
    /**
     * Closes the connection to the database.
     */
    public void close() throws SQLException {
        connection.close();
    }
 
    /**
     * Creates a statement.
     * @return    a statement
     * @throws SQLException 
     */
    public Statement createStatement() throws SQLException {
        return connection.createStatement();
    }
 
    /**
     * Creates a prepated statement using a query.
     * @param    query    the query that will be used to create
     * a prepared statement.
     * @return    a statement
     * @throws SQLException 
     */
    public PreparedStatement createPreparedStatement(String query)
        throws SQLException {
        return connection.prepareStatement(query);
    }
    
    /**
     * Performs an update in the database.
     * @param    expression    an SQL expression
     * (CREATE, DROP, INSERT, UPDATE)
     */
    public void update(String expression) throws SQLException {
        Statement st = createStatement();
        int i = st.executeUpdate(expression);
        st.close();
        if (i == -1) {
            throw new SQLException("db error : " + expression);
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
/*
 * This class is part of the book "iText in Action - 2nd Edition"
 * written by Bruno Lowagie (ISBN: 9781935182610)
 * For more info, go to: http://itextpdf.com/examples/
 * This example only works with the AGPL version of iText.
 */
 
package com.lowagie.database;
 
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
 
/**
 * This is a helper class to access an hsqldb database.
 * The database scripts are supposed to be present in directory:
 * resources/db
 */
public class HsqldbConnection extends DatabaseConnection {
 
    /**
     * Creates the connection.
     * @param db_file_name_prefix the database name,
     * which is the prefix of the database file
     * @throws SQLException 
     */
    public HsqldbConnection(String db_file_name_prefix)
        throws SQLException {
        try {
            Class.forName("org.hsqldb.jdbcDriver");
        } catch (ClassNotFoundException e) {
            throw new SQLException("HSQLDB database driver not found");
        }
        connection = DriverManager.getConnection(
            "jdbc:hsqldb:resources/db/" + db_file_name_prefix, "SA", "");
    }
    
    /**
     * Shuts down the database and closes the connection.
     */
    public void close() throws SQLException {
        Statement st = createStatement();
        st.execute("SHUTDOWN");
        super.close();
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
/*
 * This class is part of the book "iText in Action - 2nd Edition"
 * written by Bruno Lowagie (ISBN: 9781935182610)
 * For more info, go to: http://itextpdf.com/examples/
 * This example only works with the AGPL version of iText.
 */
 
package com.lowagie.database;
 
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.sql.SQLException;
 
 
/**
 * Creates the hsqldatabase using SQL scripts containing
 * CREATE statements with the table definitions, and
 * INSERT statements with the data.
 */
public class CreateHsqldbTables {
 
    /**
     * Imports a number of SQL scripts into an HSQLDB database.
     * @param    args    no arguments needed
     * @throws SQLException 
     * @throws IOException 
     * @throws UnsupportedEncodingException 
     */
    public static void main(String[] args)
        throws SQLException, UnsupportedEncodingException, IOException {
        DatabaseConnection conn = new HsqldbConnection("filmfestival");
        BufferedReader in;
        String line;
        conn.update("SET IGNORECASE TRUE");
        in = new BufferedReader(
                new FileReader("resources/scripts/filmfestival_hsqldb.sql"));
        while( (line = in.readLine()) != null) {
            conn.update(line);
        }
        in = new BufferedReader(
                new FileReader("resources/scripts/filmfestival.sql"));
        while( (line = in.readLine()) != null) {
            conn.update(line);
        }
        conn.close();
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
/*
 * This class is part of the book "iText in Action - 2nd Edition"
 * written by Bruno Lowagie (ISBN: 9781935182610)
 * For more info, go to: http://itextpdf.com/examples/
 * This example only works with the AGPL version of iText.
 */
 
package com.lowagie.database;
 
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
 
/**
 * This is a helper class to access a mySql database.
 * The username and password of the database are supposed to be present
 * in a properties file mysql.props (in the working directory)
 */
public class MySqlConnection extends DatabaseConnection {
 
    /**
     * Creates the connection.
     * @param db_file_name_prefix the database name,
     * which is the prefix of the database file
     * @throws SQLException 
     */
    public MySqlConnection(String database) throws SQLException {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new SQLException("mySql database driver not found");
        }
        Properties credentials = new Properties();
        try {
            credentials.load(new FileInputStream("mysql.props"));
        } catch (IOException e) {
            throw new SQLException("Can't read mysql.props.");
        }
        connection = DriverManager.getConnection(
                "jdbc:mysql://localhost/" + database,
                credentials.getProperty("username"),
                credentials.getProperty("password"));
    }
 
}