Select query in jdbc java

SELECT records from a table with JDBC

Note : The JDBC classes and interfaces are available in java.sql and javax.sql packages.

Steps to retrieve records from a table with SELECT SQL statement using JDBC-

Class.forName("oracle.jdbc.driver.OracleDriver");

In the upcoming example, we are going to create a table within the Oracle database using its JDBC driver.

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott", "tiger");

• 1521 is the port number to connect to the database.
• XE stands for Oracle Expess Edition Database.
• scott is our username to connect to the Oracle database and tiger is the password.

Statement stmt = con.createStatement();

Creating a Statement object to using createStatement() method of Connection interface.

ResultSet rs = stmt.executeQuery(query);

query is a String object which specifies the SQL query to execute, it results in a ResultSet object containing the retrieved records in terms of row/rows.

rs.getString(String columnName);

con is an Connection reference used to close the connection with the database.

Retrieving data from database using JDBC

We are going to execute 3 SQL SELECT queries and on the execution of each query, a row will be updated in an existing table MyTable, which we created when we learned how to create a table.

import java.sql.*; class A < public static void main(String. ar) < try < //First SQL SELECT Query String query1 = "SELECT * FROM MyTable"; //Second SQL SELECT Query String query2 = "SELECT * FROM MyTable WHERE Age = '53' "; //Third SQL SELECT Query String query3 = "SELECT FirstName FROM MyTable WHERE LastName = 'Hanks'"; Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott", "tiger"); Statement stmt = con.createStatement(); //Executing first SELECT query ResultSet rs = stmt.executeQuery(query1); System.out.println("Result of executing query1"); System.out.println("ID " + "\t" + "FirstName" + "\t" + "LastName" + "\t" + "Age"); //looping through the number of row/rows retrieved after executing query2 while(rs.next()) < System.out.print(rs.getString("ID") + "\t"); System.out.print(rs.getString("FirstName") + "\t" + "\t"); System.out.print(rs.getString("LastName")+ "\t" + "\t"); System.out.println(rs.getString("Age") + "\t"); >//Executing second SELECT query rs = stmt.executeQuery(query2); System.out.println("Result of executing query2"); //looping through the number of row/rows retrieved after executing query2 while(rs.next()) < System.out.print(rs.getString("ID") + "\t"); System.out.print(rs.getString("FirstName") + "\t" + "\t"); System.out.print(rs.getString("LastName")+ "\t" + "\t"); System.out.println(rs.getString("Age") + "\t"); >//Executing third SELECT query rs = stmt.executeQuery(query3); System.out.println("Result of executing query3"); while(rs.next()) < System.out.println(rs.getString(1)); >> catch(Exception e) < System.out.println(e); >>//main() method ends >//class definition ends 

Output

Result of executing query1 ID FirstName LastName Age 1 Tom Hanks 61 2 Johnny Depp 54 3 Brad Pitt 53 Result of executing query2 3 Brad Pitt 53 Result of executing query3 Tom

Источник

Processing SQL Statements with JDBC

In general, to process any SQL statement with JDBC, you follow these steps:

This page uses the following method, CoffeesTable.viewTable , from the tutorial sample to demonstrate these steps. This method outputs the contents of the table COFFEES . This method will be discussed in more detail later in this tutorial:

public static void viewTable(Connection con) throws SQLException < String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES"; try (Statement stmt = con.createStatement()) < ResultSet rs = stmt.executeQuery(query); while (rs.next()) < String coffeeName = rs.getString("COF_NAME"); int supplierID = rs.getInt("SUP_ID"); float price = rs.getFloat("PRICE"); int sales = rs.getInt("SALES"); int total = rs.getInt("TOTAL"); System.out.println(coffeeName + ", " + supplierID + ", " + price + ", " + sales + ", " + total); >> catch (SQLException e) < JDBCTutorialUtilities.printSQLException(e); >>

Establishing Connections

First, establish a connection with the data source you want to use. A data source can be a DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver. This connection is represented by a Connection object. See Establishing a Connection for more information.

Creating Statements

A Statement is an interface that represents a SQL statement. You execute Statement objects, and they generate ResultSet objects, which is a table of data representing a database result set. You need a Connection object to create a Statement object.

For example, CoffeesTable.viewTable creates a Statement object with the following code:

There are three different kinds of statements:

• Statement : Used to implement simple SQL statements with no parameters.
• PreparedStatement : (Extends Statement .) Used for precompiling SQL statements that might contain input parameters. See Using Prepared Statements for more information.
• CallableStatement: (Extends PreparedStatement .) Used to execute stored procedures that may contain both input and output parameters. See Stored Procedures for more information.

Executing Queries

To execute a query, call an execute method from Statement such as the following:

• execute : Returns true if the first object that the query returns is a ResultSet object. Use this method if the query could return one or more ResultSet objects. Retrieve the ResultSet objects returned from the query by repeatedly calling Statement.getResultSet .
• executeQuery : Returns one ResultSet object.
• executeUpdate : Returns an integer representing the number of rows affected by the SQL statement. Use this method if you are using INSERT , DELETE , or UPDATE SQL statements.

For example, CoffeesTable.viewTable executed a Statement object with the following code:

ResultSet rs = stmt.executeQuery(query);

Processing ResultSet Objects

You access the data in a ResultSet object through a cursor. Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the ResultSet object. Initially, the cursor is positioned before the first row. You call various methods defined in the ResultSet object to move the cursor.

For example, CoffeesTable.viewTable repeatedly calls the method ResultSet.next to move the cursor forward by one row. Every time it calls next , the method outputs the data in the row where the cursor is currently positioned:

ResultSet rs = stmt.executeQuery(query); while (rs.next()) < String coffeeName = rs.getString("COF_NAME"); int supplierID = rs.getInt("SUP_ID"); float price = rs.getFloat("PRICE"); int sales = rs.getInt("SALES"); int total = rs.getInt("TOTAL"); System.out.println(coffeeName + ", " + supplierID + ", " + price + ", " + sales + ", " + total); >// .

Closing Connections

When you are finished using a Connection , Statement , or ResultSet object, call its close method to immediately release the resources it’s using.

Alternatively, use a try -with-resources statement to automatically close Connection , Statement , and ResultSet objects, regardless of whether an SQLException has been thrown. (JDBC throws an SQLException when it encounters an error during an interaction with a data source. See Handling SQL Exceptions for more information.) An automatic resource statement consists of a try statement and one or more declared resources. For example, the CoffeesTable.viewTable method automatically closes its Statement object, as follows:

public static void viewTable(Connection con) throws SQLException < String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES"; try (Statement stmt = con.createStatement()) < ResultSet rs = stmt.executeQuery(query); while (rs.next()) < String coffeeName = rs.getString("COF_NAME"); int supplierID = rs.getInt("SUP_ID"); float price = rs.getFloat("PRICE"); int sales = rs.getInt("SALES"); int total = rs.getInt("TOTAL"); System.out.println(coffeeName + ", " + supplierID + ", " + price + ", " + sales + ", " + total); >> catch (SQLException e) < JDBCTutorialUtilities.printSQLException(e); >>

The following statement is a try -with-resources statement, which declares one resource, stmt , that will be automatically closed when the try block terminates:

try (Statement stmt = con.createStatement()) < // . >

Источник

Java — how to make MySQL select query with JDBC?

Explosssive

In Java it is possible to make SQL SELECT query with JDBC in following way.

Note: read this article to know how to download and install JDBC driver proper way.

1. SELECT query with JDBC examples

1.1. Simple select example

package com.dirask.examples; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Program < private static final String DB_NAME = "test"; private static final String DB_HOST = "127.0.0.1"; // 'localhost' private static final String DB_USER = "root"; private static final String DB_PASSWORD = "root"; private static final String DB_URL = "jdbc:mysql://" + DB_HOST + "/" + DB_NAME + "?serverTimezone=UTC"; public static void main(String[] args) throws ClassNotFoundException < String sql = "SELECT * FROM `users`"; try ( Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD); Statement statement = connection.createStatement(); ResultSet result = statement.executeQuery(sql); ) < System.out.print("[id]\t[name]\t[role]\n"); while (result.next()) < int id = result.getInt("id"); String name = result.getString("name"); String role = result.getString("role"); System.out.print(id + "\t" + name + "\t" + role + "\n"); >> catch (SQLException e) < // Some logic depending on scenario here . // Maybe: LOGGER.log(e.getMessage()) and "return false" e.printStackTrace(); >> >

[id] [name] [role] 1 John admin 2 Chris moderator 3 Kate user 4 Denis moderator 5 Matt moderator

1.2. SQL injection prevention example

package com.dirask.examples; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class Program < private static final String DB_NAME = "test"; private static final String DB_HOST = "127.0.0.1"; // 'localhost' private static final String DB_USER = "root"; private static final String DB_PASSWORD = "root"; private static final String DB_URL = "jdbc:mysql://" + DB_HOST + "/" + DB_NAME + "?serverTimezone=UTC"; public static void main(String[] args) throws ClassNotFoundException < String sql = "SELECT * FROM `users` WHERE `role` = ?"; try ( Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD); PreparedStatement statement = connection.prepareStatement(sql); ) < // this way to prevent sql injection statement.setString(1, "moderator"); try (ResultSet result = statement.executeQuery()) < System.out.print("[id]\t[name]\t[role]\n"); while (result.next()) < int id = result.getInt("id"); String name = result.getString("name"); String role = result.getString("role"); System.out.print(id + "\t" + name + "\t" + role + "\n"); >> > catch (SQLException e) < // Some logic depending on scenario here . // Maybe: LOGGER.log(e.getMessage()) and "result false" e.printStackTrace(); >> >

[id] [name] [role] 2 Chris moderator 4 Denis moderator

2. Data base preparation

CREATE TABLE `users` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(100) NOT NULL, `role` VARCHAR(15) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;

INSERT INTO `users` (`name`, `role`) VALUES ('John', 'admin'), ('Chris', 'moderator'), ('Kate', 'user'), ('Denis', 'moderator');

See also

Источник

Java JDBC: A SQL SELECT query example

Java JDBC FAQ: Can you share an example of a SQL SELECT query using the standard JDBC syntax?

In my JDBC connection article I showed how to connect your Java applications to standard SQL databases like MySQL, SQL Server, Oracle, SQLite, and others using JDBC. In those examples I showed how to connect to two different databases so you could see how little the code changes when you switch from one database to another.

In this SELECT query tutorial I’ll take JDBC to the next step, showing how to create and execute a SQL SELECT statement in your Java code.

JDBC SELECT query: A sample database

Before looking at the SQL queries, let’s take a quick look at our sample database.

In all of these examples I’ll access a database named «Demo», and in these SELECT query examples I’ll access a database table named «Customers» that’s contained in the Demo database.

Here’s what the Customers table looks like:

Cnum	Lname	Salutation	City	Snum
1001	Simpson	Mr.	Springfield	2001
1002	MacBeal	Ms.	Boston	2004
1003	Flinstone	Mr.	Bedrock	2003
1004	Cramden	Mr.	New York	2001

As you can see, the Customers table contains these four sample records. (I populated that data in my JDBC SQL INSERT tutorial.)

How to perform a JDBC SELECT query against a database

Querying a SQL database with JDBC is typically a three-step process:

1. Create a JDBC ResultSet object.
2. Execute the SQL SELECT query you want to run.
3. Read the results.

The hardest part of the process is defining the query you want to run, and then writing the code to read and manipulate the results of your SELECT query.

Creating a valid SQL SELECT query

To demontrate this I’ll write this simple SQL SELECT query:

SELECT Lname FROM Customers WHERE Snum = 2001;

This statement returns each Lname (last name) record from the Customers database, where Snum (salesperson id-number) equals 2001. In plain English, you might say «give me the last name of every customer where the salesperson id-number is 2001».

Now that we know the information we want to retrieve, how do we put this SQL statement into a Java program? It’s actually very simple. Here’s the JDBC code necessary to create and execute this query:

Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT Lname FROM Customers WHERE Snum = 2001");

In the first step, I create a Java Statement objection from the Connection object. That’s really just an intermediate step that lets us do what we want to do in the next step: Execute our query, and get a ResultSet object. The ResultSet object rs now contains the results from the database query. Now we can work with those results.

Reading the JDBC SELECT query results (i.e., a Java JDBC ResultSet)

After you execute the SQL query, how do you read the results? Well, JDBC makes this pretty easy also. In many cases, you can just use the next() method of the ResultSet object. After the previous two lines, you might add a loop like this to read the results:

This loop reads the last name returned in each record, and prints it to the screen using the normal System.out.println() method. In the case of our sample database, the printed results look like this:

because these are the last names of the two customer records where Snum equals 2001.

Notice that in this example all we’re doing is printing our results. In many JDBC applications, you’ll probably want to do something else with the results, such as displaying them in a table or grid in a GUI applet or application.

Our JDBC SELECT query example program — Query1.java

The full source code for our example JDBC program (Query1.java) is shown in Listing 1.

// Query1.java: Query an mSQL database using JDBC. import java.sql.*; /** * A JDBC SELECT (JDBC query) example program. */ class Query1 < public static void main (String[] args) < try < String url = "jdbc:msql://200.210.220.1:1114/Demo"; Connection conn = DriverManager.getConnection(url,"",""); Statement stmt = conn.createStatement(); ResultSet rs; rs = stmt.executeQuery("SELECT Lname FROM Customers WHERE Snum = 2001"); while ( rs.next() ) < String lastName = rs.getString("Lname"); System.out.println(lastName); >conn.close(); > catch (Exception e) < System.err.println("Got an exception! "); System.err.println(e.getMessage()); >> >

The source code for the Query1.java program shows how to query an SQL database for the information you want, using Java JDBC methods.

Download our example JDBC select program

If you’re interested, you can download the Java source code for our Query1.java program. You can test this JDBC example code on your own system, but note that you’ll need to change the lines where we create our url and conn objects to reflect your own database configuration.

Conclusion

Querying an SQL database with JDBC is a simple three step process, once you know how to do it. Just (1) create a ResultSet object, (2) execute the query, and then (3) read the results.

Related JDBC content

Please note that there is a *lot* of newer Java and JDBC content on my blog, including these JDBC tips:

• How do I connect to a database?
• How do I create and use a PreparedStatement?
• A good example of using try/catch/finally with JDBC
• Using a PreparedStatement with a SELECT statement and LIKE clause
• Sample JDBC PreparedStatement (using SQL UPDATE)
• A sample Java/JDBC program to connect to a database
• The SQLProcessor as a JDBC Facade (a better way to use JDBC)
• A simple Spring JDBC example showing a SELECT and INSERT
• Spring JDBC SELECT statement examples
• Spring JDBC DELETE examples
• How to test your Spring JDBC code

Источник