**Course Title:** Comprehensive Java Programming: From Basics to Advanced Concepts **Section Title:** Database Connectivity with JDBC **Topic:** Executing SQL queries from Java applications. ### Introduction to Executing SQL Queries In the previous topics, we have learned how to establish a connection to a relational database using JDBC. Now, we will discuss how to execute SQL queries from our Java application to interact with the database. ### Creating a Statement Object Before executing SQL queries, we need to create a Statement object. The Statement object is used to send SQL commands to the database and execute queries. We can create a Statement object using the Connection object: ```java Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "username", "password"); Statement stmt = con.createStatement(); ``` ### Executing SQL Queries Once we have created a Statement object, we can use it to execute SQL queries. We can use the following methods to execute SQL queries: * `executeQuery(String sql)`: This method executes a SELECT query and returns a ResultSet object. * `executeUpdate(String sql)`: This method executes an INSERT, UPDATE, or DELETE query and returns the number of rows affected. * `execute(String sql)`: This method executes a query and returns a boolean value indicating whether the query generated a ResultSet. Here's an example of executing an INSERT query using `executeUpdate()`: ```java Statement stmt = con.createStatement(); int rowsAffected = stmt.executeUpdate("INSERT INTO employees (name, age) VALUES ('John Doe', 30)"); System.out.println("Rows affected: " + rowsAffected); ``` And here's an example of executing a SELECT query using `executeQuery()`: ```java Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM employees WHERE age > 30"); while (rs.next()) { System.out.println(rs.getString("name") + ", " + rs.getInt("age")); } ``` ### Prepared Statements Prepared Statements are used to execute SQL queries with parameters. They provide several benefits over regular Statement objects: * They prevent SQL injection attacks by properly escaping input parameters. * They improve performance by allowing the database to cache the query plan. We can create a Prepared Statement object using the Connection object: ```java Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "username", "password"); PreparedStatement pstmt = con.prepareStatement("SELECT * FROM employees WHERE age > ?"); pstmt.setInt(1, 30); ResultSet rs = pstmt.executeQuery(); ``` ### Batch Updates Batch updates allow us to execute multiple SQL queries in a single database round trip, improving performance. We can add queries to a batch using the `addBatch()` method: ```java Statement stmt = con.createStatement(); stmt.addBatch("INSERT INTO employees (name, age) VALUES ('John Doe', 30)"); stmt.addBatch("INSERT INTO employees (name, age) VALUES ('Jane Doe', 25)"); int[] rowsAffected = stmt.executeBatch(); for (int i = 0; i < rowsAffected.length; i++) { System.out.println("Rows affected by query " + (i + 1) + ": " + rowsAffected[i]); } ``` ### Closing Resources It is essential to close the Statement, ResultSet, and Connection objects after use to release system resources and prevent resource leaks. ```java try { // Use the Statement, ResultSet, and Connection objects } catch (SQLException e) { // Handle the exception } finally { try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } if (con != null) { con.close(); } } catch (SQLException e) { // Handle the exception } } ``` We recommend that you read more about Java JDBC API from the official oracle documentation: [https://docs.oracle.com/javase/tutorial/jdbc/index.html](https://docs.oracle.com/javase/tutorial/jdbc/index.html) ### Conclusion In this topic, we have learned how to execute SQL queries from a Java application using the JDBC API. We have discussed various techniques for executing queries, including using the Statement object, Prepared Statements, and batch updates. ### What's Next? In the next topic, we will discuss how to manage database transactions and handle SQL exceptions. Please let us know if you have any questions or comments about this topic by posting a comment below. We'll be happy to help!