JAVA - Mysql 연동 및 조회 쿼리 실행 예제 (JDBC, mysql connector, select query 예제)

 

 

 

 

 

mysql

 

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

이전과 동일하게 DB 연결과 Select 쿼리 실행하는 예제를 살펴보겠습니다.

 

 

1. 라이브러리

 

먼저 라이브러리를 적용합니다.

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

 

mysql-connector-java-8.0.26.jar

 

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>

 

2. DB 연결 

 

DB connection 예제입니다.

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
 
public class MysqlTest {
        
    // DB Driver
    String dbDriver = "com.mysql.jdbc.Driver";
    
    // DB URL
    // IP:PORT/스키마 
    String dbUrl = "jdbc:mysql://127.0.0.1:3306/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) 
        {
            System.out.println("DB Connection [실패]");
            e.printStackTrace();
        } 
        catch (ClassNotFoundException e) 
        {            System.out.println("DB Connection [실패]");
            e.printStackTrace();
        }
    }
}
    

 

 

 

반응형

 

 

3. Select 예제

 

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

postgresql과 동일한 함수로 사용할 수 있습니다.

 

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 MysqlTest {
        
    // DB Driver
    String dbDriver = "com.mysql.jdbc.Driver";
    
    // DB URL
    // IP:PORT/스키마 
    String dbUrl = "jdbc:mysql://127.0.0.1:3306/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) 
        {
            System.out.println("DB Connection [실패]");
            e.printStackTrace();
        } 
        catch (ClassNotFoundException e) 
        {
            System.out.println("DB Connection [실패]");
            e.printStackTrace();
        }
    }
    
 
    public void dbClose()
    {
        try 
        {
            if(dbconn != null) 
            {
                dbconn.close();
                dbconn = null;
                System.out.println("DB Close [성공]");
            }
        } 
        catch (SQLException e) 
        {
            System.out.println("DB Close [실패]");
            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);
            //System.out.println("Query : " + 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;
    }
    
}