이클립스 MySQL
계좌,고객 정보 연결 실습
- 고객 정보 데이터베이스 실습
package com.varxyz.jv250.banking;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class CustomerDao { // DB 관련 내용을 상수로 설정
private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
private static final String JDBC_URL = "jdbc:mysql://localhost:3306/jv250?serverTimezone=Asia/Seoul";
private static final String JDBC_USER = "jv250";
private static final String JDBC_PASSWORD = "jv250";
public CustomerDao() {
try {
Class.forName(JDBC_DRIVER); // 드라이버 연결
System.out.println("LOADED DRIVER --->" + JDBC_DRIVER);
} catch (Exception e) {
e.printStackTrace();
}
}
public List<Customer> findAllCustomers() { // 전체 고객 검색
String sql = "SELECT * FROM Customer";
List<Customer> customerList = new ArrayList<>(); // List 배열로 데이터를 받기 위해 배열 생성
try {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD); // DB와 연결
pstmt = con.prepareStatement(sql); // con에 sql을 입력하는 것을 pstmt에 담음
rs = pstmt.executeQuery(); // 데이터베이스에서 데이터를 가져와서(pstmt 입력) 결과 집합을 반환
while (rs.next()) { // 해당 테이블에서 데이터를 찾으면 true, 없으면 false
Customer c = new Customer();
c.setCid(rs.getLong("cid"));
c.setName(rs.getString("name"));
c.setSsn(rs.getString("ssn"));
c.setPhone(rs.getString("phone"));
c.setCustomerId(rs.getString("CustomerId"));
customerList.add(c);
}
} finally {
rs.close();
pstmt.close();
con.close();
}
} catch (Exception e) {
e.printStackTrace();
}
return customerList; // List 에 결과값을 받음
}
public Customer findCustomerBySsn(String ssn) { // 해당 ssn을 가진 고객 검색
String sql = "SELECT * FROM Customer WHERE ssn = ?";
Customer c = null; // 위와 다르게 c라는 Cutomer 변수 생성, 방법은 둘다 맞음
try {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
pstmt = con.prepareStatement(sql);
pstmt.setString(1, ssn);
rs = pstmt.executeQuery();
if (rs.next()) {
c = new Customer();
c.setCid(rs.getLong("cid"));
c.setName(rs.getString("name"));
c.setSsn(rs.getString("ssn"));
c.setPhone(rs.getString("phone"));
c.setCustomerId(rs.getString("CustomerId"));
}
} finally {
rs.close();
pstmt.close();
con.close();
}
} catch (Exception e) {
e.printStackTrace();
}
return c;
}
public void addCustomers(Customer customer) { // Customer 추가
String sql = "INSERT INTO Customer(name, ssn, phone, customerId, passwd) VALUES (?, ?, ?, ?, ?)";
try {
Connection con = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, customer.getName());
pstmt.setString(2, customer.getSsn());
pstmt.setString(3, customer.getPhone());
pstmt.setString(4, customer.getCustomerId());
pstmt.setString(5, customer.getPasswd());
pstmt.executeUpdate();
pstmt.close();
con.close();
System.out.println("INSERTED....");
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
CustomerDao customerdao = new CustomerDao();
System.out.println(customerdao.findCustomerBySsn("961025-7894561").getPhone());
}
}- 계좌 정보 기존 메소드와 연동하여 연결
package com.varxyz.jv250.banking;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class AccountDao { // DB 관련 내용을 상수로 설정
private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
private static final String JDBC_URL = "jdbc:mysql://localhost:3306/jv250?serverTimezone=Asia/Seoul";
private static final String JDBC_USER = "jv250";
private static final String JDBC_PASSWORD = "jv250";
public AccountDao() {
try {
Class.forName(JDBC_DRIVER); // 드라이버 연결
System.out.println("LOADED DRIVER --->" + JDBC_DRIVER);
} catch (Exception e) {
e.printStackTrace();
}
}
public void addAccounts(Account account) { // Account 추가
String sql = "INSERT INTO Account (accountNum, balance, interestRate, overdraft, accountType, customerId) VALUES (?, ?, ?, ?, ?, ?)";
try {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
pstmt = con.prepareStatement(sql);
pstmt.setString(1, account.getAccountNum());
pstmt.setDouble(2, account.getBalance());
if (account instanceof SavingsAccount) {
SavingsAccount sa = (SavingsAccount) account;
pstmt.setDouble(3, sa.getInterestRate());
pstmt.setDouble(4, 0.0);
pstmt.setString(5, String.valueOf('S'));
} else {
CheckingAccount ca = (CheckingAccount) account;
pstmt.setDouble(3, 0.0);
pstmt.setDouble(4, ca.getOverdraftAmount());
pstmt.setString(5, String.valueOf('C'));
}
pstmt.setLong(6, account.getCustomer().getCid());
pstmt.executeUpdate();
} finally {
pstmt.close();
con.close();
}
System.out.println("NEW ACCOUNT INSERTED....\n");
} catch (Exception e) {
e.printStackTrace();
}
}
// 전달된 주민번호를 가진 특정 고객의 계좌 목록 조회
public List<Account> findAccountBySsn(String ssn) {
String sql = "SELECT a.aid, a.accountNum, a.balance, a.interestRate, a.overdraft, a.accountType, c.name, c.ssn, c.phone, a.regDate"
+ " FROM Account a INNER JOIN Customer c ON a.customerId = c.cid WHERE c.ssn = ?";
List<Account> accountList = new ArrayList<>();
try {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
pstmt = con.prepareStatement(sql);
pstmt.setString(1, ssn);
rs = pstmt.executeQuery();
Account account = null;
while (rs.next()) {
if (rs.getString("accountType").charAt(0) == 'S') {
account = new SavingsAccount();
((SavingsAccount) account).setInterestRate(rs.getDouble("interestRate"));
} else {
account = new CheckingAccount();
((CheckingAccount) account).setOverdraftAmount(rs.getDouble("overdraft"));
}
account.setAid(rs.getLong("aid"));
account.setAccountNum(rs.getString("accountNum"));
account.setBalance(rs.getDouble("balance"));
account.setCustomer(new Customer(rs.getString("name"), rs.getString("ssn"), rs.getString("phone")));
account.setRegDate(rs.getTimestamp("regDate"));
accountList.add(account);
}
} finally {
pstmt.close();
con.close();
}
System.out.println("NEW ACCOUNT INSERTED....\n");
} catch (Exception e) {
e.printStackTrace();
}
return accountList;
}
public List<Account> findAllAccounts() { // 전체 계좌 검색
String sql = "SELECT a.aid, a.accountNum, a.balance, a.interestRate, a.overdraft, a.accountType, c.name, c.ssn, c.phone, a.regDate"
+ " FROM Account a INNER JOIN Customer c ON a.customerId = c.cid";
List<Account> AccountList = new ArrayList<>(); // List 배열로 데이터를 받기 위해 배열 생성
try {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Account account = null;
try {
con = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD); // DB와 연결
pstmt = con.prepareStatement(sql); // con에 sql을 입력하는 것을 pstmt에 담음
rs = pstmt.executeQuery(); // 데이터베이스에서 데이터를 가져와서(pstmt 입력) 결과 집합을 반환
while (rs.next()) {
if (rs.getString("accountType").charAt(0) == 'S') {
account = new SavingsAccount();
((SavingsAccount) account).setInterestRate(rs.getDouble("interestRate"));
} else {
account = new CheckingAccount();
((CheckingAccount) account).setOverdraftAmount(rs.getDouble("overdraft"));
}
account.setAid(rs.getLong("aid"));
account.setAccountNum(rs.getString("accountNum"));
account.setBalance(rs.getDouble("balance"));
account.setCustomer(new Customer(rs.getString("name"), rs.getString("ssn"), rs.getString("phone")));
account.setRegDate(rs.getTimestamp("regDate"));
AccountList.add(account);
}
} finally {
rs.close();
pstmt.close();
con.close();
}
} catch (Exception e) {
e.printStackTrace();
}
return AccountList; // List 에 결과값을 받음
}
}- 실행 결과
package com.varxyz.jv250.banking;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class AccountJdbcTest {
public static void main(String[] args) {
String driver = "com.mysql.cj.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/jv250?serverTimezone=Asia/Seoul";
String id = "jv250";
String passwd = "jv250";
try {
Class.forName(driver); // 드라이버 연결
System.out.println("LOADED DRIVER --->" + driver);
Connection con = DriverManager.getConnection(url, id, passwd); // DB와 연결
System.out.println("CONNECTED TO --->" + url);
select(con);
insert(con);
select(con);
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void select(Connection con) {
String sql = "SELECT * FROM Account";
try {
PreparedStatement pstmt = con.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery(); // rs에 저장
while (rs.next()) { // 해당 테이블에서 데이터를 찾으면 true, 없으면 false
Long aid = rs.getLong("aid");
String accountNum = rs.getString("accountNum");
Double balance = rs.getDouble("balance");
Double interestRate = rs.getDouble("interestRate");
Double overdraft = rs.getDouble("overdraft");
char accountType = rs.getString("accountType").charAt(0);
System.out.println(aid);
System.out.println(accountNum);
System.out.println(balance);
System.out.println(interestRate);
System.out.println(overdraft);
System.out.println(accountType);
System.out.println("-------------------");
}
pstmt.close();
System.out.println("SELECT COMPLETED...\n");
} catch (Exception e) {
e.printStackTrace();
}
}
public static void insert(Connection con) {
String sql = "INSERT INTO Account (accountNum, balance, interestRate, overdraft, accountType, customerId) VALUES (?, ?, ?, ?, ?, ?);";
try {
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, "456-78-9012");
pstmt.setDouble(2, 3000.0);
pstmt.setDouble(3, 0.03);
pstmt.setDouble(4, 0.0);
pstmt.setString(5, String.valueOf('S'));
pstmt.setLong(6, 1004);
pstmt.executeUpdate();
pstmt.close();
System.out.println("NEW ACCOUNT INSERTED...\n");
} catch (Exception e) {
e.printStackTrace();
}
}
}
안녕하세요. 맡은 업무를 확실하게 수행하는 웹 개발자가 되기 위하여 끊임없이 학습에 정진하겠습니다.