JDBC: 연결(Connection),Statement,PreparedStatemnent,ResultSet

JDBC 드라이버 다운로드

사용할 DB의 JDBC 드라이버를 해당 DB사이트에서 다운로드 (ex: mariadb-java-client-2.7.1.jar)

---

JDBC 구현하기

---

1. Connection - 접속

  1.1  드라이버 로딩

준비된 JDBC 드라이버 파일을 사용할 수 있도록 메모리에 로딩

public static void main(String[] args) {
	
	// 드라이버로딩 시작 < 
	try {
		//연결될 드라이버를 로딩
		//클래스를 통해 데이터베이스랑 연결
		//그때그때마다 읽어옴
		//드라이버명 잘못치면 에러남
		Class.forName("org.ariadb.jdbc.Driver"); //JDBC 드라이버로딩
		System.out.println("드라이버 로딩 완료"); //출력되면 로딩완료 확인
	}catch(ClassNotFoundException e) {
		
	}// > 드라이버로딩 끝
}

---

  1.2  드라이버 연결

JDBC 드라이버 로딩이 완료되면 DB서버와 연결작업

	
}// > 드라이버로딩 끝


// 드라이버연결 시작 <
 //url(ip,port,database) / id / password  //연결을 위한 기본 자료
String url = "jdbc:mysql://localhost:3307/sample"; 	//프로토콜,서버주소,서버포트,DB이름
String user = "root"; 					//DB서버에 로그인 할 계정
String password = "pw123!";				//DB서버에 로그인 할 비밀번호

//연결을 위한 커넥션
Connection conn = null;					//예외처리에서 연결 닫아줘야함

try {
	conn = DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
	e.printStackTrace();
	conn.close(); 						//연결닫기
} // > 드라이버연결 끝

---

---

  >> BasicDataSource

드라이버연결을 위한 DriverManager.getConnection(url,user,password); 이 행위는 시간이 오래 걸리기 때문에 

BasicDataSource객체를 사용하여 미리 드라이버연결을 설정한 수대로 만들어 놓는다.

DB연결을 성능관점에서 빠르게 수행할 수 있다. 다만, 여러개의 연결을 만들어 놓기 때문에 용량을 차지

 

아래의 코드는 위의 1.1드라이버 로딩 과 1.2 드라이버 연결을  대신하는 코드이다. 성능적으로 이 코드를 더 많이 사용함

	       
        BasicDataSource bds = new BasicDataSource();

		bds.setUrl(System.getProperty("url", "jdbc:mysql://localhost:3307/sample"));
		bds.setUsername(System.getProperty("user", "root"));
		bds.setPassword(System.getProperty("pw", "pw123!"));
		bds.setDriverClassName(System.getProperty("driver", "org.ariadb.jdbc.Driver"));
		bds.setInitialSize(10); //기본 연결 갯수 설정
		bds.setMaxIdle(10); //최대 연결 갯수 설정
		bds.setValidationQuery("SELECT 1 FROM DUAL");
		bds.setDefaultAutoCommit(true);
        
        try (Connection con = bds.getConnection();
				PreparedStatement pstmt = con.prepareStatement(sql);
                .
                .
                .

---

2. Statement 와 PreparedStatement

위에서 만든 Connection으로 자바 프로그램과 DB 사이에 연결이 되었다면

이 연결을 통해,

자바프로그램은 DB쪽으로 SQL문을 전송하고, DB는 처리된 결과를 다시 자바프로그램 쪽으로 전달해야 한다.

바로 이 역할을 하는 객체가 Statement, PreparedStatement

 

Statemment는 객체 생성 이후 sql문을 완성하여 데이터베이스에 바로바로 처리

PreparedStatement는 객체를 '?'가 포함된 sql문으로 생성하고, 이후에 '?'자리만 바꿔가며 데이터베이스를 처리

  2.1   Statement, PreparedStatement 객체 생성

Statement 객체 생성은 Connection 객체가 제공하는 createStatement()메소드를 사용

Statement stmt = conn.createStatement();

PreparedStatement 객체 생성은 Connection객체가 제공하는 PreparedStatement()메소드를 사용하는데 인자값이 필요

PreparedStatement pstmt = conn.prepareStatement(sql);

PreparedStatement()는 말그대로 '준비된상태'이므로 준비상태인 '?'가 들어간 String 타입의 SQL문이 인자값이 되어야 함

String sql = "insert into TESTTB value(?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);

Statement 객체는 위의 작업을 나중에 한다. PreparedStatement 객체가 미리 하는거라고 보면 됨

>>  SQL문 형태

String타입의 sql문은 3가지 작성법이 있는데, 그 중 String.format()을 가장 많이 사용한다.

 > String의 static 메서드인 String.format()은 문자열의 형식을 설정하는 메서드

// 1
String sql = "insert into TESTTB value(123,'그린','애플')";

// 2
String deptno = "123" ;
String color = "그린";
String name = "애플";

String sql = "insert into TESTB values("+ deptno + ",'" + color + ",'" + name + "')"; 

// 3 - String.format (가장 많이 사용)
String sql = String.format("insert into dept2 values( %s, '%s', '%s')", deptno, color, name);
String sql = String.format("update TESTTB set color = '레드' where deptno = 123");

>(실습)>  SQL문 형태 - Statement - DDL(create)

public static void main(String[] args) throws SQLException {

		String url = "jdbc:mysql://localhost:3306/sample";
		String user = "root";
		String password = "pw123!";

		Connection conn = null;
		Statement stmt = null;

		try {
			Class.forName("org.ariadb.jdbc.Driver");
			System.out.println("드라이버 로딩 완료");

			conn = DriverManager.getConnection(url, user, password);
			System.out.println("연결 완료");

			// @@@@ >(실습)>  SQL문 형태 - Statement - DDL(create) @@@@
			// String sql = String.format("create table TESTTB (deptno number, color varchar(50), name varchar(50))");
			StringBuffer sql = new StringBuffer();
			sql.append("create table TESTTB (");
			sql.append("deptno number,");
			sql.append("color varchar(50),");
			sql.append("name varchar(50)");
			sql.append(")");
			// StringBuffer를 쓰는 경우 
			// executeUpdate(sql.toString())으로 해줘야 함
			
			stmt = conn.createStatement(); //Statement 객체 생성
			
			int result = stmt.executeUpdate(sql.toString());
			System.out.println("쿼리 몇개를 실행했는지? : " + result);

		} catch (ClassNotFoundException e) {
			stmt.close();
            conn.close();
		}
	}

StringBuffer: 문자열을 추가하거나 변경할 때 주로 사용하는 자료형

                     append 메서드를 사용하여 문자열을 계속해서 추가해 나갈 수 있고

                     toString() 메서드를 사용하면 StringBuffer를 String 자료형으로 변경할 수도 있다.

---

  2.2   SQL문 실행

Statement, PreparedStatement 객체를 이용해 DB서버로 sQL문을 전송하고 처리 결과를 받아올 것이다.

 SQL문을 실행하기 위해 객체에서 제공되는 메소드는 다음과 같다.

• ResultSet executeQuery(String sql)                                                                                                                                   => executeQuery() 메소드가 반환하는 ResultSet은 select한 값을 가지고 있다.
• int executeUpdate(String sql)                                                                                                                                   => executeUpdate() 메소드가 반환하는 숫자값은 SQL문 실행(insert,delete,update) 후 영향을 받은 레코드의 수

Statement 객체 SQL문 실행은 인자값으로 SQL문이 필요

int resultNum = stmt.executeUpdate(sql);
ResultSet rs = stmt.executeQuery(sql);

PreparedStatement 객체 SQL문 실행은 인자값이 필요하지 않다.(인자값을 넣으면 두번 실행하는 꼴)

int resultNum = pstmt.executeUpdate();
ResultSet rs = pstmt.executeQuery();

 

>>  ResultSet

executeQuery() 메소드에서 실행된 select 문의 결과값을 가지고 있는 객체

ResultSet객체는 내부적으로 위치를 나타내는 커서(Cursor)라는 개념이 있다.

한 행을 처리할 땐 if문을 사용하고 한 행 이상을 처리할때 보통 while문을 사용한다.

근데,

rs.next()를 꼭 한번은 가져와야하는데, 그 이유는 커서가 위에 그림과 같이 rs.next()의 바로 위에 위치하기 때문에 ! 

따라서 반복문에서 커서의 범위는 [0 ~ 행의 갯수-1]이 아닌 [1 ~ 행의갯수]가 된다.

 

다음의 ResultSet객체의 메소드들은 이 커서의 위치를 이동시킨다.

• boolean next() : 커서 다음에 레코드가 있으면 true, 없으면 false를 반환 후 커서를 다음 레코드로 이동시키는 메소드
• void afterLast() : 커서를 끝 빈  행으로 위치시키는 메소드
• void beforeFirst() : 커서를 시작 빈 행으로 위치시는 메소드

이외에도  현재 이동한 커서의 컬럼의 값을 데이터 타입에 따라 추출하는 getter메소드를 가지고 있다.

getter메소드의 인자값으로는 컬럼의 이름을 지정할 수도 있고, 컬럼의 인덱스 값을 지정할 수도 있다.

컬럼의 인덱스는 ResultSet 결과값으로 나타난 컬럼의 순서라고 생각하면 된다.

• String getString(int columnIndex) : 컬럼의 인덱스로 데이터를 가져옴
• String getString(String columnLabel) : 컬럼의 이름으로 데이터를 가져옴

  2.3  Statement를 사용한 SQL문 실행

>(실습)> SQL문 실행 - Statement - ResultSet - getString()

public static void main(String[] args) throws SQLException {

	String url = "jdbc:mysql://localhost:3306/sample";
	String user = "root";
	String password = "pw123!";

	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;

	try {
		Class.forName("org.ariadb.jdbc.Driver");
		System.out.println("드라이버 로딩 완료");

		conn = DriverManager.getConnection(url, user, password);
		System.out.println("연결 완료");

		// @@@@ >(실습)>  SQL문 형태 - ResultSet - getString()@@@@
		String sql = String.format("select deptno, color, name from TESTTB");
		
		stmt = conn.createStatement(); //Statement 객체 생성
		
		rs = stmt.executeQuery(sql);
		
		while(rs.next()) {
			//TESTTB테이블의 행 수 만큼 출력되어야 함
//			System.out.println(rs.getString("deptno"));
//			System.out.println(rs.getString("color"));
//			System.out.println(rs.getString("name"));

            //순서값으로 쓸 수도 있음
 			System.out.println(rs.getString(1)); //TESTTB
 			System.out.println(rs.getString(2)); //color
  			System.out.println(rs.getString(3)); //name

		}
	} catch (ClassNotFoundException e) {
		rs.close();
		stmt.close();
        conn.close();
	}
}

  2.4  PreparedStatement를 사용한 SQL문 실행

SQL문 안에 '?'를 넣어서 정의 후, 나중에 '?'부분을 채울 수 있다.

'?'부분을 채워 SQL문을 사용하기 위한 메서드는 setString()이다.

• setString(int index, String data) : index는 '?'순서, data는 넣을 값

String Sql = String.format("insert into TESTTB values(?,?,?)");

pstmt = conn.prepareStatement(sql);

Pstmt.setString(1,"123");
Pstmt.setString(2,"그린");
Pstmt.setString(3,"애플");

>(실습)>  SQL문 실행 - PreparedStatement - setString()

public static void main(String[] args) throws SQLException {

	String url = "jdbc:mysql://localhost:3306/sample";
	String user = "root";
	String password = "pw123!";

	Connection conn = null;
	PreparedStatement insertPstmt = null; //입력 실행
	PreparedStatement selectPstmt = null; //조회 실행
	ResultSet rs = null;

	try {
		Class.forName("org.ariadb.jdbc.Driver");
		System.out.println("드라이버 로딩 완료");

		conn = DriverManager.getConnection(url, user, password);
		System.out.println("연결 완료");

		// @@@@ >(실습)>  SQL문 실행 - PreparedStatement - setString() @@@@
		//String sql = "insert into TESTTB values(123, '그린', '애플')";
		
		String selectSql = String.format("select deptno, color, name from TESTTB");
		selectPstmt = conn.prepareStatement(selectSql);
		
		//insert문을 미리 준비시켜놓고 ?의 값만 while문을 통해 행마다 바뀌도록 함
		//따라서 Statement를 쓸때처럼 while문 안에서 insert문을 선언하고 insert문을 문자열화 시키는 과정이 필요없다.
		String insertSql = String.format("insert into TESTTB values(?,?,?)");
		//insertPstmt는 미리 선언되어 준비된 SQL문인 insertSql문이 들어간다.
		insertPstmt = conn.prepareStatement(insertSql); //insert한 쿼리문을 적용
		
		//select문의 결과로 TESTTB의 모든 행이 들어감
		rs = selectPstmt.executeQuery();
		
		while(rs.next()) {
			//값을 바로 넣어줘도 되지만 getString으로 불러와야
			//혹시라도 입력전에 처리작업을 할 때 필요함
			String deptno = rs.getString("deptno");
			String color = rs.getString("color");
			String name = rs.getString("name");
			
			//행마다 미리 준비된 곳에서 값만 계속 바뀜
			insertPstmt.setString(1,deptno);
			insertPstmt.setString(2,color);
			insertPstmt.setString(3,name);
			
			int result = insertPstmt.executeUpdate();
			System.out.println("insert된 행의 갯수: "+result);
		}
	} catch (ClassNotFoundException e) {
		rs.close();
		insertPstmt.close();
		selectPstmt.close();
		conn.close();
	}
}//main

---

3. 자원해제

앞에서 DB관련 처리 작업을 하면서 다음의 객체들을 사용했다.

• Connection : DB 연결 객체
• Statement 또는 PreparedStatement 객체 : SQL문 실행 객체
• ResultSet 객체 : select문 결과를 가지는 객체

이 객체들을 이용해 DB관련 처리 작업이 완료된 다음에는 사용했던 객체들을 메모리에서 해제해야 한다.

해제하는 순서는 최근에 사용했던 객체부터 거꾸로 올라가면서 해야한다. 

1. rs.close()
2. stmt.close() 또는 pstmt.close()
3. conn.close()

finally{

       if(rs != null) try {rs.close();}catch(SQLExeception e) {}

       if(stmt != null) try {stmt.close();}catch(SQLExeception e) {}

       if(pstmt != null) try {pstmt.close();}catch(SQLExeception e) {}

       if(conn != null) try {conn.close();}catch(SQLExeception e) {}

}

코드를 작성해보면 try~catch로 예외처리가 필요하므로 반드시 실행되어야하는 해제는 finally에서 해줘야 한다.