06. JDBC, select문 활용 연습문제(count, avg, like, paging)

○ 전체 행 개수 출력 (COUNT)

    Q) sungjuk테이블의 전체 행의 개수를 출력하시오

PreparedStatement pstmt = null;
ResultSet rs = null;	
        
StringBuilder sql=new StringBuilder();
    sql.append(" SELECT COUNT(*) AS cnt FROM sungjuk ");

    pstmt = con.prepareStatement(sql.toString());
    rs = pstmt.executeQuery();
    if(rs.next()) {//cursor가 있는지?
        System.out.println("자료있음");
        System.out.println("전체 행 개수 : " + rs.getInt(1));
        System.out.println("전체 행 개수 : " + rs.getInt("cnt"));
    }else {
        System.out.println("자료없음");
    }

○ 평균 구하기 (AVG)

    Q) sungjuk테이블의 국영수 평균을 각각 조회하시오

PreparedStatement pstmt = null;
ResultSet rs = null;

StringBuilder sql=new StringBuilder();
sql.append(" SELECT addr, ROUND(AVG(kor),2) AS kor_avg, ");
sql.append(" ROUND(AVG(eng), 2) AS eng_avg, ROUND(AVG(mat), 2) AS mat_avg ");
sql.append(" FROM sungjuk ");
sql.append(" WHERE addr=? ");

pstmt = con.prepareStatement(sql.toString());
pstmt.setString(1, addr);
rs = pstmt.executeQuery();
if(rs.next()) {//cursor가 있는지?
    System.out.println("자료있음");
    
    //칼럼명 접근
    System.out.printf("국어평균: %.2f\n", rs.getDouble("kor_avg"));
    System.out.printf("영어평균: %.2f\n", rs.getDouble("eng_avg"));
    System.out.printf("수학평균: %.2f\n", rs.getDouble("mat_avg"));	
    
    //칼럼순서 접근
    System.out.printf("국어평균: %.2f\n", rs.getDouble(2));
    System.out.printf("영어평균: %.2f\n", rs.getDouble(3));
    System.out.printf("수학평균: %.2f\n", rs.getDouble(4));

}else {
    System.out.println("자료없음");
}

 

 

○ 특정 문자가 포함된 행 가져오기 (LIKE)

    Q) 이름에 '민'문자가 있는 행을 조회하시오

String col="uname";  //검색칼럼 keyfield
String word="민";    //검색어   keyword

StringBuilder sql=new StringBuilder();
sql.append(" SELECT sno, uname, kor, eng, mat, aver, addr, wdate ");
sql.append(" FROM sungjuk ");

if(word.length()>0) {//검색어가 존재하는지?

    String where=" WHERE " + col + " LIKE '%" + word + "%' " ;
    sql.append(where);

}//if end

sql.append(" ORDER BY wdate DESC ");

pstmt = con.prepareStatement(sql.toString());
rs = pstmt.executeQuery();

if(rs.next()) {//cursor가 있는지?
    System.out.println("자료있음");

    do {

        System.out.print(rs.getInt("sno") + " ");
        System.out.print(rs.getString("uname") + " ");
        System.out.print(rs.getInt("kor") + " ");
        System.out.print(rs.getInt("eng") + " ");
        System.out.print(rs.getInt("mat") + " ");
        System.out.print(rs.getInt("aver") + " ");
        System.out.print(rs.getString("addr") + " ");
        System.out.print(rs.getString("wdate") + " ");
        System.out.println();

    }while(rs.next());	//다음 cursor가 있는지

}else {
    System.out.println("자료없음");
}

 

○ 페이징 (paging)  

    Q) sungjuk테이블에서 이름순으로 정렬후 행번호 4~6행만 조회하시오 (참고 : rownum을 이용한 페이징)

PreparedStatement pstmt = null;
ResultSet rs = null;

int start = 4;	//시작 행번호
int end=6;		//끝 행번호

StringBuilder sql=new StringBuilder();
sql.append(" SELECT * ");	
sql.append(" FROM ( ");
sql.append(" 	SELECT uname, aver, addr, rownum AS rnum ");
sql.append(" 	FROM( ");
sql.append(" 		SELECT uname, aver, addr ");
sql.append(" 		FROM sungjuk ORDER BY uname)) ");
sql.append(" WHERE rnum>=? and rnum<=? ");

pstmt = con.prepareStatement(sql.toString());
pstmt.setInt(1, start);
pstmt.setInt(2, end);

rs = pstmt.executeQuery();

if(rs.next()) {//cursor가 있는지?
    System.out.println("자료있음");

    do {

        System.out.print(rs.getString("uname") + " ");
        System.out.print(rs.getString("aver") + " ");
        System.out.print(rs.getString("addr") + " ");
        System.out.print(rs.getString("rnum") + " ");		
        System.out.println();

    }while(rs.next());	//다음 cursor가 있는지

}else {
    System.out.println("자료없음");
}

○ 테이블 조인

    Q)학번 g1001이 수강신청한 과목을 과목코드별로 조회하시오 (참고 : 학사관리DB, 테이블조인)

PreparedStatement pstmt = null;
ResultSet rs = null;

String hakno="g1001";

StringBuilder sql=new StringBuilder();
sql.append(" SELECT su.hakno, su.gcode, gw.gname ");	
sql.append(" FROM tb_sugang su JOIN tb_gwamok gw ");
sql.append(" ON su.gcode = gw.gcode ");
sql.append(" WHERE su.hakno=? ");
sql.append(" ORDER BY su.gcode ");						

pstmt = con.prepareStatement(sql.toString());
pstmt.setString(1, hakno);

rs = pstmt.executeQuery();

if(rs.next()) {//cursor가 있는지?
    System.out.println("자료있음");

    do {

        System.out.print(rs.getString("hakno") + " ");
        System.out.print(rs.getString("gcode") + " ");
        System.out.print(rs.getString("gname") + " ");
        System.out.println();

    }while(rs.next());	//다음 cursor가 있는지

}else {
    System.out.println("자료없음");
}