{"id":67,"date":"2018-07-10T10:35:33","date_gmt":"2018-07-10T10:35:33","guid":{"rendered":"http:\/\/itp16.epgpbooks.inflibnet.ac.in\/?post_type=chapter&p=67"},"modified":"2019-05-15T07:12:13","modified_gmt":"2019-05-15T07:12:13","slug":"jdbc2","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/itp16\/chapter\/jdbc2\/","title":{"rendered":"JDBC2"},"content":{"raw":"
\"epgp<\/a>\r\n<\/span><\/div>\r\n
\r\n\r\n \r\n\r\n \r\n\r\nExecuting aprecompiled SQL using Prepared Statement<\/strong>\r\n\r\n \r\n

An instance of Statement is used to execute any kind of SQL. There is a \u00a0sub-interface \u00a0of \u00a0Statement called PreparedStatement, which is used to execute precompiled SQL. An instance of PreparedStatement is also created from the instance of Connection by using the following methods of Connection\u00a0\u00a0 interface:<\/p>\r\n \r\n\r\nPreparedStatement prepareStatement(String sql)\r\n\r\nPreparedStatement prepareStatement(String sql, int rstype, int rsconcur)\r\n\r\n \r\n

Here unlike the Statement the SQL is specified at the time of creating the instance of PreparedStatement. The SQL, which is passed as parameter can have place holders which are introduced by using the \u2019 ?\u2019 character within the SQL string passed as parameter. These place holders are referred to as parameters for the PreparedStatement.<\/p>\r\n \r\n\r\nPreparedStatement ps = con.prepareStatement(\"select * from account where acno=? and type = ?\");\r\n\r\nPreparedStatement ps = con.prepareStatement(\"insert into account values(?,?,?,?)\");\r\n\r\n \r\n

The first \u2019 ?\u2019 in the SQL string is the first parameter of the PreparedStatement, and so on. So, \u00a0the number of parameters in a\u00a0 PreparedSTatement will be\u00a0 the number of \u2019 ?\u2019\u00a0 characters used in the SQL string. The parameters can only be used in place for values in the SQL statement. These \u00a0parameter values need to be setup before the precompiled SQL corresponding to the PreparedStatement can be executed.<\/p>\r\n \r\n

Now the PreparedStatement interface has additional methods for executing the SQL. These are the same three methods as in Statement interface, but without any parameters. In Statement interface the parameter to the three execute methods was the SQL to be executed. In case of PreparedStatement this has already been specified at the time of creating the instance using the parameter in the prepareStatement() method. So, the methods for executing the precompiled SQL in a PreparedStatement are:<\/p>\r\n \r\n\r\nboolean execute() int executeUpdate()\r\n\r\nResultSet executeQuery()\r\n\r\n \r\n

Before invoking any of the execute methods on an instance of PreparedStatement, the values of all the parameters in the PreparedStatement, which are indicated using the \u2019 ?\u2019 character, must be initialized. To set the values for the parameters in a PreparedStatement, the PreparedStatement interface has a set of methods. The method to be used depends on the type (SQL type) of the parameter(place holder) whose value is to be set. The general form of these methods is as given below:<\/p>\r\n \r\n

void setXXX (int parno, XXX value)<\/span><\/p>\r\n \r\n

where XXX is the type of parameter. eg. If the SQL type of the parameter whose value is to be set is a VARCHAR, then String type in Java would be more appropriate. and we would use the method<\/span><\/p>\r\n \r\n

void set String ( int param , String value )<\/span><\/p>\r\n

Similarly there are other methods for setting parameter values of other types.<\/span><\/p>\r\n \r\n

So we first use the appropriate setXXX() methods to set values of the parameters in the PreparedStatement and then use the execute method \u00a0on \u00a0the \u00a0instance \u00a0of \u00a0PreparedStatement \u00a0to execute the precompiled SQL.<\/span><\/p>\r\n \r\n

Executing Stored Procedures and functions using CallableStatement<\/strong><\/p>\r\n \r\n

There is also\u00a0 a sub-interface\u00a0 of PreparedStatement\u00a0 called the\u00a0 CallableStatement, this\u00a0 is used to execute stored procedures and functions. The instance of CallableStatement is created by the method on Connection as<\/span><\/p>\r\n \r\n

CallableStatement prepareCall(String callstatement)<\/span><\/p>\r\n

CallableStatement prepareCall(String callstatement,int rstype,int rsconcur)<\/span><\/p>\r\n \r\n

The CallableStatement inherits methods from the PreparedStatement and some more additional methods. let us look at how to create an instance of CallableStatement. In the above methods \u00a0the syntax for the callstatement is:<\/span><\/p>\r\n \r\n

{ [?] = call <proc-name or function name> (?,...} }<\/span><\/p>\r\n \r\n

eg. if it is a stored procedure which is to be invoked we create CallableStatement as<\/span><\/p>\r\n \r\n

CallableStatement cs = con.prepareCall(\"{ call myproc(?,?,?) }\");<\/span><\/p>\r\n \r\n

to execute a stored procedure or<\/span><\/p>\r\n \r\n

CallableStatement cs = con.prepareCall(\"{ ? = call myfunctino(?, ?, ?) }\");<\/span><\/p>\r\n \r\n

to execute a function, which returns some value.<\/span><\/p>\r\n \r\n

In the above stored procedure invocation we assume that \u201cmyproc\u201d stored procedure has 3 parameters required. Now, the parameters in storedprocedure are of 3 types, ie. there are some parameters which are IN parameters, some are OUT parameters and some are INOUT parameters. This is decided when the procedure has been created in the database. The IN parameters and the IN- OUT parameters need to be initialized before using the execute method on the instance of CallableStatement. This initialization can be done similar to the PreparedStatement. ie. by calling the setXXX methods corresponding to the parameters, which are IN and INOUT types. Now before calling the execute methods the OUT parameters need to be registered, this can be done by using the method:<\/span><\/p>\r\n \r\n

void registerOut Parameter(int parno, int sqldatatype)<\/span><\/p>\r\n \r\n

The value in the second parameter \u00a0of \u00a0the \u00a0registerOutParameter() \u00a0is \u00a0the \u00a0SQL \u00a0datatype \u00a0constants defined in the java.sql.Types class. Corresponding to the various standard ANSI SQL data types, there is a constant in the java.sql.Types class. We have the following kinds of declarations in the Types class.<\/span><\/p>\r\n \r\n

public static final int VARCHAR = 12 public static final int CHAR = 1\u00a0 public static final int NUMERIC = 2 public static final int DECIMAL = 3 public static final int INTEGER = 4<\/span><\/p>\r\n \r\n

So the sequence for executing a stored procedure will be as follows:<\/span><\/p>\r\n\r\n<\/div>\r\n

\r\n