[Day1] JSP/JDBC 1 - 개요, jdbc driver, dto

[Day1] JSP/JDBC 1 [10/26]

1. JDBC

ㄱ. Java DataBase Connectivity

ㄴ. 자바와 DB 연동 및 작업(CRUD) 하는 자바 표준 [인터페이스]

ㄷ. DB 연동 기술

2. 그림 설명

3&4.

3. JDBC Driver를 관리하는 관리자 : JDBC DriverManager

4. JDBC Driver 4가지 종류

ㄱ. Type1 - ODBC Driver 브릿지 사용 연동 X

ㄴ. Type2 - Native Library C/C++ 언어 사용해서 라이브러리 연동 X

ㄷ. Type3 - 서버 미들웨어 X

ㄹ. Type4 - [Thin] 드라이버, 순수 자바 만들어졌고, O

DBMS 직접 연결, 현재 가장 많이 사용된다.

5. DBMS(오라클) 연결( Connection)하는 순서 (암기)

1) Class.forName() 메서드를 사용해서 JDBC 드라이버 로딩 // JDBC를 메모리상에 올린다는 의미

2) DriverManager 클래스 의 getConnection() 메서드를 사용해서 Connection 객체 생성

3) CRUD 필요한 작업

4) 연결종료 Connection 객체 닫기(Close)

실습

public static void main(String[] args) {
		// 1. JDBC 드라이버 로딩 - Class.forName()
        // oracle.jdbc.driver = 패키지
        // OracleDriver = 드라이버
		String className = "oracle.jdbc.driver.OracleDriver";
		try {
			// java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
			Class.forName(className);
			// 2. Connection 객체 생성 - DriverManager.getConnection()
			
			// 연결 문자열( connection string  )  = url, user, password
			String url = "jdbc:oracle:thin:@localhost:1521:xe";
			String user = "scott";
			String password = "tiger";
			Connection conn =  DriverManager.getConnection(url, user, password);
			
			// 3. CRUD
			System.out.println(  conn );
			System.out.println(  conn.isClosed() );  // false 
			
			// 4. 연결 종료  - Connection.close()
			if( !conn.isClosed()) conn.close();
			
		} catch (ClassNotFoundException e) {			
			e.printStackTrace();
		} catch (SQLException e) { 
			e.printStackTrace();
		}
		
		System.out.println(" END ");

	} // main

실습2

명령을 수행하는 객체

ㄱ. Statement : 기본객체 , createStatement() 메서드로 얻어온다.

ㄴ. PerparedStatement : ? 바인딩 변수 *** // 좋은코딩!!

ㄷ. CallableStatement : 저장 프로시저 사용

Statement stmt =  conn.createStatement();

10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

String sql = "SELECT * "
        + " FROM dept";
ResultSet  rs = stmt.executeQuery(sql);   // SELECT 결과

//stmt.executeUpdate(sql); // INSERT, UPDATE, DELETE

int deptno;
String dname, loc;

b = rs.next();
if( b ) {   // 값이 있는지 확인
  deptno =  rs.getInt("deptno");
  dname =  rs.getString("dname");
  loc =  rs.getString("loc");
  System.out.printf("%d\t%s\t%s\n", deptno, dname, loc );
}

while( rs.next() ) {
  deptno =  rs.getInt("deptno");
  dname =  rs.getString("dname");
  loc =  rs.getString("loc");
  System.out.printf("%d\t%s\t%s\n", deptno, dname, loc );
}

실습3

Class.forName(className);
Connection conn =  DriverManager.getConnection(url, user, password);
String sql = "SELECT * FROM emp";
Statement stmt = conn.createStatement();
ResultSet rs =  stmt.executeQuery(sql);
			
int  empno;
String ename;
String job;
// java.sql.Date hiredate;  이거 추천! 둘다 되긴함
String hiredate;
int mgr;
double sal;	
double comm;
int deptno;
			
while( rs.next() ) {
	empno =  rs.getInt("empno");
	ename  = rs.getString("ename");
	job  = rs.getString("job");
	hiredate  = rs.getString("hiredate");
	mgr =  rs.getInt("mgr");
	sal =  rs.getDouble("sal");
	comm = rs.getDouble("comm");
	deptno =  rs.getInt("deptno");
				
	System.out.printf("%d, %s, %s, %s, %d, %.2f, %.2f, %d\n"
    	, empno, ename, job,hiredate,mgr, sal, comm, deptno);	
} // while

실습4 : 입력받아서 조건절로 주기

System.out.print("> 부서 번호를 입력하세요 ? ");
int pdeptno;
Scanner scanner = new Scanner(System.in);
pdeptno = scanner.nextInt();
		

try {
	Class.forName(className);
	Connection conn =  DriverManager.getConnection(url, user, password);
	//String sql = "SELECT * "
	String sql = "SELECT empno, ename, job, TO_CHAR( hiredate, 'yyyy-mm-dd' ) hiredate, mgr, sal, comm, deptno "
			       + " FROM emp"
			       + " WHERE deptno = " + pdeptno 
			       + " ORDER BY hiredate ASC";
			
	Statement stmt = conn.createStatement();
	ResultSet rs =  stmt.executeQuery(sql);
	
	int  empno;
	String ename;
	String job;
	// java.sql.Date hiredate;
	String hiredate;
	int mgr;
	double sal;
	double comm;
	int deptno;
				
	if(  rs.next() ) {
		
		do {
			empno =  rs.getInt("empno");
			ename  = rs.getString("ename");
			job  = rs.getString("job");
			hiredate  = rs.getString("hiredate");
			mgr =  rs.getInt("mgr");
			sal =  rs.getDouble("sal");
			comm = rs.getDouble("comm");
			deptno =  rs.getInt("deptno");
			
			System.out.printf("%d, %s, %s, %s, %d, %.2f, %.2f, %d\n"
					, empno, ename, job
//					,hiredate.substring(0, 11)
					, hiredate
					,mgr, sal, comm, deptno);
		} while ( rs.next() );
	}else {
    	System.out.printf("%d번 번 사원은  존재하지 않습니다.\n", pdeptno);
} // if

출력날짜 수정

Date hiredate;
int mgr;
double sal;
double comm;
int deptno;
						
if(  rs.next() ) {
	do {
    	//rs.getXXX()
		hiredate  = rs.getDate("hiredate");

		System.out.printf("%d, %s, %s, %s, %d, %.2f, %.2f, %d\n"
				, empno, ename, job
//				,hiredate.substring(0, 11)
				, hiredate
				,mgr, sal, comm, deptno);

DBconn활용

package com.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

// 싱글톤 선언 - 복습
public class DBConn {

	// new DBConn() 객체 생성 X
	private DBConn() {}
	
	private static Connection connection = null;
	
	// 로컬DB , scott, tiger
	public static Connection getConnection() {
		if( connection == null ) {
			// 1. + 2.
			// 1.
			String className = "oracle.jdbc.driver.OracleDriver";
			String url = "jdbc:oracle:thin:@localhost:1521:xe";  // dbname:port:SID
			String user = "scott";
			String password = "tiger";
			 
			try {
				Class.forName(className);
				connection =  DriverManager.getConnection(url, user, password);
			} catch (ClassNotFoundException e) { 
				e.printStackTrace();
			} catch (SQLException e) { 
				e.printStackTrace();
			} // try
			
		} // if
		
		return connection;
	}
	
	// 오버라이딩==재정의함수/[오버로딩==중복함수]
	public static Connection getConnection(String url, String user, String password )  {
		if( connection == null ) {
			 
			String className = "oracle.jdbc.driver.OracleDriver";
			 
			try {
				Class.forName(className);
				connection =  DriverManager.getConnection(url, user, password);
			} catch (ClassNotFoundException e) { 
				e.printStackTrace();
			} catch (SQLException e) { 
				e.printStackTrace();
			} // try
			
		} // if
		
		return connection;
	}
	
	public static void close() {
		try {
			if( connection != null  && !connection.isClosed()) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		// ********
		connection = null;
	}

	
} // class

System.out.print("> 부서 번호를 입력하세요 ? ");
int pdeptno;
Scanner scanner = new Scanner(System.in);
pdeptno = scanner.nextInt();
		 
try {
			
	Connection conn =  DBConn.getConnection();
 
	String sql = "SELECT empno, ename, job,  hiredate, mgr, sal, comm, deptno "
			       + " FROM emp"
			       + " WHERE deptno = " + pdeptno 
			       + " ORDER BY hiredate ASC";
			
	Statement stmt = conn.createStatement();
	ResultSet rs =  stmt.executeQuery(sql);
			
	int  empno;
	String ename;
	String job;
		 
	Date hiredate;
	int mgr;
	double sal;
	double comm;
	int deptno;
						
	if(  rs.next() ) {
			
		do {
			//               rs.getXXX()
			empno =  rs.getInt("empno");
			ename  = rs.getString("ename");
			job  = rs.getString("job");
			hiredate  = rs.getDate("hiredate");
			mgr =  rs.getInt("mgr");
			sal =  rs.getDouble("sal");
			comm = rs.getDouble("comm");
			deptno =  rs.getInt("deptno");
			
			System.out.printf("%d, %s, %s, %s, %d, %.2f, %.2f, %d\n"
					, empno, ename, job
//					,hiredate.substring(0, 11)
					, hiredate
					,mgr, sal, comm, deptno);
		} while ( rs.next() );
	
	}else {
		System.out.printf("%d번 번 사원은  존재하지 않습니다.\n", pdeptno);
	} // if					 
			
	// 4.
	rs.close();
	stmt.close();
	
	// conn.close();
	DBConn.close();
} catch (SQLException e) { 
	e.printStackTrace();
}
	  
System.out.println("END");
		
} // main

Connection conn =  DBConn.getConnection();
DBConn.close();
// conn.close(); X -> connection = null;을 줘야하기때문에

Properties p = new Properties();
		
p.setProperty("className",  "oracle.jdbc.driver.OracleDriver");
p.setProperty("url",  "jdbc:oracle:thin:@localhost:1521:xe");
p.setProperty("user",  "scott");
p.setProperty("password",  "tiger");
		
String comments = "> JDBC Connection String <";
		
// System.getProperty("user.dir");
	
String fileName = ".\\src\\com\\util\\ConnectionString.properties";
FileWriter writer;
try {
	writer = new FileWriter(fileName);
	p.store(writer, comments);
} catch (IOException e) { 
	e.printStackTrace();
}
		
System.out.println("END");

String className , url , user , password;
		 
Properties p = new Properties();
		
String fileName = ".\\src\\com\\util\\ConnectionString.properties";
Reader reader;
try {
	reader = new FileReader(fileName);
	p.load(reader);
			
	className = p.getProperty("className");
	url = p.getProperty("url");
	user = p.getProperty("user");
	password = p.getProperty("password");
		
	System.out.println( className );
	System.out.println( url );
	System.out.println( user );
	System.out.println( password );

} catch (FileNotFoundException e) { 
	e.printStackTrace();
} catch (IOException e) { 
	e.printStackTrace();
}

DTO (data transfer object, 데이터 전송 객체)

-domain dto-

package domain;

public class DeptDTO {
	
	// field
	private int deptno;
	private String dname;
	private String loc;
	
	// constructor
	public DeptDTO() {}
	
	public DeptDTO(int deptno, String dname, String loc) {
		super();
		this.deptno = deptno;
		this.dname = dname;
		this.loc = loc;
	}
	
	// getter, setter
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	public String getDname() {
		return dname;
	}
	public void setDname(String dname) {
		this.dname = dname;
	}
	public String getLoc() {
		return loc;
	}
	public void setLoc(String loc) {
		this.loc = loc;
	}
	
	@Override
	public String toString() {
		// return "[deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";
		return String.format("%d\t%s\t%s", deptno, dname, loc );
	}
	
} // class

-java파일-

package days01;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import com.util.DBConn;

import domain.DeptDTO;

/**
 * @author k≡n¡k
 * @date 2022. 10. 26. - 오후 4:17:37
 * @subject JDBC 수업 1 일차 
 * @content  dept 테이블 부서 정보를 읽어와서 DTO 객체 저장 -> ArrayList 저장 -> 출력
 *         -  데이터 전송 객체([D]ata [t]ransfer [o]bject)
 *         -  M - dto - V - dto - C
 *         -  DAO ( Data Access Object) : DB i,u,d,s 실제 객체 // db연동객체
 */
public class Ex07 {

	public static void main(String[] args) {
		
		// 1.  부서정보를 저장할 DTO 선언 -    domain 패키지
		// 2. DAO                                  -    persistence 패키지
		
		ArrayList<DeptDTO> deptList = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String sql =    " SELECT deptno, dname, loc "
        		            + " FROM dept";
		
		conn = DBConn.getConnection();
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			if ( rs.next() ) {
				deptList = new ArrayList<DeptDTO>();
				do {
					/*
					 * rs.getInt("deptno"); 
					 * rs.getString("dname"); 
					 * rs.getString("loc");
					 */
					int deptno = rs.getInt( 1 );
					String dname = rs.getString( 2 );
					String loc = rs.getString( 3 );
					
					/*
					DeptDTO dto = new DeptDTO();
					dto.setDeptno(deptno);
					dto.setDname(dname);
					dto.setLoc(loc);
					
					deptList.add(dto);
					*/
					
                    //생성자
					deptList.add( new DeptDTO(deptno, dname, loc) );
					
				} while ( rs.next() );
			} else {
				System.out.println("부서가 존재하지 않습니다.");
			} // if
			
			// 부서 정보를 출력하는 메서드 
			// 매개변수 : ArrayList 
			printDept(  deptList );
		} catch (SQLException e) { 
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				rs.close();
				DBConn.close();
			} catch (SQLException e) { 
				e.printStackTrace();
			}			
		} // try
		 
		System.out.println( "END" );
		
	} // main

	private static void printDept(ArrayList<DeptDTO> deptList) {
		
		Iterator<DeptDTO> ir = deptList.iterator();
		
		while (ir.hasNext()) {
			DeptDTO dto =  ir.next();
			System.out.println( dto );
		} // while
		
	} // printDept

} // class

저작자표시

'👨‍💻 Web Development > JDBC | JSP | Servlet' 카테고리의 다른 글

[Day1] JSP/Servlet 1 - 개요 (0)	2022.12.06
[Day5] JSP/JDBC 5 - transaction 처리, callable statement, reflection (0)	2022.11.02
[Day4] JSP/JDBC 4 - Board (0)	2022.11.01
[Day3] JSP/JDBC 3 - 사용자 변경, 여러출력방식, linkedhashmap사용 DTO 저장, MVC, JUnit (0)	2022.10.31
[Day2] JSP/JDBC 2 - 동적쿼리, PreparedStatement (0)	2022.10.31

[Day1] JSP/JDBC 1 - 개요, jdbc driver, dto

[Day1] JSP/JDBC 1 [10/26]

'👨‍💻 Web Development > JDBC | JSP | Servlet' 카테고리의 다른 글

전체 카테고리

블로그 인기글

티스토리툴바

[Day1] JSP/JDBC 1 [10/26]

'👨‍💻 Web Development > JDBC | JSP | Servlet' 카테고리의 다른 글

전체 카테고리

최근 글

최근댓글

블로그 인기글

티스토리툴바