Java PreparedStatement IN子句替代

最新推荐文章于 2022-08-05 15:17:06 发布

cunchi4221

最新推荐文章于 2022-08-05 15:17:06 发布

阅读量1.8k

点赞数 1

文章标签：数据库 java mysql sql jdbc

原文链接：https://www.journaldev.com/2521/java-preparedstatement-in-clause-alternatives

版权

If you are using JDBC API to run queries on database, you should know that PreparedStatement is the better choice than Statement. However since JDBC API allows only one literal for one “?” parameter, PreparedStatement doesn’t work for IN clause queries.

如果您使用JDBC API在数据库上运行查询，则应该知道PreparedStatement比Statement更好。但是，由于JDBC API只允许使用一个文字作为一个“？” 参数，PreparedStatement不适用于IN子句查询。

PreparedStatement IN子句 (PreparedStatement IN clause)

So if we need to execute a database query with IN clause, we need to look for some alternative approach. The aim of this post is to analyze different approaches and you can choose the one that suits your requirements.

因此，如果需要使用IN子句执行数据库查询，则需要寻找其他替代方法。这篇文章的目的是分析不同的方法，您可以选择适合您需求的方法。

Let’s look at these approaches one by one. But before that let’s create a utility program for database connection reading configurations from property file.

让我们一一看一下这些方法。但是在此之前，让我们创建一个实用程序，用于从属性文件读取数据库配置的数据库连接。

db.properties

#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;
	}
}

Make sure you have JDBC jars in the build path of the project.

确保项目的构建路径中有JDBC jar。

Now let’s look at the different approaches and their analysis.

现在让我们看一下不同的方法及其分析。

执行单一查询 (Execute Single Queries)

This is the simplest approach. We can get the input and execute single PreparedStatement query multiple times. A sample program with this approach will look like below.

这是最简单的方法。我们可以获取输入并多次执行单个PreparedStatement查询。使用这种方法的示例程序如下所示。

JDBCPreparedStatementSingle.java

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();
			}
		}
	}
}

The approach is simple but it’s very slow because if there are 100 parameters then it will make 100 database calls. This will result in 100 ResultSet objects that will overload the system and it will also cause performance hit. So this approach is not recommended.

该方法很简单，但是非常慢，因为如果有100个参数，那么它将进行100次数据库调用。这将导致100个ResultSet对象，这些对象将使系统超载，并且也将导致性能下降。因此，不建议使用此方法。

使用存储过程 (Using Stored Procedure)

We can write a stored procedure and send the input data to the stored procedure. Then we can execute queries one by one in the stored procedure and get the results. This approach gives fastest performance but as we all know that Stored Procedures are database specific. So if our application deals with multiple types of databases such as Oracle, MySQL then it will become hard to maintain. We should use this approach only when we are working on single type of database and there is no plan to change the database server. Since writing stored procedure is out of scope of this tutorial, I will not demonstrate how to use it.

我们可以编写一个存储过程，并将输入数据发送到该存储过程。然后，我们可以在存储过程中一一执行查询并获得结果。这种方法提供了最快的性能，但是众所周知，存储过程是特定于数据库的。因此，如果我们的应用程序处理多种类型的数据库，例如Oracle，MySQL，则将很难维护。仅在处理单一类型的数据库并且没有计划更改数据库服务器时，才应使用此方法。由于编写存储过程超出了本教程的范围，因此我将不演示如何使用它。

动态创建PreparedStatement查询 (Creating PreparedStatement Query dynamically)

This approach involves writing logic to create the PreparedStatement query dynamically based on the size of the elements in IN clause. A simple example showing how to use it will look like below code.

这种方法涉及编写逻辑以根据IN子句中元素的大小动态创建PreparedStatement查询。一个显示如何使用它的简单示例将类似于以下代码。

JDBCPreparedStatementDynamic.java

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();
	}
}

Notice that the query is created dynamically and it will run perfectly. There will be only one database call and the performance will be good. However if the size of user input varies a lot, we won’t get the PreparedStatement benefit of caching and reusing the execution plan. If you are not worried about PreparedStatement caching and there are not many queries with IN clause, then it seems to be the way to go.

请注意，查询是动态创建的，它将完美运行。只有一个数据库调用，并且性能会很好。但是，如果用户输入的大小相差很大，我们将无法获得缓存和重用执行计划的PreparedStatement好处。如果您不担心PreparedStatement缓存并且使用IN子句的查询不多，那么这似乎是可行的方法。

在PreparedStatement查询中使用NULL (Using NULL in PreparedStatement Query)

If you really want to utilize the PreparedStatement caching feature, then another approach is to use NULL in PreparedStatement parameters. Suppose that the maximum allowed parameters in the query is 10, then we can write our logic like below.

如果您真的想利用PreparedStatement缓存功能，那么另一种方法是在PreparedStatement参数中使用NULL。假设查询中允许的最大参数为10，那么我们可以像下面这样编写逻辑。

JDBCPreparedStatementNULL.java

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();
			}
		}
	}
}

Notice that above program is using same PreparedStatement query for executing IN clause statement and will get the benefit of query caching and executing plan. For simplicity, I am just returning if the number of input parameters is greater than the parameters size in the query but we can easily extend it to execute in batches to allow any number of inputs.

请注意，上面的程序使用相同的PreparedStatement查询来执行IN子句语句，并且将从查询缓存和执行计划中受益。为了简单起见，我只是返回输入参数的数量是否大于查询中的参数大小，但是我们可以轻松地扩展它以批量执行以允许任意数量的输入。

Now let’s write a simple test program to check the output. For my test program, I am using Employee table created in JDBC DataSource example.

现在，让我们编写一个简单的测试程序来检查输出。对于我的测试程序，我正在使用在JDBC DataSource示例中创建的Employee表。

Our test program code is;

我们的测试程序代码是；

JDBCPreparedStatementINTest.java

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});
	}

}

When we execute it with some test data in Employee table, we get below output.

当我们用Employee表中的一些测试数据执行它时，我们得到下面的输出。

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

That’s all for the different options we have to use PreparedStatement for IN clause in queries. You can use any one of these based on your project requirements.

这就是我们必须在查询中对IN子句使用PreparedStatement的不同选项的全部。您可以根据项目要求使用其中任何一种。

翻译自: https://www.journaldev.com/2521/java-preparedstatement-in-clause-alternatives

cunchi4221

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Java PreparedStatement IN子句替代

If you are using JDBC API to run queries on database, you should know that PreparedStatement is the better choice than Statement. However since JDBC API allows only one literal for one “?” parameter, ...
复制链接

扫一扫