SPRINGMVC (14): Example of using SPRINGMVC+SPRING+JDBC to optimize the Order management system (multi-Criteria Query user List feature implementation)

18/1/16

In the SPRINGMVC (13): Using SPRINGMVC to optimize the order management System example (login and logout of the simple implementation), to the "Supermarket order management system" to add a new function: User management function (jump all user queries and conditional user query);

Learning stage, so the use of the framework: SPRINGMVC + spring + JDBC, added user query function, but the paging function is not perfect, the related classes have Userdao, userservice;sql for the condition of the linked table query;

The contents of the "0" MySQL data sheet, as required, will be queried for the following:

Figure 1

"1" File framework and Jar package (Add the Role entity class/dao/service, and Userservice/userdao also adds the method):

Figure 2

Figure 3

Figure 4-jar Package

"2" after landing into the system page: sys.jsp, there will be user query and user management of two options. Therefore, modify the sys.jsp (the main code is listed below):

	
 

Output effect:


Figure 5



Explanation: 1, type the data in the input box, click "Submit" will send to controller;2, direct click HYPERLINK "Jump to User management, no query conditions." The request to the controller is also sent.



"3" for both requests, a processor @RequestMapping (value= "/userlist") +private String getuserlist () {} is used for processing, so the Com\user\controller\ Usercontroller.java Add the following content:


	@RequestMapping (value= "/userlist") Private String Getuserlist (model model, @RequestParam (value= "Queryusername", requ Ired=false) string queryusername, @RequestParam (value= "Queryuserrole", Required=false) string queryuserrole, @R Equestparam (value= "PageIndex", Required=false) String pageIndex) throws sqlexception{log.info ("Getuserlist ====>
		Queryusername: "+queryusername);
		Log.info ("Getuserlist ====> queryuserrole:" +queryuserrole);
		Log.info ("Getuserlist ====> pageIndex:" +pageindex);
		int _queryuserrole = 0;
		List<user> userlist = null;
		Set page capacity//int pageSize = constants.pagesize;
		int pageSize = 5;
		Sets the current page number int currentpageno = 1;
		if (Queryusername = = null) {queryusername = "";
		} if (queryuserrole! = null &&!queryuserrole.equals ("")) {_queryuserrole = Integer.parseint (queryuserrole);
			} if (PageIndex! = null) {try{Currentpageno = integer.valueof (PageIndex); }catch (NumberFormatException e) {return "redirEct:/user/syserror.html ";
		
		}}//total number int totalcount = Userservice.getusercount (queryusername,_queryuserrole);
		Total pages * * * pagesupport pages = new Pagesupport ();
		Pages.setcurrentpageno (Currentpageno);
		Pages.setpagesize (pageSize);
		Pages.settotalcount (TotalCount);
		
		int totalpagecount = Pages.gettotalpagecount ();
		Control the first and last if (Currentpageno < 1) {Currentpageno = 1;
		}else if (Currentpageno > Totalpagecount) {currentpageno = Totalpagecount;
		} userlist = Userservice.getuserlist (queryusername,_queryuserrole,currentpageno,pagesize);
		Model.addattribute ("UserList", userlist);
		List<role> rolelist = null;
		Rolelist = Roleservice.getrolelist ();
		Rolelist Model.addattribute ("Rolelist", rolelist);
		Input data Model.addattribute ("Queryusername", queryusername);
		Model.addattribute ("Queryuserrole", queryuserrole);
		Underline data Model.addattribute ("Totalpagecount", Totalpagecount);
		Model.addattribute ("TotalCount", totalcount); MoDel.addattribute ("Currentpageno", Currentpageno);
	return "UserList"; }
 


Explanation: 1, because there is no perfect paging function, paging related variables and functions are pseudo-processing (that is, the empty function of the class);

2, the View may pass in the three parameters are set in order to: @RequestParam (value= "XXXX", Required=false);

3, the model is used to transfer parameters (nature is also map);


"4" in Userserviceimpl.java Add the following:


	@Override public
	list<user> getuserlist (String queryusername, int _queryuserrole, int currentpageno, int PageSize) throws SQLException {
		return userdao.getuserlist (Queryusername, _queryuserrole, Currentpageno, pageSize );	
	}


	@Override public
	int Getusercount (String queryusername, int _queryuserrole) throws SQLException {
		return Userdao.getusercount (Queryusername,_queryuserrole);
	}
	
	
 

Add the following to the interface Userservice.java:


	Public list<user>  getuserlist (String queryusername,int _queryuserrole,
						int currentpageno,int pageSize ) throws SQLException;
	public int Getusercount (String queryusername,int _queryuserrole) throws SQLException;
 

"5" in Userdaoimpl.java Add the following:

	@Override public list<user> getuserlist (String queryusername, int _queryuserrole, int currentpageno, int pageSize)
		Throws SQLException {Logger log = Logger.getlogger (UserController.class.getName ());

		list<user> userlist = new arraylist<user> ();  sql = "Select U.usercode,u.username,u.gender,u.birthday,u.phone,r.rolename" + "from Smbms_user u,smbms_role R" + "Where U.username like CONCAT ('% ', '" +queryusername+ "', '% ')" + "and u.userrole=" +_queryuserrole + "and U.
		
		Userrole=r.id; "; For a request for a direct jump, assign another squall statement if (Queryusername = = "") {sql = "Select U.usercode,u.username,u.gender,u.birthday,u.phone,
		
		R.rolename "+" from Smbms_user u,smbms_role R "+" where u.userrole=r.id; ";}
		System.out.println ("Userdao Executed by sql:" +sql);
		String Driver = "Com.mysql.jdbc.Driver";
		String url = "Jdbc:mysql://localhost:3306/test";
		String username = "root";
		String password = "";
			try {class.forname (driver); conn = DrivermanAger.getconnection (Url,username,password);
		} catch (ClassNotFoundException e) {e.printstacktrace ();
		} stmt = Conn.createstatement ();
		rs = stmt.executequery (SQL);
			while (Rs.next ()) {User user = new User ();//It is important to open up a new space to store the new value:
			User.setusercode (rs.getstring (1));
			User.setusername (rs.getstring (2));
			User.setgender (Rs.getint (3));
			User.setbirthday (Rs.getdate (4));
			User.setphone (Rs.getstring (5));
			User.setrolename (rs.getstring (6));
			Log.info ("Loop:" +user);
			Userlist.add (user);
		Log.info ("Inside--result:" +userlist);
		} log.info ("Outside--result:" +userlist);
		if (stmt!=null) {stmt.close ();
		} if (Rs!=null) {rs.close ();
	} return userlist; } @Override public int getusercount (String queryusername, int _queryuserrole) throws SQLException {Logger log = Logg
		Er.getlogger (UserController.class.getName ());
		arraylist<user> userlist = new arraylist<user> ();
		User user = new user ();
		
		int count = 0; First method//sql = "SELECT * F"Rom Smbms_user "//+" where u.username like CONCAT ('% ', #{"+queryusername+"}, '% ') "//+" and u.userrole= "+_queryus
		
		Errole+ ";";
		
		The second method of sql = "Select COUNT (' ID ') from Smbms_user";
		SYSTEM.OUT.PRINTLN ("sql:" +sql);
		String Driver = "Com.mysql.jdbc.Driver";
		String url = "Jdbc:mysql://localhost:3306/test";
		String username = "root";
		String password = "";
			try {class.forname (driver);
		conn = Drivermanager.getconnection (Url,username,password);
		} catch (ClassNotFoundException e) {e.printstacktrace ();
		} stmt = Conn.createstatement ();
		rs = stmt.executequery (SQL);
		Rs.next ();
		Count = Rs.getint (1);
		if (stmt!=null) {stmt.close ();
		} if (Rs!=null) {rs.close ();
	} return count;
 }
 

Add the following to the interface Userdao.java:

 

	Public list<user> getuserlist (String queryusername, int _queryuserrole, int currentpageno, int pageSize) throws SQL Exception;

	public int Getusercount (String queryusername,int _queryuserrole) throws SQLException;
 

 

 
"6" New package: \com\role\entities, adding Role.java entity class:


Package com. role.entities;

Import Java.math.BigInteger;
Import java.util.Date;

public class Role {
	private Integer ID;
	Private String Rolecode;
	Private String roleName;
	Private BigInteger createdby;
	Private Date CreationDate;
	Private BigInteger modifyby;
	Private Date modifydate;
	...... Omit Setter/getter method, no parameter/parameter construction method;
}
 


"7" New package \com\role\service, interface Roleservice.java:


Package com. Role.service;

Import java.sql.SQLException;
Import java.util.List;
Import com. Role.entities.Role;

Public interface RoleService {public
	list<role> getrolelist () throws SQLException;
}
 

To add an implementation class Roleserviceimpl.java:

Package com. Role.service;

Import java.sql.SQLException;
Import java.util.List;

Import org.springframework.beans.factory.annotation.Autowired;
Import Org.springframework.stereotype.Service;

Import com. Role.Dao.RoleDao;
Import com. Role.entities.Role;

@Service ("RoleService") Public
class Roleserviceimpl implements RoleService {
	@Autowired
	private Roledao Roledao;
	
	@Override public
	list<role> getrolelist () throws SQLException {
		return roledao.getrolelist ();
	
}
 

"8" new package \com\role\dao, interface Roledao.java:

Package com. Role.dao;

Import java.sql.SQLException;
Import java.util.List;

Import com. Role.entities.Role;

Public interface Roledao {public
	list<role> getrolelist () throws SQLException;
}
 

 

 
To add an implementation class Roledaoimpl.java:


Package com.

Role.dao;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.sql.Statement;
Import java.util.ArrayList;

Import java.util.List;
Import Org.apache.log4j.Logger;

Import org.springframework.stereotype.Component; Import com.
Role.entities.Role; Import com.
User.Controller.UserController; Import com.

User.entities.User;
	@Component ("Roledao") public class Roledaoimpl implements Roledao {private Connection conn = null;
	Private Statement stmt = null;
	Private ResultSet rs = null;

	private String SQL; Public list<role> getrolelist () throws SQLException {Logger log = Logger.getlogger (UserController.class.getName (
		));
		arraylist<role> rolelist = new arraylist<role> ();

		Role role = new role ();
		sql = "Select Id,rolecode,rolename from Smbms_role;";
		System.out.println ("Roledao Executed by sql:" +sql);
		SYSTEM.OUT.PRINTLN ("sql:" +sql);
		String Driver = "Com.mysql.jdbc.Driver"; String urL = "Jdbc:mysql://localhost:3306/test";
		String username = "root";
		String password = "";
			try {class.forname (driver);
		conn = Drivermanager.getconnection (Url,username,password);
		} catch (ClassNotFoundException e) {e.printstacktrace ();
		} stmt = Conn.createstatement ();
		rs = stmt.executequery (SQL);
			while (Rs.next ()) {Role.setid (Rs.getint (1));
			Role.setrolecode (rs.getstring (2));
			Role.setrolename (Rs.getstring (3));
		Rolelist.add (role);
		} if (Stmt!=null) {stmt.close ();
		} if (Rs!=null) {rs.close ();
	} return rolelist;
 }


}
 

At this point, the background logic is complete;


"9" Configuration file:

1. Web. xml


<?xml version= "1.0" encoding= "UTF-8"?> <web-app xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance" xml ns= "Http://java.sun.com/xml/ns/javaee" xsi:schemalocation= "Http://java.sun.com/xml/ns/javaee http://java.sun.com /xml/ns/javaee/web-app_2_5.xsd "id=" webapp_id "version=" 2.5 "> <!--this specifies the directory where the Log4j.xml is placed---& Lt;context-param> <param-name>log4jConfigLocation</param-name> <param-value>classpath: Log4j.properties</param-value> </context-param> <!--Be sure to add this listener--<listener> &L t;listener-class> Org.springframework.web.util.Log4jConfigListener </listener-class> </lis tener> <!--configuration Dispatcherservlet-<servlet> <servlet-name>springmvc</serv  
        Let-name> <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> <init-param> <Param-name>contextconfiglocation</param-name> <param-value>classpath:springmvc-servlet.xml&lt ;/param-value> </init-param> <!--container is loaded when it starts--&LT;LOAD-ON-STARTUP&GT;1&L t;/load-on-startup> </servlet> <servlet-mapping> <servlet-name>springmvc</se rvlet-name> <url-pattern>/</url-pattern> </servlet-mapping> <filter > <filter-name>springUtf8Encoding</filter-name> <filter-class> Org.springframework.web.filter.characterencodingfilter</filter-class> <init-param> <param-name >encoding</param-name> <param-value>UTF-8</param-value> </init-param> <init-param&
			Gt <param-name>forceEncoding</param-name> <param-value>true</param-value> </init-param > </filter> <filter-mapping> &LT;FILTER-NAME&GT;SPRingutf8encoding</filter-name> <url-pattern>/*</url-pattern> </filter-mapping> <welcome
    
    -file-list> <welcome-file>/WEB-INF/jsp/login.jsp</welcome-file> </welcome-file-list> <context-param> <param-name>contextConfigLocation</param-name> <param-value>classpath: Applicationcontext-*.xml</param-value> </context-param> <!--Configure Spring's Contextloaderlistener listener, initialize Spring Containers-<listener> <listener-class> org.springframework.web.context.ContextLoaderListe   NER </listener-class> </listener> </web-app>
 

2, Springmvc-servlet.xml

<?xml version= "1.0" encoding= "UTF-8"?> <beans xmlns= "Http://www.springframework.org/schema/beans" xmlns:  
    Xsi= "Http://www.w3.org/2001/XMLSchema-instance" xmlns:context= "Http://www.springframework.org/schema/context" Xmlns:mvc= "Http://www.springframework.org/schema/mvc" xsi:schemalocation= "Http://www.springframework.org/schema /beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/c Ontext http://www.springframework.org/schema/context/spring-context-4.0.xsd http://www.springframework. 
	 Org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd "> <!--MVC static resource access-- <mvc:resources mapping= "/statics/**" location= "/statics/"/> <!--one-click Configuration--<context:component- Scan base-package= "com.  
  
   	User.controller "></context:component-scan> <mvc:annotation-driven></mvc:annotation-driven> <! --Configure static resource access <mvc:resources mapping= "/resources/**" location= "/resources/"/> <mvc:resources mapping= "/ images/** "location="/images/"/> <mvc:resources mapping="/js/** "location="/js/"/>-<!--configuration view Figure parser: How to resolve the handler method return value to the actual physical view-<!--prefix prefix +suffix suffix--<bean class= "Org.springframewor K.web.servlet.view.internalresourceviewresolver "> <property name=" prefix "value="/web-inf/jsp/"&GT;&LT;/PR operty> <property name= "suffix" value= ". jsp" ></property> </bean> < !--Global Exception Handling-<!----> <bean class= "Org.springframework.web.servlet.handler.SimpleMappingExceptionReso Lver "> <property name=" exceptionmappings "> <props> <prop key=" Java.lang.RuntimeException "  >login</prop> </props> </property> </bean> </beans>
 

3. Spring configuration file--Applicationcontext-jdbc.xml:

<?xml version= "1.0" encoding= "UTF-8"?> <beans xmlns= "Http://www.springframework.org/schema/beans" xmlns:
 	Xsi= "Http://www.w3.org/2001/XMLSchema-instance" xmlns:context= "Http://www.springframework.org/schema/context" xmlns:aop= "HTTP://WWW.SPRINGFRAMEWORK.ORG/SCHEMA/AOP" xmlns:tx= "Http://www.springframework.org/schema/tx" xmlns: p= "http://www.springframework.org/schema/p" xsi:schemalocation= "Http://www.springframework.org/schema/beans http ://www.springframework.org/schema/beans/spring-beans.xsd Http://www.springframework.org/schema/context http:/
        	/www.springframework.org/schema/context/spring-context-4.0.xsd HTTP://WWW.SPRINGFRAMEWORK.ORG/SCHEMA/AOP Http://www.springframework.org/schema/aop/spring-aop-4.2.xsd "> <context:component-scan base-package=" com. User.dao "></context:component-scan> <context:component-scan base-package=" com. User.service "></context:component-scan> <context:component-scan bAse-package= "com. Role.dao "></context:component-scan> <context:component-scan base-package=" com.
 Role.service "></context:component-scan> </beans>
 

"10" Output Result:
1. Unconditional inquiry:



Figure 6



2, with conditional query:



Figure 7



Figure 8