import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/*
 * JDBC 작업 순서
 * 1. Driver Loading		// verdor api
 * 2. DB 연결 (Connection 생성)
 * 3. SQL 실행 준비
 *   3-1. SQL 작성.
 *   3-2. Statement 생성 (Statement, PreparedStatement)
 * 4. SQL 실행
 *   4-1. I, U, D
 *      int x = stmt.execteUpdate(sql);
 *   	int x = pstmt.executeUpdate();
 *   4-2. S
 *      ResultSet rs = pstmt.executeQuery();
 *      rs.next() [단독, if, while]
 *      값얻기 : rs.getString()
 *            rs.getInt() 등등등....
 * 5. DB 연결 종료 : 연결 역순으로 종료, finally
 * 	if(rs != null)
 *    	rs.close()
 *  if(pstmt != null)
 *  	pstmt.close();
 *  if(conn != null)
 *  	conn.close();
 */
public class JdbcTest {
	
	private final String driver = "com.mysql.cj.jdbc.Driver";
	private final String url = "jdbc:mysql://127.0.0.1:3306/workshop?serverTimezone=UTC&useUniCode=yes&characterEncoding=UTF-8";
	private final String dbid = "ssafy";
	private final String dbpwd = "ssafy";
	
	public JdbcTest() {
		
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
	}
	
	public static void main(String[] args) throws IOException {
		JdbcTest test = new JdbcTest();
		
		BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
		
		/*
		 * 회원 등록
		 * */
		System.out.println("\n======회원 등록======");
		JdbcDto jdbcDto = new JdbcDto();
		System.out.print("아이디 : ");
		jdbcDto.setId(in.readLine());
		System.out.print("비밀번호 : ");
		jdbcDto.setPwd(in.readLine());
		System.out.print("이름 : ");
		jdbcDto.setName(in.readLine());
		
		int cnt = test.register(jdbcDto);
		
		if(cnt != 0)
			System.out.println("등록 성공!!!");
		
		/*
		 * 회원 검색 
		 * */
		System.out.println("\n======회원 검색======");
		System.out.print("검색할 아이디 : ");
		String sid = in.readLine();
		JdbcDto dto = test.searchById(sid);
		
		
		if(dto != null) {
			System.out.println("\n"+ sid + "회원 정보!!!");
			System.out.println("이름 : " + dto.getName());
			System.out.println("비번 : " + dto.getPwd());
			System.out.println("가입일 : " + dto.getJoinDate());
		} else {
			System.out.println(sid + " 회원은 없습니다.");
		}
		
		/*
		 * 회원 정보 수정 
		 * */
		System.out.println("\n======회원 정보 수정======");
		System.out.print("수정 할 회원 아이디 : ");
		String mid = in.readLine();
		System.out.print("수정할 비밀 번호 : ");
		String mpwd = in.readLine();
		
		cnt = test.updateInfo(mid, mpwd);
		System.out.println(cnt + "개 정보 수정!!!");
		
		/*
		 * 회원 삭제
		 * */
		System.out.println("\n======회원 정보 삭제======");
		System.out.print("탈퇴 할 회원 아이디 : ");
		String did = in.readLine();
		
		cnt = test.deleteById(did);
		System.out.println(cnt + "명 탈퇴!!!");
		
		/*
		 * 회원 리스트 검색 
		 * */
		System.out.println("--- 모든 회원 정보 ---");
		System.out.println("이름\t아이디\t비밀번호\t가입일");
		System.out.println("----------------------------------");
		
		List<JdbcDto> list = test.searchList();
		for(int i=0; i<list.size(); i++) {
			JdbcDto t = list.get(i);
			System.out.println(t.getName()+"\t"+t.getId()+"\t"
					+t.getPwd()+"\t" + t.getJoinDate());
		}
	}
	private List<JdbcDto> searchList() {
		List<JdbcDto> list = new ArrayList<JdbcDto>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = DriverManager.getConnection(url, dbid, dbpwd);
			StringBuilder sql = new StringBuilder();
			sql.append("select id, pwd, name, joindate \n");
			sql.append("from jdbctest ");
			pstmt = conn.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				JdbcDto jdbcDto = new JdbcDto();
				jdbcDto.setId(rs.getString("id"));
				jdbcDto.setPwd(rs.getString("pwd"));
				jdbcDto.setName(rs.getString("name"));
				jdbcDto.setJoinDate(rs.getString("joindate"));
				list.add(jdbcDto);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	private int deleteById(String did) {
		int cnt = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DriverManager.getConnection(url, dbid, dbpwd);
			StringBuilder sql = new StringBuilder();
			sql.append("delete from jdbctest \n");
			sql.append("where id = ?");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, did);
			
			cnt = pstmt.executeUpdate();
			if(cnt != 0)
				System.out.println("삭제에 성공했습니다.");
			else
				System.out.println("삭제에 실패했습니다.");
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			
			try {
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return cnt;
	}

	private int updateInfo(String mid, String mpwd) {
		int cnt = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DriverManager.getConnection(url, dbid, dbpwd);
			StringBuilder sql = new StringBuilder();
			sql.append("update jdbctest \n");
			sql.append("set pwd = ? \n");
			sql.append("where id = ? ");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, mpwd);
			pstmt.setString(2, mid);
			cnt = pstmt.executeUpdate();
			
			if(cnt != 0)
				System.out.println("비밀번호 수정에 성공했습니다.");
			else
				System.out.println("비밀번호 수정에 실패했습니다.");
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			
			try {
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return cnt;
	}

	/* 회원 검색 */
	private JdbcDto searchById(String sid) {
		JdbcDto jdbcDto = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			jdbcDto = new JdbcDto();
			conn = DriverManager.getConnection(url, dbid, dbpwd);
			StringBuilder sql = new StringBuilder();
			
//			select id, name, pwd, joindate
//			from jdbctest
//			where id = '테스트';
			
			sql.append("select * \n");
			sql.append("from jdbctest \n");
			sql.append("where id = ? ");
			System.out.println(sql.toString());
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, sid);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				jdbcDto.setId(rs.getString("id"));
				jdbcDto.setName(rs.getString("name"));
				jdbcDto.setPwd(rs.getString("pwd"));
				jdbcDto.setJoinDate(rs.getString("joindate"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return jdbcDto;
	}

	private int register(JdbcDto jdbcDto) {
		int cnt = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DriverManager.getConnection(url, dbid, dbpwd);
			StringBuilder sql = new StringBuilder();
			sql.append("insert into jdbctest(id, pwd, name, joindate) \n");
			sql.append("values (?, ?, ?, now())");
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, jdbcDto.getId());
			pstmt.setString(2, jdbcDto.getPwd());
			pstmt.setString(3, jdbcDto.getName());
			cnt = pstmt.executeUpdate();
			
			if(cnt != 0) 
				System.out.println("등록 성공!!");
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return cnt;
	}
}

✔실행결과