JDBC

[jimin-kiim]

JDBC

• a Java API for communicating with database systems supporting SQL
• supports a variety of features for querying and updating data, and for retrieving query results
• supports metadata retrieval, such as querying about relations present in the database and the names and types of relation attributes.
• model for communicating with the database
  • Open a connection
  • Create a “statement” object
  • Execute queries using the statement object to send queries and fetch results
  • Exception mechanism to handle errors

    public static void JDBCexample(String dbid, String userid, String passwd) 
                { 
         try (Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@db.yale.edu:2000:univdb", userid, passwd); // 1. Open a connection 
                Statement stmt = conn.createStatement();
)  // 2. Create a “statement” object
         { 
                … Do Actual Work ….  
         }  
        catch (SQLException sqle) {   
            System.out.println("SQLException : " + sqle);  
         }  
    }

    executeUpdate

    try {
     
    stmt.executeUpdate(
"insert into instructor values('77987', 'Kim', 'Physics', 98000)");

    } catch (SQLException sqle)
{
   
    System.out.println("Could not insert tuple. " + sqle);

    }

    executeQuery

    ResultSet rset = stmt.executeQuery(
"select dept_name, avg (salary)
 
                                 from instructor
                                
                                 group by dept_name");

    while (rset.next()) {
       
    System.out.println(rset.getString("dept_name") + " " +
 rset.getFloat(2));
    
}

    prepareStatement

    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.setString(1, "88878");
    pStmt.executeUpdate();