缓存相关:
- 一级缓存:sqlSession级别的(默认开启)
- 二级缓存:nameSpace级别的(默认关闭)
${}
和#{}
的区别:
${}
:预编译的形式,会保留sql中的?,可以防止sql注入。
#{}
:取出旳值直接放在sql语句中,会有安全的问题。
大多数情况下使用#{}
,但是因为原生的jdbc不支持占位符的地方我们就可以使用${}
,比如按年份分表的情况:
select * from ${year}_salary where xxx;
目录结构:
1.添加maven依赖(4.3.12.RELEASE):
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>18.0</version>
</dependency>
<!-- spring****************************************start -->
<!-- Spring ioc模块 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.12.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>4.3.12.RELEASE</version>
</dependency>
<!-- Spring 数据库模块 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.12.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>4.3.12.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.3.12.RELEASE</version>
</dependency>
<!-- Spring web模块 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>4.3.12.RELEASE</version>
</dependency>
<!-- Spring AOP模块 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>4.3.12.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>4.3.12.RELEASE</version>
</dependency>
<!-- Spring 基础依赖模块 -->
<dependency>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
<version>1.1.3</version>
</dependency>
<!-- spring*******************************************end -->
<!-- mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.4</version>
</dependency>
<!-- mybatis-spring整合包 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.0</version>
</dependency>
<!-- 数据库连接包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.36</version>
</dependency>
<!--google-->
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>21.0</version>
</dependency>
<!--druid-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.17</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.6</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.40</version>
</dependency>
代码:
Controller:
/**
* Created by wb-tianlihui on 2017/11/7.
*/
@Controller
@RequestMapping("/")
public class TestController {
@Autowired
private BookService service;
@RequestMapping("/test")
@ResponseBody
public String test(@RequestParam(value = "bookId") long id){
Book book = service.getBookById(id);
return JSON.toJSONString(book);
}
}
Service:
/**
* Created by wb-tianlihui on 2017/11/7.
*/
public interface BookService {
public Book getBookById(Long id);
}
/**
* Created by wb-tianlihui on 2017/11/7.
*/
@Service
public class BookServiceImpl implements BookService {
@Autowired
private BookDao dao;
@Override
public Book getBookById(Long id) {
return dao.queryById(id);
}
}
Dao:
/**
* Created by wb-tianlihui on 2017/11/7.
*/
@Repository
public interface BookDao {
/**
* 通过ID查询单本图书
*
* @param id
* @return
*/
Book queryById(long id);
}
Entity:
/**
* Created by wb-tianlihui on 2017/11/7.
*/
@Data
public class Book {
// 图书ID
private long bookId;
// 图书名称
private String name;
// 馆藏数量
private int number;
}
配置:
web.xml:
<web-app metadata-complete="true" version="3.1" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemalocation="http://xmlns.jcp.org/xml/ns/javaee
http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd">
<!-- 如果是用mvn命令生成的xml,需要修改servlet版本为3.1 -->
<!-- 配置DispatcherServlet -->
<servlet>
<servlet-name>spring</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<!-- 配置springMVC需要加载的配置文件
spring-dao.xml,1spring-service.xml,spring-servlet.xml
Mybatis - > spring -> springmvc
-->
</servlet>
<servlet-mapping>
<servlet-name>spring</servlet-name>
<!-- 默认匹配所有的请求 -->
<url-pattern>/</url-pattern>
</servlet-mapping>
<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-servlet.xml:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:mvc="http://www.springframework.org/schema/mvc" 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.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc.xsd">
<!-- 配置SpringMVC -->
<!-- 1.开启SpringMVC注解模式 -->
<!-- 简化配置:
(1)自动注册DefaultAnootationHandlerMapping,AnotationMethodHandlerAdapter
(2)提供一些列:数据绑定,数字和日期的format @NumberFormat, @DateTimeFormat, xml,json默认读写支持
-->
<mvc:annotation-driven/>
<!-- 2.静态资源默认servlet配置
(1)加入对静态资源的处理:js,gif,png
(2)允许使用"/"做整体映射
-->
<mvc:default-servlet-handler/>
<!-- 3.配置jsp 显示ViewResolver -->
<mvc:view-resolvers>
<mvc:jsp prefix="/WEB-INF/jsp/" suffix=".jsp"/>
</mvc:view-resolvers>
<!-- 4.扫描web相关的bean -->
<context:component-scan base-package="com.tianlh.web"/>
<context:component-scan base-package="com.tianlh.service"/>
<!-- 5.配置数据库相关参数properties的属性:${url} -->
<context:property-placeholder location="classpath:jdbc.properties"/>
<import resource="classpath*:spring/spring-dao.xml"/>
</beans>
spring-dao.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
<bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">
<property name="driverClassName" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<!-- 初始化连接大小 -->
<property name="initialSize" value="0"/>
<!-- 连接池最大使用连接数量 -->
<property name="maxActive" value="20"/>
<!-- 连接池最大空闲 -->
<property name="maxIdle" value="20"/>
<!-- 连接池最小空闲 -->
<property name="minIdle" value="0"/>
<!-- 获取连接最大等待时间 -->
<property name="maxWait" value="60000"/>
<property name="validationQuery" value="${validationQuery}"/>
<property name="testOnBorrow" value="false"/>
<property name="testOnReturn" value="false"/>
<property name="testWhileIdle" value="true"/>
<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="60000"/>
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="25200000"/>
<!-- 打开removeAbandoned功能 -->
<property name="removeAbandoned" value="true"/>
<!-- 1800秒,也就是30分钟 -->
<property name="removeAbandonedTimeout" value="1800"/>
<!-- 关闭abanded连接时输出错误日志 -->
<property name="logAbandoned" value="true"/>
<!-- 监控数据库 sql注入-->
<property name="filters" value="wall" />
<!--<property name="filters" value="mergeStat"/>-->
</bean>
<!-- 3.配置SqlSessionFactory对象 -->
<bean class="org.mybatis.spring.SqlSessionFactoryBean" id="sqlSessionFactory">
<!-- 注入数据库连接池 -->
<property name="dataSource" ref="dataSource"></property>
<!-- 配置MyBaties全局配置文件:mybatis-config.xml -->
<!-- <property name="configLocation" value="classpath:mybatis-config.xml"></property>-->
<!-- 扫描entity包 使用别名 -->
<property name="typeAliasesPackage" value="com.tianlh.entity"></property>
<!-- 扫描sql配置文件:mapper需要的xml文件 -->
<property name="mapperLocations" value="classpath:mapper/*.xml"></property>
</bean>
<!-- 4.配置扫描Dao接口包,动态实现Dao接口,注入到spring容器中 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!-- 注入sqlSessionFactory -->
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
<!-- 给出需要扫描Dao接口包 -->
<property name="basePackage" value="com.tianlh.dao"></property>
</bean>
</beans>
BookDao.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.tianlh.dao.BookDao">
<!-- 接口式编程时:
1.namespace需要指定成接口的全类名
2.接口方名称需要和mapper的id一致
-->
<resultMap id="BaseResultMap" type="com.tianlh.entity.Book">
<id column="book_id" property="bookId" jdbcType="INTEGER"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
<result column="number" property="number" jdbcType="INTEGER"/>
</resultMap>
<!-- 目的:为dao接口方法提供sql语句配置 -->
<select id="queryById" parameterType="long" resultMap="BaseResultMap">
<!-- 具体的sql -->
SELECT
book_id,
name,
number
FROM
book
WHERE
book_id = #{bookId}
</select>
</mapper>
jdbc.properties:
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mysql?useUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=root
validationQuery=SELECT 1
Sql:
CREATE TABLE `book` (
`book_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '图书编号',
`name` varchar(100) NOT NULL COMMENT '图书名称',
`number` int(11) NOT NULL COMMENT '数量',
PRIMARY KEY (`book_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 COMMENT='图书'
INSERT INTO `book` (`book_id`, `name`, `number`)
VALUES
(1000, 'Java', 1),
(1001, 'JSP', 1),
(1002, 'Zookper', 1),
(1003, 'Redis', 1)