【Java-DB】데이터베이스 연결확인 (MySQL/Oracle)

1. 설명 

안녕하세요 코이킹입니다. 
이 포스트에서 다룰 예제는 Java를 사용하여 데이터베이스에 연결이 가능한지 확인하는 코드가 되겠습니다. 

예제에서 사용할 MySQL의 설치와 환경설정은 이곳을 참고하시면 되며, 
Oracle의 설치와 환경설정은 이곳을 참고하시면 됩니다. 

 

2. 소스코드

- Gradle 의존성 추가

implementation group: 'commons-dbcp', name: 'commons-dbcp', version: '1.4'
implementation group: 'mysql', name: 'mysql-connector-java', version: '5.1.6'
implementation group: 'com.oracle.database.jdbc', name: 'ojdbc6', version: '11.2.0.4'

- 메서드 

	public void setDBParam(String dbDriverClassName, String dbUrl, String dbName, String dbId,
			String dbPw, String dbMaxActive, String dbMaxIdle, String dbMinIdle, String dbMaxWait,
			String dbTestQuery) {
		
		this.dbDriverClassName = dbDriverClassName;
		this.dbUrl = dbUrl;
		this.dbName = dbName;
		this.dbId = dbId;
		this.dbPw = dbPw;
		this.dbMaxActive = dbMaxActive;
		this.dbMaxIdle = dbMaxIdle;
		this.dbMinIdle = dbMinIdle;
		this.dbMaxWait = dbMaxWait;
		this.dbTestQuery = dbTestQuery;
		System.out.println(toString());
	}

	public void initConnection() {
		try {

			Class.forName(dbDriverClassName);

			GenericObjectPool conPool = new GenericObjectPool(null);
			conPool.setMaxActive(Integer.parseInt(dbMaxActive));
			conPool.setMaxIdle(Integer.parseInt(dbMaxIdle));
			conPool.setMinIdle(Integer.parseInt(dbMinIdle));
			conPool.setMaxWait(Integer.parseInt(dbMaxWait));
			conPool.setTimeBetweenEvictionRunsMillis(3600000);
			conPool.setMinEvictableIdleTimeMillis(1800000);
			conPool.setTestOnBorrow(true);

			String setUrl = dbUrl + dbName ;
			ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(setUrl, dbId, dbPw);

			PoolableConnectionFactory pcf = new PoolableConnectionFactory(
					connectionFactory,  // ConnectionFactory
					conPool,            // GenericObjectPool
					null,               // KeyedObjectPoolFactory 
					dbTestQuery,        // 커넥션이 유효한지 테스트하는 쿼리 DB별로 다른 쿼리를 써야한다.
					false,              // read only 미설정
					false);             // auto commit 미설정

			PoolingDriver driver = new PoolingDriver();
			driver.registerPool("conn", conPool);

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

	public Connection getConnection() {
		Connection con = null;

		try {
			con = DriverManager.getConnection("jdbc:apache:commons:dbcp:conn");
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		return con;
	}

- 메인 

public class DB_01_CheckConnection {
	
	static FileUtil fu = FileUtil.getInstance();
	static DBUtil du = DBUtil.getInstance();
	
	public static void main(String[] args) {
		try {
			for (String str : args ) {
				System.out.println("Param : "+str);
				connectionTest(str);
			}

	
			System.exit(0);
			
		} catch (Exception e) {
			e.printStackTrace();
			System.exit(1);
		}
	}
	
	
    public static void connectionTest(String conInfoFilePath) {
    	
		System.out.println("---------- DB Connection Test ----------");
		List<String> conInfos = fu.readPerLine(new File(conInfoFilePath));
		
		for (String str : conInfos) {
			System.out.println("ConInfo : "+str);
		}
		
		du.setDBParam(
				conInfos.get(0),
				conInfos.get(1),
				conInfos.get(2),
				conInfos.get(3),
				conInfos.get(4),
				conInfos.get(5),
				conInfos.get(6),
				conInfos.get(7),
				conInfos.get(8),
				conInfos.get(9)
				);
		
		du.initConnection();
		Connection con = du.getConnection();
		System.out.println("Connection Info = "+con);
		System.out.println();
		du.freeConnection(con);
    }
}

- DB 접속 정보

# MySQL 정보
com.mysql.jdbc.Driver
jdbc:mysql://localhost:3306/
board_test?serverTimezone=UTC
root
1234
10
10
5
5
SELECT 1

# Oracle 정보
oracle.jdbc.driver.OracleDriver
jdbc:oracle:thin:@192.168.99.100:1521:
xe
DAVID
1111
10
10
5
5
SELECT 1 FROM DUAL

 

3. 실행결과【Windows(이클립스)】

4. 전체 코드

https://github.com/leeyoungseung/template-java