Basic JDBC knowledge

JDBC is composed of a series of connections, SQL statements, and result sets. Its main functions are summarized as follows:
Establish a connection with the database.
Initiate a query request to the database.
Process the returned results of the database.

These functions are implemented through a series of APIS, and several important interfaces are shown in Table 13-1.

Table 13-1 important JDBC APIs Port connection Java. SQL. drivermanager processes driver loading and creates new database connections Java. SQL. Connection processes connections to specific databases Java. SQL. Statement processes SQL statements in a specified connection Java. SQL. resultset

The composition of these jdbc apis is 13-2.

Figure 13-2 Structure of the JDBC API

Drivermanager

The drivermanager class is a class used in the Java. SQL package for database driver management. It acts between users and drivers. It tracks available drivers and establishes connections between the database and the corresponding drivers. It also processes transactions such as driver logon time restrictions and logon and message display. The drivermanager class directly inherits from Java. Lang. object. The main member methods are shown in Table 13-2.

Table 13-2 main member methods of drivermanager and their meanings

For simple applications, the only method that a program developer needs to directly use in this class is drivermanager. getconnection. This method is used to establish a connection with the database. JDBC allows you to call the drivermanager method getdriver, getdrivers, registerdriver, and driver method connect. However, in most cases, it is best to have the drivermanager class manage the connection details.

Connection

Connection is used to indicate the object of the database connection. All operations on the database are performed on the basis of this connection. The main member methods of the connection class are shown in Table 13-3.

Table 13-3 main member methods of the connection class and their meanings Method Definition Void clearwarnings clear all warning information for the connection Statement createstatement () creates a statement object Statement createstatement (INT resultsettype, int resultsetconcurrency) Create a statement object that generates a result set with a specific type and concurrency Void commit () submits changes to the database and releases the database lock held by the current connection. Void rollback () rolls back all changes in the current transaction and releases the database lock held by the current connection. String getcatalog () gets the current directory name of the connection object Boolean isclosed () determines whether the connection is closed Boolean isreadonly () determines whether the connection is in read-only mode Void setreadonly () sets the read-only mode of the connection. Void close () immediately releases the database and JDBC resources of the connected object

Statement

Statement is an object used to send SQL statements to the database based on established connections. It is just an interface definition, including the method for executing SQL statements and obtaining returned results. There are actually three types of statement objects: Statement, preparedstatement (inherited from statement), and callablestatement (inherited from preparedstatement ). They are all used as containers for executing SQL statements on a given connection. Each container is dedicated to sending specific types of SQL statements: the statement object is used to execute simple SQL statements without parameters; the preparedstatement object is used to execute pre-compiled SQL statements with or without the in parameter. The callablestatement object is used to call stored procedures of the database. The statement interface provides basic methods for executing statements and obtaining results. The preparedstatement interface adds methods for processing in parameters, while the callablestatement interface adds methods for processing out parameters.

The method for creating a statement object is as follows:
Statement stmt = con. createstatement ();

The methods included in the statement interface definition are shown in Table 13-4.

Table 13-4 main member methods of the statement interface and their meanings Method Definition Void addbatch (string SQL) adds an SQL batch processing statement for database operations to the statement. Void cancel () cancels the database operation command specified by the SQL statement in statement Void clearbatch () clears the SQL batch statement in statement Void clearwarnings () clears warnings caused by operations in the statement Void close () closes the database connection specified by the statement Boolean execute (string SQL) executes the SQL statement Int [] executebatch () executes Multiple SQL statements Resultset executequery (string SQL) queries the database and returns the result set. Int executeupdate (string SQL) for database updates Connection getconnection () gets the connection to the database Int getfetchdirection () gets the direction for retrieving row data from the database table Int getfetchsize () gets the number of rows in the Returned Database result set. Int getmaxfieldsize () obtains the maximum number of fields in the Returned Database result set. Int getmaxrows () obtains the maximum number of rows in the Returned Database result set. Boolean getmoreresults () gets the next result of statement Int getquerytimeout () Get query timeout settings Resultset getresultset () to obtain the result set Int getupdatecount () gets the number of update records Void setcursorname (string name) sets the name of the database cursor Void setfetchdirection (int dir) sets the direction for retrieving row data in the database table Void setfetchsize (INT rows) is used to set the number of rows in the Returned Database result set. Void setmaxfieldsize (INT max) sets the maximum number of fields Void setmaxrows (INT max) sets the maximum number of rows Void setquerytimeout (INT seconds)

It is worth noting that the statement interface provides three ways to execute SQL statements: executequery, executeupdate, and execute. The method used is determined by the content generated by the SQL statement. The executequery method is used to generate SQL statements for a single result set, such as select statements. The executeupdate method is used to execute insert, update, delete, and DDL (Data Definition Language) statements, such as create table and drop table. The returned value of executeupdate is an integer, indicating the number of rows (update count) of the table in the database affected by the SQL statement it executes ). Execute is used to execute statements that return multiple result sets or update counts.

The preparedstatement interface inherits the statement interface, but the preparedstatement statement contains pre-compiled SQL statements, which improves execution efficiency. The preparedstatement statement can contain multiple "? "Field, you can use the setxxx method to set the content of this field in the program, thus enhancing the dynamic nature of the program design. The main member methods of the preparedstatement interface and their meanings are shown in Table 13-5.

Table 13-5 main member methods of the preparedstatement interface and their meanings Method Definition Void addbatch (string SQL) adds an SQL batch processing statement for database operations to the statement. Void clearparameters () clears the setting parameters in preparedstatement Resultset executequery (string SQL) executes the SQL query statement Resultsetmetadata getmetadata () is used to query databases and obtain database metadata. Void setarray (INT index, array X) is set to array type Void setasciistream (INT index, inputstream stream, int length) is set to ASCII input stream Void setbigdecimal (INT index, bigdecimal X) is set to long decimal type Void setbinarystream (INT index, inputstream stream, int length) set to binary input stream Void setcharacterstream (INT index, inputstream stream, int length) set to character input stream Void setboolean (INT index, Boolean X) is set to logical type Void setbyte (INT index, byte B) is set to byte type Void setbytes (INT byte [] B) is set to byte array type Void setdate (INT index, date X) is set to date type Void setfloat (INT index, float X) is set to floating point type Void setint (INT index, int X) is set to integer type Void setlong (INT index, long X) is set to long integer type Void SETREF (INT index, int ref) is set to reference type Void setshort (INT index, short X) is set to short Integer type Void setstring (INT index, string X) is set to string type Void settime (INT index, time X) is set to time type

The difference between preparedstatement and statement is that the SQL statements it constructs are not complete statements, but must be dynamically set in the program. This enhances the flexibility of program design. On the other hand, because the preparedstatement statement is precompiled, the execution efficiency of the constructed SQL statement is relatively high. Therefore, for some frequently used SQL statements, using preparedstatement statements has obvious advantages over using statement statements.

You can create a preparedstatement object as follows:
Preparedstatement pstmt = con. preparestatement ("Update tbl_user set reward =? Where userid =? ");

This statement contains two fields that can be dynamically set: reward and userid.

For example, if we want to reward the first registered user with 5000 points, we can use the following method to set the content of the blank field:

pstmt.setInt(1, 5000); pstmt. setInt (2, 1);

If we want to reward the first 50 registered users at each, we can use the loop statement to set the null field:

pstmt.setInt(1, 5000); for (int i = 0; i < 50; i++) { pstmt.setInt(2,i); int rowCount = pstmt.executeUpdate(); }

If the transferred data volume is large, you can set the in parameter to a JAVA input stream. When a statement is executed, the JDBC driver repeatedly calls the input stream, reads its content, and transmits them as actual parameter data. JDBC provides three methods to set the in parameter to the input stream: setbinarystream is used for streams containing unspecified bytes; setasciistream is used for streams containing ASCII characters; setunicodestream is used for streams containing Unicode characters. These methods have one more parameter than other setxxx methods to specify the total length of the stream, because some databases need to know the size of the data it transfers before sending data.

The following is an example of using a stream as the in parameter to send file content:

java.io.File file = new java.io.File("/tmp/data"); int fileLength = file.length(); java.io.InputStream fin = new java.io.FileInputStream(file); java.sql.PreparedStatement pstmt = con.prepareStatement( "update table set stuff = ? where index = 4"); pstmt.setBinaryStream (1, fin, fileLength); pstmt.executeUpdate();

When a statement is executed, the input stream fin is repeatedly called to pass its data.

The callablestatement object is used to call stored procedures of the database. In the callablestatement object, there is a common member method call, which is used to call the stored procedure in the database by name. During database calling, you can set the in parameter to provide the required parameters for executing the called stored procedure. In addition, you can use the out parameter to obtain the execution result of a stored procedure.

The main member methods of the callablestatement interface and their meanings are shown in Table 13-6.

Table 13-6 main member methods of the callablestatement interface and their meanings Method Definition Array getarray (int I) Get the Array Bigdecimal getbigdecimal (INT index) Bigdecimal getbigdecimal (INT index, int scale) Boolean getboolean (INT index) to obtain the logical type Byte getbyte (INT index) to obtain the byte type Date getdate (INT index) date getdate (INT index, calendar Cal) Get date type Double getdouble (INT index) Float getfloat (INT index) Get date type floating point type Int getint (INT index) Long getlong (INT index) Object GetObject (INT index) Object GetObject (INT index, map) Get object type Ref getref (int I) Get date type ref Short getshort (INT index) obtains the short Integer type of the date type. String getstring (INT index) Get date type string Time gettime (INT index) Time Gettime (INT index, calendar Cal) Get the time type Void registeroutputparameter (INT index) Void registeroutputparameter (INT index, int type) Void registeroutputparameter (INT index, int type, int scale) Register output parameters & Nbsp; the syntax for calling a stored procedure is: {Call procedure_name} // The process does not require parameters. {Call procedure_name [(?,…)]} // The process requires several parameters. {? = Call procedure_name [(?,…)]} // The process requires several parameters and returns a parameter

Procedure_name indicates the name of the stored procedure, and the content in square brackets is optional. The method for creating a callablestatement object is as follows:
Callablestatement cstmt = con. preparecall ("{call getdata (?, ?)} ");

The setxxx statement is used to pass the required parameters to the stored procedure. For example, we can set two parameters as follows:

cstmt.setByte(1, 25); cstmt.setInt(2,64.85);

To return the running result of a stored procedure, call the registeroutparameter method to set the output parameters of the stored procedure, and then call the getxxx method to obtain the execution result of the stored procedure. For example:

cstmt.registerOutParameter(1, java.sql.Types.TINYINT); cstmt.registerOutParameter(1, java.sql.Types. INTEGER); cstmt.executeUpdate(); byte a = cstmt.getByte(1); int b = cstmt.getInt(2);

From the above procedures, we can see that the basic data types of Java correspond to the Data Types supported by SQL. The corresponding relationship is shown in Table 13-7.

Table 13-7 ing between SQL data types and Java Data Types SQL data type Java Data Type Char string Varchar string Longvarchar string Numeric java. Math. bigdecimal Decimal java. Math. bigdecimal Bit Boolean Tinyint byte Smallint short Integer int Bigint long Real float Float double Double double Binary byte [] Varbinary byte [] Longvarbinary byte [] Date java. SQL. Date Time Java. SQL. Time Timestamp java. SQL. Timestamp

Resultset

The result set is used to temporarily store the results obtained by the database query operation. It contains all rows that meet the conditions in SQL statements, and provides a set of get methods to access data in these rows. The main member methods of the resultset class and their meanings are shown in Table 13-8.

Table 13-8 main member methods of the resultset class and their meanings Method Definition Boolean absolute (INT row) moves the pointer to a row of the result set object Void afterlast () moves the pointer to the end of the result set object Void beforefirst () moves the pointer to the header of the result set object Boolean first () moves the pointer to the first row of the result set object. Array getarray (INT row) obtains a row in the result set and stores it in an array. Boolean getboolean (INT columnindex) obtains the value of a column in the current row and returns a Boolean value. Byte getbyte (INT columnindex) obtains the value of a column in the current row and returns a byte value. Short getshort (INT columnindex) obtains the value of a column in the current row and returns a short integer value. Int getint (INT columnindex) obtains the value of a column in the current row and returns an integer value. Long getlong (INT columnindex) gets the value of a column in the current row and returns a long integer value. Double getdouble (INT columnindex) gets the value of a column in the current row and returns a double-precision value. Float getfloat (INT columnindex) gets the value of a column in the current row and returns a floating point value. String getstring (INT columnindex) to obtain the value of a column in the current row, returns a string Date getdate (INT columnindex) gets the value of a column in the current row and returns a datetime value. Object GetObject (INT columnindex) obtains the value of a column in the current row and returns an object Statement getstatement () obtains the statement object that generates the result set. URL geturl (INT columnindex) obtains the value of a column in the current row and returns a java.net. url value. Boolean isbeforefirst () determines whether the pointer is in the header of the result set. Boolean isafterlast () determines whether the pointer is at the end of the result set Boolean isfirst () determines whether the pointer is in the first row of the result set. Boolean islast () determines whether the pointer is in the last row of the result set Boolean last () moves the pointer to the last row of the result set. Boolean next () Move the pointer to the next row of the current row Boolean previous () moves the pointer to the first row of the current row

As shown in Table 13-8, The resultset class not only provides a set of get methods for accessing data, but also provides a lot of methods for moving pointers (cursor, sometimes translated as the cursor. Cursor Is the pointer maintained by resultset to the current data row. It is located before the first row. Therefore, when accessing the result set for the first time, the next method is usually called to place the pointer on the first row to make it the current row. Next, each call calls the next pointer to move a row down.