aswi2020vldc-gitlab/src/main/java/vldc/aswi/web/controller/IndexController.java @ 26342eaf

package vldc.aswi.web.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.servlet.ModelAndView;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Controller
public class IndexController {

	@Autowired
	protected DataSource dataSource;

	@GetMapping("/")
	public ModelAndView index() throws Exception {
		ModelAndView modelAndView = new ModelAndView("index");

		ModelMap modelMap = modelAndView.getModelMap();

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		String sql = "SELECT SP.fakulta_SP as fakulta_studia, " +
				"SP.kodSP_i as program_studia, " +
				"fn_meaning2(SP.typ,'TYP_OBORU') as typ_studia, " +
				"fn_meaning2(SP.forma,'FORMA_OBORU_NEW') as forma_studia, " +
				"decode(SP.vykazovan,'A',ST.vykazovan,'N') as vykazovan_na_SIMS, " +
				"ST.os_cislo as studium, fn_meaning2(ST.stav,'STAV_STUDENTA') as stav_studia, " +
				"OS.prijmeni || ' ' || OS.jmeno as osoba, trunc((sysdate - OS.datum_naroz) / 365) as vek, " +
				"1 as pocet " +
				"FROM studijni_programy SP, studenti ST, osoby OS " +
				"WHERE SP.stpridno = ST.stpridno AND ST.osobidno = OS.osobidno AND ROWNUM <= 20";

		String resultHeader = "fakulta_studia;typ_studia;forma_studia;vykazovan_na_SIMS;stav_studia;vek;pocet";
		List<String> sqlResults = new ArrayList<String>();
		sqlResults.add(resultHeader);
		 sqlResults.addAll(jdbcTemplate.query(
				sql,
				new RowMapper<String>() {
					public String mapRow(ResultSet rs, int rowNum) throws SQLException {
						String str =
								rs.getString("fakulta_studia") +
								";" +
								rs.getString("typ_studia") +
								";" +
								rs.getString("forma_studia") +
								";" +
								rs.getString("vykazovan_na_SIMS") +
								";" +
								rs.getString("stav_studia") +
								";" +
								rs.getString("vek") +
								";" +
								rs.getString("pocet")
								;
						return str;
					}
				})
		 );

		modelMap.addAttribute("message", "Hello World!");
		modelMap.addAttribute("sqlResults", sqlResults);

		return modelAndView;
	}

}