No suitable driver found for jdbc mysql ошибка

The error «java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test» occurs when you try to connect MySQL database running on your localhost, listening on port 3306 port from Java program but either you don’t have MySQL JDBC driver in your classpath or driver is not registered before calling the getConnection() method. Since JDBC API is part of JDK itself, when you write a Java program to connect any database like MySQL, SQL Server, or Oracle, everything compiles fine, as you only use classes from JDK but at runtime, when the JDBC driver which is required to connect to the database is not available, JDBC API either throws this error or «java.lang.ClassNotFoundException: com.mysql.jdbc.Driver».

The most common reason for this error is missing MySQL JDBC Driver JAR e.g. mysql-connector-java-5.0.8.jar not available in the classpath. Another common reason is you are not registering the driver before calling the getConnection() and you are running on Java version lower than 6 and not using a JDBC 4.0 compliant driver. We’ll see these reasons in more detail in this article.

Btw, if you are new to JDBC and looking for a comprehensive online course to learn JDBC in-depth then I also suggest you check out these Complete JDBC Programming course on Udemy. It’s a great course of direct classroom lectures and covers JDBC in depth

JAR not available in Classpath

If mysql-connector-java-5.0.8.jar is not available in classpath then you cannot connect to MySQL database from Java. Your program like below will compile fine but as soon as you will run it you will get the error «java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test» because of the JDBC URL format «jdbc:mysql» is not matching with any registered JDBC driver.

Here is our Java program to demonstrate this error. This program reproduces this error by first leaving out the required JDBC JAR from the classpath and also not explicitly registering the driver before use by not calling the Class.forName() method.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/*
 * Java Program to to connect to MySQL database and
 * fix java.sql.SQLException: No suitable driver found 
 * for jdbc:mysql://localhost:3306
 * error which occur if JAR is missing or you fail to register driver.
 */

public class Main {

  public static void main(String[] args) {

    Connection dbConnection = null;

    try {
      String url = "jdbc:mysql://localhost:3306/test";
      Properties info = new Properties();
      info.put("user", "root");
      info.put("password", "test");

      dbConnection = DriverManager.getConnection(url, info);

      if (dbConnection != null) {
        System.out.println("Successfully connected to MySQL database test");
      }

    } catch (SQLException ex) {
      System.out.println("An error occurred while connecting MySQL databse");
      ex.printStackTrace();
    }

  }

}

Output
An error occurred while connecting MySQL databse
java.sql.SQLException: No suitable driver found 
for jdbc:mysql://localhost:3306/test
at java.sql.DriverManager.getConnection(DriverManager.java:596)
at java.sql.DriverManager.getConnection(DriverManager.java:187) 

at Main.main(Main.java:24))

You need to do two things in order to solve this problem:

1) Add mysql-connector-java-5.0.8.jar or any other MySQL JAR corresponding to the MySQL database you are connecting. If you don’t have MySQL JDBC driver, you can download from here http://dev.mysql.com/downloads/connector/j/3.1.html

2) Add following line of code just before the call to Connection.getConnection(url, props) method

// load and register JDBC driver for MySQL
Class.forName("com.mysql.jdbc.Driver"); 

This will load the class, the JDBC driver to connect MySQL, com.mysql.jdbc.Driver from mysql-connector-java-5.0.8.jar and register it with JDBC API. Once you do that «java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test» will go away.

Also, it’s worth noting that JDBC 4.0 released with Java SE 6 has now introduced auto-loading of JDBC driver class, which means you don’t need Class.forName(«com.mysql.jdbc.Driver»); any more, but only when you are running on at least Java 6 and your driver JAR is also JDBC 4.0 compliant

For example, the driver used in this program «mysql-connector-java-5.0.8.jar» is not JDBC 4.0 compliant, so even if you run this program in Java 6, 7 or Java 8, it will not work, but if you use mysql-connector-java-5.1.36.jar then even without adding «Class.forName(«com.mysql.jdbc.Driver»);», your program will work fine. Why? because JDBC will automatically load and register the driver, provided you have mysql-connector-java-5.1.36.jar file in your classpath. . See Core Java Volume 2 — Advanced features to learn more about new features introduces in JDBC 3.0 and JDBC 4.0 releases.

That’s all about how to solve «java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test» error in Java program. This error occurs if JDBC is not able to find a suitable driver for the URL format passed to the getConnection() method e.g. «jdbc:mysql://» in our case. 

Make sure this JAR is available in classpath before running your Java program, otherwise Class.forName() will not be able to find and load the class and throw java.lang.ClassNotFoundException: com.mysql.jdbc.Driver, another dreaded JDBC error, which we have seen in the earlier post.

Recently I have seen a common pattern of this error where a Java developer running his program on a version higher than Java SE 6 expects that JDBC driver’s JAR will be automatically loaded by JVM because of autoloading of JDBC driver feature of JDBC 4.0 released in JDK 6 but misses the trick that the JDBC driver should also be JDBC 4.0 compliant like  mysql-connector-java-5.1.18-bin.jar will be automatically loaded but older version may not, even if you run on Java 6. 

So, make sure you have both JDK 6 and a JDBC 4.0 compliant driver to leverage the auto-loading feature of JDBC 4.0 specification.  You can further see these free JDBC courses to learn more about JDBC 4.0 features.

How to reproduce the «No suitable driver found for ‘jdbc:mysql://localhost:3306/» Error in Java?

In order to better understand this error, let’s first reproduce this error by executing following Java program. I expect this program to throw the «No suitable driver found for ‘jdbc:mysql://localhost:3306/» error because I don’t have JDBC driver in the classpath.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/*
 * Java Program to to reproduce
 * java.sql.SQLException: No suitable driver found 
 * for jdbc:mysql://localhost:3306
 * error which occurs if MySQL JDBC Driver JAR is missing
 * or you not registering the JDBC driver before calling 
 * DriverManager.getConnection() method in JDBC.
 */

public class MySQLTest {

    public static void main(String[] args) throws ClassNotFoundException {

        Connection con = null;

        try {
            String url = "jdbc:mysql://localhost:3306/mysql";
            String username = "root";
            String password = "root";

            // Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(url, username, password);

            if (con != null) {
                System.out
                        .println("Successfully connected to
                             MySQL database test");
            }

        } catch (SQLException ex) {
            System.out
                    .println("An error occurred while 
                           connecting MySQL databse");
            ex.printStackTrace();
        }

    }

}

Output
An error occurred while connecting MySQL databse
java.sql.SQLException: No suitable driver found 
for jdbc:mysql://localhost:3306/mysql
at java.sql.DriverManager.getConnection(DriverManager.java:596)
at java.sql.DriverManager.getConnection(DriverManager.java:215)

You can see that we got the «No suitable driver found» error in JDBC. The reason was that JDBC API couldn’t find any Driver corresponding to «jdbc:mysql://» URL because we have not added the MySQL connector JAR which contains the JDBC driver required to connect to MySQL database.

How to solve «No suitable driver found for jdbc:mysql://localhost:3306/mysql»

You can solve this problem first by adding MySQL connector JAR, which includes JDBC driver for MySQL into classpath e.g. mysql-connector-java-5.1.18-bin.jar. It’s very easy, just download the JAR from MySQL website and drop it into your classpath. For Example, if you are running in Eclipse then you can drop it on the root of your project folder. Same is true for Netbeans, but if you are running your Java program from the command prompt then either use -cp option or set the CLASSPATH as described here.

I prefer -cp option because it’s simple and easy and you can see what is included in classpath right in the command line itself, no need to worry about whether JAR is included in CLASSPATH environment variable or not.

java -cp mysql-connector-java-5.1.18-bin.jar:. MySQLTest

The error should go away just by adding JDBC driver in the classpath if you are running on Java 6, which supports JDBC 4.0 and the driver is also JDBC 4.0 compliant e.g. mysql-connector-java-5.1.36-bin.jar. From JDBC 4.0, Java has introduced auto loading of JDBC driver, hence you don’t need to load or register it manually using Class.forName() method.

If you are not running on Java SE 6 or your JDBC driver version doesn’t support JDBC 4.0 then just add the following line before calling DriverManager.getConnection() to load and register the MySQL JDBC driver. This will solve the problem (uncomment the line in above program):

Class.forName("com.mysql.jdbc.Driver");

This throws checked java.lang.ClassNotFoundException so makes sure you catch it. I have not caught it to keep the code clutter free by introducing try and catch statement.

So, in short:

1) Just add the MySQL JDBC JAR into classpath if you are running on Java SE 6 and driver is JDBC 4.0 compliant e.g. mysql-connector-java-5.1.36-bin.jar.

2) Alternatively, add the MySQL JDBC driver to classpath e.g. mysql-connector-java-5.1.18-bin.jar and call the Class.forName(«com.mysql.jdbc.Driver»); to load and register the driver before calling DriverManager.getConnection() method.

You can also check out JDBC API Tutorial and Reference (3rd Edition) to learn more about new features introduced in JDBC 3.0 and JDBC 4.0 specification and it is also one of the best books to learn JDBC API in Java.

That’s all about how to fix «No suitable driver found for jdbc:mysql://localhost:3306/mysql» error in Java. You can get this error from Eclipse or NetBeans IDE while connecting to local MySQL instance listening on default port 3306, don’t afraid, just follow the same approach. Drop the MySQL JDBC driver and call the Class.forName() method with the name of the class with implements Driver interface from JDBC API.

Related JDBC Tutorials for Java Programmers

1. How to connect to MySQL database from Java Program (Guide)
2. How to connect to Oracle database from Java (Guide)
3. How to connect to Microsoft SQL Server from Java (Guide)
4. How to setup JDBC connection Pool in Spring + Tomcat (Guide)
5. 10 JDBC Best Practices Java Programmer Should Follow (see here)
6. 6 JDBC Performance Tips for Java Applications (see here)

No suitable driver found for JDBC is an exception in Java that generally occurs when any driver is not found for making the database connectivity. In this section, we will discuss why we get such an error and what should be done to get rid of this exception so that it may not occur the next time.

Before discussing the exception, we should take a brief knowledge that what is a JDBC Driver.

What is a JDBC Driver

The JDBC (Java Database Connectivity) Driver is a driver that makes connectivity between a database and Java software. The JDBC driver can be understood as a driver that lets the database and Java application interact with each other. In JDBC, there are four different types of drivers that are to be used as per the requirement of the application. These JDBC divers are:

1. JDBC-ODBC bridge driver
2. Thin Layer driver
3. Native API driver
4. Network Protocol Driver

All four drivers have their own usage as well as pros and cons. To know more about JDBC Drivers, do visit: https://www.javatpoint.com/jdbc-driver section of our Java tutorial.

What is the Error and Why it Occurs?

Generally, «no suitable driver found» refers to throwing an error, i.e., «java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test» in the console. The error occurs when we are trying to connect to the MySql (or any other) database that is existing on your local machine, i.e., localhost, and listens to the specified port number which is set for the mysql and it founds that either no JDBC driver was registered before invoking the DriverManager.getConnection () method or we might not have added the MySQL JDBC driver to the classpath in the IDE. In case we are running a simple Java code with no requirement of database connectivity, the Java API executes it correctly and well, but if there is the need for a JDBC driver, an error is thrown, which is the «class not found» error. In simple words, such an error is thrown when no suitable driver is found by the Java API so that it could connect the Java application to the database.

How to remove the error

Now the question is how to get rid of such error. In order to resolve the problem or error, one needs to add the MYSQL Connector JAR to the classpath because the classpath includes the JDBC Driver for the MYSQL through which the connection is generated between the Java code and the database. In order to add the MYSQL connector JAR file to the IDE or tool we are using, we need to go through some quite simple steps. These steps are as follows:

For Eclipse and NetBeans IDE

1) Open any internet browser on the system and search for MySQL Connector download in the search tab. Several downloading links will appear. Click on the MYSQL website https://www.mysql.com/products/connector/ from it and download the latest version of the MYSQL connector by selecting your system specs.

2) After the successful download of the MYSQL Connector, it will be seen at the default Downloads folder of your system, as you can see in the below snippet:

3) Now, open the IDE you are working upon, either NetBeans or Eclipse, and also any other tool/IDE, whichever you use. Here, we have used Eclipse IDE.

4) Go to your project and right-click on it. A list of options will appear. Select and click on Build Path > Configure Build Path, and the Java Build Path dialog box will open up, as you can see in the below snippet:

5) Click on Add External JARs and move to the location where you have downloaded the Mysql Connector, as you can see in the below snippet:

6) Select the Mysql Connector and click on Open. The JAR file will get added to your project build path, as you can see in the below snippet:

7) Click on Apply and Close, and the JDBC Driver will be added to your Eclipse IDE.

8) Run the JDBC connection code once again, and this time you will not get the «No suitable driver found for JDBC» exception instead of other errors if you made any other syntax problem.

9) The JDBC Driver will get connected successfully, and the connection will get established successfully.

Note: If you want to know how to make JDBC Connectivity in Java, visit https://www.javatpoint.com/example-to-connect-to-the-mysql-database

Point to be noted:

• If you are using Java SE 6 with JDBC 4.0, then you may not require to load and register the driver because the new Java feature provides autoloading of the JDBC driver class. Due to which there is no requirement of using Class.forName(«com.mysql.jdbc.Driver»); statement. However, if the JDBC Jar you are using is old, i.e., JDBC 4.0 compliant with Java SE 6, then you may need to create this statement.
• In brief, we can say that such an error occurs when no JDBC JAR file is added to the classpath of Java. Just we need to add the JAR file to the classpath and then execute the code. The code will hopefully get executed with success.

The java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/testdb exception occurs if the suitable driver is not found to connect mysql database from java application. The MySQL JDBC driver is not loaded in java either because the driver jar is not available in the class path, or because it is not possible to load the mysql driver jar. If no suitable driver is found in the java class path, the exception java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/testdb will be thrown.

The MySQL JDBC driver is used to connect your Java application to a MySQL database. The mysql driver sends the database query from java to the database. The Mysql database executes the query and returns the results. The MySQL JDBC driver receives the data from the MySQL database and sends it back to the Java application.

If the MySQL JDBC driver is not loaded, the Java program will throw the exception java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/testdb.

Exception

The stack trace of the exception will be shown as shown below. The exception java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/testdb  is due to the driver class not loaded in java.

Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/testdb
	at java.sql.DriverManager.getConnection(DriverManager.java:689)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at com.yawintutor.DBConnection.main(DBConnection.java:13)

How to reproduce this exception

If the Java application can not load the MySQL JDBC driver class or the MySQL JDBC class is not available in the Java class path, the exception java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/testdb will be thrown. This exception will be reproduced in the example below.

package com.yawintutor;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnection {
	public static void main(String[] args) throws Exception {
		String url = "jdbc:mysql://localhost:3306/testdb";
		String username = "root";
		String password = "root";

		Connection con = DriverManager.getConnection(url, username, password);
		if (con != null) {
			System.out.println("Database Connected successfully");
		} else {
			System.out.println("Database Connection failed");
		}
	}
}

Solution 1

If the MySQL JDBC driver jar is available in the class path and the driver class is unable to load, the driver class must be loaded in the java using class.forName() method. The forName() method will load the class from the fully qualified class name specified as an argument. The example below demonstrates how to load the MySQL JDBC driver class using the class.forName() method.

package com.yawintutor;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnection {
	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.cj.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/testdb";
		String username = "root";
		String password = "root";

		Connection con = DriverManager.getConnection(url, username, password);
		if (con != null) {
			System.out.println("Database Connected successfully");
		} else {
			System.out.println("Database Connection failed");
		}
	}
}

Solution 2

If you are using mysql database version till 5.x.x, the MySQL JDBC driver “com.mysql.cj.jdbc.Driver” will not be available. For the older mysql databases the driver class “com.mysql.jdbc.Driver” must be used. The java program will be as like below.

package com.yawintutor;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnection {
	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/testdb";
		String username = "root";
		String password = "root@123";

		Connection con = DriverManager.getConnection(url, username, password);
		if (con != null) {
			System.out.println("Database Connected successfully");
		} else {
			System.out.println("Database Connection failed");
		}
	}
}

Solution 3

If the MySQL JDBC driver jar is not available in the java class path, download the mysql driver jar from https://dev.mysql.com/downloads/connector/j/. The name of mysql driver jar is same as mysql-connector-java-8.0.20.jar. Add the jar to the java class path.

download the jar mysql-connector-java-8.0.20.jar from https://dev.mysql.com/downloads/connector/j/ 
( 
  goto https://dev.mysql.com/downloads/connector/j/
  select "Select Operating System:" as your operating system and install
      or
  select "Select Operating System:" as "Platform Independent", download the zip and extract
)

Add in your java project.

In eclipse
Right click in mysql-connector-java-8.0.20.jar -> Build Path -> Add to Build Path

Solution 4

If the java project is a maven project, add the mysql connector dependency in the pom.xml. The dependency will download the mysql-connector-java-8.0.22.jar in the repository and link to the project. The latest mysql connector dependency can be found in https://mvnrepository.com/artifact/mysql/mysql-connector-java

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.22</version>
</dependency>

Solution 5

If the java project is build using the Gradle, add the dependency as below. When the project is build, the MySQL JDBC driver will be added to the java project.

dependencies {
    compile 'mysql:mysql-connector-java:8.0.22'
}

В этой статье мы научимся подключаться к базе данных MySQL из Java-кода и выполнять простые запросы для получения и обновления данных. Для того, чтобы получить доступ к базе данных, мы будем использовать JDBC (Java Database Connectivity) API, который входит в стандартную библиотеку Java. JDBC позволяет подключиться к любой базе данных: Postgres, MySQL, SQL Server, Oracle и т. д. — при наличии соответствующей реализации драйвера, необходимого для подключения. Для базы данных MySQL мы будем использовать драйвер Type 4 JDBC из пакета mysql-connector-java-5.1.23-bin.jar. Он написан на чистой Java, а значит, нам не понадобятся какие-либо нативные библиотеки или ODBC-мост. Все, что нам надо будет сделать — это положить JAR-файл в директорию, содержащуюся в CLASSPATH. JAR-файл содержит класс com.mysql.jdbc.Driver, необходимый для подключения к MySQL. Если его не окажется в CLASSPATH, во время выполнения программы выбросится исключение java.lang.ClassNotFoundException, поэтому убедитесь, что вы правильно настроили пути.

Кстати, если вы ищете хорошую книгу по использованию JDBC, обратите внимание на Practical Database Programming with Java (Ying Bai). Это относительно новая книга, и в ней рассматриваются две самые популярные базы данных: Oracle и SQL Server 2008. В книге используется IDE NetBeans для примеров и описываются все инструменты, необходимые для работы с базами данных в Java. Это отличная книга для начинающих и опытных программистов.

Подключаем базу данных MySQL с помощью JDBC

Для того, чтобы подключить базу данных MySQL, нам потребуется четыре вещи:

1. Строка подключения JDBC (например:
   jdbc:mysql://localhost:3306/test).
2. Имя пользователя (root).
3. Пароль (root).
4. База данных с некоторым количеством таблиц для примера (например, база данных книг).

Строка подключения для MySQL начинается с jdbc:mysql. Это название протокола соединения, за которым следуют хост и порт подключения, на которых запущена база данных. В нашем случае это localhost с портом по умолчанию 3306 (если вы его не поменяли при установке). Следующая часть — test — имя базы данных, которая уже существует в MySQL. Мы можем создать таблицу Books:

CREATE TABLE `books` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `author` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

и наполнить её хорошими книгами:

INSERT INTO test.books (id, `name`, author)
    VALUES (1, 'Effective Java', 'Joshua Bloch');
INSERT INTO test.books (id, `name`, author)
    VALUES (2, 'Java Concurrency in Practice', 'Brian Goetz');

Программа на Java, которая использует базу данных

Теперь давайте напишем программу на Java, которая будет подключаться к нашей базе данных, запущенной на localhost. Важно помнить о том, что необходимо закрывать соединение, запросы и результат выполнения после завершения работы с ними. Также важно закрывать их в finally-блоке, со своей try/catch оберткой, поскольку сам метод close() может кинуть исключение, что приведет к утечке ресурсов. За подробной информацией вы можете обратиться к этой статье. Кроме того, вы можете использовать обертку try-with-resource, которая появилась в Java 7. Более того, это стандартный способ работы с ресурсами в Java 1.7.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Simple Java program to connect to MySQL database running on localhost and
 * running SELECT and INSERT query to retrieve and add data.
 * @author Javin Paul
 */
public class JavaToMySQL {

    // JDBC URL, username and password of MySQL server
    private static final String url = "jdbc:mysql://localhost:3306/test";
    private static final String user = "root";
    private static final String password = "root";

    // JDBC variables for opening and managing connection
    private static Connection con;
    private static Statement stmt;
    private static ResultSet rs;

    public static void main(String args[]) {
        String query = "select count(*) from books";

        try {
            // opening database connection to MySQL server
            con = DriverManager.getConnection(url, user, password);

            // getting Statement object to execute query
            stmt = con.createStatement();

            // executing SELECT query
            rs = stmt.executeQuery(query);

            while (rs.next()) {
                int count = rs.getInt(1);
                System.out.println("Total number of books in the table : " + count);
            }

        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
        } finally {
            //close connection ,stmt and resultset here
            try { con.close(); } catch(SQLException se) { /*can't do anything */ }
            try { stmt.close(); } catch(SQLException se) { /*can't do anything */ }
            try { rs.close(); } catch(SQLException se) { /*can't do anything */ }
        }
    }

}

При первом запуске у вас, возможно, будет ошибка No suitable driver found for jdbc:mysql, если драйвера MySQL нет в CLASSPATH:

java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test/book
    at java.sql.DriverManager.getConnection(DriverManager.java:689)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at JavaToMySQL.main(JavaToMySQL.java:29)
Exception in thread "main" java.lang.NullPointerException
    at JavaToMySQL.main(JavaToMySQL.java:46)
Java Result: 1

Добавим нужный JAR-файл в путь и снова запустим программу. Другая частая ошибка — указать таблицу в строке соединения: jdbc:mysql://localhost:3306/test/book. В этом случае вылетит следущее исключение:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'test/book'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance
(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance
(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)

Успешный запуск программы выведет на экран следующее:

Total number of books in the table: 2

Результат верный, поскольку у нас в таблице только две книги: «Effective Java» и «Java Concurrency in Practice».

Кстати, если у вас был драйвер при компиляции, но отсутствует при запуске, вы получите исключение java.lang.ClassNotFoundException: com.mysql.jdbc.Driver. О том, как исправить эту ошибку, вы можете прочитать здесь.

Получаем данные с помощью SELECT-запроса в JDBC

Для получения данных из БД вы можете выполнить SELECT-запрос. В первом примере мы уже его использовали, но получили только количество строк. Теперь мы вернем сами строки. Большая часть программы останется без изменений, за исключением SQL-запроса и кода, возвращающего данные из объекта ResultSet:

String query = "select id, name, author from books";

rs = stmt.executeQuery(query);

while (rs.next()) {
   int id = rs.getInt(1);
   String name = rs.getString(2);
   String author = rs.getString(3);
   System.out.printf("id: %d, name: %s, author: %s %n", id, name, author);
 }

Этот код выведет на экран следующее:

id: 1, name: Effective Java, author: Joshua Bloch
id: 2, name: Java Concurrency in Practice, author: Brian Goetz

Тут есть пара моментов, на которые следует обратить внимание. Метод rs.getInt(1) используется для получения столбца с целочисленным типом, в нашем случае это столбец «id». Индексы в JDBC начинаются с единицы, поэтому rs.getInt(1) вернет значение первого столбца как целое число. В случае, если вы укажете неверный индекс (многие разработчики вызывают rs.getInt(0) для получения первого столбца), выбросится исключение InvalidColumnIndexException. Доступ к столбцам по индексу чреват ошибками, поэтому лучше использовать имя столбца, например, rs.getInt("id"). Подробнее об этом вы можете прочитать в этой статье. Метод getString() используется для получения строковых значений из базы (например, VARCHAR). Цикл будет выполняться, пока rs.next() не вернет false. Это значит, что строки закончились. В нашем случае в таблице две строки, поэтому цикл выполнится два раза, выводя информацию о книгах из таблицы на экран.

Добавляем данные с помощью INSERT-запроса в JDBC

Добавление данных мало отличается от их получения: мы просто используем INSERT-запрос вместо SELECT-запроса и метод executeUpdate() вместо executeQuery(). Этот метод используется для запросов INSERT, UPDATE и DELETE, а также для SQL DDL выражений, таких как CREATE, ALTER или DROP. Эти команды не возвращают результата, поэтому мы убираем все упоминания ResultSet‘а в коде и изменяем запрос соответственно:

String query = "INSERT INTO test.books (id, name, author) n" +
               " VALUES (3, 'Head First Java', 'Kathy Sieara');";

// executing SELECT query
stmt.executeUpdate(query);

После запуска программы вы можете проверить таблицу в СУБД. На этот раз вы увидите три записи в таблице:

Теперь вы умеете подключаться к MySQL из Java-приложения и выполнять SELECT, INSERT, DELETE и UPDATE-запросы так же, как и в MySQL GUI. Для подключения мы используем объект Connection, для чтения результатов запроса — ResultSet. Убедитесь перед подключением, что сервер MySQL запущен и mysql-connector-java-5.1.17-bin.jar находится в CLASSPATH, чтобы избежать ClassNotFoundException.

Когда разберетесь с подключением и простыми запросами, имеет смысл изучить, как использовать подготавливаемые запросы (Prepared Statement) в Java для избежания SQL-инъекции. В боевом коде всегда следует использовать подготавливаемые запросы и связывание переменных.

Если вам понравилось это руководство и не терпится узнать больше о подключении и работе с базой данных из Java-программ, обратите внимание на следующие статьи:

• Как подключиться к БД Oracle из Java-приложения;
• Отличия межу Connected RowSet и Disconnected RowSet в Java;
• Как использовать пул соединений в Spring;
• 5 способов улучшить производительность БД в приложениях на Java;
• Отличия между java.util.Date и java.sql.Date в Java;
• Как выполнить INSERT или UPDATE, используя пакетные запросы JDBC;
• Десять вопросов по JDBC на собеседованиях.

Полезные ссылки

• Если у вас нет базы данных MySQL, вы можете ее скачать здесь;
• Есди у вас нет драйвера MySQL для JDBC, вы можете скачать его отсюда;
• Рекомендованную книгу «Practical Database Programming with Java» можно купить на Amazon.

_{Перевод статьи «How to Connect to MySQL database in Java with Example»}