The difference between the Statement object and the Preparedstatement object in jdbc, as well as calling the stored procedure through the jdbc operation, preparedstatement

The difference between the Statement object and the Preparedstatement object in jdbc, as well as calling the stored procedure through the jdbc operation, preparedstatement

I,

Class Structure of java. SQL. * And javax. SQL. * packages

 

|-Driver Interface: indicates the java Driver interface. All specific database vendors must implement this interface.

|-Connect (url, properties): Method for connecting to the database.

Url: the URL to connect to the database

URL Syntax: jdbc Protocol: Database sub-Protocol: // host: Port/Database

User: user Name of the database

Password: Database User password

|-DriverManager class: Driver manager class, used to manage all registered drivers

|-RegisterDriver (driver): registers a driver Class Object.

|-Connection getConnection (url, user, password); get the Connection object

 

|-Connection interface: the Connection object between the java program and the database.

|-Statement createStatement (): Creates a Statement object.

|-PreparedStatement prepareStatement (String SQL) create a PreparedStatement object

|-CallableStatement prepareCall (String SQL) create a CallableStatement object

 

|-Statement interface: used to execute static SQL statements

|-Int executeUpdate (String SQL): Execute static update SQL statements (DDL, DML)

|-ResultSet executeQuery (String SQL): The executed static query SQL statement (DQL)

 

|-PreparedStatement interface: used to execute pre-compiled SQL statements

|-Int executeUpdate (): Execute the pre-compiled update SQL statement (DDL, DML)

|-ResultSet executeQuery (): Execute the pre-compiled query SQL statement (DQL)

 

|-CallableStatement interface: the SQL statement used to execute the Stored Procedure (call xxx)

|-ResultSet executeQuery (): method used to call a stored procedure

 

 

|-ResultSet interface: Used to encapsulate the queried data

|-Boolean next (): move the cursor to the next row

|-GetXX (): obtains the column value.

The Statemetent object executes static SQL statements, while the PreparedStatement object executes pre-compiled SQL statements, for example, the Statement object executes executeUpdate (String SQL) and executeQuery (String SQL ), the PreparedStatement object executes executeUpdate () and executeQuery () without parameters. The characteristics of these two objects can be seen from these two methods. Because of this, PreparedStatement can prevent SQL statement injection, more secure, of course, it is more efficient.

2. Call the stored procedure through jdbc code

The Code is as follows:

　　　　　　　　　　　　　　

Package com. a_callrablestatement;

Import java. SQL. CallableStatement;
Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. ResultSet;
Import java. SQL. SQLException;
Import java. SQL. Types;

Import org. junit. Test;

Import com. util. DBUtil;

Public class Demo1 {

Public Connection conn = null;
Public CallableStatement cs = null;
ResultSet rs = null;
String driverClass = "com. microsoft. sqlserver. jdbc. SQLServerDriver ";
String url = "jdbc: sqlserver: // localhost: 1433; DatabaseName = User ";
String username = "sa ";
String password = "qwer1234 ";
String SQL = "exec pro_findById? ";
Public Demo1 (){
// TODO Auto-generated constructor stub
}
@ Test
Public void test1 ()
{

Try {
// Register the driver
Class. forName (driverClass );
// Connection
Conn = DriverManager. getConnection (url, username, password );
// Obtain the prepareCall precompiled object
Cs = conn. prepareCall (SQL );
// Set the placeholder of the question mark
Cs. setInt (1, 3 );

Rs = cs.exe cuteQuery ();
// Print the result
While (rs. next ())
{
Int id = rs. getInt ("id ");
String name = rs. getString ("username ");
String password = rs. getString ("password ");
String gender = rs. getString ("gender ");
String interest = rs. getString ("interest ");
System. out. println (id + "," + name + "," + password + "," + gender + "," + interest );
}
} Catch (ClassNotFoundException e)
{
E. printStackTrace ();
} Catch (SQLException e)
{
E. printStackTrace ();
} Finally
{
DBUtil. close (conn, rs, cs );
}
}

@ Test
Public void test2 ()
{
SQL = "exec pro_findNameById ?,? ";
Try {
// Register the driver
Class. forName (driverClass );
// Connection
Conn = DriverManager. getConnection (url, username, password );
// Obtain the prepareCall precompiled object
Cs = conn. prepareCall (SQL );
// Set the placeholder parameter value of the question mark
Cs. setInt (1, 3 );
/**
* 1. Parameter 1, indicating the parameter location to be set
* 2. Parameter 2, indicating the type of the parameter value to be returned varchar (20)
*/
Cs. registerOutParameter (2, Types. VARCHAR );
// Execute the operation, but no result set is returned. The returned value is in the parameter. Only execute () can be used here, And executeQuery () cannot be used. This is in SQL Server2008.
Cs.exe cute ();

/**
* Pre-compiled SQL parameter location
*/
String name = cs. getString (2 );
// Print the result
System. out. println (name );


} Catch (ClassNotFoundException e)
{
E. printStackTrace ();
} Catch (SQLException e)
{
E. printStackTrace ();
} Finally
{
DBUtil. close (conn, rs, cs );
}
}

}

Tool

Package com. util;

Import java. SQL. Connection;
Import java. SQL. PreparedStatement;
Import java. SQL. ResultSet;
Import java. SQL. SQLException;

Public class DBUtil {

Public DBUtil (){
// TODO Auto-generated constructor stub
}
Public static void close (Connection conn, ResultSet rs, PreparedStatement ps)
{
Try {
If (conn! = Null)
{
Conn. close ();
}
If (rs! = Null)
{
Rs. close ();
}
If (ps! = Null)
{
Ps. close ();
}
} Catch (SQLException e)
{
E. printStackTrace ();
Throw new RuntimeException (e );
}
}
}

1. The test1 () method is used to call a stored procedure without return values.

2. The test2 () method is used to call stored procedures with returned values.

ExecuteQuery () cannot be used when a stored procedure with a returned value is called. Otherwise, an error with no returned result set is reported.

After the execute () method is changed, the normal result is displayed.

Stored Procedure Code in test1 ()

Use [User]
Go

Create procedure pro_findById (@ id int)
As
Select * from [tb_user] where id = @ id
Go

 

Stored Procedure in test2 ()

Use [User]
Go
Create procedure findNameById @ id int, @ name varchar (20)
As
Select @ name = username from tb_user where id = @ id
Go

Note: SQL server 2008 and jdk 1.7 plus eclipse ee 4.5