public List<License> findAll() throws SQLException { try (Connection conn = ds.getConnection()) { try (PreparedStatement statement = conn.prepareStatement("SELECT * FROM License")) { try (ResultSet resultSet = statement.executeQuery()) { List<License> licenses = new ArrayList<>(); while (resultSet.next()) { int productId = resultSet.getInt("productId"); Product productById = productRepository.getProductById(productId); int releaseId = resultSet.getInt("releaseId"); Release release = null; if (releaseId != 0) release = releaseRepository.getReleaseById(releaseId); int customerId = resultSet.getInt("customerId"); Customer customerById = customerRepository.getCustomerById(customerId); Integer licenseTypeId = getInteger(resultSet, "licenseTypeId"); LicenseType licenseType = licenseTypeId != null ? getLicenseTypeById(conn, licenseTypeId) : null; License license = getLicense(resultSet, productById, customerById, release, licenseType); licenses.add(license); } return licenses; } } } }
public List<License> findExpiringLicenses() throws SQLException { try (Connection connection = ds.getConnection()) { try (PreparedStatement stmnt = connection.prepareStatement( "Select *, DATEDIFF('DAY', CURRENT_DATE, validTill),CURRENT_DATE from License where DATEDIFF('DAY', CURRENT_DATE, validTill) < 31 AND DATEDIFF('DAY', CURRENT_DATE, validTill) > 0")) { try (ResultSet resultSet = stmnt.executeQuery()) { List<License> expiringLicenses = new ArrayList<>(); while (resultSet.next()) { int productId = resultSet.getInt("productId"); Product productById = productRepository.getProductById(productId); int releaseId = resultSet.getInt("releaseId"); Release release = null; if (releaseId != 0) release = releaseRepository.getReleaseById(releaseId); int customerId = resultSet.getInt("customerId"); Customer customerById = customerRepository.getCustomerById(customerId); Integer licenseTypeId = getInteger(resultSet, "licenseTypeId"); LicenseType licenseType = licenseTypeId != null ? getLicenseTypeById(connection, licenseTypeId) : null; License license = getLicense(resultSet, productById, customerById, release, licenseType); expiringLicenses.add(license); } return expiringLicenses; } } } }
public License findById(int id) throws SQLException { try (Connection connection = ds.getConnection()) { try (PreparedStatement statement = connection.prepareStatement("SELECT * FROM License WHERE id = ?;")) { statement.setInt(1, id); try (ResultSet resultSet = statement.executeQuery()) { if (!resultSet.next()) { throw new SQLException("Ei leitud ühtegi litsentsi, kus rida on id-ga " + id); } int releaseId = resultSet.getInt("releaseId"); Release release = null; if (releaseId != 0) release = releaseRepository.getReleaseById(releaseId); Integer licenseTypeId = getInteger(resultSet, "licenseTypeId"); LicenseType licenseType = licenseTypeId != null ? getLicenseTypeById(connection, licenseTypeId) : null; return getLicense( resultSet, productRepository.getProductById(resultSet.getInt("productId")), customerRepository.getCustomerById(resultSet.getInt("customerId")), release, licenseType); } } } }
public List<License> findByKeyword(String kword, StateHelper sh) throws SQLException { try (Connection conn = ds.getConnection()) { try (PreparedStatement statement = conn.prepareStatement( "SELECT * FROM License LEFT JOIN Customer ON License.customerId = Customer.id LEFT JOIN Release ON License.releaseId = Release.id LEFT JOIN Product ON License.productId = Product.id LEFT JOIN LicenseType ON License.licenseTypeId = LicenseType.id WHERE (License.contractNumber LIKE (LOWER(CONCAT('%',?,'%'))) OR ( License.productId = Product.id AND LOWER(Product.name) LIKE LOWER(CONCAT('%',?,'%'))) OR ( License.releaseId = Release.id AND Release.version LIKE (CONCAT('%',?,'%')) ) OR ( License.customerId = Customer.id AND LOWER(Customer.organizationName) LIKE LOWER(CONCAT('%',?,'%')) ) OR License.validFrom LIKE (CONCAT('%',?,'%')) OR License.validTill LIKE (CONCAT('%',?,'%')) OR ( License.licenseTypeId = LicenseType.id AND LicenseType.name LIKE (CONCAT('%',?,'%')) )) AND ((License.state = 1 AND ?=true) OR (License.state = 2 AND ?=true) OR (License.state = 3 AND ?=true) OR (License.state = 4 AND ?=true) OR (License.state = 5 AND ?=true) OR (License.state = 6 AND ?=true));")) { statement.setString(1, kword); statement.setString(2, kword); statement.setString(3, kword); statement.setString(4, kword); statement.setString(5, kword); statement.setString(6, kword); statement.setString(7, kword); statement.setBoolean(8, sh.getRejected()); statement.setBoolean(9, sh.getNegotiated()); statement.setBoolean(10, sh.getWaiting_for_signature()); statement.setBoolean(11, sh.getActive()); statement.setBoolean(12, sh.getExpiration_nearing()); statement.setBoolean(13, sh.getTerminated()); System.out.println(statement); try (ResultSet resultSet = statement.executeQuery()) { List<License> licenses = new ArrayList<>(); while (resultSet.next()) { int productId = resultSet.getInt("productId"); Product productById = productRepository.getProductById(productId); int releaseId = resultSet.getInt("releaseId"); Release release = null; if (releaseId != 0) release = releaseRepository.getReleaseById(releaseId); int customerId = resultSet.getInt("customerId"); Customer customerById = customerRepository.getCustomerById(customerId); Integer licenseTypeId = getInteger(resultSet, "licenseTypeId"); LicenseType licenseType = licenseTypeId != null ? getLicenseTypeById(conn, licenseTypeId) : null; License license = getLicense(resultSet, productById, customerById, release, licenseType); licenses.add(license); } return licenses; } } } }