17.JDBC
by SANGGI JEON
JDBC(Java DataBase Connectivity)
- 자바에서 데이터베이스에 접속할 수 있도록 하는 자바 API
- 인터페이스로 작성되어 있음
1.Eclipse에 Oracle JDBC 설정
1.프로젝트 우클릭
2.Properties
3.Java Build Path
4.Libraries
5.Classpath에 Add external JARs..
6.ojdbc8.jar 추가
2. Java에서 데이터베이스 연결
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String username = "c##hr";
String password = "1234";
Connection conn = null;
// 문자열을 많이 사용하고 더할 때 StringBuffer를 사용
StringBuffer sb = new StringBuffer();
// sql 쿼리문을 만들고 인자에 값을 넣어주기 위해 사용
PreparedStatement pStmt = null;
String name = "Su";
ResultSet rSet = null;
String firstName, lastName, email, phoneNumber, hireDate;
try {
// 자바의 리플렉션 기법
// Class는 클래스의 메타 정보를 관리해줌
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, username, password);
}catch(Exception e) {
e.printStackTrace();
}
3. SQL 작성
sb.append("SELECT FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE ");
sb.append("FROM EMPLOYEES ");
sb.append("WHERE FIRST_NAME LIKE ? OR LAST_NAME = ?");
String sql = sb.toString();
4. PreparedStatement와 ResultSet
try {
pStmt = conn.prepareStatement(sql);
pStmt.setString(1, "%" + name + "%");
pStmt.setString(2, "%" + name + "%");
rSet = pStmt.executeQuery();
while(rSet.next()) {
firstName = rSet.getString(1);
lastName = rSet.getString(2);
email = rSet.getString(3);
phoneNumber = rSet.getString(4);
hireDate = rSet.getDate(5).toString();
phoneNumber = phoneNumber.replace(".", "-");
System.out.println("First Name: " + firstName);
System.out.println("Last Name: " + lastName);
System.out.println("email: " + email);
System.out.println("Phone Number: " + phoneNumber);
System.out.println("Hire Date: " + hireDate);
System.out.println();
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
if(rSet != null)
rSet.close();
if(pStmt != null)
pStmt.close();
if(conn != null)
conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
5. 전체 코드
package com.jremind.main;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class ProjetMain {
public static void main(String[] args) {
//jdbc 접속 url
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String username = "c##hr";
String password = "1234";
Connection conn = null;
// 문자열을 많이 사용하고 더할 때 StringBuffer를 사용
StringBuffer sb = new StringBuffer();
// sql 쿼리문을 만들고 인자에 값을 넣어주기 위해 사용
PreparedStatement pStmt = null;
String name = "Su";
ResultSet rSet = null;
String firstName, lastName, email, phoneNumber, hireDate;
try {
// 자바의 리플렉션 기법
// Class는 클래스의 메타 정보를 관리해줌
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, username, password);
}catch(Exception e) {
e.printStackTrace();
}
sb.append("SELECT FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE ");
sb.append("FROM EMPLOYEES ");
sb.append("WHERE FIRST_NAME LIKE ? OR LAST_NAME = ?");
String sql = sb.toString();
try {
pStmt = conn.prepareStatement(sql);
pStmt.setString(1, "%" + name + "%");
pStmt.setString(2, "%" + name + "%");
rSet = pStmt.executeQuery();
while(rSet.next()) {
firstName = rSet.getString(1);
lastName = rSet.getString(2);
email = rSet.getString(3);
phoneNumber = rSet.getString(4);
hireDate = rSet.getDate(5).toString();
phoneNumber = phoneNumber.replace(".", "-");
System.out.println("First Name: " + firstName);
System.out.println("Last Name: " + lastName);
System.out.println("email: " + email);
System.out.println("Phone Number: " + phoneNumber);
System.out.println("Hire Date: " + hireDate);
System.out.println();
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
if(rSet != null)
rSet.close();
if(pStmt != null)
pStmt.close();
if(conn != null)
conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
}
예제2-1. Java Bean 생성
package com.jremind.model;
public class Employees {
private long employeeID;
private String firstName;
private String lastName;
private String email;
private String phoneNumber;
private String hireDate;
private String jobID;
private double salary;
private double commissionPct;
private long managerID;
private long departmentID;
public long getEmployeeID() {
return employeeID;
}
public void setEmployeeID(long employeeID) {
this.employeeID = employeeID;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhoneNumber() {
return phoneNumber;
}
public void setPhoneNumber(String phoneNumber) {
this.phoneNumber = phoneNumber;
}
public String getHireDate() {
return hireDate;
}
public void setHireDate(String hireDate) {
this.hireDate = hireDate;
}
public String getJobID() {
return jobID;
}
public void setJobID(String jobID) {
this.jobID = jobID;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public double getCommissionPct() {
return commissionPct;
}
public void setCommissionPct(double commissionPct) {
this.commissionPct = commissionPct;
}
public long getManagerID() {
return managerID;
}
public void setManagerID(long managerID) {
this.managerID = managerID;
}
public long getDepartmentID() {
return departmentID;
}
public void setDepartmentID(long departmentID) {
this.departmentID = departmentID;
}
}
예제2-2. Main
package com.jremind.main;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.jremind.model.Employees;
public class ProjetMain {
public static void main(String[] args) {
//jdbc 접속 url
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String username = "c##hr";
String password = "1234";
Connection conn = null;
// 문자열을 많이 사용하고 더할 때 StringBuffer를 사용
StringBuffer sb = new StringBuffer();
// sql 쿼리문을 만들고 인자에 값을 넣어주기 위해 사용
PreparedStatement pStmt = null;
String name = "Su";
ResultSet rSet = null;
try {
// 자바의 리플렉션 기법
// Class는 클래스의 메타 정보를 관리해줌
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, username, password);
}catch(Exception e) {
e.printStackTrace();
}
sb.append("SELECT FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE ");
sb.append("FROM EMPLOYEES ");
sb.append("WHERE FIRST_NAME LIKE ? OR LAST_NAME = ?");
String sql = sb.toString();
Employees employee = new Employees();
try {
pStmt = conn.prepareStatement(sql);
pStmt.setString(1, "%" + name + "%");
pStmt.setString(2, "%" + name + "%");
rSet = pStmt.executeQuery();
while(rSet.next()) {
employee.setFirstName(rSet.getString(1));
employee.setLastName(rSet.getString(2));
employee.setEmail(rSet.getString(3));
employee.setPhoneNumber(rSet.getString(4));
employee.setHireDate(rSet.getString(5));
System.out.println("First Name: " + employee.getFirstName());
System.out.println("Last Name: " + employee.getLastName());
System.out.println("email: " + employee.getEmail());
System.out.println("Phone Number: " + employee.getPhoneNumber());
System.out.println("Hire Date: " + employee.getHireDate());
System.out.println();
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
if(rSet != null)
rSet.close();
if(pStmt != null)
pStmt.close();
if(conn != null)
conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
}
2. DAO(Data Access Object)
- 데이터베이스 접근에 관련된 객체
package com.jremind.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.jremind.model.Employee;
public class HrDao {
private Connection conn;
private ResultSet rSet;
private PreparedStatement pStmt;
private final String url = "jdbc:oracle:thin:@localhost:1521:xe";
private final String username = "c##hr";
private final String password = "1234";
public void setDriver(){
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}catch (Exception e) {
e.printStackTrace();
}
}
private void getConnection() {
try {
conn = DriverManager.getConnection(url, username, password);
}catch (Exception e) {
e.printStackTrace();
}
}
private void closeConnection() {
try {
if(rSet != null)
rSet.close();
if(pStmt != null)
pStmt.close();
if(conn != null)
conn.close();
}catch (Exception e) {
e.printStackTrace();
}
}
public List<Employee> getEmployees(String name){
List<Employee> employees = new ArrayList<Employee>();
getConnection();
StringBuffer sb = new StringBuffer();
sb.append("SELECT FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE ");
sb.append("FROM EMPLOYEES ");
sb.append("WHERE FIRST_NAME LIKE ? OR LAST_NAME LIKE ?");
String sql = sb.toString();
try {
pStmt = conn.prepareStatement(sql);
pStmt.setString(1, "%" + name + "%");
pStmt.setString(2, "%" + name + "%");
rSet = pStmt.executeQuery();
Employee employee;
while(rSet.next()) {
employee = new Employee();
employee.setFirstName(rSet.getString(1));
employee.setLastName(rSet.getString(2));
employee.setEmail(rSet.getString(3));
employee.setPhoneNumber(rSet.getString(4));
employee.setHireDate(rSet.getString(5));
employees.add(employee);
}
}catch(Exception e) {
e.printStackTrace();
}finally {
closeConnection();
}
return employees;
}
}
package com.jremind.model;
public class Employee {
private long employeeID;
private String firstName;
private String lastName;
private String email;
private String phoneNumber;
private String hireDate;
private String jobID;
private double salary;
private double commissionPct;
private long managerID;
private long departmentID;
public long getEmployeeID() {
return employeeID;
}
public void setEmployeeID(long employeeID) {
this.employeeID = employeeID;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhoneNumber() {
return phoneNumber;
}
public void setPhoneNumber(String phoneNumber) {
this.phoneNumber = phoneNumber;
}
public String getHireDate() {
return hireDate;
}
public void setHireDate(String hireDate) {
this.hireDate = hireDate;
}
public String getJobID() {
return jobID;
}
public void setJobID(String jobID) {
this.jobID = jobID;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public double getCommissionPct() {
return commissionPct;
}
public void setCommissionPct(double commissionPct) {
this.commissionPct = commissionPct;
}
public long getManagerID() {
return managerID;
}
public void setManagerID(long managerID) {
this.managerID = managerID;
}
public long getDepartmentID() {
return departmentID;
}
public void setDepartmentID(long departmentID) {
this.departmentID = departmentID;
}
}
package com.jremind.main;
import java.util.ArrayList;
import java.util.List;
import com.jremind.dao.HrDao;
import com.jremind.model.Employee;
public class HrMain {
public static void main(String[] args) {
HrDao dao = new HrDao();
dao.setDriver();
List<Employee> employees = new ArrayList<Employee>();
employees = dao.getEmployees("Su");
for(Employee employee : employees) {
System.out.println("First Name: " + employee.getFirstName());
System.out.println("Last Name: " + employee.getLastName());
System.out.println("email: " + employee.getEmail());
System.out.println("Phone Number: " + employee.getPhoneNumber());
System.out.println("Hire Date: " + employee.getHireDate());
System.out.println();
}
}
}
package com.jremind.main;
import java.util.ArrayList;
import java.util.List;
import com.jremind.dao.HrDao;
import com.jremind.model.Employee;
public class HrMain {
public static void main(String[] args) {
HrDao dao = new HrDao();
dao.setDriver();
List<Employee> employees = new ArrayList<Employee>();
employees = dao.getEmployees("Su");
for(Employee employee : employees) {
System.out.println("First Name: " + employee.getFirstName());
System.out.println("Last Name: " + employee.getLastName());
System.out.println("email: " + employee.getEmail());
System.out.println("Phone Number: " + employee.getPhoneNumber());
System.out.println("Hire Date: " + employee.getHireDate());
System.out.println();
}
}
}
JDBC Singleton
// HrDao.java
package com.jremind.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.jremind.model.Employee;
public class HrDao {
private static HrDao dao = new HrDao();
private Connection conn;
private ResultSet rSet;
private PreparedStatement pStmt;
private final String url = "jdbc:oracle:thin:@localhost:1521:xe";
private final String username = "c##hr";
private final String password = "1234";
private HrDao() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}catch (Exception e) {
e.printStackTrace();
}
}
public static HrDao getInstance() {
return dao;
}
private void getConnection() {
try {
conn = DriverManager.getConnection(url, username, password);
}catch (Exception e) {
e.printStackTrace();
}
}
private void closeConnection() {
try {
if(rSet != null)
rSet.close();
if(pStmt != null)
pStmt.close();
if(conn != null)
conn.close();
}catch (Exception e) {
e.printStackTrace();
}
}
public List<Employee> getEmployees(String name){
List<Employee> employees = new ArrayList<Employee>();
getConnection();
StringBuffer sb = new StringBuffer();
sb.append("SELECT FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE ");
sb.append("FROM EMPLOYEES ");
sb.append("WHERE FIRST_NAME LIKE ? OR LAST_NAME LIKE ?");
String sql = sb.toString();
try {
pStmt = conn.prepareStatement(sql);
pStmt.setString(1, "%" + name + "%");
pStmt.setString(2, "%" + name + "%");
rSet = pStmt.executeQuery();
Employee employee;
while(rSet.next()) {
employee = new Employee();
employee.setFirstName(rSet.getString(1));
employee.setLastName(rSet.getString(2));
employee.setEmail(rSet.getString(3));
employee.setPhoneNumber(rSet.getString(4));
employee.setHireDate(rSet.getString(5));
employees.add(employee);
}
}catch(Exception e) {
e.printStackTrace();
}finally {
closeConnection();
}
return employees;
}
}
package com.jremind.main;
import java.util.ArrayList;
import java.util.List;
import com.jremind.dao.HrDao;
import com.jremind.model.Employee;
public class HrMain {
public static void main(String[] args) {
HrDao dao = HrDao.getInstance();
List<Employee> employees = new ArrayList<Employee>();
employees = dao.getEmployees("Su");
for(Employee employee : employees) {
System.out.println("First Name: " + employee.getFirstName());
System.out.println("Last Name: " + employee.getLastName());
System.out.println("email: " + employee.getEmail());
System.out.println("Phone Number: " + employee.getPhoneNumber());
System.out.println("Hire Date: " + employee.getHireDate());
System.out.println();
}
}
}
package com.jremind.model;
public class Employee {
private long employeeID;
private String firstName;
private String lastName;
private String email;
private String phoneNumber;
private String hireDate;
private String jobID;
private double salary;
private double commissionPct;
private long managerID;
private long departmentID;
public long getEmployeeID() {
return employeeID;
}
public void setEmployeeID(long employeeID) {
this.employeeID = employeeID;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhoneNumber() {
return phoneNumber;
}
public void setPhoneNumber(String phoneNumber) {
this.phoneNumber = phoneNumber;
}
public String getHireDate() {
return hireDate;
}
public void setHireDate(String hireDate) {
this.hireDate = hireDate;
}
public String getJobID() {
return jobID;
}
public void setJobID(String jobID) {
this.jobID = jobID;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
public double getCommissionPct() {
return commissionPct;
}
public void setCommissionPct(double commissionPct) {
this.commissionPct = commissionPct;
}
public long getManagerID() {
return managerID;
}
public void setManagerID(long managerID) {
this.managerID = managerID;
}
public long getDepartmentID() {
return departmentID;
}
public void setDepartmentID(long departmentID) {
this.departmentID = departmentID;
}
}
jar 파일: 클래스 파일을 묶어서 배포할 때 사용하는 파일
java bean : getter setter로 구성되며 필드에 잠시 데이터를 가지고 있는 용도로 사용
Subscribe via RSS