[DB] JDBC 테스트

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class JDBCBasic {

	private static String driver = "com.mysql.cj.jdbc.Driver";
	private static String url = "jdbc:mysql://localhost:3306/#DB명 "; // jdbc:dbportocol://host:port/database이름"
	private static String user = "# 이름 ";
	private static String password = "# 비번 ";
	private static ArrayList<Department> deptList = new ArrayList<>();

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

		// 1.Driver Load - Class.forName("MySQL Driver이름"); //com.mysql.jdbc.Drive
		Class.forName(driver);

		// 2.DB연결 - Connection conn = DriverMager.getConnection(url, id, password);
		Connection conn = DriverManager.getConnection(url, user, password);

		// 3.Query 실행 - Statement stmt = conn.createStatement(); // Query실행 시 sql
		// setting
		// PreparedStatement pstmt = conn.prepareStatement(sql); // 객체 생성시 sql setting -> data setting -> 쿼리 실행 
		// -> data setting ->
		// execute()-DDL, executeUpdate()-INSERT, UPDATE, DELETE, executeQuery()-SELECT
		String deptName = "Administrator";
		int deptId = 10;
		// String sqlUpdate = "UPDATE departments SET department_name=Administrator
		// WHERE department _id=10";
		String sqlUpdate = "UPDATE departments SET department_name=? WHERE department_id=?";

		PreparedStatement pstmt = conn.prepareStatement(sqlUpdate); // sql: ? - in param
		// data setting - in parm 순서대로data setting pstmt.setType(param순서, 값 );parm data sql 로 변환 X
		pstmt.setString(1, deptName);
		pstmt.setInt(2, deptId);
		pstmt.executeUpdate(); // 쿼리 실행

		Statement stmt = conn.createStatement();// sql 없이 객체 생성

		String sql = "SELECT department_id, department_name FROM departments WHERE department_id < 100";
		ResultSet result = stmt.executeQuery(sql);// 쿼리 실행 시 sql setting , SQL Injection 취약()

		// 4.Query 결과 처리 - ResultSet result = pstmt.executeQuery();
		while (result.next()) {
			deptList.add(new Department(result.getInt(1), result.getString(2)));
		}
		// 5. DB연결 종료 - result.close(), pstmt.close(), conn.close()
		if (result != null)
			result.close();
		if (stmt != null)
			stmt.close();
		if (conn != null)
			conn.close();

		for (Department department : deptList) {
			System.out.println(department.toString());
		}

	}
}

#Department.java

/*
DB의 Departments 테이블의 department_id, department_name 정보를 담는 클래스
Entity Object
@author SSAFY * */
public class Department {
	private int deptId;
	private String deptName;

	public Department(int deptId, String deptName) {
		super();
		this.deptId = deptId;
		this.deptName = deptName;
	}

	public Department() {
		super();
	}

	public int getDeptId() {
		return deptId;
	}

	public void setDeptId(int deptId) {
		this.deptId = deptId;
	}

	public String getDeptName() {
		return deptName;
	}

	public void setDeptName(String deptName) {
		this.deptName = deptName;
	}

	@Override
	public String toString() {
		return "Department [deptId=" + deptId + ", deptName=" + deptName + "]";
	}
}

 

 

---

 

Statement사용하게 된다면 변수로 주게 된다면 SQL Injection 취약에 취약하기 때문에

 

Statement사용이 아닌 prepareStatement로 변경하여 사용함 → SQL 쿼리문으로 실행이 되지 않음 !