JDBC  «Prev  Next»
Lesson 1

Using Database Table using JDBC

In this module, you will learn about some of the requests you can make to the database itself. In a previous exercise, you saw a complete JDBC program. You wrote the driver and connection Statements in the program. You will next learn about some of the commands you can use to communicate with a database system once the connection has been established. In this module, you will explore methods that will allow you to use those databases. The story continues on your journey as a team member working on the Brazilian Hospital Database Project. You have made a connection to a pilot database, and the customer was happy that all went well. Having a connection is a good first step, but to manage the project's information, you will have to get data into and out of a database. Your objective will be to put information into the database. Along the way, you will first learn how to get information about the database that you will be loading with data.
In an effort to move forward with the project, your next goal will be to populate the database table with the data from the pilot test group that consisted of hospitals and doctors. It is imperative that this be completed within 48 hours, as you will need to give Dr. Tucano a status update then. After completing this module, you will have the skills and knowledge to:
  1. Describe the purpose of Statement objects
  2. Describe the purpose of ResultSets
  3. Describe how to use other ResultSet methods to manipulate data
  4. Describe the reason for using DatabaseMetaData methods
  5. Obtain database content information by using the various DatabaseMetaData methods


Purpose of the Statement object in JDBC

In JDBC (Java Database Connectivity), the Statement object is used to execute SQL queries against a relational database and retrieve the results. It provides an interface for sending SQL commands to the database and is part of the java.sql package.
Purpose of the Statement Object
  1. Execute SQL Queries: The primary purpose of the Statement object is to allow Java applications to send SQL commands (like SELECT, INSERT, UPDATE, and DELETE) to a database and execute them.
  2. Types of SQL Statements:
    • Queries that return results: Such as SELECT, which retrieves data from the database.
    • Queries that modify the database: Such as INSERT, UPDATE, DELETE, which change data in the database.
  3. Executing Statements: The Statement object provides different methods for executing SQL commands:
    • executeQuery(String sql): Used for executing SQL SELECT queries. It returns a ResultSet object containing the result of the query.
    • executeUpdate(String sql): Used for executing SQL INSERT, UPDATE, or DELETE statements, or DDL commands like CREATE TABLE. It returns an int representing the number of rows affected by the query.
    • execute(String sql): Can be used to execute any kind of SQL statement, and returns a boolean indicating whether the result is a ResultSet (for SELECT) or an update count (for INSERT, UPDATE, or DELETE).
  4. Retrieving Query Results: For SELECT queries, the Statement object returns a ResultSet that allows you to iterate over the rows and columns of the query result. 
    Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM employees");

while (rs.next()) {
   String name = rs.getString("name");
   int age = rs.getInt("age");
   // Process the result set
}
  5. Managing Connections: The Statement object works within the context of a Connection to a database. It ensures that the SQL commands are executed on the open database connection.


Methods of Statement Object
  1. executeQuery(String sql): Executes a SQL SELECT query and returns a ResultSet object with the data. 
    ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
  2. executeUpdate(String sql): Executes an SQL INSERT, UPDATE, DELETE, or DDL (CREATE, ALTER, etc.) statement.
    Returns the number of affected rows. 
    int rowsAffected = stmt.executeUpdate("UPDATE employees SET salary = 5000 WHERE id = 1");
  3. execute(String sql): Used for more general cases, where the SQL might return either a ResultSet (for queries) or an update count (for modifications). Returns a boolean: 1) true if it returns a ResultSet, false if it returns an update count. 
       boolean hasResultSet = stmt.execute("SELECT * FROM employees");
  4. close(): Closes the Statement and releases resources.

Example Usage: 
Connection connection = DriverManager.getConnection(url, user, password);
Statement stmt = connection.createStatement();

// Execute SELECT query
ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
while (rs.next()) {
    String name = rs.getString("name");
    int age = rs.getInt("age");
    // Process the result set
}

// Execute UPDATE query
int rowsAffected = stmt.executeUpdate("UPDATE employees SET salary = 5000 WHERE id = 1");

// Close the statement and connection
stmt.close();
connection.close();


Limitations of Statement
  • SQL Injection: If user input is directly concatenated into a SQL statement, it may lead to SQL injection vulnerabilities.
    For safer execution of SQL queries, PreparedStatement is recommended, as it allows parameterized queries.
  • Performance: PreparedStatement and CallableStatement are often preferred for repeated queries or stored procedures, as they are precompiled, which improves performance and security.

Conclusion The Statement object in JDBC is an essential tool for executing SQL commands and retrieving results. While it serves its purpose well, in modern applications, PreparedStatement is generally preferred for better security and performance when working with dynamic queries.
In the next lesson, you will explore objects and how you can send SQL requests to the database.

SEMrush Software