Databases have become key elements of the majority of applications. They replace the use of files managed by the developer itself. This contribution allows significant productivity gains during development and significantly improved application performance. They also facilitate the sharing of information between users. To use a database, you must know a minimum of vocabulary related to this technology, and we’ll see how to implement the same thing in a java 8 tutorial.
In the context of databases, the following terms are frequently used:
Relational database: A relational database is a database type that uses tables to store information.They use values from two tables to associate data table to the data in another table. Generally, in a relational database, the information is only stored once.
Table: A table is a component of a database that stores information in records (rows) and fields (columns). The information is generally grouped by category in a table. For example, we have the table of Customers, Products, or commands.
Registration: Registration is the set of information about an item in a table. Records are equivalent to logic rows of a table. For example, a recording of the Customers table contains the characteristics of a particular customer.
Field: a record comprises several fields. Every field in a record contains information about one-ment records. For example a customer record can contain CodeClient fields Name, First …
Primary key: a primary key is used to uniquely identify each row of a table. The primary key is a field or combination of fields whose value is unique in the table. For example, the CodeClient field is the primary key of the Customer table. There can not be two customers with the same code.
Foreign key: a foreign key is one or more fields in a table that refer to the fields of the primary key of another table. Foreign keys indicate how the tables are linked.
Relationship: A relationship is an association established between common fields in two tables. A relationship can be one to one, one to many or many to many. Through relationships, query results can include data from multiple tables. A relationship between a multi-client table and the table control allows a query to return all the orders corresponding to a client.
2. The SQL Language
Before you can write a Java application using data, you need to know what is java and what has to do with SQL, you should be familiar with SQL (Structured QueryLanguage). This language allows to interact with the database. There are different versions of SQL depending on the database used. However SQL also has a separate standardized basic syntax of any database.
to. Finding Information
The SQL language is used to specify the records to be extracted and the order in which you want to extract. You can create a SQL statement that retrieves information from multiple tables simultaneously, or you can create a statement that extracts only a specific recording. The instruction SELECT is used to return specific fields from one or more tables of the database.
The following statement returns the list of names and surnames of all records from the Customer table.
SELECT Name, Surname FROM Customer
You can use the * symbol in place of the list of fields that you want the value.
SELECT * FROM Customer
You can limit the number of records selected using one or more fields to filter the query result. Various clauses are available to perform this filtering.
This clause allows you to specify a list of conditions to be met the records to be part of the returned results. The following example retrieves all customers living Nantes.
SELECT * FROM customer WHERE City = 'Nantes'
The syntax of the clause requires the use of a simple rating for delimiting character strings.
WHERE clause IN …
You can use the clause WHERE … IN to return all the records that meet a list of criteria. For example, you can search for all customers living in France or Spain.
SELECT * FROM customer WHERE Country IN ('France', 'Spain')
BETWEEN WHERE clause …
You can also return a selection of records that fall between two specified criteria. The following query will retrieve a list of orders in the month of November 2005.
SELECT * from Orders WHERE OrderDate BETWEEN '01 / 11/05 'AND '30 / 11/05'
WHERE clause … LIKE
You can use the clause WHERE … LIKE to return all the records for which there is a specific requirement for a given field. For example, the following syntax will select all customers whose name starts with a d:
SELECT * FROM customer WHERE name LIKE '% s'
In this statement, the% symbol is used to replace any sequence of characters.
ORDER BY …
You can use the clause ORDER BY to return records in a particular order. The option ASC indicates ascending order, the option DESC indicates descending order. Multiple fields can be specified as sorting criteria. They are analyzed from left to right. If tied to the value of a field, the next field is used.
SELECT * FROM Customer ORDER BY DESC Name, Surname ASC
This returns the clients sorted in descending order on behalf and in case of equality in ascending order on the first name.
b. Added information
The creation of records in a table is via the command INSERT INTO. You must indicate the table where you want to insert a line, the list of fields for which you specify a value and finally the list of corresponding values. The full syntax is as follows:
INSERT INTO customer (codeClient, name, first name) VALUES (1000, 'Smith', 'Pierre')
When adding this new client, only the first and last name will be filled in the table. The other fields take the value NULL. If the fields list is not specified, the statement INSERT requires that you specify a value for each field in the table. You are therefore obliged to use the keyword NULL to indicate that for a particular field there is no information. If the Customer table has five fields (codeClient, name, first name, address, country) the previous statement can be written with the following syntax:
INSERT INTO customer VALUES (1000, 'Smith', 'Pierre', NULL, NULL)
Note that in this case the two NULL key word are required for fields address and country.
c. Update information
Changing fields of existing records is performed by the instruction UPDATE. This statement can update several fields of several records in a table based on expressions that are provided. You must supply the table name to be updated and the value to be assigned to different fields. The list is indicated by the key word SET followed by the assignment of the new value to the various fields. If you want the changes concern only a limited set of records, you must specify the clause WHERE to limit the scope of the update. If any clause WHERE is indicated modification will be done on all table records.
For example, to change the address of a particular client, you can use the following statement:
UPDATE Customer SET Address = '4 rue de Paris 44000 Nantes' WHERE codeClient = 1000
If the amendment relates to all records in the table, the clause WHERE is useless. For example if you want to increase the price of all your items, you can use the following statement:
UPDATE SET CATALOG unitPrice unitPrice = 1.1 *
d. Delete Information
The instruction DELETE FROM deletes one or more records of a table-ments. You must at least provide the table name on which will make the deletion. If you do not specify further details, in this case all rows of the table are deleted. In general a clause WHERE is added for limiting the extent of the deletion. The following command deletes all records from the Customer table:
DELETE FROM Customer
The following command is less radical and removes a particular record:
DELETE FROM Customer WHERE codeClient = 1000
The SQL language is of course much more comprehensive than that and can not be reduced to these five instructions. Nevertheless, they are sufficient for handling data from Java. If you want to deepen the learning of SQL I advise you to consult one of the books available in the same collection on this subject so further.