Posts tagged ‘JDBC’

GlassFish データベースの設定

GlassFishはデータベースとの接続にデータベースベンダーが提供するJDBCドライバを使用します.

GlassFishにはインストール時デフォルトで Java DBが付属しており,あらかじめJava DBに対するJDBCリソースと接続プールの設定がされています.そして GlassFish の監視系の機能の一つである,「呼び出しフロー」のデータ保存先として使用されています.
ここでは,新たにMySQL 5.1との接続設定方法について紹介します.MySQLと接続するためには,MySQLのJDBCドライバを事前に入手しておいてください.
また以降の手順ではMySQLが既にインストールされていることを前提に説明します.

JDBCドライバに対するクラスパスを設定

dashost > asadmin get “cluster1-config.java-config.server-classpath”
cluster1-config.java-config.server-classpath =

dashost > asadmin set “cluster1-config.java-config.server-classpath=/sun/glassfish-v2.1.1/lib/mysql-connector-java-5.1.5-bin.jar”
cluster1-config.java-config.server-classpath = /sun/glassfish-v2.1.1/lib/mysql-connector-java-5.1.5-bin.jar

クラスパスの設定は,管理コンソールから左ペインの「設定」→「対象のコンフィグ(cluster1-config)」→「JVM 設定」を選択し右ペインの「サーバクラスパス」内に記載し設定できます.


次に JDBC の接続プールを作成します.

dashost > asadmin create-jdbc-connection-pool –datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlDataSource –restype=javax.sql.DataSource –property user=root:password=password:DatabaseName=MySQL:ServerName=localhost:port=3306:URL=”jdbc\:mysql\://localhost\:3306/test” MySQL-Pool
コマンド create-jdbc-connection-pool は正常に実行されました。


分散トランザクションが必要な場合,リソースタイプ,データソースにそれぞれ下記を使用してください.
javax.sql.XADataSource
com.mysql.jdbc.jdbc2.optional.MysqlXADataSource

管理コンソールからは下記の手順に従い設定してください.
まず、左ペインの「リソース」→「JDBC」→ 「接続プール」を選択し右ペインの「新規 …」ボタンを押下します.

次に,「接続プール名」,「リソースタイプ」,「データベースベンダー」を選択し「次へ」ボタンを押下します.

最後に,JDBC の各種詳細な設定項目を選択/入力し「完了」ボタンを押下します.






JDBCドライバ等の設定項目一覧が数多くリストされますが,ここでは最低限下記の項目のみ編集して設定を完了します.

名前
user root
password password
DatabaseName MySQL
ServerName localhost
port 3306
URL jdbc:mysql://localhost:3306/test


「完了」ボタンを押下すると下記の画面が表示されます.

ここで、作成した JDBC 接続プールが有効か否か疎通確認を行います.

成功時:

dashost > asadmin ping-connection-pool MySQL-Pool
コマンド ping-connection-pool は正常に実行されました。


失敗時:

dashost > asadmin ping-connection-pool MySQL-Pool
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
CLI137 コマンド ping-connection-pool は失敗しました。


また,管理コンソールから左ペインの「リソース」→「 JDBC」→「 接続プール」より対象の接続プールを選択し「Ping」ボタンを押下し確認できます.
成功時:

失敗時:

次に、作成した JDBC 接続プールに対して,リソース設定を行います.
アプリケーションはここで設定する JNDI 名を利用して DB にアクセスできるようになります.

dashost > asadmin create-jdbc-resource –connectionpoolid MySQL-Pool jdbc/mysql
コマンド create-jdbc-resource は正常に実行されました。


管理コンソールからは下記の手順に従い設定してください.
まず、左ペインの「リソース」→「JDBC」→ 「 JDBCリソース」を選択し右ペインより「新規 …」ボタンを押下します.


続いて,「JNDI 名」,「プール名」,有効状態のチェックをつけ「利用可能なターゲット」に対象のクラスタ(cluster1)を追加して「了解」ボタンを押下します.


「了解」ボタンを押下すると下記の画面が表示され JDBC リソースの設定は完了します.以降アプリケーションはここで設定した “jdbc/mysql” を利用して DB
に対して接続ができるようになります.

DBの設定情報の取得
ここで,DB の設定可能な項目の一覧をリストしてみます.asadmin の get コマンドを実行して確認ができます.各設定項目は asadmin set コマンドで設定変更を更新できますので必要に応じて設定変更を行ってください.

dashost > asadmin get “domain.resources.jdbc-connection-pool.MySQL-Pool.*”
domain.resources.jdbc-connection-pool.MySQL-Pool.allow-non-component-callers = false
domain.resources.jdbc-connection-pool.MySQL-Pool.associate-with-thread = false
domain.resources.jdbc-connection-pool.MySQL-Pool.connection-creation-retry-attempts = 0
domain.resources.jdbc-connection-pool.MySQL-Pool.connection-creation-retry-interval-in-seconds = 10
domain.resources.jdbc-connection-pool.MySQL-Pool.connection-leak-reclaim = false
domain.resources.jdbc-connection-pool.MySQL-Pool.connection-leak-timeout-in-seconds = 0
domain.resources.jdbc-connection-pool.MySQL-Pool.connection-validation-method = auto-commit
domain.resources.jdbc-connection-pool.MySQL-Pool.datasource-classname = com.mysql.jdbc.jdbc2.optional.MysqlDataSource
domain.resources.jdbc-connection-pool.MySQL-Pool.description =
domain.resources.jdbc-connection-pool.MySQL-Pool.fail-all-connections = false
domain.resources.jdbc-connection-pool.MySQL-Pool.idle-timeout-in-seconds = 300
domain.resources.jdbc-connection-pool.MySQL-Pool.is-connection-validation-required = false
domain.resources.jdbc-connection-pool.MySQL-Pool.is-isolation-level-guaranteed = false
domain.resources.jdbc-connection-pool.MySQL-Pool.lazy-connection-association = false
domain.resources.jdbc-connection-pool.MySQL-Pool.lazy-connection-enlistment = false
domain.resources.jdbc-connection-pool.MySQL-Pool.match-connections = false
domain.resources.jdbc-connection-pool.MySQL-Pool.max-connection-usage-count = 0
domain.resources.jdbc-connection-pool.MySQL-Pool.max-pool-size = 32
domain.resources.jdbc-connection-pool.MySQL-Pool.max-wait-time-in-millis = 60000
domain.resources.jdbc-connection-pool.MySQL-Pool.name = MySQL-Pool
domain.resources.jdbc-connection-pool.MySQL-Pool.non-transactional-connections = false
domain.resources.jdbc-connection-pool.MySQL-Pool.pool-resize-quantity = 2
domain.resources.jdbc-connection-pool.MySQL-Pool.property.AllowLoadLocalInfile = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.AllowMultiQueries = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.AllowNanAndInf = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.AllowUrlInLocalInfile = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.AlwaysSendSetIsolation = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.AutoClosePStmtStreams = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.AutoDeserialize = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.AutoGenerateTestcaseScript = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.AutoReconnectForPools = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.AutoSlowLog = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.BlobSendChunkSize = 1048576
domain.resources.jdbc-connection-pool.MySQL-Pool.property.BlobsAreStrings = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.CacheCallableStatements = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.CacheCallableStmts = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.CachePrepStmts = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.CachePreparedStatements = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.CacheResultSetMetadata = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.CacheServerConfiguration = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.CallableStatementCacheSize = 100
domain.resources.jdbc-connection-pool.MySQL-Pool.property.CallableStmtCacheSize = 100
domain.resources.jdbc-connection-pool.MySQL-Pool.property.CapitalizeTypeNames = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.ClientInfoProvider = com.mysql.jdbc.JDBC4CommentClientInfoProvider
domain.resources.jdbc-connection-pool.MySQL-Pool.property.ClobberStreamingResults = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.ConnectTimeout = 0
domain.resources.jdbc-connection-pool.MySQL-Pool.property.ContinueBatchOnError = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.CreateDatabaseIfNotExist = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.DatabaseName = MySQL
domain.resources.jdbc-connection-pool.MySQL-Pool.property.DefaultFetchSize = 0
domain.resources.jdbc-connection-pool.MySQL-Pool.property.DontTrackOpenResources = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.DumpMetadataOnColumnNotFound = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.DumpQueriesOnException = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.DynamicCalendars = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.ElideSetAutoCommits = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.EmptyStringsConvertToZero = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.EmulateLocators = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.EmulateUnsupportedPstmts = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.EnablePacketDebug = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.EnableQueryTimeouts = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.ExplainSlowQueries = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.FailOverReadOnly = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.FunctionsNeverReturnBlobs = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.GatherPerfMetrics = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.GatherPerformanceMetrics = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.GenerateSimpleParameterMetadata = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.HoldResultsOpenOverStatementClose = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.IgnoreNonTxTables = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.IncludeInnodbStatusInDeadlockExceptions = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.InitialTimeout = 2
domain.resources.jdbc-connection-pool.MySQL-Pool.property.InteractiveClient = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.IsInteractiveClient = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.JdbcCompliantTruncation = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.JdbcCompliantTruncationForReads = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.LoadBalanceStrategy = random
domain.resources.jdbc-connection-pool.MySQL-Pool.property.LocatorFetchBufferSize = 1048576
domain.resources.jdbc-connection-pool.MySQL-Pool.property.LogSlowQueries = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.LogXaCommands = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.Logger = com.mysql.jdbc.log.StandardLogger
domain.resources.jdbc-connection-pool.MySQL-Pool.property.LoggerClassName = com.mysql.jdbc.log.StandardLogger
domain.resources.jdbc-connection-pool.MySQL-Pool.property.LoginTimeout = 0
domain.resources.jdbc-connection-pool.MySQL-Pool.property.MaintainTimeStats = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.MaxQuerySizeToLog = 2048
domain.resources.jdbc-connection-pool.MySQL-Pool.property.MaxReconnects = 3
domain.resources.jdbc-connection-pool.MySQL-Pool.property.MaxRows = -1
domain.resources.jdbc-connection-pool.MySQL-Pool.property.MetadataCacheSize = 50
domain.resources.jdbc-connection-pool.MySQL-Pool.property.NetTimeoutForStreamingResults = 600
domain.resources.jdbc-connection-pool.MySQL-Pool.property.NoAccessToProcedureBodies = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.NoDatetimeStringSync = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.NoTimezoneConversionForTimeType = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.NullCatalogMeansCurrent = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.NullNamePatternMatchesAll = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.OverrideSupportsIntegrityEnhancementFacility = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.PacketDebugBufferSize = 20
domain.resources.jdbc-connection-pool.MySQL-Pool.property.PadCharsWithSpace = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.Paranoid = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.Password = password
domain.resources.jdbc-connection-pool.MySQL-Pool.property.Pedantic = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.PinGlobalTxToPhysicalConnection = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.PopulateInsertRowWithDefaultValues = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.Port = 3306
domain.resources.jdbc-connection-pool.MySQL-Pool.property.PortNumber = 3306
domain.resources.jdbc-connection-pool.MySQL-Pool.property.PrepStmtCacheSize = 25
domain.resources.jdbc-connection-pool.MySQL-Pool.property.PrepStmtCacheSqlLimit = 256
domain.resources.jdbc-connection-pool.MySQL-Pool.property.PreparedStatementCacheSize = 25
domain.resources.jdbc-connection-pool.MySQL-Pool.property.PreparedStatementCacheSqlLimit = 256
domain.resources.jdbc-connection-pool.MySQL-Pool.property.ProcessEscapeCodesForPrepStmts = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.ProfileSQL = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.ProfileSql = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.QueriesBeforeRetryMaster = 50
domain.resources.jdbc-connection-pool.MySQL-Pool.property.ReconnectAtTxEnd = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.RelaxAutoCommit = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.ReportMetricsIntervalMillis = 30000
domain.resources.jdbc-connection-pool.MySQL-Pool.property.RequireSSL = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.ResultSetSizeThreshold = 100
domain.resources.jdbc-connection-pool.MySQL-Pool.property.RewriteBatchedStatements = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.RollbackOnPooledClose = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.RoundRobinLoadBalance = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.RunningCTS13 = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.SecondsBeforeRetryMaster = 30
domain.resources.jdbc-connection-pool.MySQL-Pool.property.ServerName = localhost
domain.resources.jdbc-connection-pool.MySQL-Pool.property.SlowQueryThresholdMillis = 2000
domain.resources.jdbc-connection-pool.MySQL-Pool.property.SlowQueryThresholdNanos = 0
domain.resources.jdbc-connection-pool.MySQL-Pool.property.SocketFactory = com.mysql.jdbc.StandardSocketFactory
domain.resources.jdbc-connection-pool.MySQL-Pool.property.SocketFactoryClassName = com.mysql.jdbc.StandardSocketFactory
domain.resources.jdbc-connection-pool.MySQL-Pool.property.SocketTimeout = 0
domain.resources.jdbc-connection-pool.MySQL-Pool.property.StrictFloatingPoint = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.StrictUpdates = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.TcpKeepAlive = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.TcpNoDelay = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.TcpRcvBuf = 0
domain.resources.jdbc-connection-pool.MySQL-Pool.property.TcpSndBuf = 0
domain.resources.jdbc-connection-pool.MySQL-Pool.property.TcpTrafficClass = 0
domain.resources.jdbc-connection-pool.MySQL-Pool.property.TinyInt1isBit = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.TraceProtocol = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.TransformedBitIsBoolean = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.TreatUtilDateAsTimestamp = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.URL = jdbc:mysql://:3306/
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UltraDevHack = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.Url = jdbc:mysql://localhost:3306/test
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseBlobToStoreUTF8OutsideBMP = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseCompression = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseCursorFetch = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseDirectRowUnpack = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseDynamicCharsetInfo = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseFastDateParsing = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseFastIntParsing = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseGmtMillisForDatetimes = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseHostsInPrivileges = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseInformationSchema = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseJDBCCompliantTimezoneShift = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseJvmCharsetConverters = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseLocalSessionState = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseNanosForElapsedTime = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseOldAliasMetadataBehavior = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseOldUTF8Behavior = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseOnlyServerErrorMessages = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseReadAheadInput = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseSSL = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseSSPSCompatibleTimezoneShift = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseServerPrepStmts = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseServerPreparedStmts = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseSqlStateCodes = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseStreamLengthsInPrepStmts = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseTimezone = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseUltraDevWorkAround = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseUnbufferedInput = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseUnicode = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.UseUsageAdvisor = false
domain.resources.jdbc-connection-pool.MySQL-Pool.property.User = root
domain.resources.jdbc-connection-pool.MySQL-Pool.property.YearIsDateType = true
domain.resources.jdbc-connection-pool.MySQL-Pool.property.ZeroDateTimeBehavior = exception
domain.resources.jdbc-connection-pool.MySQL-Pool.res-type = javax.sql.DataSource
domain.resources.jdbc-connection-pool.MySQL-Pool.statement-timeout-in-seconds = -1
domain.resources.jdbc-connection-pool.MySQL-Pool.steady-pool-size = 8
domain.resources.jdbc-connection-pool.MySQL-Pool.transaction-isolation-level =
domain.resources.jdbc-connection-pool.MySQL-Pool.validate-atmost-once-period-in-seconds = 0
domain.resources.jdbc-connection-pool.MySQL-Pool.validation-table-name =
domain.resources.jdbc-connection-pool.MySQL-Pool.wrap-jdbc-objects = false


コネックションプールのモニタリング
最後に、JDBC の接続プールに対してモニタリングの設定を行う方法を紹介します.モニタリングを有効にするために下記の手順に従い設定を行い,確認してください.

dashost > asadmin list “*monitor*”
server.monitoring-service
server.monitoring-service.module-monitoring-levels
dashost > asadmin set “server.monitoring-service.module-monitoring-levels.jdbc-connection-pool=HIGH”
server.monitoring-service.module-monitoring-levels.jdbc-connection-pool = HIGH
dashost > asadmin get “server.monitoring-service.module-monitoring-levels.*”
server.monitoring-service.module-monitoring-levels.connector-connection-pool = OFF
server.monitoring-service.module-monitoring-levels.connector-service = OFF
server.monitoring-service.module-monitoring-levels.ejb-container = OFF
server.monitoring-service.module-monitoring-levels.http-service = OFF
server.monitoring-service.module-monitoring-levels.jdbc-connection-pool = HIGH
server.monitoring-service.module-monitoring-levels.jms-service = OFF
server.monitoring-service.module-monitoring-levels.jvm = OFF
server.monitoring-service.module-monitoring-levels.orb = OFF
server.monitoring-service.module-monitoring-levels.thread-pool = OFF
server.monitoring-service.module-monitoring-levels.transaction-service = OFF
server.monitoring-service.module-monitoring-levels.web-container = OFF
dashost > asadmin list –monitor=true “*MySQL*”
server.resources.MySQL-Pool
dashost > asadmin get –monitor=true “server.resources.MySQL-Pool.*”

広告

2010年2月8日 at 12:21 PM 1件のコメント


Java Champion & Evangelist

ご注意

このエントリは個人の見解であり、所属する会社の公式見解ではありません

カレンダー

2017年11月
« 10月    
 12345
6789101112
13141516171819
20212223242526
27282930  

カテゴリー

Twitter

clustermap

ブログ統計情報

  • 1,015,021 hits

Feeds