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

Editor to share with you how the JSP database to achieve data sorting, master / slave table processing, transaction processing, I believe that most people do not understand, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

This paper briefly introduces three tips for JSP database operation:

1. Data sorting

In JSP database technology, you can use the ORDER clause to sort the query results [ORDER BY {order_by_exdivssion [ASC | DESC]} [. N]], where order_by_exdivssion is used to specify the columns to sort, ASC specifies to sort in ascending order, and DESC specifies to sort in descending order.

Class.forName ("com.microsoft.jdbc.sqlserver.SQLServerDriver"

). New Instance ()

String url= "jdbc:microsoft:sqlserver://localhost:1433

DatabaseName=pubs "

String user= "dxaw"

String password= "123"

Connection conn=DriverManager.getConnection (url, user, password)

Statement st=conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE

ResultSet.CONCUR_UPDATABLE)

String sql= "select * from tbl order by id desc"

ResultSet rs=st.executeQuery (sql)

While (rs.next). {

Out.println (rs.getString ("..."))

}

Rs.close ()

St.close ()

Conn.close ()

two。 Master / slave table processing

The master-slave table query is mainly realized by IN clause and EXISTS clause.

The IN clause is a method of retrieving data connected by IN (or NOT IN), and the returned result set can contain zero or more values.

The EXISTS clause is a method of retrieving data connected by EXISTS (or NOT EXISTS). Its function is to determine whether any data rows are returned in the result set of the subquery, return TRUE or FALSE, but not return other actual data. Since there is no need to return a specific value in this subquery, the select list of this subquery often uses the format "SELECT *", and the outer WHERE clause does not need to specify a column name.

Select au_id, au_name form authors where au_id IN (select au_id form titleauthor where title_id IN (select title_id form titles where type= "business"))

3. Transaction processing

Transaction is one of the core concepts in JSP database theory. If a set of processing steps either occurs or is not performed at all, we call the set of processing steps a transaction. When all steps are performed as complete as an operation, we say that the transaction is committed. Because some or more of these steps fail, so that no steps are committed, the transaction must be rolled back (back to the original system state). Transactions must follow the ACID principles established by ISO/IEC. ACID is an abbreviation for atomicity, consistency, isolation, and durability. The atomicity of the transaction means that any failure in the execution of the transaction will invalidate any changes made by the transaction. Consistency means that when a transaction fails, all data affected by the transaction should be restored to the state it was before it was executed. Isolation means that data is modified during the execution of a transaction and is not visible to other transactions until the transaction is committed. Persistence means that the committed data should be in the correct state when the transaction fails.

How do you combine multiple SQL statements into a single transaction in JDBC? In JDBC, when you open a connection object Connection, the default is auto- commit mode, and each SQL statement is treated as a transaction, that is, each time a statement is executed, the transaction is automatically confirmed. In order to combine multiple SQL statements into a single transaction, block the auto-commit schema. After the auto-commit mode is blocked, the SQL statement will not get the transaction confirmation if the commit () method is not called. All SQL after the most recent call to the commit () method is confirmed when the method commit () is called.

Public int delete (int sID) {dbc = new DataBaseConnection (); Connection con = dbc.getConnection (); try {con.setAutoCommit (false); / / change the default commit method of JDBC transactions dbc.executeUpdate ("delete from tab1 where ID=" + sID); dbc.executeUpdate ("delete from tab2 where ID=" + sID); dbc.executeUpdate ("delete from tab3 where bylawid=" + sID); con.commit (); / / commit JDBC transaction con.setAutoCommit (true) / / restore the default commit mode of JDBC transactions: dbc.close (); return 1;} catch (Exception exc) {con.rollBack (); / / rollback JDBC transactions exc.printStackTrace (); dbc.close (); return-1;} check out all the contents of this article from the above article, "how to implement data sorting, master / slave table processing, transaction processing in JSP database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.