SpringMVC+Spring+Mybatis+Maven整合-mybatis及SpringMVC、Mybatis查询
首先需要建立一下文件：
config.properties，数据库配置
hibernate.dialect=org.hibernate.dialect.MySQLDialect driverClassName=com.mysql.jdbc.Driver validationQuery=SELECT 1 jdbc_url=jdbc:mysql://localhost:3306/uracsdb?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull jdbc_username=root jdbc_password=root
log4j.properties，日志配置
log4j.rootLogger=DEBUG,Console,File
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.Target=System.out
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=[%c]%m%n
log4j.appender.File=org.apache.log4j.RollingFileAppender
log4j.appender.File.File=mybatis.log
log4j.appender.File.MaxFileSize=10MB
log4j.appender.File.Threshold=ALL
log4j.appender.File.layout=org.apache.log4j.PatternLayout
log4j.appender.File.layout.ConversionPattern=[%p][%d{yyyy-MM-dd HH\:mm\:ss,SSS}][%c]%m%n
spring-mvc.xml，配置
1 <?xml version="1.0" encoding="UTF-8"?> 2 <beans xmlns="http://www.springframework.org/schema/beans" xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans 3 http://www.springframework.org/schema/beans/spring-beans-3.0.xsd 4 http://www.springframework.org/schema/context 5 http://www.springframework.org/schema/context/spring-context-3.0.xsd 6 http://www.springframework.org/schema/mvc 7 http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd"> 8 9 <!-- 自动扫描controller包下的所有类，使其认为spring mvc的控制器 --> 10 <context:component-scan base-package="sy.controller" /> 11 12 <!-- 避免IE执行AJAX时,返回JSON出现下载文件 --> 13 <bean id="mappingJacksonHttpMessageConverter" class="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter"> 14 <property name="supportedMediaTypes"> 15 <list> 16 <value>text/html;charset=UTF-8</value> 17 </list> 18 </property> 19 </bean> 20 21 <!-- 启动Spring MVC的注解功能，完成请求和注解POJO的映射 --> 22 <bean class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter"> 23 <property name="messageConverters"> 24 <list> 25 <ref bean="mappingJacksonHttpMessageConverter" /><!-- json转换器 --> 26 </list> 27 </property> 28 </bean> 29 30 <!-- 对模型视图名称的解析，即在模型视图名称添加前后缀 --> 31 <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver" p:prefix="/" p:suffix=".jsp" /> 32 33 <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> 34 <property name="defaultEncoding"> 35 <value>UTF-8</value> 36 </property> 37 <property name="maxUploadSize"> 38 <value>32505856</value><!-- 上传文件大小限制为31M，31*1024*1024 --> 39 </property> 40 <property name="maxInMemorySize"> 41 <value>4096</value> 42 </property> 43 </bean> 44 45 </beans>
spring-mybatis.xml，配置
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd"> <!-- 自动扫描controller包下的所有类，使其认为spring mvc的控制器 --> <context:component-scan base-package="sy.controller" /> <!-- 避免IE执行AJAX时,返回JSON出现下载文件 --> <bean id="mappingJacksonHttpMessageConverter" class="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter"> <property name="supportedMediaTypes"> <list> <value>text/html;charset=UTF-8</value> </list> </property> </bean> <!-- 启动Spring MVC的注解功能，完成请求和注解POJO的映射 --> <bean class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter"> <property name="messageConverters"> <list> <ref bean="mappingJacksonHttpMessageConverter" /><!-- json转换器 --> </list> </property> </bean> <!-- 对模型视图名称的解析，即在模型视图名称添加前后缀 --> <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver" p:prefix="/" p:suffix=".jsp" /> <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> <property name="defaultEncoding"> <value>UTF-8</value> </property> <property name="maxUploadSize"> <value>32505856</value><!-- 上传文件大小限制为31M，31*1024*1024 --> </property> <property name="maxInMemorySize"> <value>4096</value> </property> </bean> </beans>
spring.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" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd "> <!-- 引入属性文件 --> <context:property-placeholder location="classpath:config.properties" /> <!-- 自动扫描(自动注入) --> <context:component-scan base-package="sy.service" /> </beans>
SpringMVC-Controller-Mybatis测试
@Controller @RequestMapping("/userController") public class UserController { private UserServiceI userService; public UserServiceI getUserService() { return userService; } @Autowired public void setUserService(UserServiceI userService) { this.userService = userService; } //@RequestMapping("/showUser/{id}") @RequestMapping("/{id}/showUser") public String showUser(@PathVariable String id,HttpServletRequest requet){ User user = userService.getUserById(id); requet.setAttribute("user", user); return "showUser"; } }
启动tomcat输入连接测试：http://localhost:8080/testmybatis/userController/2/showUser.do
SpringMVC-Mybatis测试
@RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = {"classpath:spring.xml","classpath:spring-mybatis.xml"}) public class TestMybatis{ private static final Logger logger = Logger.getLogger(TestMybatis.class); // private ApplicationContext ac; private UserServiceI userService; public UserServiceI getUserService() { return userService; } @Autowired public void setUserService(UserServiceI userService) { this.userService = userService; } // @Before // public void before(){ // ac = new ClassPathXmlApplicationContext(new String[]{"spring.xml","spring-mybatis.xml"}); // userService = (UserServiceI)ac.getBean("userService"); // } @Test public void test1(){ User u = userService.getUserById("2"); logger.info(JSON.toJSONStringWithDateFormat(u, "yyyy-MM-dd HH:mm:ss")); } @Test public void test2(){ List<User> u = userService.getAll(); logger.info(JSON.toJSONStringWithDateFormat(u, "yyyy-MM-dd HH:mm:ss")); } @Test public void test3() { List<User> l = userService.getAll2(); logger.info(JSON.toJSONStringWithDateFormat(l, "yyyy-MM-dd HH:mm:ss")); } @Test public void test4() { List<User> l = userService.getAll3(); logger.info(JSON.toJSONStringWithDateFormat(l, "yyyy-MM-dd HH:mm:ss")); } @Test public void test5() { List<User> l = userService.getAll4(); logger.info(JSON.toJSONStringWithDateFormat(l, "yyyy-MM-dd HH:mm:ss")); } }
UserMapper.xml，配置
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="sy.dao.UserMapper"> <resultMap id="BaseResultMap" type="sy.model.User"> <id column="id" property="id" jdbcType="INTEGER" /> <result column="userName" property="username" jdbcType="VARCHAR" /> <result column="passWord" property="password" jdbcType="VARCHAR" /> <result column="createTime" property="createtime" jdbcType="TIMESTAMP" /> <result column="lastUpdate" property="lastupdate" jdbcType="TIMESTAMP" /> <result column="status" property="status" jdbcType="INTEGER" /> </resultMap> <sql id="Base_Column_List"> id, userName, passWord, createTime, lastUpdate, status </sql> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer"> select <include refid="Base_Column_List" /> from t_app_user where id = #{id,jdbcType=INTEGER} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer"> delete from t_app_user where id = #{id,jdbcType=INTEGER} </delete> <insert id="insert" parameterType="sy.model.User"> insert into t_app_user (id, userName, passWord, createTime, lastUpdate, status ) values (#{id,jdbcType=INTEGER}, #{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, #{createtime,jdbcType=TIMESTAMP}, #{lastupdate,jdbcType=TIMESTAMP}, #{status,jdbcType=INTEGER} ) </insert> <insert id="insertSelective" parameterType="sy.model.User"> insert into t_app_user <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id != null"> id, </if> <if test="username != null"> userName, </if> <if test="password != null"> passWord, </if> <if test="createtime != null"> createTime, </if> <if test="lastupdate != null"> lastUpdate, </if> <if test="status != null"> status, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null"> #{id,jdbcType=INTEGER}, </if> <if test="username != null"> #{username,jdbcType=VARCHAR}, </if> <if test="password != null"> #{password,jdbcType=VARCHAR}, </if> <if test="createtime != null"> #{createtime,jdbcType=TIMESTAMP}, </if> <if test="lastupdate != null"> #{lastupdate,jdbcType=TIMESTAMP}, </if> <if test="status != null"> #{status,jdbcType=INTEGER}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="sy.model.User"> update t_app_user <set> <if test="username != null"> userName = #{username,jdbcType=VARCHAR}, </if> <if test="password != null"> passWord = #{password,jdbcType=VARCHAR}, </if> <if test="createtime != null"> createTime = #{createtime,jdbcType=TIMESTAMP}, </if> <if test="lastupdate != null"> lastUpdate = #{lastupdate,jdbcType=TIMESTAMP}, </if> <if test="status != null"> status = #{status,jdbcType=INTEGER}, </if> </set> where id = #{id,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="sy.model.User"> update t_app_user set userName = #{username,jdbcType=VARCHAR}, passWord = #{password,jdbcType=VARCHAR}, createTime = #{createtime,jdbcType=TIMESTAMP}, lastUpdate = #{lastupdate,jdbcType=TIMESTAMP}, status = #{status,jdbcType=INTEGER} where id = #{id,jdbcType=INTEGER} </update> <select id="selectUserRole" parameterType="java.lang.String" resultType="sy.model.User"> SELECT t_app_user_role.id, t_app_user_role.userId, t_app_user_role.roleId FROM t_app_user_role where t_app_user_role.userId=#{id,jdbcType=VARCHAR} </select> <resultMap type="sy.model.User" id="userResultMap" extends="BaseResultMap"> <collection property="userroles" column="id" javaType="list" select="selectUserRole" /> </resultMap> <select id="getAll" resultMap="userResultMap"> select id, userName, passWord, createTime, lastUpdate, status from t_app_user </select> <resultMap type="sy.model.User" id="userResultMap2" extends="BaseResultMap"> <collection property="userroles" javaType="list" ofType="sy.model.UserRole"> <id property="id" column="userrole_id" /> <result column="roleid" property="roleId" /> <result property="userid" column="user_id" /> </collection> </resultMap> <select id="getAll2" resultMap="userResultMap2"> SELECT t_app_user.id, t_app_user.userName, t_app_user.`passWord`, t_app_user.createTime, t_app_user.lastUpdate, t_app_user.`status`, t_app_user_role.id userrole_id, t_app_user_role.userId user_id, t_app_user_role.roleId role_id FROM t_app_user INNER JOIN t_app_user_role ON t_app_user.id = t_app_user_role.userId </select> <resultMap type="sy.model.User" id="userResultMap3" extends="BaseResultMap"> <collection property="userroles" javaType="list" ofType="sy.model.UserRole"> <id property="id" column="userrole_id" /> <result column="roleid" property="roleId" /> <result property="userid" column="user_id" /> <association property="role" javaType="sy.model.Role"> <id property="id" column="role_id" /> <result property="rolename" column="role_text" /> </association> </collection> </resultMap> <select id="getAll3" resultMap="userResultMap3"> SELECT t_app_user.id, t_app_user.userName, t_app_user.createTime, t_app_user.lastUpdate, t_app_user.`status`, t_app_user.`passWord`, t_app_user_role.id userrole_id, t_app_user_role.userId user_id, t_app_user_role.roleId role_id, t_app_role.id role_id, t_app_role.roleName role_text, t_app_role.createTime role_createtime, t_app_role.lastUpdate role_lastUpdate, t_app_role.`status` role_status FROM t_app_user INNER JOIN t_app_user_role ON t_app_user.id = t_app_user_role.userId INNER JOIN t_app_role ON t_app_user_role.roleId = t_app_role.id </select> <resultMap type="sy.model.User" id="userResultMap4" extends="BaseResultMap"> <collection property="roles" javaType="list" ofType="sy.model.Role"> <id property="id" column="role_id" /> <result property="rolename" column="role_text" /> </collection> </resultMap> <select id="getAll4" resultMap="userResultMap4"> SELECT t_app_user.id, t_app_user.userName, t_app_user.createTime, t_app_user.lastUpdate, t_app_user.`status`, t_app_user.`passWord`, t_app_user_role.id, t_app_user_role.userId, t_app_user_role.roleId, t_app_role.id role_id, t_app_role.roleName role_text, t_app_role.createTime, t_app_role.lastUpdate role_lastUpdate, t_app_role.`status` FROM t_app_user INNER JOIN t_app_user_role ON t_app_user.id = t_app_user_role.userId INNER JOIN t_app_role ON t_app_user_role.roleId = t_app_role.id </select> </mapper>
测试数据库脚本位置
