Use JDBC to operate stored procedures and jdbc to operate stored procedures

Use JDBC to operate stored procedures and jdbc to operate stored procedures

You can use CallableStatement to perform JDBC operations on stored procedures.

The SQL statement for calling the stored procedure is: {call procedure_name [(arg1), (arg2)]}

CallableStatement can be obtained through the prepareCall () method of the database connection object.

For example:

conn.prepareCall(sql);

If the stored procedure has an output parameter, you can use its registerOutParameter () method to register the output parameter as the JDBC Type. For example:

registerOutParameter(int parameterIndex,int sqlType)

The following is an example of counting the number of students. First, we will look at the records in the database:

It is not hard to see that there are seven public records in the student table.

Write a stored procedure to count the number of students in the student table.

create or replace procedure getStudentCount(v_student out number) isbegin  select count(*) into v_student from student;end getStudentCount;

Then test the stored procedure:

declare  v_count number;begin  getstudentCount(v_count);  dbms_output.put_line(v_count);end;

The output is 7. There is no problem with the stored procedure.

Then you can see how to call the stored procedure using JDBC.

import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class ProcedureDemo {public static void main(String[] args) {Connection conn = null;int studentCount = 0;try {Class.forName("oracle.jdbc.driver.OracleDriver");conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "myhr", "myhr");} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}String sql = "{call getStudentCount(?)}";try {CallableStatement proc = conn.prepareCall(sql);proc.registerOutParameter(1, java.sql.Types.INTEGER);proc.execute();studentCount = proc.getInt(1);} catch (SQLException e) {e.printStackTrace();}System.out.println(studentCount);}}

In this case, output 7 on the console to prove that the stored procedure is successfully called.
 

 

Problems with jdbc calling stored procedures

It indicates that sun's driver is more compatible than SQLServerDriver.

If you want to use jdbc, you have to run according to the standard jdbc. execute is enough. Do not use executeQuery ......
 
JDBC procedure

Connect to the database through JDBC
• Create a program to connect to the database using JDBC, which consists of seven steps:
1. Load the JDBC driver:
Before connecting to the database, load the driver of the database to be connected to the JVM (Java Virtual Machine ),
This is achieved through the static method forName (String className) of the java. lang. Class.
For example:
Try {
// Load the MySql Driver Class
Class. forName ("com. mysql. jdbc. Driver ");
} Catch (ClassNotFoundException e ){
System. out. println ("The driver class cannot be found. Failed to load the driver! ");
E. printStackTrace ();
}
After the Driver class is loaded, the Driver class instance is registered to the DriverManager class.
2. Provide the URL of the JDBC connection
• The Connection URL defines the protocol, sub-protocol, and data source identification used to connect to the database.
• Writing format: Protocol: Sub-Protocol: data source ID
Protocol: In JDBC, the sub-Protocol always starts with jdbc: the driver of the bridge connection or the name of the database management system.
Data source ID: Mark the address and connection port of the database source.
For example:
(MySql connection URL)
Jdbc: mysql: /// localhost: 3306/test? UseUnicode = true & characterEncoding = gbk;
UseUnicode = true:
Indicates that the Unicode character set is used. If characterEncoding is set to gb2312 or GBK, this parameter must be set to true. CharacterEncoding = gbk: character encoding method.
3. Create a database connection
• To connect to the database, you need to request java. SQL. DriverManager and obtain the Connection object, which represents the Connection of a database.
• Use the getConnectin (String url, String username, String password) method of DriverManager to pass in the path of the database to be connected, and obtain the username and password of the database.
For example: // connect to the MySql database. The username and password are both root.
String url = "jdbc: mysql: // localhost: 3306/test ";
String username = "root ";
String password = "root ";
Try {
Connection con = DriverManager. getConnection (url, username, password );
} Catch (SQLException ...... remaining full text>