Types of DBMS

Each of these has its own pros and cons. Selecting a database depends on your own needs. Based upon your needs, instead of choosing an SQL data structure (which is suitable for an RDBMS), you may prefer NoSQL ( it is a non-relational structure and can be suitable for a DDBMS).

In this chapter, you’ll see the usage of an RDBMS and simple SQL statements only.

RDBMS

In RDBMS, data are stored in rows and columns, which is similar to tables. These tables are termed relations. Rows of a table are referred to as tuples , and columns are referred to as attributes .

Each row of a table contains a record. Each column contains fields. Consider the table in Figure 13-2.

For your reference, I have marked all the records and attributes in Figure 13-2.

You can process different records of a relation based on some mathematical formulation, which is termed relational algebra . Since the entire database can be processed using these mathematical formulae, relational algebra is the theoretical foundation for relational databases and SQL.

Oracle Database, MySQL, Microsoft SQL Server, IBM DB2, and so on are common examples of RDBMS. In this chapter, I have used MySQL to demonstrate the examples.

SQL

In this chapter, you will see the use of these basic operations when the Java program interacts with the MySQL database.

Q&A Session

13.1 How can I decide which driver is suitable for my application?

If you know that you need only one specific database, pick Type-4. If you need to access multiple databases, choose Type-3. When you do not have either Type-3 or Type-4, you can consider Type-2 drivers. Type-1 is in general not recommended to use, but you can limit its use to testing purposes.

13.2 What are typical operations of the application server when any client uses type-3 drivers?

Some typical operations include logging, load balancing, auditing, and so on.

MySQL Command Prompt View

Demonstration 1

Demonstration 2

In Demonstration 2, I’ll update some records, then I’ll delete a record. Finally, I’ll update the records in such a way that I can obtain the initial state of the table. For example, you’ll see that though Amit’s age was updated from 25 to 35, at the end I have reset it back to 25. Also, I have deleted the newly added record for John. Similarly, though I made a change to Bob’s salary, at the end I have reset the value.

Demonstration 3

It is important to note that the first argument of these setter methods specifies the question mark placeholder. For example, the two setInt() calls specify the first and third placeholders, respectively; setString() specifies the second placeholder; and setDouble() specifies the fourth placeholder.

Q&A Session

13.3 Why is the use of PreparedStatement objects considered faster than using Statement objects?

PreparedStatement objects can contain precompiled SQL statements. So, if you pass the same query (with the same or different data) multiple times, DBMS can run the query much faster. But, in the case of a Statement object, SQL needs to validate the query each time you use it.

13.4 Is passing the parameters mandatory for PreparedStatement objects ?

Normally, you use PreparedStatement when your SQL query takes parameters. The SQL statement that takes parameters can help you to execute the statement with different values, which is very common in real-world scenarios. For simplicity and to make the program shorter, I have not used command-line arguments or user-defined inputs. But in practice, you can always pass the arguments using the command line, which in turn will replace the question marks before your query is processed.

13.5 How is the executeQuery() method different from executeUpdate()?

The executeUpdate() method is associated with insert, update, or delete operations or SQL statements that return nothing, such as DDL statements. This method does not return any ResultSet object.

On the other hand, executeQuery() executes an SQL statement that returns a single ResultSet object. This method cannot be called on PreparedStatement or CallableStatement (this will be discussed in the next demonstration).

13.6 What are the key advantages of using PreparedStatement?

13.7 What is batch processing ? Can you give an example?

By “batch processing,” I mean that you can execute a bunch (or group or set) of queries. The addBatch() and executeBatch() methods in the Statement interface can help you in this area.

Demonstration 4

This demonstration shows the use of the CallableStatement object . CallableStatement is an interface that extends PreparedStatement, which in turn extends the Statement interface. CallableInterface is used to execute stored procedures and functions in SQL.

You can see that the table has two attributes, FirstNumber and SecondNumber, and each row of the table contains various double type values. Let’s say you want to calculate the aggregate of the two double values of each row in the table; you can accomplish this task using a function called total().

You can complete Step 1 and Step 2 in various ways and in your preferred order. In the following section, I give you the commands (with corresponding outputs) that I used once I connected my database to complete these steps. The SQL statements are shown in bold for a better readability. I am also putting the supporting comments inside brackets [ ].

Step 2

Create the function called total: [A function is a stored program in which you can pass parameters, and, in turn, it will return a value.]

mysql> create function total(firstNumber double, secondNumber double) returns double deterministic return firstNumber + secondNumber;

Query OK, 0 rows affected (1.03 sec)

Note

As per the MySQL 8.0 reference manual: “A routine is considered ‘deterministic’ if it always produces the same result for the same input parameters, and “’not deterministic’ otherwise. If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC.”

So, if you miss the word deterministic in the previous query, you may encounter following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sum(firstNumber double,secondNumber double)

returns double

return firstNumber+se' at line 1

[Now I display the function details. This is optional for you.]

mysql> Select Routine_name as "Function Name", routine_Definition as "Definition", Routine_Schema "Schema", Data_Type as "Types", Created From  Information_Schema.Routines Where Routine_Name="total" and Routine_Type= 'FUNCTION';

Here is a snapshot from the MySQL 8.0 command-line client for better readability:

Now, let’s analyze the upcoming program. In the following example, you will notice the following line of code:

CallableStatement callableStmt=connectionOb.prepareCall("{?= call total(?,?)}");

You can see that a CallableStatement object is created by invoking the prepareCall() method, and that the prepareCall() method accepts a String parameter.

Note

Like many other methods, there are various overloaded versions of the prepareCall() method. I have used the simplest one in this example.

Notice the three question marks. You know that these are used for method parameters. These parameters are sequential in nature, and the first parameter starts with 1.

The following lines will show the usage of these parameters:

callableStmt.setDouble(2,queryResult.getDouble("FirstNo"));

callableStmt.setDouble(3,queryResult.getDouble("SecondNo"));

/*

Here, we have used the registerOutParameter() method of the CallableStatement interface, which registers the OUT parameter in the ordinal position in parameterIndex(first argument) to the JDBC type sqlType(second argument). All OUT parameters must be registered before a stored procedure is executed.

*/

callableStmt.registerOutParameter (1,Types.DOUBLE);

callableStmt.execute();

It indicates that there are two double type values, in positions 2 and 3. The question mark in position 1 will be replaced with the function (the function name is total() in this case) call, which accepts these double type values as parameters and returns the aggregate (which is also a double type value). Now, go through the full implementation and the corresponding output.

package java2e.chapter13;

import java.sql.*;

class Demonstration4 {

       public static void main(String[] args) throws SQLException {

             System.out.println("***Demonstration-4.Using a Callable Statement.***");

             Connection connectionOb = null;

             try {

                    // for MySQL database

                    Class.forName("com.mysql.cj.jdbc.Driver").newInstance();

                    connectionOb = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "admin");

                    Statement statementOb = connectionOb.createStatement();

                    System.out.println("This is the original table.");

                    ResultSet queryResult = statementOb.executeQuery("select * from NumberTable");

                    System.out.println("FirstNumber " + "SecondNumber");

                    System.out.println("--------------------------");

                    while (queryResult.next()) {

                           System.out.print(queryResult.getDouble("FirstNo") + " " + queryResult.getString("SecondNo"));

                           System.out.println();

                    }

                    System.out.println(" Calling the total() function on each record of the NumberTable.");

                    //Using the Callable statement

                    CallableStatement callableStmt=connectionOb.prepareCall("{?= call total(?,?)}");

                    queryResult = statementOb.executeQuery("select * from NumberTable");

                    System.out.println("FirstNumber " + "SecondNumber "+"Total");

                    System.out.println("--------------------------------------");

                    while (queryResult.next()) {

                           System.out.print(queryResult.getDouble("FirstNo") + " " + queryResult.getDouble("SecondNo")+ " ");

                           callableStmt.setDouble(2,queryResult.getDouble("FirstNo"));

                           callableStmt.setDouble(3,queryResult.getDouble("SecondNo"));

                           /*Here, we have used the registerOutParameter method of the CallableStatement interface, which registers the OUT parameter in the ordinal position in parameterIndex(first argument) to the JDBC type sqlType(second argument). All OUT parameters must be registered before a stored procedure is executed.*/

                           callableStmt.registerOutParameter (1,Types.DOUBLE);

                           callableStmt.execute();

                           System.out.print(callableStmt.getDouble(1));

                           System.out.println();

                    }

             } catch (SQLException ex) {

                    System.out.println(ex.getMessage());

             }

             // To catch any other exception

             catch (Exception ex) {

                    System.out.println(ex.getMessage());

                    ex.printStackTrace();

             } finally {

                    // Close the connection

                    if (connectionOb != null) {

                           connectionOb.close();

                    }

             }

       }

}

Output:

***Demonstration-4.Using a Callable Statement.***

This is the original table.

FirstNumber      SecondNumber

----------------------------

12.3             15.7

32.5             25.3

25.0             75.0

Calling the total() function on each record of the NumberTable:

FirstNumber      SecondNumber     Total

---------------------------------------

12.3             15.7              28.0

32.5             25.3              57.8

25.0             75.0             100.0

Q&A Session

13.8 When should I prefer a Statement object over a PreparedStatement or a CallableStatement?

13.9 What is a stored procedure?

In simple words, if you want to repeat a sequence of tasks, you create a stored procedure. A stored procedure is much like writing a method in Java. The steps to create a stored procedure can vary across the databases.

In this chapter, I have used a simple function, total(), in Demonstration 4 to serve our needs.

13.10 You have used the term DDL in the context of SQL in some places. What does it mean?

Demonstration 5

This demonstration shows the use of a javax.sql.DataSource interface object to connect to a datasource. It is a new addition to the JDBC 2.0 API and a preferred way to connect to a datasource. Using a DataSource object can provide connection pooling and distributed transactions. But since you are just starting your journey, we’ll focus on the the connection part only.

In Demonstration 5, the getMySqlDataSource() method is used. In this method, you create a MySqlDataSource object, set the database URL, and pass the user ID and password to connect to the database. Once this information is set, you can use this MySqlDataSource object to connect to the database.

You will understand the changes better if you compare Demonstration 5 with Demonstration 1. To make things simple, only a small portion of code in Demonstration 1 has been changed, and there is no significant change in the output. But you must take note of this new way of connecting to the database.

Before you proceed, I suggest you go through the following points.