Part 1. Introduction to SQL

JDBC. Connecting a database and creating a Java console application in IntelliJ IDEA

Introduction

This article opens a short series devoted to the basics of interacting with databases in Java and an introduction to SQL . Many programs are busy processing and modifying information and keeping it up to date. Since data is a very important part of program logic, separate storage is often allocated for it. The information in it is structured and subject to special rules to ensure correct processing and storage. Data is accessed and modified using a special query language - SQL (Structured Query Language). A database management system is software that ensures the interaction of various external programs with data and additional services (logging, recovery, backup, etc.), including through SQL. That is, a software layer between the data and external programs that work with it. In this part, we will answer the questions about what SQL is, what a SQL server is, and create the first program to interact with the DBMS.

Types of DBMS

There are several types of DBMS based on the way they organize data storage:

• Hierarchical. The data is organized in a tree structure. An example is a file system that starts from the root of the disk and then grows with branches of files of different types and folders of varying degrees of nesting.
• Network. A modification of hierarchical, each node can have more than one parent.
• Object-oriented. Data is organized in the form of classes/objects with their attributes and principles of interaction according to OOP.
• Relational. The data of this type of DBMS is organized in tables. Tables can be linked to each other, the information in them is structured.

In this series of articles, we will look at relational DBMSs (as the most common ones) using H2 as an example and create an application from scratch that emulates something similar to how an exchange works. Question: Why not PostgreSQL, MySQL, MSSQL or Oracle? Answer: In order not to be distracted by issues of installing a separate set of programs. Further configuration, creating a database, the intricacies of working in different OSes, versions. To work with H2, you need to perform a minimum of actions. But nothing prevents you from changing the current H2 JDBC to a relational DBMS from another manufacturer (only the server address line and the driver class name change).

SQL

External programs generate queries to the DBMS in the data management language Structured Query Language. What is SQL and how does it differ from conventional programming languages? One of the features of SQL is declarativeness. That is, SQL is a declarative language . This means that when entering commands, that is, creating queries to the SQL server, we describe what exactly we want to get, and not in what way. By sending a request to the server SELECT * FROM CUSTOMER(approximate translation from SQL into Russian: “make a selection from the COSTUMER table, the selection consists of all rows in the table” ), we will receive data for all users. It doesn’t matter at all how and from where the server downloads and generates the data we are interested in. The main thing is to formulate the request correctly.

• What is SQL Server and how does it work? Interaction with the DBMS occurs on a client-server principle. Some external program sends a request in the form of operators and commands in SQL language, the DBMS processes it and sends a response. For simplicity, let's assume that SQL Server = DBMS.

If you can drive one brand of car, you will most likely be able to drive others without any problems. The basics of driving are the same everywhere, except for small details. The same is true for SQL servers from different manufacturers - each of them has its own version of SQL, but it meets the specified standards (SQL92, SQL2003...). We will use operators and commands within the SQL92 framework. Basic SQL statements are divided into the following groups:

• Data Definition Language ( DDL ) – data definitions. Creation of the database structure and its objects;
• Data Manipulation Language( DML ) – actual interaction with data: insertion, deletion, modification and reading;
• Transaction Control Language ( TCL ) – transaction management;
• Data Control Language( DCL ) – management of access rights to data and database structures.

In a series of articles we will look at the first three groups, paying particular attention to DML.

JDBC

In the 80s of the last century, personal computers of the PC XT/AT type conquered the market. This was largely due to the modularity of their design. This means that the user could quite easily change one or another component of his computer (processor, video card, disks, etc.). This wonderful property has been preserved to this day: we change the video card and update the driver (sometimes it even updates itself, automatically). Most often, nothing bad happens with such manipulations, and existing programs will continue to work with the updated system without reinstallation. The same applies to working in Java with a DBMS. To standardize work with SQL servers, interaction with it can be performed through a single point - JDBC (Java DataBase Connectivity). It is an implementation of the java.sql package for working with a DBMS. Manufacturers of all popular SQL servers release JDBC drivers for them. Consider the diagram below. The application uses instances of classes from java.sql . We then pass the necessary commands to retrieve/modify the data. Next, java.sql interacts with the DBMS through the jdbc driver and returns the finished result to us. To switch to a DBMS from another manufacturer, it is often enough to change JDBC and perform basic settings. The remaining parts of the program do not change.

First program

Let's move on to the practical part. Let's create a Java project using the JetBrains IntelliJ IDEA IDE . Note that the Ultimate Edition contains a wonderful tool for working with SQL and databases - Data Grip . However, it is paid for for most users. So for educational purposes we can only use the publicly available IntelliJ IDEA Community Edition . So:

1. Launch the IDE and create a new project:
   


2. Select a Java project, indicate the SDK version (in the example JDK8, but this is not critical):
   


3. In the next step, select console application as the type :
   


4. We indicate the project name , package and its location on disk (I created a separate directory specifically for this):
   


5. Let’s put the IDE aside for a minute and download from www.h2database.com the necessary JDBC file for working with the H2 DBMS (download platform independent ZIP):
   


6. We go inside the downloaded file (we are interested in the jar file along the path h2\bin , which we will need later, copy it):
   


7. We return to the IDE and create directories in the root of the project: db , where files with DBMS data will be located; lib – here is the JDBC JAR library:
   


8. Move the jar file from step 6 to the lib directory and add it to the project as a library:
   


9. Let's rename the java file to src/sql/demo to StockExchange.java (in case you forgot, we are going to emulate a simple “exchange”), change its contents and run:
   

Now we can connect to and disconnect from the DBMS. Each step is reflected in the console. When you first connect to the DBMS, a database file stockExchange.mv.db is created .

Code parsing

The actual code:

package sql.demo;

import java.sql.*;

public class StockExchangeDB {
    // Блок объявления констант
    public static final String DB_URL = "jdbc:h2:/c:/JavaPrj/SQLDemo/db/stockExchange";
    public static final String DB_Driver = "org.h2.Driver";

    public static void main(String[] args) {
        try {
            Class.forName(DB_Driver); //Проверяем наличие JDBC драйвера для работы с БД
            Connection connection = DriverManager.getConnection(DB_URL);//соединениесБД
            System.out.println("Соединение с СУБД выполнено.");
            connection.close();       // отключение от БД
            System.out.println("Отключение от СУБД выполнено.");
        } catch (ClassNotFoundException e) {
            e.printStackTrace(); // обработка ошибки  Class.forName
            System.out.println("JDBC драйвер для СУБД не найден!");
        } catch (SQLException e) {
            e.printStackTrace(); // обработка ошибок  DriverManager.getConnection
            System.out.println("Ошибка SQL !");
        }
    }
}

Constant block:

1. DB_Driver : Here we defined the name of the driver, which can be found, for example, by clicking on the connected library and expanding its structure in the lib directory of the current project.
2. DB_URL : Our database address. Consists of data separated by a colon:
   
3. Protocol=jdbc
4. Vendor (manufacturer/name) DBMS=h2
5. The location of the DBMS, in our case the path to the file (c:/JavaPrj/SQLDemo/db/stockExchange). For network DBMSs, the names or IP addresses of remote servers, TCP/UDP port numbers, and so on are additionally indicated here.

Error processing:

Calling methods in our code may return errors that you should pay attention to. At this stage we simply report them in the console. Note that errors when working with a DBMS are most often SQLException .

Logic of operation:

1. Class.forName (DB_Driver) – we make sure that the corresponding JDBC driver is available (which we previously downloaded and installed).
2. DriverManager.getConnection (DB_URL) – establish a DBMS connection. Using the passed address, JDBC itself will determine the type and location of our DBMS and return a Connection, which we can use to communicate with the database.
3. connection.close() – close the connection to the DBMS and finish working with the program.

In the next part of the series, we will get acquainted with DDL operators and SQL data types, and also create the initial structure of our database and fill it with tables. Second part Third part