【无标题】如何在springboot中执行裸sql

什么时候使用裸 SQL

Mybatis 可以解决90%的数据库访问. 但有时我们需要进行复杂的 join 和根据参数动态的构造 where 条件. 这时 Mybatis 提供的 sql 构建方式有点局限, 反而不如直接上手.

获得 JdbcTemplate

只要项目正确的配置了数据库, 即可通过 @Autowired 注入来获得 JdbcTemplate

package com.rambo.component;

import com.rambo.component.SqlExecutor;
import lombok.extern.slf4j.Slf4j;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

/**
 * 向容器提供 SqlExecutor
 */
@Slf4j
@Component
public class ApiAnnotation {

    /**
     * 封装直接拼接的 sql 实现
     *
     * @param jdbcTemplate 数据库连接
     * @return 类似 Mybatis
     */
    @Bean
    public SqlExecutor createSqlExecutor(@Autowired JdbcTemplate jdbcTemplate) {
        log.info("ApiAnnotation createSqlExecutor >>>");
        return new SqlExecutor(jdbcTemplate);
    }
}

package com.rambo.component;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.rambo.component.PrepareSimple;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Objects;
import java.util.function.Function;

/**
 * sql 执行器
 */
@Slf4j
public class SqlExecutor {

    private final JdbcTemplate jdbcTemplate;

    public SqlExecutor(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 直接执行 sql
     */
    public int exec(String sql, Object... params) throws SQLException {

        return jdbcTemplate.update(new PrepareSimple(sql, params));
    }
}

package com.rambo.component;

import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.PreparedStatementCreator;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

@Slf4j
public class PrepareSimple implements PreparedStatementCreator {

    private final String sql;
    private final Object[] params;

    public PrepareSimple(String sql, Object[] params) {
        this.sql = sql;
        this.params = params;
    }

    @Override
    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {

       PreparedStatement ps = con.prepareStatement(this.sql);

        for (int i = 0; i < params.length; i++) {
            ps.setObject(i + 1, params[i]);
        }

        return ps;
    }
}

下面我们就可以这样来直接执行 SQL 了

package com.rambo.service;

import com.rambo.component.SqlExecutor;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.SQLException;
import java.util.List;

/**
 * 维护用户表
 */
@Slf4j
@Service
public class UserService {

    @Autowired
    private SqlExecutor sqlExecutor;

    /**
     * 删除用户
     *
     * @param now 当前时间
     * @param dto 套餐对象
     */
    private void deleleUser(String uid) throws SQLException {

        int count = sqlExecutor.exec("delete from users where id=?", uid);
        log.info("deleleUser result count:{}", count);
    }
}