SQL, MySQL, and connecting with JDBC

Close the registration form you built two lessons ago and everything typed into it is gone. Variables live in memory, and memory is cleared the moment a program ends. Writing to a file solved part of that in Phase 4. Most real programs hand the job to a database server instead: a separate program that keeps data in tables and answers questions about it in a language called SQL. This lesson teaches enough SQL to build and query a table at the MySQL prompt, then connects Java to the same server with JDBC.

Why a database, and not a file#

In writing files you saved data as lines of text. That is fine for a high-score list. It starts to hurt once the data grows, or once more than one program needs it.

SQL: the language#

Four things to know about SQL before you type any.

• SQL stands for Structured Query Language. A query is a question put to a database. SQL reads a lot like English, because its keywords are plain English words, so a statement reads almost like a sentence: select name from JavaCourse where marks > 80says “pick the name, from the JavaCourse table, where the marks are above 80”.
• It is a language, not a program. Nothing called SQL gets installed on your computer. See the note below.
• Simple to set up and use. The language itself needs no setup. What you install is a database system such as MySQL, in the next section, and the statements you need are short, as the rest of this page shows.
• Used to create a table, enter data, update records and retrieve data. Those four jobs are the statements create table, insert, update and select. Add delete for removing rows, and you meet all five below.

One idea separates SQL from Java. In Java you write how to get a result: a loop, an index, an if. In SQL you write what result you want, and the database works out how to find it. The select above contains no loop and says nothing about the order in which rows are checked. Languages that work this way are called declarative. Once the JavaCourse table exists, further down, you can run the Java loop that one line of SQL replaces.

Key idea

SQL describes the result you want. The database decides the steps to get it.

MySQL: the database server#

SQL is the language. MySQL is a program that understands it, and the one this course uses. Ten facts about it:

• One of the most popular database management systems. It is among the most widely used in the world, from student projects to large websites.
• Used on the web. It is the M in the LAMP stack (Linux, Apache, MySQL, PHP) that a great many websites run on. WordPress, for example, keeps every post in MySQL or in its close relative, MariaDB.
• Runs on a server. MySQL is a program, mysqld, that runs in the background and waits for other programs to connect to it, by default on network port 3306. “Server” describes the role, not the size of the machine: on your laptop the server is a background process at the address localhost. The programs that connect to it are called clients: the mysql> command-line client, the graphical MySQL Workbench, and soon your own Java program.
• Ideal for small and large applications.The same server holds a class project's single table or a company's millions of rows.
• Very fast, reliable and easy to use. It installs in minutes, and the everyday statements are the handful in the next section.
• Uses standard SQL.Almost everything below is standard SQL. A few words are MySQL's own additions: show databases, use, desc, and AUTO_INCREMENT, which the next lesson uses.
• Works on a number of platforms. Windows, macOS and Linux all have it.
• Free to download and use. The Community Edition is open source, under the GPL licence. Oracle also sells an Enterprise Edition with extra tools and support.
• Developed, distributed and supported by Oracle Corporation. MySQL began at a Swedish company, MySQL AB. Sun Microsystems bought it in 2008, and Oracle bought Sun in 2010, the same purchase that made Oracle the owner of Java.
• Named after co-founder Monty Widenius's daughter, My.The “My” in MySQL is her name.

Getting to the mysql> prompt#

When you install MySQL, the installer creates an administrator account called root and asks you to choose its password. Keep that password safe: you type it to open the prompt, and your Java program will need it too. To reach the prompt, open a terminal and start the client.

Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql>

-u root names the user and -p makes the client ask for the password. On Windows, run it from the bin folder of the MySQL installation, or add that folder to your PATH first. If you would rather click than type commands, MySQL Workbench does the same job: connect as root with the same password, type SQL into a query tab, and run it with the lightning-bolt button.

The basic statements#

This section builds one small table, JavaCourse, and runs every basic statement against it. Type along at the prompt. Three rules cover the typing: every statement ends with a semicolon, keywords may be upper or lower case (CREATE and create are the same, and the examples mix both), and text values go in single quotes. In each box below, lines starting with -- are SQL comments. The first part is the general syntax; the second is an example, ready to type.

Databases: show, create, use#

One MySQL server holds many databases, and each database holds tables. Before you make a table you choose, or create, the database it will live in. Three commands do that: one lists the databases, one creates a new one, and one makes it the current one.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Database changed

• show databases; lists every database on the server. A new server already has the four above, where MySQL keeps its own records; leave them alone. Run it again after the next line and test appears as a fifth.
• create database test; makes a new, empty database called test. Running it a second time fails with Can't create database 'test'; database exists.
• use test; makes test the current database: every statement after it works inside test until you use another one. MySQL answers Database changed.

CREATE TABLE#

The table creation command needs three things: the name of the table, the name of each field (a field is a column), and a definition for each field, meaning its type and any rules.

Query OK, 0 rows affected (0.02 sec)

• Integer holds whole numbers. MySQL treats Integer and INT as the same type, which covers roughly minus 2.1 billion to plus 2.1 billion.
• Varchar(30) holds text of varying length, at most 30 characters. Debasish uses 8 and stores 8. Anything longer is refused: the two-character Grade column rejects 'EXC' with Data too long for column 'Grade' at row 1.
• primary key on Rollmakes the roll number the row's identity. No two rows may share one, and it can never be empty. A second row with roll 1 is refused with a Duplicate entry '1' error.
• not null on Marks means every row must have marks. Name and Grade have no such rule, so a row may leave them empty, which SQL calls NULL.

The reply says 0 rows affected because creating a table builds structure. It adds no rows.

DESC: the structure of a table#

DESC is shorthand for DESCRIBE, and both show how a table is built: each column, its type, its keys and its rules. Use it to check that a table came out the way you meant.

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Roll  | int         | NO   | PRI | NULL    |       |
| Name  | varchar(30) | YES  |     | NULL    |       |
| Marks | int         | NO   |     | NULL    |       |
| Grade | varchar(2)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

That is what a current MySQL prints. Older books and tutorials show the same table from MySQL 5.x:

• Field is the column name, and Type is its type.
• Null says whether the column may be left empty. Roll says NO because a primary key can never be empty; Marks says NO because of not null.
• Key shows PRI on the primary key.
• Default is the value MySQL uses when an insert leaves the column out. NULL here means no default has been set.

INSERT: adding rows#

You can add rows at the mysql> prompt, or from a program in any language that has a driver for MySQL. The prompt comes first. The program with a driver is Java with JDBC, later in this lesson.

Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)

• The values go in the same order as the columns in create table: roll, name, marks, grade. This form has no list of column names, so it must give a value for every column.
• Text goes in single quotes, 'Debasish'; numbers do not.
• 01 is the number 1 written with a leading zero, which is why the table shows 1.
• Every line ends with a semicolon. Leave one off and the prompt does not run the statement. It shows -> and waits for the rest until you type ;.

A second form names the columns it fills: insert into JavaCourse (Roll, Name, Marks) values (5, 'Asha', 70); leaves Grade empty. Programs prefer this form, because it keeps working if someone adds a column to the table later. You meet it again in the next lesson.

UPDATE: changing rows#

UPDATE changes rows that are already in a table. SET gives the new values, and WHERE picks which rows get them.

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Watch the commas: they only separate the column=value pairs after SET. A comma before WHERE, or a bracket at the end, is a syntax error, and MySQL refuses the whole statement.

• The first statement renames Debasish to Debasis. The second finds the row by its new name and changes two columns at once, separated by a comma. Run them in the other order and the second matches nothing: the prompt reports Rows matched: 0.
• Rows matched counts the rows the WHERE found; Changed counts the ones whose values actually differed. Setting marks to 85 on a row that already has 85 matches one row and changes none.
• MySQL stores exactly what you type, so Debasis now has grade Ex while Nilanjan keeps EX. With MySQL's default settings, text comparisons ignore case, so where Grade='EX' finds both rows.

SELECT: asking questions#

SELECT reads data from a table. It changes nothing. Here are five queries on the table as it stands after the updates.

Separate the column names with commas. Without them MySQL reads the second name as a new heading for the first column: select name roll from JavaCourse returns a single column, headed roll, full of names.

select * from JavaCourse; The star means every column, in the order the table defines them.

select name, roll from JavaCourse; Only the columns you list, in the order you list them. MySQL column names are not case-sensitive, so name finds the Name column. The headings keep the spelling from your query, which is why they come out in lower case.

select * from JavaCourse where marks>80; WHERE keeps only the rows where the condition is true. The comparisons are =, <> (not equal), <, >, <= and >=, and conditions can be joined with AND and OR.

This is the declarative idea from the SQL section. To get the same answer in Java, you write the loop yourself. This version keeps the table in three arrays, one per column. Step through it and watch i visit every row.

The loop has to check every row, because that is the only way this program knows. A database answering the same where may check every row too, or jump to the matching ones through an index. You do not have to care which, and you never write the loop.

select * from JavaCourse where name like '%jee%'; LIKE matches text against a pattern. In a pattern, % stands for any run of characters, including none, and _ stands for exactly one character. So '%jee%'means “contains jee anywhere”. None of Debasis, Nilanjan, Tauheed or Priyabrata has those three letters in a row, so no row matches. The prompt prints Empty set (0.00 sec). A student called Rajeev or Banerjee would have matched, and since MySQL's default comparisons ignore case, so would JEE.

select * from JavaCourse order by marks; ORDER BY sorts the result, smallest first unless you say otherwise; writing asc (ascending) only makes the default explicit. order by marks desc sorts largest first: Debasis, Nilanjan, Priyabrata, Tauheed. Two details. This desc means descending, a different word from the desc that describes a table; MySQL tells them apart by where they appear. And Debasis and Nilanjan tie on 85, and SQL promises no particular order between tied rows. If the order matters, add a second column: order by marks, roll.

DELETE: removing rows#

DELETE FROM removes rows from a table. WHERE picks the rows to remove, the same way it picks rows for UPDATE and SELECT.

Query OK, 1 row affected (0.01 sec)

Tauheed had 65, the only marks below 66, so his row is gone. DELETE always removes whole rows; to clear a single value, update it instead.

DROP: removing the table#

DROP TABLE removes a whole table: its structure and every row in it. Be careful with it. There is no undo, and the data is gone for good.

Query OK, 0 rows affected (0.01 sec)

DELETE removes rows and leaves the empty table in place. DROP removes the table itself: its structure, its rows, everything. drop database test; goes one level further and removes a whole database with every table in it.

The statements side by side#

From Java, every one of these statements is sent through one of two JDBC methods, and the choice depends on what the statement gives back. Keep this table in mind for the database lessons that follow.

The rule underneath: a statement that answers with rows runs through executeQuery; a statement that changes something runs through executeUpdate.

JDBC: Java Database Connectivity#

So far you have talked to MySQL by typing. A Java program has to do the same from code: open a connection, send a statement, and get back rows or a count. Three facts about the part of Java that does it:

• JDBC is a standard Java API for working with databases. An API, application programming interface, is a set of classes and interfaces with documented methods for you to call. Standard means it is part of Java itself: it ships with every JDK, the same everywhere, with nothing to download.
• It lives in the package java.sql. You import from it like any other package: import java.sql.Connection; or import java.sql.*;.
• Three of its types do the connecting: the class DriverManager and the interfaces Driver and Connection. The table below gives each one's job.

Think of JDBC as an adapter with two plugs: one fits your Java program, the other fits the database. It sits in the middle so that neither side has to know the other's details. Your program speaks JDBC, the server speaks its own protocol, and the driver translates between them.

Notice the split. DriverManager is a class, and you use it directly through its static method DriverManager.getConnection(...). Driver and Connection are interfaces: Java describes what their methods do, and the code that does it is written by the database vendor and arrives in the driver jar. A session, in the Connection row, is one logged-in conversation with the server, like one open mysql> prompt.

JDBC characteristics#

JDBC has four characteristics worth knowing.

• SQL-level API. JDBC does not invent a query language of its own. You write the same SQL you typed at the prompt, as a Java String, and JDBC carries it to the database: stmt.executeQuery("select * from JavaCourse"). JDBC's part is the delivery: connecting, sending the text, and bringing back the result.
• RDBMS popularity. It is the standard, most widely used way to reach a relational database (an RDBMS keeps data in related tables, like MySQL) from Java. Tools that hide SQL from you, such as Hibernate, still use JDBC underneath.
• Database compatibility. It works with OracleDB, MySQL, Sybase, Microsoft SQL Server and others, PostgreSQL and SQLite among them. Each database only needs its own driver.
• Simplicity. A handful of types, the six in the core table further down, and one fixed sequence of steps, the same for every database.

Each database has its own driver, and inside, the drivers are nothing alike. Yet the person writing the program does not have to care how any of them work.

That is interfaces and polymorphism at work. Your code is written against the interfaces in java.sql. In Connection conn = DriverManager.getConnection(url, user, password);the variable's type is the interface Connection, but the object it holds belongs to a class inside the driver jar that implements Connection. When you call conn.createStatement(), that class's version runs. It is the Animal and Dog idea again: the declared type decides which methods you may call, and the object decides what they do. Change the jar and the URL, and the same lines talk to Oracle.

Key idea

JDBC is a set of interfaces, and the driver is the implementation. Your code names only the interfaces, so it does not care which database is on the other end.

Key benefits of JDBC#

Four benefits follow from that design.

• Write Once, Run Anywhere. The core Java promise: compiled .classfiles run on any machine with a JVM. A JDBC program compiled on Windows runs unchanged on Linux or macOS, as long as the database's driver is available there. MySQL's driver is itself written entirely in Java, so the same jar works on every platform.
• Database independence.Because JDBC is a standard interface, the same Java code can work with Oracle, MySQL or SQL Server, and the vendor's driver handles everything specific to its database. Moving from MySQL to Oracle means a new jar and a new URL. One honest limit: the SQL inside your strings can still differ between databases. AUTO_INCREMENT, which the next lesson uses, is MySQL's own word, and Oracle writes it differently.
• Network independence. The database can be on your own machine or across a network, and the code does not change. Only the host in the URL does: localhost for your machine, or an address such as 192.168.1.20 for another. The driver handles the network traffic, which travels over TCP/IP, the protocol the Internet runs on.
• Object-relational mapping (conceptual). Java keeps data in objects; a relational database keeps it in rows. Moving between the two by hand means reading each column of a row and building an object from it. ORM tools such as Hibernate automate that mapping, and they are built on top of JDBC, so JDBC is their foundation.

The JDBC driver#

The JDBC driver is the piece that lets a Java program talk to one particular database. It translates Java's standard JDBC calls into that database's own protocol. A protocolis the exact format of the messages two programs exchange. MySQL's server understands only MySQL's own protocol, so when you call stmt.executeQuery(sql), the driver packs your SQL into a message in that format, sends it to port 3306, and unpacks the rows that come back into a ResultSet.

• It is the gateway. Every call from your program to the database passes through it.
• It is just a .jar file. Despite the name, it is not like a printer driver, which is installed into the operating system. A JDBC driver is a library of Java classes zipped into a .jar file, and the only program that needs to find it is Java, through the classpath.
• Each database has its own.MySQL's is called Connector/J; see the note.

Getting the jar#

• Download it. At dev.mysql.com/downloads/connector/j, choose Platform Independent, download the ZIP archive and extract it. Keep the mysql-connector-j-9.x.x.jar inside; the rest is documentation and source code.
• Or let Maven fetch it. In a Maven project, add the dependency below, and the jar is downloaded and put on the classpath for you.
• Or add it in your editor. In IntelliJ IDEA, add the jar as a library under File, Project Structure. In VS Code, open the Java Projects view and press + next to Referenced Libraries.

Putting it on the classpath#

Once you have the jar, add its path to your classpath. The classpath, from packages and imports, is the list of folders and jar files Java searches for classes while your program runs. A jar on the classpath is searched as if it were a folder. On Linux or macOS you can set it with export:

• $CLASSPATH keeps whatever was on the list already, and each : adds an entry: here the jar, then ..
• The trailing . is the current folder, and it matters. Java searches the current folder by itself only while no classpath is set. Once you set one, your own classes disappear unless . is on the list, and Java answers Error: Could not find or load main class.
• export lasts until you close that terminal. To keep the setting, add the line to ~/.bashrc (or ~/.zshrc on a Mac).

On Windows, add the jar's path to the system variable CLASSPATH instead: open System Properties, then Environment Variables, create a system variable named CLASSPATH with a value such as C:\mysql\mysql-connector-j-9.7.0.jar;., and open a fresh Command Prompt. Windows separates entries with ; instead of :.

A setting that applies to every program is easy to forget about. The simpler way, and the one this course uses, is the -cp option, which sets the classpath for one command only:

Database Connected Successfully!
Connected to MySQL, database studentdb

• Compiling needs no jar: your code mentions only java.sql types, which are part of the JDK. Only running needs the driver.
• -cp replaces CLASSPATH for that one command. The . is your own classes again, and in this example the jar sits in the same folder.
• Keep the quotes. In PowerShell, a bare ; ends the command.
• Forget the jar and the program still compiles and starts, then fails to connect with No suitable driver found for jdbc:mysql://localhost:3306/studentdb.

The two programs being run here are written in the DBConnection section below.

Core JDBC classes and interfaces#

Six types turn up in every JDBC program.

A few entries in the table are worth unpacking. To find the right driver, DriverManager offers your URL to each driver it knows, and the MySQL driver accepts any URL that starts jdbc:mysql:. A ResultSet works like an iterator: it hands you the rows one at a time, which the next lesson shows in detail. And the Connection row mentions transactions: a group of statements that must all take effect or none, controlled with conn.setAutoCommit(false), conn.commit() and conn.rollback(). By default each statement takes effect on its own, the moment it runs.

The Statement row mentions two interfaces that extend it.

• PreparedStatement extends Statement. Its SQL contains ? placeholders, and the values are submitted separately with methods such as setString and setInt: that is the parameter submission. The next lesson uses it for every insert, update and delete.
• CallableStatement extends PreparedStatement. It runs a stored procedure, a named routine saved inside the database, with a call such as conn.prepareCall("{call add_bonus(?)}").

The steps to connect to a database#

Every JDBC program follows the same six steps, from loading the driver to closing the connection. Each one is a line or two of Java.

1. 1Load the driver. Class.forName("com.mysql.cj.jdbc.Driver"); loads the driver class, which registers itself with DriverManager. Since JDBC 4.0 (Java 6), DriverManager finds drivers on the classpath by itself, because each driver jar lists its driver class in a file called META-INF/services/java.sql.Driver. So this line is optional today. It still works, and you will meet it in older code and tutorials. When the jar is missing it throws ClassNotFoundException, a checked exception, so it needs a catch.
2. 2Create the connection. Connection conn = DriverManager.getConnection(url, user, password); opens a session with the server: the driver reaches the port, logs in, and selects the database named in the URL.
3. 3Create a statement. Statement stmt = conn.createStatement(); gives you an object that can send SQL. For SQL whose values are filled in later, conn.prepareStatement(sql) gives a PreparedStatement instead.
4. 4Execute the query. ResultSet rs = stmt.executeQuery("select * from JavaCourse"); for a statement that returns rows, or int count = stmt.executeUpdate(sql); for one that changes data.
5. 5Process the results. Move through the rows with while (rs.next()) and copy each column into Java variables or objects with rs.getInt(...) and rs.getString(...).
6. 6Close the connection. rs.close(); stmt.close(); conn.close();, in the reverse of the order they were opened. Better still, let try-with-resources do it, as shown below.

All six steps in one program#

This program runs the six steps against the JavaCourse table from the mysql> section, which lives in the test database. (If you dropped it, run the create table and insert lines again.) The output shows the three rows left after the DELETE.

1 | Debasis | 85 | Ex
2 | Nilanjan | 85 | EX
4 | Priyabrata | 78 | A

Step 6 has a weakness written this way. If anything in steps 2 to 5 throws, control jumps to a catch, the three close() calls are skipped, and the connection stays open on the server. Do that often enough and MySQL, which allows 151 connections at a time by default, starts refusing new ones with Too many connections. The try-with-resources block from writing files closes them on every path:

1 | Debasis | 85 | Ex
2 | Nilanjan | 85 | EX
4 | Priyabrata | 78 | A

Everything declared in the brackets after try is closed automatically when the block ends, normally or through an exception, in reverse order: rs, then stmt, then conn. Step 1 has gone too, since it is optional. This is the shape every JDBC program in the rest of the course uses.

The connection URL, taken apart#

Every JDBC connection starts from a URL. The programs here use jdbc:mysql://localhost:3306/studentdb.

Settings for the driver can follow a ? at the end, written name=value and joined with &. Nothing in this module needs any.

Connecting to the database: DBConnection#

It pays to wrap step 2 in a small class that every later program uses. Its URL names a database called studentdb, so create that first, at the prompt:

Query OK, 1 row affected (0.01 sec)

Database Connected Successfully!

Here is what each part does:

• A utility class. DBConnection exists to hand out connections to the MySQL database. A utility class holds a helper method and is never turned into objects. Every button handler in the next lesson asks it for a connection, so the URL, user name and password are written once, in one place.
• The imports. Three types from the java.sql package: the interface Connection and the classes DriverManager and SQLException.
• A static method. getConnection() returns a Connection. static means you call it on the class itself, DBConnection.getConnection(), without ever writing new DBConnection(), as in static and final.
• URL, user name and password. The URL is the one taken apart above. root is the administrator account from the MySQL install, and your_password is a placeholder: put the root password you chose there. A password typed into source code is fine for practice on your own machine; real applications read it from a settings file or an environment variable instead.
• Opening the connection. DriverManager.getConnection(url, user, password) opens the connection. That is step 2. There is no Class.forNameline, because step 1 is left to JDBC 4.0's automatic loading.
• Success and failure. If the connection opens, a confirmation is printed; if a SQLException is thrown, the catch block prints it. Both go to the console, not to any window. e.printStackTrace()prints the exception's type, its message, and the chain of method calls that led to it.
• The return value. The method returns the Connection, and the rest of the program uses it to run insert, update, delete and select statements. Keep an eye on what it returns when the connection fails: the next section is about exactly that.

What happens when the connection fails#

Read the method again for the case where MySQL is not running. conn starts as null. getConnection throws, so the assignment never happens. The catch prints a stack trace, and the method returns conn, which is still null. Nothing tells the caller that anything went wrong. The handlers in the next lesson begin like this:

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
    (the rest of the stack trace from e.printStackTrace())
Exception in thread "JavaFX Application Thread" java.lang.NullPointerException: Cannot invoke "java.sql.Connection.createStatement()" because "conn" is null

conn.createStatement() on a null reference throws a NullPointerException. That is an unchecked exception, not a SQLException, so catch (SQLException ex) does not catch it. It escapes the handler, JavaFX prints it to the console and carries on, and the window shows nothing: no message in the text area, no error, just a button that seems dead. The real cause, the communications failure, sits higher up in the console, above an exception that looks unrelated. (Compiled with plain javac, which leaves variable names out of the class file, the message shows a placeholder such as <local1> instead of conn.)

Key idea

A method that catches a failure and returns null hides it. Let the exception travel to the code that can show it to the user.

A version that reports the failure#

Database Connected Successfully!

Two changes. The method declares throws SQLException instead of catching it, so a failed connection reaches the caller as the real exception, and the method can never return null. And the connection details are private static final constants at the top, easy to find and change. The handlers in the next lesson need no change: every one of them already ends with catch (SQLException ex), so with this version a stopped server puts Communications link failurestraight into the window's text area. The next lesson assumes this version.

Testing the connection from the console#

Before building any window, check the connection on its own. This program uses the improved DBConnection and reports which database it reached.

Database Connected Successfully!
Connected to MySQL, database studentdb

getMetaData() describes the database you reached, and getCatalog()returns the name of the current database, which MySQL's driver calls the catalog. Compile and run the two files with the -cp commands from the classpath section. If it fails, the message says why. With a wrong password it prints Could not connect: Access denied for user 'root'@'localhost' (using password: YES); with the server stopped, Could not connect: Communications link failure followed by a line about the last packet sent.

When it goes wrong: SQLException#

SQLException is a checked exception class in java.sql. Nearly every JDBC method declares throws SQLException, so the compiler makes you catch it or declare it, exactly as exception handling showed for IOException. A database is a separate program, often on another machine, and it can refuse almost anything. Besides its message, a SQLException carries two codes.

Message : Access denied for user 'root'@'localhost' (using password: YES)
SQLState: 28000
Code    : 1045

• getMessage() is the sentence written for a person. Read it first; it usually names the problem.
• getSQLState() is a five-character code defined by the SQL standard, so it means the same on every database. The first two characters give the kind of problem: 08 for connection trouble, 28 for a refused login, 42 for bad SQL or a missing table.
• getErrorCode() is the database's own error number, 1045 here, which is useful to search for. It is 0 when the driver found the problem before the server was involved.

The errors you will meet#