记录一次JDBC连接数据库操作

将数据库相关参数保存到配置文件中：

Download_Path=

publicState=3

needEncoding=false
#链接任务中心仓库 获取关联方数据(生产)
RE_name=关联方查询
RE_driver=oracle.jdbc.driver.OracleDriver
RE_username=*********
RE_password=*********
RE_url = jdbc:oracle:thin:@***********:1521:salemd

接口代码：

package cn.sh.ideal.relative.service.impl;

import cn.sh.ideal.datastage.util.JDBCUtil;
import cn.sh.ideal.framework.dao.util.Page;
import cn.sh.ideal.framework.dao.util.Where;
import cn.sh.ideal.framework.exception.ServiceException;
import cn.sh.ideal.framework.util.Empty;
import cn.sh.ideal.relative.entity.relativeView;
import cn.sh.ideal.relative.service.IrelativeServices;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
public class relativeServiceImpl implements IrelativeServices{

    @Value("${RE_driver}")
    private String RE_driver;
    @Value("${RE_username}")
    private String RE_username;
    @Value("${RE_password}")
    private String RE_password;
    @Value("${RE_url}")
    private String RE_url;

    @Override
    public List<relativeView> findDateByMap(Map<String, Object> map) throws Exception {

        List<relativeView>  relativeViews =new ArrayList<>();
        // 采用手写sql  为了方便对接 旧的考勤系统表
        StringBuffer sql = new StringBuffer("select ci.related_Person from saledmmonitor.v_related_query ci   where 1=1 ");

        if(!"".equals(map.get("principal"))){
            sql.append("   and  ci.principal = '"+map.get("principal").toString().trim()+"'");
        }
        if(!"".equals(map.get("relatedType"))){
            sql.append("  and    ci.related_Type = '"+map.get("relatedType").toString().trim()+"'");
        }
        if(!"".equals(map.get("relatedPerson"))){
            sql.append("  and    Upper(ci.related_Person) like '%"+map.get("relatedPerson").toString().trim()+"%'");
        }
        JDBCUtil jdbcUtil = new JDBCUtil();
        List<Map<String, Object>> list = jdbcUtil.execQuery(RE_driver, RE_url, RE_username, RE_password, sql.toString(), null);
        if (!Empty.isEmpty(list)) {
            for(Map m : list){
                relativeView relativeview=new relativeView();
                relativeview.setRelatedPerson(!Empty.isEmpty(m.get("RELATED_PERSON"))?m.get("RELATED_PERSON")+"":"");
                relativeViews.add(relativeview);
            }
        }
        return relativeViews;
    }

    @Override
    public void addVO(relativeView vo) throws ServiceException {

    }

    @Override
    public void updateVO(relativeView vo) throws ServiceException {

    }

    @Override
    public List<relativeView> delList(String[] ids) throws ServiceException {
        return null;
    }

    @Override
    public List<relativeView> findList(Page page, Where where) throws ServiceException {
        return null;
    }

    @Override
    public List<?> findList(Page page, Where where, String from) throws ServiceException {
        return null;
    }

    @Override
    public relativeView findVO(String id) throws ServiceException {
        return null;
    }
}

JDBC工具类：

package cn.sh.ideal.datastage.util;

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

public class JDBCUtil {
    Connection connection = null;// 连接对象
    PreparedStatement preparedStatement = null;// 语句对象
    ResultSet resultSet = null;// 结果集对象

    /**
     * 获得连接对象
     *
     * @return 连接对象
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public Connection getConnection(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        connection = DriverManager.getConnection(url, username, password);
        return connection;
    }

    /**
     * 关闭连接
     *
     * @throws SQLException
     */
    public void close(ResultSet rs, PreparedStatement pstmt, Connection con) {

        try {
            if (rs != null)
                rs.close();
            if (pstmt != null)
                pstmt.close();
            if (con != null)
                con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 执行查询
     *
     * @param sql    传入的预设的 sql语句
     * @param params 问号参数列表
     * @return 查询后的结果
     */
    public List<Map<String, Object>> execQuery(String driver, String url, String username, String password, String sql, Object[] params) {

        try {
            this.getConnection(driver, url, username, password);// 获得连接对象
            this.preparedStatement = this.connection.prepareStatement(sql);// 获得预设语句对象

            if (params != null) {
                // 设置参数列表
                for (int i = 0; i < params.length; i++) {
                    // 因为问号参数的索引是从1开始，所以是i+1，将所有值都转为字符串形式，好让setObject成功运行
                    this.preparedStatement.setObject(i + 1, params[i] + "");
                }
            }
            // 执行查询
            ResultSet resultSet = preparedStatement.executeQuery();
            List<Map<String, Object>> list = new ArrayList<>();
            // 获得结果集元数据（元数据就是描述数据的数据，比如把表的列类型列名等作为数据）
            ResultSetMetaData rsmd = resultSet.getMetaData();
            // 获得列的总数
            int columnCount = rsmd.getColumnCount();
            // 遍历结果集
            while (resultSet.next()) {
                Map<String, Object> hm = new HashMap<String, Object>();
                for (int i = 0; i < columnCount; i++) {
                    // 根据列索引取得每一列的列名,索引从1开始
                    String columnName = rsmd.getColumnName(i + 1);
                    // 根据列名获得列值
                    Object columnValue = resultSet.getObject(columnName);
                    // 将列名作为key，列值作为值，放入 hm中，每个 hm相当于一条记录
                    hm.put(columnName,columnValue);
                }
                // 将每个 hm添加到al中, al相当于是整个表，每个 hm是里面的一条记录
                list.add(hm);
                //显示两条数据
            }
            return list;

        } catch (ClassNotFoundException | SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            this.close(this.resultSet, this.preparedStatement, this.connection);
        }
        return null;
    }
    /**
     * 执行插入更新
     *
     * @param sql    传入的预设的 sql语句
     * @param params 问号参数列表
     *
     */
    public void updateQuery(String driver, String url, String username, String password, String sql, Object[] params){
        try {
            this.connection= this.getConnection(driver, url, username, password);// 获得连接对象
            this.preparedStatement = this.connection.prepareStatement(sql);// 获得预设语句对象
           if (params != null) {
                // 设置参数列表
                for (int i = 0; i < params.length; i++) {
                    // 因为问号参数的索引是从1开始，所以是i+1，将所有值都转为字符串形式，好让setObject成功运行
                    this.preparedStatement.setObject(i + 1, params[i] + "");
                }
            }
            this.preparedStatement.executeQuery();
            this.close(null,this.preparedStatement,this.connection);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}