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

Java连接数据库

一、最原始的连接数据库

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

首先是编写db.proterties文件

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

为了方便使用可以编写一个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();
            }
        }
    }
}

可以编写一个测试程序来测试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);
        }
    }
}

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

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

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

第一步仍然是编写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

依然编写一个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();
    }

}

配置了上面这个后，最后还配置一个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>

然后可以编写一个父类来实现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);
        }
    }

}

最好遵守规范一个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());
    }
}

这样就编写好一个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>

依旧先编写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

编写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>

然后就可以编写接口和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>

这样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>

可以配置文件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配置类

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

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

@ConfigurationProperties(prefix = "spring.datasource.druid")

需要和yaml中
    
spring:
  datasource:
    druid:

对应
    
或者也可以用下面的形式

第二就是一定要用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、SpringBoot整合Mybatis

需要导入依赖

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

在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

编写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注解

然后就可以测试了，看看是否连接上了
开启事务的注解@Transactional一般用到service层的方法上

五、springboot整合mybatis-plus

创建数据库mybatis_plus

创建一个表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');

创建测试项目；以springBoot为例

导入依赖

<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>

编写配置文件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

编写实体类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);
    }

}

开始测试：查询全部的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

再次执行后

posted @ 2022-06-10 14:22 水三丫阅读(716) 评论(0) 编辑收藏举报

刷新页面返回顶部

水三丫