JDBC Basic teaching PreparedStatement

Source: Internet
Author: User

Overview

The PreparedStatement interface inherits Statement and differs from the other in two ways:

The PreparedStatement instance contains compiled SQL statements. This is to make the statement "ready". The SQL statement contained in the PreparedStatement object can have one or more in parameters. The value of the in parameter was not specified when the SQL statement was created. Instead, the statement retains a question mark for each in parameter ("? ") as a placeholder. The value of each question mark must be provided by the appropriate Setxxx method before the statement is executed.

Because the PreparedStatement object has been precompiled, it executes faster than the Statement object. As a result, SQL statements executed multiple times are often created as preparedstatement objects to improve efficiency.

As a subclass of Statement, PreparedStatement inherits all the functionality of Statement. It also adds a set of methods for setting the value that is sent to the database to replace the in parameter placeholder. Also, three methods execute, ExecuteQuery, and executeupdate have been changed so that the parameters are no longer needed. The Statement form of these methods (in the form of accepting SQL statement parameters) should not be used for PreparedStatement objects.

1. Create PreparedStatement objects

The following code snippet (where con is the Connection object) creates a PreparedStatement object that contains an SQL statement with two in parameter placeholders:

PreparedStatement pstmt = con.preparestatement ("UPDATE table4 SET m =? WHERE x =? ");

Pstmt object contains statement "UPDATE table4 SET m =? where x =? ", which has been sent to the DBMS and ready for execution.

2. Pass in Parameter

Before executing the PreparedStatement object, you must set each? The value of the parameter. This can be done by calling the Setxxx method, where XXX is the type corresponding to the parameter. For example, if the parameter has a Java type long, the method used is setlong. The first parameter of the Setxxx method is the ordinal position of the parameter to set, and the second parameter is the value set to the parameter. For example, the following code sets the first argument to 123456789 and the second parameter to 100000000:

pstmt.setLong(1, 123456789);
pstmt.setLong(2, 100000000);

Once you set the parameter value for a given statement, you can use it to execute the statement multiple times until you call the Clearparameters method to clear it. In the default mode of the connection (enable autocommit), the statement is automatically committed or restored when the statement completes.

The same preparedstatement can be executed multiple times if the base database and the driver maintain the open state of the statements after the statement has been committed. If this is not true, there is no point in trying to improve performance by using the PreparedStatement object instead of the Statement object.

Using Pstmt (The PreparedStatement object created earlier), the following code illustrates how to set the value of a two-parameter placeholder and execute pstmt 10 times. As mentioned above, to do this, the database cannot close pstmt. In the example, the first parameter is set to "Hi" and persisted as a constant. In the For loop, the second argument is set to a different value each time: starting at 0 and ending with 9.

pstmt.setString(1, "Hi");
for (int i = 0; i < 10; i++) {
 pstmt.setInt(2, i);
 int rowCount = pstmt.executeUpdate();
}

3. Consistency of data types in Parameters

XXX in the Setxxx method is a Java type. It is an implied type of JDBC (generic SQL type) because the driver maps the Java type to the appropriate JDBC type (following the mapping specified in the "Mapping Java and JDBC types" table in the Jdbcguide) and sends the JDBC type to the §8.6.2 Database. For example, the following snippet sets the second argument of the PreparedStatement object pstmt to the 44,java type as short:

pstmt.setShort(2, 44);

The driver sends 44 as a JDBC SMALLINT to the database, which is the standard mapping of the Java short type.

It is the responsibility of the programmer to ensure that the Java type of each in parameter is mapped to a JDBC type compatible with the JDBC data type required by the database. Consider the situation where the database requires JDBC SMALLINT. If you use method SetByte, the driver sends the JDBC TINYINT to the database. This is possible because many databases can be converted from one related type to another, and usually TINYINT can be used anywhere smallint applies

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.