Day32

1.JDBCを利用してMVCモードを理解・連動することができる.
2.VO、Entityなどの値伝達を明確に理解する.
3.プログラム開発により、データベーステーブル内のオブジェクトの連動や利用が可能となる.

JDBC DriverとOracleデータベース構造

jdbc-jdbc符号化プロセス

登録driver->Dbms接続->statement生成->sql送信->결과受信->クローズ(クローズ)
1)ドライブ登録
DriverManager

Class.forName("oracle.jdbc.driver.OracleDriver"); //오라클 드라이버

  etc)
  Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); //sql드라이버
  Class.forName("org.git.mn.mysql.Driver");

データベース接続に必要なドライバを分類します.forNameとしてインポートします.
2)dmbs接続
ドライバから取得したConnection instance

  public static Connection 
  getConnection( String url, String user, String password )throws SQLException

예시)
Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:XE","scott","TIGER");

3)生成文Connection instanceからStatement instanceを取得
DriverManager -> Connection -> Statement

  Statement stmt = conn.createStatement();

ConnectionでのStatementの作成
4)sql転送Statement method()を使用してSQLを実行
5)結果の取得
実行後の結果は、ResultSet(select)またはint형 변수(DML)で処理される.
DriverManager -> Connection -> Statement -> ResultSet

// case01) ResultSet(select)
 
 String query = "SELECT ID, LAST_NAME FROM EMP";
 ResultSet rs = stmt.executeQuery(query) // ResultSet으로 처리
 while(rs.next){
        System.out.println(rs.getString("ID") + "/t" + rs.getString(2));
}

// case02 ) DML
 
 String query = "update emp set last_name = 'kim' where id = '10000'";
 int res = stmt.executeUpdate(query)  //int형 DML로 받아서 처리
 

6)クローズ

// case 01 ) ResultSet(select)

  rs.close()
  stmt.close()
  conn.close();

// case 02 ) DML

  stmt.close()
  conn.close(); 

MVCモードの構成と説明

package and class and interface

1)common : JDBCTempl = connection, close, rollback, commit
2)com.vo:MyEmpVo=従業員番号、氏名、職業、給与
3)com.cv:ConstadView=ページ移動、検証、ビュー:main()
4)com.dao:(インタフェース)MyEmpDao/MyEmpDaoImple=DMLバインド
5)com.Biz:MyEmpBiz=論理(daoなどの演算を呼び出す)ss
ストリームの順序:3<->5<->4<->1

💡操作順序
[vo作成->controller and view->biz->dao->db]
com.vo.MyEmpVo -> com.cv.ContandView -> com.biz.MyEmpBiz -> com.dao.My.dao
-> common.JDBCCCTemplate->db連動

VOクラス

package com.vo;

public class MyEmpVO {
 private int empno;
 private String ename;
 private String job;
 private double sal;
public MyEmpVO() {
	super();
	// TODO Auto-generated constructor stub
}
public MyEmpVO(int empno, String ename, String job, double sal) {
	super();
	this.empno = empno;
	this.ename = ename;
	this.job = job;
	this.sal = sal;
}
public int getEmpno() {
	return empno;
}
public void setEmpno(int empno) {
	this.empno = empno;
}
public String getEname() {
	return ename;
}
public void setEname(String ename) {
	this.ename = ename;
}
public String getJob() {
	return job;
}
public void setJob(String job) {
	this.job = job;
}
public double getSal() {
	return sal;
}
public void setSal(double sal) {
	this.sal = sal;
}
 
}

dbテーブルをVOと考えれば簡単です.
defaultジェネレータ、field、getter&setterを作成します.
Controller And View

package com.cv;

import java.util.*;
import com.biz.*;
import com.vo.*;

public class ContandVIew {
	static Scanner sc = new Scanner(System.in);
	public static int View_Menew(){
		
		StringBuilder sb = new StringBuilder();
		sb.append(" 메뉴  \n");
		sb.append("1.전체 출력 \n");
		sb.append("2.입       력\n");
		sb.append("3.수       정\n");
		sb.append("4.삭       제\n");
		sb.append("5.검       색\n");
		sb.append("6.종 료 \n");
		System.out.println(sb.toString());
		System.out.print("Input no : ");
		int no = sc.nextInt();
		return no;
	}
	public static void main(String[] args) {
		//View -> controller -> biz -> dao ->db
		int no=0;
		MyEmpVO vo = null; //null = 필요할 때 마다 생성할 수 있는 유형
		List<MyEmpVO> all = null;
		
		do {
			no = View_Menew();
			switch (no) {
		case 1: System.out.println(" 전체 출력 "); // getSelectAll()
				all = new MyEmpBiz().getSelectAll();
				
				for(MyEmpVO res : all) {
					String str = String.format("%10d %10s %10s %5.1f \n",
							res.getEmpno(),res.getEname(),res.getJob(),res.getSal());
					System.out.println(str);
				}
			break;
			
		case 2: System.out.println(" 입력 페이지 "); 
			System.out.print("input empno : ");
			int empno = sc.nextInt();
			sc.nextLine();
			System.out.print("input ename : ");
			String ename = sc.nextLine();
			System.out.print("input job : ");
			String job = sc.nextLine();
			System.out.print("input sal : ");
			double sal = sc.nextDouble();
			//객체에 담기
			vo = new MyEmpVO(empno, ename, job, sal);
			
			//biz로 보내서 결과를 리턴 받는다.
			int res = new MyEmpBiz().my_emp_insert(vo);
			if (res > 0) {
				System.out.println("입력 성공이야.");
			}
			break;
			
		case 3: System.out.println(" 수정 페이지 ");
			System.out.print("input empno : ");
			empno = sc.nextInt();
			sc.nextLine();
			System.out.print("input ename : ");
		    ename = sc.nextLine();
			System.out.print("input job : ");
			job = sc.nextLine();
			System.out.print("input sal : ");
			sal = sc.nextDouble();
			
			vo = new MyEmpVO(empno, null, job, sal);
		
			res = new MyEmpBiz().my_emp_insert(vo);
			if (res > 0) {
				System.out.println("입력 성공이야.");
			}
			break;
			
		case 4: System.out.println(" 삭제 페이지 ");
			System.out.print("input empno : ");
			empno = sc.nextInt();
			
			vo = new MyEmpVO();
			vo.setEmpno(empno);
			res = new MyEmpBiz().my_emp_insert(vo);
			if (res > 0) {
				System.out.println("입력 성공이야.");
			}
			break;
			
		case 5: 
			System.out.println(" 검색 페이지 ");
			System.out.print("input ename : ");
		    ename = sc.next();
		    
		    vo = new MyEmpVO();
		    vo.setEname(ename);
		    
		    all = new MyEmpBiz().getFindAll(vo);
			for(MyEmpVO r : all) {
				String str = String.format("%10d %10s %10s %5.1f \n",
						r.getEmpno(),r.getEname(),r.getJob(),r.getSal());
				System.out.println(str);
			}
			break;
			
		case 6:
				System.out.println("종료");
				System.exit(0);
			}
		}while (no !=6);
	}
}

クラスには、中間ロールcontrollerとビューのフォーマットが含まれます.
Biz

package com.biz;

import java.sql.Connection;
import java.util.List;

import com.dao.MyEmpDao;
import com.dao.MyEmpDaoImple;
import com.vo.MyEmpVO;
import static common.JDBCTemplate.*;
// 컨트롤러로 부터 1) 검증된 데이터를 받아서 dao로 바로 연결할 건지,
// 2)계산된 클래스를 호출한 후  계산이 끝난 후의 데이터를 dao로 연결할 건지
// 3)아니면 계산된 클래스를 호출한 후 계산이 끝난 후에 컨트롤러에게 리턴할 것인지를 결정한다.
// 만일 1,2 번의 작업을 하게 되면 데이터베이스 연동하는 dao 즉dml,ddl,tcl을 구현하는 클래스의
// 메소드를 호출하기 때문에 connect, close를 구현한다.

public class MyEmpBiz {

	public List<MyEmpVO> getSelectAll() {
		Connection con = getConnection();
		List<MyEmpVO> all = new MyEmpDaoImple(con).getSelectAll();
		Close(con);
		return all;
	}

	public int my_emp_insert(MyEmpVO vo) {
		Connection con = getConnection();
		int r = new MyEmpDaoImple(con).my_emp_insert(vo);
		Close(con);
		return r;
	}
	public int my_emp_update(MyEmpVO vo) {
		Connection con = getConnection();
		int r = new MyEmpDaoImple(con).my_emp_update(vo);
		Close(con);
		return r;
	}
	public int my_emp_delete(MyEmpVO vo) {
		Connection con = getConnection();
		int r = new MyEmpDaoImple(con).my_emp_delete(vo);
		Close(con);
		return r;
	}
	public List<MyEmpVO> getFindAll(MyEmpVO vo) {
		Connection con = getConnection();
		List<MyEmpVO> all = new MyEmpDaoImple(con).getFindAll(vo);
		Close(con);
		return all;
	}
	

}

ControllerとDaoに情報を提供(?)そうだと思って
Dao

package com.dao;

//SQL 쿼리문 사용 , 추상클래스, 상수
public interface MyEmpDao  {
public static final String my_emp_selectall = "select * from myemp";
//public static final을 생략하여 사용가능하다.
String my_emp_insert = "insert into myemp values(?,?,?,?)";
//String my_emp_update = "update myemp set job=? ,sal=? where empno=?";
String  my_emp_update = "{call GET_UPDATE(?,?,?)}";
String my_emp_delete = "delete from myemp where empno=?";
String my_emp_find = "select * from myemp where ename=?";
}

クエリー文をDaoImpleのインタフェースにインポートする
DaoImple

package com.dao;
import com.biz.*;
import java.util.*;
import java.sql.*;
import com.vo.*;
import static common.JDBCTemplate.*;

//   DML  or  DDL  or  TCL

// view <-> controller <-> biz <-> dao
public class MyEmpDaoImple implements MyEmpDao {
	
	private Connection conn;
	public MyEmpDaoImple(Connection conn) {
		this.conn = conn;
	}
	public List<MyEmpVO> getSelectAll(){
		List<MyEmpVO> all = new ArrayList<>();
		MyEmpVO vo = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(my_emp_selectall);
			while(rs.next()) {
				vo= new MyEmpVO(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDouble(4));
				all.add(vo);
			}
		} catch (SQLException e) {
		System.out.println("select 쿼리 확인~");
		
		}finally {
			Close(rs);
			Close(stmt);
		}
		return all;
	}
	public int my_emp_insert(MyEmpVO vo) {
		int r = 0;
		PreparedStatement pstm = null;
		try {
			pstm = conn.prepareStatement(my_emp_insert);
			pstm.setInt(1, vo.getEmpno());
			pstm.setString(2, vo.getEname());
			pstm.setString(3, vo.getJob());
			pstm.setDouble(4, vo.getSal());
			
			r = pstm.executeUpdate();
			if(r > 0) {
				Commit(conn);
			}
		}catch (SQLException se) {
			RollBack(conn); // catch에 rollback이 반드시 와야한다.
		}finally {
			Close(pstm); // 반드시 명령문을 닫아줘야한다.
		}
		
		return r;
	}
	public int my_emp_update(MyEmpVO vo) {
		int r = 0;
		CallableStatement pstm = null;
		try {
			pstm = conn.prepareCall(my_emp_update);
			pstm.setString(1, vo.getJob());
			pstm.setDouble(2, vo.getSal());
			pstm.setInt(3, vo.getEmpno());;
			r = pstm.executeUpdate();
			System.out.println(r);
			if(r > 0) {
				Commit(conn);
			}
		}catch (SQLException se) {
			RollBack(conn); // catch에 rollback이 반드시 와야한다.
		}finally {
			Close(pstm); // 반드시 명령문을 닫아줘야한다.
		}
		
		return r;
}
	public int my_emp_delete(MyEmpVO vo) {
		int r = 0;
		PreparedStatement pstm = null;
		try {
			pstm = conn.prepareStatement(my_emp_delete);
			pstm.setInt(1, vo.getEmpno());
			r = pstm.executeUpdate();
			if(r > 0) {
				Commit(conn);
			}
		}catch (SQLException se) {
			RollBack(conn); // catch에 rollback이 반드시 와야한다.
		}finally {
			Close(pstm); // 반드시 명령문을 닫아줘야한다.
		}
		
		return r;
}
	public List<MyEmpVO> getFindAll(MyEmpVO empvo){
		List<MyEmpVO> all = new ArrayList<>();
		MyEmpVO vo = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.prepareStatement(my_emp_find);
			stmt.setString(1,empvo.getEname());
			rs = stmt.executeQuery(my_emp_find);
			while(rs.next()) {
				vo= new MyEmpVO(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getDouble(4));
				all.add(vo);
			}
		} catch (SQLException e) {
		System.out.println("select 쿼리 확인~");
		
		}finally {
			Close(rs);
			Close(stmt);
		}
		return all;
	}
}

Daoの継承とバインド
JDBCTemplate

package common;

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

//DB Connection을 리턴 받고 반환하는 등의 기본적인 기능들을 모아둔 Util 클래스

public class JDBCTemplate {
	// 1.Connection
	public static Connection getConnection() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		Properties pro = new Properties();
		pro.put("user", "scott");
		pro.put("password", "TIGER");
		Connection conn = null;

		try {
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:Xe", pro);
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	// 1-2. DB와 Connet 되었는지 유무를 리턴

	public static boolean isConnected(Connection conn) {
		boolean validConnection = true;
		try {
			if (conn == null || conn.isClosed()) {
				validConnection = false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return validConnection;
	}

	// 2.Close _Connection
	public static void Close(Connection conn) {
		try {
			if (isConnected(conn)) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	// 3.Close_statement
	public static void Close(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 4.Close_ResultSet
	public static void Close(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 5.RollBack() 트랜잭션 rollback
	public static void RollBack(Connection conn) {
		if (isConnected(conn)) {
			try {
				conn.rollback();
				System.out.println("[JDBCTemplate.Rollback] : DB Successfully Rollbacked!"); // 테스트용 이런식으로도 사용한다~
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 6.Commit()
	public static void Commit(Connection conn) {
		if (isConnected(conn)) {
			try {
				conn.commit();
				System.out.println("[JDBCTemplate.Commit] : DB Successfully Committed!");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

Connection、Close、RollBack、Commitによるdb接続の鍵