JAVA - Postgresql 연동 및 조회 쿼리 실행 예제 (JDBC, postgresql, select query 예제)

 

 

 

 

 

자바 프로그램을 개발하면서 DB 연동을 하는 일은 아주 많이 있습니다.

 

오늘은 자바에서 Postgresql 연동하는 방법에  대해 알아보도록 하겠습니다.

DB 연결부터 Select 쿼리 실행하는 예제를 살펴보겠습니다.

 

1. 라이브러리

 

우선 라이브러리를 적용해야 합니다.

jar파일이나 dependency를 사용하여 적용합니다.

 

postgresql-42.2.13.jar

 

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.13</version>
</dependency>

 

 

2. DB 연결 

 

DB connection 예제입니다.

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 
 
public class PostgresqlTest {
        
    // DB Driver
    String dbDriver = "org.postgresql.Driver";
    
    // DB URL
    // IP:PORT/스키마 
    String dbUrl = "jdbc:postgresql://127.0.0.1:54321/dbName";
    
    // DB ID/PW
    String dbUser = "userId";
    String dbPassword = "userPassword";
    
    
    Connection dbconn = null;
    
    
    public void dbConnection()
    {
        Connection connection = null;
        
        try 
        {
            Class.forName(dbDriver);
            connection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
            dbconn = connection;
            
            System.out.println("DB Connection [성공]");
        } 
        catch (SQLException e) 
        {
            e.printStackTrace();
        } 
        catch (ClassNotFoundException e) 
        {
            e.printStackTrace();
        }
    }
 
}

 

 

 

반응형

 

 

3. Select 예제

 

DB connection 후 연결한 DB의 데이터를 select 할 수 있는 함수가 포함된 예제입니다.

쿼리를 작성하여 호출해보시기 바랍니다.

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
public class PostgresqlTest {
        
    // DB Driver
    String dbDriver = "org.postgresql.Driver";
    
    // DB URL
    // IP:PORT/스키마 
    String dbUrl = "jdbc:postgresql://127.0.0.1:54321/dbName";
    
    // DB ID/PW
    String dbUser = "userId";
    String dbPassword = "userPassword";
    
    
    Connection dbconn = null;
    
    
    public void dbConnection()
    {
        Connection connection = null;
        
        try 
        {
            Class.forName(dbDriver);
            connection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
            dbconn = connection;
            
            System.out.println("DB Connection [성공]");
        } 
        catch (SQLException e) 
        {
            e.printStackTrace();
        } 
        catch (ClassNotFoundException e) 
        {
            e.printStackTrace();
        }
    }
    
 
    public void dbClose()
    {
        try 
        {
            if(dbconn != null) 
            {
            dbconn.close();
            dbconn = null;
            System.out.println("DB Close [성공]");
            }
        } 
        catch (SQLException e) 
        {
            e.printStackTrace();
        }
    }
    
 
    
    public List<Object> selectSample(String sql) {
        
        Statement st = null;
 
        Map<String, Object> tempMap = new HashMap<String, Object>();
        List<Object> resultList = new ArrayList<Object>();
        
        try 
        {
            st = dbconn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            
            while (rs.next()) 
            {
                for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) 
                {
                    tempMap.put(rs.getMetaData().getColumnName(i+1), rs.getString(rs.getMetaData().getColumnName(i+1)));
                }
                
                resultList.add(tempMap);
                tempMap = new HashMap<>();    // tempMap reset
            }
            
            rs.close(); st.close();
        }
        catch (SQLException se1) 
        {
            se1.printStackTrace();
        }
        catch (Exception ex) 
        {
            ex.printStackTrace();
        }
        finally
        {
            try 
            {
                if (st != null)
                {
                    st.close();
                }
            } 
            catch (SQLException se2) 
            { 
                se2.printStackTrace();
            }
        }
        
        return resultList;
    }
    
}