建数据库和表结构
create database testdb go create table [User]( id int identity(1,1), name nvarchar(30), sex char(1), age int, address nvarchar(100) ) go
创建maven工程
POM.xml中导入坐标并建立依赖
<dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>5.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>5.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>5.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.aspectj</groupId> <artifactId>aspectjweaver</artifactId> <version>1.9.4</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-web</artifactId> <version>5.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aop</artifactId> <version>5.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aspects</artifactId> <version>4.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>5.2.0.RELEASE</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.2</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis-spring</artifactId> <version>1.3.0</version> </dependency> <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> <version>7.2.2.jre8</version> </dependency> <dependency> <groupId>javax.servlet</groupId> <artifactId>servlet-api</artifactId> <version>2.5</version> </dependency> <dependency> <groupId>javax.servlet.jsp</groupId> <artifactId>jsp-api</artifactId> <version>2.2</version> </dependency> <dependency> <groupId>jstl</groupId> <artifactId>jstl</artifactId> <version>1.2</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.7.0</version> </dependency> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> </dependencies>
编写实体类User.java
public class User { private int id; private String name; private char sex; private int age; private String address; @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", sex=" + sex + ", age=" + age + ", address='" + address + '\'' + '}'; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public char getSex() { return sex; } public void setSex(char sex) { this.sex = sex; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } }
编写UserMapper接口UserMapper.java
public interface UserMapper { void saveUser(User user) throws SQLException; void updateUser(User user) throws SQLException; void deleteUserById(int id) throws SQLException; User queryUserById(int id) throws SQLException; List<User> queryUserList() throws SQLException; }
编写UserMapper配置文件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="com.company.mapper.UserMapper"> <select id="queryUserById" parameterType="int" resultType="com.company.pojo.User"> select * from [User] where id=#{id} </select> <select id="queryUserList" resultType="com.company.pojo.User"> select * from [USER] </select> <insert id="saveUser" parameterType="com.company.pojo.User"> insert into [User](name,sex,age,address) values(#{name},#{sex},#{age},#{address}) </insert> <update id="updateUser" parameterType="com.company.pojo.User"> update [User] set age=#{age},address=#{address} where id=#{id} </update> <delete id="deleteUserById" parameterType="int"> delete from [User] where id=#{id} </delete> </mapper>
编写MyBatis配置文件SqlMapConfig.xml以及db.properties
jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver jdbc.url=jdbc:sqlserver://localhost:1433;DatabaseName=testdb jdbc.username=sa jdbc.password=123.abc
<?xml version="1.0" encoding="utf-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <properties resource="db.properties"/> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"></transactionManager> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driver}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </dataSource> </environment> </environments> <mappers> <mapper resource="com/company/mapper/UserMapper.xml"/> </mappers> </configuration>
测试持久层
public class testMybatis { @Test public void test() throws IOException, SQLException { InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream); inputStream.close(); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper userMapper = sqlSession.getMapper(UserMapper.class); List<User> userList = userMapper.queryUserList(); userList.forEach(System.out::println); sqlSession.close(); } }
编写业务层接口UserService.java及接口实现类UserServiceImpl.java
public interface UserService { void saveUser(User user) throws SQLException; void updateUser(User user) throws SQLException; void deleteUserById(int id) throws SQLException; User queryUserById(int id) throws SQLException; List<User> queryUserList() throws SQLException; }
@Service("userService")
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public void saveUser(User user) throws SQLException {
userMapper.saveUser(user);
}
@Override
public void updateUser(User user) throws SQLException {
userMapper.updateUser(user);
}
@Override
public void deleteUserById(int id) throws SQLException {
userMapper.deleteUserById(id);
}
@Override
public User queryUserById(int id) throws SQLException {
return userMapper.queryUserById(id);
}
@Override
public List<User> queryUserList() throws SQLException {
return userMapper.queryUserList();
}
}
MyBatis整合Spring
整合目标：
- 数据库连接池以及事务管理都交给Spring容器来完成。
- SqlSessionFactory对象应该放到Spring容器中单例对象管理。
- Mapper动态代理对象交给Spring管理，从Spring容器中直接获得Mapper的代理对象。
整合思路：把MyBatis配置文件（SqlMapConfig.xml）中内容配置到spring配置文件中。同时把MyBatis配置文件的内容清掉。
<?xml version="1.0" encoding="utf-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> </configuration>
<?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" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/aop https://www.springframework.org/schema/aop/spring-aop.xsd"> <!--加载配置文件--> <context:property-placeholder location="classpath:db.properties"/> <context:component-scan base-package="com.company"/> <!--配置数据源--> <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"> <property name="driverClassName" value="${jdbc.driver}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </bean> <!--配置MyBatis的Session工厂--> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <!--数据库连接池--> <property name="dataSource" ref="dataSource"/> <!--加载MyBatis的全局配置文件--> <property name="configLocation" value="classpath:SqlMapConfig.xml"/> </bean> <!--配置Mapper扫描器--> <bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/> <property name="basePackage" value="com.company.mapper"/> </bean> <!--配置事务管理器--> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource"/> </bean> <!--配置事务的通知--> <tx:advice id="txAdvice" transaction-manager="transactionManager"> <tx:attributes> <tx:method name="*" propagation="REQUIRED" read-only="false"/> <tx:method name="query*" propagation="SUPPORTS" read-only="true"/> </tx:attributes> </tx:advice> <!--配置aop--> <aop:config> <!--配置切入点表达式--> <aop:pointcut id="pt" expression="execution(* com.company.service.UserServiceImpl.*.*(..))"/> <!--建立通知和切入点表达式的关系--> <aop:advisor advice-ref="txAdvice" pointcut-ref="pt"/> </aop:config> </beans>
但是需要在web.xml中设置，否则会报错
<context-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:SqlMapConfig.xml</param-value> </context-param>
测试业务层
@RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = "classpath:applicationContext.xml") public class testSpring { @Autowired public UserService userService; @Test public void test() throws SQLException { User user = userService.queryUserById(21); System.out.println(user); } }
整合SpringMVC和Spring
配置web.xml
<?xml version="1.0" encoding="UTF-8"?> <web-app 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_2_5.xsd" version="2.5"> <context-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:SqlMapConfig.xml</param-value> </context-param> <!--配置spring mvc的核心控制器--> <servlet> <servlet-name>dispatcherServlet</servlet-name> <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> <!--配置初始化参数，用于读取springmvc的配置参数--> <init-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:applicationContext.xml</param-value> </init-param> <!--配置servlet的对象的创建时间点：应用加载时创建。取值只能是非0正整数，表示启动顺序--> <load-on-startup>1</load-on-startup> </servlet> <servlet-mapping> <servlet-name>dispatcherServlet</servlet-name> <url-pattern>/</url-pattern> </servlet-mapping> <!--配置springMVC编码过滤器--> <filter> <filter-name>encodingFilter</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>encodingFilter</filter-name> <url-pattern>/*</url-pattern> </filter-mapping> </web-app>
在spring配置文件applicationContext.xml添加以下设置
<!--配置创建spring容器需要扫描的包--> <context:component-scan base-package="com.company"> <!--指定扫包规则，只扫描使用@Controller注解的java类--> <context:include-filter type="annotation" expression="org.springframework.stereotype.Controller"/> </context:component-scan> <!--配置视图解析器--> <bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver"> <property name="prefix" value="/WEB-INF/page/"></property> <property name="suffix" value=".jsp"></property> </bean> <mvc:annotation-driven/>
编写控制器
@Controller("userController")
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@RequestMapping("/queryUserList")
public String queryUserList(Model model) throws SQLException {
List<User> userList = userService.queryUserList();
model.addAttribute("userList",userList);
return "result";
}
@RequestMapping("/saveUser")
public String saveUser(User user) throws SQLException{
System.out.println(user);
userService.saveUser(user);
return "redirect:queryUserList";
}
}
编写前端页面index.jsp和result.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %> <html> <head> <title>Title</title> </head> <body> <a href="${pageContext.request.contextPath}/user/queryUserList">查询所有用户信息</a> <fieldset> <h1>新增用户</h1> <form method="post" action="${pageContext.request.contextPath}/user/saveUser"> 姓名：<input type="text" name="name"/><br> 性别：<input type="text" name="sex"/><br> 年龄：<input type="text" name="age"/><br> 地址：<input type="text" name="address"/><br> <input type="submit" value="保存"/> </form> </fieldset> </body> </html>
<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <html> <head> <title>Title</title> </head> <body> <h1>查询结果</h1> <table border="1"> <tr> <td>序号</td> <td>姓名</td> <td>性别</td> <td>年龄</td> <td>地址</td> </tr> <c:forEach items="${userList}" varStatus="status" var="item"> <tr> <td>${status.count}</td> <td>${item.name}</td> <td>${item.sex}</td> <td>${item.age}</td> <td>${item.address}</td> </tr> </c:forEach> </table> </body> </html>
测试SSM整合，运行项目
项目目录格式
posted on
浙公网安备 33010602011771号