乐天笔迹

天才在于勤奋，聪明在于积累
BlogJava 联系 聚合 管理
  3 Posts :: 2 Stories :: 8 Comments :: 0 Trackbacks
Mysql Driver Class Name and JDBC URL Format (转)

Driver Class Name and JDBC URL Format

The name of the class that implements java.sql.Driver in MySQL Connector/J is 'com.mysql.jdbc.Driver'. The 'org.gjt.mm.mysql.Driver' class name is also usable to remain backwards-compatible with MM.MySQL. You should use this class name when registering the driver, or when otherwise configuring software to use MySQL Connector/J.

The JDBC URL format for MySQL Connector/J is as follows, with items in square brackets ([, ]) being optional:

jdbc:mysql://[host][,failoverhost...][:port]/[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

If the hostname is not specified, it defaults to '127.0.0.1'. If the port is not specified, it defaults to '3306', the default port number for MySQL servers.

Starting with version 3.0.12 and 3.1.2, Connector/J supports multiple hosts with ports, separated by commas:

jdbc:mysql://[host:port],[host:port].../[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

If the database is not specified, the connection will be made with no 'current' database. In this case, you will need to either call the 'setCatalog()' method on the Connection instance, issue a 'USE dbname' query or fully-specify table names using the database name (i.e. 'SELECT dbname.tablename.colname FROM dbname.tablename...') in your SQL. Not specifying the database to use upon connection is generally only useful when building tools that work with multiple databases, such as GUI database managers.

MySQL Connector/J has fail-over support. This allows the driver to fail-over to any number of "slave" hosts and still perform read-only queries. Fail-over only happens when the connection is in an autoCommit(true) state, because fail-over can not happen reliably when a transaction is in progress. Most good application servers and connection pools set autoCommit to 'true' at the end of every transaction/connection use. The fail-over functionality has the following behavior: If the URL property "autoReconnect" is false: Failover only happens at connection initialization, and failback occurs when the driver determines that the first host has become available again If the URL property "autoReconnect" is true: Failover happens when the driver determines that the connection has failed (before every query), and falls back to the first host when it determines that the host has become available again (after queriesBeforeRetryMaster queries have been issued). In either case, whenever you are connected to a "failed-over" server, the connection will be set to read-only state, so queries that would modify data will have exceptions thrown (the query will never be processed by the MySQL server).

You may specify additional properties to the JDBC driver, either by placing them in a java.util.Properties instance and passing that instance to the DriverManager when you connect, or by adding them to the end of your JDBC URL as name-value pairs. The first property needs to be preceeded with a '?' character, and additional name-value pair properties are separated by an '&' character. The properties, their definitions and their default values are covered in the following table:

Table 2.1. Connection Properties

Connection/Authentication
user The user to connect as No
password The password to use when connecting No
socketFactory The name of the class that the driver should use for creating socket connections to the server. This class must implement the interface 'com.mysql.jdbc.SocketFactory' and have public no-args constructor. No com.mysql.jdbc.StandardSocketFactory
connectTimeout Timeout for socket connect (in milliseconds), with 0 being no timeout. Only works on JDK-1.4 or newer. Defaults to '0'. No 0
socketTimeout Timeout on network socket operations (0, the default means no timeout). No 0
useConfigs Load the comma-delimited list of configuration properties before parsing the URL or applying user-specified properties. These configurations are explained in the 'Configurations' of the documentation. No
interactiveClient Set the CLIENT_INTERACTIVE flag, which tells MySQL to timeout connections based on INTERACTIVE_TIMEOUT instead of WAIT_TIMEOUT No false
propertiesTransform An implementation of com.mysql.jdbc.ConnectionPropertiesTransform that the driver will use to modify URL properties passed to the driver before attempting a connection No
useCompression Use zlib compression when communicating with the server (true/false)? Defaults to 'false'. No false
High Availability and Clustering
autoReconnect Should the driver try to re-establish bad connections? No false
autoReconnectForPools Use a reconnection strategy appropriate for connection pools (defaults to 'false') No false
failOverReadOnly When failing over in autoReconnect mode, should the connection be set to 'read-only'? No true
maxReconnects Maximum number of reconnects to attempt if autoReconnect is true, default is '3'. No 3
initialTimeout If autoReconnect is enabled, the initial time to wait between re-connect attempts (in seconds, defaults to '2'). No 2
queriesBeforeRetryMaster Number of queries to issue before falling back to master when failed over (when using multi-host failover). Whichever condition is met first, 'queriesBeforeRetryMaster' or 'secondsBeforeRetryMaster' will cause an attempt to be made to reconnect to the master. Defaults to 50. No 50
secondsBeforeRetryMaster How long should the driver wait, when failed over, before attempting to reconnect to the master server? Whichever condition is met first, 'queriesBeforeRetryMaster' or 'secondsBeforeRetryMaster' will cause an attempt to be made to reconnect to the master. Time in seconds, defaults to 30 No 30
Security
allowMultiQueries Allow the use of ';' to delimit multiple queries during one statement (true/false, defaults to 'false' No false
useSSL Use SSL when communicating with the server (true/false), defaults to 'false' No false
requireSSL Require SSL connection if useSSL=true? (defaults to 'false'). No false
allowUrlInLocalInfile Should the driver allow URLs in 'LOAD DATA LOCAL INFILE' statements? No false
paranoid Take measures to prevent exposure sensitive information in error messages and clear data structures holding sensitive data when possible? (defaults to 'false') No false
Performance Extensions
metadataCacheSize The number of queries to cacheResultSetMetadata for if cacheResultSetMetaData is set to 'true' (default 50) No 50
prepStmtCacheSize If prepared statement caching is enabled, how many prepared statements should be cached? No 25
prepStmtCacheSqlLimit If prepared statement caching is enabled, what's the largest SQL the driver will cache the parsing for? No 256
cacheCallableStmts Should the driver cache the parsing stage of CallableStatements No false
cachePrepStmts Should the driver cache the parsing stage of PreparedStatements? No false
cacheResultSetMetadata Should the driver cache ResultSetMetaData for Statements and PreparedStatements? (Req. JDK-1.4+, true/false, default 'false') No false
cacheServerConfiguration Should the driver cache the results of 'SHOW VARIABLES' and 'SHOW COLLATION' on a per-URL basis? No false
dontUnpackBinaryResults Should the driver delay unpacking results from server-side prepared statements until the values are asked for? No false
dynamicCalendars Should the driver retrieve the default calendar when required, or cache it per connection/session? No false
elideSetAutoCommits If using MySQL-4.1 or newer, should the driver only issue 'set autocommit=n' queries when the server's state doesn't match the requested state by Connection.setAutoCommit(boolean)? No false
useFastIntParsing Use internal String->Integer conversion routines to avoid excessive object creation? No true
useNewIO Should the driver use the java.nio.* interfaces for network communication (true/false), defaults to 'false' No false
useReadAheadInput Use newer, optimized non-blocking, buffered input stream when reading from the server? No true
Debuging/Profiling
logger The name of a class that implements 'com.mysql.jdbc.log.Log' that will be used to log messages to.(default is 'com.mysql.jdbc.log.StandardLogger', which logs to STDERR) No com.mysql.jdbc.log.StandardLogger
profileSQL Trace queries and their execution/fetch times to the configured logger (true/false) defaults to 'false' No false
profileSql Deprecated, use 'profileSQL' instead. Trace queries and their execution/fetch times on STDERR (true/false) defaults to 'false' No
maxQuerySizeToLog Controls the maximum length/size of a query that will get logged when profiling or tracing No 2048
packetDebugBufferSize The maximum number of packets to retain when 'enablePacketDebug' is true No 20
slowQueryThresholdMillis If 'logSlowQueries' is enabled, how long should a query (in ms) before it is logged as 'slow'? No 2000
useUsageAdvisor Should the driver issue 'usage' warnings advising proper and efficient usage of JDBC and MySQL Connector/J to the log (true/false, defaults to 'false')? No false
dumpQueriesOnException Should the driver dump the contents of the query sent to the server in the message for SQLExceptions? No false
enablePacketDebug When enabled, a ring-buffer of 'packetDebugBufferSize' packets will be kept, and dumped when exceptions are thrown in key areas in the driver's code No false
explainSlowQueries If 'logSlowQueries' is enabled, should the driver automatically issue an 'EXPLAIN' on the server and send the results to the configured log at a WARN level? No false
logSlowQueries Should queries that take longer than 'slowQueryThresholdMillis' be logged? No false
traceProtocol Should trace-level network protocol be logged? No false
Miscellaneous
useUnicode Should the driver use Unicode character encodings when handling strings? Should only be used when the driver can't determine the character set mapping, or you are trying to 'force' the driver to use a character set that MySQL either doesn't natively support (such as UTF-8), true/false, defaults to 'true' No false
characterEncoding If 'useUnicode' is set to true, what character encoding should the driver use when dealing with strings? (defaults is to 'autodetect') No
characterSetResults Character set to tell the server to return results as. No
connectionCollation If set, tells the server to use this collation via 'set connection_collation' No
allowNanAndInf Should the driver allow NaN or +/- INF values in PreparedStatement.setDouble()? No false
autoDeserialize Should the driver automatically detect and de-serialize objects stored in BLOB fields? No false
capitalizeTypeNames Capitalize type names in DatabaseMetaData? (usually only useful when using WebObjects, true/false, defaults to 'false') No false
clobberStreamingResults This will cause a 'streaming' ResultSet to be automatically closed, and any oustanding data still streaming from the server to be discarded if another query is executed before all the data has been read from the server. No false
continueBatchOnError Should the driver continue processing batch commands if one statement fails. The JDBC spec allows either way (defaults to 'true'). No true
emulateLocators N/A No false
ignoreNonTxTables Ignore non-transactional table warning for rollback? (defaults to 'false'). No false
jdbcCompliantTruncation Should the driver throw java.sql.DataTruncation exceptions when data is truncated as is required by the JDBC specification when connected to a server that supports warnings(MySQL 4.1.0 and newer)? No true
maxRows The maximum number of rows to return (0, the default means return all rows). No -1
pedantic Follow the JDBC spec to the letter. No false
relaxAutoCommit If the version of MySQL the driver connects to does not support transactions, still allow calls to commit(), rollback() and setAutoCommit() (true/false, defaults to 'false')? No false
rollbackOnPooledClose Should the driver issue a rollback() when the logical connection in a pool is closed? No true
serverTimezone Override detection/mapping of timezone. Used when timezone from server doesn't map to Java timezone No
strictFloatingPoint Used only in older versions of compliance test No false
strictUpdates Should the driver do strict checking (all primary keys selected) of updatable result sets (true, false, defaults to 'true')? No true
tinyInt1isBit Should the driver treat the datatype TINYINT(1) as the BIT type (because the server silently converts BIT -> TINYINT(1) when creating tables)? No true
ultraDevHack Create PreparedStatements for prepareCall() when required, because UltraDev is broken and issues a prepareCall() for _all_ statements? (true/false, defaults to 'false') No false
useHostsInPrivileges Add '@hostname' to users in DatabaseMetaData.getColumn/TablePrivileges() (true/false), defaults to 'true'. No true
useOldUTF8Behavior Use the UTF-8 behavior the driver did when communicating with 4.0 and older servers No false
useOnlyServerErrorMessages Don't prepend 'standard' SQLState error messages to error messages returned by the server. No true
useServerPrepStmts Use server-side prepared statements if the server supports them? (defaults to 'true'). No true
useSqlStateCodes Use SQL Standard state codes instead of 'legacy' X/Open/SQL state codes (true/false), default is 'true' No true
useStreamLengthsInPrepStmts Honor stream length parameter in PreparedStatement/ResultSet.setXXXStream() method calls (true/false, defaults to 'true')? No true
useTimezone Convert time/date types between client and server timezones (true/false, defaults to 'false')? No false
useUnbufferedInput Don't use BufferedInputStream for reading data from the server No true
zeroDateTimeBehavior What should happen when the driver encounters DATETIME values that are composed entirely of zeroes (used by MySQL to represent invalid dates)? Valid values are 'exception', 'round' and 'convertToNull'. No exception

Connector/J also supports access to MySQL via named pipes on Windows NT/2000/XP using the 'NamedPipeSocketFactory' as a plugin-socket factory via the 'socketFactory' property. If you don't use a 'namedPipePath' property, the default of '\\.\pipe\MySQL' will be used. If you use the NamedPipeSocketFactory, the hostname and port number values in the JDBC url will be ignored.

Adding the following property to your URL will enable the NamedPipeSocketFactory:

socketFactory=com.mysql.jdbc.NamedPipeSocketFactory

Named pipes only work when connecting to a MySQL server on the same physical machine as the one the JDBC driver is being used on. In simple performance tests, it appears that named pipe access is between 30%-50% faster than the standard TCP/IP access.

You can create your own socket factories by following the example code in com.mysql.jdbc.NamedPipeSocketFactory , or com.mysql.jdbc.StandardSocketFactory .

posted on 2006-02-18 11:10 乐天 阅读(3602) 评论(0)  编辑  收藏

只有注册用户登录后才能发表评论。
网站导航:
博客园   IT新闻   Chat2DB   C++博客   博问