[백엔드개발자 준비하기] 자바와 데이터베이스 연결하기

데이터베이스와 연결할 수 있는 코드를 하나 따로 작성해야 함

 private Vector connections = new Vector(10);
    private String _driver = "com.mysql.cj.jdbc.Driver",
    _url = "jdbc:mysql://127.0.0.1:3306/chatting",
    _user = "root",
    _password = "root";
    
    private boolean _traceOn = false;
    private boolean initialized = false;
    private int _openConnections = 50;
    private static DBConnectionMgr instance = null;

    private DBConnectionMgr() {
    }

    /** Use this method to set the maximum number of open connections before
     unused connections are closed.
     */

    public static DBConnectionMgr getInstance() {
        if (instance == null) {
            synchronized (DBConnectionMgr.class) {
                if (instance == null) {
                    instance = new DBConnectionMgr();
                }
            }
        }

        return instance;
    }

[RoleInsert]

public class RoleInsert {

	private DBConnectionMgr pool;
	
	public RoleInsert() {
		pool = DBConnectionMgr.getInstance();
	}
	
	public int saveRole(String roleName) {
		int successCount = 0;
		
		String sql = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			con = pool.getConnection();
			sql = "insert into role_mst values (0, ?)";
			pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			pstmt.setString(1, roleName);
			
			successCount = pstmt.executeUpdate();
			
			int newKey = 0;
			
			rs = pstmt.getGeneratedKeys();
			if(rs.next()) {
				newKey = rs.getInt(1);
			}
			
			System.out.println(newKey != 0 ? "새로운 키값 : " + newKey : "키가 생성되지 않음");
			
//			e guzigoteun capslock hayoten doumi andeam
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con, pstmt, rs); 
		}
		
		return successCount;
	}
	
	public static void main(String[] args) {
		
		RoleInsert roleInsert = new RoleInsert();
		
		int successCount = roleInsert.saveRole("ROLE_TESTER");
		System.out.println("insert 성공 건수 : " + successCount);
	}
}

[UserInsert]

public class UserInsert {

	private DBConnectionMgr pool;

	public UserInsert() {
		pool = DBConnectionMgr.getInstance();
	}

	public int saveUser(User user) {
		int successCount = 0;

		String sql = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			connection = pool.getConnection();

			sql = "insert into user_mst\r\n" + "values (0, ?, ?, ?, ?)";

			preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

			preparedStatement.setString(1, user.getUsername());
			preparedStatement.setString(2, user.getPassword());
			preparedStatement.setString(3, user.getName());
			preparedStatement.setString(4, user.getEmail());

			successCount = preparedStatement.executeUpdate(); // insert, update, delete 명령 실행

			resultSet = preparedStatement.getGeneratedKeys();
			if (resultSet.next()) {
				System.out.println("이번에 만들어진 user_id key 값 : " + resultSet.getInt(1));
				user.setUserId(resultSet.getInt(1));
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return successCount;
	}

	public int saveRoles(Map<String, Object> map) {

		int successCount = 0;

		String sql = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;

		try {
			connection = pool.getConnection();

			List<Integer> roles = (List<Integer>) map.get("roles");
			User user = (User) map.get("user");

			sql = "insert into role_dtl values";

			for (int i = 0; i < roles.size(); i++) {
				sql += "(0, ?, ?)";
				if (i < roles.size() - 1) {
					sql += ',';
				}
			}

			preparedStatement = connection.prepareStatement(sql);

			for (int i = 0; i < roles.size(); i++) {

				preparedStatement.setInt((i * 2) + 1, roles.get(i));
				preparedStatement.setInt((i * 2) + 2, user.getUserId());

			}

			successCount = preparedStatement.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}

		return successCount;
	}

	public static void main(String[] args) {

		UserInsert userInsert = new UserInsert();

		User user = User.builder().username("eee").password("1234").name("eee").email("eee@gmail.com").build();

		int successCount = userInsert.saveUser(user);

		System.out.println("쿼리 실행 성공 : " + successCount + "건");

		System.out.println(user);

		//=========================================================
		
		List<Integer> roleIdList = new ArrayList();

		roleIdList.add(2);
		roleIdList.add(3);

		Map<String, Object> map = new HashMap<>();
		map.put("user", user);
		map.put("roles", roleIdList);

		successCount = userInsert.saveRoles(map);
		System.out.println(map);
		System.out.println("쿼리 실행 성공 : " + successCount + "건");
	}
}

[UserSelect]

public class UserSelect {

	private DBConnectionMgr pool;

	public UserSelect() {
		pool = DBConnectionMgr.getInstance();
	}

	public User findUserByUsername(String username) {
		User user = null;

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			con = pool.getConnection();

			String sql = "select user_id, username, password, name, email from user_mst where username = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);

			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				user = User.builder()
						.userId(rs.getInt(1))
						.username(rs.getString(2))
						.password(rs.getString(3))
						.name(rs.getString(4))
						.email(rs.getString(5))
						.build();
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con, pstmt, rs);
		}

		return user;
	}

	public static void main(String[] args) {
		UserSelect userSelect = new UserSelect();

		User user = userSelect.findUserByUsername("aaa");

		System.out.println(user);

	}
}