Chapter 30. JDBC

목차

1. JDBC(Java Database Connectivity)

￭ JDBC란?

• 자바에서 데이터베이스에 접속할 수 있도록 하는 자바 AP
• 데이터베이스에서 자료를 쿼리하거나 업데이트하는 방법 제공
• JDBC 구조는 아래 이미지를 참조
  

  

￭ 데이터베이스 연결

• 데이터베이스와 연결을 위해DBConnectionMgr 클래스를 사용
• DBConnectionMgr 클래스에서 드라이버, 데이터베이스 주소, 유저, 비밀번호를 설정하면 됨
• MySQL의 포트번호는 3306
  

  

￭ DML(Data Manipulation Language)

• DML은 CRUD역할을 하는 Insert, Select, Update, Delete로 구성
• 자바에서 MySQL로 데이터베이스를 연결하여 해당 쿼리 실행 가능
• Select를 제외한 명령어는 executeUpdate()를 통해 실행 시 성공 횟수를 리턴하여 실행함
• Select명령을 실행 시 executeQuery()를 사용함

 

2. DBConnection

￭ DBConnectionMgr

package j23_database;

/**
 * Copyright(c) 2001 iSavvix Corporation (http://www.isavvix.com/)
 *
 *                        All rights reserved
 *
 * Permission to use, copy, modify and distribute this material for
 * any purpose and without fee is hereby granted, provided that the
 * above copyright notice and this permission notice appear in all
 * copies, and that the name of iSavvix Corporation not be used in
 * advertising or publicity pertaining to this material without the
 * specific, prior written permission of an authorized representative of
 * iSavvix Corporation.
 *
 * ISAVVIX CORPORATION MAKES NO REPRESENTATIONS AND EXTENDS NO WARRANTIES,
 * EXPRESS OR IMPLIED, WITH RESPECT TO THE SOFTWARE, INCLUDING, BUT
 * NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
 * FITNESS FOR ANY PARTICULAR PURPOSE, AND THE WARRANTY AGAINST
 * INFRINGEMENT OF PATENTS OR OTHER INTELLECTUAL PROPERTY RIGHTS.  THE
 * SOFTWARE IS PROVIDED "AS IS", AND IN NO EVENT SHALL ISAVVIX CORPORATION OR
 * ANY OF ITS AFFILIATES BE LIABLE FOR ANY DAMAGES, INCLUDING ANY
 * LOST PROFITS OR OTHER INCIDENTAL OR CONSEQUENTIAL DAMAGES RELATING
 * TO THE SOFTWARE.
 *
 */

import java.sql.*;
import java.util.Properties;
import java.util.Vector;

/**
 * Manages a java.sql.Connection pool.
 *
 * @author  Anil Hemrajani
 */
public class DBConnectionMgr {

    private Vector connections = new Vector(10);
    private String _driver = "com.mysql.cj.jdbc.Driver",
    _url = "jdbc:mysql://127.0.0.1:3306/chatting", //jdbc프로토콜 통신을 함
    _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;
    }

    public void setOpenConnectionCount(int count) {
        _openConnections = count;
    }

    public void setEnableTrace(boolean enable) {
        _traceOn = enable;
    }

    /** Returns a Vector of java.sql.Connection objects */
    public Vector getConnectionList() {
        return connections;
    }

    /** Opens specified "count" of connections and adds them to the existing pool */
    public synchronized void setInitOpenConnections(int count)
            throws SQLException {
        Connection c = null;
        ConnectionObject co = null;

        for (int i = 0; i < count; i++) {
            c = createConnection();
            co = new ConnectionObject(c, false);

            connections.addElement(co);
            trace("ConnectionPoolManager: Adding new DB connection to pool (" + connections.size() + ")");
        }
    }

    /** Returns a count of open connections */
    public int getConnectionCount() {
        return connections.size();
    }

    /** Returns an unused existing or new connection.  */
    public synchronized Connection getConnection()
            throws Exception {
        if (!initialized) {
            Class c = Class.forName(_driver);
            DriverManager.registerDriver((Driver) c.newInstance());

            initialized = true;
        }

        Connection c = null;
        ConnectionObject co = null;
        boolean badConnection = false;

        for (int i = 0; i < connections.size(); i++) {
            co = (ConnectionObject) connections.elementAt(i);

            // If connection is not in use, test to ensure it's still valid!
            if (!co.inUse) {
                try {
                    badConnection = co.connection.isClosed();
                    if (!badConnection)
                        badConnection = (co.connection.getWarnings() != null);
                } catch (Exception e) {
                    badConnection = true;
                    e.printStackTrace();
                }

                // Connection is bad, remove from pool
                if (badConnection) {
                    connections.removeElementAt(i);
                    trace("ConnectionPoolManager: Remove disconnected DB connection #" + i);
                    continue;
                }

                c = co.connection;
                co.inUse = true;

                trace("ConnectionPoolManager: Using existing DB connection #" + (i + 1));
                break;
            }
        }

        if (c == null) {
            c = createConnection();
            co = new ConnectionObject(c, true);
            connections.addElement(co);

            trace("ConnectionPoolManager: Creating new DB connection #" + connections.size());
        }

        return c;
    }

    /** Marks a flag in the ConnectionObject to indicate this connection is no longer in use */
    public synchronized void freeConnection(Connection c) {
        if (c == null)
            return;

        ConnectionObject co = null;

        for (int i = 0; i < connections.size(); i++) {
            co = (ConnectionObject) connections.elementAt(i);
            if (c == co.connection) {
                co.inUse = false;
                break;
            }
        }

        for (int i = 0; i < connections.size(); i++) {
            co = (ConnectionObject) connections.elementAt(i);
            if ((i + 1) > _openConnections && !co.inUse)
                removeConnection(co.connection);
        }
    }

    public void freeConnection(Connection c, PreparedStatement p, ResultSet r) {
        try {
            if (r != null) r.close();
            if (p != null) p.close();
            freeConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void freeConnection(Connection c, Statement s, ResultSet r) {
        try {
            if (r != null) r.close();
            if (s != null) s.close();
            freeConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void freeConnection(Connection c, PreparedStatement p) {
        try {
            if (p != null) p.close();
            freeConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void freeConnection(Connection c, Statement s) {
        try {
            if (s != null) s.close();
            freeConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /** Marks a flag in the ConnectionObject to indicate this connection is no longer in use */
    public synchronized void removeConnection(Connection c) {
        if (c == null)
            return;

        ConnectionObject co = null;
        for (int i = 0; i < connections.size(); i++) {
            co = (ConnectionObject) connections.elementAt(i);
            if (c == co.connection) {
                try {
                    c.close();
                    connections.removeElementAt(i);
                    trace("Removed " + c.toString());
                } catch (Exception e) {
                    e.printStackTrace();
                }

                break;
            }
        }
    }

    private Connection createConnection()
            throws SQLException {
        Connection con = null;

        try {
            if (_user == null)
                _user = "";
            if (_password == null)
                _password = "";

            Properties props = new Properties();
            props.put("user", _user);
            props.put("password", _password);

            con = DriverManager.getConnection(_url, props);
        } catch (Throwable t) {
            throw new SQLException(t.getMessage());
        }

        return con;
    }

    /** Closes all connections and clears out the connection pool */
    public void releaseFreeConnections() {
        trace("ConnectionPoolManager.releaseFreeConnections()");

        Connection c = null;
        ConnectionObject co = null;

        for (int i = 0; i < connections.size(); i++) {
            co = (ConnectionObject) connections.elementAt(i);
            if (!co.inUse)
                removeConnection(co.connection);
        }
    }

    /** Closes all connections and clears out the connection pool */
    public void finalize() {
        trace("ConnectionPoolManager.finalize()");

        Connection c = null;
        ConnectionObject co = null;

        for (int i = 0; i < connections.size(); i++) {
            co = (ConnectionObject) connections.elementAt(i);
            try {
                co.connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

            co = null;
        }

        connections.removeAllElements();
    }

    private void trace(String s) {
        if (_traceOn)
            System.err.println(s);
    }

}

class ConnectionObject {
    public java.sql.Connection connection = null;
    public boolean inUse = false;

    public ConnectionObject(Connection c, boolean useFlag) {
        connection = c;
        inUse = useFlag;
    }
}

 

3. 예시

￭ UserInsert

package j23_database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import usermanagement.entity.User;

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 {
			// database에 접속
			connection = pool.getConnection();

			sql = "insert into user_mst\r\n"
					+ "values (0, ?, ?, ?, ?)";
            // editor에 쿼리 작성하는 것과 같음
			preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            // values에 값을 넣는 것으로 작은따옴표까지 찍어서 만들어줌
			preparedStatement.setString(1, user.getUsername());
			preparedStatement.setString(2, user.getPassword());
			preparedStatement.setString(3, user.getName());
			preparedStatement.setString(4, user.getEmail());

            // insert, update, delete 명령 실행
            // 적용된 행의 개수가 리턴됨
			successCount = preparedStatement.executeUpdate();
			
			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();
            // up casting되어 있어서 기능들을 사용하지 못하기 때문에 다시 down casting을 해줌
			User user = (User) map.get("user");
			List<Integer> roles = (List<Integer>) map.get("roles");

			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((2 * i) + 1, roles.get(i));
			preparedStatement.setInt((2 * i) + 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("q")
				.password("1234")
				.name("q")
				.email("q@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("쿼리 실행 성공 : " + successCount + "건");
	}
}

• 결과
  

  

￭ RoleInsert

package j23_database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class RoleInsert {
	// 싱글톤 디자인 패턴이므로 private로 변수 선언
	private DBConnectionMgr pool;
	
	// RoleInsert클래스 생성 시에 싱글톤으로 DBConnectionMgr이 생성되도록 함
	public RoleInsert() {
		pool = DBConnectionMgr.getInstance();
	}
	
	public int saveRole(String roleName) {
		// 성공 횟수를 알기 위해 int 타입의 변수 선언 및 초기화
		int successCount = 0;

		String sql = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		
		
		try {
			// DataBase에 접속
			con = pool.getConnection();
			// 변수 sql에 쿼리문 대입
			sql = "insert into role_mst values (0, ?)";
			// editor에 쿼리문을 작성하고 자동적으로 key값을 넣는 것
			pstmt = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			// 1번 파라미터에 매개변수roleName을 입력
			pstmt.setString(1, roleName);
			
			// 실행 후 성공한 쿼리의 개수를 대입
			successCount = pstmt.executeUpdate();
			
			int newKey = 0;
			
			// 자동적으로 넣은 key값 찾아 데이터를 ResultSet타입의 변수에 대입
			ResultSet rs = pstmt.getGeneratedKeys();
			// rs의 다음 데이터가 있으면 rs의 1번 파라미터(id)의 값을 newKey의 변수에 대입 
			if(rs.next()) {
				newKey = rs.getInt(1);
			}
			// newKey가 0이 아니면 newKey값 출력, 0이면 키가 생성되지 않음
			System.out.println(newKey != 0 ? "새로운 키값 : " + newKey : "키가 생성되지 않음");
			
		// 예외처리
		} catch (Exception e) {
			e.printStackTrace();
		}
		// 성공횟수를 리턴
		return successCount;
	}
	
	public static void main(String[] args) {
		// 생성자를 통해 싱글톤 생성
		RoleInsert roleInsert = new RoleInsert();
		
		// 매개변수 ROLE_TESTER를 입력하여 saveRole 실행 후 리턴된 성공횟수를 대입
		int successCount = roleInsert.saveRole("ROLE_TESTER");
		System.out.println("insert 성공 건수 : " + successCount);
	}
	
}

• 결과
  

  

￭ UserSelect

package j23_database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import usermanagement.entity.User;

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);
	}
}