Multiple OR and AND SELECT query in JDBC with example Java program

Here's an example of a SELECT query with multiple OR and AND combinations:

Sample select query to get the all the matching data of first name and last name combination :

SELECT *

FROM Employee

WHERE 

          (First_Name = ‘value1’ AND Last_Name = ‘value2’)

    OR (First_Name = ‘value3’ AND Last_Name = ‘value4’)

    OR (First_Name = ‘value5’ AND Last_Name = ‘value6’);

You can modify the column names and values to fit your specific use case. Additionally, you can include more OR and AND combinations as needed.

To implement the same query using Java JDBC, you'll need to establish a connection to your database and execute the query using a PreparedStatement object. Here's an example:

import java.sql.*;

import java.util.Map;

public class JDBCExample {

    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/database_name";

        String username = "your_username";

        String password = "your_password";

        try (Connection conn = DriverManager.getConnection(url, username, password)) {

            String tableName = “Employee”;

            // Your method to get the dynamic column-value map, First Name as Key and Last Name as Value

            Map<String, String> columnValueMap = getDynamicColumnValueMap(); 

            StringBuilder sqlBuilder = new StringBuilder("SELECT Emp_Id, First_Name, Last_Name FROM ");

            sqlBuilder.append(tableName);

            if (!columnValueMap.isEmpty()) {

                sqlBuilder.append(" WHERE ");

                int paramCount = 1;

                for (Map.Entry<String, String> entry : columnValueMap.entrySet()) {

                    if (paramCount > 1) {

                        sqlBuilder.append(" OR ");

                    }

                    sqlBuilder.append(" First_Name = ?");

                    sqlBuilder.append(“ Last_Name = ?");

                    paramCount++;

                }

            }

            String sql = sqlBuilder.toString();

            System.out.println(“sql: ” + sql);

            PreparedStatement stmt = conn.prepareStatement(sql);

            int paramIndex = 1;

            for (Map.Entry<String, String> entry : columnValueMap.entrySet()) {    

                stmt.setObject(++paramIndex, entry.getKey());

                stmt.setObject(++paramIndex, entry.getValue());

            }

            ResultSet rs = stmt.executeQuery();

            // Process the ResultSet as needed

            while (rs.next()) {

                // Retrieve column values using rs.getXXX() methods and store into custom employee object

                // Replace XXX with the appropriate data type of the column

                // Example: String value = rs.getInt(“Emp_Id”);

                // Example: String value = rs.getString(“First_Name”);

            }

            rs.close();

            stmt.close();

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

    private static Map<String, Object> getDynamicColumnValueMap() {

        // Your implementation to populate and return the dynamic column-value map

        // This could be based on user input, configuration, or any other source

        // Return a map where the keys are column names and values are the corresponding values

        // Example: Map<String, String> columnValueMap = new HashMap<>();

        //          columnValueMap.put(“value1”, “value2”);

        //          columnValueMap.put(“value3”, “value4”);

        //          return columnValueMap;

    }

}

This example assumes you are using the MySQL database, but you can modify the URL and driver class accordingly for your specific database management system. Also, remember to handle any potential SQLExceptions that may occur during the execution of the query.

Another example to implement with IN keyword

If you want to use the IN keyword to match multiple values for a single column, you can modify the Java JDBC code accordingly. Here's an example:

import java.sql.*;

import java.util.List;

public class JDBCExample {

    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/database_name";

        String username = "your_username";

        String password = "your_password";

        try (Connection conn = DriverManager.getConnection(url, username, password)) {

            String tableName = "table_name";

            String columnName = "column_name";

            List<Object> values = getDynamicValues(); // Your method to get the dynamic values

            StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM ");

            sqlBuilder.append(tableName);

            if (!values.isEmpty()) {

                sqlBuilder.append(" WHERE ").append(columnName).append(" IN (");

                int paramCount = 1;

                for (int i = 0; i < values.size(); i++) {

                    if (paramCount > 1) {

                        sqlBuilder.append(", ");

                    }

                    sqlBuilder.append("?");

                    paramCount++;

                }

                sqlBuilder.append(")");

            }

            String sql = sqlBuilder.toString();

            PreparedStatement stmt = conn.prepareStatement(sql);

            for (int i = 0; i < values.size(); i++) {

                stmt.setObject(i + 1, values.get(i));

            }

            ResultSet rs = stmt.executeQuery();

            // Process the ResultSet as needed

            while (rs.next()) {

                // Retrieve column values using rs.getXXX() methods

                // Replace XXX with the appropriate data type of the column

                // Example: String value = rs.getString("column_name");

            }

            rs.close();

            stmt.close();

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

    private static List<Object> getDynamicValues() {

        // Your implementation to populate and return the dynamic values

        // This could be based on user input, configuration, or any other source

        // Return a list of values to be used in the IN clause

        // Example: List<Object> values = List.of(value1, value2, value3);

        //          return values;

    }

}

In this example, the getDynamicValues() method represents your implementation to populate the dynamic list of values to be used in the IN clause. The list can be retrieved based on your requirements (e.g., user input, configuration, etc.).

The code then builds the SQL query dynamically using a StringBuilder, appending the IN clause with the appropriate number of placeholders (?) based on the size of the values list.

The values are then set using the setObject() method on the PreparedStatement object.

Remember to adjust the tableName and columnName variables with your specific table and column names.

Again, ensure proper input validation and handling for the dynamic values to maintain the integrity and safety of the query execution.

Tags: