• 欢迎光临~

sql 执行公共类

开发技术 开发技术 2022-09-27 次浏览
package com.feifan.aida.utils;

import com.alibaba.druid.pool.DruidDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
@Component
public class CommonExecuteSqlUtils {

    @Value("${spring.datasource.saas.url}")
    private String url;

    @Value("${spring.datasource.saas.username}")
    private String user;

    @Value("${spring.datasource.saas.password}")
    private String pwd;

    @Value("${spring.datasource.saas.connectionProperties}")
    private String connectionProperties;

    @Value("${spring.datasource.saas.filters}")
    private String filter;


    /**
     * 手动执行sql查询
     * @param
     * @return
     */
    public List<Map<String, Object>> getConnectionAndExecuteQuery(String sql) {
        Statement stmt = null;
        Connection con = null;
        ResultSet resultSet = null;
        List<Map<String, Object>> resultList = new ArrayList<>();

        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(pwd);
        dataSource.setConnectionProperties(connectionProperties);
        try {
            dataSource.setFilters(filter);
            con = dataSource.getConnection();
            stmt = con.createStatement();
            resultSet = stmt.executeQuery(sql);
            ResultSetMetaData metaData = resultSet.getMetaData();

            while (resultSet.next()) {
                Map<String, Object> dataMap = new HashMap<>();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    dataMap.put(metaData.getColumnLabel(i), resultSet.getObject(i));
                }                resultList.add(dataMap);
            }
        }catch (Exception e){
            log.error("数据库连接失败",e);
        }finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (con != null){
                    con.close();
                }
            } catch (SQLException e) {
                log.error("数据库资源关闭失败",e);
            }
        }

        return resultList;
    }

    public void executeSql(String sql){

        String driver = "com.mysql.cj.jdbc.Driver";
        try {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url, user, pwd);
            conn.setAutoCommit(false);
            PreparedStatement ps = conn.prepareStatement(sql);
            //创建表要用 execute
            ps.execute();
            conn.commit();
            ps.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 

程序员灯塔
转载请注明原文链接:sql 执行公共类
喜欢 (0)