X

TOTD #121: JDBC resource for MySQL and Oracle sample database in GlassFish v3

Guest Author

This blog clearly explains how to configure the MySQL sample database (sakila) with GlassFish. Even though the instructions use a specific database but should work for other databases (such as Oracle, JavaDB, PostgreSQL, and others) as well. The second half of the blog provide specific syntax for the Oracle sample database.


  1. Download sakila sample database and unzip the archive.
  2. Install the database as described here - basically load and run "sakila-schema.sql" and "sakila-data.sql" extracted from the archive.

  3. Create a new MySQL user account using MySQL CLI Admin and assign the privileges

    1. Using "root" user (sudo mysql --user root)
      CREATE USER glassfish IDENTIFIED BY 'glassfish';
      GRANT ALL PRIVILEGES ON \*.\* TO 'glassfish'@'localhost' IDENTIFIED BY 'glassfish';
      FLUSH PRIVILEGES;

    2. Using "glassfish" user (sudo mysql --user glassfish)
      source sakila-schema.sql;
      source sakila-data.sql;
  4. Download Connector/J, unzip and copy "mysql-connector-java-5.x.x-bin.jar" to "glassfish/domains/domain1/lib/ext" directory.

  5. Start GlassFish server as:
    asadmin start-domain

  6. Create a JDBC resource

    1. Create JDBC connection pool as:
      asadmin create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource --restype javax.sql.DataSource --property "User=glassfish:Password=glassfish:URL=jdbc\\:mysql\\://localhost/sakila" jdbc/sakilaPool

    2. Test the JDBC connection pool as:
      asadmin ping-connection-pool jdbc/sakilaPool

    3. Create the JDBC resource as:
      asadmin create-jdbc-resource --connectionpoolid jdbc/sakilaPool jdbc/sakila

That's it!

Creating a JDBC resource for any other database requires the following updates to the steps mentioned above. Lets consider modifying these steps for the Oracle sample database.



  1. Use the client interface SQL\*PLus and connect as:
    sqlplus "/ as sysdba"


    create user and grant the privileges as:
    CREATE USER glassfish IDENTIFIED BY glassfish DEFAULT tablespace users TEMPORARY tablespace temp;
    GRANT CONNECT TO glassfish IDENTIFIED BY glassfish;
    GRANT UNLIMITED TABLESPACE TO glassfish;
    GRANT CREATE TABLE TO glassfish;
    GRANT CREATE SEQUENCE TO glassfish;
  2. Copy the appropriate JDBC driver (ojdbc6.jar).

  3. Create the JDBC resource as:
    asadmin create-jdbc-connection-pool --datasourceclassname oracle.jdbc.pool.OracleDataSource --restype javax.sql.DataSource --property "User=hr:Password=hr:URL=jdbc\\:oracle\\:thin\\:@localhost\\:1521\\:orcl" jdbc/hr
    asadmin ping-connection-pool jdbc/hr
    asadmin create-jdbc-resource --connectionpoolid jdbc/hr jdbc/hr


    as explained in TOTD #108.

Here are a few other related entries:

Technorati: totd javaee glassfish v3 jpa mysql sakila oracle

Join the discussion

Comments ( 1 )
  • uberVU - social comments Tuesday, February 16, 2010
    [Trackback] This post was mentioned on Twitter by arungupta: New Blog: TTD #121: JDBC resource for MySQL and Oracle sample database in GlassFish v3: This blog clearly explains... http://bit.ly/d4qxyq
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.