Parsing databases and SQL language - "Java project from A to Z"

An article in a series about creating a Java project. Its goal is to analyze key technologies, the result is to write a telegram bot. The introduction is here . Aloha, software engineers. Today we are talking about databases and the SQL language. Who is this article for? Yes, to anyone who is interested. Some may start their journey with this article, others will be able to brush up on interesting facts. This part will be purely theoretical. Before we talk about what databases and SQL syntax are, we need to understand and determine why we will study them. At this stage, we will talk ONLY about SQL databases: NoSQL will not be considered in this series of articles.

Database: what is it

A database (hereinafter referred to as a DB) is a place where structured data is stored., which can be obtained using the query language. Databases can not only store, but also process and modify information in large volumes. For a remark: try to quickly change the set of values ​​​​in Excel according to some criteria. In principle, nothing complicated. Not like in our non-digitized real world. For example, renaming lanes, streets, cities. Everything would be virtual and entered into the database, if it were normalized, it would be a couple of trifles. And so suffers a lot of data carriers that cannot automatically change. In this context, SQL for databases is the language that the DB understands and reacts accordingly. Let's say it would not be difficult to rename the city, renaming Dnepropetrovsk to Dnipro would look something like this:

UPDATE city SET name = “Днипро” WHERE id = 1231;

All subsequent requests to the system would already give the name that we need.

Why databases are needed

As I said, databases can store data. But what is this data? To understand with a real-life example, you can talk about some kind of application. For example, about the same telegram bot. We want to know how many people are using the bot. How can this be implemented? In a regular Java application, you can create many unique elements - Set, which will store the user's nickname or chat ID. Will it work? Will. Exactly until the Java application is stopped, and as soon as it is started again, the set of unique elements will be empty. That is, the data that was stored in the running application simply disappeared. What can be done about it? You can move the storage of the system state (data) from a Java application somewhere else. You can store this in CSV format in a regular file on your computer. It is possible in general one line in a separate file. You can do this and then overwrite this data before the Java application stops. Although the guarantee that the recording will be successful is far from being close to 100%, because you can simply unplug the server's power cord and the recording will not occur. There is a significant drawback in this approach: there is no functionality for obtaining, aggregating and searching for information at the file level. Yes, of course, you can subtract a file, create objects for it, but in this case you will have to download all the data into the application EVERY time. And there can be many of them, for example, a couple of gigabytes. This can be avoided by storing data in a database. How? Through tables and relationships between them. This is where all relational databases stand. how the Java application will stop. Although the guarantee that the recording will be successful is far from being close to 100%, because you can simply unplug the server's power cord and the recording will not occur. There is a significant drawback in this approach: there is no functionality for obtaining, aggregating and searching for information at the file level. Yes, of course, you can subtract a file, create objects for it, but in this case you will have to download all the data into the application EVERY time. And there can be many of them, for example, a couple of gigabytes. This can be avoided by storing data in a database. How? Through tables and relationships between them. This is where all relational databases stand. how the Java application will stop. Although the guarantee that the recording will be successful is far from being close to 100%, because you can simply unplug the server's power cord and the recording will not occur. There is a significant drawback in this approach: there is no functionality for obtaining, aggregating and searching for information at the file level. Yes, of course, you can subtract a file, create objects for it, but in this case you will have to download all the data into the application EVERY time. And there can be many of them, for example, a couple of gigabytes. This can be avoided by storing data in a database. How? Through tables and relationships between them. This is where all relational databases stand. there is no functionality for getting, aggregating and searching for information at the file level. Yes, of course, you can subtract a file, create objects for it, but in this case you will have to download all the data into the application EVERY time. And there can be many of them, for example, a couple of gigabytes. This can be avoided by storing data in a database. How? Through tables and relationships between them. This is where all relational databases stand. there is no functionality for getting, aggregating and searching for information at the file level. Yes, of course, you can subtract a file, create objects for it, but in this case you will have to download all the data into the application EVERY time. And there can be many of them, for example, a couple of gigabytes. This can be avoided by storing data in a database. How? Through tables and relationships between them. This is where all relational databases stand.

SQL: the language that databases understand

There are Database Management Systems (hereinafter - DBMS) and the language they understand - SQL. SQL is a programming language for manipulating and managing databases. To understand how this works, let's look at the figure:The user sends SQL queries to the DBMS, the DBMS understands what needs to be done, does it, and if the request was to receive data, then returns them. Therefore, we as developers need to master the SQL query language. You might be thinking, “Oh, another language. With God's help, I barely understand Java here, and you immediately offer me another language. This is not true: SQL was invented as a specification language for DBMS so that accountants who are very far from databases and programming in general could write in this language. This means that learning it will not be so difficult. The main thing is PRACTICE-PRACTICE-PRACTICE. Today there will be theory, but the next article will be about practice. Since a relational database is a set of two-dimensional-array-tables and relationships between them, the work will go around them. Speaking of example,For us at this stage, it is important to understand that the entries in the table are data about some object from the Java world. For example, these two tables can be described in Java like this:

public class Country {
   private Long id;
   private String name;
}

public class City {

   private Long id;
   private String name;
   private Country country;
   private Integer population;
}

Well, isn't it a beauty? The database can store as many tables as you like. In our case, there are two.

Table structure

I think that everyone has come across excel tables in one way or another, and you understand the terms row and column . Within databases, we talk more about records and fields: So each object of the City class is a record in a table in the database.

primary key

It often happens that fields in databases have the same values. For example, social networks, where more than one person can be found by name, surname and even patronymic. And relational databases require a unique field by which to access the record. This is what is called the first key or Primary Key. Usually, the ID (id) field is used as such a key - this is short for identifier. That's why you need to add an ID field to each table .

External key

In our example, there is such a field in the City table, which uses the key from Country. And it works like this: each city knows a unique information identifier for its country, and if we take it and create a query in the database, we will get comprehensive information about the country. As you can see in the picture, there is a relationship between two tables: Here is the foreign key principle.

What sections are there in SQL

At interviews, by the way, they often ask what operations are in SQL:

• DDL (Data Definition Language) is a group of operators that change/create tables, their structure, and more. That is, creating a table, deleting it, creating / deleting fields in tables; creating a new primary key, and so on;
• DML (Data Manipulation Language) is a group of operators that manage data modification. These are all operations that change data in the database: adding, getting, changing and deleting;
• DCL (Data Control Language) - means of confirming the user's rights to perform actions. Operations to grant access and rights to a particular user so that he can perform DDL/DML operations.

What data types are there in SQL

Tables can store and process certain types of data. Everything here will be similar to what we use in Java. Let's talk about the main ones. There are only three of them, we will already add others as needed and / or as desired: As you can see from the picture, these are:

• INT - integer values. Used for a unique identifier and for a simple integer;
• VARCHAR is our String ;
• DATE is our LocalDate.

So far so easy, right? Field types are set when the table is created. It is clear that it will not be possible to write a value of another type to the field.

What are SQL statements

An operator is a single command that performs some action. Operators are made up of sentences, which in turn are made up of keywords that are used in queries. Let's look at an example: It's more interesting here, the query has already been built here. What are they doing there? Everything is simple, it says: “Take three fields (Name, Age and CreatedDate) from the Clients table, select only those records where name is equal to Roman”.

Conclusion

In this article, we started to study databases. We understood what it is and why we need it. Next, we ran through the first signs of SQL, which we will discuss in more detail in the next article. Traditionally, I suggest that you register on GitHub and subscribe to my account in order to follow this series and my other projects that I lead there.

Homework

To add interest to this series of articles on JRTB, there will be homework assignments from time to time. For example, without today's assignment, the next article will be much more difficult to understand, because there will be a lot of practice. Therefore, the task is to install the MySQL DBMS on your computer and enter the database either through the console or through other solutions. Thank you all for reading, see you soon!

List of all materials in the series at the beginning of this article.