Execution of queries with JDBC

What are the database queries made with?

Java uses three interfaces to query the database:

  • java.sql.Statement - for SQL statements without parameters;
  • java.sql.PreparedStatement - for SQL statements with parameters and frequently executed statements;
  • java.sql.CallableStatement - for executing procedures stored in the database.

Interface bearers are created using the methods of the java.sql.Connection object:

  • java.sql.createStatement() returns a Statement object;
  • java.sql.prepareStatement() returns a PreparedStatement object;
  • java.sql.prepareCall() returns a CallableStatement object;

Difference between Statement and PreparedStatement

  • Statement: Used for simple query cases without parameters.
  • PreparedStatement: precompiles a query that can contain input parameters and be executed multiple times with a different set of those parameters.

Before execution, the DBMS parses each query, optimizes it, and creates a query plan for its execution. If the same query is executed several times, then the DBMS is able to cache the plan of its execution and not perform the disassembly and optimization stages again. This makes the request faster.

In summary: PreparedStatement compares favorably with Statement in that when reused with one or more parameter sets, it can take advantage of a pre-compiled and cached query while helping to avoid SQL Injection.

How are database queries and results processed?

Queries are executed by calling methods of an object that implements the java.sql.Statement interface:

  • executeQuery() - for queries that result in a single set of values, such as SELECT queries. The result of execution is an object of the java.sql.ResultSet class;
  • executeUpdate() - for executing INSERT, UPDATE or DELETE statements, as well as for Data Definition Language (DDL) statements. The method returns an integer indicating how many records have been modified;
  • execute() - Executes SQL commands that can return different results. For example, it can be used for a CREATE TABLE operation. Returns true if the first result contains a ResultSet and false if the first result is the number of modified records or there is no result. To get the first result, you must call the getResultSet() or getUpdateCount() method. The rest of the results are available through the getMoreResults() call, which can be performed multiple times if necessary.

An object with the java.sql.ResultSet interface stores the result of a database query - a certain data set, inside which there is a cursor pointing to one of the data set elements - the current record.

Using a cursor, you can navigate through the dataset using the next() method.

Immediately after receiving a data set, its cursor is in front of the first record, and to make it the current one, you need to call the next() method.

The content of the fields of the current record is available through calls to the methods getInt(), getFloat(), getString(), getDate() and the like.

Call a stored procedure

Stored procedures are a named set of SQL statements stored on the server. Such a procedure can be called from a Java class by calling methods on an object that implements the java.sql.Statement interface.

The choice of an object depends on the characteristics of the stored procedure:

  • no parameters -> Statement
  • with parameters -> PreparedStatement
  • with in and out parameters -> CallableStatement

If you do not know how the stored procedure was defined, you can use the java.sql.DatabaseMetaData methods to obtain information about the stored procedure (for example, the names and types of parameters) to obtain information about the structure of the data source.

Close the connection to the database

The connection to the database is closed by calling the close() method on the corresponding java.sql.Connection object, or by using the try-with-resources mechanism to create such an object, which was introduced in Java 7.

You must first close all requests created by this connection.

Read also:


Comments

Post a Comment

Popular posts from this blog

Methods for reading XML in Java

Image
DOM (Document Object Model) - object - reads XML, recreating it in memory in the form of an object structure, while the XML document is represented as a set of tags - nodes. Each node can have an unlimited number of child nodes. Each child can also contain several levels of descendants or none at all. Thus, the result is a kind of tree. Pluses: Easy to program. If there are many objects in XML with cross-references to each other, it is enough to go through the document twice: first time create objects without references and fill in the name-object dictionary, the second time - restore links. If an error occurs in XML, a half-created XML structure remains in memory, which will be automatically destroyed. Suitable for both reading and writing. Minuses: Low speed. Wastes a lot of memory. SAX (Simple API for XML) - event-driven - reads an XML document, responding to emerging events (opening or closing tag, string, attribute) by calling the event handlers provided by the appl...
Read more

XML, well-formed XML and valid XML

Image
XML, the eXtensible Markup Language, is a language with a simple formal syntax, well suited for creating and manipulating documents with programs, and at the same time easy to read and create documents by humans. XML is extensible, it does not fix the markup used in documents, and the developer is free to create markup in accordance with the needs of a particular area, being limited only by the syntax rules of the language. DTD A DTD, Document Type Definition, is a predefined set of rules that defines the relationships between elements and attributes. For example, the DTD for HTML says that the DIV tag must be inside the BODY tag and can appear multiple times, TITLE - in HEAD and only once, and SCRIPT - both there and there as many times as you want. A DTD is usually described directly in the document as a formulation line starting with <!DOCTYPE ... > or in a separate file. Difference between well-formed XML and valid XML Depending on the level of compliance, the document...
Read more

ArrayList and LinkedList in Java, memory usage and speed

Image
How much extra memory is needed when calling ArrayList.add()? If there is enough space in the array to accommodate a new element, then no additional memory is required. Otherwise, a new array is created, 1.5 times larger than the existing one (this is true for JDK above 1.7, in earlier versions the size of the increase is different). How much extra memory is allocated when you call LinkedList.add()? One new instance of the nested Node class is created. How much memory to store one byte primitive in LinkedList? Each LinkedList element stores a link to the previous element, the next element, and a data link. private static class Node<E> { E item; Node<E> next; Node<E> prev; //... } For 32-bit systems, each link is 32 bits (4 bytes). The object (header) of the nested Node class itself takes 8 bytes. 4 + 4 + 4 + 8 = 20 bytes, and since the size of each object in Java is a multiple of 8, so we get 24 bytes. A byte primitive occupies 1 by...
Read more