Apache Commons Javaプログラム演習問題　第１７回

問題

jp.pjin.tech.commons.domain.Todo.java

package jp.pjin.tech.commons.domain;

import java.sql.Timestamp;

import lombok.Data;

@Data
public class Todo {
	private Timestamp createdAt;
	private Timestamp updatedAt;
	private long id;
	private String todo;
}

jp.pjin.tech.commons.db.DbConnection.java

package jp.pjin.tech.commons.db;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSourceFactory;

public class DbConnection {
	private static DataSource ds;

	static {
		Properties prop = new Properties();
		try {
			prop.load(ClassLoader.getSystemResourceAsStream("dbcp.properties"));
			ds = BasicDataSourceFactory.createDataSource(prop);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static Connection get() {
		try {
			return ds.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}

jp.pjin.tech.commons.Practice017.java

package jp.pjin.tech.commons;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import jp.pjin.tech.commons.db.DbConnection;
import jp.pjin.tech.commons.domain.Todo;

public class Practice017 {
	public static void main(String[] args) {
		Connection con = DbConnection.get();
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<Todo> todos = new ArrayList<>();

		try {
			pStmt = con.prepareStatement("select * from todo where id <= ? order by id desc");
			pStmt.setInt(1, 3);
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Todo todo = new Todo();
				todo.setCreatedAt(rs.getTimestamp("createdAt"));
				todo.setUpdatedAt(rs.getTimestamp("updatedAt"));
				todo.setId(rs.getLong("id"));
				todo.setTodo(rs.getString("todo"));
				todos.add(todo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pStmt != null) {
				try {
					pStmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		todos.forEach(System.out::println);
	}
}

PreparedStatementを利用し、SQLの変数(?:プレースフォルダー)に値を入れるサンプルです。
上記のコードをQueryRunnerを利用して、書き換えてください。