JDBC

오랫만에 JDBC

2021년 10월 08일

java JDBC

JDBC 게시판

notice table에 데이터 넣고 가지고 놀기.

SQL

create database member;

use member;

create table notice (
id int,
title varchar(100),
writer_id varchar(50),
content longtext,
regdate timestamp,
hit int,
files varchar(1000)
);

create table comment (
id int,
content varchar(2000),
regdate timestamp,
writer_id varchar(50),
notice_id int
);

create table role(
id varchar(50),
discription varchar(500)
);

create table member_role (
member_id varchar(50),
role_id varchar(50)
);

create table member (
id varchar(50),
pwd varchar(50),
name varchar(50),
gender char(2),
birthday char(10),
phone char(13),
regdate date,
email varchar(200)
);

insert into notice values (1, 'JDBC란 무엇인가?', 'yoon', 'aaa', sysdate(), 0, '');
insert into notice values (2, 'JDBC란 무엇인가?2', 'yoon2', 'aaa', sysdate(), 0, '');
insert into notice values (3, 'JDBC란 무엇인가?3', 'yoon3', 'aaa', sysdate(), 0, '');

Program.java

package ex1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class Program {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		
		String url = "jdbc:mysql://localhost:3306/member?";
		
		String sql = "select * from notice";
		
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection con = DriverManager.getConnection(url, "root", "password");
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery(sql);
		
		
	
		while (rs.next()) {
			
				int id = rs.getInt("id");
				String title = rs.getString("title");
				String writerId = rs.getString("writer_id");
				Date regDate = rs.getDate("regdate");
				String content = rs.getString("content");
				int hit = rs.getInt("hit");
				
				
				System.out.printf(" id: %d \n title: %s \n writerId: %s \n regDate: %s \n content: %s \n hit: %d \n",
						id, title, writerId, regDate, content, hit);
				System.out.println("-------------------------------");
			
		}
		
		rs.close();
		st.close();
		con.close();

	}

}

결과

 id: 1 
 title: JDBC란 무엇인가? 
 writerId: yoon 
 regDate: 2021-10-08 
 content: aaa 
 hit: 0 
-------------------------------
 id: 2 
 title: JDBC란 무엇인가?2 
 writerId: yoon2 
 regDate: 2021-10-08 
 content: aaa 
 hit: 0 
-------------------------------
 id: 3 
 title: JDBC란 무엇인가?3 
 writerId: yoon3 
 regDate: 2021-10-08 
 content: aaa 
 hit: 0 
-------------------------------

문제

hit가 10 이상만 출력해보자.
일단 SQL에서 update구문을 이용하여 hit를 10이상으로 만들어준다.

SET SQL_SAFE_UPDATES = 0;
update notice set hit = 13 where id = 1;

id 1번에 hit를 13으로 바꿔주었다.
그리고 자바코드를 수정해준다.

while (rs.next()) {
			
				int id = rs.getInt("id");
				String title = rs.getString("title");
				String writerId = rs.getString("writer_id");
				Date regDate = rs.getDate("regdate");
				String content = rs.getString("content");
				int hit = rs.getInt("hit");
				
				//수정부분
				if (hit > 10) {
					
					System.out.printf(" id: %d \n title: %s \n writerId: %s \n regDate: %s \n content: %s \n hit: %d \n",
							id, title, writerId, regDate, content, hit);
					System.out.println("-------------------------------");
				}
			
		}

보통 이렇게 if문을 걸어서 하는 사람도 꽤나 있을 것 같다.
그러나 이렇게 하면 안된다. 이유는 지금은 상관없지만 나중에 데이터가 1억개 10억개가 넘는다면 그 1억개 10억개를 다 돌면서 찾기때문에 엄청 느릴뿐더러 효율적이지도 않다.
sql문을 고쳐주어야 한다.

package ex1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class Program {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		
		String url = "jdbc:mysql://localhost:3306/member?";
		
		//수정부분
		String sql = "select * from notice where hit > 10";
		
		Class.forName("com.mysql.cj.jdbc.Driver");
		Connection con = DriverManager.getConnection(url, "root", "password");
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery(sql);
		
		
	
		while (rs.next()) {
			
				int id = rs.getInt("id");
				String title = rs.getString("title");
				String writerId = rs.getString("writer_id");
				Date regDate = rs.getDate("regdate");
				String content = rs.getString("content");
				int hit = rs.getInt("hit");
				
				
				System.out.printf(" id: %d \n title: %s \n writerId: %s \n regDate: %s \n content: %s \n hit: %d \n",
						id, title, writerId, regDate, content, hit);
				System.out.println("-------------------------------");
			
		}
		
		rs.close();
		st.close();
		con.close();

	}

	//수정부분
	String sql = "select * from notice where hit > 10";

애초부터 쿼리문을 이렇게 하면 된다.