springmvc + springJdbc 实现一个简单的增删改查数据的Demo

前言

由于刚做 java 后台的时候， 项目使用的是 springboot ，这个相较于 springMVC 等框架自带 tomcat，配置更少更方便的框架， 所有对于 java 基础一些的 ssm 或者 ssh 框架知之甚少， 现在大多数公司的项目结构也都是 ssh 或是 ssm 开发，加上 springboot 虽然简单，但也就意味着自己能做的操作更少， 有这个其局限性， 所以就有了这篇随笔；

记录这篇随笔的目的在于：

　　　　　　1、记录基于 maven 的 javaweb 工程的流程；

　　　　　　2、简单整合 springjdbc 实现数据库的增删改查操作；

下面是正文

搭建 基于 maven 的 javaweb 工程, 基本流程如下：

首先新建一个 javaweb 工程，选择对应的 类型 按照要求填写好资料，最后就能得到一个结构如下的 javaweb 目录；

可以看到，新建的文件最外层有一个 pox.xml 文件， 这个文件主要作用是对项目需要的依赖进行管理，要使用 springmvc 和 springJdbc 就需要添加对应的 依赖， 如下：

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>...</groupId>
    <artifactId>...</artifactId>
    <packaging>war</packaging>
    <version>1.0-SNAPSHOT</version>
    <name>...</name>
    <url>http://maven.apache.org</url>

    <build>
        <finalName>...</finalName>
    </build>


    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>4.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>4.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>4.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>4.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>4.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>4.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.0.5</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>4.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-expression</artifactId>
            <version>4.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.0.2.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.codehaus.jackson</groupId>
            <artifactId>jackson-mapper-asl</artifactId>
            <version>1.9.11</version>
        </dependency>
        <dependency>
            <groupId>org.codehaus.jackson</groupId>
            <artifactId>jackson-core-asl</artifactId>
            <version>1.9.11</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
        </dependency>
    </dependencies>

</project>

pox 文件配置好了之后，接下来就是 web-inf 目录下的 web.xml 文件了

web.xml 文件可以指定 欢迎页面 、配置 servlet 以及配置一些 tomcat 启动时的能够读取的参数等；
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="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_3_0.xsd"
        version="3.0">
    <display-name>Archetype Created Web Application</display-name>

    <welcome-file-list>
        <welcome-file>/index.jsp</welcome-file>
    </welcome-file-list>

    <servlet>
        <servlet-name>springServlet</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <load-on-startup>1</load-on-startup>
    </servlet>

    <servlet-mapping>
        <servlet-name>springServlet</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>

　　<!-- tomcat 启动时需要读取 context-param 中配置的东东 -->
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>classpath:applicationContext.xml</param-value>
    </context-param>
</web-app>


所以，很明显的，接下来就要探索 applicationContext.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: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-4.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-4.0.xsd">
　　<!-- 使用注解需要 -->
    <context:annotation-config/>
　　<!-- 扫描目录的结构，会扫描此目录下设置了注解的类，交给 spring 容器管理 -->
    <context:component-scan base-package="com.study.hugy"/>
　　<!-- 加载了 jdbc.properties 文件 -->
    <context:property-placeholder location="classpath:jdbc.properties"/>
    <bean id="jdbcConfig" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location">
            <value>classpath:jdbc.properties</value>
        </property>
    </bean>

　　<!-- 配置了数据库连接的一些参数 -->
    <bean id="MyDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

　　<!-- jdbcTemplate 一种比原版 jdbc 稍微方便一点的数据库操作类 -->
    <bean id="jdbcTemplate" name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="MyDataSource"/>
    </bean>

　　<!-- 可以无视 在类上使用注解同样可以将类交给容器管理 -->
    <bean id="userDao" class="controller.dao.UserDAO">
    </bean>

</beans>

jdbc.properties 文件就是以键值对记录的一些数据库连接参数

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=XXX
jdbc.username=XXX
jdbc.password=XXX

好了，配置文件基本就讲到这里，然后进入代码的编写
在java文件下依次建立如下目录

   首先，编写一个 controller 类 ， 测试 javaweb 工程是否能够正常运行；

@RestController
@RequestMapping(value = "/test")
public class TestController {

    @RequestMapping(value = "/sayHello",produces = "text/html;charset=UTF-8")
    public String testSay() {
        return "hello springMVC + 你好";
    }
}

然后，配置一下 tomcat (这个必须要自己配，相较于 springboot 比较那啥)

第一步 配置 project structure 

 

完成之后 ， 正式进入 tomcat 配置

如上图，然后在 Deployment 中将 project structure 中配置的 artiFact 添加到 tomcat 中就 ok 了；

点击运行， 等启动之后 在浏览器之中输入 http://localhost:8080/test/sayHello 就可以访问到 上面写的 controller了；

 

配置部分基本就到这了，下面是 jdbc 部分， 基本就贴代码了

@RestController
@RequestMapping(value = "/crud")
public class CRUDController {

    @Autowired
    private UserDAO userDao;
    @RequestMapping(value = "/select", produces = "application/json;charset=UTF-8")
    public RspBean insertSomething() {
        Map<String, List> map = new HashMap<String, List>();
        List<SurveyBean> list = userDao.getSurveyQuest();
        map.put("result", list);
        return RspBean.build(200, "请求成功", map);
    }

    @RequestMapping(value = "/insert", method = RequestMethod.POST)
    public RspBean insertSomething (@RequestBody Map bean) {
        SurveyBean surveyBean = new SurveyBean();
        surveyBean.setSurveyId(bean.get("surveyId").toString());
        surveyBean.setSceneId(bean.get("sceneId").toString());
        surveyBean.setQuestId(bean.get("requestId").toString());
        surveyBean.setType(bean.get("type").toString());
        surveyBean.setRequired(bean.get("required").toString());
        surveyBean.setName(bean.get("name").toString());
        surveyBean.setSeq(bean.get("seq").toString());
        surveyBean.setCreateTime(bean.get("createTime").toString());
        surveyBean.setVersion(bean.get("version").toString());
        Map<String, Integer> result = new HashMap<String, Integer>();
        result.put("result", userDao.insertSurveyQuest(surveyBean));
        return RspBean.build(result);
    }

    @RequestMapping(value = "/update",method = RequestMethod.POST)
    public RspBean updateSomething (@RequestBody Map bean ) {
        SurveyBean surveyBean = new SurveyBean();
        surveyBean.setSurveyId(bean.get("surveyId").toString());
        surveyBean.setSceneId(bean.get("sceneId").toString());
        surveyBean.setQuestId(bean.get("requestId").toString());
        surveyBean.setType(bean.get("type").toString());
        surveyBean.setRequired(bean.get("required").toString());
        surveyBean.setName(bean.get("name").toString());
        surveyBean.setSeq(bean.get("seq").toString());
        surveyBean.setCreateTime(bean.get("createTime").toString());
        surveyBean.setVersion(bean.get("version").toString());
        Integer result = userDao.updateOneSurveyData(surveyBean);
        Map<String, Integer> map = new HashMap<String, Integer>();
        map.put("result", result);
        return RspBean.build(map);
    }

    @RequestMapping(value = "delete/{surveyId}", method = RequestMethod.GET)
    public RspBean deleteSomething (@PathVariable String surveyId) {
        Map<String ,Integer> map = new HashMap<String, Integer>();
        map.put("result", userDao.deleteOneSurveyData(surveyId));
        return  RspBean.build(map);
    }
}

@Component
public class UserDAO {

    public JdbcTemplate jdbcTemplate;
    ClassPathXmlApplicationContext cpx = null;

    public List<SurveyBean> getSurveyQuest() {
        initClassPathXmlApplication();
        List<SurveyBean> mResult = new ArrayList<SurveyBean>();
        String sql = "SELECT quest_id 'questId'," +
                "scene_id 'sceneId', survey_id 'surveyId'," +
                "quest_name 'name', version 'version'," +
                "quest_seq 'seq', quest_type 'type'," +
                "required 'required', create_time 'createTime'" +
                "FROM abcd.survey_quest";
        mResult = jdbcTemplate.query(sql, new RowMapper<SurveyBean>() {
            public SurveyBean mapRow(ResultSet resultSet, int i) throws SQLException {
                SurveyBean bean = new SurveyBean();
                bean.setQuestId(resultSet.getString("questId"));
                bean.setSceneId(resultSet.getString("sceneId"));
                bean.setSurveyId(resultSet.getString("surveyId"));
                bean.setName(resultSet.getString("name"));
                bean.setVersion(resultSet.getString("version"));
                bean.setSeq(resultSet.getString("seq"));
                bean.setType(resultSet.getString("type"));
                bean.setRequired(resultSet.getString("required"));
                bean.setCreateTime(resultSet.getString("createTime"));
                return bean;
            }
        });
        return mResult;
    }

    public Integer insertSurveyQuest(SurveyBean bean) {
        initClassPathXmlApplication();
        String sql = "INSERT INTO abcd.survey_quest (quest_id, scene_id, survey_id, quest_name, version," +
                "quest_seq, quest_type, required, create_time) VALUES" +
                "('" + bean.getQuestId() + "','" + bean.getSceneId() + "','" + bean.getSurveyId() + "','" +
                bean.getName() + "','" + bean.getVersion() + "','" + bean.getSeq() + "','" + bean.getType() +
                "','" + bean.getRequired() + "','" + bean.getCreateTime() + "')";
        int result = jdbcTemplate.update(sql);
        return result;
    }

    public Integer updateOneSurveyData(SurveyBean surveyBean) {
        initClassPathXmlApplication();
        String sql = "UPDATE abcd.survey_quest SET survey_id = '" + surveyBean.getSurveyId() + "',scene_id = '" +
                surveyBean.getSceneId() + "', quest_name = '" + surveyBean.getName() + "',version = '" +
                surveyBean.getVersion() + "', quest_seq = '" + surveyBean.getSeq() + "', quest_type = '" +
                surveyBean.getType() + "', required = '" + surveyBean.getRequired() + "', create_time = '" +
                surveyBean.getCreateTime() + "' WHERE quest_id = '" + surveyBean.getQuestId() + "'";
        int result = jdbcTemplate.update(sql);
        return result;
    }

    public void initClassPathXmlApplication () {
        if (cpx == null) {
            cpx = new ClassPathXmlApplicationContext("applicationContext.xml");
            jdbcTemplate = (JdbcTemplate) cpx.getBean("jdbcTemplate");
        }
    }

    public Integer deleteOneSurveyData(String surveyId) {
        initClassPathXmlApplication();
        String sql = "DELETE FROM abcd.survey_quest WHERE survey_id = '" + surveyId + "'";
        return jdbcTemplate.update(sql);
    }
}

public class RspBean {
    private Integer responseCode = 200;
    private String message = "";
    private Map<String, Object> response = null;
    private RspBean () {}
    public static RspBean build(Integer code, String message, Map data) {
        RspBean rspBean = new RspBean();
        rspBean.responseCode = code;
        rspBean.message = message;
        rspBean.response = data;
        return rspBean;
    }

    public static RspBean build(Map data) {
        RspBean rspBean = new RspBean();
        rspBean.response = data;
        return rspBean;
    }

    @Override
    public String toString() {
        return "RspBean{" +
                "responseCode=" + responseCode +
                ", message='" + message + '\'' +
                ", response=" + response +
                '}';
    }

    public Integer getResponseCode() {
        return responseCode;
    }

    public void setResponseCode(Integer responseCode) {
        this.responseCode = responseCode;
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public Map<String, Object> getResponse() {
        return response;
    }

    public void setResponse(Map<String, Object> response) {
        this.response = response;
    }
}

public class SurveyBean {
    public String questId;
    public String sceneId;
    public String surveyId;
    public String name;
    public String version;
    public String seq;
    public String type;
    public String required;
    public String createTime;

    @Override
    public String toString() {
        return "SurveyBean{" +
                "questId='" + questId + '\'' +
                ", sceneId='" + sceneId + '\'' +
                ", surveyId='" + surveyId + '\'' +
                ", name='" + name + '\'' +
                ", version='" + version + '\'' +
                ", seq='" + seq + '\'' +
                ", type='" + type + '\'' +
                ", required='" + required + '\'' +
                ", createTime='" + createTime + '\'' +
                '}';
    }

    public void setQuestId(String questId) {
        this.questId = questId;
    }

    public void setSceneId(String sceneId) {
        this.sceneId = sceneId;
    }

    public void setSurveyId(String surveyId) {
        this.surveyId = surveyId;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setVersion(String version) {
        this.version = version;
    }

    public void setSeq(String seq) {
        this.seq = seq;
    }

    public void setType(String type) {
        this.type = type;
    }

    public void setRequired(String required) {
        this.required = required;
    }

    public void setCreateTime(String createTime) {
        this.createTime = createTime;
    }

    public String getQuestId() {

        return questId;
    }

    public String getSceneId() {
        return sceneId;
    }

    public String getSurveyId() {
        return surveyId;
    }

    public String getName() {
        return name;
    }

    public String getVersion() {
        return version;
    }

    public String getSeq() {
        return seq;
    }

    public String getType() {
        return type;
    }

    public String getRequired() {
        return required;
    }

    public String getCreateTime() {
        return createTime;
    }
}