[데이터베이스] jdbc 입문

* TCP/IP 포트번호를 매번 확인해서 코드 수정하기

 

# 데이터베이스 접속

접속은 DriverManager 클레스(java.sql.*)의 getConnection 메서드를 사용해 열 수 있다.

# SQL구문 전달

데이터베이스 접속이 열리면 프로그램은 접속을 사용해, 실행을 위해 SQL구문을 데이터베이스 시스템으로 보낼 수 있다. -> statement 클래스의 인스턴스를 통해 수행된다.

conn 접속에 대해 Statement 접근자를 생성.

구문을 실행하기 위해 SQL 구문이 질의(결과 집합을 반환하는)냐 / update, insert, delete, create table 등과 같은 비질의 구문이냐에 따라 executeQuery 메서드나 executeUpdate 메서드를 호출하게 된다.

# 질의 결과 검색

ResultSet 객체 rset에 그 결과들의 투플의 집합을 구하고, 결과 집합에서 한번에 한 투플씩 받아올 수 있다.

next 메서드는 결과 집합에서 받아오지 않는 투플이 남아있는지 아닌지 검사한다. 만약 있으면 받아온다.

(next 의 반환값은 Boolean값)

받아온 투플의 속성은 get으로 시작하는 메서드들을 사용해 검색된다.

# get ~ 메서드 - 문자열로 명시된 속성 이름 / 속성의 위치

- getString : 자바의 String 객체로 검색가능     rest.getString("dept_name")

- getFloat : 해당 번호의 Column(열)    rset.getFloat(2);  --> 2번째 열 값

# 종료

stmt.close();

conn.close();

import java.sql.*;
import java.sql.SQLException;

public class test_jdbc {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
            Connection conn = DriverManager.getConnection(connectionUrl); //접속
            Statement stmt = conn.createStatement(); // 접근자
            System.out.println("MS-SQL 서버 접속에 성공하였습니다.1");
            ResultSet rs = stmt.executeQuery("SELECT * FROM instructor");
            while( rs.next() ) {
                   String field1 = rs.getString("name");
                   String field2 = rs.getString("dept_name");
                   String field3 = rs.getString(3);
                   System.out.print(field1 + "\t");
                   System.out.print(field2 + "\t");
                   System.out.println(field3 );
                  }
            rs.close();
            stmt.close();   
            conn.close();
        } catch (ClassNotFoundException sqle) {
        	System.out.println("SQLException : " + sqle);
        	}
   }
}

String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
  - localhost : TCP/IP 포트 번호
  - databse : 특정 데이터베이스 선택
  - intergratedSecurity : 내부 컴퓨터에 연결할 것인가 (문자열로된 패스워드)

 

Connection conn = DriverManager.getConnection(connectionUrl);
  - DriverManager메소드를 이용해 getConnection해달라고한다 = 해당 url을 연결하라. conn객체 생성

 

Statement stmt = conn.createStatement();
  - conn객체를 이용해 stmt객체 생성하기  --> 접속 성공

 

ResultSet rs = stmt.executeQuery("SELECT * FROM instructor");
  - SQL 문장 실행

 

- 교수들의 학과,연봉 출력하고 평균 연봉 구하기

// 모든 교수들의 연봉 구하기
import java.sql.*;
import java.sql.SQLException;

public class sample2 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement stmt = conn.createStatement();
            System.out.println("MS-SQL 서버 접속에 성공하였습니다.1");
            ResultSet rs = stmt.executeQuery("SELECT * FROM instructor");
            double sum=0;
            int i=0;
            while( rs.next() ) {
                   String field1 = rs.getString("name");
                   String field2 = rs.getString("dept_name");
                   String field3 = rs.getString(4);
                   sum =sum+ Double.parseDouble(rs.getString(4));
                   System.out.print(field1 + "\t");
                   System.out.print(field2 + "\t");
                   System.out.println(field3);
                   i=i+1;
                  }
            System.out.println("교수들의 평균 연봉은 "+sum/i);
            rs.close();
            stmt.close();   
            conn.close();
        } catch (ClassNotFoundException sqle) {
        	System.out.println("SQLException : " + sqle);
        	}
   }
}

- 학과 별 교수들의 평균 급여 구하기

import java.sql.*;
import java.sql.SQLException;

public class sample3 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement stmt = conn.createStatement();
            System.out.println("MS-SQL 서버 접속에 성공하였습니다.");
            String d_name; 
            float sal; 
            ResultSet rset= stmt.executeQuery("select dept_name, avg(salary) "
            					+ "from instructor group by dept_name"); 
            while (rset.next()) { 
            	d_name= rset.getString("dept_name"); 
            	sal= rset.getFloat(2); 
            	System.out.println(d_name+ " " + sal); 
            }
            rset.close();
            stmt.close();   
            conn.close();
        } catch (ClassNotFoundException sqle) {
        	System.out.println("SQLException : " + sqle);
        	}
	}
}

- instructor 테이블에 NULL값이 있는지 확인하기

import java.sql.*;
import java.sql.SQLException;

public class is_in_null {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement stmt = conn.createStatement();
            System.out.println("MS-SQL 서버 접속에 성공하였습니다.1");
            
            ResultSet rs= stmt.executeQuery("select * from instructor"); 
            while (rs.next()) { 
            	 String field1 = rs.getString("name");
                 String field2 = rs.getString("dept_name");
                 Float field3 = rs.getFloat("salary");
            
                 if (rs.wasNull()) {
                	 System.out.println("Got null value");
                	 break;
                 }
                 System.out.print(field1 + "\t");
                 System.out.print(field2 + "\t");
                 System.out.println(field3);
            }
            rs.close();
            stmt.close();   
            conn.close();
        } catch (ClassNotFoundException sqle) {
        	System.out.println("SQLException : " + sqle);
        }
    }
}

re.wasNull로 null이 있는지 확인하기. 여기 데이터베이스에는 없어서 break없이 쭉 출력

 

- instructor 테이블에 새로운 교수(투플) 삽입 (# 준비된 구문)

import java.sql.*;
import java.sql.SQLException;

public class sample4_insert {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement stmt = conn.createStatement();
            System.out.println("MS-SQL 서버 접속에 성공하였습니다.");
            
            //stmt.executeUpdate("insert into instructor values('77987', 'Kim', 'Physics', 98000)"); 하드코딩이므로
            //preparedstatement 방식을 해야함
            
            PreparedStatement pStmt = conn.prepareStatement(
                    "insert into instructor values(?,?,?,?)");
			pStmt.setString(1, "88877");
			pStmt.setString(2, "Perry");
			pStmt.setString(3, "Finance");
			pStmt.setInt(4, 125000);
			//values(?,?,?,?) 에 순서대로 값을 넣어주고
			pStmt.executeUpdate();
			//값 업데이트
			pStmt.setString(1, "88878");
			pStmt.executeUpdate();
            stmt.close();   
            conn.close();
        } catch (ClassNotFoundException sqle) {
        	System.out.println("SQLException : " + sqle);
        }
    }
}

# Prepared statement 준비된 구문

바로 대입하지 않고, 변수에 따로 담아준 다음 변수에 담아주기.

테이블이 생성될 때 넣어주는 자료형과 칼럼 순서를 칼럼별로 넣어준다.

"?"로 먼저 준비시키고 실제 값이 나중에 제공되도록 명시한다.

PreparedStatement pStmt = conn.prepareStatement(
                            "insert into instructor values(?,?,?,?)");
pStmt.setString(1, "88877");
pStmt.setString(2, "Perry");
pStmt.setString(3, "Finance");
pStmt.setInt(4, 125000);
// values(?,?,?,?) 에 순서대로 값을 넣어주고
pStmt.executeUpdate();
// 값 업데이트
pStmt.setString(1, "88878");
pStmt.executeUpdate();

** 하드코딩 : stmt.executeUpdate("insert into instructor values('77987', 'Kim', 'Physics', 98000)"

# Hard coding 하드코딩

변경할 수 없는 식의 코딩

하드코딩을 피하기 위해서는 prepared statement를 사용한다.

 

삽입직후 ssms에서 테이블 확인해보니 삽입이 잘 이루어짐

한 번 더 실행하면 에러가 발생. 그 이유는 77987이 primary key이므로 중복 삽입 불가능하다.*** 중요 ****

---

# Metadata Features 메타데이터 특성

-> 열의 개수, 명시된 열의 이름 혹은 명시된 열의 타입과 같은 메타데이터 정보 찾을 수 있음

import java.sql.*;
import java.sql.SQLException;

public class sample5{

   public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement stmt = conn.createStatement();
            ResultSet rs= stmt.executeQuery("select * from instructor where id > 70000"); 
            ResultSetMetaData rsmd= rs.getMetaData(); 
            for(int i= 1; i<= rsmd.getColumnCount(); i++) { 
            // getColumnCount : 릴레이션의 열의 수를 반환한다.
            	System.out.print(rsmd.getColumnName(i) + "\t"); // 열의 이름
            	System.out.println(rsmd.getColumnTypeName(i));  // 열의 속성
            } 

            conn.close();
        } catch (ClassNotFoundException sqle) {
        	System.out.println("SQLException : " + sqle);
        }
   }

}

// ResultSetMetaData 객체를 반환하는 getMetaData 메서드
ResultSetMetaData rsmd= rs.getMetaData();
for(int i= 1; i<= rsmd.getColumnCount(); i++) {
     System.out.print(rsmd.getColumnName(i) + "\t");
     System.out.println(rsmd.getColumnTypeName(i));
}

rs에게 metadata의 정보를 가져다달라고 부탁한 후 rsmd에 대입한다.
rsmd객체를 반복문을 이용해 테이블의 칼람별 타입을 출력한다.
이를 통해 출력할때 getFloat을 할지 getString으로 할지 알게 되는 것.

- metadata 

import java.sql.*;
import java.sql.SQLException;

public class sample6{

   public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:49684;database=largeDB;integratedSecurity=true";
            Connection conn = DriverManager.getConnection(connectionUrl);
            DatabaseMetaData dbmd = conn.getMetaData(); 
            ResultSet rs = dbmd.getColumns(null, null, "department", "%"); 
            while(rs.next()) { 
            	System.out.print(rs.getString("COLUMN_NAME") + "\t"); 
            	System.out.println(rs.getString("TYPE_NAME")); 
            } 
            conn.close();
        } catch (ClassNotFoundException sqle) {
        	System.out.println("SQLException : " + sqle);
        }
   }

}