filip

5 years ago

Categories: Java EE

How to Configure Glassfish 4 with MySQL

This article explains how to setup and configure MySQL database with Glassfish Application Server

MySQL is widely used nowadays in production and development environments. Glassfish 4 (and previous version) have an easy interface to manage Database connections. You can use Glassfish’s Admin Console to setup and configure MySQL database, or if you prefer you can edit the configuration file, which holds the database preferences. I will show you both methods in this tutorial. Of cores This tutorial will also work on Payara.

Requirements

MySQL Server installed locally or on a remote machine. Also you will need an existing user (with password) and a database with read/write permissions for that user

Glassfish (or Payara) 4 Application Server

Install MySQL Java Connector on Glassfish

The official JDBC (Java Database Connectivity) driver for MySQL is called MySQL Connector/J. You can find the download page at https://dev.mysql.com/downloads/connector/j/

Download either the TAR or the ZIP archive – whatever you prefer to work with. Once you unpack the archive you will find a JAR file called mysql-connector-java-X.X.XX-bin.jar (where X.X.XX is the version number).
Paste the JAR file into your GLASSFISH_HOME/glassfish/bin directory
Restart Glassfish for the changes to take effect

Create Glassfish JDBC Connection Pool

Open Glassfish Admin Console at localhost:4848

Expand JDBC and click on “JDBC Connection Pools”

Click the “New…” button to create a new connection pool

Glassfish create new JDBC connection pool

Enter the name of the connection pool in “Pool Name”

Select javax.sql.DataSource as “Resource Type”

Select MySql as “Database Driver Vendor”

Click the “Next” button to proceed to next step

Create JDBC connection pool

On screen #2 check the “Datasource Classname”, it should be set to com.mysql.jdbc.jdbc2.optional.MysqlDataSource

Enable “Ping”

Create JDBC connection pool step 2

Now scroll down on the same screen.

Fill out following mandatory fields:

User – enter database user name
DatabaseName – enter the name of your database
Password – is the password for the user you entered in the first field
URL and Url – after jdbc:mysql://:3306/ add the name of the database, so it becomes jdbc:mysql://:3306/tutorials

You may want to fill in some of the other fields according to your project preferences, this is up to you!

Click “Finish” button to save your configuration. A ping to the database is sent to check the connection. If everything is OK you will see following message:

If you see some error message instead, click on “Edit” and check if the data you have entered is correct.

Create JDBC Resource in Glassfish

Creating a JDBC Resource is not necessarily part of the MySQL configuration tutorial, but you will need this to connect your project to the database, so lets cover this here:

Select “JDBC Resources” and click the “New…” button

Glassfish JDBC-resources

Under “JNDI Name” enter jdbc/ followd by the name you want to give to this resource. For example jdbc/tutorialsDS

Under “Pool name” select the name of the connection pool you just created in previous step

Save your newly created JDBC Resource

Glassfish create new JDBC resource

That’s it! You are now ready to use MySQL database with the Apps you deploy on your Glassfish Server.

Alternative Configuration Method

If you prefer to create the connection pool and the JDBC resource by editing config files, you can easily do this by inserting following lines between <resources>...</resources> tags into Glassfish’s configuration file for selected domains GLASSFISH_HOME/glassfish/domains/domain1/config/domain.xml

<jdbc-connection-pool ping="true" datasource-classname="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" name="MySQLPool" res-type="javax.sql.DataSource">
      <property name="SelfDestructOnPingSecondsLifetime" value="0"></property>
      <property name="UseUsageAdvisor" value="false"></property>
      <property name="AllowSlaveDownConnections" value="false"></property>
      <property name="LoadBalanceBlacklistTimeout" value="0"></property>
      <property name="QueryTimeoutKillsConnection" value="false"></property>
      <property name="CacheServerConfiguration" value="false"></property>
      <property name="RoundRobinLoadBalance" value="false"></property>
      <property name="UseCursorFetch" value="false"></property>
      <property name="JdbcCompliantTruncation" value="true"></property>
      <property name="UseOnlyServerErrorMessages" value="true"></property>
      <property name="AllowPublicKeyRetrieval" value="false"></property>
      <property name="DefaultAuthenticationPlugin" value="com.mysql.jdbc.authentication.MysqlNativePasswordPlugin"></property>
      <property name="DontTrackOpenResources" value="false"></property>
      <property name="UseInformationSchema" value="false"></property>
      <property name="UseNanosForElapsedTime" value="false"></property>
      <property name="UseCompression" value="false"></property>
      <property name="EnableEscapeProcessing" value="true"></property>
      <property name="PasswordCharacterEncoding" value="UTF-8"></property>
      <property name="AutoDeserialize" value="false"></property>
      <property name="TcpRcvBuf" value="0"></property>
      <property name="CallableStatementCacheSize" value="100"></property>
      <property name="AutoSlowLog" value="true"></property>
      <property name="IgnoreNonTxTables" value="false"></property>
      <property name="UseJDBCCompliantTimezoneShift" value="false"></property>
      <property name="AllowNanAndInf" value="false"></property>
      <property name="TcpSndBuf" value="0"></property>
      <property name="ContinueBatchOnError" value="true"></property>
      <property name="Logger" value="com.mysql.jdbc.log.StandardLogger"></property>
      <property name="MaxAllowedPacket" value="-1"></property>
      <property name="PrepStmtCacheSqlLimit" value="256"></property>
      <property name="AllowMultiQueries" value="false"></property>
      <property name="StrictFloatingPoint" value="false"></property>
      <property name="PreparedStatementCacheSqlLimit" value="256"></property>
      <property name="CachePreparedStatements" value="false"></property>
      <property name="InitialTimeout" value="2"></property>
      <property name="UseUnicode" value="true"></property>
      <property name="AutoClosePStmtStreams" value="false"></property>
      <property name="UseServerPrepStmts" value="false"></property>
      <property name="IncludeThreadNamesAsStatementComment" value="false"></property>
      <property name="PreparedStatementCacheSize" value="25"></property>
      <property name="CreateDatabaseIfNotExist" value="false"></property>
      <property name="RollbackOnPooledClose" value="true"></property>
      <property name="SocketFactoryClassName" value="com.mysql.jdbc.StandardSocketFactory"></property>
      <property name="LoadBalanceValidateConnectionOnSwapServer" value="false"></property>
      <property name="CompensateOnDuplicateKeyUpdateCounts" value="false"></property>
      <property name="EnablePacketDebug" value="false"></property>
      <property name="JdbcCompliantTruncationForReads" value="true"></property>
      <property name="PadCharsWithSpace" value="false"></property>
      <property name="NoAccessToProcedureBodies" value="false"></property>
      <property name="UseTimezone" value="false"></property>
      <property name="ClientCertificateKeyStoreType" value="JKS"></property>
      <property name="Port" value="3306"></property>
      <property name="ClientInfoProvider" value="com.mysql.jdbc.JDBC4CommentClientInfoProvider"></property>
      <property name="IsInteractiveClient" value="false"></property>
      <property name="CachePrepStmts" value="false"></property>
      <property name="ProfileSQL" value="false"></property>
      <property name="ProfileSql" value="false"></property>
      <property name="MaxQuerySizeToLog" value="2048"></property>
      <property name="LoadBalanceHostRemovalGracePeriod" value="15000"></property>
      <property name="TreatUtilDateAsTimestamp" value="true"></property>
      <property name="DynamicCalendars" value="false"></property>
      <property name="DatabaseName" value="tutorials"></property>
      <property name="UseSSL" value="false"></property>
      <property name="DisconnectOnExpiredPasswords" value="true"></property>
      <property name="DontCheckOnDuplicateKeyUpdateInSQL" value="false"></property>
      <property name="TrustCertificateKeyStoreType" value="JKS"></property>
      <property name="MaxRows" value="-1"></property>
      <property name="SlowQueryThresholdMillis" value="2000"></property>
      <property name="ResultSetSizeThreshold" value="100"></property>
      <property name="UseBlobToStoreUTF8OutsideBMP" value="false"></property>
      <property name="TransformedBitIsBoolean" value="false"></property>
      <property name="Pedantic" value="false"></property>
      <property name="SocksProxyPort" value="1080"></property>
      <property name="UseUltraDevWorkAround" value="false"></property>
      <property name="EnableQueryTimeouts" value="true"></property>
      <property name="LogXaCommands" value="false"></property>
      <property name="PrepStmtCacheSize" value="25"></property>
      <property name="Password" value="YOUR_DB_PASSWORD_HERE"></property>
      <property name="SelfDestructOnPingMaxOperations" value="0"></property>
      <property name="ZeroDateTimeBehavior" value="exception"></property>
      <property name="CallableStmtCacheSize" value="100"></property>
      <property name="SecondsBeforeRetryMaster" value="30"></property>
      <property name="EmulateLocators" value="false"></property>
      <property name="LoginTimeout" value="0"></property>
      <property name="GatherPerfMetrics" value="false"></property>
      <property name="RetriesAllDown" value="120"></property>
      <property name="DetectCustomCollations" value="false"></property>
      <property name="NoDatetimeStringSync" value="false"></property>
      <property name="LoadBalanceStrategy" value="random"></property>
      <property name="UseOldUTF8Behavior" value="false"></property>
      <property name="LoadBalanceAutoCommitStatementThreshold" value="0"></property>
      <property name="UltraDevHack" value="false"></property>
      <property name="UseFastIntParsing" value="true"></property>
      <property name="StrictUpdates" value="true"></property>
      <property name="EmptyStringsConvertToZero" value="true"></property>
      <property name="UseLocalSessionState" value="false"></property>
      <property name="LoggerClassName" value="com.mysql.jdbc.log.StandardLogger"></property>
      <property name="NullNamePatternMatchesAll" value="true"></property>
      <property name="PopulateInsertRowWithDefaultValues" value="false"></property>
      <property name="ProfilerEventHandler" value="com.mysql.jdbc.profiler.LoggingProfilerEventHandler"></property>
      <property name="UseStreamLengthsInPrepStmts" value="true"></property>
      <property name="RunningCTS13" value="false"></property>
      <property name="UseOldAliasMetadataBehavior" value="false"></property>
      <property name="ReadOnlyPropagatesToServer" value="true"></property>
      <property name="SocketFactory" value="com.mysql.jdbc.StandardSocketFactory"></property>
      <property name="MaxReconnects" value="3"></property>
      <property name="ReportMetricsIntervalMillis" value="30000"></property>
      <property name="BlobsAreStrings" value="false"></property>
      <property name="SendFractionalSeconds" value="true"></property>
      <property name="CacheResultSetMetadata" value="false"></property>
      <property name="Paranoid" value="false"></property>
      <property name="AllowUrlInLocalInfile" value="false"></property>
      <property name="NoTimezoneConversionForTimeType" value="false"></property>
      <property name="EmulateUnsupportedPstmts" value="true"></property>
      <property name="ReconnectAtTxEnd" value="false"></property>
      <property name="RequireSSL" value="false"></property>
      <property name="UseHostsInPrivileges" value="true"></property>
      <property name="UseSSPSCompatibleTimezoneShift" value="false"></property>
      <property name="UseReadAheadInput" value="true"></property>
      <property name="ParseInfoCacheFactory" value="com.mysql.jdbc.PerConnectionLRUFactory"></property>
      <property name="DefaultFetchSize" value="0"></property>
      <property name="URL" value="jdbc:mysql://localhost:3306/tutorials"></property>
      <property name="Url" value="jdbc:mysql://localhost:3306/tutorials"></property>
      <property name="AllowMasterDownConnections" value="false"></property>
      <property name="CacheDefaultTimezone" value="true"></property>
      <property name="QueriesBeforeRetryMaster" value="50"></property>
      <property name="FunctionsNeverReturnBlobs" value="false"></property>
      <property name="DumpQueriesOnException" value="false"></property>
      <property name="LoadBalanceExceptionChecker" value="com.mysql.jdbc.StandardLoadBalanceExceptionChecker"></property>
      <property name="VerifyServerCertificate" value="true"></property>
      <property name="NetTimeoutForStreamingResults" value="600"></property>
      <property name="ProcessEscapeCodesForPrepStmts" value="true"></property>
      <property name="UseAffectedRows" value="false"></property>
      <property name="GatherPerformanceMetrics" value="false"></property>
      <property name="TinyInt1isBit" value="true"></property>
      <property name="MetadataCacheSize" value="50"></property>
      <property name="RewriteBatchedStatements" value="false"></property>
      <property name="CacheCallableStatements" value="false"></property>
      <property name="ServerName" value="localhost"></property>
      <property name="GetProceduresReturnsFunctions" value="true"></property>
      <property name="UseGmtMillisForDatetimes" value="false"></property>
      <property name="CapitalizeTypeNames" value="true"></property>
      <property name="ServerConfigCacheFactory" value="com.mysql.jdbc.PerVmServerConfigCacheFactory"></property>
      <property name="NoTimezoneConversionForDateType" value="true"></property>
      <property name="TcpTrafficClass" value="0"></property>
      <property name="AutoGenerateTestcaseScript" value="false"></property>
      <property name="CacheCallableStmts" value="false"></property>
      <property name="FailOverReadOnly" value="true"></property>
      <property name="LoadBalancePingTimeout" value="0"></property>
      <property name="LocatorFetchBufferSize" value="1048576"></property>
      <property name="RelaxAutoCommit" value="false"></property>
      <property name="UseFastDateParsing" value="true"></property>
      <property name="LoadBalanceEnableJMX" value="false"></property>
      <property name="OverrideSupportsIntegrityEnhancementFacility" value="false"></property>
      <property name="HoldResultsOpenOverStatementClose" value="false"></property>
      <property name="InteractiveClient" value="false"></property>
      <property name="UseJvmCharsetConverters" value="false"></property>
      <property name="PortNumber" value="3306"></property>
      <property name="UseDynamicCharsetInfo" value="true"></property>
      <property name="LogSlowQueries" value="false"></property>
      <property name="IncludeThreadDumpInDeadlockExceptions" value="false"></property>
      <property name="ReplicationEnableJMX" value="false"></property>
      <property name="SlowQueryThresholdNanos" value="0"></property>
      <property name="UseDirectRowUnpack" value="true"></property>
      <property name="UseSqlStateCodes" value="true"></property>
      <property name="IncludeInnodbStatusInDeadlockExceptions" value="false"></property>
      <property name="MaintainTimeStats" value="true"></property>
      <property name="PinGlobalTxToPhysicalConnection" value="false"></property>
      <property name="TcpNoDelay" value="true"></property>
      <property name="TraceProtocol" value="false"></property>
      <property name="AlwaysSendSetIsolation" value="true"></property>
      <property name="NullCatalogMeansCurrent" value="true"></property>
      <property name="YearIsDateType" value="true"></property>
      <property name="SocketTimeout" value="0"></property>
      <property name="UseServerPreparedStmts" value="false"></property>
      <property name="UseLocalTransactionState" value="false"></property>
      <property name="GenerateSimpleParameterMetadata" value="false"></property>
      <property name="ExplainSlowQueries" value="false"></property>
      <property name="UseColumnNamesInFindColumn" value="false"></property>
      <property name="ConnectTimeout" value="0"></property>
      <property name="ElideSetAutoCommits" value="false"></property>
      <property name="PacketDebugBufferSize" value="20"></property>
      <property name="RetainStatementAfterResultSetClose" value="false"></property>
      <property name="DumpMetadataOnColumnNotFound" value="false"></property>
      <property name="BlobSendChunkSize" value="1048576"></property>
      <property name="UseLegacyDatetimeCode" value="true"></property>
      <property name="UseUnbufferedInput" value="true"></property>
      <property name="AllowLoadLocalInfile" value="true"></property>
      <property name="ReadFromMasterWhenNoSlaves" value="false"></property>
      <property name="AutoReconnectForPools" value="false"></property>
      <property name="TcpKeepAlive" value="true"></property>
      <property name="ClobberStreamingResults" value="false"></property>
      <property name="User" value="root"></property>
    </jdbc-connection-pool>
    <jdbc-resource pool-name="MySQLPool" jndi-name="jdbc/tutorialsDS"></jdbc-resource>

Also add following line in <server>...</server> tag

<resource-ref ref="jdbc/tutorialsDS"></resource-ref>

Thanks for reading. If you have any questions or suggestions about the many configuration properties, please use the comments section below.

0 0 votes

Article Rating

Glassfish Enable Secure Admin »

« Java File Upload REST service

Tags: glassfishjavajdbcmysqlpayararesource pool

filip:

View Comments (4)

Florian says:

November 17, 2017 at 3:21 pm

Huge Thanks! Step 2 didn't work for me: I copied the jar file into glassfish5/glassfish/Domains/domain1/lib/ext Note that I use glassfish 5 and Domain 1 is the default for a new domain.
- filip says:
  
  November 17, 2017 at 3:56 pm
  
  Hi Florian,
  step 2 says to copy the mysql connector jar into GLASSFISH_HOME/glassfish/bin which in your case will be glassfish5/glassfish/bin . I recommend using this approach, because the DB connector is a library used in the entire application and not only for specific domain. Try to copy the jar in the location I just described or alternatively copy it to glassfish5/glassfish/Domains/domain1/lib (without ext). Please share what worked for you best
Md Tamim Haider says:

December 4, 2017 at 12:37 pm

Hello,

Huge huge Thanks a lot for nice tutorial. But when i pasted the jar file in glassfish/bin the ping was showing error. Then i have tried keep the jar file both in glassfish/bin and glassfish/lib folder and ping is successful.
Henry says:

April 24, 2018 at 3:38 am

I'm getting HTTP Status 500 - Internal Server Error when click Next on the Step One