{"id":3465,"date":"2015-05-15T17:32:57","date_gmt":"2015-05-16T01:32:57","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=3465"},"modified":"2024-01-11T02:15:10","modified_gmt":"2024-01-11T09:15:10","slug":"querying-data-from-mysql-using-jdbc","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-jdbc-tutorial\/querying-data-from-mysql-using-jdbc\/","title":{"rendered":"Querying Data From MySQL Using JDBC"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you will learn how to query data from MySQL using JDBC Statement and ResultSet objects.<\/p>\n\n\n\n

This tutorial picks up where the Connecting to MySQL Using JDBC Driver<\/a>\u00a0tutorial left off.<\/p>\n\n\n\n

Step 1<\/strong>. Create a new file called Candidate.java<\/code>.<\/p>\n\n\n\n

Step 2<\/strong>. Define a new class called Candidate<\/code> in the Candidate.java<\/code> file:<\/p>\n\n\n

import<\/span> java.sql.SQLException;\n\npublic class<\/span> Candidate<\/span> <\/span>{\n    public static<\/span> void<\/span> getAll(){\n        String<\/span> sql = \"SELECT first_name, last_name, email \"<\/span> +\n                \"FROM candidates\"<\/span>;\n\n        try<\/span> (var<\/span> conn = MySQLConnection.connect();\n             var<\/span> stmt  = conn.createStatement();\n             var<\/span> rs    = stmt.executeQuery(sql)) {\n\n            \/\/ loop through the result set<\/span>\n            while<\/span> (rs.next()) {\n                System.out.println(\n                    rs.getString(\"first_name\"<\/span>) + \"\\t\"<\/span> +\n                    rs.getString(\"last_name\"<\/span>)  + \"\\t\"<\/span> +\n                    rs.getString(\"email\"<\/span>)\n                );\n\n            }\n        } catch<\/span> (SQLException ex) {\n            System.out.println(ex.getMessage());\n        }\n    }\n}\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import the SQLException<\/code> class from the java.sql<\/code> package. SQLException<\/code> is an exception class that handles database-related errors:<\/p>\n\n\n
import<\/span> java<\/span>.sql<\/span>.SQLException<\/span>;<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, define a class named Candidate<\/code>:<\/p>\n\n\n
public<\/span> class<\/span> Candidate<\/span> <\/span>{<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, define a static method called getAll()<\/code> that retrieves data from the database:<\/p>\n\n\n
public static<\/span> void<\/span> getAll() {<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, construct a query that retrieves the first name and last name from the candidates<\/code> table:<\/p>\n\n\n
String<\/span> sql = \"SELECT first_name, last_name, email \"<\/span> +\n             \"FROM candidates\"<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, open the database connection using the connect()<\/code> method of the MySQLConnection<\/code> class, create a Statement<\/code> object for executing SQL queries, execute the SQL query by calling the executeQuery()<\/code> method of the Statement<\/code> object, and store the result in the ResultSet<\/code> object (rs<\/code>):<\/p>\n\n\n
try<\/span> (var<\/span> conn = MySQLConnection.connect();\n     var<\/span> stmt  = conn.createStatement();\n     var<\/span> rs    = stmt.executeQuery(sql)) {<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that we use the try<\/code> block is a try-with-resources block to automatically close resources like database connections (conn<\/code>), statements (stmt<\/code>), and result sets (rs<\/code>) when the block is exited.<\/p>\n\n\n\n
Sixth, use a while<\/code> loop to iterate through the result set. Fetch each row and print the values of first name, last name, and email to the console:<\/p>\n\n\n
while<\/span> (rs.next()) {\n    System.out.println(\n        rs.getString(\"first_name\"<\/span>) + \"\\t\"<\/span> +\n        rs.getString(\"last_name\"<\/span>)  + \"\\t\"<\/span> +\n        rs.getString(\"email\"<\/span>)\n    );\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Seventh, handle the exception if it occurs:<\/p>\n\n\n
} catch<\/span> (SQLException ex) {\n    System.out.println(ex.getMessage());\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3<\/strong>. Modify the Main.java file that calls the getAll() method of the Candidate class:<\/p>\n\n\n
\npublic class<\/span> Main<\/span> <\/span>{\n    public static<\/span> void<\/span> main(String<\/span>[] args){\n        Candidate.getAll();\n    }\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
If you run the Main program, you’ll see the following output:<\/p>\n\n\n
Carine<\/span> \tSchmitt<\/span>\tcarine<\/span>.s<\/span>@gmail<\/span>.com\nJean\tKing\tjean.k@gmail.com\nPeter\tFerguson\tpeter.f@gmail.com\n...<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the Statement<\/code> object to execute an SQL query.<\/li>\n\n\n\n
  • Use the ResultSet<\/code> object to store the result of a query.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t