Главная » Языки программирования

Java database connection jdbc and mysql

Содержание
  1. Connect Java to a MySQL database
  2. Establishing a Connection
  3. Using the DriverManager Class
  4. Specifying Database Connection URLs
  5. Java DB Database Connection URLs
  6. MySQL Connector/J Database URL

Connect Java to a MySQL database

Here’s a step by step explanation how to install MySQL and JDBC and how to use it:

  1. Download and install the MySQL server. Just do it the usual way. Remember the port number whenever you’ve changed it. It’s by default 3306 .
  2. Download the JDBC driver and put in classpath, extract the ZIP file and put the containing JAR file in the classpath. The vendor-specific JDBC driver is a concrete implementation of the JDBC API (tutorial here). If you’re using an IDE like Eclipse or Netbeans, then you can add it to the classpath by adding the JAR file as Library to the Build Path in project’s properties. If you’re doing it «plain vanilla» in the command console, then you need to specify the path to the JAR file in the -cp or -classpath argument when executing your Java application. 
java -cp .;/path/to/mysql-connector.jar com.example.YourClass
 CREATE DATABASE javabase DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; 
 CREATE USER 'java'@'localhost' IDENTIFIED BY 'password'; GRANT ALL ON javabase.* TO 'java'@'localhost' IDENTIFIED BY 'password'; 
jdbc:mysql://hostname:port/databasename
  • hostname : The hostname where MySQL server is installed. If it’s installed at the same machine where you run the Java code, then you can just use localhost . It can also be an IP address like 127.0.0.1 . If you encounter connectivity problems and using 127.0.0.1 instead of localhost solved it, then you’ve a problem in your network/DNS/hosts config.
  • port : The TCP/IP port where MySQL server listens on. This is by default 3306 .
  • databasename : The name of the database you’d like to connect to. That’s javabase .

So the final URL should look like:

jdbc:mysql://localhost:3306/javabase
 String url = "jdbc:mysql://localhost:3306/javabase"; String username = "java"; String password = "password"; System.out.println("Connecting database. "); try (Connection connection = DriverManager.getConnection(url, username, password)) < System.out.println("Database connected!"); >catch (SQLException e)

If you get a SQLException: No suitable driver , then it means that either the JDBC driver wasn’t autoloaded at all or that the JDBC URL is wrong (i.e. it wasn’t recognized by any of the loaded drivers). Normally, a JDBC 4.0 driver should be autoloaded when you just drop it in runtime classpath. To exclude one and other, you can always manually load it as below:

 System.out.println("Loading driver. "); try < Class.forName("com.mysql.jdbc.Driver"); System.out.println("Driver loaded!"); >catch (ClassNotFoundException e)
  1. IP address or hostname in JDBC URL is wrong.
  2. Hostname in JDBC URL is not recognized by local DNS server.
  3. Port number is missing or wrong in JDBC URL.
  4. DB server is down.
  5. DB server doesn’t accept TCP/IP connections.
  6. DB server has run out of connections.
  7. Something in between Java and DB is blocking connections, e.g. a firewall or proxy.

To solve the one or the other, follow the following advices:

  1. Verify and test them with ping .
  2. Refresh DNS or use IP address in JDBC URL instead.
  3. Verify it based on my.cnf of MySQL DB.
  4. Start the DB.
  5. Verify if mysqld is started without the —skip-networking option .
  6. Restart the DB and fix your code accordingly that it closes connections in finally .
  7. Disable firewall and/or configure firewall/proxy to allow/forward the port.

Note that closing the Connection is extremely important. If you don’t close connections and keep getting a lot of them in a short time, then the database may run out of connections and your application may break. Always acquire the Connection in a try-with-resources statement. Or if you’re not on Java 7 yet, explicitly close it in finally of a try-finally block. Closing in finally is just to ensure that it get closed as well in case of an exception. This also applies to Statement , PreparedStatement and ResultSet .

That was it as far the connectivity concerns. You can find here a more advanced tutorial how to load and store fullworthy Java model objects in a database with help of a basic DAO class.

Using a Singleton Pattern for the DB connection is a bad approach. See among other questions: Is it safe to use a static java.sql.Connection instance in a multithreaded system?. This is a #1 starters mistake.

Источник

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]*
Оцените статью
© 2024 Программирование