[FIXED] OR Condtion with Hibernate JPA

Issue

I am trying to migrate our JDBC code to Hibernate/JPA. So for now I was able to learn findAll(), findById(), delete() and save() and do them pretty well. But then I stumbled up a SQL query like this:

StringBuilder sqlQuery = new StringBuilder();

sqlQuery.append("SELECT *");
sqlQuery.append(" FROM " + DeviceAccessTable);
sqlQuery.append(" WHERE (DEVICENAME = ? OR DEVICENAME = 'ALL')");
sqlQuery.append(" AND (SERVICE = ? OR SERVICE = 'ALL')");
sqlQuery.append(" AND (VENDOR = ? OR VENDOR = 'ALL')");
sqlQuery.append(" AND CREDENTIAL_USE =? ");
sqlQuery.append(" AND PASSWORDTYPE =?;");

preparedStatement = connection.prepareStatement(sqlQuery.toString());

preparedStatement.setString(1, deviceDetails.getName());
preparedStatement.setString(2, deviceDetails.getService());
preparedStatement.setString(3, deviceDetails.getVendor().toUpperCase());
preparedStatement.setString(4, use);
preparedStatement.setString(5, passwordType);

resultSet = preparedStatement.executeQuery();

I wrote it something like this:

deviceAccessCredKey.setCredentialUse("ALL");
deviceAccessCredKey.setPasswordType("LOCAL");
            
if (deviceConnectionDetails.getDeviceName() != null) {
    deviceAccessCredKey.setDeviceName(deviceConnectionDetails.getDeviceName());
} else {
    deviceAccessCredKey.setDeviceName("ALL");
}

if (deviceConnectionDetails.getService() != null) {
    deviceAccessCredKey.setService(deviceConnectionDetails.getService());
} else {
    deviceAccessCredKey.setService("ALL");
}

if (deviceConnectionDetails.getVendor() != null) {
    deviceAccessCredKey.setVendor(deviceConnectionDetails.getVendor());
} else {
    deviceAccessCredKey.setVendor("ALL");
}

Optional<DeviceAccessCreditals> optional = deviceAccessCredRepository.findById(deviceAccessCredKey);

My JPA classes are something like:

@Entity
public class DeviceAccessCreditals implements Serializable {

    /**
     *
     */
    private static final long serialVersionUID = 4561830468065539524L;

    @EmbeddedId
    DeviceAccessCredKey deviceAccessCredKey;

    @Column(name = "enablepassword", nullable = false)
    private String enablePassword;

    @Column(name = "lastupdate", nullable = false)
    private String lastUpdated;

    @Column(name = "updateuser", nullable = false)
    private String updateUser;

    @Column(name = "userid", nullable = false)
    private String userID;
    
.
.
.
}

and:

@Embeddable
public class DeviceAccessCredKey implements Serializable {

    /**
     *
     */
    private static final long serialVersionUID = -2858692808393518724L;

    @Column(name = "credential_use", nullable = false)
    private String credentialUse;

    @Column(name = "devicename", nullable = false)
    private String deviceName;

    @Column(name = "password", nullable = false)
    private String password;

    @Column(name = "passwordtype", nullable = false)
    private String passwordType;

    @Column(name = "service", nullable = false)
    private String service;

    @Column(name = "vendor", nullable = false)
    private String vendor;

    .
    .
    .
    .
}

My Repository class is like:

public interface DeviceAccessCredentialsRepository extends PagingAndSortingRepository<DeviceAccessCreditals, DeviceAccessCredKey> {

}

The above code returns nothing from the table. Is my approach okay? Not sure how to do and OR statement in this situation, any ideas would be helpful. Thanks!

Solution

Would be good to post your DeviceAccessCredRepository.java as well. Have you tried if this works? (Query Methods)

public interface DeviceAccessCredRepository extends JpaRepository<DeviceAccessCreditals, DeviceAccessCredKey>{
  
  //Query Method
  DeviceAccessCreditals findByDeviceAccessCredKey_DeviceNameIn(List<String> deviceNames);

}

You can also implement native or JPQL queries by defining them in your Repository like:

//JPQL (Foo here is your model and not the table)
@Query("SELECT foo FROM Foo WHERE bar = ?1")
List<Foo>findByBar(String bar);

//Native (Foo here is the db table)
@Query(
  value = "SELECT foo FROM Foo WHERE bar = ?1", 
  nativeQuery = true)
  List<Foo>findByBar(String bar);

Answered By - aglamp
Answer Checked By - Mildred Charles (JavaFixing Admin)