백엔드 공부(java로 mysql 쿼리 쓰기)1

처음은 백엔드 공부를 하고싶다였다. 그래서 네이버 부스트코스에서 풀스택 과정을 들었는데, 솔직히 잘 모르겠었다. 그래서 실제로 하는건 어떻게 하는거지...? DB는 어떻게 쓰지...? 싶어서 백엔드 과정을 듣기로 했다. 물론 js도 그렇게 해서 제대로 해보기 시작한거기도 하고...

일단 DB를 사용하는 방식들에 대해 이야기를 해보자. 

 

 

sql 관련된 라이브러리를 import하고

드라이버를 로드한다.

그 다음 connection을 열고

Connection

dbUrl은 jdbc:mysql://localhost:3306/connectdb?serverTimezone=UTC로 쓰자, 왜냐하면 시간대가 안맞아서 안되더라고...

 

PrepareStatement

그 다음 이렇게 해서 State를 해준다

ResultSet

그 다음 이런 식으로 resultset을 가져온다. 뭐 이런 식으로 하는거다.

 

package kr.or.connect.jdbcexam.dao;
import java.util.ArrayList;
import java.util.List;

import java.sql.*;

import kr.or.connect.jdbcexam.dto.*;



public class RoleDao {
	private static String dbUrl =  "jdbc:mysql://localhost:3306/connectdb?serverTimezone=UTC";
	private static String dbUser = "connectuser";
	private static String dbPassword = "fbehddls1";
	
	
	public List<Role> getRoles(){
		List<Role> list = new ArrayList<>();
		try {
			Class.forName("com.mysql.jdbc.Driver");		
		}catch(Exception e) {
			e.toString();
		}
		String sql = "SELECT description, role_id FROM role order by role_id desc";
		
		try(Connection conn = DriverManager.getConnection(dbUrl,dbUser,dbPassword);
				PreparedStatement ps =conn.prepareStatement(sql)){
			try(ResultSet rs = ps.executeQuery()){
				while(rs.next()) {
					String description = rs.getString(2);
					int id = rs.getInt("role_id");
					Role role = new Role(id, description);
					list.add(role);
				}
			}catch(Exception e) {
				e.printStackTrace();
			}
		}catch(Exception ex) {
			ex.printStackTrace();
			
		}
		return list;
	}
	
	public Role getRole(Integer roleId) {
		
		Role role = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;


		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
			
			
			String sql = "SELECT role_id,description FROM role WHERE role_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, roleId);
			rs = ps.executeQuery();
			
			if(rs.next()) {
				String desc = rs.getString(2);
				int id = rs.getInt("role_id");
				role = new Role(id, desc);
				
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			if(rs!=null) {
				try {
					rs.close();
				}catch(SQLException e) {
					e.printStackTrace();
				}
			}
			if(ps != null) {
				try {
					ps.close();
				}catch(SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn!= null) {
				try {
					conn.close();
				}catch(SQLException e){
					e.printStackTrace();
				}
			}
		}
		return role;
	}
	
	public int addRole(Role role) {
		int insertCount = 0;
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(dbUrl,dbUser,dbPassword);
			String sql = "insert into role (role_id, description) values (?,?)";
			
			ps = conn.prepareStatement(sql);
			
			ps.setInt(1, role.getRoleId());
			ps.setString(2, role.getDescription());
			insertCount = ps.executeUpdate();
		}catch(Exception ex) {
			ex.printStackTrace();
		}finally {
			if(ps!=null) {
			 try {
				 ps.close();
			 }catch(SQLException e) {}
			}
			if(conn != null) {
				try {
					conn.close();
				}catch(SQLException e) {}
			}
		
		}
		return insertCount;
	}
}

RoleDao.java (Search one, add one, Search All)

 

package kr.or.connect.jdbcexam.dto;

public class Role {
	private Integer roleId;
	private String description;
	public Integer getRoleId() {
		return roleId;
	}
	
	public Role(Integer roleId, String description) {
		super();
		this.roleId = roleId;
		this.description = description;
	}
	
	public void setRoleId(Integer roleId) {
		this.roleId = roleId;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	@Override
	public String toString() {
		return "Role [roleId=" + roleId + ", description =" + description + "]";
	}
}

Role.java(여기저기 잘 쓰인다.)

 

package kr.or.connect.jdbcexam;
import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.jdbcexam.dto.Role;
public class JDBCExam1 {
	public static void main(String[] args) {
		RoleDao dao = new RoleDao();
		Role role = dao.getRole(500);
		System.out.println(role.toString());
	}
}

JDBCExam1 500th role_id

 

package kr.or.connect.jdbcexam;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.jdbcexam.dto.Role;

public class JDBCExam2 {
	public static void main(String[] args) {
		int roleId = 500;
		String description = "CTO";
		
		Role role = new Role(roleId, description);
		
		RoleDao dao = new RoleDao();
		int insertCount = dao.addRole(role);
		
		System.out.println(insertCount);
	}
}

JDBCExam2 500th role_id CTO

 

package kr.or.connect.jdbcexam;

import java.util.ArrayList;
import java.util.List;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.jdbcexam.dto.Role;


public class JCDBExam3 {

	public static void main(String[] args) {
		RoleDao dao = new RoleDao();
		List<Role> list = dao.getRoles();
		
		for(Role role : list) {
			System.out.println(role);
		}

	}

}

JDBCExam3 리스트 받아오기