Shulou(Shulou.com)06/01 Report--

1. JDBC

I. Overview of JDBC

Both JDBC and ODBC are based on the idea that programs written according to API can communicate with the driver manager, while the driver manager communicates with the actual database through the driver.

In the three-tier application mode, the client does not call the database directly, but calls the middleware layer on the server, and finally the middle tier completes the operation on the database.

Connect to the database:

String driver = "com.mysql.jdbc.Driver"; String dbName = "bookdb"; String passwrod = "root"; String userName = "123456"; String url = "jdbc:mysql://localhost:3308/" + dbName; String sql = "select * from users"; Class.forName (driver); Connection conn = DriverManager.getConnection (url, userName, passwrod); 4. Close the database: after the operation is completed, all used JDBC objects should be closed to release JDBC resources. The order of closing is opposite to the vocal order: 1. Close the recordset 2, close the declaration 3, close the connection object if (rs! = null) {/ / close the recordset try {rs.close () } catch (SQLException e) {e.printStackTrace ();}} if (stmt! = null) {/ / close the declaration try {stmt.close ();} catch (SQLException e) {e.printStackTrace () }} if (conn! = null) {/ / close the connection object try {conn.close ();} catch (SQLException e) {e.printStackTrace ();}}

Execute the SQL statement

Create a Statement object Statement st = conn.createStatement ()

Call the Statement interface executeUpdate method st.executeUpdate (sql)

1] this method returns the number of rows affected by the SQL command, or 0. 0 if not affected.

2] this method can execute INSERT, UPDATE, DELETE, CREATE, TABLE, DROP. But you cannot execute a SELECT query

The execute method can execute arbitrary SQL statements.

The executeQuery method can perform a query operation and return an object of type ResultSet.

ResultSet rs = st.executeQuery ("SELECT * from table")

While (re.next ()) {

System.out.println ("ID:" + rs.getInt ("ID") + "name:" + rs.getString (2))

}

Rs represents a line, and data can be obtained through numeric and string parameters. The index starts at 1.

5. Each Connection object can create one or more Statement objects. The same Statement object can be used for multiple unrelated commands and queries, but a Statement object can only have at most one open result set.

Third, perform query operations

Preparatory statement

String sql = "insert into students (Name,Sex,Age) values"

PreparedStatement pstmt

Pstmt = (PreparedStatement) conn.prepareStatement (sql); / / create an object using preprocessing

Pstmt.setString (1, student.getName ()); / / the first? The content of the number

Pstmt.setInt (2, student.getSex ())

Pstmt.setDate (3, student.getAge ())

Int r = pstmt.executeUpdate (); / / execute the SQL statement to update the database

Variable use? To indicate that variables are assigned through setXXX. If it is a query, call pstmt.executeQuery (); get a ResultSet

two。 Read and write LOB:

The database can also store large objects, binary large objects become BLOB, character large objects become CLOB.

Scrollable and updatable result sets

1. Set up

For ResultSet to scroll and update, you must specify the corresponding parameters in the following way when creating the Statement object:

Statement stmt = conn.createStatement (type, concurrency)

For PreparedStatement, specify the parameters in the following way:

PreparedStatement pstmt = conn.prepareStatement (sql, type, concurrency)

Where type represents the type of ResuleSet, and concurrency indicates whether the database can be updated using ResuleSet.

The values and meanings of type and concurrency:

ResultSet.TYPE_FORWARD_ONLY-the result set cannot be scrolled, which is the default

ResultSet.TYPE_SCROLL_INSENSITIVE-the result set can be scrolled, but ResuleSet is insensitive to changes in the data sent in the database

ResultSet.TYPE_SCROLL_SENSITIVE-result sets can be scrolled, and ResuleSet is sensitive to changes that occur in the database

ResultSet.CONCUR_READ_ONLY-read-only result set, which cannot be used to update the database

ResultSet.CONCUR_UPDATABLE-updatable result set that can be used to update the database

When using TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE to create Statement objects, you can use ResultSet's first () / last () / beforeFirst () / afterLast () / relative () / absolute () methods to move around the result set at will.

Tip: even if you use the CONCUR_UPDATABLE parameter to create a Statement, the resulting recordset is not necessarily "updatable". If your recordset comes from a merge query, that is, the results of the query come from multiple tables, then such a result set may not be an updatable result set. You can use the getConcurrency () method of the ResuleSet class to determine whether it is an updatable result set.

If the result set is updatable, you can use ResultSet's updateRow (), insertRow (), moveToCurrentRow (), deleteRow (), cancelRowUpdates () methods to update the database.

If the updatable result set is not set, it will hold com.microsoft.sqlserver.jdbc.SQLServerException: the result set is not updatable

[methods]

Next (), which moves the cursor to the next record.

Previous (), which moves a record on the cursor, as long as there is a record before it.

Absolute (int row), you can use this method to jump to the specified record location. True is returned after successful positioning.

If false is not returned successfully, and the return value is false, the cursor will not move.

AfterLast (), the cursor jumps after the last record.

BeforeFirst (), the cursor jumps before the first record. (jump to the initial position of the cursor)

First (), with the cursor pointing to the first record.

Last (), with the cursor pointing to the last record.

Relative (int rows), relative positioning method, parameter value can be positive or negative, parameter is positive, cursor moves down specified value from current position, parameter is negative

The cursor moves up the specified value from the current position.

two。 Update

[1] Statement stm = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE)

So the result set obtained by calling executeQuery is the updatable result set.

[2] insert a new row:

ResultSet result set, 1. First use moveToInsertRow () to move the cursor to a buffer similar to the result set structure

two。 You can then use the updateXxx (int column,columnType value) method to update the specified column data

3. Then insert the record using the insertRow () method

4. Finally, point the cursor back to its original position, moveToCurrentRow ().

Rs.first ()

/ / move the cursor over the insert line

Rs.moveToInsertRow ()

/ / build row data

Rs.updateString (2, "xxxx")

Rs.updateInt (3, "x")

/ / insert a row

Rs.insertRow ()

Fifth, Row set

1) a rowset is one or more rows of data retrieved from a tabular data source:

Similar to the result set (ResultSet) (RowSet interface inherits ResultSet interface)

However, the connection to the database cannot be disconnected when using the result set, and the rowset can exist when the connection is closed, and the rowset is generally used when the connection is closed, and the connection is established only when some special operations are needed.

2) data sources in the rowset:

Data retrieved from the database using the JDBC driver, data obtained from other data sources, such as file data

Advantages of Row Set:

1) you can disconnect the database and manipulate the data

2) can be transferred between different components in a distributed system

3) updatable, scrollable, serializable by default, and can be easily transmitted between networks

4) data can be easily converted between rowsets and JavaBean objects

A row of data in a rowset can be encapsulated as a JavaBean object

JavaBean is a class that has several private properties, followed by public get and set methods associated with the attributes. In this way, we can store a row of data in a JavaBean object

Rowset-related interfaces:

Javax.sql.RowSet: the parent interface of all rowsets

Java.sql.rowset.CachedRowSet: a data row container that caches rows of data in memory without having to connect to the data source when manipulating the data. You can modify the data in the CachedRowSet object, which can then be updated to the database. It is also a JavaBean component that is scrollable, updatable, and serializable.

Java.sql.rowset.JDBCRowSet: data row container, scrollable and updatable. Always connect to the database.

Java.sql.rowset.WebRowSet: the cached rowset whose data can be saved as a xml file.

Java.sql.rowset.JoinRowSet: the data row container, which is taken from the RowSet objects that form the SQL JOIN relationship. Both connected and disconnected RowSet objects can become part of the JOIN.

Java.sql.rowset.FilteredRowSet: a data row container that allows conditional filtering of data.

Fill in the rowset:

1) traditional JDBC method

Class.forName ("com.mysql.jdbc.Driver")

String connectionUrl = "jdbc:mysql://localhost:3306/test?user=root&password=root"

Connection connection = DriverManager.getConnection (connectionUrl)

Statement statement = connection.createStatement ()

ResultSet rs = statement.executeQuery (sql)

CachedRowSetImpl rowset = new CachedRowSetImpl (); / / get the rowset

Rowset.populate (rs)

Rs.close (); statement.close (); connection.close ()

2) set rowset properties to connect to the database and retrieve data

CachedRowSetImpl rowset = new CachedRowSetImpl ()

Rowset.setUrl ("jdbc:mysql://127.0.0.1:3306/test")

Rowset.setUsername ("root")

Rowset.setPassword ("test")

Rowset.setCommand ("select * from student")

Rowset.execute ()

-

Use of RowSet:

1. The following rowset classes use the default implementation class of Java

CachedRowSetImpl rowset = new CachedRowSetImpl ()

/ / CachedRowSetImpl is the default implementation class of CachedRow interface defined by SUN

Statement statement = connection.createStatement ()

ResultSet rs = statement.executeQuery ("select * from table1")

Rowset.populate (rs); / / populate the rowset

Rs.close (); statement.close (); connection.close (); / / close the connection

/ / display CachedRow data, using methods inherited from the result set

While (rowset.next ()) {

System.out.print (rowset.getString (1) + ":"), System.out.println (rowset.getString ("CompanyName"));

}

2. Update CachedRow data (conn.setAutoCommit (false);)

Crs.last ()

Crs.updateShort (3,58)

Crs.updateInt (4, 150000)

Crs.updateRow (); / / update the rowset

Crs.acceptChanges (conn); / / Update the database

Note:

Modifying data using a rowset is independent of how the data in the rowset is populated. But to ensure that there are available connection objects when the acceptChanges () method executes, you can call acceptChanges () if there are available connection objects in the rowset, and if there are no available connection objects in the rowset, you need to call the acceptChanges (Connection) method.

When you use a rowset to add, modify, or delete data, you must ensure that the transaction commit is non-autocommit (the acceptChanges () method calls the commit () method).

3. Add data

Crs.setTableName ("student"); / / add data must be specified

Crs.moveToInsertRow (); / / identifies the position of the pointer

Crs.updateInt ("id", 33); / / the primary key must be specified when adding data

Crs.updateString ("name", "Shakespeare")

Crs.updateShort ("age", 58)

Crs.insertRow (); / / update the rowset

Crs.moveToCurrentRow (); / / return the pointer to the identified position

Crs.acceptChanges (conn); / / Update the database

/ / Delete data

Crs.first ()

Crs.deleteRow (); / / Delete rowset data

Crs.acceptChanges (conn); / / Update the database

4. Paging 1: populate the rowset with the result set

Rs = stm.executeQuery ("select * from student")

Crs.setPageSize (4); / / set lines per page

Crs.populate (rs, 10); / / populate the rowset with 4 rows of data from the tenth row of the result set

...

Crs.nextPage (); / / get the subsequent page data, and return true if there is any data

...

Note:

Resultset,statement (preparedstatement), connection cannot be closed at this time, otherwise the rowset cannot get subsequent page data

5. Paging 2: the rowset establishes a connection to read data from the database

CachedRowSetImpl crs= new CachedRowSetImpl ()

Crs.setUrl ("jdbc:mysql://127.0.0.1:3306/test")

Crs.setUsername ("root")

Crs.setPassword ("root")

Crs.setCommand ("select * from student")

Crs.setPageSize (4); / / the number of rows per page, which must be set before the execution of the execute () method, otherwise it is invalid

Crs.execute ()

……

Crs.nextPage (); / / get the data of the next page, regardless of the result set and connection object

Public class JDBC3 {public static void main (String [] args) {Connection conn=null; PreparedStatement pst=null; ResultSet rs=null; DBUtil util=new DBUtil (); String sql= "select*from score"; try {conn=util.getConn (); pst=conn.prepareStatement (sql) / / non-autocommit conn.setAutoCommit (false); rs=pst.executeQuery (); / / create rowset instance CachedRowSetImpl rowset=new CachedRowSetImpl (); / / populate rowset.populate (rs); rs.close (); pst.close () Rowset.absolute (5); / / rowset.updateInt ("id", 5); rowset.updateInt ("English", 55); / / Update rowset.updateRow (); / / submit rowset.acceptChanges (conn) / / close the connection conn.close () before exporting the result set; / / output rowset data System.out.println ("id\ tChinese\ tEnglish\ thistory") While (rowset.next ()) {System.out.print (rowset.getInt ("id") + "\ t"); System.out.print (rowset.getInt ("Chinese") + "\ t"); System.out.print (rowset.getInt ("English") + "\ t"); System.out.println (rowset.getInt ("history")) } catch (ClassNotFoundException e) {e.printStackTrace ();} catch (IOException e) {e.printStackTrace ();} catch (SQLException e) {e.printStackTrace ();}

Public class JDBC4 {public static void main (String [] args) {try {CachedRowSetImpl rowset=new CachedRowSetImpl (); rowset.setUrl ("jdbc:mysql://localhost:3308/test"); rowset.setUsername ("root"); rowset.setPassword ("123456"); rowset.setCommand ("select*from score;"); rowset.execute () / / output rowset data System.out.println ("id\ tChinese\ tEnglish\ thistory"); while (rowset.next ()) {System.out.print (rowset.getInt ("id") + "\ t"); System.out.print (rowset.getInt ("Chinese") + "\ t") System.out.print (rowset.getInt ("English") + "\ t"); System.out.println (rowset.getInt ("history"));} catch (SQLException e) {e.printStackTrace ();}

Public class JDBC6 {public static void main (String [] args) {try {CachedRowSetImpl rowset=new CachedRowSetImpl (); rowset.setUrl ("jdbc:mysql://localhost:3308/test"); rowset.setUsername ("root"); rowset.setPassword ("123456"); rowset.setCommand ("select*from score;") / / set the number of data items displayed per page rowset.setPageSize (3); rowset.execute (); int item2; System.out.println ("page 1"); System.out.println ("id\ tChinese\ tEnglish\ thistory") While (rowset.next ()) {System.out.print (rowset.getInt ("id") + "\ t"); System.out.print (rowset.getInt ("Chinese") + "\ t"); System.out.print (rowset.getInt ("English") + "\ t") System.out.println (rowset.getInt ("history"));} while (rowset.nextPage ()) {System.out.println ("page" + I + "); iTunes; System.out.println (" id\ tChinese\ tEnglish\ thistory ") While (rowset.next ()) {System.out.print (rowset.getInt ("id") + "\ t"); System.out.print (rowset.getInt ("Chinese") + "\ t"); System.out.print (rowset.getInt ("English") + "\ t") System.out.println (rowset.getInt ("history"));} catch (SQLException e) {e.printStackTrace ();}

VI. Transactions and metadata

A transaction refers to a unit of work that contains a set of add, delete, modify and other data operation commands, which are submitted to the system as a whole for execution, either successfully or restored.

Using transactions in JDBC

1) con.setAutoCommit (false), cancel automatic submission

2) perform one or more operations on the database (one or more SQL statements)

3) con.commit (), commit the transaction (multiple operations of the second part above are committed as a whole)

4) if an operation fails, roll back all operations through con.rollback () (undo the above operations and restore the data to its pre-execution state)

Transaction processing depends on the underlying database implementation, and different drivers may have different support for transaction processing.

[transaction characteristics]

1. Four attributes of transaction (Transaction) (ACID)

Atomicity (Atomic) either all or none of the changes to the data are performed.

Consistency (Consistent) data state remains consistent before and after the transaction is executed.

Isolated) the processing of one transaction cannot affect the processing of another transaction.

The Durable transaction ends and its effect is persisted in the database.

two。 Problems that may be caused by concurrent transaction processing

Dirty read: one transaction reads data that has not been committed by another transaction

Unrepeatable read (non-repeatable read) the operation of one transaction causes different data to be read before and after another transaction.

Phantom read the operation of one transaction results in a difference in the amount of data between the two queries before and after another transaction.

For example:

When transactions An and B are executed concurrently

When A transaction update, B transaction select reads the data that A has not yet committed, and A transaction rollback, then the data read by B is invalid "dirty" data.

When the B transaction select reads the data, the A transaction update operation changes the data to which the B transaction select arrives. At this time, the B transaction reads the data again and finds that the data before and after the two times are different.

When the B transaction select reads the data, the A transaction insert or delete has a record that satisfies the select condition of the A transaction. At this time, the B transaction select again and finds that the previous record does not exist ("Phantom"), or one of the previous records is missing.

Transaction support of JDBC

JDBC's support for transactions is reflected in three aspects:

1. Auto-commit mode (Auto-commit mode)

Connection provides a property of auto-commit to specify when the transaction ends.

a. When auto-commit is true, when each individual SQL operation is completed, the transaction commits automatically, that is, each SQL operation is a transaction.

When an independent SQL operation is completed, the JDBC specification states:

For data manipulation languages (DML, such as insert,update,delete) and data definition languages (such as create,drop), statements are considered to be completed as soon as they are executed.

For a select statement, execution is considered complete when the ResultSet object associated with it is closed.

For a stored procedure or other statement that returns multiple results, execution is considered complete when all ResultSet objects associated with it are closed and all update count (number of rows affected by update,delete and other statement operations) and output parameter (output parameters of the stored procedure) have been obtained.

b. When auto-commit is false, each transaction must show either a call to the commit method to commit or a call to the rollback method to roll back. Auto-commit defaults to true.

JDBC provides five different levels of transaction isolation, which are defined in Connection.

two。 Transaction isolation level (Transaction Isolation Levels)

JDBC defines five transaction isolation levels:

TRANSACTION_NONE JDBC driver does not support transactions

TRANSACTION_READ_UNCOMMITTED allows dirty, unrepeatable and phantom reading.

TRANSACTION_READ_COMMITTED forbids dirty reading, but allows unrepeatable and phantom reading.

TRANSACTION_REPEATABLE_READ forbids dirty reading and unrepeatable reading, and runs phantom reading alone.

TRANSACTION_SERIALIZABLE forbids dirty, unrepeatable and phantom reading.

3. Save Point (SavePoint)

JDBC defines the SavePoint interface, which provides a finer-grained transaction control mechanism. When a SavePoint is set, you can rollback to the state at that SavePoint instead of rollback the entire transaction.

The setSavepoint and releaseSavepoint methods of the Connection interface set and release Savepoints.

Although the JDBC specification defines the above supporting behavior of transactions, the degree of support for transactions may vary from JDBC driver to database vendor. If you set it arbitrarily in the program, you may not get the desired effect. To this end, JDBC provides a DatabaseMetaData interface, which provides a series of methods to obtain the support of JDBC features. For example, support for transaction isolation levels can be determined by the DatabaseMetaData.supportsTransactionIsolationLevel method, and support for Savepoints can be determined by the DatabaseMetaData.supportsSavepoints method.

Grammar

Connection con = DriverManger.getConnection (urlString); con.setAutoCommit (false); / / cancel autocommit Statement stm = con.createStatement (); stm.executeUpdate (sqlString); con.transactionEndMethod; / / if the transaction method succeeds, commit or fail rollback con.commit () or con.rollback ()

Example:

Try {Class.forName (drv). NewInstance (); / load driver conn = DriverManager.getConnection (url,user,pwd); conn.setAutoCommit (false); / / disable auto-commit stmt = conn.createStatement (); / / insert data operation stmt.executeUpdate ("insert into (username,password,email) user values ('admin','admin','admin@123.com')") / / update data operation stmt.executeUpdate ("update user set email='admin@163.com'"); / / transaction commit conn.commit ();} catch (Exception ex) {try {/ / transaction rollback conn.rollback () if failed;} catch (Exception e) {e.printStackTrace ();}} finally {/ / close connection if (conn! = null) {try {conn.close () } catch (Exception e) {e.printStackTrace ();}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.