Differences between statement objects and PreparedStatement objects in JDBC, and calls to stored procedures through JDBC operations

One

The class structure of java.sql.* and javax.sql.* packages

|-Driver Interface: Represents the Java driver interface. All of the specific database vendors are going to implement this interface.

|-Connect (URL, properties): A way to connect to a database.

URL: The URL of the connection database

URL syntax: JDBC Protocol: Database Sub-protocol://HOST: Port/Database

User: Username for database

Password: Database user password

|-DriverManager class: Driver manager class for managing all registered drivers

|-registerdriver (Driver): Registering a Driver class object

|-connection getconnection (Url,user,password); Get Connection Object

|-Connection interface: Represents a Connection object for Java programs and databases.

|-Statement createstatement (): Create Statement Object

|-PreparedStatement preparestatement (String sql) Creating PreparedStatement objects

|-callablestatement preparecall (String sql) Creating CallableStatement objects

|-statement interface: For executing static SQL statements

|-int executeupdate (String sql): Execute a static update SQL statement (DDL,DML)

|-ResultSet executeQuery (String sql): Static query SQL statement executed (DQL)

|-preparedstatement interface: Used to execute precompiled SQL statements

|-int executeupdate (): Perform Precompiled update SQL statement (DDL,DML)

|-resultset executeQuery (): Execute a Precompiled query SQL statement (DQL)

|-callablestatement interface: SQL statement for execution of stored procedures (call XXX)

|-resultset ExecuteQuery (): Methods for calling stored procedures

|-resultset interface: Used to encapsulate the data of the query

|-Boolean next (): Move the cursor to the next line

|-GETXX (): Gets the value of the column

The Statemetent object executes a static SQL statement, while the PreparedStatement object executes a precompiled SQL statement, such as statement object execution executeupdate (String sql) and ExecuteQuery (String SQL), while the PreparedStatement object executes the executeupdate () and executequery () without parameters, the two methods can be used to see the characteristics of these two objects, because of this, PreparedStatement can prevent SQL statement injection, is more secure, of course, it is more efficient.

Second, call the stored procedure through the 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{
Registration driver
Class.forName (Driverclass);
Connection
conn = Drivermanager.getconnection (Url,username,password);
Get Preparecall precompiled objects
CS = conn.preparecall (SQL);
Set a placeholder for a question mark
Cs.setint (1,3);

rs = Cs.executequery ();
Print results
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{
Registration driver
Class.forName (Driverclass);
Connection
conn = Drivermanager.getconnection (Url,username,password);
Get Preparecall precompiled objects
CS = conn.preparecall (SQL);
Set the parameter value of a question mark placeholder
Cs.setint (1,3);
/**
* 1. Parameter one, indicating the position of the parameter to be set
* 2. Parameter two, indicating the parameter value type to return varchar (20)
*/
Cs.registeroutparameter (2, Types.varchar);
Performs an operation, but does not return a result set, the return value is in the parameter, only execute () can be used here, not executequery (), which is in SQL Server2008
Cs.execute ();

/**
* The location of parameters in precompiled SQL
*/
String name = cs.getstring (2);
Print results
SYSTEM.OUT.PRINTLN (name);


}catch (ClassNotFoundException e)
{
E.printstacktrace ();
}catch (SQLException e)
{
E.printstacktrace ();
}finally
{
Dbutil.close (CONN,RS,CS);
}
}

}

Tool class

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);
}
}
}

The 1.test1 () method is to call a stored procedure that has no return value

The 2,test2 () method is to call a stored procedure that has a return value

You cannot use ExecuteQuery () when you call a stored procedure that has a return value, or you do not report an error that returns a result set

Instead, the Execute () method is used to get normal results.

Stored procedure Code in TEST1 ()

Use [User]
Go

CREATE PROCEDURE Pro_findbyid (@id int)
As
SELECT * FROM [Tb_user] where [email protected]
Go

Stored procedures in Test2 ()

Use [User]
Go
CREATE PROCEDURE Findnamebyid @id int, @name varchar (20)
As
Select @name =username from tb_user where [email protected]
Go

Note: SQL Server 2008 and JDK 1.7 plus eclipse EE 4.5

Differences between statement objects and PreparedStatement objects in JDBC, and calls to stored procedures through JDBC operations