최근 몇 주간 작성한 통합 검색 코드이다. 아직 부족한 부분이 너무 많지만 함께 보며 개선해 나가면 좋을 것 같아 업로드한다.
DAO(java file)
package data.dao;
import java.security.Timestamp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Objects;
import java.util.StringJoiner;
/*import javax.security.auth.message.callback.PrivateKeyCallback.Request;*/
import com.mysql.cj.jdbc.result.ResultSetMetaData;
import data.dto.SearchResult;
import mysql.db.DBConnect;
public class SearchDao_v3 {
DBConnect db=new DBConnect();
//String [] tables= {"tourspot","mycourse","recomcourse","tourreview","guestreview","comment"};
/*public List<HashMap<String, String>> searchInputWordsInWholeTables_GPT(String inputWords, int startNum, int perPage) {
List<HashMap<String, String>> resultsList = new ArrayList<HashMap<String, String>>();
String[] keyWordsDivided = inputWords.split(" ");
StringJoiner sj = new StringJoiner("* ");
for (String keyWord : keyWordsDivided) {
sj.add(keyWord);
}
// Combine all the relevant tables using SQL JOIN
String sql = "SELECT t1.table_name, t2.* " +
"FROM (SELECT 'tourspot' AS table_name UNION " +
" SELECT 'mycourse' AS table_name UNION " +
" SELECT 'recomcourse' AS table_name UNION " +
" SELECT 'tourreview' AS table_name) AS t1 " +
"LEFT JOIN (SELECT *, 'tourspot' AS table_name FROM tourspot " +
" UNION ALL " +
" SELECT *, 'mycourse' AS table_name FROM mycourse " +
" UNION ALL " +
" SELECT *, 'recomcourse' AS table_name FROM recomcourse " +
" UNION ALL " +
" SELECT *, 'tourreview' AS table_name FROM tourreview) AS t2 " +
"ON t1.table_name = t2.table_name " +
"WHERE MATCH(t2.columns) AGAINST(? IN BOOLEAN MODE) " +
"LIMIT ?, ?";
try {
Connection conn = db.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, sj.toString());
pstmt.setInt(2, startNum);
pstmt.setInt(3, perPage);
ResultSet rs = pstmt.executeQuery();
// Process the combined result set
while (rs.next()) {
String tableName = rs.getString("table_name");
HashMap<String, String> map = new HashMap<String, String>();
// Populate map with column names and values
ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
String columnName = rsmd.getColumnName(i);
String columnValue = rs.getString(columnName);
map.put(columnName, columnValue);
}
// Add the map to the resultsList
resultsList.add(map);
}
// Close resources
db.dbClose(rs, pstmt, conn);
} catch (SQLException e) {
e.printStackTrace();
}
return resultsList;
}*/
/*public List<HashMap<String, HashMap<String, String>>> searchInputWordsInWholeTables(String inputWords,int startNum,int perPage) {
List<HashMap<String, HashMap<String, String>>> resultsList=new ArrayList<HashMap<String,HashMap<String,String>>>();
String [] keyWordsDivided=inputWords.split(" ");
StringJoiner sj=new StringJoiner("* ");
for(String keyWord:keyWordsDivided)
{
sj.add(keyWord);
}
for(String table:tables)
{
HashMap<String, HashMap<String, String>> mmap=new HashMap<String, HashMap<String,String>>();
String columns=searchColumnNamesInEachTables(table);
List<HashMap<String, String>> mapList=searchColumnsValuesInTable(table, columns, sj.toString()+"*", startNum, perPage);
for(HashMap<String, String> map:mapList)
{
mmap.put(table, map);
resultsList.add(mmap);
}
}
return resultsList;
}*/
/*public List<HashMap<String, HashMap<String, String>>> searchInputWordsInWholeTablesWithStatistics(String inputWords,List<String> tables,int startNum,int perPage,int sort) {
List<HashMap<String, HashMap<String, String>>> resultsList=new ArrayList<HashMap<String,HashMap<String,String>>>();
String [] keyWordsDivided=inputWords.split(" ");
StringJoiner sj=new StringJoiner("* ");
for(String keyWord:keyWordsDivided)
{
sj.add(keyWord);
}
for(String table:tables)
{
HashMap<String, HashMap<String, String>> mmap=new HashMap<String, HashMap<String,String>>();
List<HashMap<String, String>> mapList=null;
String columns=searchColumnNamesInEachTables(table);
if(sort==1) {mapList=searchColumnsValuesInTable(table, columns, sj.toString()+"*", startNum, perPage);}
else if(sort==2) {mapList=searchColumnsValuesInTableOrderByLatest(table, columns, sj.toString()+"*", startNum, perPage);}
else if(sort==3) {mapList=searchColumnsValuesInTableOrderByPopularity(table, columns, sj.toString()+"*", startNum, perPage);}
//else if(sort.equals("")) {return null;}
for(HashMap<String, String> map:mapList)
{
mmap.put(table, map);
resultsList.add(mmap);
}
}
return resultsList;
}*/
public List<HashMap<String, List<HashMap<String, String>>>> searchInputWordsInWholeTablesWithStatistics(String inputWords,List<String> tables,int startNum,int perPage,int sort) {
List<HashMap<String, List<HashMap<String, String>>>> resultsList=new ArrayList<HashMap<String,List<HashMap<String,String>>>>();
String [] keyWordsDivided=inputWords.split(" ");
StringJoiner sj=new StringJoiner("* ");
for(String keyWord:keyWordsDivided)
{
sj.add(keyWord);
}
for(String table:tables)
{
HashMap<String, List<HashMap<String, String>>> mmap=new HashMap<String, List<HashMap<String,String>>>();
List<HashMap<String, String>> mapList=null;
String columns=searchColumnNamesInEachTables(table);
if(sort==1) {mapList=searchColumnsValuesInTable(table, columns, sj.toString()+"*", startNum, perPage);}
else if(sort==2) {mapList=searchColumnsValuesInTableOrderByLatest(table, columns, sj.toString()+"*", startNum, perPage);}
else if(sort==3) {mapList=searchColumnsValuesInTableOrderByPopularity(table, columns, sj.toString()+"*", startNum, perPage);}
//else if(sort.equals("")) {return null;}
mmap.put(table, mapList);
resultsList.add(mmap);
}
return resultsList;
}
public List<HashMap<String, String>> searchInputWordsInTable(String inputWords,String table,int startNum,int perPage) {
List<HashMap<String, String>> results=new ArrayList<HashMap<String,String>>();
String [] keyWordsDivided=inputWords.split(" ");
StringJoiner sj=new StringJoiner("* ");
for(String keyWord:keyWordsDivided)
{
sj.add(keyWord);
}
String columns=searchColumnNamesInEachTables(table);
results=searchColumnsValuesInTable(table, columns, sj.toString(), startNum, perPage);
return results;
}
public String searchColumnNamesInEachTables(String table) {
//String [] columnsArr=null;
String columns="";
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
ResultSet rs=null;
String sql="select column_name from information_schema.columns "
+"where table_schema='semi' and table_name=?";
try {
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, table);
rs=pstmt.executeQuery();
while(rs.next())
{
String cn=rs.getString("column_name");
if(!cn.equals("tour_seq")&&!cn.equals("writeday")&&!cn.equals("stars")&&!cn.equals("viewcount")&&!cn.equals("pass")&&
!cn.equals("price")&&!cn.equals("day")&&!cn.equals("turn")&&!cn.equals("likes")&&!cn.equals("seq")&&!cn.equals("com_seq"))
{columns+=cn+",";}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
db.dbClose(rs, pstmt, conn);
}
columns=(columns.length()<=0?"":columns.substring(0, columns.length()-1));
//columnsArr=columns.split(",");
return columns;
}
public List<HashMap<String, String>> searchColumnsValuesInTable(String table,String columns,String keyWords,int startNum,int perPage) {
List<HashMap<String, String>> results=new ArrayList<HashMap<String,String>>();
String [] columnsArr=columns.split(",");
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
ResultSet rs=null;
String seqName="";
if(table.equals("TourSpot")) {seqName="seq";}
else {seqName="tour_seq";}
StringJoiner sj=new StringJoiner(",t.");
for(String column:columnsArr){sj.add(column);}
String sql="";
if(table.equals("TourReview")) {
sql="select t.*,c.id,c.membercomment from "+table+" t JOIN Statistics s ON t."+seqName+"=s.tour_seq JOIN MemberComment c ON t.com_seq=c.com_seq"
+" where MATCH(t."+sj.toString()+",c.id,c.membercomment) AGAINST(? IN BOOLEAN MODE) limit ?,?";
}
else if(table.equals("GuestReview")) {
sql="select t.*,c.writer,c.guestcomment from "+table+" t JOIN Statistics s ON t."+seqName+"=s.tour_seq JOIN GuestComment c ON t.com_seq=c.com_seq"
+" where MATCH(t."+sj.toString()+",c.writer,c.guestcomment) AGAINST(? IN BOOLEAN MODE) limit ?,?";
}
else {
sql="select t.* from "+table+" t JOIN Statistics s ON t."+seqName
+"=s.tour_seq where MATCH(t."+sj.toString()+") AGAINST(? IN BOOLEAN MODE) limit ?,?";
}
try {
pstmt=conn.prepareStatement(sql);
//pstmt.setString(1, "%"+keyWord+"%");
pstmt.setString(1, keyWords);
pstmt.setInt(2, startNum);
pstmt.setInt(3, perPage);
rs=pstmt.executeQuery();
keyWords=keyWords.substring(0, keyWords.length()-1);
String [] keyWordsDivided=keyWords.split("\\* ");
while(rs.next())
{
HashMap<String, String> map=new HashMap<String, String>();
for(String column:columnsArr)
{
String columnValue=rs.getString(column)==null?"":rs.getString(column);
StringBuilder result=new StringBuilder(columnValue);
int minForPrevAbrv=Integer.MAX_VALUE;
int maxForEndAbrv=0;
for(String keyWord:keyWordsDivided)
{
columnValue=result.toString();
if(columnValue.contains(keyWord))
{
StringBuilder keyWordResult=new StringBuilder();
int index=0;
while((index=columnValue.indexOf(keyWord, 0))>=0)
{
//int forPrevAbrv=Math.max(0, index-10);
//int forEndAbrv=Math.min(columnValue.length(), index+keyWord.length());
//minForPrevAbrv=Math.min(minForPrevAbrv, forPrevAbrv);
//maxForEndAbrv=Math.max(maxForEndAbrv, forEndAbrv);
keyWordResult.append(columnValue.substring(0, index));
keyWordResult.append("<b style='background-color:yellow'>"+keyWord+"</b>");
columnValue=columnValue.substring(index+keyWord.length());
}
keyWordResult.append(columnValue);
System.out.println(column+":"+keyWordResult.toString());
result.replace(0, result.length(), keyWordResult.toString());
maxForEndAbrv=Math.max(maxForEndAbrv, result.lastIndexOf(keyWord)+keyWord.length()+14);
minForPrevAbrv=Math.min(minForPrevAbrv, result.indexOf(keyWord)-45);
}
}
if(result.toString().contains("<b style='background-color:yellow'>"))
{
System.out.println(minForPrevAbrv);
map.put(column, (minForPrevAbrv<0?"":"...")+result.toString().substring(Math.max(0, minForPrevAbrv), Math.min(result.toString().length(), maxForEndAbrv))+(maxForEndAbrv>result.toString().length()?"":"..."));
} else {map.put(column, (result.toString().length()>20?result.toString().substring(0, 20)+"...":result.toString()));}
//map.put(column, result.toString());
}
results.add(map);
}
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
db.dbClose(rs, pstmt, conn);
}
return results;
}
public List<HashMap<String, String>> searchColumnsValuesInTableOrderByPopularity(String table,String columns,String keyWords,int startNum,int perPage) {
List<HashMap<String, String>> results=new ArrayList<HashMap<String,String>>();
String [] columnsArr=columns.split(",");
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
ResultSet rs=null;
String seqName="";
if(table.equals("TourSpot")) {seqName="seq";}
else {seqName="tour_seq";}
StringJoiner sj=new StringJoiner(",t.");
for(String column:columnsArr){sj.add(column);}
String sql="";
if(table.equals("TourReview")) {
sql="select t.*,c.id,c.membercomment from "+table+" t JOIN Statistics s ON t."+seqName+"=s.tour_seq JOIN MemberComment c ON t.com_seq=c.com_seq"
+" where MATCH(t."+sj.toString()+",c.id,c.membercomment) AGAINST(? IN BOOLEAN MODE) order by s.selected_cnt desc limit ?,?";
}
else if(table.equals("GuestReview")) {
sql="select t.*,c.writer,c.guestcomment from "+table+" t JOIN Statistics s ON t."+seqName+"=s.tour_seq JOIN GuestComment c ON t.com_seq=c.com_seq"
+" where MATCH(t."+sj.toString()+",c.writer,c.guestcomment) AGAINST(? IN BOOLEAN MODE) order by s.selected_cnt desc limit ?,?";
}
else {
sql="select t.* from "+table+" t JOIN Statistics s ON t."+seqName
+"=s.tour_seq where MATCH(t."+sj.toString()+") AGAINST(? IN BOOLEAN MODE) order by s.selected_cnt desc limit ?,?";
}
//String sql="select * from "+table+" where name like ?";
try {
pstmt=conn.prepareStatement(sql);
//pstmt.setString(1, "%"+keyWord+"%");
pstmt.setString(1, keyWords);
pstmt.setInt(2, startNum);
pstmt.setInt(3, perPage);
rs=pstmt.executeQuery();
keyWords=keyWords.substring(0, keyWords.length()-1);
String [] keyWordsDivided=keyWords.split("\\* ");
while(rs.next())
{
HashMap<String, String> map=new HashMap<String, String>();
for(String column:columnsArr)
{
String columnValue=rs.getString(column)==null?"":rs.getString(column);
StringBuilder result=new StringBuilder(columnValue);
int minForPrevAbrv=Integer.MAX_VALUE;
int maxForEndAbrv=0;
for(String keyWord:keyWordsDivided)
{
columnValue=result.toString();
if(columnValue.contains(keyWord))
{
StringBuilder keyWordResult=new StringBuilder();
int index=0;
while((index=columnValue.indexOf(keyWord, 0))>=0)
{
keyWordResult.append(columnValue.substring(0, index));
keyWordResult.append("<b style='background-color:yellow'>"+keyWord+"</b>");
columnValue=columnValue.substring(index+keyWord.length());
}
keyWordResult.append(columnValue);
System.out.println(column+":"+keyWordResult.toString());
result.replace(0, result.length(), keyWordResult.toString());
maxForEndAbrv=Math.max(maxForEndAbrv, result.lastIndexOf(keyWord)+keyWord.length()+14);
minForPrevAbrv=Math.min(minForPrevAbrv, result.indexOf(keyWord)-45);
}
}
if(result.toString().contains("<b style='background-color:yellow'>"))
{
map.put(column, (minForPrevAbrv<0?"":"...")+result.toString().substring(Math.max(0, minForPrevAbrv), Math.min(result.toString().length(), maxForEndAbrv))+(maxForEndAbrv>result.toString().length()?"":"..."));
} else {map.put(column, (result.toString().length()>20?result.toString().substring(0, 20)+"...":result.toString()));}
//map.put(column, result.toString());
}
results.add(map);
}
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
db.dbClose(rs, pstmt, conn);
}
return results;
}
public List<HashMap<String, String>> searchColumnsValuesInTableOrderByLatest(String table,String columns,String keyWords,int startNum,int perPage) {
List<HashMap<String, String>> results=new ArrayList<HashMap<String,String>>();
String [] columnsArr=columns.split(",");
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
ResultSet rs=null;
String seqName="";
if(table.equals("TourSpot")) {seqName="seq";}
else {seqName="tour_seq";}
StringJoiner sj=new StringJoiner(",t.");
for(String column:columnsArr){sj.add(column);}
String sql="";
if(table.equals("TourReview")) {
sql="select t.*,c.id,c.membercomment from "+table+" t JOIN Statistics s ON t."+seqName+"=s.tour_seq JOIN MemberComment c ON t.com_seq=c.com_seq"
+" where MATCH(t."+sj.toString()+",c.id,c.membercomment) AGAINST(? IN BOOLEAN MODE) order by s.selected_date desc limit ?,?";
}
else if(table.equals("GuestReview")) {
sql="select t.*,c.writer,c.guestcomment from "+table+" t JOIN Statistics s ON t."+seqName+"=s.tour_seq JOIN GuestComment c ON t.com_seq=c.com_seq"
+" where MATCH(t."+sj.toString()+",c.writer,c.guestcomment) AGAINST(? IN BOOLEAN MODE) order by s.selected_date desc limit ?,?";
}
else {
sql="select t.* from "+table+" t JOIN Statistics s ON t."+seqName
+"=s.tour_seq where MATCH(t."+sj.toString()+") AGAINST(? IN BOOLEAN MODE) order by s.selected_date desc limit ?,?";
}
//String sql="select * from "+table+" where name like ?";
try {
pstmt=conn.prepareStatement(sql);
//pstmt.setString(1, "%"+keyWord+"%");
pstmt.setString(1, keyWords);
pstmt.setInt(2, startNum);
pstmt.setInt(3, perPage);
rs=pstmt.executeQuery();
keyWords=keyWords.substring(0, keyWords.length()-1);
String [] keyWordsDivided=keyWords.split("\\* ");
while(rs.next())
{
HashMap<String, String> map=new HashMap<String, String>();
for(String column:columnsArr)
{
String columnValue=rs.getString(column)==null?"":rs.getString(column);
StringBuilder result=new StringBuilder(columnValue);
int minForPrevAbrv=Integer.MAX_VALUE;
int maxForEndAbrv=0;
for(String keyWord:keyWordsDivided)
{
columnValue=result.toString();
if(columnValue.contains(keyWord))
{
StringBuilder keyWordResult=new StringBuilder();
int index=0;
while((index=columnValue.indexOf(keyWord, 0))>=0)
{
keyWordResult.append(columnValue.substring(0, index));
keyWordResult.append("<b style='background-color:yellow'>"+keyWord+"</b>");
columnValue=columnValue.substring(index+keyWord.length());
}
keyWordResult.append(columnValue);
System.out.println(column+":"+keyWordResult.toString());
result.replace(0, result.length(), keyWordResult.toString());
maxForEndAbrv=Math.max(maxForEndAbrv, result.lastIndexOf(keyWord)+keyWord.length()+14);
minForPrevAbrv=Math.min(minForPrevAbrv, result.indexOf(keyWord)-45);
}
}
if(result.toString().contains("<b style='background-color:yellow'>"))
{
map.put(column, (minForPrevAbrv<0?"":"...")+result.toString().substring(Math.max(0, minForPrevAbrv), Math.min(result.toString().length(), maxForEndAbrv))+(maxForEndAbrv>result.toString().length()?"":"..."));
} else {map.put(column, (result.toString().length()>20?result.toString().substring(0, 20)+"...":result.toString()));}
//map.put(column, result.toString());
}
results.add(map);
}
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
db.dbClose(rs, pstmt, conn);
}
return results;
}
public int getTotalCount(String inputWords,List<String> tables) {
int total=0;
String [] keyWordsDivided=inputWords.split(" ");
StringJoiner sj=new StringJoiner("* ");
for(String keyWord:keyWordsDivided)
{
sj.add(keyWord);
}
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
ResultSet rs=null;
for(String table:tables)
{
int totalIndividual=0;
String columns=searchColumnNamesInEachTables(table);
StringJoiner js=new StringJoiner(",");
/*for(String column:columnsArr)
{
sj.add(column);
}*/
String sql="select count(*) from "+table+" where MATCH("+columns+") AGAINST(? IN BOOLEAN MODE)";
//String sql="select count(*) from "+table+" where name like ?";
try {
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, sj.toString()+"*");
rs=pstmt.executeQuery();
if(rs.next())
totalIndividual=Integer.parseInt(rs.getString(1));
System.out.println("각자:"+totalIndividual);
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
db.dbClose(rs, pstmt, conn);
}
total+=totalIndividual;
}
System.out.println("개수:"+total);
return total;
}
}
이 함수를 실행하여 실제로 화면에 구현할 jsp 코드는 다음과 같다.
구현 코드
<%@page import="data.dao.SearchDao"%>
<%@page import="java.util.HashMap"%>
<%@page import="java.util.ArrayList"%>
<%@page import="data.dto.SearchResult"%>
<%@page import="java.util.List"%>
<%@page import="data.dao.SearchDao_v3"%>
<%@ page language="java" contentType="text/html; charset=EUC-KR"
pageEncoding="EUC-KR"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="EUC-KR">
<link href="https://fonts.googleapis.com/css2?family=Dongle:wght@300&family=Nanum+Pen+Script&family=Noto+Serif+KR:wght@200&display=swap" rel="stylesheet">
<!-- <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet"> -->
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.5/font/bootstrap-icons.css">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.1/dist/js/bootstrap.bundle.min.js"></script>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.1/dist/css/bootstrap.min.css" rel="stylesheet">
<title>Insert title here</title>
</head>
<style>
ul.left-tab{
float: left;
}
ul.right-tab{
float: right;
}
div.tab-pane{
position: absolute;
top: 130px;
left: 40px;
}
@import url(//fonts.googleapis.com/earlyaccess/jejugothic.css);
body * {
font-family: 'Jeju Gothic', sans-serif;
}
</style>
<script>
$(function(){
$("button.v").click(function(){
$("div.v_align").show();
$("div.grid_align").hide();
});
$("button.h").click(function(){
$("div.grid_align").show();
$("div.v_align").hide();
});
$("div.grid_align").hide();
});
</script>
<body>
<%!
public String translation(String table) {
String tableName="";
if(table.equals("TourSpot")){tableName="관광지";}
else if(table.equals("MyCourse")){tableName="나만의코스";}
else if(table.equals("RecomCourse")){tableName="추천코스";}
else if(table.equals("TourReview")){tableName="리뷰";}
else{tableName="비회원리뷰";}
return tableName;
}%>
<%
String root = request.getContextPath();
request.setCharacterEncoding("utf-8");
String sortidx=(request.getParameter("sortidx")==null||request.getParameter("sortidx").equals("null")?"":request.getParameter("sortidx"));
List<String> tables=new ArrayList<String>();
if(sortidx.equals("0")||sortidx.equals(""))
{tables.add("TourSpot");tables.add("MyCourse");tables.add("RecomCourse");tables.add("TourReview");tables.add("GuestReview");}
else if(sortidx.equals("1")){tables.add("TourSpot");}
else if(sortidx.equals("2")){tables.add("MyCourse");}
else if(sortidx.equals("3")){tables.add("RecomCourse");}
else if(sortidx.equals("4")){tables.add("TourReview");}
else if(sortidx.equals("5")){tables.add("GuestReview");}
String inputWords=(request.getParameter("inputWords")==null||request.getParameter("inputWords").equals("null")?"":request.getParameter("inputWords"));
SearchDao_v3 searchDao=new SearchDao_v3();
int totalCount=searchDao.getTotalCount(inputWords,tables);
int totalPage;
int startPage;
int endPage;
int startNum;
int perPage=3;
int perBlock=5;
int currentPage;
if(request.getParameter("currentPage")==null||request.getParameter("currentPage").equals("null")) {currentPage=1;}
else {currentPage=Integer.parseInt(request.getParameter("currentPage"));}
totalPage=totalCount/perPage+(totalCount%perPage==0?0:1);
startPage=(currentPage-1)/perBlock*perBlock+1;
endPage=startPage+perBlock-1;
if(endPage>totalPage)
endPage=totalPage;
startNum=(currentPage-1)*perPage;
System.out.println("키워드:"+inputWords+",sidx:"+sortidx+"cp:"+currentPage);
%>
<div>
<div style="margin: 0px 50px 0px 50px"><br>
<nav class="navbar navbar-expand-sm bg-light navbar-light">
<div class="container-fluid">
<div class="collapse navbar-collapse" id="collapsibleNavbar">
<ul class="navbar-nav">
<li class="nav-item">
<a class="nav-link" href="index.jsp?main=search/searchIndex.jsp?sortidx=0¤tPage=<%=currentPage%>&inputWords=<%=inputWords%>">전체</a>
</li>
<li class="nav-item">
<a class="nav-link" href="index.jsp?main=search/searchIndex.jsp?sortidx=1¤tPage=<%=currentPage%>&inputWords=<%=inputWords%>">관광지</a>
</li>
<li class="nav-item">
<a class="nav-link" href="index.jsp?main=search/searchIndex.jsp?sortidx=2¤tPage=<%=currentPage%>&inputWords=<%=inputWords%>">나만의코스</a>
</li>
<li class="nav-item">
<a class="nav-link " href="index.jsp?main=search/searchIndex.jsp?sortidx=3¤tPage=<%=currentPage%>&inputWords=<%=inputWords%>">추천코스</a>
</li>
<li class="nav-item">
<a class="nav-link" href="index.jsp?main=search/searchIndex.jsp?sortidx=4¤tPage=<%=currentPage%>&inputWords=<%=inputWords%>">리뷰</a>
</li>
<li class="nav-item">
<a class="nav-link" href="index.jsp?main=search/searchIndex.jsp?sortidx=5¤tPage=<%=currentPage%>&inputWords=<%=inputWords%>">비회원리뷰</a>
</li>
</ul>
</div>
</div>
</nav>
</div>
<div class="container mt-3">
<!-- Nav tabs -->
<ul class="nav nav-tabs left-tab" style="margin-left: 33px">
<li class="nav-item">
<button type="button" class="btn btn-outline-info btn-sm v"><i class="bi bi-justify"></i></button>
</li>
<li class="nav-item">
<button type="button" class="btn btn-outline-info btn-sm h"><i class="bi bi-border-all"></i></button>
</li>
</ul>
<ul class="nav nav-tabs right-tab">
<li class="nav-item">
<a class="nav-link active sortBtn1" data-bs-toggle="tab" href="#tabs1" style="color: black">관련도순</a>
</li>
<li class="nav-item">
<a class="nav-link sortBtn2" data-bs-toggle="tab" href="#tabs2" style="color: black">최신순</a>
</li>
<li class="nav-item">
<a class="nav-link sortBtn3" data-bs-toggle="tab" href="#tabs3" style="color: black">인기순</a>
</li>
</ul>
</div>
</div>
<div class="tab-content">
<div id="tabs1" class="container tab-pane fade row"><br>
<span>"<%=inputWords %>"에 대한 총 <%=totalCount %>개의 검색결과가 있습니다</span>
<%List<HashMap<String, List<HashMap<String, String>>>> list_relevance=searchDao.searchInputWordsInWholeTablesWithStatistics(inputWords, tables, startNum, perPage, 1);
for(int i=0;i<list_relevance.size();i++)
{
HashMap<String, List<HashMap<String, String>>> tableMap=list_relevance.get(i);
for(String table:tables)
{
String columns=searchDao.searchColumnNamesInEachTables(table);
String [] columnsArr=columns.split(",");
if(tableMap.get(table)==null){continue;}
else
{
List<HashMap<String, String>> mapList=tableMap.get(table);
for(HashMap<String, String> map:mapList)
{
%>
<div class="v_align">
<table class="tb1">
<caption align="top" style="font-size: 1.2em"><%=translation(table) %></caption>
<tr style="text-align: center;">
<th>사진</th><th>정보</th>
</tr>
<%String photo="";
for(String column:columnsArr){
if(column.equals("photo")){photo=(map.get("photo")==null?"":map.get("photo"));}
else if(column.equals("mainphoto")){photo=(map.get("mainphoto")==null?"":map.get("mainphoto"));}
}
int j=0;
for(String column:columnsArr)
{%>
<tr>
<%if(j==0&&!photo.equals("")){%>
<td rowspan="<%=columnsArr.length%>"><img src="<%=root%>/jeju/<%=photo%>" style="width: 300px; height: 300px; border-radius: 10px 10px 10px 10px;"></td>
<%}else if(j==0&&photo.equals("")){%>
<td rowspan="<%=columnsArr.length%>"><img src="<%=root%>/jeju/maininfo.png" style="width: 300px; height: 300px; border-radius: 10px 10px 10px 10px;"></td>
<%}
if(!column.equals("photo")&&!column.equals("mainphoto")){%>
<td><%=map.get(column) %></td><%} %>
</tr>
<%j++;}%>
</table>
</div>
<div class="grid_align col-md-6" style="float: left">
<table class="tb2">
<caption align="top" style="font-size: 1.2em"><%=translation(table) %></caption>
<tr>
<%if(!photo.equals("")){%>
<td rowspan="<%=columnsArr.length%>"><img src="<%=root%>/jeju/<%=photo%>" style="width: 300px; height: 300px; border-radius: 10px 10px 10px 10px;"></td>
<%}else if(photo.equals("")){%>
<td rowspan="<%=columnsArr.length%>"><img src="<%=root%>/jeju/maininfo.png" style="width: 300px; height: 300px; border-radius: 10px 10px 10px 10px;"></td>
<%}%>
</tr>
<%for(String column:columnsArr)
{
if(!column.equals("photo")&&!column.equals("mainphoto")){%>
<tr>
<td><%=map.get(column) %></td>
</tr>
<%}}%>
</table>
</div>
<%}
}
}
}%>
</div>
<div id="tabs2" class="container tab-pane fade"><br>
<span>"<%=inputWords %>"에 대한 총 <%=totalCount %>개의 검색결과가 있습니다</span>
<%List<HashMap<String, List<HashMap<String, String>>>> list_latest=searchDao.searchInputWordsInWholeTablesWithStatistics(inputWords, tables, startNum, perPage, 2);
for(int i=0;i<list_latest.size();i++)
{
HashMap<String, List<HashMap<String, String>>> tableMap=list_latest.get(i);
for(String table:tables)
{
String columns=searchDao.searchColumnNamesInEachTables(table);
String [] columnsArr=columns.split(",");
if(tableMap.get(table)==null){continue;}
else
{
List<HashMap<String, String>> mapList=tableMap.get(table);
for(HashMap<String, String> map:mapList)
{
%>
<div class="v_align">
<table class="tb3">
<caption align="top" style="font-size: 1.2em"><%=translation(table) %></caption>
<tr style="text-align: center;">
<th>사진</th><th>정보</th>
</tr>
<%String photo="";
for(String column:columnsArr){
if(column.equals("photo")){photo=(map.get("photo")==null?"":map.get("photo"));}
else if(column.equals("mainphoto")){photo=(map.get("mainphoto")==null?"":map.get("mainphoto"));}
}
int j=0;
for(String column:columnsArr)
{%>
<tr>
<%if(j==0&&!photo.equals("")){%>
<td rowspan="<%=columnsArr.length%>"><img src="<%=root%>/jeju/<%=photo %>" style="width: 300px; height: 300px; border-radius: 10px 10px 10px 10px;"></td>
<%}else if(j==0&&photo.equals("")){%>
<td rowspan="<%=columnsArr.length%>"><img src="<%=root%>/jeju/maininfo.png" style="width: 300px; height: 300px; border-radius: 10px 10px 10px 10px;"></td>
<%}
if(!column.equals("photo")&&!column.equals("mainphoto")){%>
<td><%=map.get(column) %></td><%} %>
</tr>
<%j++;}%>
</table>
</div>
<div class="grid_align col-md-6" style="float: left">
<table class="tb4">
<caption align="top" style="font-size: 1.2em"><%=translation(table) %></caption>
<tr>
<%if(!photo.equals("")){%>
<td rowspan="<%=columnsArr.length%>"><img src="<%=root%>/jeju/<%=photo%>" style="width: 300px; height: 300px; border-radius: 10px 10px 10px 10px;"></td>
<%}else if(photo.equals("")){%>
<td rowspan="<%=columnsArr.length%>"><img src="<%=root%>/jeju/maininfo.png" style="width: 300px; height: 300px; border-radius: 10px 10px 10px 10px;"></td>
<%}%>
</tr>
<%for(String column:columnsArr)
{
if(!column.equals("photo")&&!column.equals("mainphoto")){%>
<tr>
<td><%=map.get(column) %></td>
</tr>
<%}}%>
</table>
</div>
<%}
}
}
}%>
</div>
<div id="tabs3" class="container tab-pane fade"><br>
<span>"<%=inputWords %>"에 대한 총 <%=totalCount %>개의 검색결과가 있습니다</span>
<%List<HashMap<String, List<HashMap<String, String>>>> list_popularity=searchDao.searchInputWordsInWholeTablesWithStatistics(inputWords, tables, startNum, perPage, 3);
for(int i=0;i<list_popularity.size();i++)
{
HashMap<String, List<HashMap<String, String>>> tableMap=list_popularity.get(i);
for(String table:tables)
{
String columns=searchDao.searchColumnNamesInEachTables(table);
String [] columnsArr=columns.split(",");
if(tableMap.get(table)==null){continue;}
else
{
List<HashMap<String, String>> mapList=tableMap.get(table);
for(HashMap<String, String> map:mapList)
{
%>
<div class="v_align">
<table class="tb5">
<caption align="top" style="font-size: 1.2em"><%=translation(table) %></caption>
<tr style="text-align: center;">
<th>사진</th><th>정보</th>
</tr>
<%String photo="";
for(String column:columnsArr){
if(column.equals("photo")){photo=(map.get("photo")==null?"":map.get("photo"));}
else if(column.equals("mainphoto")){photo=(map.get("mainphoto")==null?"":map.get("mainphoto"));}
}
int j=0;
for(String column:columnsArr)
{%>
<tr>
<%if(j==0&&!photo.equals("")){%>
<td rowspan="<%=columnsArr.length%>"><img src="<%=root%>/jeju/<%=photo %>" style="width: 300px; height: 300px; border-radius: 10px 10px 10px 10px;"></td>
<%}else if(j==0&&photo.equals("")){%>
<td rowspan="<%=columnsArr.length%>"><img src="<%=root%>/jeju/maininfo.png" style="width: 300px; height: 300px; border-radius: 10px 10px 10px 10px;"></td>
<%}
if(!column.equals("photo")&&!column.equals("mainphoto")){%>
<td><%=map.get(column) %></td><%} %>
</tr>
<%j++;}%>
</table>
</div>
<div class="grid_align col-md-6" style="float: left">
<table class="tb6">
<caption align="top" style="font-size: 1.2em"><%=translation(table) %></caption>
<tr>
<%if(!photo.equals("")){%>
<td rowspan="<%=columnsArr.length%>"><img src="<%=root%>/jeju/<%=photo%>" style="width: 300px; height: 300px; border-radius: 10px 10px 10px 10px;"></td>
<%}else if(photo.equals("")){%>
<td rowspan="<%=columnsArr.length%>"><img src="<%=root%>/jeju/maininfo.png" style="width: 300px; height: 300px; border-radius: 10px 10px 10px 10px;"></td>
<%}%>
</tr>
<%for(String column:columnsArr)
{
if(!column.equals("photo")&&!column.equals("mainphoto")){%>
<tr>
<td><%=map.get(column) %></td>
</tr>
<%}}%>
</table>
</div>
<%}
}
}
}%>
</div>
<div style="width: 600px;text-align: center" class="container mt-3">
<ul class="pagination justify-content-center">
<%
if(startPage>1)
{%>
<li class="page-item">
<a href="index.jsp?main=search/searchIndex.jsp?currentPage=<%=startPage-1%>&sortidx=<%=sortidx %>&inputWords=<%=inputWords %>" class="page-link">이전</a>
</li>
<%}
for(int pp=startPage;pp<=endPage;pp++)
{
if(pp==currentPage)
{%>
<li class="page-item active">
<a href="index.jsp?main=search/searchIndex.jsp?currentPage=<%=pp%>&sortidx=<%=sortidx %>&inputWords=<%=inputWords %>" class="page-link"><%=pp %></a>
</li>
<%}else
{%>
<li class="page-item">
<a href="index.jsp?main=search/searchIndex.jsp?currentPage=<%=pp%>&sortidx=<%=sortidx %>&inputWords=<%=inputWords %>" class="page-link"><%=pp %></a>
</li>
<%}
}
if(endPage<totalPage)
{%>
<li class="page-item">
<a href="index.jsp?main=search/searchIndex.jsp?currentPage=<%=endPage+1%>&sortidx=<%=sortidx %>&inputWords=<%=inputWords %>" class="page-link">다음</a>
</li>
<%}
%>
</ul>
</div>
</div>
</body>
</html>
초보개발자