Java ПОДГОТОВЛЕННОЕ утверждение В альтернативных предложениях

Автор оригинала: Pankaj Kumar.

Если вы используете API JDBC для выполнения запросов к базе данных, вы должны знать, что PreparedStatement-лучший выбор, чем оператор . Однако, поскольку API JDBC допускает только один литерал для одного параметра”?”, PreparedStatement не работает для запросов предложений IN.

ПОДГОТОВЛЕННОЕ заявление В пункте

Поэтому, если нам нужно выполнить запрос к базе данных в предложении, нам нужно найти какой-то альтернативный подход. Цель этого поста-проанализировать различные подходы, и вы можете выбрать тот, который соответствует вашим требованиям.

Выполнение Отдельных Запросов
Использование Хранимой Процедуры
Динамическое создание запроса PreparedStatement
Использование NULL в подготовленном запросе оператора

Давайте рассмотрим эти подходы один за другим. Но перед этим давайте создадим служебную программу для чтения конфигураций соединений с базой данных из файла свойств.

#mysql DB properties
DB_DRIVER_CLASS=com.mysql.jdbc.Driver
DB_URL=jdbc:mysql://localhost:3306/UserDB
DB_USERNAME=pankaj
DB_PASSWORD=pankaj123

#Oracle DB Properties
#DB_DRIVER_CLASS=oracle.jdbc.driver.OracleDriver
#DB_URL=jdbc:oracle:thin:@localhost:1521:orcl
#DB_USERNAME=hr
#DB_PASSWORD=oracle

package com.journaldev.jdbc.preparedstatement.in;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBConnection {

	public static Connection getConnection() {
		Properties props = new Properties();
		FileInputStream fis = null;
		Connection con = null;
		try {
			fis = new FileInputStream("db.properties");
			props.load(fis);

			// load the Driver Class
			Class.forName(props.getProperty("DB_DRIVER_CLASS"));

			// create the connection now
			con = DriverManager.getConnection(props.getProperty("DB_URL"),
					props.getProperty("DB_USERNAME"),
					props.getProperty("DB_PASSWORD"));
		} catch (SQLException e) {
			System.out.println("Check database is UP and configs are correct");
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("Looks like db.property file has some issues");
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			System.out.println("Please include JDBC API jar in classpath");
			e.printStackTrace();
		}finally{
			try {
				fis.close();
			} catch (IOException e) {
				System.out.println("File Close issue, lets ignore it.");
			}
		}
		return con;
	}
}

Убедитесь, что у вас есть JDBC-банки в пути сборки проекта.

Теперь давайте рассмотрим различные подходы и их анализ.

Выполнение Отдельных Запросов

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

package com.journaldev.jdbc.preparedstatement.in;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCPreparedStatementSingle {

	private static final String QUERY = "select empid, name from Employee where empid = ?";
	
	public static void printData(int[] ids){
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(QUERY);
			
			for(int empid : ids){
				ps.setInt(1, empid);
				rs = ps.executeQuery();
				
				while(rs.next()){
					System.out.println("Employee ID="+rs.getInt("empid")+", Name="+rs.getString("name"));
				}
				
				//close the resultset here
				try{
					rs.close();
				} catch(SQLException e){}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

Подход прост, но он очень медленный, потому что при наличии 100 параметров он выполнит 100 вызовов базы данных. Это приведет к появлению 100 объектов набора результатов, которые перегрузят систему, а также снизят производительность. Поэтому такой подход не рекомендуется.

Использование Хранимой Процедуры

Мы можем написать хранимую процедуру и отправить входные данные в хранимую процедуру. Затем мы можем выполнять запросы один за другим в хранимой процедуре и получать результаты. Этот подход обеспечивает максимальную производительность, но, как мы все знаем, хранимые процедуры зависят от конкретной базы данных. Поэтому, если наше приложение имеет дело с несколькими типами баз данных, такими как Oracle, MySQL, то его будет сложно поддерживать. Мы должны использовать этот подход только в том случае, если мы работаем с одним типом базы данных и не планируем менять сервер базы данных. Поскольку написание хранимой процедуры выходит за рамки этого руководства, я не буду демонстрировать, как ее использовать.

Динамическое создание запроса PreparedStatement

Этот подход предполагает написание логики для динамического создания запроса PreparedStatement на основе размера элементов в предложении IN. Простой пример, показывающий, как его использовать, будет выглядеть следующим образом.

package com.journaldev.jdbc.preparedstatement.in;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCPreparedStatementDynamic {

	public static void printData(int[] ids){
		
		String query = createQuery(ids.length);
		
		System.out.println("Query="+query);
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(query);
			
			for(int i = 1; i <=ids.length; i++){
				ps.setInt(i, ids[i-1]);
			}
			rs = ps.executeQuery();
				
			while(rs.next()){
				System.out.println("Employee ID="+rs.getInt("empid")+", Name="+rs.getString("name"));
			}
				
			//close the resultset here
			try{
				rs.close();
			} catch(SQLException e){}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private static String createQuery(int length) {
		String query = "select empid, name from Employee where empid in (";
		StringBuilder queryBuilder = new StringBuilder(query);
		for( int i = 0; i< length; i++){
			queryBuilder.append(" ?");
			if(i != length -1) queryBuilder.append(",");
		}
		queryBuilder.append(")");
		return queryBuilder.toString();
	}
}

Обратите внимание, что запрос создается динамически и будет выполняться идеально. Будет только один вызов базы данных, и производительность будет хорошей. Однако, если размер пользовательского ввода сильно варьируется, мы не получим преимущества PreparedStatement от кэширования и повторного использования плана выполнения. Если вы не беспокоитесь о кэшировании PreparedStatement и в предложении не так много запросов, то, похоже, это правильный путь.

Использование NULL в подготовленном запросе оператора

Если вы действительно хотите использовать функцию кэширования подготовленных операторов, то другой подход заключается в использовании NULL в параметрах PreparedStatement. Предположим, что максимально допустимые параметры в запросе равны 10, тогда мы можем написать нашу логику, как показано ниже.

package com.journaldev.jdbc.preparedstatement.in;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCPreparedStatementNULL {

	private static final String QUERY = "select empid, name from Employee where empid in ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private static final int PARAM_SIZE = 10;
	public static void printData(int[] ids){
		
		if(ids.length > PARAM_SIZE){
			System.out.println("Maximum input size supported is "+PARAM_SIZE);
			//in real life, we can write logic to execute in batches, for simplicity I am returning
			return;
		}
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(QUERY);
			
			int i = 1;
			for(; i <=ids.length; i++){
				ps.setInt(i, ids[i-1]);
			}
			
			//set null for remaining ones
			for(; i<=PARAM_SIZE;i++){
				ps.setNull(i, java.sql.Types.INTEGER);
			}
			
			rs = ps.executeQuery();
				
			while(rs.next()){
				System.out.println("Employee ID="+rs.getInt("empid")+", Name="+rs.getString("name"));
			}
				
			//close the resultset here
			try{
				rs.close();
			} catch(SQLException e){}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

Обратите внимание, что вышеуказанная программа использует один и тот же запрос PreparedStatement для выполнения В операторе предложения и получит преимущества кэширования запросов и плана выполнения. Для простоты я просто возвращаю, если количество входных параметров больше, чем размер параметров в запросе, но мы можем легко расширить его для пакетного выполнения, чтобы разрешить любое количество входных данных.

Теперь давайте напишем простую тестовую программу для проверки результатов. Для моей тестовой программы я использую таблицу сотрудников, созданную в примере источника данных JDBC.

Наш код тестовой программы является;

package com.journaldev.jdbc.preparedstatement.in;

public class JDBCPreparedStatementINTest {

	private static int[] ids = {1,2,3,4,5,6,7,8,9,10};
	
	public static void main(String[] args) {
		
		JDBCPreparedStatementSingle.printData(ids);
		
		System.out.println("*********");
		
		JDBCPreparedStatementDynamic.printData(ids);
		
		System.out.println("*********");
		
		JDBCPreparedStatementNULL.printData(new int[]{1,2,3,4,5});
	}

}

Когда мы выполняем его с некоторыми тестовыми данными в таблице сотрудников, мы получаем результат ниже.

Employee ID=1, Name=Pankaj
Employee ID=2, Name=David
Employee ID=3, Name=Ram
Employee ID=4, Name=Leela
Employee ID=5, Name=Lisa
Employee ID=6, Name=Saurabh
Employee ID=7, Name=Mani
Employee ID=8, Name=Avinash
Employee ID=9, Name=Vijay
*********
Query=select empid, name from Employee where empid in ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Employee ID=1, Name=Pankaj
Employee ID=2, Name=David
Employee ID=3, Name=Ram
Employee ID=4, Name=Leela
Employee ID=5, Name=Lisa
Employee ID=6, Name=Saurabh
Employee ID=7, Name=Mani
Employee ID=8, Name=Avinash
Employee ID=9, Name=Vijay
*********
Employee ID=1, Name=Pankaj
Employee ID=2, Name=David
Employee ID=3, Name=Ram
Employee ID=4, Name=Leela
Employee ID=5, Name=Lisa

Это все для различных опций, для которых мы должны использовать PreparedStatement в предложении IN в запросах. Вы можете использовать любой из них в зависимости от требований вашего проекта.