618
No
class CarDTO
package java19.car;
//dto : Data Transfer Object ( 데이터 전달 객체)
// 하나의 레코드를 저장함
//vo : value Object (값을 저장하는 객체)
public class CarDTO {
private String license_number;
private String company;
private String type;
private int year;
private int effiency;
public CarDTO() {
super();
}
public CarDTO(String license_number, String company, String type, int year, int effiency) {
super();
this.license_number = license_number;
this.company = company;
this.type = type;
this.year = year;
this.effiency = effiency;
}
public String getLicense_number() {
return license_number;
}
public void setLicense_number(String license_number) {
this.license_number = license_number;
}
public String getCompany() {
return company;
}
public void setCompany(String company) {
this.company = company;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public int getYear() {
return year;
}
public void setYear(int year) {
this.year = year;
}
public int getEffiency() {
return effiency;
}
public void setEffiency(int effiency) {
this.effiency = effiency;
}
@Override
public String toString() {
return "CarDTO [license_number=" + license_number + ", company=" + company + ", type=" + type + ", year=" + year
+ ", effiency=" + effiency + "]";
}
}
class CarDAO
package java19.car;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
//dao : Data Access Object (데이터 처리 객체)
// 핵심적인 비즈니스 로직 처리
// 주로 db 연동 관련 작업
// front-end, back-end
public class CarDAO {
private static CarDAO carDao;
private CarDAO(){
}
public static CarDAO getInstance(){
if(carDao==null){
carDao=new CarDAO();
System.out.println("객체 생성");
}else{
System.out.println("객체 x");
}
return carDao;
}
//car 테이블의 전체 레코드 리턴
public ArrayList listCar(){
ArrayList items=new ArrayList<>();
Connection conn=null;//db 접속
PreparedStatement pstmt =null;//sql 을 실행시키는 객체
ResultSet rs=null;//java.sql.ResultSet 레코드셋( 레코드 집합)
try{
conn =DBConnectManager.dbConn();
String sql="select * from car";
pstmt=conn.prepareStatement(sql);//sql 명령어 실행 객체 생성
rs=pstmt.executeQuery();// sql 을 실행, 실행결과를 rs 에 전달
// CREATE TABLE `car` (
// `license_number` VARCHAR(50) NOT NULL,
// `company` VARCHAR(50) NULL DEFAULT NULL,
// `type` VARCHAR(50) NULL DEFAULT NULL,
// `year` INT(11) NULL DEFAULT NULL,
// `efficiency` INT(11) NULL DEFAULT NULL,
// PRIMARY KEY (`license_number`)
// )
while(rs.next()){ //결과셋.next() 다음 레코드가 존재하면 true
String license_number=rs.getString("license_number");
String company=rs.getString("company");
String type=rs.getString("type");
int year=rs.getInt("year");
int efficiency=rs.getInt("efficiency");
CarDTO dto =new CarDTO(license_number, company, type, year, efficiency);
items.add(dto);
}
}catch(Exception e){
e.printStackTrace();
}finally{
DBConnectManager.close(conn, pstmt, rs);
}
return items;
}
//결과셋 = executeQuery() select query
// 영향을 받은 레코드 갯수 = executeUpdate()
//car 테이블에 레코드 추가
public void inSertCar(CarDTO dto){
Connection conn=null;
PreparedStatement pstmt=null;
try{
conn=DBConnectManager.dbConn();
String sql="insert into car (license_number, company, type, year, efficiency"
+ " ) values (?, ?, ? ,? , ?)";
pstmt =conn.prepareStatement(sql);
pstmt.setString(1, dto.getLicense_number());
pstmt.setString(2, dto.getCompany());
pstmt.setString(3, dto.getType());
pstmt.setInt(4, dto.getYear());
pstmt.setInt(5, dto.getEffiency());
pstmt.execute();
//pstmt.executeUpdate(); //select 이외의 모든 값
}catch(Exception e){
e.printStackTrace();
}finally{
DBConnectManager.close(conn, pstmt);
}
}
public int deleteCar(String license_number){
Connection conn=null;
PreparedStatement pstmt =null;
int result=0;
try{
conn=DBConnectManager.dbConn();
String sql ="delete from car where license_number= ? ";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, license_number);
result=pstmt.executeUpdate();
}catch (Exception e) {
e.printStackTrace();
}finally{
DBConnectManager.close(conn, pstmt);
}
return result;
}
}
class DBConnectManager
package java19.car;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
public class DBConnectManager {
public static Connection dbConn(){
Connection conn=null;
try{
//db 접속 정보가 저장되 db.prop 파일을 로딩
FileInputStream fis =new FileInputStream("db.prop");
Properties prop=new Properties();
prop.load(fis);
String url=prop.getProperty("url");
String id=prop.getProperty("id");
String password=prop.getProperty("password");
//mysql에 접속 처리
conn=DriverManager.getConnection(url, id, password);
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn){
try {
if(conn!=null)conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close(PreparedStatement pstmt){
try {
if(pstmt!=null)pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close(ResultSet rs){
try {
if(rs!=null)rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close(Connection conn, PreparedStatement pstmt ){
try{
if(pstmt!=null)pstmt.close();
}catch(Exception e){
e.printStackTrace();
}
try {
if(conn!=null)conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close(Connection conn, PreparedStatement pstmt, ResultSet rs){
try{
if(rs!=null)rs.close();
}catch(Exception e){
e.printStackTrace();
}
try{
if(pstmt!=null)pstmt.close();
}catch(Exception e){
e.printStackTrace();
}
try {
if(conn!=null)conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close(Connection conn, Statement stmt, ResultSet rs){
try{
if(rs!=null)rs.close();
}catch(Exception e){
e.printStackTrace();
}
try{
if(stmt!=null)stmt.close();
}catch(Exception e){
e.printStackTrace();
}
try {
if(conn!=null)conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
class CarManage
package java19.car;
import java.util.List;
import java.util.Scanner;
public class CarManage {
public void carSelect(){
CarDAO dao =CarDAO.getInstance();
List items=dao.listCar();
System.out.println("---------------------------------------------------------------------");
System.out.println("차량번호\t연도\t제조사\t타입\t연비");
System.out.println();
for(CarDTO dto : items){
System.out.print(dto.getLicense_number()+"\t");
System.out.print(dto.getYear()+"\t");
System.out.print(dto.getCompany()+"\t");
System.out.print(dto.getType()+"\t");
System.out.print(dto.getEffiency());
System.out.println();
}
}
public void carInsert(){
CarDAO dao =CarDAO.getInstance();
@SuppressWarnings("resource")
Scanner scan=new Scanner(System.in);
System.out.print("차량번호:");
String license_number=scan.next();
System.out.print("제조사:");
String company=scan.next();
System.out.print(" 타입 :");
String type=scan.next();
System.out.print("연도:");
int year=scan.nextInt();
System.out.print("연비:");
int effiency=scan.nextInt();
CarDTO dto =new CarDTO(license_number, company, type, year, effiency);
dao.inSertCar(dto);
System.out.println("추가 되었습니다.");
}
public void carDelete(){
CarDAO dao =CarDAO.getInstance();
@SuppressWarnings("resource")
Scanner scan=new Scanner(System.in);
System.out.print("삭제할 차량 번호를 입력하세요.");
String license_number=scan.next();
int result=dao.deleteCar(license_number);
if(result==0){
System.out.println("차량 번호가 존재하지 않습니다." + result);
}else{
System.out.println("삭제 되었습니다." + result);
}
}
public static void main(String[] args) {
CarManage manage =new CarManage();
Scanner scanner =new Scanner(System.in);//
while(true){
System.out.println();
System.out.println("작업을 선택하세요 (1.목록, 2. 추가, 3.삭제, 0:종료)");
int code =scanner.nextInt();
switch (code) {
case 0:
scanner.close();//스캐너 객체 클리어
System.out.println("프로그램 종료");
System.exit(0);//프로그램 종료
break;
case 1:
manage.carSelect();
break;
case 2:
manage.carInsert();
break;
case 3:
manage.carDelete();
break;
default:
System.out.println("잘못 입력 하셨습니다.");
break;
}
}
}
}
실행 =>
작업을 선택하세요 (1.목록, 2. 추가, 3.삭제, 0:종료)
3
객체 생성
삭제할 차량 번호를 입력하세요. 3
삭제에 실패 하였습니다.0
작업을 선택하세요 (1.목록, 2. 추가, 3.삭제, 0:종료)
1
객체 x
---------------------------------------------------------------------
차량번호 연도 제조사 타입 연비
111가1234 2018 준강 스팅 100
112가1234 2018 준강 스팅 100
212가1234 2018 준강 스팅 100
3124ㄱㄷ 2001 기아 오토 40
512가1234 2018 준강 스팅 100
99가1234 2017 현대 오토 20
작업을 선택하세요 (1.목록, 2. 추가, 3.삭제, 0:종료)
3
객체 x
삭제할 차량 번호를 입력하세요.3124ㄱㄷ
삭제 되었습니다.1
작업을 선택하세요 (1.목록, 2. 추가, 3.삭제, 0:종료)
1
객체 x
---------------------------------------------------------------------
차량번호 연도 제조사 타입 연비
111가1234 2018 준강 스팅 100
112가1234 2018 준강 스팅 100
212가1234 2018 준강 스팅 100
512가1234 2018 준강 스팅 100
99가1234 2017 현대 오토 20
작업을 선택하세요 (1.목록, 2. 추가, 3.삭제, 0:종료)
댓글 ( 5)
댓글 남기기