sql server工具类

1.依赖

<!--sqlserver驱动-->
    <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc</artifactId>
      <version>7.2.1.jre8</version>
    </dependency>

2.工具类

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Date;
import java.util.*;

public class SqlUtil {
    //获取sql server连接
    public static Connection getConnection() {
        Connection connection = null;
        try {
            Properties properties = new Properties();
            // 使用ClassLoader加载properties配置文件生成对应的输入流
            InputStream in = SqlUtil.class.getClassLoader().getResourceAsStream("xxx.properties");
            // 使用properties对象加载输入流
            properties.load(in);
            // 获取key对应的value值
            String driver = properties.getProperty("xx.driver");
            String url = properties.getProperty("xx.url");
            String user = properties.getProperty("xxx.user");
            String password = properties.getProperty("xxx.password");
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return connection;
    }
    //执行sql
    public List<Map> query(String sql,Object[] params) throws Exception {
        //获取连接
        Connection conn = getConnection();
        //普通sql
        PreparedStatement ps;
        //存储过程
        CallableStatement cs;
        //结果集
        ResultSet rs;
        //返回结果
        List<Map> list;
        //参数指针
        int i = 0;
        if(sql.toLowerCase().startsWith("call")){
            cs = conn.prepareCall(sql);
            for(;i<params.length;i++){
                if (params[i] instanceof String) {
                    cs.setString(i + 1, params[i].toString());
                } else if (params[i] instanceof Integer) {
                    cs.setInt(i + 1, Integer.parseInt(params[i].toString()));
                } else if (params[i] instanceof Date) {
                    cs.setDate(i + 1, (java.sql.Date) params[i]);
                } else {
                    cs.setObject(i + 1, params[i]);
                }
            }
            rs = cs.executeQuery();
        }else{
            ps = conn.prepareStatement(sql);
            for(;i < params.length;i++){
                if (params[i] instanceof String) {
                    ps.setString(i + 1, params[i].toString());
                } else if (params[i] instanceof Integer) {
                    ps.setInt(i + 1, Integer.parseInt(params[i].toString()));
                } else if (params[i] instanceof Date) {
                    ps.setDate(i + 1, (java.sql.Date) params[i]);
                } else {
                    ps.setObject(i + 1, params[i]);
                }
            }
            rs = ps.executeQuery();
        }
        list = convertList(rs);
        return list;
    }
    private static List<Map> convertList(ResultSet rs) throws SQLException {
        List<Map> list = new ArrayList<>();
        ResultSetMetaData md = rs.getMetaData();
        int columnCount = md.getColumnCount();
        while(rs.next()) {
            Map rowData = new LinkedHashMap();
            for(int i = 1; i <= columnCount; ++i) {
                rowData.put(md.getColumnLabel(i), rs.getObject(i));
            }
            list.add(rowData);
        }
        return list;
    }
}