JAVA(6) - JDBC로 CRUD실습

Wooney98·2022년 11월 9일

JAVA

목록 보기

6/8

새 프로젝트의 jdbc의 driveManager을 연결

패키지 dpcp의 JDBCUtill

package org.comstudy.dbcp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JdbcUtil {
	private static String className = "org.mariadb.jdbc.Driver";
	private static String url = "jdbc:mariadb://localhost:3306/myapp";
	private static String user = "myuser";
	private static String password = "12345";
	private static Connection conn;

	public static Connection getConnection() {
		try {
			Class.forName(className);
			conn = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static void close(Connection conn) {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		System.out.println(getConnection());
	}
}

패키지 myapp.model의 DAO

package org.comstudy.myapp.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.comstudy.dbcp.JdbcUtil;

//테이블명과 클래스명 동일하게
public class UserDAO {
	private String SELECT_ALL="SELECT * FROM users";
	
	private Connection conn;
// Statement를 상속 받는 PreparedStatement타입의 객체 생성
	private PreparedStatement stmt; 
	private ResultSet rs;
	
	public List<UserDTO> selectAll() {
	      try {
	         conn = JdbcUtil.getConnection();
	         stmt = conn.prepareStatement(SELECT_ALL);
             // connection 클래스를 상속받는 prepareStatement메소드
             // connection객체를 prepareStatement메소드를 통해
             //PreparedStatement타입의을 반환한다. 즉, 다른구문
	         rs = stmt.executeQuery();
	         while(rs.next()) {
	            int no = rs.getInt("no");
	            String id = rs.getString("id");
	            String name = rs.getString("name");
	            int age = rs.getInt("age");
	            System.out.printf("%d, %s, %s, %d\n", no, id, name, age);
	         }
	      } catch (SQLException e) {
	         e.printStackTrace();
	      }
	      
	      return null;
	   }
	   
	   public static void main(String[] args) {
	      UserDAO dao = new UserDAO();
	      dao.selectAll();
	   }
	}

package org.comstudy.myapp.model;

public class UserDTO {
	private int no; 
	private String id;
	private String name;
	private int age;
	
	public UserDTO() {
		this(0,"","",0);
	}

	public UserDTO(int no, String id, String name, int age) {
		// TODO Auto-generated constructor stub
		this.age = age;
		this.id = id;
		this.name = name;
		this.no = no;
	}

	public int getNo() {
		return no;
	}

	public void setNo(int no) {
		this.no = no;
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}
	public static void main(String[] args) {
		
	}
}

SQL File

New - Other - SQL Development - SQL File 추가 - myapp.sql 생성
Maria DB 계정 접속해서 table 연결
Data ManageMent -> Data Source Explorer
New 클릭 -> MySQL 클릭 후 Name 수정 -> Next 클릭
New Driver 클릭

Name/Type Version 선택 -> jar list에서 mariaDB Client.jar 파일 선택하고 나머지 파일 remove -> properties에서 JdbcUtil에 정의한 url로 복붙 -> className 복붙 -> userid와 password 복붙
로그인 후 test connection -> finish
코드 드래그&우클릭 -> Execute Selected Text후 에러가 없으면 -> JdbcUtil main 실행


drop table if exists users;

create table if not exists users(
no int not null primary key auto_increment,
id varchar(20) not null,
name varchar(20),
age int
);

show tables;

insert into users(id, name, age) values('ddong','홍길동',25);
insert into users(id, name, age) values('kim','김길동',25);
insert into users(id, name, age) values('park','박길동',25);

select *from users;

commit;

SQL문 CRUD 생성

JdbcUtil close문 오버라이딩하여 작성

	public static void close(Connection conn) {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void close(PreparedStatement conn) {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void close(ResultSet conn) {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	//메소드 오버로딩
	public static void close(Connection conn, PreparedStatement stmt, ResultSet rs) {
		// TODO Auto-generated method stub
		close(rs);
		close(stmt);
		close(conn);
        // 열린 순서의 역순으로 메소드들을 닫아야한다.
	}

UserDAO

public class UserDAO {
// '?'에 변수명을 넣어주면 됨
	   private static String SELECT_ALL = "SELECT * FROM USERS";
	   private static String SELECT_ONE = "SELECT * FROM USERS WHERE no=?";
	   private static String INSERT = "INSERT INTO USERS(id,name,age) values(?,?,?)";
	   private static String UPDATE = "UPDATE USERS SET ID=?,NAME=?,AGE=? WHERE NO=?";
	   private static String DELETE = "DELETE FROM USERS WHERE NO=?";

commitCheck

	   private void commitCheck(int cnt) {
	      try {
	         if (cnt > 0) {
	            conn.commit();
	         } else {
	            conn.rollback();
	         }
	      } catch (SQLException e) {
	         e.printStackTrace();
	      }
	   }

INSERT

	   public void insert(UserDTO dto) {
	      try {
	         conn = JdbcUtil.getConnection();
	         stmt = conn.prepareStatement(INSERT);
	         stmt.setString(1, dto.getId());
	         stmt.setString(2, dto.getName());
	         stmt.setInt(3, dto.getAge());
	         commitCheck( stmt.executeUpdate() );// 처리 횟수가 리턴된다.
	      } catch (SQLException e) {
	         e.printStackTrace();
	      } finally {
	         JdbcUtil.close(conn, stmt, rs);
	      }
	   }

UPDATE

	   public void update(UserDTO dto) {
	      try {
	         conn = JdbcUtil.getConnection();
	         stmt = conn.prepareStatement(UPDATE);
	         stmt.setString(1, dto.getId());
	         stmt.setString(2, dto.getName());
	         stmt.setInt(3, dto.getAge());
	         stmt.setInt(4, dto.getNo());
	         commitCheck(stmt.executeUpdate());
	      } catch (SQLException e) {
	         e.printStackTrace();
	      } finally {
	         JdbcUtil.close(conn, stmt, rs);
	      }
	   }

DELETE

	   public void delete(UserDTO dto) {
	      try {
	         conn = JdbcUtil.getConnection();
	         stmt = conn.prepareStatement(DELETE);
	         stmt.setInt(1, dto.getNo());
	         commitCheck(stmt.executeUpdate());
	      } catch (SQLException e) {
	         e.printStackTrace();
	      } finally {
	         JdbcUtil.close(conn, stmt, rs);
	      }
	   }

SELECT_ONE

	   public UserDTO selectOne(UserDTO dto) {
	      UserDTO user = null;
	      try {
	         conn = JdbcUtil.getConnection();
	         stmt = conn.prepareStatement(SELECT_ONE);
	         stmt.setInt(1, dto.getNo());
	         rs = stmt.executeQuery();
	         if (rs.next()) {
	            int no = rs.getInt(1);
	            String id = rs.getString(2);
	            String name = rs.getString(3);
	            int age = rs.getInt(4);
	            user = new UserDTO(no, id, name, age);
	         }
	      } catch (SQLException e) {
	         e.printStackTrace();
	      } finally {
	         JdbcUtil.close(conn, stmt, rs);
	      }
	      return user;
	   }

main

	   public static void main(String[] args) {
	      UserDAO dao = new UserDAO();
	      List<UserDTO> list = dao.selectAll();

	      for (UserDTO user : list) {
	         System.out.println(user);
	      }
	   }

Wooney98

👨Education Computer Engineering 🎓Expected Graduation: February 2023 📞Contact info thstjddn77@gmail.com

JAVA(6) - JDBC로 CRUD실습

JAVA

새 프로젝트의 jdbc의 driveManager을 연결

SQL File

SQL문 CRUD 생성

JAVA(5)-예외처리

JAVA(7)- 클래스

0개의 댓글