How to Use executeQuery() in Java for Database Queries
Retrieve and Manage Data with SQL
Connect Java with MySQL using JDBC Driver
Having successfully established a connection between your Java application and the MySQL database, you can now leverage the executeQuery() method to execute SQL queries and retrieve data from the database.
Now let's take a look on how we can implement executeQuery() in our application.
In this example we will get the average final amount using AVG() one of the Aggregate functions in SQL
1
2// !!! import this !!!
3import java.sql.PreparedStatement;
4import java.sql.ResultSet;
5import java.sql.Connection
6
7// in your CLASS
8// initialize variable
9// myRS with type ResultSet
10// myPStmt with type PreparedStatement
11// myConn with type Connection
12
13public class ExampleExequteQeury {
14
15 public static PreparedStatement myPStmt = null;
16 public static ResultSet myRs = null;
17 public static Connection myConn = null;
18
19 // create a METHOD
20 // In here I'm using NetBeans
21 // this method works when you click the button
22 // the action will execute the query
23
24 private void btnAvgActionPerformed(java.awt.event.ActionEvent evt){
25
26 try {
27
28 // sample query
29 // "SELECT AVG(<field>) from <table name>"
30
31 String query = "SELECT AVG(finalamount) from tblentry";
32
33 // Prepare Statement
34
35 myPStmt = myConn.prepareStatement(query);
36
37 // Execute SQL query and get the result
38
39 myRs = myPStmt.executeQuery();
40
41 if (myRs.next()) {
42
43 String averageAmount = myRs.getString(1)
44 System.out.println(averageAmount);
45
46 } else {
47
48 System.out.println("No Data Found");
49
50 }
51
52 } catch(SQLException e) {
53
54 JOptionPane.showMessageDialog(null, "SQL Error: " + e.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
55 }
56
57 }
58
59}
60Imports
1import java.sql.PreparedStatement;Imports the PreparedStatement class for executing SQL queries with placeholders for values.
1import java.sql.ResultSet;Imports the ResultSet class for representing a set of results returned from a database query.
1import java.sql.Connection;Imports the Connection class for representing a connection to a database.
Variable Initialization
1public static PreparedStatement myPStmt = null;Declares a variable myPStmt to hold a PreparedStatement object, initialized to null.
1public static ResultSet myRs = null;Declares a variable myRs to hold a ResultSet object, initialized to null.
1public static Connection myConn = null;Declares a variable myConn to hold a Connection object, initialized to null.
Method Explanation
1try {...} catch(SQLException e) {...}Uses a try-catch block to handle potential SQL exceptions.
1String query = "SELECT AVG(finalamount) from tblentry";Defines the SQL query to calculate the average of the finalamount column from the tblentry table.
1myPStmt = myConn.prepareStatement(query);Creates a PreparedStatement object from the connection and the query string.
1myRs = myPStmt.executeQuery();Executes the query and stores the results in the myRs object.
1if (myRs.next()) {...} else {...}Checks if there are any results in the ResultSet.
1String averageAmount = myRs.getString(1);Retrieves the first value (average amount) from the ResultSet as a string.
1System.out.println(averageAmount);Prints the average amount to the console.
1System.out.println("No Data Found")Prints a message indicating no data was found.
1JOptionPane.showMessageDialog(...)Displays an error dialog box with the SQL error message if an exception occurs.
Done!
Created
coding
java
mysql
jdbc
database query
sql
data retrieval
data manipulation
Back on Top
If you have any questions or feedback about this article feel free to email me here. Have a great day!
