Spring-MVC第二弹数据库链接

Spring-MVC第一弹简单项目搭建
Spring-MVC第二弹数据库链接

spring_mvc和mybatis jar包下载

数据库链接以及dao层和service层编写

数据库链接需要的jar包：
mybatis-3.4.1.jar
mybatis-spring-1.3.0.jar
mysql-connector-java-5.1.20.jar(最新的6.x链接出问题)
commons-dbcp-1.4.jar
commons-pool-1.6.jar

spring-jdbc-4.3.2.RELEASE.jar(自动下载spring-tx-4.3.2.RELEASE.jar)

pom.xml 中添加如下配置

<!-- mysql数据库配置 需要的jar -->

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.1</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.3.0</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.20</version>
        </dependency>
        <!-- mysql数据库配置 需要的jar -->

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!-- 数据库连接池dbcp 需要的jar -->
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>

        <dependency>
            <groupId>commons-pool</groupId>
            <artifactId>commons-pool</artifactId>
            <version>1.6</version>
        </dependency>

        <!-- 数据库连接池dbcp 需要的jar -->

配置数据库链接：
spring-servlet.xml
1，配置dao层和service层
2，配置数据库链接(用户名，密码，驱动，url)
3，配置sqlSessionFactory
4，配置mapper映射器

<!-- 扫描指定包,注意包名写法 及 use-default-filters="false" -->
    <context:component-scan base-package="com.axure.web">
        <context:include-filter type="annotation"
            expression="org.springframework.stereotype.Controller" />
        <context:include-filter type="annotation"
            expression="org.springframework.stereotype.Service" />
    </context:component-scan>


<!-- dbcp数据源配置 -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/leying?useUnicode=yes&amp;characterEncoding=utf-8" />
        <property name="username" value="root" />
        <property name="password" value="123456" />

        <!-- 池启动时创建的连接数量 -->
        <property name="initialSize" value="5" />
        <!-- 同一时间可以从池分配的最多连接数量。设置为0时表示无限制。 -->
        <property name="maxActive" value="30" />
        <!-- 池里不会被释放的最多空闲连接数量。设置为0时表示无限制。 -->
        <property name="maxIdle" value="20" />
        <!-- 在不新建连接的条件下，池中保持空闲的最少连接数。 -->
        <property name="minIdle" value="3" />
        <!-- 设置自动回收超时连接 -->
        <property name="removeAbandoned" value="true" />
        <!-- 自动回收超时时间(以秒数为单位) -->
        <property name="removeAbandonedTimeout" value="200" />
        <!-- 设置在自动回收超时连接的时候打印连接的超时错误 -->
        <property name="logAbandoned" value="true" />
        <!-- 等待超时以毫秒为单位，在抛出异常之前，池等待连接被回收的最长时间（当没有可用连接时）。设置为-1表示无限等待。 -->
        <property name="maxWait" value="100" />

    </bean>

<!-- 创建SqlSessionFactory，同时指定数据源 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation" value="classpath:/mybatis/mybatis-config.xml" />
        <property name="mapperLocations" value="classpath:/mybatis/sqlmapping/**/*.xml" />
    </bean>

<!-- 创建数据映射器，数据映射器必须为接口 ,可以把扫描到的Mapper接口变成Mapper对象 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--指定要扫描包： 多个包用逗号隔开 -->
        <property name="basePackage" value="com.axure.web.dao" />
        <!--指定sqlSessionFactory -->
        <property name="sqlSessionFactory" ref="sqlSessionFactory"></property>
    </bean>

mapper文件编写
UserInfoMapper.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="com.axure.web.dao.UserInfoDao">
    <resultMap id="BaseResultMap" type="com.axure.entity.User">
        <id column="userId" property="userId" jdbcType="INTEGER" />
        <result column="userName" property="userName" jdbcType="VARCHAR" />
        <result column="password" property="password" jdbcType="VARCHAR" />
        <result column="realName" property="realName" jdbcType="VARCHAR" />
    </resultMap>

    <!-- 添加用户信息 -->
    <insert id="register" keyProperty="userId" parameterType="com.axure.entity.User">
        insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <!-- <if test="userId != null">userId,</if> -->
            <if test="userName != null">userName,</if>
            <if test="password != null">password,</if>
            <if test="realName != null">realName,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <!-- <if test="userId != null"> #{userId,jdbcType=INTEGER}, </if> -->
            <if test="userName != null">
                #{userName,jdbcType=VARCHAR},
            </if>
            <if test="password != null">
                #{password,jdbcType=VARCHAR},
            </if>
            <if test="realName != null">
                #{realName,jdbcType=VARCHAR},
            </if>
        </trim>

    </insert>
    <!-- 查询用户信息 通过用户id -->

    <select id="getUserInfoById" resultMap="BaseResultMap"
        parameterType="int">
        select * from user where userId = #{id}
    </select>

    <!-- 查询用户信息 通过用户名称 -->
    <select id="getUserInfoByUserName" resultMap="BaseResultMap"
        parameterType="string">
        select * from user where userName = #{userName}
    </select>


    <!-- 更新用户信息 -->
    <update id="updateUserInfo" parameterType="com.axure.entity.User">
        update user
        <set>
            <if test="userName != null">
                userName = #{userName,jdbcType=VARCHAR},
            </if>
            <if test="password != null">
                password = #{password,jdbcType=VARCHAR},
            </if>
            <if test="realName != null">
                realName = #{realName,jdbcType=VARCHAR},
            </if>
        </set>
        where userId = #{userId,jdbcType=INTEGER}
    </update>


</mapper>

dao中的方法名称和mapper中的一一对应

service属于业务逻辑，灵活处理

dao中的UserInfoDao 接口；

public interface UserInfoDao {
    User getUserInfoById(int id);
    User getUserInfoByUserName(String userName);
    void updateUserInfo(User userInfo);
    int register(User userInfo);
}

Service中的UserInfoService 接口及实现类：

public interface UserInfoService {

    User getUserInfoById(int id);
    User getUserInfoByUserName(String userName);
    void updateUserInfo(User userInfo);
    int register(User userInfo);

}

实现类：自动注入UserInfoDao对象

@Service("userInfoService")
public class UserInfoServiceImpl implements UserInfoService{

    @Autowired
    UserInfoDao userInfoDao;

    public User getUserInfoById(int id) {
        return userInfoDao.getUserInfoById(id);
    }
    public User getUserInfoByUserName(String userName) {
        return userInfoDao.getUserInfoByUserName(userName);
    }

    public void updateUserInfo(User userInfo) {
        userInfoDao.updateUserInfo(userInfo);
    }

    public int register(User userInfo) {
        return userInfoDao.register(userInfo);
    }



}

Controller控制器：

@Controller
@RequestMapping("/user")
public class LoginController {

    @Autowired
    private UserInfoService userInfoService;

    /**
     * app 接口访问直接返回json数据
     * 
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "/json", method = RequestMethod.GET)
    public String returnJSON() {
        System.out.println("==========");
        return "index" + new Random().nextInt(100);
    }

    @ResponseBody
    @RequestMapping(value = "/register", method = RequestMethod.GET,produces = {"application/json;charset=UTF-8"})
    public String register(
                @RequestParam(value = "userName",required=true) String userName,
                @RequestParam(value = "password",required=true) String password,
                @RequestParam(value = "realName",required=false) String realName){
        User user = userInfoService.getUserInfoByUserName(userName);
        if (user!=null) {
            System.out.println("register  ===== 用户已经存在====="+user);
            return "该用户名已经存在";
        }
        User userInfo = new User(userName,password,realName);
        System.out.println("register  =====dao ====="+userInfo);
        int regResult = userInfoService.register(userInfo);
        if (regResult==1) {
            return "注册成功" + "regResult = " + regResult;
        }
        return "注册失败";
    }


    /**
     * pc or wap 返回 ModelAndView<br>
     * 如果jsp页面是在WEB-INF下，返回controller对应的路径。如果是这样用户无法直接访问对应的jsp页面
     * 
     * @param user
     * @return
     */
    @RequestMapping(value = "/register2", method = RequestMethod.GET)
    public ModelAndView register2(User user) {
        System.out.println("====register==jsp ===user = " + user);
        ModelAndView mv = new ModelAndView();
        mv.setViewName("user/loginSuccess");
        mv.addObject("user", user);
        return mv;
    }

    @ResponseBody
    @RequestMapping(value = "/getuserInfo", method = RequestMethod.GET,produces = {"application/json;charset=UTF-8"})
    public String getUserInfoById(@RequestParam(value = "userId",required=true) int userId) {
        System.out.println("====getuserInfo=====user = userId"+ userId);
        User user = userInfoService.getUserInfoById(userId);
        System.out.println("====getuserInfo=====user = userId"+ userId +"  " + user);
        return user.toString();
    }
}

创建数据库和表的sql语句：

 create database if not exists leying;
 use leying;
CREATE TABLE user
(
userId   int AUTO_INCREMENT PRIMARY KEY,
userName varchar(255),
password varchar(255),
realName varchar(255)
) CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=0;

INSERT INTO user(userName,password,realName) VALUES ('heaven','123','hyc');  

效果图：