分享一个朋友的人工智能教程。比较通俗易懂,风趣幽默,感兴趣的朋友可以去看看。
一:简介
Java程序很大一部分要操作数据库,为了提高性能操作数据库的时候,有不得不使用数据库连接池。数据库连接池有很多选择,c3p0、dhcp、proxool等,druid作为一名后起之秀,凭借其出色的性能,也逐渐印入了大家的眼帘。企业中接触到的项目几乎都是在使用druid作为数据源
Druid 提供了很多配置参数:
配置 | 缺省值 | 说明 |
name | 配置这个属性的意义在于,如果存在多个数据源, 监控的时候可以通过名字来区分开来。 如果没有配置,将会生成一个名字, 格式是:"DataSource-" + System.identityHashCode(this) |
mysql : jdbc:mysql://10.20.153.104:3306/druid2
oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto username 连接数据库的用户名 password 连接数据库的密码。如果你不希望密码直接写在配置文件中,
可以使用ConfigFilter。详细看这里:https://github.com/alibaba/druid/wiki/%E4%BD%BF%E7%94%A8ConfigFilter driverClassName 根据url自动识别 这一项可配可不配,如果不配置druid会根据url自动识别dbType,
然后选择相应的driverClassName(建议配置下) initialSize 0 初始化时建立物理连接的个数。
初始化发生在显示调用init方法,或者第一次getConnection时 maxActive 8 最大连接池数量 maxIdle 8 已经不再使用,配置了也没效果 minIdle 最小连接池数量 maxWait 获取连接时最大等待时间,单位毫秒。
配置了maxWait之后,缺省启用公平锁,并发效率会有所下降,
如果需要可以通过配置useUnfairLock属性为true使用非公平锁。 poolPreparedStatements false 是否缓存preparedStatement,也就是PSCache。
PSCache对支持游标的数据库性能提升巨大,
比如说oracle。在mysql下建议关闭。 maxOpenPreparedStatements -1 要启用PSCache,必须配置大于0,当大于0时,
poolPreparedStatements自动触发修改为true。
在Druid中,不会存在Oracle下PSCache占用内存过多的问题,
可以把这个数值配置大一些,比如说100 validationQuery 用来检测连接是否有效的sql,要求是一个查询语句。
如果validationQuery为null,testOnBorrow、testOnReturn、
testWhileIdle都不会其作用。 testOnBorrow true 申请连接时执行validationQuery检测连接是否有效,
做了这个配置会降低性能。 testOnReturn false 归还连接时执行validationQuery检测连接是否有效,
做了这个配置会降低性能 testWhileIdle false 建议配置为true,不影响性能,并且保证安全性。申请连接的时候
检测,如果空闲时间大于timeBetweenEvictionRunsMillis,
执行validationQuery检测连接是否有效。 timeBetweenEvictionRunsMillis 有两个含义:
1) Destroy线程会检测连接的间隔时间2)
testWhileIdle的判断依据,详细看testWhileIdle属性的说明 numTestsPerEvictionRun 不再使用,一个DruidDataSource只支持一个EvictionRun minEvictableIdleTimeMillis connectionInitSqls 物理连接初始化的时候执行的sql exceptionSorter 根据dbType自动识别 当数据库抛出一些不可恢复的异常时,抛弃连接 filters 属性类型是字符串,通过别名的方式配置扩展插件,
常用的插件有:
监控统计用的filter:stat日志用的filter:log4j
防御sql注入的filter:wall proxyFilters
类型是List<com.alibaba.druid.filter.Filter>,
如果同时配置了filters和proxyFilters,是组合关系,并非替换关系
二:MyBatis与Druid 整合
本示例只讲Spring与MyBatis、Druid的整合,尽可能的不整合其他框架(如log4j2、hibernate-validator等),减少整合的复杂度
1、在pom.xml中引入必须的相关依赖(test、springframework、数据源等)
Spring集成MyBatis不需要spring-web,引入该依赖是为了测试Druid的监控功能(是一个辅助功能,不影响druid与mybatis的整合)
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<jdk.version>1.7</jdk.version>
<junit.version>4.12</junit.version>
<spring.version>4.3.8.RELEASE</spring.version>
<mysql.version>5.1.40</mysql.version>
<druid.version>1.0.29</druid.version>
<mybatis.version>3.2.6</mybatis.version>
<servlet.version>3.0.1</servlet.version>
</properties>
<dependencies>
<!-- test begin -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>${junit.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
<scope>test</scope>
</dependency>
<!-- test end -->
<!-- spring base begin -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- spring base end -->
<!-- database begin -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
<!-- database end -->
<!-- web begin -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>${servlet.version}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jsp-api</artifactId>
<version>2.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!-- web end -->
</dependencies>
<build>
<finalName>Spring-Mybatis-Druid</finalName>
<!-- 防止不拷贝mybatis文件 -->
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>${jdk.version}</source>
<target>${jdk.version}</target>
<encoding>${project.build.sourceEncoding}</encoding>
<showDeprecation>true</showDeprecation>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-resources-plugin</artifactId>
<configuration>
<encoding>${project.build.sourceEncoding}</encoding>
</configuration>
</plugin>
</plugins>
</build>
2、在src/main/resources/conf/settings配置数据源application.properties
druid.driverClassName=com.mysql.jdbc.Driver
druid.url=jdbc:mysql://127.0.0.1:3306/manager?useUnicode=true&characterEncoding=UTF-8
druid.username=root
druid.password=root123
druid.initialSize=10
druid.minIdle=6
druid.maxActive=50
druid.maxWait=60000
druid.timeBetweenEvictionRunsMillis=60000
druid.minEvictableIdleTimeMillis=300000
druid.validationQuery=SELECT 'x'
druid.testWhileIdle=true
druid.testOnBorrow=false
druid.testOnReturn=false
druid.poolPreparedStatements=false
druid.maxPoolPreparedStatementPerConnectionSize=20
druid.filters=wall,stat
3、在src/main/resources/conf/下配置spring-db.xml
spring-db.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:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:jpa="http://www.springframework.org/schema/data/jpa"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc-4.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.2.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-4.2.xsd
http://www.springframework.org/schema/data/jpa
http://www.springframework.org/schema/data/jpa/spring-jpa.xsd">
<context:component-scan base-package="com.mengdee.manager.dao.**.impl" />
<context:property-placeholder location="classpath:conf/settings/*.properties" />
<!-- 数据源 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">
<!-- 数据库基本信息配置 -->
<property name="url" value="${druid.url}" />
<property name="username" value="${druid.username}" />
<property name="password" value="${druid.password}" />
<property name = "driverClassName" value = "${druid.driverClassName}" />
<!-- 初始化连接数量 -->
<property name="initialSize" value="${druid.initialSize}" />
<!-- 最小空闲连接数 -->
<property name="minIdle" value="${druid.minIdle}" />
<!-- 最大并发连接数 -->
<property name="maxActive" value="${druid.maxActive}" />
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="${druid.maxWait}" />
<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="${druid.timeBetweenEvictionRunsMillis}" />
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="${druid.minEvictableIdleTimeMillis}" />
<property name="validationQuery" value="${druid.validationQuery}" />
<property name="testWhileIdle" value="${druid.testWhileIdle}" />
<property name="testOnBorrow" value="${druid.testOnBorrow}" />
<property name="testOnReturn" value="${druid.testOnReturn}" />
<!-- 打开PSCache,并且指定每个连接上PSCache的大小 如果用Oracle,则把poolPreparedStatements配置为true,mysql可以配置为false。 -->
<property name="poolPreparedStatements" value="${druid.poolPreparedStatements}" />
<property name="maxPoolPreparedStatementPerConnectionSize"
value="${druid.maxPoolPreparedStatementPerConnectionSize}" />
<!-- 配置监控统计拦截的filters -->
<property name="filters" value="${druid.filters}" />
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" value="classpath:/mapper/mybatis-config.xml"/>
<property name="mapperLocations" value="classpath:/mapper/*Mapper.xml" />
<property name="typeAliasesPackage" value="com.mengdee.manager.domain" />
</bean>
<!-- 配置mybatis mapper接口 -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.mengdee.manager.dao.**.mapper" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
</bean>
<!-- 事务配置 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<!-- 使用annotation注解方式配置事务 -->
<tx:annotation-driven transaction-manager="transactionManager" />
</beans>
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>
<settings>
<setting name="cacheEnabled" value="false"/>
<setting name="lazyLoadingEnabled" value="false"/>
<setting name="aggressiveLazyLoading" value="true"/>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
</configuration>
MyBatis打印执行的SQL,只需要在mybatis-config.xml中配置一下即可
value的值可以是:
- SLF4J
- LOG4J
- LOG4J2:一般设置这个
- STDOUT_LOGGING : 标准输出,打印在控制台上
- NO_LOGGING
- JDK_LOGGING
- COMMONS_LOGGING
settings的更多参数可以参考官网文档:http://www.mybatis.org/mybatis-3/zh/configuration.html#settings
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.mengdee.manager.dao.user.mapper.UserMapper">
<select id="getUserList" resultType="User">
SELECT id, `name`, age FROM sys_user
</select>
<select id="getUserList2" resultType="User">
SELECT id, `name`, age FROM sys_user
</select>
</mapper>
4、测试
manager数据库sys_user表
/*
Navicat MySQL Data Transfer
Source Server : mengday
Source Server Version : 50535
Source Host : localhost:3306
Source Database : manager
Target Server Type : MYSQL
Target Server Version : 50535
File Encoding : 65001
Date: 2017-05-31 15:05:46
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `sys_user`
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
`id` bigint(20) NOT NULL COMMENT '用户ID',
`name` varchar(50) DEFAULT NULL COMMENT '用户名',
`age` int(3) DEFAULT NULL COMMENT '用户年龄',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系统用户表';
-- ----------------------------
-- Records of sys_user
-- ----------------------------
INSERT INTO `sys_user` VALUES ('784972358981328902', 'Tom', '24');
INSERT INTO `sys_user` VALUES ('784972358981328903', 'Jammy', '21');
SpringTestCase
package com.mengdee.manager.user;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractJUnit4SpringContextTests;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
@ContextConfiguration(locations = {"classpath:conf/spring/spring-*.xml"})
@RunWith(SpringJUnit4ClassRunner.class)
public class SpringTestCase extends AbstractJUnit4SpringContextTests{
}
UserServiceTest
package com.mengdee.manager.user;
import java.util.List;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import com.mengdee.manager.dao.user.UserDao;
import com.mengdee.manager.domain.User;
public class UserServiceTest extends SpringTestCase{
@Autowired
private UserDao userDao;
@Test
public void testGetUserList(){
List<User> userList = userDao.getUserList();
for (User user : userList) {
System.out.println(user);
}
}
}
UserDao
package com.mengdee.manager.dao.user;
import java.util.List;
import com.mengdee.manager.domain.User;
public interface UserDao {
public List<User> getUserList();
}
UserDaoImpl
package com.mengdee.manager.dao.user.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.mengdee.manager.dao.user.UserDao;
import com.mengdee.manager.dao.user.mapper.UserMapper;
import com.mengdee.manager.domain.User;
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
private UserMapper userMapper;
@Override
public List<User> getUserList() {
return userMapper.getUserList();
}
}
UserMapper
package com.mengdee.manager.dao.user.mapper;
import java.util.List;
import com.mengdee.manager.domain.User;
public interface UserMapper {
public List<User> getUserList();
}
此时Spring + MyBatis + Druid 已经集成完成,可以通过Junit4来测试是否成功
注意:
1、这里讲XxxMapper.xml配置文件放到了src/main/resources
2、集成MyBatis时使用Mapper接口方式,不需要类型别名,不需要Dao层实现类
MyBatis即可以使用Mapper接口方式集成,也可以通过继承SqlSessionDaoSupport使用DAO层的实现层来实现,如getSqlSession().selectList(UserMapper.class.getName() + “.getUserList2”);
定义一个BaseDAO,集成SqlSessionDaoSupport,为了解决Property ‘sqlSessionFactory’ or ‘sqlSessionTemplate’ are required 问题
package com.mengdee.manager.dao.user;
import javax.annotation.Resource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.support.SqlSessionDaoSupport;
public class BaseDAO extends SqlSessionDaoSupport{
@Resource
public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory){
super.setSqlSessionFactory(sqlSessionFactory);
}
}
UserDaoImpl
package com.mengdee.manager.dao.user.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.mengdee.manager.dao.user.BaseDAO;
import com.mengdee.manager.dao.user.UserDao;
import com.mengdee.manager.dao.user.mapper.UserMapper;
import com.mengdee.manager.domain.User;
@Repository
public class UserDaoImpl extends BaseDAO implements UserDao {
public List<User> getUserList2() {
return getSqlSession().selectList(UserMapper.class.getName() + ".getUserList2");
}
}
UserServiceTest
package com.mengdee.manager.user;
import java.util.List;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import com.mengdee.manager.dao.user.UserDao;
import com.mengdee.manager.domain.User;
public class UserServiceTest extends SpringTestCase {
@Autowired
private UserDao userDao;
@Test
public void testGetUserList2(){
List<User> userList = userDao.getUserList2();
for (User user : userList) {
System.out.println(user);
}
}
}
三:Druid 提供了StatViewServlet,用于查看对数据库的监控,为了测试监控数据需要通过web访问数据库,所以接下来需要集成SpringMVC
1、配置web.xml
<!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>
<display-name>Archetype Created Web Application</display-name>
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:conf/spring/spring-*.xml</param-value>
</context-param>
<listener>
<description>Spring监听器</description>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<servlet>
<servlet-name>spring-mvc</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:conf/spring/spring-mvc.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>spring-mvc</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>DruidStatView</servlet-name>
<servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>DruidStatView</servlet-name>
<url-pattern>/druid/*</url-pattern>
</servlet-mapping>
<filter>
<filter-name>druidWebStatFilter</filter-name>
<filter-class>com.alibaba.druid.support.http.WebStatFilter</filter-class>
<init-param>
<param-name>exclusions</param-name>
<param-value>/public/*,*.js,*.css,/druid*,*.jsp,*.swf</param-value>
</init-param>
<init-param>
<param-name>principalSessionName</param-name>
<param-value>sessionInfo</param-value>
</init-param>
<init-param>
<param-name>profileEnable</param-name>
<param-value>true</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>druidWebStatFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
</web-app>
2、在src/main/resources/conf/spring/srping-mvc.xml中配置SpringMVC
<?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">
<!-- Controller包(自动注入) -->
<context:component-scan base-package="com.mengdee.manager.controller,com.mengdee.manager.service"/>
<!-- 对模型视图名称的解析,即在模型视图名称添加前后缀 -->
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/WEB-INF/views/"/>
<property name="suffix" value=".jsp"/>
</bean>
</beans>
3、定义UserController
package com.mengdee.manager.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.mengdee.manager.domain.User;
import com.mengdee.manager.service.UserService;
@Controller
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@RequestMapping("/list")
public String getUserList(){
List<User> userList = userService.getUserList();
for (User user : userList) {
System.out.println(user);
}
return "index";
}
}
UserServiceImpl
package com.mengdee.manager.service.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.mengdee.manager.dao.user.UserDao;
import com.mengdee.manager.domain.User;
import com.mengdee.manager.service.UserService;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao;
@Override
public List<User> getUserList() {
return userDao.getUserList();
}
}
在浏览器上访问druid: http://localhost:8081/Spring-Mybatis-Druid/druid/index.html
项目目录结构如图:
四:使用Druid的ConfigFilter对数据库密码加密
对于大部分程序员来说,数据库的信息,如用户名,密码等信息一般都写到配置文件中,便于修改和维护,然而这对于运维安全来说确实一个很大的挑战,如果黑客进入到你的系统里面去,那这些数据库用户名和密码就一目了然,这个是很不安全的。Druid为此提供一种数据库密码加密的手段ConfigFilter,使用他加密数据库密码,即使别人拿到了数据库连接密码,破解这个密码也得稍稍花点时间了,也对咱们的网站安全性提高了一些。
1、对数据库密码加密得到公钥publicKey和加密后的密码password
1、先切换到druid-1.0.29.jar 所在位置,这里是切换到maven 仓库中的指定版本的jar(cd G:\Java\maven\repo\com\alibaba\druid\1.0.29)
2、G:\Java\maven\repo\com\alibaba\druid\1.0.29> java -cp druid-1.0.29.jar com.alibaba.druid.filter.confi
g.ConfigTools root123
其中root123为数据库的密码,使用该命令生成三个键值对 私钥privateKey 公钥publicKey 密码password,其中公钥publicKey 密码password是需要配置在数据源中的
privateKey:MIIBVQIBADANBgkqhkiG9w0BAQEFAASCAT8wggE7AgEAAkEA67ZKXgB7upA5iOozBfHfSeh5rwxg3PM33RsZs/ira
uhLclPLo2ZXeeXL9CnLuzyBpkyEyu/ffqmRlB3D5ky3TwIDAQABAkEAj+/dMdHxvFdeCvGY/sRYqfVAeViBver37hkidPghFUJg0
Gw2uRGNaUoNjLUvLAZJKK+5J9F0acl23pMugQLAoQIhAPxy9aaFD02CtYfKz5o1S9G70l5ePGE/sSl6DUOrqL8DAiEA7wcQHAbf8
LFdTqJ29EkIhuFwcNXL8/pBtbUiqTFlPsUCIBu2GtUOoiFmCbtAPg21b/8z2OHXAlXCghYFedNF0p41AiEA3twG+Rqv0QqNpLN1l
W5CtP+chpfQATpj6bdpCbc0XsECIHEYHbJtf2hintQ8v5YUyLYbKK8ZvuBzNsbGee8AGKEy
publicKey:MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAOu2Sl4Ae7qQOYjqMwXx30noea8MYNzzN90bGbP4q2roS3JTy6NmV3nly/
Qpy7s8gaZMhMrv336pkZQdw+ZMt08CAwEAAQ==
password:tL5sPSoby0bkbwdjiAPjvjj9sD9WYqsS8HgCIR6QVYekZWz+gDzD4hDjWYj9HDPCfwhWtWR9gOspxTmDuKRO3w==
2、在application.properties中配置数据源中的filters,publicKey,password
数据源中filters的值指定config,用于解密,如果filters有多个值用逗号分隔
druid.filters=wall,stat,config
druid.publicKey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAOu2Sl4Ae7qQOYjqMwXx30noea8MYNzzN90bGbP4q2roS3JTy6NmV3nly/Qpy7s8gaZMhMrv336pkZQdw+ZMt08CAwEAAQ==
druid.password=tL5sPSoby0bkbwdjiAPjvjj9sD9WYqsS8HgCIR6QVYekZWz+gDzD4hDjWYj9HDPCfwhWtWR9gOspxTmDuKRO3w==
3、在spring-db.xml中配置数据源时指定password、filters、connectionProperties
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">
<property name="password" value="${druid.password}" />
<property name="filters" value="${druid.filters}" />
<property name="connectionProperties" value="config.decrypt=true;config.decrypt.key=${druid.publicKey}" />
</bean>
以上三步就完成了所有的配置, 顺利的话使用前面的测试方法操作数据库如果能查询出数据证明配置正常
参考地址:https://github.com/alibaba/druid/wiki/使用ConfigFilter
五:MyBatis自动生成Dao层接口(如UserMapper.java)、实体(如UserModel)、SQL文件(如UserMapper.xml)
在开发中写实体和mapper.xml映射文件中的resultMap算是一项比较费时无趣的机械工作,为了节省时间,在实际开发中通常会自动生成这些内容
1、在pom.xml中配置mybatis代码生成插件mybatis-generator-maven-plugin
该插件中需要配置mybaits在生成代码时使用到的一些参数(比如数据库连接信息,都生成哪些表,每个表对应的实体名称是什么灯)的配置文件的位置configurationFile
<build>
<finalName>Spring-Mybatis-Druid</finalName>
<plugins>
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.2</version>
<configuration>
<configurationFile>src/main/resources/conf/mybatis/mybatis-generator-config.xml</configurationFile>
<verbose>true</verbose>
<overwrite>true</overwrite>
</configuration>
<executions>
<execution>
<id>Generate MyBatis Artifacts</id>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.2</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
2、在src/main/resources/conf/mybatis/ 下配置 mybatis-generator-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd" >
<generatorConfiguration>
<!-- 引入配置文件 -->
<properties resource="./conf/settings/application.properties"/>
<!-- 指定数据连接驱动jar地址 -->
<classPathEntry location="${generator.jdbc.classPath}" />
<!-- 一个数据库一个context -->
<context id="myContext" targetRuntime="MyBatis3">
<!-- 注释 -->
<commentGenerator >
<property name="suppressAllComments" value="true"/><!-- 是否取消注释 -->
<property name="suppressDate" value="false" /> <!-- 是否生成注释代时间戳-->
</commentGenerator>
<!-- jdbc连接 -->
<jdbcConnection driverClass="${druid.driverClassName}"
connectionURL="${druid.url}" userId="${druid.username}"
password="${generator.jdbc.password}" />
<!-- 类型转换 -->
<javaTypeResolver>
<!-- 是否使用bigDecimal, false可自动转化以下类型(Long, Integer, Short, etc.) -->
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!-- 生成实体类地址 -->
<javaModelGenerator targetPackage="com.mengdee.manager.domain"
targetProject="src/main/java" >
<property name="enableSubPackages" value="false"/>
<!-- 是否针对string类型的字段在set的时候进行trim调用 -->
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!-- 生成XxxMapper.xml文件 -->
<sqlMapGenerator targetPackage="mapper" targetProject="src/main/resources" >
<property name="enableSubPackages" value="false" />
</sqlMapGenerator>
<!-- 生成接口 XxxMapper.java -->
<javaClientGenerator targetPackage="com.mengdee.manager.dao.user.mapper" targetProject="src/main/java" type="XMLMAPPER" >
<property name="enableSubPackages" value="false" />
</javaClientGenerator>
<!-- 配置表信息 -->
<table tableName="sys_user"
domainObjectName="UserEntity" enableCountByExample="false"
enableDeleteByExample="false" enableSelectByExample="false"
enableUpdateByExample="false">
</table>
</context>
</generatorConfiguration>
关于配置文件的配置请参考官网文档:http://mybatis.org/generator/configreference/xmlconfig.html
- javaModelGenerator: 用于指定生成的实体放在哪个包下面
- sqlMapGenerator:用于指定XxxMapper.xml文件放置的位置
- javaClientGenerator:用于指定生成的DAO层接口存放的位置(如:UserMapper.java)
- table: 用于配置要生成那张表,生成的对应的实体类的名称是什么,如果要生成多张表就要配置多个table,如果每个表对应的生成的实体和Mapper接口的存放位置都不一样,可以修改一下targetPackage,生成一个表,然后再注释掉生成的table,然后再生成另一张表,一个一个的生成或者一个子包分批生成
注意:
在生成时,如果已经生成过了,没有注释掉相应的table配置,还会重新生成,此时会覆盖原先的,如果原先已经写了其它额外的代码,这时候就完了,所以生成时一定要小心,生成后一定要注释掉生成的table,经测试XxxMapper.xml不会重新生成而是在原来的基础上进行追加
3. 在application.properties中配置键值对
generator.jdbc.classPath=G:/Java/maven/repo/mysql/mysql-connector-java/5.1.40/mysql-connector-java-5.1.40.jar
generator.jdbc.password=root123
4. 生成
方式一:
首先cmd,将目录切换到.pom文件所在目录
然后执行mvn mybatis-generator:generate
G:\manageworkspace\Spring-Mybatis-Druid>mvn mybatis-generator:generate
执行之后刷新一下项目即可
方式二:
也可以在eclipse中右键项目–>Run As -->Maven Build…–> Goals: mybatis-generator:generate --> Run 方式生成
mybatis代码生成参考文章:http://www.cnblogs.com/yjmyzz/p/4210554.html
六:Mybatis分页插件PageHelper
github地址:https://github.com/pagehelper/Mybatis-PageHelper
1、在pom.xml中引入分页插件依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.0.1</version>
</dependency>
2、在SqlSessionFactoryBean中配置分页插件PageHelper
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" value="classpath:/conf/mybatis/mybatis-config.xml"/>
<property name="mapperLocations" value="classpath:/mapper/*Mapper.xml" />
<property name="typeAliasesPackage" value="com.mengdee.manager.domain" />
<property name="plugins">
<array>
<bean class="com.github.pagehelper.PageInterceptor">
<property name="properties">
<value>
helperDialect=mysql
reasonable=true
supportMethodsArguments=true
params=count=countSql
autoRuntimeDialect=true
</value>
</property>
</bean>
</array>
</property>
</bean>
reasonable:分页合理化参数,默认值为false。当该参数设置为 true 时,pageNum<=0 时会查询第一页,pageNum>pages(超过总数时),会查询最后一页。默认false 时,直接根据参数进行查询。
3、dao层分页代码
public List<User> getUserList3() {
PageHelper.startPage(1, 5);
return getSqlSession().selectList(UserMapper.class.getName() + ".getUserList2");
}
public Page<User> getUserList4() {
Page<User> page = PageHelper.startPage(1, 5).doSelectPage(new ISelect() {
@Override
public void doSelect() {
getSqlSession().selectList(UserMapper.class.getName() + ".getUserList2");
}
});
return page;
}
4、测试代码
@Test
public void testGetUserList333(){
List<User> userList = userDao.getUserList3();
for (User user : userList) {
System.out.println(user);
}
}
@Test
public void testGetUserList444(){
Page<User> page = userDao.getUserList4();
// Page{count=true, pageNum=1, pageSize=5, startRow=0, endRow=5, total=6, pages=2, reasonable=true, pageSizeZero=false}
System.out.println(page);
List<User> result = page.getResult();
for (User user : result) {
System.out.println(user);
}
}
注意:
- 使用pagehelper时需要在在查询之前传入当前页码和分页数量 PageHelper.startPage(1, 5)
- 只有紧跟在PageHelper.startPage方法后的第一个Mybatis的查询(Select)方法会被分页。
- 使用pagehelper后sql中不用再写分页语句limit。
七:通用Mapper
通用Mapper: 是MyBatis的一个插件,里面包含常用的单表操作的常用方法(注意:只支持单表操作,不支持多表),可以直接调用方法而无需创建映射文件xxxMapper.xml, 使用MyBatis中的接口方式集成, 通用映射内部是使用的是JPA。
###通用Mapper集成
1、 引入依赖
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper</artifactId>
<version>3.4.0</version>
</dependency>
2、在Spring中配置
通用mapper的集成方式多种多样,可以使用xml、注解等方式,xml方式支持两种方式,这里使用MapperScannerConfigurer方式。
mybatis如果使用了接口方式需要配置org.mybatis.spring.mapper.MapperScannerConfigurer的,如果配置了只需要将包名中的org改成tk即可,
org —> tk
<!-- 配置mybatis mapper接口 -->
<bean class="tk.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.mengdee.manager.dao.**.mapper" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
</bean>
3、创建实体
package com.mengdee.manager.domain;
import org.apache.ibatis.type.IntegerTypeHandler;
import org.apache.ibatis.type.JdbcType;
import tk.mybatis.mapper.annotation.ColumnType;
import tk.mybatis.mapper.annotation.NameStyle;
import tk.mybatis.mapper.code.Style;
import javax.persistence.*;
@Table(name = "sys_user")
// 通用Mapper默认情况下是将实体类字段按照驼峰转下划线形式的表名列名进行转换。例如 实体类的 userName 可以映射到表的 user_name 上
@NameStyle(Style.camelhump)
public class UserModel {
@Id
@GeneratedValue(generator = "JDBC")
private Long id;
@Column(name = "name")
private String name;
@ColumnType(
column = "age",
jdbcType = JdbcType.INTEGER,
typeHandler = IntegerTypeHandler.class // 常用于枚举类
)
private int age;
@Transient
private int level;
public UserModel() {
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
}
}
4、创建Mapper接口,需要集成Mapper
public interface UserModelMapper extends Mapper<UserModel> {
// mybatis注解方式, 额外演示一下,和通用mapper没关系
@Select("select * from sys_user where name = #{name}")
UserModel selectByName(String name);
}
5、 在实现类中调用通用Mapper中的封装好的方法
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserModelMapper userModelMapper;
@Override
public void getUserList2(){
List<UserModel> userModels = userModelMapper.selectAll();
UserModel userModel = userModelMapper.selectByPrimaryKey(1L);
UserModel admin = userModelMapper.selectByName("admin");
Example example = new Example(UserModel.class);
example.createCriteria().andGreaterThan("id", 1);
example.setOrderByClause("id desc");
List<UserModel> userModels1 = userModelMapper.selectByExample(example);
}
}
6、测试
@Autowired
private UserService userService;
@Test
public void testGetUserList2ForMapper(){
userService.getUserList2();
}
关于注解:
@Table: 用于标注实体名对应的表名
@NameStyle:用于配置实体属性和数据库字段的转换方式
@Id:用于标记主键
@GeneratedValue:生成id值,可以是自增的,也可以是uuid
@Column:配置列名
@ColumnType:可以同时指定列名、数据类型等
@Transient:标记不需要映射的字段
分享一个朋友的人工智能教程。比较通俗易懂,风趣幽默,感兴趣的朋友可以去看看。
Demo下载地址:http://download.csdn.net/download/vbirdbest/10264378