TIL 2023.01.10 JDBC - MySQL

JDBC (Java Database Connectivity)

1. mysql Connector 다운로드

 

2. JAVA 프로젝트에서 DB Connection을 하기 위한 Connector 적용 방법

프로젝트 선택 후 Properties - Java Bulid Path - Libraries - Add External JARs... 에서 다운 받은 jar파일 추가 

3. eclipse를 이용한 DB Connection Test

eclipse - new Connection profile로 DB를 등록해두게 되면 eclipse에서 DB 와 Connection 되어 Query문을 입력할 수 있게 된다.

 

 

Perspective - DataBase Development추가

 

환경변수 이용

ID, Password 같은 민감한 정보들이 코드에 그대로 노출 되기 때문에
환경변수를 불러오는 System.getenv("USER_ID") 를 이용해서 DB 커넥션할 때의 정보를 숨겨서 가져 올 수 있다.

Map<String, String> env = System.getenv();
String user = env.get("local.mysql.user");
String password = env.get("local.mysql.password");
  
Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);

JAVA DB Connection

데이터 입력, 수정, 삭제 구현

public class Ex02 {

	public static void main(String[] args) {
		Scanner sc = new Scanner(System.in);
		String url = "jdbc:mysql://localhost:3306/lecture";
		Map<String, String> env = System.getenv();
		Properties props = new Properties();
		props.setProperty("user", env.get("local.mysql.user"));
		props.setProperty("password", env.get("local.mysql.password"));

		while (true) {
			System.out.print("1.emp 2.dept 0.exit >");
			int input = Integer.parseInt(sc.nextLine());

			if (input == 0)
				break;
			// emp
			if (input == 1) {
				System.out.print("1.list 2.insert 3.update(add deptno) 4.delete >");
				int input2 = Integer.parseInt(sc.nextLine());
				// list
				if (input2 == 1) {
					String sql = "select * from emp";
					Connection conn = null;
					Statement stmt = null;
					ResultSet rs = null;
					try {
						Class.forName("com.mysql.cj.jdbc.Driver");
						conn = DriverManager.getConnection(url, props);
						stmt = conn.createStatement();
						rs = stmt.executeQuery(sql);
						System.out.println("EMPNO\tENAME\tJOB\tMGR\tHIREDATE\tSAL\tCOMM\tDEPTNO");
						while (rs.next()) {
							System.out.print(rs.getObject(1) + "\t");
							System.out.print(rs.getObject(2) + "\t");
							System.out.print(rs.getObject(3) + "\t");
							System.out.print(rs.getObject(4) + "\t");
							System.out.print(rs.getObject(5) + "\t");
							System.out.print(rs.getObject(6) + "\t");
							System.out.print(rs.getObject(7) + "\t");
							System.out.println(rs.getObject(8));
						}

					} catch (SQLException e) {
						e.printStackTrace();
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
					}
				}
				// insert
				if (input2 == 2) {
//				EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO
					System.out.print("사번>");
					int empno = Integer.parseInt(sc.nextLine());
					System.out.print("이름>");
					String name = sc.nextLine();
					System.out.print("연봉>");
					int sal = Integer.parseInt(sc.nextLine());
					System.out.print("커미션>");
					int comm = Integer.parseInt(sc.nextLine());

					String sql = "insert into emp (empno, ename, hiredate, sal, comm) " + "values(" + empno + ",'"
							+ name + "',now()," + sal + "," + comm + ")";
					Connection conn = null;
					Statement stmt = null;
					try {
						Class.forName("com.mysql.cj.jdbc.Driver");
						conn = DriverManager.getConnection(url, props);
						stmt = conn.createStatement();
						stmt.execute(sql);

					} catch (SQLException e) {
						e.printStackTrace();
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
					}
				}
				// update
				if (input2 == 3) {
					String sql = "select deptno from dept";
					Connection conn = null;
					Statement stmt = null;
					ResultSet rs = null;
					try {
						Class.forName("com.mysql.cj.jdbc.Driver");
						conn = DriverManager.getConnection(url, props);
						stmt = conn.createStatement();
						rs = stmt.executeQuery(sql);
						System.out.println("지정할 수 있는 deptno");
						while (rs.next()) {
							System.out.println(rs.getObject(1));
						}

					} catch (SQLException e) {
						e.printStackTrace();
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
					}
					System.out.print("업데이트 할 사번>");
					int empno = Integer.parseInt(sc.nextLine());
					System.out.print("등록할 deptno>");
					int deptno = Integer.parseInt(sc.nextLine());
					sql = "update emp set deptno=" + deptno + " where empno=" + empno;

					try {
						Class.forName("com.mysql.cj.jdbc.Driver");
						conn = DriverManager.getConnection(url, props);
						stmt = conn.createStatement();
						stmt.executeUpdate(sql);

					} catch (SQLException e) {
						e.printStackTrace();
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
					}
				}
				// delete
				if (input2 == 4) {
					System.out.print("삭제할 사번>");
					int empno = Integer.parseInt(sc.nextLine());
					String sql = "delete from emp where empno=" + empno;
					Connection conn = null;
					Statement stmt = null;
					try {
						Class.forName("com.mysql.cj.jdbc.Driver");
						conn = DriverManager.getConnection(url, props);
						stmt = conn.createStatement();
						stmt.executeUpdate(sql);

					} catch (SQLException e) {
						e.printStackTrace();
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
					}

				}

			}
			// dept
			if (input == 2) {
				System.out.println("1.list 2.insert 3.delete");
				int input2 = Integer.parseInt(sc.nextLine());
				// list
				if (input2 == 1) {
					String sql = "select * from dept";
					Connection conn = null;
					Statement stmt = null;
					ResultSet rs = null;
					try {
						Class.forName("com.mysql.cj.jdbc.Driver");
						conn = DriverManager.getConnection(url, props);
						stmt = conn.createStatement();
						rs = stmt.executeQuery(sql);
						System.out.println("DEPTNO\tDNAME\tLOC");
						while (rs.next()) {
							System.out.print(rs.getObject(1) + "\t");
							System.out.print(rs.getObject(2) + "\t");
							System.out.println(rs.getObject(3));
						}

					} catch (SQLException e) {
						e.printStackTrace();
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
					}
				}
				// insert
				if (input2 == 2) {
					System.out.print("부서번호>");
					int deptno = Integer.parseInt(sc.nextLine());
					System.out.print("부서 이름>");
					String dname = sc.nextLine();
					System.out.print("부서 위치>");
					String loc = sc.nextLine();

					String sql = "insert into dept (deptno, dname, loc) " + "values(" + deptno + ",'" + dname + "','"
							+ loc + "')";
					Connection conn = null;
					Statement stmt = null;
					try {
						Class.forName("com.mysql.cj.jdbc.Driver");
						conn = DriverManager.getConnection(url, props);
						stmt = conn.createStatement();
						stmt.execute(sql);

					} catch (SQLException e) {
						e.printStackTrace();
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
					}
				}
				// delete
				if (input2 == 3) {
					System.out.print("삭제할 부서번호>");
					int deptno = Integer.parseInt(sc.nextLine());
					String sql = "delete from dept where deptno=" + deptno;
					Connection conn = null;
					Statement stmt = null;
					try {
						Class.forName("com.mysql.cj.jdbc.Driver");
						conn = DriverManager.getConnection(url, props);
						stmt = conn.createStatement();
						stmt.executeUpdate(sql);

					} catch (SQLException e) {
						e.printStackTrace();
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
					}
				}
			}

		}
	}

}