jdbc_result_set.java

package com.labex;

import java.sql.*;

public class JDBCTest {
   // JDBC driver name
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   //database url
   static final String DB_URL = "jdbc:mysql://localhost/example";

   //  database user name
   static final String USER = "root";
   // database user password
   static final String PASS = "";

   public static void main(String[] args) {
       Connection conn = null;
       PreparedStatement stmt = null;
       try{
           // load driver class
           Class.forName("com.mysql.jdbc.Driver");

           // connect to database
           System.out.println("Connecting to database...");
           conn = DriverManager.getConnection(DB_URL,USER,PASS);

           // execute query
           System.out.println("Creating statement...");

           String sql = "UPDATE students set age=? WHERE id=?";
           stmt = conn.prepareStatement(sql);
           // bind 22 to age, 1 is index of age in param list.
           stmt.setInt(1,22);
           // bind 1 to id, 2 is index of id in param list.
           stmt.setInt(2,1);

           // update student age to 22 whose id is 1
           int rows = stmt.executeUpdate();
           System.out.println("Affected rows: " + rows );

          // search all records
          sql = "SELECT id, name, age FROM students";
           ResultSet rs = stmt.executeQuery(sql);

           // get result set
           while(rs.next()){
               // retrive data
               int id  = rs.getInt("id");
               int age = rs.getInt("age");
               String name = rs.getString("name");

               // print result
               System.out.print("ID: " + id);
               System.out.print(", Age: " + age);
               System.out.print(", Name: " + name);
               System.out.println();
           }
           // release resource
           rs.close();
           stmt.close();
           conn.close();
       }catch(SQLException se){
           // JDBC exception handle
           se.printStackTrace();
       }catch(Exception e){
           // Class.forName error
           e.printStackTrace();
       }finally{
           // close resource
           try{
               if(stmt!=null)
                   stmt.close();
           }catch(SQLException se2){
           }
           try{
               if(conn!=null)
                   conn.close();
           }catch(SQLException se){
               se.printStackTrace();
           }
       }
       System.out.println("Goodbye!");
   }
}