Two options to interact with a database:
SQL
: the idea is to embed SQL
commands directly in the program: a pre-compiler scans the code, extact the SQL
commands, execute them on the DBMS. This system is used primarily for C, C++, COBOL or Fortran, and Language Integrated Query to some extend is part of this approach.SQL
(for instance, PL/SQL
).In this chapter, we will study how to develop a database application that uses a library.
Every database application follows the same routine:
Which API is used vary with the pair Language / DBMS. Here are some of the most commonly used pairs:
Language | API | Website |
---|---|---|
Python | Python Database API | Python |
C, C++ | MySQL C API | C, C++ |
C# | MySQL Connector/Net | C# |
Java | Java DataBase Connectivity | Java |
In this chapter, we will more precisely study how to develop a database application coded in Java that uses the Java DataBase Connectivity library.
Java actually uses
The situation is similar e.g. in Python, where you have to use an API and a connector. Among Python’s connector compatible with MySQL’s API, there is PyMySQL or mysql-connector-python.
Note that the A.P.I. is needed when you write and compile your program, and the driver / connector is needed when you execute it. We will come back to this when we explore our first program.
For a quick introduction to Java, cf. A Very Short Intro to Java.
We will write and compile a simple java program that manipulates a simple database. Even if the creation and population of the database could have been done from within the program, we will do it as a preliminary step, using the C.L.I., to make our program simpler.
This program ows a lot to the one presented at JDBC Programing by Examples with MySQL.
For this program, we will use the following database:
/* code/sql/HW_EBookshop.sql */ DROP SCHEMA IF EXISTS HW_EBookshop; CREATE DATABASE HW_EBookshop; USE HW_EBookshop; CREATE TABLE BOOKS ( ID INT PRIMARY KEY, title VARCHAR(50), author VARCHAR(50), price DECIMAL(10, 2), qty INT ); -- Cf. https://en.wikipedia.org/wiki/List_of_best-selling_books INSERT INTO BOOKS VALUES ( 1, 'The Communist Manifesto', 'Karl Marx and Friedrich Engels', 11.11, 11); INSERT INTO BOOKS VALUES ( 2, 'Don Quixote', 'Miguel de Cervantes', 22.22, 22); INSERT INTO BOOKS VALUES ( 3, 'A Tale of Two Cities', 'Charles Dickens', 33.33, 33); INSERT INTO BOOKS VALUES ( 4, 'The Lord of the Rings', 'J. R. R. Tolkien', 44.44, 44); INSERT INTO BOOKS VALUES ( 5, 'Le Petit Prince', 'Antoine de Saint-Exupéry', 55.55, 55); SELECT * FROM BOOKS;
MariaDB [HW_EBookshop]> SELECT * FROM BOOKS; +----+-------------------------+--------------------------------+-------+------+ | ID | title | author | price | qty | +----+-------------------------+--------------------------------+-------+------+ | 1 | The Communist Manifesto | Karl Marx and Friedrich Engels | 11.11 | 11 | | 2 | Don Quixote | Miguel de Cervantes | 22.22 | 22 | | 3 | A Tale of Two Cities | Charles Dickens | 33.33 | 33 | | 4 | The Lord of the Rings | J. R. R. Tolkien | 44.44 | 44 | | 5 | Le Petit Prince | Antoine de Saint-Exupéry | 55.55 | 55 | +----+-------------------------+--------------------------------+-------+------+ 5 rows in set (0.00 sec)
You can copy and paste the code, then execute it, or use MySQL’s batch mode: you can find the code previously given at code/sql/HW_EBookshop.sql, i.e., at RocketGit. Open a terminal (or command-line interpreter), navigate to the folder where you stored that file (using cd), and type
mysql -u testuser -p < HW_EBookshop.sql
for linux, or (something like)
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" -u testuser -p < HW_EBookshop.sql
for Windows. Refer to the Logging-In as testuser section if you forgot how to log-in to your database.
You just discovered MySQL’s batch mode, that perform series of instructions from a file. You can easily make sure that the database and the table were indeed created, and the values inserted, by logging the way you used to, and executing the usual commands.
As we are about to see, a database application needs to be written following this order:
and the program needs to load the driver (which is specific to DBMS) at execution time.
Of course, if the second step failed, then the program needs to exit gracefully, or to provide debugging information to the user. The program we will obtain can (normally) be compiled, using something like javac FirstProg.java
(or an equivalent command for windows). But another refinment is needed when you want to execute it. We need to set up the driver (or connector) to make the java SQL API and MySQL communicate. To do so,
Once this is done and your program was compiled, you can execute it using (where you replace *** with the actual number, of course, e.g. 8.0.19
):
java -cp .:mysql-connector-java-***.jar FirstProg
in Linux, or
java -cp .;mysql-connector-java-***.jar FirstProg
in Windows. The -cp
option lists the places where java should look for the class used in the program: we are explicitely asking java to use the mysql-connector-java-***.jar
executable (the driver) to execute our FirstProg
executable.
If we try to execute FirstProg
without that flag, we obtain the following error message:
$ java FirstProg java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/HW_EBOOKSHOP at java.sql.DriverManager.getConnection(DriverManager.java:689) at java.sql.DriverManager.getConnection(DriverManager.java:247) at FirstProg.main(FirstProg.java:9)
// code/java/FirstProg.java import java.sql.*; public class FirstProg { public static void main(String[] args) { try (Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/HW_EBOOKSHOP", "testuser", "password"); /* * If at execution time you receive an error that starts with * "java.sql.SQLException: The server time zone value 'EDT' is unrecognized or * represents more than one time zone. You must configure either the server ..." * add ?serverTimezone=UTC at the end of the previous string, * i.e., replace the previous lines of code with: * Connection conn = DriverManager.getConnection( * "jdbc:mysql://localhost:3306/HW_EBOOKSHOP?serverTimezone=UTC", * "testuser","password"); * cf. for instance https://stackoverflow.com/q/26515700 * Or, change your server's configuration, cf. * https://stackoverflow.com/a/44720416 */ Statement stmt = conn.createStatement(); ) { String strSelect = "SELECT title, price, qty FROM BOOKS WHERE qty > 40"; System.out.print("The SQL query is: " + strSelect + "\n"); ResultSet rset = stmt.executeQuery(strSelect); System.out.println("The records selected are:"); int rowCount = 0; String title; double price; int qty; while (rset.next()) { title = rset.getString("title"); price = rset.getDouble("price"); qty = rset.getInt("qty"); System.out.println(title + ", " + price + ", " + qty); rowCount++; } System.out.println("Total number of records = " + rowCount); conn.close(); } catch (SQLException ex) { ex.printStackTrace(); } } }
A couple of comments:
java.sql.*
, whose documentation is at Oracle, contains the classes
DriverManager
, used for managing a set of JDBC drivers,Connection
, used to make a connection with a database via DriverManager
objects,Statement
, used to send basic SQL
statements via Connection
objects,ResultSet
, to retrieve and update the results of a query, returned by a Statement
object,ResultSetMetadata
, to get information about the columns of a ResultSet
object,SQLException
, a class of exceptions relative to SQL
.Connection
is a bridge (the physical connection), and Statement
is a lane (a symbolic, or logic, path on the bridge)."jdbc:mysql://localhost:3306/HW_EBOOKSHOP"
,
jdbc
is the protocol,mysql
is the subprotocol,localhost
is the url of the database,3306
is the port, andHW_EBOOKSHOP
is the schema (that needs to already exist in this case).strSelect
does not end with ;
(it could, but does not have to).next
() returns true
if there is something left in the set of result, and move to the next line if it is the case. It ressembles what we would use to read from a file. If you try to use getString
before moving to the first row, you’ll get an error like java.sql.SQLException: Before start of result set:
indeed, the cursor is “above” the first row of results when the ResultSet
object is created.1
, 2
, and 3
instead of "title"
, "price"
and "qty"
in the while
loop: the getString
, getDouble
and getInt
are overloaded, and have versions that take one integer as input, corresponding to the position of the attribute in the result set.If you store the program in FirstProg.java
, compile it, with
javac FirstProg.java
and then execute it, with
java -cp .:mysql-connector-java-***.jar FirstProg
(refer back to “Executing Database Application” for more details) then you should obtain:
The `SQL` query is: SELECT title, price, qty FROM BOOKS WHERE qty > 40 The records selected are: The Lord of the Rings, 44.44, 44 Le Petit Prince, 55.55, 55 Total number of records = 2
Take the time to make sure you have the same result on your installation, and that you understand how the code works before moving on.
If you were to replace the body of try in the previous program with
String strSelect = "SELECT * FROM BOOKS"; ResultSet rset = stmt.executeQuery(strSelect); System.out.println("The records selected are:"); ResultSetMetaData rsmd = rset.getMetaData(); int columnsNumber = rsmd.getColumnCount(); String columnValue; while (rset.next()) { for (int i = 1; i <= columnsNumber; i++) { if (i > 1) System.out.print(", "); columnValue = rset.getString(i); System.out.print(columnValue + " " + rsmd.getColumnName(i)); } System.out.println();
You would obtain:
The records selected are: 1 ID, The Communist Manifesto title, Karl Marx and Friedrich Engels author, 11.11 price, 11 qty 2 ID, Don Quixote title, Miguel de Cervantes author, 22.22 price, 22 qty 3 ID, A Tale of Two Cities title, Charles Dickens author, 33.33 price, 33 qty 4 ID, The Lord of the Rings title, J. R. R. Tolkien author, 44.44 price, 44 qty 5 ID, Le Petit Prince title, Antoine de Saint-Exupéry author, 55.55 price, 55 qty
In that code, please note:
ResultSetMetadata
,ResultSet
using the getColumnCount
method,getString
method with integer input to read all the data in the table, no matter its “original” data type.Overall, this code would work equally well if the table had a different number of columns, as opposed to our first program.
Note that in the previous code, we read everything as a string. But, actually, SQL
and JAVA
datatypes can be mapped as follows:
SQL
JAVA
INTEGER
int
CHARACTER(n)
String
VARCHAR(n)
String
REAL
float
DOUBLE
double
DECIMAL(t,d)
java.math.BigDecimal
DATE
java.sql.date
BOOLEAN
boolean
BIT(1)
byte
(DECIMAL(t,d)
was not previously introduced: the t
stands for the number of digits, the d
for the precision.)
We cannot always have that correspondance: what would correspond to a reference variable? To a private attribute? This series of problems is called “object-relational impedance mismatch”, it can be overcomed, but at a cost.
executeQuery
, executeUpdate
and execute
Previously, we used executeQuery
to send a SQL command to the DBMS. This method is tailored for SELECT statement, and it is not the only method we can use.
Name | executeQuery |
executeUpdate |
execute |
---|---|---|---|
Used for | SELECT |
INSERT , UPDATE , DELETE |
Any type |
Input Type | string |
string |
string |
Return Type | ResultSet |
int , the number of rows affected by the query |
boolean , true if the query returned a ResultSet , false if the query returned an int or nothing |
To retrieve the ResultSet
obtained by an execute
statement, you need to use getResultSet
or getUpdateCount
. For more details, consult Oracle
The program in Problem 5.1 (Advanced Java Programming) uses the modifications discussed below. Please refer to it once you are done with this section.
We can pass options (values of fields) when connecting to the database:
Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/HW_DBPROG" + "?user=testuser" + "&password=password" + "&allowMultiQueries=true" + "&createDatabaseIfNotExist=true" + "&useSSL=true");
On top of user
and password
(which are self-explanatory), setting allowMultiQueries
to true
allows to pass multiple queries with one executeUpdate
statement, and createDatabaseIfNotExist
creates the schema passed in the url
(so, here, HW_DBPROG
) if it does not already exist.
The syntax used is the syntax of querying strings, i.e., it follows the pattern
?field1=value1&field2=value2…&fieldN=valueN
That is, it starts with an ? and then “pile up” the field / value pairs with &. In particular, if you needed to add ?serverTimezone=UTC in the first application program we used, you will need here to replace
+ "&useSSL=true");
with
+ "&useSSL=true" + "&serverTimezone=UTC");
You can read about other options at MySQL or JDBC.
We can create a table with the method stmt.execute
.
stmt.execute( "CREATE TABLE DVD (" + "Title CHAR(25) PRIMARY KEY, " + "Minutes INTEGER, " + "Price DOUBLE)");
If we were to execute SHOW TABLES;
after this execute instruction directly in the MySQL interpreter, this would display at the screen:
+---------------------+ | Tables_in_HW_DBPROG | +---------------------+ | DVD | +---------------------+
But here, to access this information, we will use the connection’s metadata. The DatabaseMetaData
is a class used to get information about the database: the driver, the user, the versions, etc. We can use the getMetaData()
method of this class to obtain information about the schema we just created:
DatabaseMetaData md = conn.getMetaData(); ResultSet rs = md.getTables("HW_DBPROG", null, "%", null);
The first parameter of getMetaData()
is the schema’s name, as you probably guessed, and the the third parameter is String tableNamePattern
, i.e., what must match the table name stored in the database to be selected. Here, by using the wildcard %
, we select all the table names (which is only “DVD” at this point).
The getMetaData()
method returns a ResultSet
(here named rs
), where 3
is the TABLE_NAME
. We can now iterate over this rs
object to list all the elements in it, as we would with any ResultSet
object:
while (rs.next()) { System.out.println(rs.getString(3)); }
You can read at Oracle the full specification of this method.
To insert values in our table, we can use stmt.executeUpdate
:
String sqlStatement = "INSERT INTO DVD VALUES ('Gone With The Wind', 221, 3);"; int rowsAffected = stmt.executeUpdate(sqlStatement); System.out.print(sqlStatement + " changed " + rowsAffected + " row(s).\n");
Note that the executeUpdate
returns an integer, the number of rows changed. We can even use this method to perform multiple insertions at the same time, if allowMultiQueries
was set to true, cf. StackOverflow:
String insert1 = "INSERT INTO DVD VALUES ('Aa', 129, 0.2)"; String insert2 = "INSERT INTO DVD VALUES ('Bb', 129, 0.2)"; stmt.executeUpdate(insert1 + ";" + insert2);
Another way of “batch processing” statements (i.e., of executing multiple insertions at the same time) is to use addBatch
(that “loads” statements in the statement
object) and executeBatch()
(that execute all the statements loaded):
String insert3 = "INSERT INTO DVD VALUES ('Cc', 129, 0.2)"; String insert4 = "INSERT INTO DVD VALUES ('DD', 129, 0.2)"; stmt.addBatch(insert3); stmt.addBatch(insert4); stmt.executeBatch();
Note that the database is not solicited until the executeBatch
method is called: we simply loaded the instruction in the program, and connect to the database only once, with all the instructions, when this executeBatch()
instruction is met.
Note also that executeBatch
may be used, per Oracle:
for updating, inserting, or deleting a row; and it may also contain DDL statements such asCREATE TABLE
andDROP TABLE
. It cannot, however, contain a statement that would produce aResultSet
object, such as aSELECT
statement.
Also, the name suggests that it should be possible to fetch the SQL
instructions from a file and load them in your Java
program, but there is actually no easy way to do this, c.f. StackOverflow.
A prepared statement is “a query with a slot”: it is a query that takes one or multiple parameters, is parsed and stored on the database, but not executed. It is only after the value of the slot(s) are fixed by the program that this query can be executed. The program can re-use the same prepared statement with multile (different) values multiple times.
Compared to executing SQL
statements directly, prepared statements have three main advantages:
Let us look at a first example:
/* * We create a string with an empty slot, * represented by "?". */ sqlStatement = "SELECT title FROM DVD WHERE Price <= ?"; /* * We create a PreparedStatement object, using that string with an * empty slot. */ PreparedStatement ps = conn.prepareStatement(sqlStatement); /* * Then, we "fill" the first slot with the value of a variable. */ double maxprice = 0.5; ps.setDouble(1, maxprice); /* * Finally, we can execute the query, and display the results. */ ResultSet result = ps.executeQuery(); System.out.printf("For %.2f you can get:\n", maxprice); while (result.next()) { System.out.printf("\t %s \n", result.getString(1)); }
Note that once the ps PreparedStatement
object is created, we cannot change the content of the query, beside instantiating the slot. cf. e.g. the discussion at StackOverflow.
As we said earlier, a prepared statement can have multiple “slots”, as we can see in that second example:
sqlStatement = "INSERT INTO DVD VALUES (?, ?, ?)"; // Now, our string has 3 empty slots, and it is an INSERT statement. PreparedStatement preparedStatement = conn.prepareStatement(sqlStatement); preparedStatement.setString(1, "The Great Dictator"); preparedStatement.setInt(2, 124); preparedStatement.setDouble(3, 5.4); rowsAffected = preparedStatement.executeUpdate(); /* You can check "by hand" that this statement was correctly * executed. Note that the toString method is quite verbose. */ System.out.print(preparedStatement.toString() + " changed " + rowsAffected + " row(s).\n");
Where we stored the integer value returned by executeUpdate
and displayed the the prepared statement using the toString
method.
If we try to mess things up, i.e., provide wrong datatypes:
preparedStatement.setString(1, "The Great Dictator"); preparedStatement.setString(2, "Not-an-integer"); preparedStatement.setString(3, "Not-a-double"); /* This command will make your program crash: * rowsAffected = preparedStatement.executeUpdate(); */
Java compiler will be ok, but we’ll have an error at execution time when executing the query.
Executing rowsAffected = preparedStatement.executeUpdate();
would return an error containing
com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect integer value: 'Not-an-integer' for column `HW_DBPROG`.`DVD`.`Minutes` at row 1
since "Not-an-integer"
is not … a valid integer!
Of course, prepared statements are particularly convenient when you want to automate some tasks or repeat them multiple times, as you write the query only once, and then re-use it. For instance, inserting the whole “Saw” franchise can be made into a loop:
for (int i = 1; i < 5; i++) { preparedStatement.setString(1, "Saw " + i); preparedStatement.setInt(2, 100); preparedStatement.setDouble(3, .5); preparedStatement.executeUpdate(); }
When you create the Statement
objects, you can give two arguments to the createStatement
method:
Statement stmtNew = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
Those options change two things about the ResultSet
we obtain using this statement The first argument indicates whenever you can scroll (go forward and backward) in the ResultSet
objects that will be created using this Statement
object:
TYPE_FORWARD_ONLY
is the default (you can only move forward).TYPE_SCROLL_INSENSITIVE
means that you can scroll, but that updates don’t impact result set.TYPE_SCROLL_SENSITIVE
means that you can scroll, and that updates impact result set.Allowing to go in both direction extends the methods one can use in the ResultSet
class: now, to scroll through the results, one can use:
first()
last()
next()
previous()
relative(x)
: move cursor x times (positive = forward, negative = backward)absolute(x)
: move to the row number x, where 1 is the first.The second argument is the concurrency level, it indicates whenever you can update the values into the ResultSet
directly.
CONCUR_READ_ONLY
is the default.CONCUR_UPDATABLE
means that we can change the database without issuing SQL statement.In other terms, manipulting the ResultSet
object will directly impact the data stored in the database if we set the second parameter to CONCUR_UPDATABLE
.
This createStatement
method is documented at Oracle.
You can find below a simple example of “scrollable” ResultSet
:
// code/java/ScrollingProgram.java import java.sql.*; public class ScrollingProgram { public static void main(String[] args) { try (Connection conn = DriverManager.getConnection( // We connect to the database, not to a particular schema. "jdbc:mysql://localhost:3306/" + "?user=testuser" + "&password=password" + "&allowMultiQueries=true" /* * We want to allow multiple statements * to be shipped in one execute() call. */ ); Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); /* * Finally, we want to be able to move back and forth in our * ResultSets. This implies that we have to also chose if the * ResultSets will be updatable or not: we chose to have them * to be "read-only". */ ) { /* * Before you ask: no, there are no "simple" way of * constructing a string over multiple lines, * besides concatenating them, * cf. e.g. https://stackoverflow.com/q/878573 */ stmt.execute( "DROP SCHEMA IF EXISTS HW_SCROLLABLE_DEMO;" + /* * We drop the schema we want to use if it already exists. * (This allows to execute the same program multiple times.) */ "CREATE SCHEMA HW_SCROLLABLE_DEMO;" + "USE HW_SCROLLABLE_DEMO;" + // We create and use the schema. "CREATE TABLE TEST(" + " Id INT" + ");" // The schema contains only one very simple table. ); /* * We can execute all those queries at once * because we passed the "allowMultiQueries=true" * token when we created the Connection object. */ // Let us insert some dummy values in this dummy table: for (int i = 0; i < 10; i++) stmt.addBatch("INSERT INTO TEST VALUES (" + i + ")"); /* * no ";" in the statements that we add * to the batch! */ stmt.executeBatch(); // We execute the 10 statements that were loaded at once. // Now, let us write a simple query, and navigate in the result: ResultSet rs = stmt.executeQuery("SELECT * FROM TEST"); /* * We select all the tuples in the table. * If we were to execute this instruction on the * command-line interface, we would get: * MariaDB [HW_SCROLLABLE_DEMO]> SELECT * FROM TEST; * +----+ * | Id | * +----+ * | 0 | * | 1 | * | 2 | * | 3 | * | 4 | * | 5 | * | 6 | * | 7 | * | 8 | * | 9 | * +----+ * 10 rows in set (0.001 sec) */ // We can "jump" to the 8th result in the set: rs.absolute(8); System.out.printf("%-22s %s %d.\n", "After absolute(8),", "we are at Id", rs.getInt(1)); /* Note that this would display "7" since the * 8th result contains the value 7 (sql starts * counting at 1. */ // We can move back 1 item: rs.relative(-1); System.out.printf("%-22s %s %d.\n", "After relative(-1),", "we are at Id", rs.getInt(1)); // We can move to the last item: rs.last(); System.out.printf("%-22s %s %d.\n", "After last(),", "we are at Id", rs.getInt(1)); // We can move to the first item: rs.first(); System.out.printf("%-22s %s %d.\n", "After first(),", "we are at Id", rs.getInt(1)); conn.close(); } catch (SQLException ex) { ex.printStackTrace(); } } }
You can also have a look at the end of code/java/AdvancedProg.java
, which creates a second Statement
object is created and used.
What are the technologies that make it possible for a Java application to communicate with a DBMS?
Why is it important to have the statements creating the connection to the database inside a try…catch
statement?
Name three classes in the SQL API of java.
What JDBC method do you call to get a connection to a database?
What is the class of the object used to create a ResultSet
object?
Briefly explain what the next()
method from the ResultSet
class does and give its return type.
How do you submit a SELECT
statement to the DBMS?
What method should be used to perform an INSERT
command from your program?
Where is a ResultSet
object’s cursor initially pointing? How do you move the cursor forward in the result set?
Give three navigation methods provided by ResultSet
.
Explain this JDBC URL format:
jdbc:mysql://localhost:3306/HW_NewDB?createDatabaseIfNotExist=true&useSSL=true
In what class is the getColumnName()
method?
Assuming stmt is a Statement
object, in the statement:
modif = stmt.executeUpdate(strC);
What is…
modif
?strC
?strC
?What is a prepared statement?
Assume ps
is the prepared statement:
INSERT INTO EXAM VALUES (?, ?);
Write the three statements needed to allocate “Quiz” and “5” to the two slots and to execute the prepared statement in the database.
In the code below, there are five errors between line 13 and line 32. They are not subtle Java errors (like misspelling a key word) and do not come from the DBMS (so you should assume that the password is correct, that the database exists, etc.). Highlight each error and explain why it is an error.
// code/java/ProgWithErrors.java import java.sql.*; public class ProgWithErrors { public static void main(String[] args) { try (Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/" + "HW_TestDB?user=testuser&password=password"); Statement stmt = conn.createStatement(); ) { // Errors after this point. String strSelect = "SELECT title FROM DISKS WHERE qty > 40;"; ResultSet rset = stmt.executeUpdate(strSelect); System.out.println("The records selected are: (listed last first):"); rset.last(); while (rset.previous()) { String title = rset.getDouble("title"); System.out.println(title + "\n"); } String sss = "SELECT title FROM DISKS WHERE Price <= ?"; PreparedStatement ps = conn.prepareStatement(sss); ResultSet result = ps.executeQuery(); conn.close(); // Errors before this point. } catch (SQLException ex) { ex.printStackTrace(); } } }
Write a program that determines if the null value from Java code is equal to the NULL
value in the DBMS.
The technologies theat make it possible for a Java application to communicate with w DBMS are API’s and the drivers to implement them.
It is important to put the statements that create the connection to the database inside the try…catch
statement because the program will interact with the environment if this interraction fails (typically, if the connection does not succeed), for which we want to be able to catch the exception and recover from that failure.
There are many classes in the SQL API if Java. There are Connection
, DatabaseMetaData
, ResultSetMetaData
, PreparedStatement
, and Statement
to name a few. You can find them listed at Oracle.
The JDBC method that must be called to connect to a database is DriverManager.getConnection()
The class of the object used to create a ResultSet
object is the Statement
class. A Statement
object is used to create a ResultSet
object, e.g. by calling the executeQuery
method.
The next()
method checks if there is data to read and, if there is, it moves the cursor to read it. Its return type is Boolean.
You submit a SELECT
statement to the DBMS by using .executeQuery(strSelect)
.
The executeUpdate()
or execute()
methods can be used to perform an INSERT
command from our program.
The ResultSet
object’s cursor is initially pointing at the position before the first line. We move the cursor forward by using the next()
method.
There are many navigation methods provided by ResultSet
. They are the first()
, last()
, next()
, previous()
, relative()
, and absolute()
methods.
This JDBC URL format connects to localhost:3306
, creates a new database if needed, and uses the secure SSL connection.
The getColumnName()
method is in the ResultSetMetaData
class.
In the statement modif = stmt.executeUpdate(strC);
…
modif
is an integer (the number of rows modified by the query).strC
is a String
(a SQL
command).strC
is DELETE FROM BOOKS Where Price > 0.5
.A prepared statement is a feature used to execute SQL
statements repeatedly with high efficiency that protects against SQL
injections.
ps.setString(1,"Quiz"); ps.setInt(2, 5); ps.execute();
The errors are:
ResultSet
object creation line and should only be this part:
stmt.executeUpdate(strSelect);
The error is that the executeUpdate()
method cannot be used to perform SELECT
statements.rset.previous()
This error is subtle: we need to display the last record before using the previous()
method, otherwise it would be skipped. We can fix this using a do…while
loop.title
:
The error is that theString title = rset.getDouble("title");
getDouble()
method returns a double
, which cannot be stored as a String
.ResultSet
object named result
:
ps.executeQuery();
The error here comes from the previous prepared statement that did not receive a value for the ?
.You can find the corrected program in `code/java/ProgWithErrorsPatched.java`, which looks like: ```{.bash} 16c16 < ResultSet rset = stmt.executeUpdate(strSelect); --- > ResultSet rset = stmt.executeQuery(strSelect); // Error 1 21,24c21,24 < while(rset.previous()) { < String title = rset.getDouble("title"); < System.out.println(title + "\n"); < } --- > do { // Error 2 > String title = rset.getString("title"); // Error 3 > System.out.println(title); // Not an error, but we probably do not need two new lines. > }while(rset.previous()); // Error 2 bis 27a28 > ps.setInt(1, 10); // Error 4 ```
Here is what the program should look like:
// code/java/TestForNull.java import java.sql.*; public class TestForNull { public static void main(String[] args) { try (Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/HW_DBPROG?user=testuser&password= password&createDatabaseIfNotExist= true&serverTimezone=UTC"); Statement stmt = conn.createStatement(); ) { stmt.execute("CREATE TABLE Test (" + "A CHAR(25), " + "B INTEGER, " + "C DOUBLE)"); String strAdd = "INSERT INTO Test VALUES (NULL, NULL, NULL);"; int number_of_row_changed = stmt.executeUpdate(strAdd); System.out.print("This last query changed " + number_of_row_changed + " row(s).\n"); ResultSet result = stmt.executeQuery("SELECT * FROM Test"); if (result.next()) { System.out.print(result.getString(1) + " " + result.getDouble(2) + " " + result.getInt(3)); if (result.getString(1) == null) { System.out.print("\nAnd null for CHAR in SQL is null for String in Java.\n"); } } conn.close(); } catch (SQLException ex) { ex.printStackTrace(); } } }
This program should display:
This last query changed 1 row(s). null 0.0 0 And null for CHAR in `SQL` is null for String in Java.
Read, execute, break, edit, compile, patch, hack and (most importantly) understand the following program:
// code/java/AdvancedProg.java /* * This is a long program, introducing: * I. How to pass options when connecting to the database, * II. How to create a table and read its meta-data, * III. How to insert values, * IV. How to use prepared statements, * V. How to read backward and write in ResultSets. * * To be able to execute this program multiple times, the schema * is dropped and re-created. * */ import java.sql.*; public class AdvancedProg { public static void main(String[] args) { try ( // I. Passing options to the database // start snippet passing-options Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/HW_DBPROG" + "?user=testuser" + "&password=password" + "&allowMultiQueries=true" + "&createDatabaseIfNotExist=true" + "&useSSL=true"); // end snippet passing-options Statement stmt = conn.createStatement(); ) { /* * Below, we drop the schema and re-create it to allow multiple * execution of the * program. You can ignore this part if you want. */ stmt.execute( "DROP SCHEMA IF EXISTS HW_DBPROG;" + "CREATE SCHEMA HW_DBPROG;" + "USE HW_DBPROG;"); // II. Creating a table and reading its meta-data // start snippet table-creation stmt.execute( "CREATE TABLE DVD (" + "Title CHAR(25) PRIMARY KEY, " + "Minutes INTEGER, " + "Price DOUBLE)"); // end snippet table-creation // start snippet table-metadata-1 DatabaseMetaData md = conn.getMetaData(); ResultSet rs = md.getTables("HW_DBPROG", null, "%", null); // end snippet table-metadata-1 // start snippet table-metadata-2 while (rs.next()) { System.out.println(rs.getString(3)); } // end snippet table-metadata-2 // III. Inserting values // start snippet inserting-1 String sqlStatement = "INSERT INTO DVD VALUES ( 'Gone With The Wind', 221, 3);"; int rowsAffected = stmt.executeUpdate(sqlStatement); System.out.print(sqlStatement + " changed " + rowsAffected + " row(s).\n"); // end snippet inserting-1 // start snippet inserting-2 String insert1 = "INSERT INTO DVD VALUES ('Aa', 129, 0.2)"; String insert2 = "INSERT INTO DVD VALUES ('Bb', 129, 0.2)"; stmt.executeUpdate(insert1 + ";" + insert2); // end snippet inserting-2 // start snippet inserting-3 String insert3 = "INSERT INTO DVD VALUES ('Cc', 129, 0.2)"; String insert4 = "INSERT INTO DVD VALUES ('DD', 129, 0.2)"; stmt.addBatch(insert3); stmt.addBatch(insert4); stmt.executeBatch(); // end snippet inserting-3 // IV. Prepared Statements // start snippet prepared-queries-1 /* * We create a string with an empty slot, * represented by "?". */ sqlStatement = "SELECT title FROM DVD WHERE Price <= ?"; /* * We create a PreparedStatement object, using that string with an * empty slot. */ PreparedStatement ps = conn.prepareStatement(sqlStatement); /* * Then, we "fill" the first slot with the value of a variable. */ double maxprice = 0.5; ps.setDouble(1, maxprice); /* * Finally, we can execute the query, and display the results. */ ResultSet result = ps.executeQuery(); System.out.printf("For %.2f you can get:\n", maxprice); while (result.next()) { System.out.printf("\t %s \n", result.getString(1)); } // end snippet prepared-queries-1 // start snippet prepared-queries-2 sqlStatement = "INSERT INTO DVD VALUES (?, ?, ?)"; // Now, our string has 3 empty slots, and it is an INSERT statement. PreparedStatement preparedStatement = conn.prepareStatement(sqlStatement); preparedStatement.setString(1, "The Great Dictator"); preparedStatement.setInt(2, 124); preparedStatement.setDouble(3, 5.4); rowsAffected = preparedStatement.executeUpdate(); /* You can check "by hand" that this statement was correctly * executed. Note that the toString method is quite verbose. */ System.out.print(preparedStatement.toString() + " changed " + rowsAffected + " row(s).\n"); // end snippet prepared-queries-2 // start snippet prepared-queries-3 preparedStatement.setString(1, "The Great Dictator"); preparedStatement.setString(2, "Not-an-integer"); preparedStatement.setString(3, "Not-a-double"); /* This command will make your program crash: * rowsAffected = preparedStatement.executeUpdate(); */ // end snippet prepared-queries-3 // start snippet prepared-queries-4 for (int i = 1; i < 5; i++) { preparedStatement.setString(1, "Saw " + i); preparedStatement.setInt(2, 100); preparedStatement.setDouble(3, .5); preparedStatement.executeUpdate(); } // end snippet prepared-queries-4 // V. Reading backward and writing in ResultSets // start snippet new-statement-1 Statement stmtNew = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); // end snippet new-statement-1 // Reading backward sqlStatement = "SELECT title FROM DVD WHERE Price < 1;"; result = stmtNew.executeQuery(sqlStatement); System.out.println("For $1, you can get:"); if (result.last()) { // We can jump to the end of the ResultSet System.out.print(result.getString("Title") + " "); } System.out.print("and also, (in reverse order)"); while (result.previous()) { // Now we can scroll back! System.out.print(result.getString("Title") + " "); } // Changing the values System.out.print("\n\nLet us apply a 50% discount. Currently, the prices are:\n"); sqlStatement = "SELECT title, price FROM DVD;"; result = stmtNew.executeQuery(sqlStatement); while (result.next()) { System.out.printf("%20s \t $%3.2f\n", result.getString("title"), result.getDouble("price")); } // We need to scroll back! result.absolute(0); while (result.next()) { double current = result.getDouble("price"); result.updateDouble("price", (current * 0.5)); result.updateRow(); } System.out.print("\n\nAfter update, the prices are:\n"); // We need to scroll back! result.absolute(0); while (result.next()) { System.out.printf("%20s \t $%3.2f\n", result.getString("title"), result.getDouble("price")); } conn.close(); } catch (SQLException ex) { ex.printStackTrace(); } } }
Consider the code below:
// code/java/GuestProgram.java // java.util.Scanner is an API to read from the keyboard. import java.sql.*; import java.util.Scanner; // This first part is "standard". Just note that we allow multiple statements. public class GuestProgram { public static void main(String[] args) { try (Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/?user=testuser&password=password" + "&allowMultiQueries=true"); Statement stmt = conn.createStatement(); ) { // We create a schema, use it, create two tables, // and insert a value in the second one. stmt.execute( "CREATE SCHEMA HW_GUEST_PROGRAM;" + "USE HW_GUEST_PROGRAM;" + "CREATE TABLE GUEST(" + "Id INT PRIMARY KEY," + "Name VARCHAR(30)," + "Confirmed BOOL" + ");" + "CREATE TABLE BLACKLIST(" + "Name VARCHAR(30)" + ");" + "INSERT INTO BLACKLIST VALUES (\"Marcus Hells\");"); /* * INSERT HERE Solution to exercises 1, 2 and 3. * Tip for Exercise 1, this solves the first item. */ System.out.print("How many guests do you have?\n"); Scanner key = new Scanner(System.in); int guest_total = key.nextInt(); } catch (SQLException ex) { ex.printStackTrace(); } } }
In the following three exercises, you will add some code below the comment // INSERT HERE Solution to exercises 1, 2 and 3.
in order to obtain a behavior like the following one (you do not have to reproduce it exactly!). The user input is underlined, and hitting “enter” is represented by ↵
:
How many guests do you have? ͟2͟↵ Enter name of guest 1. M͟a͟r͟c͟u͟s͟ ͟H͟e͟l͟l͟s͟↵ Enter name of guest 2. C͟y͟n͟t͟h͟i͟a͟ ͟H͟e͟a͟v͟e͟n͟s͟↵ ……………⌛…………… Oh no, (at least) one of the guest from the black list confirmed their presence! The name of the first one is Marcus Hells. Do you want to remove all the guests that are on the black list and who have confirmed their presence? Enter "Y" for yes, anything else for no.
You should suppose that BLACKLIST
contains more than one name, and that some other operations are performed where ……………⌛…………… is (typically, some guests will confirm their presence). Using batch processing or prepared statements will be a plus, but is not mandatory to solve these exercises.
key.nextLine()
, that returns the String
entered by the user),GUEST
table an integer that is incremented after each insertion, the name entered by the user, and NULL
.The file code/java/GuestProgramSolution.java
contains the whole code for you to compile and test.
Pb 5.2 – Solution to Q. 1
We explore two solutions, one with batch processing, the second with prepared statement.
They both start with:
int guest_id; String guest_name; int counter = 0;
Then the solution using batch processing could be:
while (counter < guest_total) { // Ask the name of the guest. System.out.print("Enter name of guest " + (counter + 1) + ".\n"); // Read the name of the guest. guest_name = key.nextLine(); stmt.addBatch("INSERT INTO GUEST VALUES (" + counter + ", \"" + guest_name + "\", NULL)"); // Add to the batch the statement to insert the required data in the table counter++; } stmt.executeBatch(); // Execute the batch statement.
while the solution using prepared statements could be:
PreparedStatement ps = conn.prepareStatement("INSERT INTO GUEST VALUES( ?, ?, NULL);"); while (counter < guest_total) { System.out.print("Enter name of guest " + (counter + 1) + ".\n"); guest_name = key.nextLine(); ps.setInt(1, counter); ps.setString(2, guest_name); ps.executeUpdate(); counter++; }
Pb 5.2 – Solution to Q. 2
We let SQL
do all the hard work:
int guest_id; String guest_name; int counter = 0;
Then the solution using batch processing could be:
while (counter < guest_total) { // Ask the name of the guest. System.out.print("Enter name of guest " + (counter + 1) + ".\n"); // Read the name of the guest. guest_name = key.nextLine(); stmt.addBatch("INSERT INTO GUEST VALUES (" + counter + ", \"" + guest_name + "\", NULL)"); // Add to the batch the statement to insert the required data in the table counter++; } stmt.executeBatch(); // Execute the batch statement.
while the solution using prepared statements could be:
PreparedStatement ps = conn.prepareStatement("INSERT INTO GUEST VALUES( ?, ?, NULL);"); while (counter < guest_total) { System.out.print("Enter name of guest " + (counter + 1) + ".\n"); guest_name = key.nextLine(); ps.setInt(1, counter); ps.setString(2, guest_name); ps.executeUpdate(); counter++; }
Pb 5.2 – Solution to Q. 2
We let SQL do all the hard work:
ResultSet rset = stmt.executeQuery( "SELECT * FROM GUEST, BLACKLIST WHERE GUEST.Name = BLACKLIST.Name AND GUEST.Confirmed = true"); if (rset.next()) { System.out.print( "Oh no, (at least) one of the guest from the black list confirmed their presence! \nThe name of the first one is " + rset.getString(2) + ".\n"); }
Pb 5.2 – Solution to Q. 3
Similarly, we let SQL do all the hard work:
System.out.print( "Do you want to remove all the guests that are on the black list and confirmed their presence? Enter \"Y\" for yes, anything else for no.\n"); if (key.nextLine().equals("Y")) { stmt.execute( "DELETE FROM GUEST WHERE NAME IN (SELECT NAME FROM BLACKLIST) AND Confirmed = true;"); }