Skip to main content

How to Use ParameterMetaData to Learn Parameter Information?

--
In the following example, it shows various methods of the ParameterMetaData object are used to display information about the type and mode of the parameters that are contained within a stored procedure. 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

public class JDBCPrepareMetaData {

    private static final String DBURL = 
            "jdbc:mysql://localhost:3306/mydb?user=usr&password=sql"
             +"&useUnicode=true&characterEncoding=UTF-8";
    private static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
  
    static {
        try {
            Class.forName(DBDRIVER).newInstance();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    private static Connection getConnection() 
    {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(DBURL);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void createEmployees()
    {
        Connection con = getConnection();
        Statement stmt =null;
        String createString;
        createString = "CREATE TABLE  `mydb`.`employees` ("+
          "`EmployeeID` int(10) unsigned NOT NULL default '0',"+
          "`Name` varchar(45) collate utf8_unicode_ci NOT NULL default '',"+
        "`Office` varchar(10) collate utf8_unicode_ci NOT NULL default '',"+
          "`CreateTime` timestamp NOT NULL default CURRENT_TIMESTAMP,"+
          "PRIMARY KEY  (`EmployeeID`)"+
          ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";            
        try {
            stmt = con.createStatement();
            stmt.executeUpdate(createString);
        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }
    private static void dropEmployees()
    {
        Connection con = getConnection();
        Statement stmt =null;
        String createString;
        createString = "DROP TABLE IF EXISTS `mydb`.`employees`;";            
        try {
            stmt = con.createStatement();
            stmt.executeUpdate(createString);
        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }
    
    private static void insertEmployee() 
    {
        Connection con = getConnection();
        Statement stmt = null;

        try {
            stmt = con.createStatement();  

            stmt.addBatch("INSERT INTO employees(EmployeeID, Name, Office) "  
                   + "VALUES(1001, 'David Walker', 'HQ101')");

            stmt.addBatch("INSERT INTO employees(EmployeeID, Name, Office) "  
                    + "VALUES(1002, 'Paul Walker', 'HQ202')");            

            stmt.addBatch("INSERT INTO employees(EmployeeID, Name, Office) "  
                   + "VALUES(1003, 'Scott Warner', 'HQ201')");            

            int [] updateCounts = stmt.executeBatch();

        } catch (SQLException e) {
            System.err.println("SQLException: " + e.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }
    private    static void dropProcedure() {
        Connection con = getConnection();
        Statement stmt =null;
        try {
            stmt = con.createStatement();
            stmt.execute("DROP PROCEDURE IF EXISTS `mydb`.`WhoAreThey`");
        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
        
    }
    
    private    static void createProcedure() {
        Connection con = getConnection();
        Statement stmt =null;
        try {
            stmt = con.createStatement();
               stmt.execute("CREATE PROCEDURE `WhoAreThey`(" +
                       "OUT error VARCHAR(128)," +
                       "IN office VARCHAR(10)) " +
                       "BEGIN "+
                       "SET error = NULL; "+
                       "IF office IS NULL THEN "+
                       "SET error = 'You need to pass in an office number'; "+
                       "ELSE "+
                       "  SELECT EmployeeID, Name FROM employees " + 
                       " WHERE office = office; "+
                       "END IF; "+
                       "END");

        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
        
    }

    public static void getParameterMetaData() {
        Connection con = getConnection();
        PreparedStatement stmt = null;

        try {
            stmt = con.prepareStatement("INSERT INTO " +
                  employees(EmployeeID, Name, Office) " + 
                    "VALUES(?,?,?)");  

            ParameterMetaData pmd = stmt.getParameterMetaData();
            int count = pmd.getParameterCount();
            for (int i = 1; i <= count; i++) {
                System.out.println("Name: " + pmd.getParameterType(i));
                System.out.println("TYPE: " + pmd.getParameterTypeName(i));
                String mode = "";
                switch(pmd.getParameterMode(i)) {
                     case ParameterMetaData.parameterModeIn:
                         mode = "IN";
                         break;
                     case ParameterMetaData.parameterModeInOut:
                         mode = "IN/OUT";
                         break;
                     case ParameterMetaData.parameterModeOut:
                         mode = "OUT";
                         break;
                     case ParameterMetaData.parameterModeUnknown:
                         mode = "UNKNOWN";
                         break;
                
                }
                System.out.println("MODE: " + mode);
            }

        } catch (SQLException e) {
            System.err.println("SQLException: " + e.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
        
    }

    public static void getProcedureParameterMetaData() {
        Connection con = getConnection();
        CallableStatement cs = null;
        try {
            cs = con.prepareCall("{call WhoAreThey(?,?)}");
            ParameterMetaData pmd = cs.getParameterMetaData();
            int count = pmd.getParameterCount();
            for (int i = 1; i <= count; i++) {
                System.out.println("Name: " + pmd.getParameterType(i));
                System.out.println("TYPE: " + pmd.getParameterTypeName(i));
                String mode = "";
                switch(pmd.getParameterMode(i)) {
                     case ParameterMetaData.parameterModeIn:
                         mode = "IN";
                         break;
                     case ParameterMetaData.parameterModeInOut:
                         mode = "IN/OUT";
                         break;
                     case ParameterMetaData.parameterModeOut:
                         mode = "OUT";
                         break;
                     case ParameterMetaData.parameterModeUnknown:
                         mode = "UNKNOWN";
                         break;
                
                }
                System.out.println("MODE: " + mode);
            }
            
        } catch (SQLException e) {
            System.err.println("SQLException: " + e.getMessage());
        }
        finally {
            if (cs != null) {
                try {
                    cs.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
        
    }

    public static void main(String[] args) {
        dropProcedure();
        dropEmployees();
        createEmployees();
        insertEmployee();
        getParameterMetaData();
        System.out.println("Call Procedure.....");
        createProcedure();
        getProcedureParameterMetaData();
    }

}
Labels:

Comments

Post a Comment

Popular posts from this blog

Advantages & Disadvantages of Synchronous / Asynchronous Communications?

  Asynchronous Communication Advantages: Requests need not be targeted to specific server. Service need not be available when request is made. No blocking, so resources could be freed.  Could use connectionless protocol Disadvantages: Response times are unpredictable. Error handling usually more complex.  Usually requires connection-oriented protocol.  Harder to design apps Synchronous Communication Advantages: Easy to program Outcome is known immediately  Error recovery easier (usually)  Better real-time response (usually) Disadvantages: Service must be up and ready. Requestor blocks, held resources are “tied up”.  Usually requires connection-oriented protocol
Post a Comment
Continue Reading >>>>

WebSphere MQ Interview Questions

What is MQ and what does it do? Ans. MQ stands for MESSAGE QUEUEING. WebSphere MQ allows application programs to use message queuing to participate in message-driven processing. Application programs can communicate across different platforms by using the appropriate message queuing software products. What is Message driven process? Ans . When messages arrive on a queue, they can automatically start an application using triggering. If necessary, the applications can be stopped when the message (or messages) have been processed. What are advantages of the MQ? Ans. 1. Integration. 2. Asynchrony 3. Assured Delivery 4. Scalability. How does it support the Integration? Ans. Because the MQ is independent of the Operating System you use i.e. it may be Windows, Solaris,AIX.It is independent of the protocol (i.e. TCP/IP, LU6.2, SNA, NetBIOS, UDP).It is not required that both the sender and receiver should be running on the same platform What is Asynchrony? Ans. With messag
11 comments
Continue Reading >>>>

XML Binding with JAXB 2.0 - Tutorial

Java Architecture for XML Binding (JAXB) is an API/framework that binds XML schema to Java representations. Java objects may then subsequently be used to marshal or unmarshal XML documents. Marshalling an XML document means creating an XML document from Java objects. Unmarshalling means creating creating a Java representation of an XML document (or, in effect, the reverse of marshaling). You retrieve the element and attribute values of the XML document from the Java representation. The JAXB 2.0 specification is implemented in JWSDP 2.0. JAXB 2.0 has some new features, which facilitate the marshalling and unmarshalling of an XML document. JAXB 2.0 also allows you to map a Java object to an XML document or an XML Schema. Some of the new features in JAXB 2.0 include: Smaller runtime libraries are required for JAXB 2.0, which require lesser runtime memory. Significantly, fewer Java classes are generated from a schema, compared to JAXB 1.0. For each top-level complexType, 2.0 generates a v
Post a Comment
Continue Reading >>>>