使用Spring SpringMVC Mysql Jdbctemplate 完成对数据库的CRUD

项目结构

环境配置

导包

jar包 相关jar包 提取码 bkia

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.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd">

	<!-- 开启注解扫描 -->
	<context:component-scan base-package="cn.dustyzhou.dao,cn.dustyzhou.service"></context:component-scan>
	<!-- 连接连接池 -->
	<context:property-placeholder location="classpath:db.properties" />
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" >
		<property name="username" value="${jdbc.username}"></property>
		<property name="password" value="${jdbc.password}"></property>
		<property name="driverClassName" value="${jdbc.driverClassName}"></property>
		<property name="url" value="${jdbc.url}"></property>
	</bean>
	<!-- 配置jdbctemplate -->
	<bean id="jdbcTemplate"  class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
</beans>

SpringMVC核心配置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:mvc="http://www.springframework.org/schema/mvc"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.1.xsd
		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.1.xsd">
	
	<!-- 扫描注解 -->
	<context:component-scan base-package="cn.dustyzhou.controller"></context:component-scan>

	<!-- 视图解析器 -->
	<bean id="views" class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="prefix" value="/WEB-INF/views/"></property>
		<property name="suffix" value=".jsp"></property>	
	</bean>
	
	<!--静态资源放行 -->
	<mvc:default-servlet-handler/>
	
	<!-- 注解放行 -->
	<mvc:annotation-driven/>

</beans>

web.xml配置

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
  <display-name>CRUD</display-name>
  
  	  <!-- 加载spring容器 -->
	  <context-param>
	  		<param-name>contextConfigLocation</param-name>
	  		<param-value>classpath:applicationContext.xml</param-value>
	  </context-param>
	  
	 <!-- 利用监听器 监听spring 加载spring核心配置文件--> 
		<listener>
			<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
		</listener>
	
	<!-- 加载springMVC核心配置文 件 前端控制器 -->
	<servlet>
		<servlet-name>springDispatcherServlet</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>classpath:applicationContext-mvc.xml</param-value>
		</init-param>
		<load-on-startup>1</load-on-startup>
	</servlet>
	
	<servlet-mapping>
		<servlet-name>springDispatcherServlet</servlet-name>
		<url-pattern>/</url-pattern>
	</servlet-mapping>
	
	<!-- 解决中文乱码问题 使用filter -->
	<filter>
		<filter-name>characterEncodingFilter</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>characterEncodingFilter</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>	
</web-app>

Dao层

package cn.dustyzhou.dao;

import java.util.List;

import cn.dustyzhou.domain.User;

public interface IUserDao {
	/**
	 *  添加用户
	 * @param user
	 */
	public void insert(User user);
	
	/**
	 * 删除用户
	 * @param id
	 */
	public void delete(Integer id);
	
	
	/**
	 * 修改用户
	 * @param user
	 */
	public void update(User user);
	
	/**
	 * 查询单个用户
	 * @param id
	 * @return
	 */
	public User selectOne(Integer id);
	
	/**
	 * 查询全部用户
	 * @return
	 */
	public List<User> selectAll();

}

impl实现类

package cn.dustyzhou.dao.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import cn.dustyzhou.dao.IUserDao;
import cn.dustyzhou.domain.User;

@Repository 
public class UserDaoImpl implements IUserDao {
	
	@Autowired //注入
	private JdbcTemplate jt;

	@Override
	public void insert(User user) {
		//使用jdbctemplate中的update方法
		jt.update("insert into user(username,password,age) values(?,?,?)",user.getUsername(),user.getPassword(),user.getAge());
	}

	@Override
	public void delete(Integer id) {
		//使用jdbctemplate中的update方法
		jt.update("delete from user where id=?",id);
	}

	@Override
	public void update(User user) {
		//使用jdbctemplate中的update方法
		jt.update("update user set username=?,password=?,age=? where id=?",user.getUsername(),user.getPassword(),user.getAge(),user.getId());
	}

	@Override
	public User selectOne(Integer id) {
		//使用jdbctemplate中的queryForObject方法
		User user = jt.queryForObject("select * from user where id = ?",new BeanPropertyRowMapper<User>(User.class),id);
		return user;
	}

	@Override
	public List<User> selectAll() {
		//使用jdbctemplate中的query方法
		List<User> list = jt.query("select * from user ",new BeanPropertyRowMapper<User>(User.class));
		return list;
	}

}

service层

package cn.dustyzhou.service;

import java.util.List;

import cn.dustyzhou.domain.User;

public interface IUserService {
	/**
	 *  添加用户
	 * @param user
	 */
	public void insert(User user);
	
	/**
	 * 删除用户
	 * @param id
	 */
	public void delete(Integer id);
	
	
	/**
	 * 修改用户
	 * @param user
	 */
	public void update(User user);
	
	/**
	 * 查询单个用户
	 * @param id
	 * @return
	 */
	public User selectOne(Integer id);
	
	/**
	 * 查询全部用户
	 * @return
	 */
	public List<User> selectAll();
	

}

impl实现类

package cn.dustyzhou.service.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.dustyzhou.dao.IUserDao;
import cn.dustyzhou.domain.User;
import cn.dustyzhou.service.IUserService;

@Service
public class UserServiceImpl  implements IUserService{
	
	@Autowired
	private IUserDao dao;

	@Override
	public void insert(User user) {
		dao.insert(user);
		
	}

	@Override
	public void delete(Integer id) {
		dao.delete(id);
	
	}

	@Override
	public void update(User user) {
		dao.update(user);	
	}

	@Override
	public User selectOne(Integer id) {
		
		return dao.selectOne(id);
	}

	@Override
	public List<User> selectAll() {
		// TODO Auto-generated method stub
		return dao.selectAll();
	}

}

Controller层

package cn.dustyzhou.controller;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import cn.dustyzhou.domain.User;
import cn.dustyzhou.service.IUserService;

@Controller
public class UserController {
	
	@Autowired //注入
	private IUserService service;
	
	/**
	 * 查询所有用户
	 * @param map
	 * @return
	 */
	@RequestMapping("/show")
	public String show(Map<String,Object> map) {
		List<User> list = service.selectAll();
		map.put("list", list);		
		return "index";
	}
	/**
	 * 删除用户
	 * @param id
	 * @return
	 */
	@RequestMapping("/delete")
	public String delete(Integer id) {
		service.delete(id);
		//重定向方式
		return "redirect:/show ";
	}
	
	/**
	 * 起一个中间站，跳转页面使用
	 * @param id
	 * @param map
	 * @return
	 */
	@RequestMapping("/goAdd")
	public String goAdd(Integer id,Map<String,Object> map) {
		
		if(id!=null) {
			User user = service.selectOne(id);
			map.put("user", user);
		}
		//请求转发
		return "save";
	}
	
	
	/**
	 * 增加和修改用户
	 * @param user
	 * @return
	 */
	@RequestMapping("/saveUser")
	public String saveUser(User user) {
		
		if(user.getId()!=null) {
			service.update(user);
		}else {
			service.insert(user);
		}
		//重定向
		return "redirect:/show ";
	}
}

jsp页面

index.jsp页面

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
		<h2 align="center" > <a href="/goAdd">添加</a></h2>
		<table align="center" border="1px" width="500px" height="300px" >
			<tr>
				<th>编号</th>
				<th>用户名</th>
				<th>年龄</th>
				<th>操作</th>
			</tr>
			<c:forEach items="${list }" var="user">
				<tr align="center">
					<td>${user.id }</td>
					<td>${user.username }</td>
					<td>${user.age }</td>
					<td>
						<a href="/delete?id=${user.id }">删除</a> |
						<a href="/goAdd?id=${user.id }">修改</a>
					</td>
				</tr>
			</c:forEach>	
		</table>
</body>
</html>

save.jsp页面

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core"  prefix="c"%>    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>	
	<form action="/saveUser" method="post">
		<input type="hidden" name="id" value="${user.id }">
		用户名：<input type="text" name="username" value="${user.username }"><br/>
		密码：<input type="text" name="password" value="${user.password }"><br/>
		年龄：<input type="text" name="age" value="${user.age }"><br/>
		
		<c:if test="${empty user }">
			<input type="submit" value="添加">
		</c:if>
		
		<c:if test="${!empty user }">
			<input type="submit" value="修改">
		</c:if>	
	</form>
</body>
</html>

前端效果

因只是简单完成功能，展示页面有点略丑

总结

前两天学习了Spring和SpringMVC 和JdbcTemplate 然后整合了下知识，简单写了一个数据库的CRUD ，最重要的是环境的搭建与配置，环境配置好，代码跟着逻辑一步一步来就基本没什么问题。在此记录下自己成长的一点一滴 ，还有很多很多不懂的地方，前行的路还长。