package jp.ac.kcska.questionsystem.categorymanager.horizon;

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

import jp.ac.kcska.questionsystem.Mst_categoryVo;

public class GetCategoryList {
	private Connection getConnection() throws SQLException {
		Connection connection = null;
		connection = DriverManager.getConnection(
				"jdbc:mysql://localhost/questionsystem", "root", "mysql");
		connection.setAutoCommit(false);
		return connection;
	}

	public ArrayList<Mst_categoryVo> getList() {
		ArrayList<Mst_categoryVo> categoryList = new ArrayList<Mst_categoryVo>();
		try {
			Connection connection = getConnection();
			PreparedStatement statement = null;
			ResultSet resultSet = null;

			String strSQL = "SELECT * FROM mst_category";
			statement = connection.prepareStatement(strSQL);
			resultSet = statement.executeQuery();

			while (resultSet.next()) {
				Mst_categoryVo categoryVo = new Mst_categoryVo();
				int id = resultSet.getInt("id");
				String categoryname = resultSet.getString("categoryname");
				byte valid = resultSet.getByte("valid");
				categoryVo.setId(id);
				categoryVo.setCategoryname(categoryname);
				categoryVo.setValid(valid);
				
				categoryList.add(categoryVo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return categoryList;
	}
}