Badsqlgrammarexception beim Testen der Serviceschicht, die JDBC verwendete ⇐ Java

[Anonymous]

Ich schreibe einen Testcode, der mir einen Fehler von Badsqlgrammarexception gibt. Ich verwende eine JDBC -Vorlage. Ergebnisset. < /P>

Code: Select all

@Repository("jdbc")
public class CustomerJDBCDataAccessService implements CustomerDAO{
private final JdbcTemplate jdbcTemplate;
private final CustomerRowMapper customerRowMapper;

public CustomerJDBCDataAccessService(JdbcTemplate jdbcTemplate, CustomerRowMapper customerRowMapper) {
this.jdbcTemplate = jdbcTemplate;
this.customerRowMapper = customerRowMapper;
}

@Override
public List selectAllCustomers() {
var sql = """
SELECT (id, name, email,age) FROM customer
""";
return jdbcTemplate.query(sql , customerRowMapper);

}

//    @Override
//    public List selectAllCustomers() {
//        var sql = """
//                SELECT (id, name, email,age) FROM customer
//                """;
//
//        return jdbcTemplate.query(sql,((rs, rowNum) ->
//                new Customer(
//                        rs.getInt("id"),
//                        rs.getString("name"),
//                        rs.getString("email"),
//                        rs.getInt("age")
//                )));
//    }

@Override
public Optional selectCustomerByID(Integer customerID) {
var sql  = """
SELECT (id, name, email, age) FROM customer
where id = ?;
""";

return jdbcTemplate.query(sql,customerRowMapper,customerID)
.stream()
.findFirst();

}

@Override
public void insertCustomer(Customer customer) {
var sql = """
INSERT INTO customer (name, email, age)
VALUES(?,?,?)
""";
int result = jdbcTemplate.update(sql,customer.getName(),customer.getEmail(),customer.getAge());
System.out.println("jdbcTemplate.update = " + result);
}

@Override
public boolean existPersonWithEmail(String email) {
var sql = """
SELECT COUNT(id)
FROM customer
WHERE email = ?
""";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class,email);
return count != null && count>0;

}

@Override
public void deleteCustomerByID(Integer customerID) {
var sql = """
DELETE
FROM customer
WHERE id = ?
""";
Integer count = jdbcTemplate.update(sql, customerID);

}

@Override
public boolean existsCustomerWithID(Integer customerID) {
var sql = """
SELECT COUNT(id)
FROM customer
WHERE id = ?
""";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class, customerID);
return count !=null &&  count>0;
}

@Override
public void updateCustomer(Customer customer) {
if(customer.getName() != null){
String sql = "UPDATE customer SET name = ? WHERE id = ?";
int result = jdbcTemplate.update(sql, customer.getName(), customer.getId());
}
if(customer.getEmail() != null){
String sql = "UPDATE customer SET email = ? WHERE id = ?";
int result = jdbcTemplate.update(sql, customer.getEmail(), customer.getId());
}
if(customer.getAge() != null){
String sql = "UPDATE customer SET age = ? WHERE id = ?";
int result = jdbcTemplate.update(sql, customer.getAge(), customer.getId());
}
}
}

class CustomerJDBCDataAccessServiceTest extends AbstractTestContainersUnitTest {

private CustomerJDBCDataAccessService underTest;
private final CustomerRowMapper customerRowMapper = new CustomerRowMapper();

@BeforeEach
void setUp() {
underTest = new CustomerJDBCDataAccessService(
getJdbcTemplate(),
customerRowMapper
);
}

@Test
void selectAllCustomers() {
//GIVEN
Customer customer = new Customer(
faker.name().fullName(),
faker.internet().emailAddress()+"_"+ UUID.randomUUID()  ,
20
);
underTest.insertCustomer(customer);
//WHEN
List customers = underTest.selectAllCustomers();
//THEN
assertThat(customers).isNotEmpty();
}
}