Java连接数据库从入门到入土

Java连接数据库

一、最原始的连接数据库

是没有导入任何数据源的；只导入了一个数据库驱动：mysql-connector-java-8.0.27.jar

1. 首先是编写db.proterties文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

2. 为了方便使用可以编写一个JdbcUtils的工具类来获取连接和关闭连接

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
 
public class JdbcUtils {
    private static  String driver = null;
    private static  String url = null;
    private static  String username = null;
    private static  String password = null;
 
    static{
        try {
            InputStream in =  JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);
 
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
 
            //驱动只用加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    //获取链接
    public static Connection getConection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
 
    //释放资源
    public static void releass(Connection conn, Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

3. 可以编写一个测试程序来测试sql语句操作了

import mysqlTest.uttils.JdbcUtils;
 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
//SQL注入
public class SqlInjection {
    public static void main(String[] args) {
       // login("吴八","123456");
        login(" 'or '1=1"," 'or '1=1");//SQL注入
    }
 
    //登入业务
    public static void login(String username,String password){
        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;
        try {
            conn= JdbcUtils.getConection();
            
            //方式一：这个是有sql注入风险的
            /**
            st=conn.createStatement();
            //SQl
            String sql="select * from users where `name` = '"+username+"' AND 			`password`='"+password+"'";
            rs=st.executeQuery(sql);//查询完返回一个结果集
            */
            
           //方式二：这个没有sql注入风险；使用占位符
            String sql = "select * from users where `name` =?" + "AND `password`=?";
            st = conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,password);
            
            while (rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("=======================================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.releass(conn,st,rs);
        }
    }
}

4. 至此java就连上数据库了，只是这个比较简陋，不好使用和统一管理，但是对于新手来说，连接上就已经很神奇了。

二、JavaWeb阶段使用druid数据源来连接数据库

数据源 druid-1.2.8.jar 以下是学习尚硅谷的JavaWeb课程所学，感谢！！！

1. 第一步仍然是编写jdbc.properties文件

username=root
password=123456
url=jdbc:mysql://localhost:3306/form?useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
driverClassName=com.mysql.jdbc.Driver
initialSize=5
maxActive=10

2. 依然编写一个JdbcUtils的工具类，方便操作数据库，只是这里不同的是开启了事务，所以使用了ThreadLocal线程类来统一数据库的连接

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
 
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
 
public class JdbcUtils {
    private static final DruidDataSource dataSource;
    private static final ThreadLocal<Connection> conns = new ThreadLocal<Connection>();
 
    static {
        try {
            //读取jdbc.properties的属性配置位置
            InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            //从流中加载数据
            properties.load(inputStream);
            //创建了数据连接池
            dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }
 
    /**
     * 获取数据库连接池的连接
     * @return 如果返回null,获取连接失败；有值就是获取连接成功
     */
    public static Connection getConnection(){
        Connection conn = conns.get();
 
        if(conn == null){
            try {
                conn = dataSource.getConnection();//从数据库连接池中国获取连接
                conns.set(conn); //保存连接，给后面的jdbc使用
                conn.setAutoCommit(false);//设置为手动管理
            } catch (SQLException e){
                e.printStackTrace();
            }
        }
        return conn;
    }
 
    /**
     * 提交事务并释放关闭连接
     */
    public static void commitAndClose(){
        Connection connection = conns.get();
        if(connection != null){//如果不等于null说明以前使用过
            try {
                connection.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        //用完后一定要remove，否则会出错
        conns.remove();
    }
 
    /**
     * 回滚事务并释放关闭连接
     */
    public static void rollbackAndClose(){
        Connection connection = conns.get();
        if(connection != null){//如果不等于null说明以前使用过
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        //用完后一定要remove，否则会出错
        conns.remove();
    }
 
}

3. 配置了上面这个后，最后还配置一个filter也就是web中的过滤器来配合使用就更加方便了；下面是配置过滤器

   • 首先编写过滤器类

   import com.mhy.utils.JdbcUtils;
    
   import javax.servlet.*;
   import java.io.IOException;
    
   public class TransactionFilter implements Filter {
       @Override
       public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
           try {
               filterChain.doFilter(servletRequest,servletResponse);
               JdbcUtils.commitAndClose();//提交事务
           } catch (Exception e) {
               JdbcUtils.rollbackAndClose();
               e.printStackTrace(); //回滚事务
               throw new RuntimeException(e);
           }
       }
    
       @Override
       public void init(FilterConfig filterConfig) throws ServletException {
       }
       @Override
       public void destroy() {
       }
   }

   • 然后去web.xml中去注册和配置就可以了

   <filter>
       <filter-name>TransactionFilter</filter-name>
       <filter-class>com.mhy.filter.TransactionFilter</filter-class>
   </filter>
   <filter-mapping>
       <filter-name>TransactionFilter</filter-name>
       <!--        当前工程下的所有请求-->
       <url-pattern>/*</url-pattern>
   </filter-mapping>

4. 然后可以编写一个父类来实现CRUD（增删改查）等操作

   import com.mhy.utils.JdbcUtils;
   import org.apache.commons.dbutils.QueryRunner;
   import org.apache.commons.dbutils.handlers.BeanHandler;
   import org.apache.commons.dbutils.handlers.BeanListHandler;
   import org.apache.commons.dbutils.handlers.ScalarHandler;
    
   import java.sql.Connection;
   import java.sql.SQLException;
   import java.util.List;
    
   public abstract class BaseDao {
       //使用DbUtils操作数据库
       private final QueryRunner queryRunner = new QueryRunner();
    
       /**
        * update()方法用来执行:Insert/Update/Delete语句
        * @return 如果返回-1，说明执行失败，其他表示影响的行数
        */
       public int update(String sql,Object ... args){
           Connection connection = JdbcUtils.getConnection();
           try {
               return queryRunner.update(connection,sql,args);
           } catch (SQLException e) {
               e.printStackTrace();
               throw new RuntimeException(e);
           }
       }
    
       /**
        * 查询返回一个javaBean的语句
        * @param type  返回的对象类型
        * @param sql   执行的sql语句
        * @param args  sql对应的参数值
        * @param <T>   返回的类型的泛型
        * @return
        */
       public <T> T queryForOne(Class<T> type,String sql,Object ... args){
           Connection connection = JdbcUtils.getConnection();
           try {
               return queryRunner.query(connection,sql,new BeanHandler<T>(type),args);
           } catch (SQLException e) {
               e.printStackTrace();
               throw new RuntimeException(e);
           }
       }
    
       /**
        * 查询返回多个javaBean的语句
        * @param type  返回的对象类型
        * @param sql   执行的sql语句
        * @param args  sql对应的参数值
        * @param <T>   返回的类型的泛型
        * @return
        */
       public <T> List<T> queryForList(Class<T> type, String sql, Object ... args){
           Connection connection = JdbcUtils.getConnection();
           try {
               return queryRunner.query(connection,sql,new BeanListHandler<T>(type),args);
           } catch (SQLException e) {
               e.printStackTrace();
               throw new RuntimeException(e);
           }
       }
    
       /**
        * 执行返回一行一列的sql语句
        * @param sql   执行的sql语句
        * @param args  执行对应的参数值
        * @return 返回相对于的数据
        */
       public Object queryForSingleValue(String sql,Object ... args){
           Connection connection = JdbcUtils.getConnection();
           try {
               return queryRunner.query(connection,sql,new ScalarHandler(),args);
           } catch (SQLException e) {
               e.printStackTrace();
               throw new RuntimeException(e);
           }
       }
    
   }

5. 最好遵守规范一个xxxDao接口，一个xxxDao接口的实现了xxxDaoImpl类

   • 接口

   import com.mhy.pojo.User;
    
   public interface UserDao {
    
       /**
        * 根据用户名查询用户信息
        * @param username 用户名
        * @return 如果返回null，说明没有这个用户；反之亦然
        */
       public User queryUserByUsername(String username);
    
       /**
        * 根据用户名和用户查询用户信息
        * @param username 用户名
        * @param password 用户密码
        * @return 如果返回null，说明用户名和密码错误；反之亦然
        */
       public User queryUserByUsernameAndPassword(String username,String password);
    
       /**
        * 保存用户信息
        * @param user
        * @return 如果返回 -1表示操作失败，其他是sql语句影响的行数
        */
       public int saveUser(User user);
    
   }

   • 实现类

   import com.mhy.dao.UserDao;
   import com.mhy.pojo.User;
    
   public class UserDaoImpl extends BaseDao implements UserDao {
       @Override
       public User queryUserByUsername(String username) {
           String sql = "select `id`,`username`,`password`,`email` from t_user where username = ?";
           return queryForOne(User.class,sql,username);
       }
    
       @Override
       public User queryUserByUsernameAndPassword(String username, String password) {
           String sql = "select `id`,`username`,`password`,`email` from t_user where username = ? and password = ?";
           return queryForOne(User.class,sql,username,password);
       }
    
       @Override
       public int saveUser(User user) {
           String sql = "INSERT INTO `t_user`(`username`,`password`,`email`) \n" +
                   "VALUES(?,?,?)";
           return update(sql,user.getUsername(),user.getPassword(),user.getEmail());
       }
   }

6. 这样就编写好一个Dao连接数据库的简单实用了，这样在原生的JavaWeb上还是挺方便的，推荐使用

三、spring加mybatis来连接数据库

需要导入的maven的pom,xml依赖，当然不同的版本需要自己测试

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.37</version>
</dependency>
 
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
</dependency>
 
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.9</version>
</dependency>
 
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.3.15</version>
</dependency>
 
<dependency>
    <groupId>org.aspectj</groupId>
    <artifactId>aspectjweaver</artifactId>
    <version>1.9.4</version>
</dependency>
 
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>2.0.7</version>
</dependency>

1. 依旧先编写jdbc.proterties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/commodity?useSSL=true&useUnicode=true&characterEncoding=UTF-8
jdbc.username=root
jdbc.password=123456

2. 编写mybaties-config.xml、spring-dao.xml、spring-service.xml文件

   • mybaties-config.xml

   <?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>
    
       <settings>
           <setting name="logImpl" value="STDOUT_LOGGING"/>
           <setting name="mapUnderscoreToCamelCase" value="true"/>
           <setting name="cacheEnabled" value="true"/>
       </settings>
    
       <typeAliases>
           <package name="com.mhy.ssm.pojo"/>
       </typeAliases>
       
   </configuration>

   • spring-dao.xml

   <?xml version="1.0" encoding="UTF8"?>
   <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
           https://www.springframework.org/schema/beans/spring-beans.xsd
           http://www.springframework.org/schema/context
           https://www.springframework.org/schema/context/spring-context.xsd">
    
   <!--    1、关联数据库配置文件-->
       <context:property-placeholder location="classpath:jdbc.properties"/>
   <!--    2、连接池-->
       <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close" lazy-init="false">
           <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>
    
   <!--    3、sqlSessionFactory-->
       <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
           <property name="dataSource" ref="dataSource"/>
   <!--        spring 接管 mybatis-config.xml-->
           <property name="configLocation" value="classpath:mybatis-config.xml"/>
           <property name="mapperLocations" value="classpath:com/mhy/ssm/mapper/*.xml"/>
       </bean>
    
   <!--    4、配置dao接口扫描包，动态实现了dao接口注入spring容器-->
       <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
           <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
           <property name="basePackage" value="com.mhy.ssm.mapper"/>
       </bean>
    
   <!--    <context:component-scan base-package="com.mhy.ssm.mapper"/>-->
    
   </beans>

   • spring-service.xml这个主要是展示开启事务

   <?xml version="1.0" encoding="UTF8"?>
   <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:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop"
          xsi:schemaLocation="http://www.springframework.org/schema/beans
           https://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/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:component-scan base-package="com.mhy.ssm.service"/>
    
       <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
           <property name="dataSource" ref="dataSource"/>
       </bean>
    
       <tx:advice id="transactionInterceptor" transaction-manager="transactionManager">
           <tx:attributes>
               <!--     需要开启事务订单方法       -->
               <!--            传播特性 propagation 默认是REQUIRED-->
               <!--            read-only="true"表只读事务-->
               <tx:method name="queryUsers" propagation="REQUIRED"/>
               <tx:method name="deleteUserById" propagation="REQUIRED"/>
               <tx:method name="addUser" propagation="REQUIRED"/>
               <tx:method name="updateUser" propagation="REQUIRED"/>
               <tx:method name="*"/>
           </tx:attributes>
       </tx:advice>
    
       <!--    - 事务的切入-->
       <aop:config proxy-target-class="true">
           <aop:pointcut id="txPointcut" expression="execution(* com.mhy.ssm.service.*.*(..))"/>
           <aop:advisor advice-ref="transactionInterceptor" pointcut-ref="txPointcut"/>
       </aop:config>
    
   </beans>

3. 然后就可以编写接口和mybatis的文件了

   • 接口

   import com.mhy.ssm.pojo.User;
   import org.apache.ibatis.annotations.Param;
    
   import java.util.List;
    
   public interface UserMapper {
    
       int addUser(User user);
    
       int updateUser(User user);
    
       int deleteUserById(@Param("userCode") String userCode);
    
       List<User> queryUsers(@Param("userCode") String userCode);
    
   }

   • mybatis文件

   <?xml version="1.0" encoding="UTF-8" ?>
   <!DOCTYPE mapper
           PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    
   <mapper namespace="com.mhy.ssm.mapper.UserMapper">
    
       <insert id="addUser" parameterType="user">
           insert into t_user(user_code, user_name, phone, address)
           values (#{userCode},#{userName},#{phone},#{address})
       </insert>
    
       <update id="updateUser" parameterType="user">
           update t_user set user_name=#{userName},phone=#{phone},address=#{address}
           where user_code=#{userCode}
       </update>
    
       <delete id="deleteUserById" parameterType="String">
           delete from t_user where user_code=#{userCode}
       </delete>
    
       <select id="queryUsers" resultType="user" parameterType="String">
           select user_code,user_name,phone,address
           from t_user
           <where>
               <if test="userCode != null">
                   user_code=#{userCode}
               </if>
           </where>
       </select>
    
   </mapper>

4. 这样mybatis也就连接上了数据了

四、SpringBoot连接数据库和整合mybatis

1、数据源连接数据库

需要导入的maven的pom,xml依赖，当然不同的版本需要自己测试

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
 
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
 
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.8</version>
</dependency>
 
<dependency>
    <groupId>org.apache.logging.log4j</groupId>
    <artifactId>log4j-core</artifactId>
    <version>2.17.1</version>
</dependency>
 
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
 
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
 
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

1. 可以配置文件application.yml，application.properties也类似，当然也可以自己定义java配置类（也就@Config注解的类）

   • application.yml

   spring:
   #  datasource:
   #    druid:
   #      username: root
   #      password: 123456
   #      url: jdbc:mysql://localhost:3306/smbms?useSSL=true&useUnicode=true&characterEncoding=UTF-8
   #      driver-class-name: com.mysql.cj.jdbc.Driver
     datasource:
   #    数据的用户账号
       username: root
   #    数据的用户密码
       password: 123456
   #    url
       url: jdbc:mysql://localhost:3306/smbms?useSSL=true&useUnicode=true&characterEncoding=UTF-8
       driver-class-name: com.mysql.cj.jdbc.Driver
   #    更改连接的数据源
       type: com.alibaba.druid.pool.DruidDataSource
   #    下面是druid的配置
       druid:
         filters:
           - stat
           - wall
           - log4j
   #     开启过滤器一些驱动
         filter:
           stat:
             enabled: true
             log-slow-sql: true
             merge-sql: true
             slow-sql-millis: 2000
           wall:
             config:
               multi-statement-allow: true
   #     开启过滤器
         web-stat-filter:
   #     这个必须设置为true才可以实现bean的自动注入
           enabled: true
           exclusions:
             -"*.js"
             -"*.gif"
             -"*.jpg"
             -"*.png"
             -"*.css"
             -"*.ico"
             -"/monitor/druid/*"
   #     开启后台监控功能，但是现在加了监听器，使用必须开启监听器，否是无法显示sql监控
         stat-view-servlet:
   #     这里必须设置为true才可以实现bean的自动注入
           enabled: true
   #     后台登入账号   可以自己定义
           login-username: 123456
   #     后台登入密码   可以自己定义
           login-password: 123456

   或者这样都是可以的

   spring:
     datasource:
    druid:
         username: root
         password: 123456
         url: jdbc:mysql://localhost:3306/smbms?useUnicode=true&charactor=utf-8&useSSL=false
         driver-class-name: com.mysql.cj.jdbc.Driver
         filters: stat,wall,log4j
         stat-view-servlet:
           login-username: 123456
           login-password: 123456
           enabled: true
         web-stat-filter:
           enabled: true
           exclusions:
             -"*.js"
             -"*.gif"
             -"*.jpg"
             -"*.png"
             -"*.css"
             -"*.ico"
             -"/monitor/druid/*"
         initialSize: 5
         minIdle: 5
         maxActive: 20
         maxWait: 60000
         timeBetweenEvictionRunsMillis: 60000
         minEvictableIdleTimeMillis: 300000
         validationQuery: SELECT 1 FROM DUAL
         testWhileIdle: true
         testOnBorrow: false
         testOnReturn: false
         poolPreparedStatements: true
         maxPoolPreparedStatementPerConnectionSize: 20
         use-global-data-source-stat: true
         connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

   • 自定义java配置类

     1. 在自动配置java类时，有几个注意点和容易错误的点

     2. 第一就是配置filters时，需要和注入时的名称一致

        @ConfigurationProperties(prefix = "spring.datasource.druid")
         
        需要和yaml中
            
        spring:
          datasource:
            druid:
         
        对应
            
        或者也可以用下面的形式

     3. 第二就是一定要用log4j的原生包，如果用的是log4j-core一定会在注册log4jFilter时报错

        <!--        <dependency>-->
        <!--            <groupId>org.apache.logging.log4j</groupId>-->
        <!--            <artifactId>log4j-core</artifactId>-->
        <!--            <version>2.17.1</version>-->
        <!--        </dependency>-->
         
        <!--  使用这种 -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

     • 当然首先的配置一下yaml

     spring:
       datasource:
         username: root
         password: 123456
         url: jdbc:mysql://localhost:3306/smbms?useUnicode=true&charactor=utf-8&useSSL=false
         driver-class-name: com.mysql.cj.jdbc.Driver
         filters: stat,wall,log4j
         
     #    initialSize: 5
     #    minIdle: 5
     #    maxActive: 20
     #    maxWait: 60000
     #    timeBetweenEvictionRunsMillis: 60000
     #    minEvictableIdleTimeMillis: 300000
     #    validationQuery: SELECT 1 FROM DUAL
     #    testWhileIdle: true
     #    testOnBorrow: false
     #    testOnReturn: false
     #    poolPreparedStatements: true
     #    maxPoolPreparedStatementPerConnectionSize: 20
     #    use-global-data-source-stat: true
     #    connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

     • 然后就位置Config类

     import com.alibaba.druid.pool.DruidDataSource;
     import com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties;
     import com.alibaba.druid.support.http.StatViewServlet;
     import com.alibaba.druid.support.http.WebStatFilter;
     import org.springframework.boot.context.properties.ConfigurationProperties;
     import org.springframework.boot.web.servlet.FilterRegistrationBean;
     import org.springframework.boot.web.servlet.ServletRegistrationBean;
     import org.springframework.context.annotation.Bean;
     import org.springframework.context.annotation.Configuration;
      
     import javax.sql.DataSource;
     import java.util.HashMap;
     import java.util.Map;
      
     @Configuration
     public class DruidConfig {
      
         @Bean
         @ConfigurationProperties(prefix = "spring.datasource")
         public DataSource druidDataSource(){
             return new DruidDataSource();
         }
      
         @Bean
         public ServletRegistrationBean servletRegistrationBean(){
      
             ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*");
      
             Map<String,String> map = new HashMap<>();
      
             map.put("loginUsername","admin");
             map.put("loginPassword","admin");
             map.put("allow","");
      
             servletRegistrationBean.setInitParameters(map);
      
             return servletRegistrationBean;
         }
         
           @Bean
         public FilterRegistrationBean filterRegistrationBean(){
             FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<>();
             WebStatFilter webStatFilter = new WebStatFilter();
             filterRegistrationBean.setFilter(webStatFilter);
             filterRegistrationBean.addUrlPatterns("/*");
        		      filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
      
             return filterRegistrationBean;
         }
     }

   2. 虽然看上去比较多，但是功能确实强大了许多，而去都是配置，如果你需要连接数据库，就不用配置那么多

2、SpringBoot整合Mybatis

1. 需要导入依赖

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.2</version>
</dependency>

2. 在application.yml中配置Mybatis的信息

mybatis:
#  扫描的实体类包
  type-aliases-package: com.mhy.pojo
#  sql的xml文件的位置  在resources目录下
  mapper-locations: classpath:mybatis/mapper/*.xml
  configuration:
#    日志类
    log-impl: org.apache.ibatis.logging.log4j2.Log4j2Impl
#    开启驼峰命名的转换
    map-underscore-to-camel-case: true
    cache-enabled: true
 
#开启控制台的日志输出
logging:
  level:
    com:
      mhy:
        mapper: debug

3. 编写Mapper接口和Mapper.xml文件

   • Mapper接口

   @Mapper
   @Repository
   public interface UserMapper {
    
       List<User> queryAllUsers();
       
       void updateUserById(@Param("id") Integer id,@Param("userName") String userName);
    
   }

   • Mapper.xml文件

   <?xml version="1.0" encoding="UTF-8" ?>
   <!DOCTYPE mapper
           PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   <mapper namespace="com.mhy.mapper.UserMapper">
    
       <!-- 开启缓存  -->
       <cache readOnly="true"/>
       
       <select id="queryAllUsers" resultType="user">
           select * from smbms.t_smbms_user
       </select>
       
       <update id="updateUserById">
           update smbms.t_smbms_user set userName = #{userName} where id = #{id}
       </update
   </mapper>

   这里注册Mapper有两种方式

   • 方式一，Mapper类上加上@Mapper注解

   

   • 方法二，在主启动类上加入@MapperScan注解

   

4. 然后就可以测试了，看看是否连接上了

5. 开启事务的注解@Transactional一般用到service层的方法上

五、springboot整合mybatis-plus

1. 创建数据库mybatis_plus

2. 创建一个表user 并插入数据

   CREATE TABLE USER
   (
       id BIGINT(20) NOT NULL COMMENT '主键ID',
       NAME VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
       age INT(11) NULL DEFAULT NULL COMMENT '年龄',
       email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
       PRIMARY KEY (id)
   );
    
   INSERT INTO USER (id, NAME, age, email) VALUES
   (1, 'Jone', 18, 'test1@baomidou.com'),
   (2, 'Jack', 20, 'test2@baomidou.com'),
   (3, 'Tom', 28, 'test3@baomidou.com'),
   (4, 'Sandy', 21, 'test4@baomidou.com'),
   (5, 'Billie', 24, 'test5@baomidou.com');

3. 创建测试项目；以springBoot为例

4. 导入依赖

   <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
   </dependency>
    
   <dependency>
       <groupId>org.projectlombok</groupId>
       <artifactId>lombok</artifactId>
   </dependency>
    
   <dependency>
       <groupId>com.baomidou</groupId>
       <artifactId>mybatis-plus-boot-starter-test</artifactId>
       <version>3.5.2</version>
   </dependency>
    
   <!-- 我一般导入druid的数据源，当然也可以使用springboot自带的 -->
   <dependency>
       <groupId>com.baomidou</groupId>
       <artifactId>mybatis-plus-boot-starter-test</artifactId>
       <version>3.5.2</version>
   </dependency>

5. 编写配置文件application.yaml

   server:
     port: 8081
   spring:
     datasource:
       password: 123456
       username: root
       url: jdbc:mysql://localhost:3306/mybatis_plus?useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
       driver-class-name: com.mysql.cj.jdbc.Driver
       type: com.alibaba.druid.pool.DruidDataSource

6. 编写实体类pojo和测试的Mapper

   User实体类

   package com.mhy.mybatis_plus.pojo;
    
   import lombok.AllArgsConstructor;
   import lombok.Data;
   import lombok.NoArgsConstructor;
   import lombok.ToString;
    
   @Data
   @AllArgsConstructor
   @NoArgsConstructor
   @ToString
   public class User {
       private Long id;
       private String name;
       private Integer age;
       private String email;
   }

   mapper接口 这里只是继承了一个BaseMapper的父类

   @Repository
   public interface UserMapper extends BaseMapper<User> {
   }

   需要在扫描mapper这个文件下的Mapper类

   在主启动类上写MapperScan(路径)进行扫描

   @SpringBootApplication
   @MapperScan("com.mhy.mybatis_plus.mapper")
   public class MybatisPlusApplication {
    
       public static void main(String[] args) {
           SpringApplication.run(MybatisPlusApplication.class, args);
       }
    
   }

7. 开始测试：查询全部的users数据

   @Test
   void contextLoads() {
       List<User> users = userMapper.selectList(null);
       users.forEach(System.out::println);
   }

   结果：

   

配置日志

也就是把所有的sql显示出来；配置控制台输出

# 配置日志
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

再次执行后