JDBC big dataset paging, big data read/write, and transaction isolation level

I. pagination of large datasets
1. Memory paging: all data is retrieved and put into the list, and then paged. (Optional)
2. Database-layer paging: query data by page number.
MySQL: Select * from Table limit m, n
M: Start record index
N: Number of retrieved entries
Assume that 10 records are displayed on each page.
The first page of data: Select * from customer limit;
The second page of data: Select * from customer limit 10, 10;

Index recorded at the beginning of each page = (page number-1) * 10
Total number of pages: Total number of records % 10 = 0? Total records/10: Total records/10 + 1
Total number of records: Select count (*) from customer;

Dao:
Int gettotalcount (); // obtain the total number of records
List <customer> findcustomer (INT startindex, int pagesize );

3. How to compile other services that require paging?

A. Dao: Two paging methods are required.
Int gettotalcount (); // obtain the total number of records
List <customer> findcustomer (INT startindex, int pagesize); // index of the startindex start record
// Pagesize the number of records displayed per page
B. Service: encapsulate paging-related data into a page object.
Public page findcustomers (INT pagenum); // page to be viewed by the pagenum user
Use the following code to implement this method:
Public page findcustomers (INT pagenum ){
Int totalrecord = Dao. gettotalcount ();
Page page = new page (totalrecord, pagenum );
List <customer> cs = Dao. findcustomer (page. getstartindex (), page. getpagesize ());
Page. setlist (CS); // you must put the results of the page into the page object.
Return page;
}
C. servlet: Get the page you want to view, call the service layer to obtain the Page Object, encapsulate the data, and turn to the display page.
Note: Do not forget to set the URL attribute of the page object. This attribute points to the URL of the Serlvet processing page, such as/servlet/someservlet.
D. jsp: used to display paging data
Paging data is included in page. jsp in the form of static inclusion. Page. jsp does not need to be modified.
Ii. 2.1 reading and writing of big data
BLOB:
// Obtain the binary input stream of the Blob object from the result set
Inputstream in = resultset. getbinarystream (I );
Inputstream in = resultset. getblob (I). getbinarystream ();
// Sets blob pre-compilation and obtains the input stream based on the absolute path. // note that the length must be set and set to int type. You can obtain the input stream by using file.
Preparedstatement. setbinarystream (I, inputstream, length );

Clob:
Obtain:
Reader = resultset. getcharacterstream (I );
Reader = resultset. getclob (I). getcharacterstream ();
String S = resultset. getstring (I );
Settings:
String Path = classloader ();
File F = new file (PATH );
Preparedstatement. setcharacterstream (index, reader, length );
// Note that the length must be set and set to int type. You can use file to obtain the length.
2.2 Batch Processing
Two batch processing methods are available:
Statement. addbatch (SQL) List
Execute SQL statements for Batch Processing
Executebatch () method: Execute the batch processing command
Clearbatch () method: Clear the batch processing command

Connection conn = NULL;
Statement ST = NULL;
Resultset rs = NULL;
Try {
Conn = jdbcutil. getconnection ();
String sql1 = "insert into user (name, password, email, birthday)
Values ('kkkk', '123', 'abc @ Sina.com ', '2017-08-08 ')";
String sql2 = "update user SET Password = '000000' where id = 3 ";
St = conn. createstatement ();
St. addbatch (sql1); // Add the SQL statement to the batch command
St. addbatch (sql2); // Add the SQL statement to the batch command
St.exe cutebatch ();
} Finally {
Jdbcutil. Free (Conn, St, RS );
}
Batch Processing Using statement. addbatch (SQL:
Advantage: multiple different SQL statements can be sent to the database.
Disadvantages:
The SQL statement is not pre-compiled.
When multiple SQL statements with the same parameters are sent to the database, multiple SQL statements must be repeatedly written.

The second method for batch processing:
Preparedstatement. addbatch ()
Pay attention to memory overflow issues
Detailed batch processing is required for specific applications
Conn = jdbcutil. getconnection ();
String SQL = "insert into user (name, password, email, birthday) values (?,?,?,?) ";
St = conn. preparestatement (SQL );
For (INT I = 0; I <50000; I ++ ){
St. setstring (1, "AAA" + I );
St. setstring (2, "123" + I );
St. setstring (3, "AAA" + I + "@ Sina.com ");
St. setdate (4, new date (1980, 10, 10 ));

St. addbatch ();
If (I % 1000 = 0) {// process every 1000 entries. After processing, clear the records.
St.exe cutebatch ();
St. clearbatch ();
}
}
St.exe cutebatch (); // the excess part must also be executed in batches.

Batch Processing Using preparedstatement. addbatch ()
Advantage: the pre-compiled SQL statement is sent, and the execution efficiency is high.
Disadvantage: it can only be used in batches with the same SQL statement but different parameters. Therefore, this form of batch processing is often used to insert data in batches in the same table or update data in tables in batches.

2.3 obtain the primary key automatically generated by the database.
Eg:
Connection conn = jdbcutil. getconnection ();

String SQL = "insert into user (name, password, email, birthday)
Values ('abc', '123', 'abc @ Sina.com ', '2017-08-08 ')";
Preparedstatement ST = Conn.
Preparestatement (SQL, statement. return_generated_keys );

St.exe cuteupdate ();
Resultset rs = ST. getgeneratedkeys (); // obtain the primary key of the inserted row.
If (Rs. Next ())
System. Out. println (Rs. GetObject (1 ));
2.4jdbc call Stored Procedure
Write a stored procedure
Delimiter $

Create procedure demosp (in inputparam varchar (255), inout inoutparam varchar (255 ))
Begin
Select Concat ('zyxw --- ', inputparam) into inoutparam;
End $

Delimiter;
Get
Callablestatement stmt = conn. preparecall ("{call demosp (?,?)} ");
Set parameters, register the returned values, and output
Stmt. setstring (1, "ABCDE ");
Stmt. registeroutparameter (2, types. varchar );
Stmt.exe cute ();
System. Out. println (stmt. getstring (2 ));

Result: zyxw --- ABCDE

Iii. Getting started with transactions

When the JDBC program obtains a connection object from the database, the connection object will automatically submit the SQL statement sent to the database by default. To disable this default commit method and run multiple SQL statements in one transaction, you can use the following statements:
JDBC control transaction statement
// Start the transaction
Conn. setautocommit (false );
// Submit the transaction
Conn. Commit ();
// Set the transaction retention point
Savepoint sp = conn. setsavepoint ();
// Transaction rollback // must be committed after rollback
Conn. rollback (SP );
Conn. Commit ();

Iv. transaction isolation level
Features of transactions:
Atomicity refers to the fact that a transaction is an inseparable unit of work. Operations in a transaction either occur or do not occur .?
A Consistency transaction must change the database from one consistent state to another.
Isolation: when multiple users access the database concurrently, the transactions opened by the database for each user cannot be disturbed by the operation data of other transactions, multiple concurrent transactions must be isolated from each other.
Durability refers to a transaction that changes the data in the database permanently once committed. In the future, even if the database fails, it should not have any impact on it.
Transaction exceptions:
Dirty read:
A transaction reads uncommitted data from another transaction.

It cannot be read repeatedly (for one record, the same record is different before and after)
When reading a row of data in a table in a transaction, the results are different for multiple reads.

Virtual read (phantom read, different records before and after the same table)

 

1. Read uncommitted: dirty reads, non-repeated reads, and virtual reads may occur.
2. Read committed: it can prevent dirty reads and avoid repeated reads or virtual reads.
3. Repeatable read: prevents dirty reads and repeated reads. Virtual reads may occur.
4. serializable: prevents dirty reads, non-repeated reads, and virtual reads. (Lock)

Note: You must set the transaction isolation level before starting the transaction. Otherwise, the transaction cannot take effect.

Eg:

Conn. settransactionisolation (connection. transaction_read_committed );
Conn. setautocommit (false );