Java learning summary chapter 1 Database Operations

I. JDBC Basics

JDBC is a standard API for interaction between Java applications and the database management system, including two packages: core API --- java. SQL and extended API --- javax. SQL. ApplicationProgramThe core API interfaces are used to achieve database connection and data processing. The main interfaces are as follows:

Interface Name	Function
Java. SQL. Driver	The driver that connects to the application and database. It is used to read the information of the database drive, provide the connection method, and establish the connection object used to access the database. When loading a driver class, it should create its own instance and register the instance with drivermanager.
Java. SQL. drivermanager	The driver Manager manages a group of driver objects and manages the drivers used in the program, including loading the driver, obtaining the connection object, and sending information to the database. When calling the getconnection method, drivermanager tries to find the appropriate driver from the drivers loaded at initialization and those explicitly loaded using the same class loaders as the current applet or application.
Java. SQL. Connection	Connect the main object between the Java database and the Java application and create all statement objects. No matter what operations you perform on the database, you need to create a connection to complete the operation.
Java. SQL. Statement	Statement object, which represents a specific container and executes SQL statements on a specific database. The object used to execute a static SQL statement and return the result it generates. By default, only one resultset object can be opened for each statement object at a time. Therefore, if you read a resultset object and read another object, the two objects must be generated by different statement objects. If the current resultset object opened by a statement exists, all execution methods in the statement interface will disable it implicitly.
Preparedstatement	Indicates the objects of pre-compiled SQL statements. SQL statements are pre-compiled and stored in the preparedstatement object. This object can then be used to efficiently execute the statement multiple times.
Java. SQL. resultset	Controls the access to row data of a specific statement, that is, a set of records or rows in the database.

Ii. Result set and common methods

1. There are three types of result sets, which determine whether the cursor in the result set can be operated, and whether changes to the concurrent data source can be reflected in the result set. The detailed description is as follows:

Type	Description
Type_forward_only	The default result set type. The cursor of this type of result set object can only move forward, from the beginning of the first row to the end of the last row.
Type_scroll_insensitive	The cursor of this type of result set object can be moved forward or directly located on a row, but it is not sensitive to the changes in the corresponding data in the result set.
Type_scroll_sensitive	The cursor of this type of result set object can be moved forward or directly located on a row, and is sensitive to the changes in the corresponding data in the result set (IF ).

2. The concurrency of the result set determines the level of update operations supported by the result set. There are two levels of concurrency:

Type	Description
Concur_read_only	The default result set concurrency type. In this case, the result set object cannot be updated.
Concur_updatable	In this case, the result set object supports the update operation.

You can call the supportsresultsetconcurrency method of databasemetadata to check whether the driver supports the update operation on the result set. The method is defined as follows:

Boolean supportsresultsetconcurrency (INT type, int concurrency) throws sqlexception The first data indicates the result set type, and the second parameter indicates the concurrency type.

3. Result set continuity refers to whether the result set created in the current transaction is closed when the transaction is committed. This result set object is disabled by default. Continuity can be specified through the following two static attributes:

Type	Description
Hold_cursors_over_commit	This result set object is not closed when a transaction is committed.
Close_cursors_at_commit	When a transaction is committed, the result set object is closed, which sometimes improves the performance.

4. the type, concurrency, and continuity of the result set can be achieved through connection. createstatement, connection. preparestatement and connection. preparecall and other methods are specified. The statement, preparedstatement, and callablestatement interfaces also provide the setter method and getter method. As follows:CodeSpecifies the type, concurrency, and continuity of the result set when a statement object is created.

Connection conn = Ds. getconnection (user, passwd ); Statement stmt = conn. createstatement (resultset. type_scroll_insensitive, Resultset. concur_read_only, resultset. close_cursors_at_commit );

5. The following table lists the common cursor operations for result sets:

Return Value	Method Name	Function Description
Boolean	Next ()	Moves the cursor forward from the current position. If it points to a row, true is returned. If it points to the end of the last row, false is returned.
Boolean	Previous ()	Moves the cursor from the current position to the back row. If it points to a row, true is returned. If it points to the first row, false is returned.
Boolean	First ()	Move the cursor to the first row of the result set.
Boolean	Last ()	Move the cursor to the last row of some result sets.
Void	Beforefirst ()	Move the cursor to the beginning of the result set, just before the first row.
Void	Afterlast ()	Move the cursor to the end of the result set, just after the last row
Boolean	Relative (INT rows)	Move the cursor by the number of rows. If the parameter is 0, the cursor does not change. If the parameter is positive, the cursor moves the rows row forward. If the rows is too large, the cursor points to the end of the last record. If the parameter is a negative number, the cursor moves the rows row backward. If the rows is too small, the cursor points to the front of the first 1st records. If the cursor points to a valid row, the method returns true; otherwise, false.
Boolean	Absolute (INT row)	Move the cursor to the row specified by the Parameter

6. The following is the sample code:

// Obtain the column type in the result set Resultset rs = stmt.exe cutequery (sqlstring ); Resultsetmetadata rsmd = Rs. getmetadata (); Int [] coltype = new int [rsmd. getcolumncount ()]; For (INT idx = 0, int Col = 1; idx <coltype. length; idx ++, Col ++) Coltype [idx] = rsmd. getcolumntype (COL ); // If the result set supports the update operation, you can update, delete, and insert the result set. Statement stmt = conn. createstatement (resultset. type_forward_only, resultset. concur_updatable ); Resultset rs = stmt.exe cutequery ("select author from booklist where ISBN = 111 "); Rs. Next (); Rs. updatestring ("author", "jerrychoi "); Rs. updaterow (); // Insert record Resultset rs = stmt.exe cutequery ("select author, title, ISBN from Booklist "); Rs. movetoinsertrow (); // Set the value of each column Rs. updatestring (1, "AAA "); Rs. updatestring (2, "BBB "); Rs. updatelong (3,1234 ); // Insert a row Rs. insertrow (); Rs. movetocurrentrow (); // Delete a record Rs. Absolute (4 ); Rs. deleterow ();

III. Basic database connection process

The basic process is as follows:

L create a data source

L packages used for Import

L load the driver

L create a connection to the database

L create statement object

L write SQL statements

L execute SQL statements

L process data

L close related objects

L handling exceptions

1.Create a data source

Assume that a MySQL database named bookstore already exists.

2.Packages used for Import

Import java. SQL .*;

3.Load driver

// If the driver and its name are mydriver, the statement for loading the driver is: Class. forname ("mydriver "); // Or Class. forname ("mydriver"). newinstance (); // MySQL is used in this example. The loaded statement is: Class. forname ("com. MySQL. JDBC. Driver ");

4.Create a connection to the database

You need to know the following information when connecting to the database:

L host and port used by the database

L Database Name

L user information, including user name and password

// The format for connecting to the database is as follows: // Constr: connection string; User: User Name; passwd: Password Connection con = drivermanager. getconnection (constr, user, passwd ); // MySQL database connection example: Connection con = drivermanager. getconnection ("JDBC: mysql: // localhost: 3306/Bookstore", "root", "root ");

The following table lists Common Database driver names and URL formats:

Database	Driver	URL
Oracle	Oracle. JDBC. Driver. oracledriver	JDBC: oracle: thin: @ dbip: Port: databasename Dbip: IP address of the database server. If it is a local IP address, you can enter localhost or 127.0.0.1. Port: The listening port of the database. Check the configuration during installation. The default value is 1521. Databasename: The database Sid, usually the name of the global database.
SQL Server	Com. Microsoft. JDBC. sqlserver. sqlserverdriver	JDBC: Microsoft: sqlserver: // dbip: port; databasename = databasename Dbip: IP address of the database server. If it is a local IP address, you can enter localhost or 127.0.0.1. Port: The listening port of the database. Check the configuration during installation. The default value is 1433. Databasename: The database Sid, usually the name of the global database.
DB2	Com. IBM. db2.jdbc. App. db2driver	JDBC: DB2: // dbip: Port/databasename Dbip: IP address of the database server. If it is a local IP address, you can enter localhost or 127.0.0.1. Port: The listening port of the database. Check the configuration during installation. Databasename: The database Sid, usually the name of the global database.
Informix	Com. Informix. JDBC. ifxdriver	JDBC: infoxmix-sqli: // dbip: Port/testdb: informixserver = databasename; user = username; Password = userpassword Dbip: IP address of the database server. If it is a local IP address, you can enter localhost or 127.0.0.1. Port: The listening port of the database. Check the configuration during installation. Databasename: The database Sid, usually the name of the global database. User: User Name Password: User Password
Sybase	com. Sybase. JDBC. sybdriver	JDBC: Sybase: TDS: dbip: port/databasename dbip : IP address of the database server. If it is a local IP address, you can enter localhost or 127.0.0.1. port : the listening port of the database. Check the configuration during installation. databasename : the database Sid, usually the global database name.
mysql	COM. mySQL. JDBC. driver or Org. gjt. mm. mySQL. driver	JDBC: mysql: // dbip: Port/databasename [? Param1 = value1] [& param2 = value2]…… Note: [] is an optional option dbip : IP address of the database server, if it is local, it can be written as localhost or 127.0.0.1. port : the listening port of the database. Check the configuration at installation. The default value is 3306. databasename : the database Sid, usually the global database name. param1 , param2 , value1 , and value2 : additional parameters. Common parameters: L User: User Name L password: Password L autoreconnect: online connection failure (true/false) L maxreconnect: number of attempts to reconnect L initialtimeout: try to re-connect the interval L maxrows: Maximum number of lines returned L useunicode: whether Unicode encoding is used (ture/false) L characterencoding: encoding L relaxautocommit: whether to submit automatically (true/false) L capitalizetypenames: data Definition name expressed in uppercase
PostgreSQL	Org. PostgreSQL. Driver	JDBC: PostgreSQL: // dbip: Port/databasename Dbip: IP address of the database server. If it is a local IP address, it can be written as localhost or 127.0.0.1. Port: The listening port of the database. Check the configuration during installation. Databasename: The database Sid, usually the name of the global database.
Access	Sun. JDBC. ODBC. jdbcodbcdriver	JDBC: ODBC: datasourcename Datasourcename: the database Sid, usually the name of the global database. Note: The driver can only work in windows. First, you must create a local data source (ODBC) that can access the ACCESS database in the operating system. If the name is allandb, the URL is written as follows: JDBC: ODBC: allandb

5.Create statement object

The job of the statement object is to execute SQL statements. The statement object is created through the connection object. Therefore, before creating the statement object, make sure that the connection object has been created. The following code creates a statement object. No matter what database is connected or what operations are performed, you do not need to modify this Code:

Statement stmt = con. createstatement ();

6.Write SQLStatement

The sample code is as follows:

String SQL = "select * From usertable ";

7.Execute SQLStatement

You can use the following methods to execute SQL statements:

Return Value	Method	Description
Resultset	Executequery (string SQL)	It is mainly used to execute the SQL statement returned by a result set, typically the SELECT query statement.
Int	Executeupdate (string SQL)	This method is mainly used to execute SQL statements that do not return result sets. If you want to add, modify, or delete information in the database, they do not return values, you should use this method. The return value of this method is an integer that affects the number of records in the database.

For example, to execute the preceding query statement, you can use the following code:

Resultset rs = stmt.exe cutequery (SQL );

8.Processing result set

Refer to "result set and common methods" above"

Note: For row traversal, use the next () method of the result set. To obtain a column, you can use the getxxx (INT columnindex) or getxxx (string columnlabel) method. The following is the sample code:

// Obtain the 1st column or column named username in the form of a string String userid = Rs. getstring (1 ); String username = Rs. getstring ("username ");

9.Close related objects

The Code is as follows:

// Close related objects in the following order. The order is the opposite to that of object creation. // Close the result set Rs. Close (); // Close the statement Stmt. Close (); // Close the connection Con. Close ();

10.Exception Handling

Use the following framework to handle exceptions:

Try { ...... // Code that may fail to be executed } Catch (exception e ){ ...... // Processing code after an error occurs } Finally { ...... // Code to be executed regardless of whether an error occurs }

The following is a comprehensive sample code:

string classname = "com. mySQL. JDBC. driver "; string url =" JDBC: mysql: // localhost: 3306/bookstore "; string username = "root"; string userpass = "root "; string tablename = "usertable" connection con = NULL; preparedstatement pstmt = NULL; resultset rs = NULL; try { class. forname (classname); con = drivermanager. getconnection (URL, username, userpass); pstmt = con. preparestatement ("select * from" + tablename); rs = pstmt.exe cutequery (); while (RS. next () { for (INT I = 0; I system. out. println (RS. getstring (I + 1); system. out. println (); } }catch (exception e) { system. out. println (E. tostring); }finally { try {Rs. close () ;}catch (exception e) {} try {pstmt. close () ;}catch (exception e) {} try {con. close () ;}catch (exception e) {} }