Using SQL in java

Java Database Connectivity (JDBC)

API (Application Programming Interface) in Java that defines how a client may access a database. It provides methods for querying and updating data in a relational database using SQL.

SQL

How sql is used withint java will be shown with a sample code that was written to for a personal project, specifically the ScheduleCongroller.java file.

Initially, to use jdbc, its template needes to be loaded.

public class ScheduleController {
    private final JdbcTemplate jdbcTemplate;

    public ScheduleController(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    ... (other codes)
    
}

createSchedule method

• The KeyHolder class in Spring's JDBC framework is used to retrieve auto-generated keys, typically primary keys, after an INSERT operation is performed in a database.

• PreparedStatement is a Java class provided by JDBC that represents a precompiled SQL statement. It allows you to safely execute SQL queries with parameters (i.e., placeholders ?) without having to concatenate SQL strings, which helps prevent SQL injection attacks.

• con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS):

  • con: Represents the Connection object to the database. This is usually obtained from a data source or a connection pool.
  • sql: The SQL string that you want to execute. In this case, it's an INSERT statement.
  • Statement.RETURN_GENERATED_KEYS: This flag indicates that the database should return the generated keys (e.g., primary key values) after executing the INSERT statement.

public ScheduleResponseDto createSchedule(@RequestBody ScheduleRequestDto requestDto) {
    // Convert request DTO to entity
    Schedule schedule = new Schedule(requestDto);

    // SQL INSERT query to save a new schedule
    String sql = "INSERT INTO schedule (title, in_charge, password, notes) VALUES (?, ?, ?, ?)";
    
    // Use `JdbcTemplate.update` to execute the insert
    KeyHolder keyHolder = new GeneratedKeyHolder(); // To capture the generated primary key
    jdbcTemplate.update(con -> {
        PreparedStatement preparedStatement = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        // Set the parameters in the SQL query
        preparedStatement.setString(1, schedule.getTitle());
        preparedStatement.setString(2, schedule.getInCharge());
        preparedStatement.setString(3, schedule.getPassword());
        preparedStatement.setString(4, schedule.getNotes());
        return preparedStatement;
    }, keyHolder);

    // Retrieve the generated key (ID) and set it in the entity
    Long id = keyHolder.getKey().longValue();
    schedule.setId(id);

    // Convert the entity back to a response DTO
    return new ScheduleResponseDto(schedule);
}

getSchedules

This method is supposed to get items in that match certain condition(s): timeEdited, inCharge or both.

• StringBuilder dynamically constructs an SQL query based on the optional filters (timeEdited, inCharge).
• jdbcTemplate.query executes the constructed SQL query and maps the result set to a list of ScheduleResponseDto objects.
• The jdbcTemplate.query() method
  • SQL query: A string representing the SQL query to execute.
  • RowMapper: A callback interface that maps each row of the result set to a corresponding Java object.
  • Optional query parameters: If the SQL query has placeholders (?), you provide the values to be substituted.
• The mapRow function is a method defined in the RowMapper interface in Spring. It is responsible for mapping each row of the result set from a SQL query to a Java object. When using JdbcTemplate to execute a query, the mapRow method is called once for each row in the result set, allowing you to transform the data in each row into an instance of a specific class.
  • ResultSet rs: This parameter represents the current row in the result set obtained from the SQL query. It provides methods to access the data in each column of the row.
  • int rowNum: This parameter is the row number of the current row being processed, starting from 0. It can be used if you need to know the row's position, though it's often not required.

public List<ScheduleResponseDto> getSchedules(@RequestParam(value = "timeEdited", required = false) String timeEdited,
                                                @RequestParam(value = "inCharge", required = false) String inCharge) {
    // SQL query construction with optional filters
    // 'WHERE 1=1' is equivalent to a 'true' expression
    StringBuilder sql = new StringBuilder("SELECT * FROM schedule WHERE 1=1");
    
    if (timeEdited != null && !timeEdited.isEmpty()) {
        sql.append(" AND DATE(time_edited) = '").append(timeEdited).append("'");
    }
    if (inCharge != null && !inCharge.isEmpty()) {
        sql.append(" AND in_charge = '").append(inCharge).append("'");
    }

    // Sort the results by `time_edited` in descending order
    sql.append(" ORDER BY time_edited DESC");

    // Execute the query and map results to a list of DTOs
    return jdbcTemplate.query(sql.toString(), new RowMapper<ScheduleResponseDto>() {
        @Override
        public ScheduleResponseDto mapRow(ResultSet rs, int rowNum) throws SQLException {
            Long schedule_id = rs.getLong("schedule_id");
            String title = rs.getString("title");
            String inCharge = rs.getString("in_charge");
            String time_created = rs.getString("time_created");
            String time_edited = rs.getString("time_edited");
            String notes = rs.getString("notes");

            return new ScheduleResponseDto(schedule_id, title, inCharge, time_created, time_edited, notes);
        }
    });
}

💡 JDBC is a pretty complicated and difficult tool to use, next time we will learn about how to simplify all this with JPA! 💡