Nelpon Fungsi SQL Server

CallableStatement

JDBC duwe antarmuka liyane kanggo skenario sing luwih rumit. Iki minangka warisan saka PreparedStatement lan diarani CallableStatement .

Iki digunakake kanggo nelpon (Call) prosedur sing disimpen ing database. Keanehan saka telpon kasebut yaiku, saliyane asil ResultSet , paramèter uga bisa ditransfer menyang prosedur sing disimpen.

Apa sing anyar, sampeyan takon? PreparedStatement uga duwe asil ResultSet lan sampeyan uga bisa ngirim parameter kasebut. Ya, sing bener, nanging keanehan prosedur sing disimpen yaiku liwat paramèter sing ora mung bisa ditampa, nanging uga ngasilake data.

Prosedur sing disimpen diarani nganggo parameter IN , OUT , lan INOUT . Iki ngasilake siji utawa luwih obyek ResultSet . Cara Connection.prepareCall () digunakake kanggo nggawe obyek CallableStatement .

Kene mbayangno sing duwe ADD prosedur disimpen sing nampa paramèter a, b lan c. Prosedur iki nambahake a lan b lan nempatake asil tambahan ing variabel c.

Ayo nulis kode ing ngendi kita bakal nyoba nelpon:

// Connect to the server
Connection connection = DriverManager.getConnection("jdbc:as400://mySystem");

// Create a CallableStatement object. It does preprocessing
// calling a stored procedure. Question marks
// indicate where the input parameters should be substituted, and where the output ones
// The first two parameters are input,
// and the third one is a day off.
CallableStatement statement = connection.prepareCall("CALL MYLIBRARY.ADD (?, ?, ?)");

// Setting up input parameters. Passing 123 and 234 to the procedure
statement.setInt (1, 123);
statement.setInt (2, 234);

// Registering the output parameter type
statement.registerOutParameter (3, Types.INTEGER);

// Run stored procedure
statement.execute();

// Get the value of the output parameter
int sum = statement.getInt(3);

// Close CallableStatement and Connection
statement.close();
connection.close();

Pakaryan meh padha karo PreparedStatement , mung ana nuansa. Fungsi ADD kita ngasilake asil saka tambahan ing parameter katelu. Mung obyek CallableStatement ora ngerti apa-apa babagan iki. Mula, kita ngandhani kanthi jelas kanthi nelpon metode registerOutParameter() :

registerOutParameter(parameter number, Parameter type)

Sawisé iku, sampeyan bisa nelpon prosedur liwat cara eksekusi () banjur maca data saka parameter katelu nggunakake getInt () cara .

Batching Panjaluk

Ing proyek nyata, kahanan asring muncul nalika sampeyan kudu nggawe akeh pitakon sing padha (paling umum ing kasus iki PreparedStatement ), contone, sampeyan kudu nglebokake sawetara puluhan utawa atusan cathetan.

Yen sampeyan nglakokake saben panjaluk kanthi kapisah, bakal entuk akeh wektu lan nyuda kinerja aplikasi kasebut. Kanggo nyegah iki, sampeyan bisa nggunakake mode insert kumpulan. Iku dumunung ing kasunyatan sing nglumpukake sawetara buffer karo panjalukan, lan banjur nglakokaké langsung.

Iki minangka potongan kode minangka conto:

PreparedStatement stmt = con.prepareStatement(
        	"INSERT INTO jc_contact (first_name, last_name, phone, email) VALUES (?, ?, ?, ?)");

for (int i = 0; i < 10; i++) {
	// Fill in the request parameters
	stmt.setString(1, "FirstName_" + i);
    stmt.setString(2, "LastNAme_" + i);
    stmt.setString(3, "phone_" + i);
    stmt.setString(4, "email_" + i);
	// The request is not executed, but fits into the buffer,
	// which is then executed immediately for all commands
	stmt.addBatch();
}
// Execute all requests at once
int[] results = stmt.executeBatch();

Tinimbang nglakokake pitakon nganggo metode eksekusi () , kita batch karo metode addBatch () .

Banjur, nalika ana sawetara atus panjalukan, sampeyan bisa ngirim kabeh bebarengan menyang server kanthi nelpon printah executeBatch () .

Sehat. Metode executeBatch () ngasilake array integer - int []. Saben sel ing larik iki ngemot nomer sing nuduhake jumlah larik sing diowahi dening pitakon sing cocog. Yen panjaluk nomer 3 ing batch diganti 5 baris, sel kaping 3 saka array bakal ngemot nomer 5.