Database connection url in java

JDBC Database Connection URLs for Common Databases

This post lists JDBC database URLs for common databases so you can refer when programming database in Jjava.

You know, when working with a database system via JDBC, the following information is required for making connection to the database:

• • Driver class name: is name of the class that implements java.sql.Driver interface. The JDBC’s driver manager needs to load this class in order to work with the database driver.
  • Database URL: a string that contains information about the database to connect to and other configuration properties. This string has its own format and is varied among different databases.

  1. JDBC Database URL for MySQL

  jdbc:mysql://[ host ][, failoverhost . ][: port ]/[ database ]

  [? propertyName1 ][= propertyValue1 ][& propertyName2 ][= propertyValue2 ].

  See more information about MySQL connection properties.

  • • Examples:
      • jdbc:mysql://localhost:3306/test
      • jdbc:mysql://localhost:3306/test?user=root&password=secret
      • • View JDBC connection code example for MySQL.

        2. JDBC Database URL for SQL Server

        • • Driver class name: com.microsoft.sqlserver.jdbc.SQLServerDriver
          • Format of database URL:

          jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

          jdbc:sqlserver://localhost;integratedSecurity=true;

          jdbc:sqlserver://localhost\\sqlexpress;integratedSecurity=true

          jdbc:sqlserver://localhost\\sqlexpress;user=sa;password=secret

          3. JDBC Database URL for Oracle

          where drivertype can be thin, oci or kprb.

          jdbc:oracle:thin:@localhost:1521:testdb

          jdbc:oracle:thin:root/secret@localhost:1521:testdb

          jdbc:oracle:oci:@hoststring

          jdbc:oracle:oci:@localhost:1521:testdb

          jdbc:oracle:oci:root/secret@hoststring>

          Jdbc:oracle:oci:root/secret@localhost:1521:testdb

          4. JDBC Database URL for PostgreSQL

          jdbc:postgresql: database

          jdbc:postgresql:// host / database

          jdbc:postgresql:// host : port / database

          jdbc:postgresql://localhost/testdb

          jdbc:postgresql://localhost:5432/testdb

          5. JDBC Database URL for JavaDB (Apache Derby)

           jdbc:derby:[subsubprotocol:][databaseName][;attribute=value]*

          where subsubprotocol can take one of the following values: directory (default), memory, classpath, and jar.

          • • Examples:
              • jdbc:derby:testdb
              • jdbc:derby:centraldb/sales
              • jdbc:derby:sample;create=true
              • jdbc:derby:memory:testdb
              • jdbc:derby:classpath:testdb
              • jdbc:derby:directory:testdb
              • jdbc:derby:jar(C:/testdb.jar)/market/asia

              6. JDBC Database URL for SQLite

              Because there is no official JDBC driver for SQLite from www.sqlite.org, the following information is applied for a SQLite JDBC driver provided by www.xerial.org.

              jdbc:sqlite:database_file_path

              Where database_file_path points to relative path or absolute path of a SQLite database file.

              Syntax for in-memory SQLite database:

              NOTE: From JDBC 4.0, loading the driver class explicitly is no longer needed, so you don’t need to care about the driver class name.

              7. JDBC Database URL for H2

              Database URLs for Embedded Mode:

              • jdbc:h2:~/test (‘test’ database in user home directory)
              • jdbc:h2:/data/test (‘test’ database in /data directory — Unix)
              • jdbc:h2:D:/data/test (‘test’ database in D:/data directory — Windows)
              • jdbc:h2:./test (‘test’ datase in current directory)

              Database URLs for In-Memory Mode:

              • jdbc:h2:mem:test (multiple connections in one process)
              • jdbc:h2:mem: (unamed private; one connection)

              Database URLs for Server Mode:

              • jdbc:h2:tcp://localhost/~/test (‘test’ database in user home)
              • jdbc:h2:tcp://localhost//data/test (‘test’ database in /data directory — Unix)
              • jdbc:h2:tcp://localhost/D:/data/tes t (‘test’ database in D:/data directory — Windows)

              Related JDBC Tutorials:

              • JDBC Drivers Download
              • How to connect to a database with JDBC
              • JDBC Create, Retrieve, Update and Delete (CRUD) Tutorial
              • JDBC Transaction Tutorial
              • How to call stored procedure with JDBC
              • How to read database metadata in JDBC
              • How to insert binary data into database with JDBC
              • How to read binary data from database with JDBC
              • How to use Scrollable ResultSet
              • How to use Updatable ResultSet
              • How to use CachedRowSet

              About the Author:

              

              Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

              Источник

              Establishing a Connection

              First, you need to establish a connection with the data source you want to use. A data source can be a DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver. Typically, a JDBC application connects to a target data source using one of two classes:

              • DriverManager : This fully implemented class connects an application to a data source, which is specified by a database URL. When this class first attempts to establish a connection, it automatically loads any JDBC 4.0 drivers found within the class path. Note that your application must manually load any JDBC drivers prior to version 4.0.
              • DataSource : This interface is preferred over DriverManager because it allows details about the underlying data source to be transparent to your application. A DataSource object’s properties are set so that it represents a particular data source. See Connecting with DataSource Objects for more information. For more information about developing applications with the DataSource class, see the latest The Java EE Tutorial.

              Note: The samples in this tutorial use the DriverManager class instead of the DataSource class because it is easier to use and the samples do not require the features of the DataSource class.

              This page covers the following topics:

              Using the DriverManager Class

              Connecting to your DBMS with the DriverManager class involves calling the method DriverManager.getConnection . The following method, JDBCTutorialUtilities.getConnection , establishes a database connection:

              public Connection getConnection() throws SQLException < Connection conn = null; Properties connectionProps = new Properties(); connectionProps.put("user", this.userName); connectionProps.put("password", this.password); if (this.dbms.equals("mysql")) < conn = DriverManager.getConnection( "jdbc:" + this.dbms + "://" + this.serverName + ":" + this.portNumber + "/", connectionProps); >else if (this.dbms.equals("derby")) < conn = DriverManager.getConnection( "jdbc:" + this.dbms + ":" + this.dbName + ";create=true", connectionProps); >System.out.println("Connected to database"); return conn; >

              The method DriverManager.getConnection establishes a database connection. This method requires a database URL, which varies depending on your DBMS. The following are some examples of database URLs:

              1. MySQL: jdbc:mysql://localhost:3306/ , where localhost is the name of the server hosting your database, and 3306 is the port number
              2. Java DB: jdbc:derby:testdb;create=true , where testdb is the name of the database to connect to, and create=true instructs the DBMS to create the database. Note: This URL establishes a database connection with the Java DB Embedded Driver. Java DB also includes a Network Client Driver, which uses a different URL.

              This method specifies the user name and password required to access the DBMS with a Properties object.

              • Typically, in the database URL, you also specify the name of an existing database to which you want to connect. For example, the URL jdbc:mysql://localhost:3306/mysql represents the database URL for the MySQL database named mysql . The samples in this tutorial use a URL that does not specify a specific database because the samples create a new database.
              • In previous versions of JDBC, to obtain a connection, you first had to initialize your JDBC driver by calling the method Class.forName . This methods required an object of type java.sql.Driver . Each JDBC driver contains one or more classes that implements the interface java.sql.Driver . The drivers for Java DB are org.apache.derby.jdbc.EmbeddedDriver and org.apache.derby.jdbc.ClientDriver , and the one for MySQL Connector/J is com.mysql.cj.jdbc.Driver . See the documentation of your DBMS driver to obtain the name of the class that implements the interface java.sql.Driver . Any JDBC 4.0 drivers that are found in your class path are automatically loaded. (However, you must manually load any drivers prior to JDBC 4.0 with the method Class.forName .)

              The method returns a Connection object, which represents a connection with the DBMS or a specific database. Query the database through this object.

              Specifying Database Connection URLs

              A database connection URL is a string that your DBMS JDBC driver uses to connect to a database. It can contain information such as where to search for the database, the name of the database to connect to, and configuration properties. The exact syntax of a database connection URL is specified by your DBMS.

              Java DB Database Connection URLs

              The following is the database connection URL syntax for Java DB:

              jdbc:derby:[subsubprotocol:][databaseName][;attribute=value]*

              • subsubprotocol specifies where Java DB should search for the database, either in a directory, in memory, in a class path, or in a JAR file. It is typically omitted.
              • databaseName is the name of the database to connect to.
              • attribute=value represents an optional, semicolon-separated list of attributes. These attributes enable you to instruct Java DB to perform various tasks, including the following:
                • Create the database specified in the connection URL.
                • Encrypt the database specified in the connection URL.
                • Specify directories to store logging and trace information.
                • Specify a user name and password to connect to the database.

                See Java DB Developer’s Guide and Java DB Reference Manual from Java DB Technical Documentation for more information.

                MySQL Connector/J Database URL

                The following is the database connection URL syntax for MySQL Connector/J:

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

                • host:port is the host name and port number of the computer hosting your database. If not specified, the default values of host and port are 127.0.0.1 and 3306, respectively.
                • database is the name of the database to connect to. If not specified, a connection is made with no default database.
                • failover is the name of a standby database (MySQL Connector/J supports failover).
                • propertyName=propertyValue represents an optional, ampersand-separated list of properties. These attributes enable you to instruct MySQL Connector/J to perform various tasks.

                Источник

                Читайте также:  Printing exceptions in python