Java jdbc connection query

JDBC: Query the Database

Querying a database means searching through its data. You do so be sending SQL statements to the database. To do so, you first need an open database connection. Once you have an open connection, you need to create a Statement object, like this:

Statement statement = connection.createStatement();

Once you have created the Statement you can use it to execute SQL queries, like this:

String sql = "select * from people"; ResultSet result = statement.executeQuery(sql);

When you execute an SQL query you get back a ResultSet . The ResultSet contains the result of your SQL query. The result is returned in rows with columns of data. You iterate the rows of the ResultSet like this:

The ResultSet.next() method moves to the next row in the ResultSet , if there are anymore rows. If there are anymore rows, it returns true. If there were no more rows, it will return false.

You need to call next() at least one time before you can read any data. Before the first next() call the ResultSet is positioned before the first row.

You can get column data for the current row by calling some of the getXXX() methods, where XXX is a primitive data type. For instance:

result.getString ("columnName"); result.getLong ("columnName"); result.getInt ("columnName"); result.getDouble ("columnName"); result.getBigDecimal("columnName"); etc.

The column name to get the value of is passed as parameter to any of these getXXX() method calls.

You can also pass an index of the column instead, like this:

result.getString (1); result.getLong (2); result.getInt (3); result.getDouble (4); result.getBigDecimal(5); etc.

For that to work you need to know what index a given column has in the ResultSet . You can get the index of a given column by calling the ResultSet.findColumn() method, like this:

int columnIndex = result.findColumn("columnName");

If iterating large amounts of rows, referencing the columns by their index might be faster than by their name.

When you are done iterating the ResultSet you need to close both the ResultSet and the Statement object that created it (if you are done with it, that is). You do so by calling their close() methods, like this:

result.close(); statement.close();

Of course you should call these mehtods inside a finally block to make sure that they are called even if an exception occurs during ResultSet iteration.

Full Example

Here is a full query code example:

Statement statement = connection.createStatement(); String sql = "select * from people"; ResultSet result = statement.executeQuery(sql); while(result.next()) < String name = result.getString("name"); long age = result.getLong("age"); System.out.println(name); System.out.println(age); >result.close(); statement.close();

And here the example is again, with try-finally blocks added. Notice, I have left out the catch blocks to make the example shorter.

Statement statement = null; try < statement = connection.createStatement(); ResultSet result = null; try< String sql = "select * from people"; ResultSet result = statement.executeQuery(sql); while(result.next()) < String name = result.getString("name"); long age = result.getLong("age"); System.out.println(name); System.out.println(age); >> finally < if(result != null) result.close(); >> finally

Источник

Шаг 3. Подтверждение концепции: подключение к SQL с помощью Java

Этот пример следует рассматривать только как подтверждение концепции. Пример кода упрощен для ясности и для него не гарантируется соблюдение рекомендаций корпорации Майкрософт.

Шаг 1. Подключение

Используйте класс подключения для подключения к базе данных SQL.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class SQLDatabaseConnection < // Connect to your database. // Replace server name, username, and password with your credentials public static void main(String[] args) < String connectionUrl = "jdbc:sqlserver://yourserver.database.windows.net:1433;" + "database=AdventureWorks;" + "user=yourusername@yourserver;" + "password=yourpassword;" + "encrypt=true;" + "trustServerCertificate=false;" + "loginTimeout=30;"; try (Connection connection = DriverManager.getConnection(connectionUrl);) < // Code here. >// Handle any errors that may have occurred. catch (SQLException e) < e.printStackTrace(); >> > 

Шаг 2. Выполнение запроса

В этом примере следует подключиться к базе данных SQL Azure, выполнить инструкцию SELECT и вернуть выбранные строки.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class SQLDatabaseConnection < // Connect to your database. // Replace server name, username, and password with your credentials public static void main(String[] args) < String connectionUrl = "jdbc:sqlserver://yourserver.database.windows.net:1433;" + "database=AdventureWorks;" + "user=yourusername@yourserver;" + "password=yourpassword;" + "encrypt=true;" + "trustServerCertificate=false;" + "loginTimeout=30;"; ResultSet resultSet = null; try (Connection connection = DriverManager.getConnection(connectionUrl); Statement statement = connection.createStatement();) < // Create and execute a SELECT SQL statement. String selectSql = "SELECT TOP 10 Title, FirstName, LastName from SalesLT.Customer"; resultSet = statement.executeQuery(selectSql); // Print results from select statement while (resultSet.next()) < System.out.println(resultSet.getString(2) + " " + resultSet.getString(3)); >> catch (SQLException e) < e.printStackTrace(); >> > 

Шаг 3. Вставка строки

В этом примере следует выполнить инструкцию INSERT, передать параметры и извлечь автоматически созданное значение первичного ключа.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; public class SQLDatabaseConnection < // Connect to your database. // Replace server name, username, and password with your credentials public static void main(String[] args) < String connectionUrl = "jdbc:sqlserver://yourserver.database.windows.net:1433;" + "database=AdventureWorks;" + "user=yourusername@yourserver;" + "password=yourpassword;" + "encrypt=true;" + "trustServerCertificate=false;" + "loginTimeout=30;"; String insertSql = "INSERT INTO SalesLT.Product (Name, ProductNumber, Color, StandardCost, ListPrice, SellStartDate) VALUES " + "('NewBike', 'BikeNew', 'Blue', 50, 120, '2016-01-01');"; ResultSet resultSet = null; try (Connection connection = DriverManager.getConnection(connectionUrl); PreparedStatement prepsInsertProduct = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);) < prepsInsertProduct.execute(); // Retrieve the generated key from the insert. resultSet = prepsInsertProduct.getGeneratedKeys(); // Print the ID of the inserted row. while (resultSet.next()) < System.out.println("Generated: " + resultSet.getString(1)); >> // Handle any errors that may have occurred. catch (Exception e) < e.printStackTrace(); >> > 

Источник

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

Источник