一.数据库的安装及创建

我们首先需要去下载安装mysql,并且配置相关环境变量，当这些准备好后，打开windows系统，

输入cmd指令进行登录：mysql -u root -p回车输入设置的密码：

创建数据库：

create database ssmProduct;

 

使用数据库：

use ssmProduct;

 

创建product数据库表：

create table product(
   id varchar(64) not null unique,
   name varchar(1024) not null,
   description varchar(1024) not null,
   primary key(id)
   )engine=innodb default charset=utf8;

 

展示数据库表：

show tables;

ssis 连接 sql server ssms怎么连接到数据库_mybatis

 

二.maven项目的创建

1.配置下pom.xml的相关依赖：

ssis 连接 sql server ssms怎么连接到数据库_mybatis_02

<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>com.ssm</groupId>
  <artifactId>SpringMVC-SSM</artifactId>
  <packaging>war</packaging>
  <version>0.0.1-SNAPSHOT</version>
  <name>SpringMVC-SSM Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    <dependency>
    	<groupId>org.springframework</groupId>
    	<artifactId>spring-webmvc</artifactId>
    	<version>5.2.3.RELEASE</version>
    </dependency>
    <dependency>
    	<groupId>org.mybatis</groupId>
    	<artifactId>mybatis</artifactId>
    	<version>3.4.2</version>
    </dependency>
    <dependency>
    	<groupId>org.mybatis</groupId>
    	<artifactId>mybatis-spring</artifactId>
    	<version>1.3.1</version>
    </dependency>
    <dependency>
    	<groupId>mysql</groupId>
    	<artifactId>mysql-connector-java</artifactId>
    	<version>8.0.12</version>
    	<scope>runtime</scope>
    </dependency>
    <dependency>
    	<groupId>commons-dbcp</groupId>
    	<artifactId>commons-dbcp</artifactId>
    	<version>1.4</version>
    </dependency>
    <dependency>
    	<groupId>org.springframework</groupId>
    	<artifactId>spring-jdbc</artifactId>
    	<version>4.0.2.RELEASE</version>
    </dependency>
    <dependency>
    	<groupId>jstl</groupId>
    	<artifactId>jstl</artifactId>
    	<version>1.2</version>
    </dependency>

  </dependencies>
  <build>
    <finalName>SpringMVC-SSM</finalName>
     <plugins>
    	<plugin>
    		<groupId>org.apache.maven.plugins</groupId>
    		<artifactId>maven-compiler-plugin</artifactId>
    		<version>3.1</version>
    		<configuration>
    			<source>1.8</source>
    			<target>1.8</target>
    		</configuration>
    	</plugin>
    </plugins>
  </build>
</project>

2.配置jdbc数据库驱动

jdbc.properties: 解析

driver =

url = jdbc:mysql://localhost:3306

/ssmProduct

useUnicode=true&characterEncoding=utf-8

&serverTimezone=GMT%2B8

&useSSL=false

username=root

password=123456

initialSize=0

maxActive=20 表示 连接池的最大使用数量
maxIdle=20 表示 连接池的最大空闲数量
minIdle=1 表示连接池的最小空闲数量
maxWait=60000

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/ssmProduct?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false
username=root
password=1314521888nv
#connect size initialization
initialSize=0
#max quantity of connection pool
maxActive=20
#max free of connection pool
maxIdle=20
minIdle=1
maxWait=60000

 

3. mybatis配置

spring-mybatis.xml 解析：

org.springframework.beans.factory.config.PropertyPlaceholderConfigurer 表示：使用占位符配置方式连接数据库

value="classpath:jdbc.properties"  表示使用本地 读取加载jdbc.properties 文件进行连接数据库

 class="org.apache.commons.dbcp.BasicDataSource"  表示通过BasicDataSource 类来获取dataSource  

<property name="driverClassName" value="${driver}"/> 表示读取jdbc.properties中的内容，并设置到dataSource 对象里

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> 表示将mapper/*.xml配置文件（保存了映射文件和sql语句），读取sql语句后，写入到已经建立连接的数据库中。

 <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> 表示扫描dao包里的数据库实体类对象，并和mapper/*.xml配置文件（保存了映射文件和sql语句） 中的文件，同名类名进行一一映射。

<?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"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	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">
	<!-- 引入数据库配置文件 -->
	<bean id="propertyConfigure" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="location" value="classpath:jdbc.properties"></property>
	</bean>
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="${driver}"/>
		<property name="url" value="${url}"/>
		<property name="username" value="${username}"/>
		<property name="password" value="${password}"/>
		<property name="initialSize" value="${initialSize}"/>
		<property name="maxActive" value="${maxActive}"/>
		<property name="maxIdle" value="${maxIdle}"/>
		<property name="minIdle" value="${minIdle}"/>
		<property name="maxWait" value="${maxWait}"/>
	</bean>
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource"/>
		<property name="mapperLocations" value="classpath:mapper/*.xml"/>
	</bean>
	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<property name="basePackage" value="com.ssm.dao"/>
		<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
	</bean>
</beans>

 

4.配置springmvc-servlet.xml

<mvc:default-servlet-handler/> 能自动加载静态资源static里的一些前端资源

<?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"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	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:component-scan base-package="com.ssm"/>
	
	<!-- 配置视图解析器 -->
	<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="prefix" value="/WEB-INF/pages/"></property>
		<property name="suffix" value=".jsp"></property>
	</bean>
	
	<!-- 静态资源处理 -->
	<mvc:annotation-driven/>
	<mvc:default-servlet-handler/>
</beans>

 

5. 把配置好的mybatis和springmvc 加入到web.xml中，进行初始化配置加载。

<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> 表示可以使用@resources注解

<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>Archetype Created Web Application</display-name>
  <!-- The front controller of this Spring Web application, responsible for handling all application requests -->
	<servlet>
		<servlet-name>springmvc</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>classpath:springmvc-servlet.xml</param-value>
		</init-param>
		<load-on-startup>1</load-on-startup>
	</servlet>

	<!-- Map all requests to the DispatcherServlet for handling -->
	<servlet-mapping>
		<servlet-name>springmvc</servlet-name>
		<url-pattern>/</url-pattern>
	</servlet-mapping>
	
	<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>
	</filter>
	<filter-mapping>
		<filter-name>characterEncodingFilter</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>
	
	<!-- 配置监听器 -->
	<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>

</web-app>

6.需要一个product.java实体类

package com.ssm.entities;

public class Product {
	private String id;
	private String name;
	private String description;
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	
}

7.需要一个操作数据库的持久类：ProductDao.java

package com.ssm.dao;

import java.util.List;

import com.ssm.entities.Product;

public interface ProductDao {
	public int insertProduct(Product product);
	public List<Product> findAllProduct();
	public Product findProductByID(String id);
	public int updateProduct(Product product);
	public int removeProduct(String id);
}

 

8. 逻辑层：ProductService.java

package com.ssm.services;

import java.util.List;

import com.ssm.entities.Product;

public interface ProductService {
	public int insertProduct(Product product);
	public List<Product> findAllProduct();
	public Product findProductByID(String id);
	public int updateProduct(Product product);
	public int removeProduct(String id);
}

9.逻辑层的实现类：ProductServiceImpl.java

package com.ssm.services.impl;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.ssm.dao.ProductDao;
import com.ssm.entities.Product;
import com.ssm.services.ProductService;

@Service("productService")
public class ProductServiceImpl implements ProductService {

	@Resource
	private ProductDao productDao;
	
	@Override
	public int insertProduct(Product product) {
		// TODO Auto-generated method stub
		return productDao.insertProduct(product);
	}

	@Override
	public List<Product> findAllProduct() {
		// TODO Auto-generated method stub
		return productDao.findAllProduct();
	}

	@Override
	public Product findProductByID(String id) {
		// TODO Auto-generated method stub
		return productDao.findProductByID(id);
	}

	@Override
	public int updateProduct(Product product) {
		// TODO Auto-generated method stub
		return productDao.updateProduct(product);
	}

	@Override
	public int removeProduct(String id) {
		// TODO Auto-generated method stub
		return productDao.removeProduct(id);
	}

}

10. 请求处理 控制层： ProductController.java

package com.ssm.controller;

import javax.annotation.Resource;
import javax.websocket.server.PathParam;

import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.RequestMapping;

import com.ssm.entities.Product;
import com.ssm.form.ProductForm;
import com.ssm.services.ProductService;

@Controller
@RequestMapping("p")
public class ProductController {
	
	@Resource
	private ProductService productService;
	
	@RequestMapping("show")
	public String showPage(Model model) {
		ProductForm productForm = new ProductForm();
		model.addAttribute("productForm", productForm);
		return "show";
	}
	
	@RequestMapping("addProduct")
	public String addProductSubmit(ProductForm productForm, BindingResult bindingResult, Model model) {
		Product product = new Product();
		BeanUtils.copyProperties(productForm, product);
		int ret = productService.insertProduct(product);
		return "redirect:/p/list";
	}
	
	@RequestMapping("list")
	public String showProductsPage(Model model) {
		model.addAttribute("products", productService.findAllProduct());
		return "list";
	}
	
	@RequestMapping("detail")
	public String showProductDetailPage(@PathParam("id") String id, Model model) {
		model.addAttribute("product", productService.findProductByID(id));
		return "detail";
	}
	
	@RequestMapping("update")
	public String showUpdatePage(@PathParam("id") String id, Model model) {
		ProductForm productForm = new ProductForm();
		BeanUtils.copyProperties(productService.findProductByID(id), productForm);
		model.addAttribute("productForm", productForm);
		return "update";
	}
	
	@RequestMapping("updateSubmit")
	public String submitUpdate(ProductForm productForm, Model model) {
		Product product = new Product();
		BeanUtils.copyProperties(productForm, product);
		productService.updateProduct(product);
		return "redirect:/p/list";
	}
	
	@RequestMapping("remove")
	public String removeProduct(@PathParam("id") String id, Model model) {
		productService.removeProduct(id);
		return "redirect:/p/list";
	}
}

11. 表单类： 其实和实体类一样，就是我们提交的是表单，用来承接读取上来，或者提交的表单数据。

ProductForm.java

package com.ssm.form;

public class ProductForm {
	private String id;
	private String name;
	private String description;
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
}

12.  数据库展示页面：

show.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert Product Page</title>
</head>
<body>
	<h2>Add New Product</h2>
	<form:form method="post" action="addProduct" modelAttribute="productForm">
		<table>
			<tr>
				<td><form:label path="id">ID</form:label></td>
				<td><form:input path="id"/></td>
			</tr>
			<tr>
				<td><form:label path="name">Name</form:label></td>
				<td><form:input path="name"/></td>
			</tr>
			<tr>
				<td><form:label path="description">Description</form:label></td>
				<td><form:textarea path="description"/></td>
			</tr>
			<tr>
				<td colspan="2">
					<form:button>Submit</form:button>
				</td>
			</tr>
		</table>
	</form:form>
</body>
</html>

列表页面:list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Product List</title>
</head>
<body>
	<h2>Product List</h2>
	<table>
		<tr>
			<td>ID</td>
			<td>Name</td>
			<td>Description</td>
			<td></td>
			<td></td>
		</tr>
		<c:forEach items="${products }" var="product">
			<tr>
				<td><a href="detail?id=${product.id }">${product.id }</a></td>
				<td>${product.name }</td>
				<td>${product.description }</td>
				<td><a href="update?id=${product.id }">Edit</a></td>
				<td><a href="remove?id=${product.id }">Remove</a></td>
			</tr>
		</c:forEach>
	</table>
</body>
</html>

 

详情界面：detail.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Product Detial</title>
</head>
<body>
	${product.id }<br>
	${product.name }<br>
	${product.description }<br>
</body>
</html>

 

更新界面：update.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Update Product Detail</title>
</head>
<body>
	<h2>Update Product Detail</h2>
	<form:form method="post" action="updateSubmit" modelAttribute="productForm">
		<table>
			<tr>
				<td><form:label path="id">ID</form:label></td>
				<td><form:hidden path="id"/><form:input path="id" disabled="true"/></td>
			</tr>
			<tr>
				<td><form:label path="name">Name</form:label></td>
				<td><form:input path="name"/></td>
			</tr>
			<tr>
				<td><form:label path="description">Description</form:label></td>
				<td><form:textarea path="description"/></td>
			</tr>
			<tr>
				<td colspan="2">
					<form:button>Submit</form:button>
				</td>
			</tr>
		</table>
	</form:form>
</body>
</html>

成功页面：success.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
Success
</body>
</html>

 

 

三.实现CURD功能：

因为我们已经使用了mybatis框架技术，可以直接使用：ProductDaoMapper.xml配置进行数据库和实体类进行映射。

就相当于省略了我们的映射类：列如：student类必须和数据库表的字节进行一一对应。

package com.itany.sms.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.itany.sms.entity.Clazz;
import com.itany.sms.entity.Student;
import com.itany.sms.util.RowMapper;

public class StudentMapper implements RowMapper<Student> {

	@Override
	public Student mapRow(ResultSet rs) throws SQLException {
		Student stu = new Student();
		stu.setId(rs.getInt("id"));
		stu.setName(rs.getString("name"));
		stu.setAge(rs.getInt("age"));
		stu.setHeight(rs.getDouble("height"));
		stu.setBithday(rs.getDate("birthday")); // getDate()返回的是java.sql.Date
		Clazz clazz = new Clazz();
		clazz.setId(rs.getInt("cid"));
		clazz.setName(rs.getString("cname"));
		stu.setClazz(clazz);
		return stu;
	}

}

使用mybatis框架技术 ，就直接省了这步。

ProductDaoMapper.xml：

<resultMap type="com.ssm.entities.Product" id="ProductResultMap"> 表示  数据库 字段 和 实体类 进行字段映射

<insert id="insertProduct" parameterType="com.ssm.entities.Product"> 表示 insertProduct 和 ProductDao 接口中的方法相对应。

<?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.ssm.dao.ProductDao">
	<resultMap type="com.ssm.entities.Product" id="ProductResultMap">
		<result column="id" property="id" jdbcType="VARCHAR"/>
		<result column="name" property="name" jdbcType="VARCHAR"/>
		<result column="description" property="description" jdbcType="VARCHAR"/>
	</resultMap>
	<insert id="insertProduct" parameterType="com.ssm.entities.Product">
		insert into product(id, name, description) values(#{id}, #{name}, #{description})
	</insert>
	<select id="findAllProduct" resultMap="ProductResultMap">
		select * from product;
	</select>
	<select id="findProductByID" resultMap="ProductResultMap" parameterType="String">
		select * from product where id = #{id}
	</select>
	<update id="updateProduct" parameterType="com.ssm.entities.Product">
		update product set name=#{name}, description=#{description} where id=#{id}
	</update>
	<delete id="removeProduct" parameterType="String">
		delete from product where id=#{id}
	</delete>
</mapper>

四.测试效果：

输入添加功能的界面 ： http://localhost:8080/SpringMVC-SSM/p/show

ssis 连接 sql server ssms怎么连接到数据库_springmvc_03

 

1.测试添加功能  :  insert功能：

ssis 连接 sql server ssms怎么连接到数据库_mybatis_04

在上述中点击提交后，会进行读取数据库，并且跳转到列表界面，进行数据展示。

ssis 连接 sql server ssms怎么连接到数据库_spring_05

数据库中也真实存在数据：

ssis 连接 sql server ssms怎么连接到数据库_mybatis_06

 

2. 测试删除功能： remove

点击remove 后进行删除数据

ssis 连接 sql server ssms怎么连接到数据库_mybatis_07

ssis 连接 sql server ssms怎么连接到数据库_mybatis_08

 

3. 编辑数据 

点击edit 后进行删除数据 

ssis 连接 sql server ssms怎么连接到数据库_数据库_09

点击提交后再次展示：

ssis 连接 sql server ssms怎么连接到数据库_spring_10