Java DataBase Connectivity

You know the basics of SQL. This is good. But today you will feel even better. Today you will start learning how to work with a database from a Java application.

There are a lot of different DBMS, so the creators of Java decided to standardize how a Java application works with databases. And they called this standard - JDBC : Java DataBase Connectivity .

Java DataBase Connectivity

JDBC has 3 main interfaces:

  • Connection - responsible for connecting to the database
  • Statement - responsible for querying the database
  • ResultSet - responsible for the result of the query to the database

In fact, that's all. And we won't even learn all methods of all interfaces. Only working examples, which we will gradually complicate.

JDBC is somewhat similar to working with servlets. The creators of JDBC have written several interfaces and proudly call it the Java DataBase API. And the implementation of these interfaces was assigned to ... the creators of the DBMS. How it is implemented there - no one cares. Works fine.

Java webinar

There are four versions of JDBC and we will be looking at the latest one. And not because she is the coolest, but because she is the simplest.

JDBC Driver Manager

The Java application and the database communicate with each other through a library called the JDBC Driver. This is a set of classes that implement the JDBC API for a particular DBMS.

The correct JDBC driver is selected using a class called DriverManager . Their interaction can be represented as follows:

DriverManager

The thing is, you can use In-Memory-DB, No-SQL-DB, or even a database built into an Android app. As a Java developer, these nuances do not concern you at all. The Driver Manager will select the correct JDBC driver for you and everything will work like clockwork.

By the way, how does he do it?

Connecting to MySQL server

Remember what you needed to connect to a local SQL server through MySQL Workbench? If I forgot, then I remind you, you needed three things:

  • host
  • login
  • password

Everything is clear with the login and password, and host, if you remember, is the name of the computer on which the server is located. If it is located on your local computer, then you need to specify localhost as the host name. What if it's not local?

Then you need to use ... URL. URL stands for Universal Resource Locator . It can be used to specify the location of anything on the network: a site, a printer, a SQL server. In fact, the URL is also used to access the local SQL server. It looks something like this:

mysql://localhost:3306/db_scheme
  • mysql is a server protocol
  • localhost - hostname on the network
  • 3306 - port on which requests are made
  • db_scheme - schema name (database name)

Note. The database name can be omitted. But if the server stores many database schemas, then usually different users and different access rights are created for them. And if the user under which you log in to the SQL server does not have access to all databases, then you definitely need to specify the name of the specific database to which you have access.

At the end of this url there can be various parameters, encoding types, timezone, which are considered parameters for the new database connection being established.

Also, the protocol can be composite. If all communication with the database server is over an encrypted channel using the SSH protocol, then the URL can be specified like this:

ssh:mysql://localhost:3306/db_scheme

A protocol is not necessarily an external program. For example, if you are working with a server using the JNDI protocol, you can specify it like this:

jndi:mysql://localhost:3306/db_scheme

And if you want to work using the JDBC API protocol, then you need to write like this:

jdbc:mysql://localhost:3306/db_scheme

When you try to create a database connection, the JDBC Driver Manager parses your SQL-db-URL and determines the JDBC driver name from the protocol name. Here is such a little trick.