spring-mvc-mybatis

---

前言

通过spring-MVC-mybatis技术,实现接口暴露,在网页上显示mysql查询的数据。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、准备工作

在虚拟机的mysql中创建数据表,例如进入到test数据库,建表:

create table user_info(
user_id int(6) zerofill auto_increment primary key,
user_name varchar(10) not null,
user_account varchar(30) not null,
user_pass varchar(50) not null,
user_phone varchar(20) not null unique key,
user_gender char(1) not null,
user_pid varchar(20) not null unique key,
user_province varchar(50) not null,
user_city varchar(50) not null,
user_district varchar(50) not null,
user_address varchar(200) not null,
user_balance decimal(10,2) not null default 2000
);

insert into user_info(user_id,user_name,user_account,user_pass,user_phone,user_gender,user_pid,user_province,user_city,user_district,user_address) values
(1,'张三','ZhangSan',md5('123456'),'18818818888','男','111111111111111111','江苏省','南京市','秦淮区','夫子庙');
(数据可以多加)

在电脑上安装tomcat,便于浏览器查看数据

二、创建工程

1.准备环境

在maven下创建webapp工程,在quickstart下第三个,具体为org.apache.maven.archetypes:maven-archetype-webapp.
然后打jar包,修改maven环境配置,pom中打jar包代码如下:

<groupId>cn.kgc</groupId>
  <artifactId>springmvcmybatis01</artifactId>
  <version>1.0</version>
  <packaging>war</packaging>

  <name>springmvcmybatis01 Maven Webapp</name>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.47</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.6</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>2.0.6</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.3.7</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.3.7</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-web</artifactId>
      <version>5.3.7</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>5.3.7</version>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.12.3</version>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-core</artifactId>
      <version>2.12.3</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>4.0.1</version>
    </dependency>
  </dependencies>

  <build>

2.创建文件夹

有的创建文件夹,类下面创建是包,package,然后创建接口,实体类,写java源代码要用蓝色文件夹的java,创建资源resources(带黄色下标),创建userMapper.xml时抬头可以使用快捷方式,

代码如下

<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="">

创建spring-mvc.xml和spring-mybatis.xml时,new的时候点XML Configuration File下面的Spring Config进行创建

三. 代码内容

1.创建实体类

entity下面的实体类
UserInfo类:

package cn.kgc.smmvc.entity;

public class UserInfo {
    private Integer user_id;
    private String user_name;
    private String user_account;
    private String user_pass;
    private String user_phone;
    private String user_gender;
    private String user_pid;
    private String user_province;
    private String user_city;
    private String user_district;
    private String user_address;
    private Double user_balance;

    public Integer getUser_id() {
        return user_id;
    }

    public void setUser_id(Integer user_id) {
        this.user_id = user_id;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public String getUser_account() {
        return user_account;
    }

    public void setUser_account(String user_account) {
        this.user_account = user_account;
    }

    public String getUser_pass() {
        return user_pass;
    }

    public void setUser_pass(String user_pass) {
        this.user_pass = user_pass;
    }

    public String getUser_phone() {
        return user_phone;
    }

    public void setUser_phone(String user_phone) {
        this.user_phone = user_phone;
    }

    public String getUser_gender() {
        return user_gender;
    }

    public void setUser_gender(String user_gender) {
        this.user_gender = user_gender;
    }

    public String getUser_pid() {
        return user_pid;
    }

    public void setUser_pid(String user_pid) {
        this.user_pid = user_pid;
    }

    public String getUser_province() {
        return user_province;
    }

    public void setUser_province(String user_province) {
        this.user_province = user_province;
    }

    public String getUser_city() {
        return user_city;
    }

    public void setUser_city(String user_city) {
        this.user_city = user_city;
    }

    public String getUser_district() {
        return user_district;
    }

    public void setUser_district(String user_district) {
        this.user_district = user_district;
    }

    public String getUser_address() {
        return user_address;
    }

    public void setUser_address(String user_address) {
        this.user_address = user_address;
    }

    public Double getUser_balance() {
        return user_balance;
    }

    public void setUser_balance(Double user_balance) {
        this.user_balance = user_balance;
    }
}

UserCond类(里面为要查询的字段):

package cn.kgc.smmvc.entity;

public class UserCond {
    private String user_name;
    private String user_gender;
    private Integer user_id;
    private String user_city;

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public String getUser_gender() {
        return user_gender;
    }

    public void setUser_gender(String user_gender) {
        this.user_gender = user_gender;
    }

    public Integer getUser_id() {
        return user_id;
    }

    public void setUser_id(Integer user_id) {
        this.user_id = user_id;
    }

    public String getUser_city() {
        return user_city;
    }

    public void setUser_city(String user_city) {
        this.user_city = user_city;
    }
}

Message类(网页要显示的结构形式):

package cn.kgc.smmvc.entity;

public abstract class Message<T> {
    private int code;
    private T t;

    public static <T> Message succeed(T t){
        return new Message(200,t) {};
    }

    public static <T> Message fail(T t){
        return new Message(500,null) {};
    }

    public Message(int code, T t) {
        this.code = code;
        this.t = t;
    }

    public int getCode() {
        return code;
    }

    public T getT() {
        return t;
    }
}

创建接口UserMapper:

package cn.kgc.smmvc.biz.mapper;

import cn.kgc.smmvc.entity.UserCond;
import cn.kgc.smmvc.entity.UserInfo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserMapper {
    @Select("select * from user_info limit 0,10")
    List<UserInfo> top10();

    @Select("select * from user_info where user_id=#{user_id}")
    UserInfo findById(int user_id);

    @Select("select count(1) from user_info where user_account=#{acc} and user_pass=md5(#{pass})")
    int login(@Param("acc") String account,@Param("pass") String password);

    /*
    * 一个参数随便写
    * <=3个参数,直接入参,但要加注解
    * >3个参数,封装入参
    * @return
    * */
    List<UserInfo> findBy(UserCond uc);
}

创建UserService :

package cn.kgc.smmvc.biz.service;

import cn.kgc.smmvc.biz.mapper.UserMapper;
import cn.kgc.smmvc.entity.UserCond;
import cn.kgc.smmvc.entity.UserInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {
    @Autowired
    UserMapper userMapper;

    public List<UserInfo> top10(){
        return userMapper.top10();
    }

    public boolean login(String acc,String pass){
        return userMapper.login(acc,pass)>0;
    }

    public List<UserInfo> findBy(UserCond uc){
        return userMapper.findBy(uc);
    }

    public UserInfo findById(int user_id){
        return userMapper.findById(user_id);
    }
}

创建UserControl:

package cn.kgc.smmvc.control;


import cn.kgc.smmvc.biz.service.UserService;
import cn.kgc.smmvc.entity.Message;
import cn.kgc.smmvc.entity.UserCond;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;

import java.sql.SQLException;

//http://localhost:8080/user
@RestController
@CrossOrigin("*")
@RequestMapping("/user")
public class UserControl {
    @Autowired
    UserService userService;

    @RequestMapping("/top10.do")
    public Message top10(){
        return Message.succeed(userService.top10());
    }

    @Transactional(rollbackFor = SQLException.class,isolation = Isolation.READ_COMMITTED,propagation = Propagation.REQUIRED)
    @RequestMapping("/fb.do")
    public Message findBy(UserCond uc){
        return Message.succeed(userService.findBy(uc));
    }

    @RequestMapping("/login.do")
    public Message login(@RequestParam("acc") String acc,@RequestParam("pass") String pass){
        return Message.succeed(userService.login(acc, pass));
    }

    @RequestMapping("/{id}/fbi.do")
    public Message findById(@PathVariable("id") int user_id){
        return Message.succeed(userService.findById(user_id));
    }
}

2.创建xml和properties 文件

创建mysql的连接 datasource.properties:

mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://192.168.99.999:3306/test?useSSL=false
mysql.username=root
mysql.password=12345678

创建log4j的配置文件:

log4j.rootLogger=INFO, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d %p [%c] - %m%n
log4j.appender.logfile=org.apache.log4j.FileAppender
log4j.appender.logfile.File=log/hd.log
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=%d %p [%c] - %m%n

写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="cn.kgc.smmvc.biz.mapper.UserMapper">
    <select id="findBy" parameterType="UserCond" resultType="UserInfo">
        select * from user_info
        <where>
            <choose>
                <when test="null != user_id">
                    user_id=#{user_id}
                </when>
                <otherwise>
                    <if test="null != user_name">
                        and user_name like concat('%',#{user_name},'%')
                    </if>
                    <if test="null != user_gender">
                        and user_gender=#{user_gender}
                    </if>
                    <if test="null != user_city">
                        and user_city=#{user_city}
                    </if>
                </otherwise>
            </choose>
        </where>
    </select>
</mapper>

写spring-mvc.xml :

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       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.xsd
               http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
">

    <context:annotation-config/>
    <context:component-scan base-package="cn.kgc.smmvc.control"/>
    <mvc:annotation-driven/>
    <mvc:default-servlet-handler/>

</beans>

写spring-mybatis.xml :

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       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.xsd
">

    <context:property-placeholder location="classpath*:datasource.properties"/>
    <context:annotation-config/>
    <context:component-scan base-package="cn.kgc.smmvc.biz"/>

    <!-- springframework 控制数据源连接和事务 -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://192.168.91.180:3306/test?test?useSSL=false"/>
        <property name="username" value="root"/>
        <property name="password" value="12345678"/>
    </bean>

    <!--  springframework 声明式事务配置 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!-- mybatis-spring 创建sqlSessionFactory -->
    <bean id="sessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="typeAliasesPackage" value="cn.kgc.smmvc.entity"/>
        <property name="mapperLocations" value="classpath*:mapping/*Mapper.xml"/>
    </bean>

    <!-- mybatis-spring 配置扫描路径托管mapper对象给spring容器 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="sessionFactory"/>
        <property name="basePackage" value="cn.kgc.smmvc.biz.mapper"/>
    </bean>
</beans>

web.xml :

<?xml version="1.0" encoding="UTF-8"?>
<web-app
        version="3.0"
        xmlns="http://java.sun.com/xml/ns/javaee"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">

  <context-param>
    <param-name>contextConfigLocation</param-name>
    <param-value>classpath*:spring-mybatis.xml</param-value>
  </context-param>

  <listener>
    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
  </listener>

  <filter>
    <filter-name>CharSetFilter</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>
      <param-name>forceEncoding</param-name>
      <param-value>true</param-value>
    </init-param>
  </filter>
  <filter-mapping>
    <filter-name>CharSetFilter</filter-name>
    <url-pattern>/*</url-pattern>
  </filter-mapping>

  <servlet>
    <servlet-name>DispatcherServlet</servlet-name>
    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    <init-param>
      <param-name>contextConfigLocation</param-name>
      <param-value>classpath*:spring-mvc.xml</param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
  </servlet>
  <servlet-mapping>
    <servlet-name>DispatcherServlet</servlet-name>
    <url-pattern>*.do</url-pattern>
  </servlet-mapping>
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>

</web-app>

index.jsp :

<html>
<body>
<h2>congratulations and welcome to springmvcmybatis</h2>
</body>
</html>

四.设置tomcat

上面Configure设置tomcat的文件夹路径,例如D:\Software\tomcat85,下面Application context 改为/
然后运行tomcat,在网页进行显示,输入条件查询
http://localhost:8080/user/login.do?acc=LlYlll&pass=123456
http://localhost:8080/user/fb.do?user_id=1
http://localhost:8080/user/1/fbi.do

---

总结

利用springMVCMybatis进行接口暴露,进行前端页面展示大数据结果呈现。