JDBC 코드 예시

# 모든 교수들의 이름, 학과 이름, 연봉 구하기

import java.sql.*;
import java.sql.SQLException;

public class jdbc {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:49670;database=largeDB;integratedSecurity=true";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement stmt = conn.createStatement();
            System.out.println("MS-SQL 서버 접속에 성공하였습니다.1");
            ResultSet rs = stmt.executeQuery("SELECT * FROM instructor");
            while( rs.next() ) {
                   String field1 = rs.getString("name");
                   String field2 = rs.getString("dept_name");
                   String field3 = rs.getString(3);
                   System.out.print(field1 + "\t");
                   System.out.print(field2 + "\t");
                   System.out.println(field3 );
                  }
            rs.close();
            stmt.close();   
            conn.close();
        } catch (ClassNotFoundException sqle) {
        	System.out.println("SQLException : " + sqle);
        	}
   }
}

 

# 학과 별 교수들의 평균 급여 구하기

import java.sql.*;
import java.sql.SQLException;

public class jdbc {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:49670;database=largeDB;integratedSecurity=true";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement stmt = conn.createStatement();
            System.out.println("MS-SQL 서버 접속에 성공하였습니다.1");
            String d_name; 
            float sal; 
            ResultSet rset= stmt.executeQuery("select dept_name, avg(salary) "
            					+ "from instructor group by dept_name"); 
            while (rset.next()) { 
            	d_name= rset.getString("dept_name"); 
            	sal= rset.getFloat(2); 
            	System.out.println(d_name+ " " + sal); 
            }
            
            rset.close();
            stmt.close();   
            conn.close();
        } catch (ClassNotFoundException sqle) {
        	System.out.println("SQLException : " + sqle);
        	}
	}
}

 

# insrtuctor 테이블에 null 값 있는 지 확인

import java.sql.*;
import java.sql.SQLException;

public class jdbc {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:49670;database=largeDB;integratedSecurity=true";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement stmt = conn.createStatement();
            System.out.println("MS-SQL 서버 접속에 성공하였습니다.1");
            
            ResultSet rs= stmt.executeQuery("select * from instructor"); 
            while (rs.next()) { 
            	 String field1 = rs.getString("name");
                 String field2 = rs.getString("dept_name");
                 Float field3 = rs.getFloat("salary");
            
                 if (rs.wasNull()) {
                	 System.out.println("Got null value");
                	 break;
                 }
                 System.out.print(field1 + "\t");
                 System.out.print(field2 + "\t");
                 System.out.println(field3);
            }
            rs.close();
            stmt.close();   
            conn.close();
        } catch (ClassNotFoundException sqle) {
        	System.out.println("SQLException : " + sqle);
        }
    }
}

 

# instructor 테이블에 투플(새로운 교수) 삽입 – Statement stmt = conn.createStatement()

import java.sql.*;
import java.sql.SQLException;

public class Test2{
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:49670;database=largeDB;integratedSecurity=true";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement stmt = conn.createStatement();
            System.out.println("MS-SQL 서버 접속에 성공하였습니다.1");
            
            stmt.executeUpdate("insert into instructor values('77987', 'Kim', 'Physics', 98000)"); 
            
            stmt.close();   
            conn.close();
        } catch (ClassNotFoundException sqle) {
        	System.out.println("SQLException : " + sqle);
        }
    }
}

 

# instructor 테이블에 투플(새로운 교수) 삽입 – PreparedStatement pStmt – conn.prepareStatement()

import java.sql.*;
import java.sql.SQLException;

public class Test2{
   public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:49670;database=largeDB;integratedSecurity=true";
            Connection conn = DriverManager.getConnection(connectionUrl);
            PreparedStatement pStmt = conn.prepareStatement("insert into instructor values(?,?,?,?)"); 
            pStmt.setString(1, "88877"); 
            pStmt.setString(2, "Perry"); 
            pStmt.setString(3, "Finance"); 
            pStmt.setInt(4, 125000); 
            pStmt.executeUpdate(); 
          
            pStmt.close();   
            conn.close();
        } catch (ClassNotFoundException sqle) {
        	System.out.println("SQLException : " + sqle);
        }
    }
}

# 교수 id가 70000보다 큰 교수를 구할 때 meta data(column name, column type)

import java.sql.*;
import java.sql.SQLException;

public class Test2{
   public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:49670;database=largeDB;integratedSecurity=true";
            Connection conn = DriverManager.getConnection(connectionUrl);
            Statement stmt = conn.createStatement();
            ResultSet rs= stmt.executeQuery("select * from instructor where id > 70000"); 
            ResultSetMetaData rsmd= rs.getMetaData(); 
            for(int i= 1; i<= rsmd.getColumnCount(); i++) { 
            	System.out.print(rsmd.getColumnName(i) + "\t"); 
            	System.out.println(rsmd.getColumnTypeName(i)); 
            } 
            conn.close();
        } catch (ClassNotFoundException sqle) {
        	System.out.println("SQLException : " + sqle);
        }
    }
}

결과 :
ID  varchar
name  varchar
dept_name  varchar
salary  numeric

 

# (null, null, "department", "%") col의 meta data(col name, type)

import java.sql.*;
import java.sql.SQLException;

public class Test2{
   public static void main(String[] args) throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String connectionUrl = "jdbc:sqlserver://localhost:49670;database=largeDB;integratedSecurity=true";
            Connection conn = DriverManager.getConnection(connectionUrl);
            DatabaseMetaData dbmd = conn.getMetaData(); 
            ResultSet rs = dbmd.getColumns(null, null, "department", "%"); 
            while(rs.next()) { 
            	System.out.print(rs.getString("COLUMN_NAME") + "\t"); 
            	System.out.println(rs.getString("TYPE_NAME")); 
            } 
            
            conn.close();
        } catch (ClassNotFoundException sqle) {
        	System.out.println("SQLException : " + sqle);
        }
   }
}

결과 :

dept_name varchar

building varchar

budget numeric