Java JDBC Learning

To connect to the database, you can. SQL. drivermanager requires and obtains Java. SQL. connection object. A connection object represents a specific database connection object. A connection object represents a database connection. You can use the getconnection method of drivermanager to create a jdbc url as the independent variable and obtain the connection object:

String url = "JDBC: mysql: // localhost: 3306/demo? "+

& Quot; user = root & Password = 123 & quot ";

Connection conn = drivermanager. getconnection (URL );

 

You can write jdbc url, user name, password, and other settings in an attribute file. The program reads the information in this attribute file. To change the information, you only need to modify the attribute file, you do not need to modify the program or re-compile the program. In Java SE, read attribute files can be handed over to Java. util. properties class.

 

In database applications, database connection acquisition is a time-consuming and resource-consuming operation, including the establishment of socket connection, data exchange (user password verification, related parameters), session), logs (Loggin), process allocation and other resources.

If database operations are frequent, you must consider the need to reuse connections to save time and resources for obtaining connections. Generally, a connection pool is implemented, the connection can be obtained from the pool when a connection is required. If a connection is not required, the connection is put back into the pool instead of closing the connection directly.

 

Use JDBC for Data Operations

The basic techniques for table data operations are as follows:

1. The statement class is used to execute a static SQL statement and repent the object of the result it generates. One statement object can only open one resultset object.

2. Execute (string SQL) method of the statement class executes a given SQL statement. Its return value type is boolean. Flag: if the first result is a resultset object, true is returned. If it is an update count or no result exists, false is returned.

3. The executequery (string SQL) method of the statement class is used to execute the query SQL statement and return the query result set, a resultset object.

4. The next () method of resultset is used to move the pointer down a row from the current position. Its return value type is Boolean, indicating that if the new current row is not null, true is returned, and false is returned. This method is usually used for the while statement and serves as the basis for determining whether data exists.

5. The getstring (string columnname) method of resultset is used to return the value of the specified column name in the current row of the resultset object in the form of a string.

6. The executeupdate (string SQL) method of the statement class is used to execute update SQL statements, including insert, modify, and delete statements. An int value is returned, indicating the number of updated rows.

7. The resultsetmetadata interface is used to obtain the type and attribute information of columns in the resultset object. Because resultsetmetadata is an interface, there is no constructor, so you cannot use new to create the resultsetmetadata object, but you can create it through getmetadata () of resultset, as shown in figure

String SQL = "Select ...";

Statement Sm = NULL;

Sm = con. getstatement ();

Resultset = sm.exe cutequery (SQL );

Resultsetmetadata MD = Rs. getmetadata ();

8. getcolumnname (INT column) of resultsetmetadata can get the name of the specified column, where column refers to the number of columns.

9. getcolumntypename (INT column) of resultsetmetadata returns the database-specific type name of the specified column to be retrieved. column indicates the number of columns.

10. The isnullable (INT column) of resultsetmetadata indicates whether the value in the specified column can be null, where column indicates the number of columns.

 

Batch Processing:

1. the databasemetadata interface describes the overall information about the database. Because databasemetadata is an interface, there is no constructor, so you cannot use new to create a databasemetadata object, but you can use the getmetadata () method. For example:

Databasemetadata = con. getmetadata ();

2. The supportsbatchupdates method of the databasemetadata class is used to determine whether the database supports batch update. The return value is boolean.

3. The addbatch (string SQL) method of statement adds the given SQL command to the current command list of the statement object. This method can be called multiple times.

4. The executebatch () method of statement submits a batch of commands to the database for execution. If all the commands are successfully executed, an array consisting of update counts is returned.

Public static int [] startbatch (connection con, string [] sqls) throws exception

{

If (sqls = NULL) return NULL;

Statement Sm = con. createstatement ();

For (INT I = 0; I <sqls. length; I ++)

{

SM. addbatch (sqls [I]);

}

Return sm.exe cutebatch ();

}

Function call result:

Int [] Results = startbatch (con, sqls );

For (INT I = 0; I <sqls. length; I ++)

{

If (results [I]> 0) system. out. println ("statement" + sqls [I] + "execution succeeded, affecting" + results [I] + "row data ");

Else if (results [I] = Statement. success_no_info) system... the affected function is unknown;

Else if (results [I] = Statement. excute_failed) execution failed;

}

 

Transaction processing:

1. The supportstransactions () method of databasemetadata can be used to determine whether the database supports transactions. Its return value type is boolean.

2. The database connection object is automatically submitted by default. To control the transaction commit, call the setautocommit (Boolean autocommit) method of connection to disable the automatic commit mode of database connection before the transaction commit. The autocommit parameter is true, indicates that automatic submission mode is enabled; false indicates that automatic submission mode is disabled. All operations will be submitted to the database only after the connection commit method is called.

3. the rollback method of connection is used to perform the rollback operation. If one of the statements that are operated together has an error, other statements will also be invalid. When using this method, you must note that it can only be used in setautocommit (false) mode.

 

Try

{

System. Out. println ("transaction start ");

Con. setautocommit (false );

Sm = con. createstatement ();

For (INT I = 0; I <sqls. length; I ++)

{

Sm.exe cute (sqls [I]); // execute the SQL statement, but it is not updated to the database

}

System. Out. println ("transaction commit ");

Con. Commit ();

System. Out. println ("transaction end ");

}

Catch (sqlexception E)

{

Con. rollback (); // if an exception occurs, perform the rollback operation to cancel the previous operation.

}