How to use a MySQL database in Java

Preamble

​​In this article, we will learn how to connect to a MySQL database from Java code and execute simple queries to retrieve and update data. In order to access the database, we will use the JDBC (Java Database Connectivity) API, which is part of the standard Java library.

JDBC allows connecting to any database: Postgres, MySQL, SQL Server, Oracle, etc. – in the presence of the appropriate driver implementation required for connection. For the MySQL database, we will use the Type 4 JDBC driver from the mysql-connector-java-5.1.23-bin.jar package.

It is written in pure Java, which means we will not need any native libraries or ODBC bridge. All we will need to do is put the JAR file in the directory contained in CLASSPATH. The JAR file contains the class com.mysql.jdbc.The driver needed to connect to MySQL. If it does not appear in CLASSPATH, the java.lang.classNotFoundException will be thrown at runtime, so make sure you set the paths correctly.

By the way, if you are looking for a good book on using JDBC, pay attention to Practical Database Programming with Java (Ying Bai). This is a relatively new book, and it covers two of the most popular databases: Oracle and SQL Server 2008. The book uses NetBeans IDE for examples and describes all the tools needed to work with databases in Java. This is an excellent book for beginners and experienced programmers.

Connect a MySQL database using JDBC

In order to connect a MySQL database, we need four things:

• The JDBC connection string (for example: jdbc:mysql://localhost:3306/test).
• Username (root).
• Password (root).
• Database with some number of tables for example (e.g. book database).

The connection string for MySQL starts with jdbc:mysql. This is the name of the connection protocol, followed by the host and connection port on which the database is running. In our case, it is localhost with a default port of 3306 (unless you changed it during installation). The next part is the test – the name of the database, which already exists in MySQL. We can create a Books table:

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

and fill it with good books:

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');

A Java program that uses a database of

Now let’s write a Java program, which will connect to our database running on the localhost. It is important to remember that you need to close the connection, queries, and the result of execution after you have finished working with them.

It is also important to close them in the final block, with its own try/catch wrapper, because the close() method itself may throw an exception, which will lead to a leak of resources. For more information, you can refer to this article. In addition, you can use the try-with-resource wrapper, which appeared in Java 7. Moreover, it is a standard way to work with resources in 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 for opening and managing connections
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 */ }
}
}

}

The first time you run it, you may have a No suitable driver found for jdbc:mysql if the MySQL driver is not in 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

Add the desired JAR file to the path and run the program again. Another common error is to specify a table in the connection line: jdbc:mysql://localhost:3306/test/book. In this case, the following exception will be thrown:

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)

Successful launch of the program will display the following:

Total number of books in the table: 2

The result is correct because we have only two books on the table: “Effective Java” and “Java Concurrency in Practice”.

By the way, if you had a driver at compile-time, but it wasn’t at run time, you will get the exception java.lang.ClassNotFoundException: com.mysql.jdbc.Driver. You can read about how to fix this bug here.

We get the data with a SELECT query in JDBC

To receive data from the database, you can make a SELECT query. In the first example, we already used it, but only got the number of lines. Now we will return the rows themselves. Most of the program will remain unchanged, except for the SQL query and the code returning data from the ResultSet object:

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);
}

This code will display the following:

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

There are a couple of points to pay attention to. The rs.getInt(1) method is used to get a column with an integer type, in our case, it is an “id” column. Indexes in JDBC start with one, so rs.getInt(1) will return the first column as an integer.

In case you specify an incorrect index (many developers call rs.getInt(0) to get the first column), an InvalidColumnIndexException exception will be thrown. Accessing columns by an index is fraught with errors, so it is better to use a column name like rs.getInt(“id”).

You can read more about this in this article. The getString() method is used to get string values from the base (e.g. VARCHAR). The loop will be executed until rs.next() returns false. This means that the lines have run out. In our case, there are two rows in the table, so the cycle will be executed twice, displaying information about the books from the table.

We add the data with an INSERT query to JDBC

Adding data is a little different from getting it: we just use the INSERT query instead of the SELECT query and the executeUpdate() method instead of executeQuery(). This method is used for INSERT, UPDATE and DELETE queries as well as for SQL DDL expressions such as CREATE, ALTER, or DROP.

These commands do not return any result, so we remove all ResultSet references in the code and modify the query accordingly:

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

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

After starting the program, you can check the table in the DBMS. This time you will see three entries in the table:

Now you can connect to MySQL from a Java application and execute SELECT, INSERT, DELETE and UPDATE queries in the same way as in MySQL GUI. We use the Connection object for connection, ResultSet object for reading query results.

Make sure that the MySQL server is running and that mysql-connector-java-5.1.17-bin.jar is in CLASSPATH before connecting to avoid ClassNotFoundException.

When you understand the connection and simple queries, it makes sense to learn how to use Prepared Statements in Java to avoid SQL injection. In combat code, you should always use pre-prepared queries and variable binding.

Java Connect to MySQL Database Step by Step

Enteros

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.