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

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

Comments:

[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

Posted by uberVU - social comments on February 16, 2010 at 02:12 AM PST #

Post a Comment:
Comments are closed for this entry.
About

profile image
Arun Gupta is a technology enthusiast, a passionate runner, author, and a community guy who works for Oracle Corp.


Java EE 7 Samples

Stay Connected

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
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
   
       
Today