Jdbc pre-processing, batch processing, LOB field processing, calling stored procedure, jdbclob

Jdbc pre-processing, batch processing, LOB field processing, calling stored procedure, jdbclob

(1) jdbc execution preprocessing

PreparedStatment preparation statement

Eg: String SQL = "insert into user (id, name, birthday, money) values (5, 'admin ',?, '123 ')";

PreparedStatement stmt = conn. prepareStatment (SQL );

Stmt. setTimestamp (1, new Timestamp (System. currentTimeMillis ()));

Stmt.exe cute ();

(2) Call the Stored Procedure

For a scalar function, the call String is {fn functionname (?,?)} /{Fn functionname ()}

For a stored procedure, the call String is {call proc (?,?)} /{Call proc}/{call? = Proc (?,?)}

CallableStatement

Stored Procedure with no parameters or returned values

String SQL = "{call test1 ()}";

CallableStatment stmt = conn. prepareCall (SQL );

Stmt.exe cuteUpdate ();

Stored Procedures with parameters and returned values

String SQL = "{call? = Test1 (?)} "; // Method 1

CallableStatment stmt = conn. prepareCall (SQL );

Stmt. setString (2, "test ");

Stmt. registerOutParameter (1, Type. Interger );

Stmt.exe cute ();

Int result = stmt. getInt (1 );

 

String SQL = "{call? = Test1 (?,?)} "; // Method 2

CallableStatment stmt = conn. prepareCall (SQL );

Stmt. setString (1, "test ");

Stmt. registerOutParameter (2, Type. Interger );

Stmt.exe cute ();

Int result = stmt. getInt (2 );

(3) jdbc data Batch Processing

String SQL = "insert into user (id, name, birthday, money) values (?,?,?,?) ";

PreparedStatement stmt = conn. prepareStatement (SQL );

For (int I = 1; I <1000; I ++ ){

Stmt. setInt (1, I );

Stmt. setString (2, "test ");

Stmt. setDate (3, new Date (System. currentTimeMillis ()));

Stmt. setFloat (4,1000 );

Stmt. addBatch (SQL );

}

Int [] result=stmt.exe cuteBatch ();

(4) LOB field (BLOB, CLOB) Processing

Common classes and Methods

Java. SQL. ResultSet

Blob getBlob (int columnIndex );

Blob getBlob (string columnLabel );

Clob getClob (int columnIndex );

Clob getClob (string columnLabel );

-----------------------------------------------------------------

Java. SQL. Blob

Long length ();

Byte [] getBytes (long startPosition, long length );

InputStream getBinaryStream ();

InputStream getBinaryStream (long startPosition, long length );

OutputStream setBinaryStream (long startPosition );

Java. SQL. Clob

Long length ();

Byte [] getSubString (long startPosition, long length );

Reader getCharacterStream ();

Reader getCharacterStream (long startPosition, long length );

Writer setCharacterStream (long startPosition );

-----------------------------------------------------------------

Java. SQL. Connection

Blob createBlob ();

Clob createClob ();

----------------------------------------------------------------

Eg:

PreparedStament stmt = conn. prepareStatement ("select pic from book where bokkid = '1 '");

ResultSet rt1_stmt.exe cuteQuery ();

If (rt. next ()){

Blob blob = rt. getBlob (1 );

Image img = ImageIO. read (blob. getInputStream ());

}

Bytes --------------------------------------------------------------------------------------------

Blob blob = conn. createBlob ();

Int offset = 0;

OutputStream out = blob. setBinaryStream (offset );

ImageIO. write (img, "PNG", out );

PreparedStament stmt = conn. prepareStatement ("insert into book values (?,?) ");

Stmt. setInt (1, 1 );

Stmt. setBlob (2, blob );

Stmt.exe cuteUpdate ();

Bytes --------------------------------------------------------------------------------------------