Web day19 Service Layer processing transactions (using threadlocal), Txqueryrunner gadgets, single-table exercises (add-and-revise operations), paging

Service transaction

The DAO is not a place to process transactions, because each method in DAO is a single operation on the database

Connection should not appear in the service, it should only appear in DAO,

Because it's a jdbc thing, JDBC stuff is used to connect to the database.

Modify Jdbcutils

We put the opening and closing of the transaction into the Jdbcutils, and call the Jdbcutils method in the service to complete the transaction processing.

But in the service will no longer appear connection this "taboo".

Code

public class Jdbcutils {//config file default configuration! Ask you to give c3p0-config.xml!!! private static Combopooleddatasource DataSource = new Combopooleddatasource ();//It is a transactional private connection! private static threadlocal<connection> TL = new threadlocal<connection> ();/** * Returns a connection object using a connection pool * @return * @thr oWS SQLException */public static Connection getconnection () throws SQLException {Connection con = tl.get ();//When con is not equal to NULL, The description has been called BeginTransaction (), indicating that the transaction is open! if (con! = null) return Con;return datasource.getconnection ();} /** * Return the connection pool Object! * @return */public static DataSource Getdatasource () {return DataSource;} /** * Open Transaction * 1. Gets a connection that sets its Setautocomnmit (FALSE) * 2. Also make sure that the connection used in DAO is the one we just created! *--------------* 1. Create a connection, set to manually submit * 2. Give this connection to DAO! * 3. Also let CommitTransaction or rollbacktransaction can get to! * @throws SQLException */public static void BeginTransaction () throws SQLException {Connection con = tl.get (); if (con! = N ull) throw new SQLException ("The transaction has already been opened, do not open it again!") ");/* * 1. Assign a value to con! * 2. Set the con to manual commit! */con =Getconnection ();//assigns a value to con, indicating that the transaction has started Con.setautocommit (false); Tl.set (con);//Save the current thread's connection! }/** * COMMIT TRANSACTION * 1. Obtain the connection provided by BeginTransaction, and then call the Commit method * @throws SQLException */public static void CommitTransaction () throws SQLException {Connection con = tl.get ();//Gets the private connection of the current thread if (con = = null) throw new SQLException ("The transaction has not been opened and cannot be committed!") ");/* * 1. Direct use of Con.commit () */con.commit (); Con.close ();//Set it to NULL to indicate that the transaction is over! The next time you call Getconnection () The return is not con tl.remove ();//Remove connection from TL}/** * COMMIT TRANSACTION * 1. Gets the connection provided by BeginTransaction, and then calls the Rollback method * @throws SQLException */public static void RollbackTransaction () Throws SQLException {Connection con = tl.get (); if (con = = null) throw new SQLException ("The transaction has not been opened and cannot be rolled back!") ");/* * 1. Direct use of Con.rollback () */con.rollback (); Con.close (); Tl.remove ();} /** * Release Connection * @param connection * @throws SQLException */public static void Releaseconnection (Connection connection) thro WS SQLException {Connection con = tl.get ();/* Determine if it is a transaction-specific, and if so, do not close it! * If the transaction is not dedicated, then close it! *///if con = = null, stating that there is no transaction now, thenConnection must not be dedicated to the business! if (con = = null) connection.close ();//if con! = null, stating that there is a transaction, then you need to determine if the parameter connection is equal to con, if unequal, the parameter connection is not a transactional private connection if (con! = connection) Connection.close ();}

Txqueryrunner Gadgets

Automatically handles the closing of the connection without having to pass the connection parameters, automatically passing

By inheriting the Queryrunner class and overriding all methods that do not have successive parameter passes (Overrides are added)

Code

/** * This class of methods, self to handle the problem of connection * no outside delivery! * How to deal with it? * Get connected by jdbcutils.getconnection ()! There could be a transactional connection, or it could be a normal connection! * Jdbcutils.releaseconnection () Complete the release of the connection! If it is a normal connection, close it! * @author CXF * */public class Txqueryrunner extends Queryrunner {@Overridepublic int[] batch (String sql, object[][] param s) throws SQLException {/* * 1. Get Connected * 2. Execute the parent class method, pass the Connection object * 3. Release connection * 4. return value */connection con = jdbcutils.getconnection (); T[] result = Super.batch (con, SQL, params); Jdbcutils.releaseconnection (con); return result;} @Overridepublic <T> T query (String sql, Object param, resultsethandler<t> rsh) throws SQLException { Connection con = jdbcutils.getconnection (); T result = Super.query (con, SQL, param, rsh); Jdbcutils.releaseconnection (con); return result;} @Overridepublic <T> T query (String sql, object[] params, resultsethandler<t> rsh) throws SQLException { Connection con = jdbcutils.getconnection (); T result = Super.query (con, SQL, params, rsh); Jdbcutils.releaseconnection (con); return result;} @OverridepUblic <T> T query (String sql, resultsethandler<t> rsh, Object ... params) throws SQLException {Connection con = Jdbcutils.getconnection (); T result = Super.query (con, SQL, rsh, params); Jdbcutils.releaseconnection (con); return result;} @Overridepublic <T> T query (String sql, resultsethandler<t> rsh) throws SQLException {Connection con = Jdbcuti Ls.getconnection (); T result = Super.query (con, SQL, rsh); Jdbcutils.releaseconnection (con); return result;} @Overridepublic int update (String sql) throws SQLException {Connection con = jdbcutils.getconnection (); int result = Super. Update (con, SQL); Jdbcutils.releaseconnection (con); return result;} @Overridepublic int update (String sql, Object param) throws SQLException {Connection con = jdbcutils.getconnection (); int R Esult = Super.update (con, SQL, param); Jdbcutils.releaseconnection (con); return result;} @Overridepublic int update (String sql, Object ... params) throws SQLException {Connection con = jdbcutils.getconnection (); int result = SuPer.update (con, SQL, params); Jdbcutils.releaseconnection (con); return result;}}

The practice of adding and deleting a single table
Add Customer Analytics

Add.jsp→customerservlet#add () display added successfully!

Implementation of the Code

Dao#addcustomer (Customer C):

Give the template of the SQL statement

Convert the parameter C into a object[]

Call the Queryrunner Update method.

Service#addcustomer (Customer C):

Direct invocation of DAO's Addcustomer (c);

View Customer Analytics View a list of all user information

Top.jsp→customerservlet#findall () →list.jsp

DAO:

sql = "SELECT * from T_customer";

Qr.query (), you need to map the result set to List<customer> so use Beanlisthandler

Conditional Query query.jsp customerservlet#query () list.jsp

Modify Customer

query process by ID

Editing process

Delete Customer

1. When the user clicks "Delete" in the list.jsp page, it is handled by Customerservlet's Delpre method:

2. Access to the CID;

3. Obtain the Customer object through CID;

4. Save the Customer object to request

5. Forward to Del.jsp

2.1 When the user clicks on the del.jsp page to delete, through the customer's Del method to process:

2.2 Get CID

2.3 To complete the deletion by CustomerService

3. Output "Delete succeeded" to the page

Code

Web tier

/** * Web Layer * @author CXF * */public class Customerservlet extends Baseservlet {private CustomerService CustomerService = n EW CustomerService ();/** * Add Customer */public String Add (httpservletrequest request, httpservletresponse response) throws  Servletexception, IOException {/* * 1. Encapsulates the form data to the Customer object * 2. Completion: CID, using UUID * 3. Use the service method to complete the Add work * 4. Save success information to the request domain * 5. Forward to msg.jsp */customer C = Commonutils.tobean (Request.getparametermap (), customer.class); C.setcid (CommonUtils.uuid ()); Customerservice.add (c); Request.setattribute ("msg", "Congratulations, add Customer success!") "); return" f:/msg.jsp ";} /** * Query All * @param request * @param response * @return * @throws servletexception * @throws IOException */public String F Indall (HttpServletRequest request, httpservletresponse response) throws Servletexception, IOException {/* * 1. Call service to get all customers * 2. Save to request domain * 3. Forward to list.jsp */request.setattribute ("Cstmlist", Customerservice.findall ()); return "f:/list.jsp";} /** * Edit Previous loading work * @param request * @param response * @return * @throWS servletexception * @throws ioexception */public String preedit (httpservletrequest request, HttpServletResponse Response) throws Servletexception, IOException {/* * 1. Get CID * 2. Use the CID to invoke the service method to get the Customer Object * 3. Save the customer to the request domain Medium * 4. Forward to edit.jsp displayed in the form */string cid = Request.getparameter ("CID"); Customer cstm = customerservice.load (CID); Request.setattribute ("Cstm", cstm); return "f:/edit.jsp";} /** * Edit Method * @param request * @param response * @return * @throws servletexception * @throws ioexception */public String E DIT (httpservletrequest request, httpservletresponse response) throws Servletexception, IOException {/* * 1. Encapsulates the form data into the Customer object * 2. Call the service method to complete the modification * 3. Save the success message to request domain * 4. Forward to msg.jsp display success Information *///has encapsulated the CID into the Customer object in customer c = Commonutils.tobean (Request.getparametermap (), Customer.class) ; Customerservice.edit (c); Request.setattribute ("msg", "Congratulations, edit Customer success!") "); return" f:/msg.jsp ";} Public String Query (httpservletrequest request, httpservletresponse response) throws ServletexceptIon, IOException {/* * 1. Encapsulates the form data into the customer object, it has only four attributes (CNAME, gender, cellphone, email) * It is a condition * 2. Use the customer to invoke the service method, Get list<customer> * 3. Save to the request domain * 4. Forward to list.jsp */customer criteria = Commonutils.tobean (Request.getparametermap (), customer.class); list<customer> cstmlist = customerservice.query (criteria); Request.setattribute ("Cstmlist", cstmList); return " /list.jsp ";}}

DAO layer (persistence layer)

/** * Persistence Layer * * @author CXF * */public class Customerdao {private Queryrunner QR = new Txqueryrunner ();/** * Add Customer * * @pa Ram C */public void Add (Customer c) {try {String sql = "INSERT into T_customer values (?,?,?,?,?,?,?)"; O Bject[] params = {c.getcid (), C.getcname (), C.getgender (), C.getbirthday (), C.getcellphone (), C.getemail (), C.getdescription ()};qr.update (SQL, params);} catch (SQLException e) {throw new RuntimeException (e);}} /** * Query all * @return */public list<customer> findAll () {try {String sql = "SELECT * from T_customer"; return qr.query (SQL, new Beanlisthandler<customer> (Customer.class));} catch (SQLException e) {throw new RuntimeException (e);}} /** * Load Client * @param CID * @return */public Customer Load (string cid) {try {String sql = "SELECT * from T_customer where CI D=? "; return qr.query (SQL, New beanhandler<customer> (Customer.class), CID);} catch (SQLException e) {throw new RuntimeException (e);}} /** * Edit Customer * @param c */public void edit (Customer c) {try {String sql = "Update T_customer set cname=?,gender=?,birthday=?", "+" cellphone=?,email=?,description=? where cid=? "; O Bject[] params = {c.getcname (), C.getgender (), C.getbirthday (), C.getcellphone (), C.getemail (), C.getdescription (), C.getcid ()};qr.update (SQL, params);} catch (SQLException e) {throw new RuntimeException (e);}} /** * Multi-conditional combination query * @param criteria * @return */public list<customer> query (Customer criteria) {try {* * * 1. Give SQL template * 2 . Give the parameter * 3. Call the Query method, using the result set Processor: Beanlisthandler *//* * First, give the SQL template * Two, give the parameters! *//* * 1. Give the first half of a SQL statement */stringbuilder sql = new StringBuilder ("SELECT * from T_customer where 1=1");/* * 2. To determine the condition, complete appending the WHERE clause to SQL *//* * 3. Create a ArrayList to load the parameter values */list<object> params = new arraylist<object> (); String cname = Criteria.getcname (); if (CNAME! = null &&!cname.trim (). IsEmpty ()) {Sql.append ("and cname like?"); Params.add ("%" + CNAME + "%");} String gender = Criteria.getgender (); if (gender! = NULL &&!gender.trim (). IsEmpty ()) {Sql.append ("and gender=?"); Params.add (gender);} String cellphone = Criteria.getcellphone (); if (cellphone! = null &&!cellphone.trim (). IsEmpty ()) {Sql.append (" And cellphone like? "); Params.add ("%" + cellphone + "%");} String email = criteria.getemail (); if (email! = null &&!email.trim (). IsEmpty ()) {sql.append ("and email like?"); Params.add ("%" + email + "%");} /* * Third, execute query */return qr.query (sql.tostring (), New beanlisthandler<customer> (Customer.class), Params.toarray ( ));} catch (SQLException e) {throw new RuntimeException (e);}}}

Pagination

Web day19 Service Layer processing transactions (using threadlocal), Txqueryrunner gadgets, single-table exercises (add-and-revise operations), paging