Summary of book Management system--database Manipulation (iii): Database security

Last record, say a word about the security of the database.

One, consistency control

To ensure transactional consistency, you can use Display transaction statements and add integrity constraints to the database. For example, in the return of the book, you want to modify the user's user table in the remaining number of books, borrowing records of the option and the remaining number in the Book table. There are two strategies available at this time:

1. Use a trigger to define a trigger in MySQL: (for example only)

DELIMITER $$CREATE    /*[definer = {User | Current_User}]*/    TRIGGER' db_book '. ' Borrow ' afterINSERT     on' db_book '. ' T_borrow ' forEach ROWBEGIN     UPDATET_userSETBorronumrem=Borrownumrem-1 WHEREId=(SELECTUserid frominserted); --Modify user table UPDATET_bookSETNumer= Number-1 WHEREId=(SELECTBookId frominserted); --Modify the book list END$ $DELIMITER;

2, in the application software to do three table modification

But it's not so good to know that this is a mistake. Finally, three tables are used in the application software. This calculation is not also deducted the money on a regular basis to operate a little, in the application to change parameters or more accustomed to.

Second, concurrency control

First about one user cannot log on twice. If Java EE can use the web-side session:http://www.cnblogs.com/loveweiwei/p/4139668.html

But this is not the network, I do not know how to do, because the time relationship is not empty detailed investigation, can only use the most inferior method, in the user table with a property to indicate whether to log in. Then log in the function to add a row-level lock, to ensure that a person in the first logon when the person behind the login is not in this is the use of the previous "Database manipulation (i)" in the definition of the function of the transaction:

/*** Login Verification *@paramCon *@paramUser *@return     * @throwsException*/     PublicUser Login (Connection con,user user)throwsException {User resultuser=NULL; Dbutil Dbutil=NewDbutil (); PreparedStatement pstmt=NULL; ResultSet RS=NULL; String SQL= "SELECT * from T_user where id=?" and password=? and islogin=? For update ";//Add pessimistic lock, do not allow simultaneous login        Try{dbutil.begintransaction (con);//Start a transactionpstmt=con.preparestatement (SQL); //set the question markPstmt.setstring (1, User.getid ()); Pstmt.setstring (2, User.getpassword ()); Pstmt.setbyte (3, (byte) 0); RS=Pstmt.executequery (); if(Rs.next ()) {//if it is found, the instantiationResultuser=NewUser (); Resultuser.setid (Rs.getstring ("id"));//getInt ("id")Resultuser.setusername (rs.getstring ("UserName")); Resultuser.setpassword (Rs.getstring ("Password")); Resultuser.setborrownumrem (Rs.getint ("Borrownumrem")); Resultuser.setbalance (Rs.getfloat ("Balance")); Resultuser.setislogin ((byte) 1);//landed                intNum=modifyisloginfield (Con,user.getid (), (byte) 1); //Commit a transactiondbutil.committransaction (con); }      }Catch(Exception e) {e.printstacktrace (); //rolling back a transactiondbutil.rollbacktransaction (con); Throw Newruntimeexception (); }finally{dbutil.close (RS);            Dbutil.close (PSTMT);            Dbutil.resettransaction (con);        Dbutil.closecon (con); }          returnResultuser; }    

Similarly, the closure mechanism was used when paying user fees.

/*** User pays fine *@paramTableName *@return     */     Public Static intRecharge (Connection con,user User)throwsException {//pessimistic lock for update using databaseString sql = "Select balance from T_user where id=?" For update ";//plus the for update adds row-level exclusive locks to the database to prevent errors in modifying the amount        intNum//Update () returns the number of barsDbutil dbutil=NewDbutil (); PreparedStatement pstmt=NULL; ResultSet RS=NULL; floatValue = 0; Try{              //Set auto-commit to Falsedbutil.begintransaction (con); Pstmt=con.preparestatement (SQL); Pstmt.setstring (1, User.getid ()); RS=Pstmt.executequery ();  Rs.next (); //point to first recordValue = Rs.getfloat ("Balance"); Value=value+user.getbalance ();//request incoming user for the balance to be added! User.setbalance (value);//Change Balancenum=Modifyvaluefield (Con,user.getid (), value); //Commit a transactiondbutil.committransaction (con); }Catch(Exception e) {e.printstacktrace (); //rolling back a transactiondbutil.rollbacktransaction (con); Throw Newruntimeexception (); }finally{dbutil.close (RS);              Dbutil.close (PSTMT);              Dbutil.resettransaction (con);          Dbutil.closecon (con); }          returnnum; }      

Because of the time relationship, only these two important events were added to the lock.

Summary of book Management system--database Manipulation (iii): Database security