投稿
  1. 首页
  2. 随笔

写个企业级的JdbcUtils

嘉立创 • 2022-12-17 • 随笔 • 阅读 37

写个企业级的JdbcUtils,第1张

写个企业级的JdbcUtils 
package com.grm.util;

import com.grm.exception.BusinessException;
import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class JdbcUtils {
    
    private static final String USERNAME = "root";
    //数据库密码（本身不能明文写在项目中，此处简化了，先不考虑密码加密的问题）
    private static final String PASSWORD = "root";
    //驱动信息
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    //数据库地址
    private static final String URL = "jdbc:mysql://localhost:3306/mydb";
    private Connection connection;
    private PreparedStatement pstmt;
    private ResultSet resultSet;

    
    public JdbcUtils() {
        try {
            Class.forName(DRIVER);
        } catch (Exception e) {
            throw new BusinessException(500, "获取数据库连接驱动异常！");
        }
    }

    
    public Connection getConnection() {
        try {
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            throw new BusinessException(500, "获取数据库连接异常！");
        }
        return connection;
    }


    
    public boolean insertOrUpdateOrDelete(String sql, List params) throws SQLException {
        boolean flag;
        int result = -1;
        pstmt = connection.prepareStatement(sql);
        int index = 1;
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        result = pstmt.executeUpdate();
        flag = result > 0 ? true : false;
        return flag;
    }

    
    public Map querySingle(String sql, List params) throws SQLException {
        Map map = new HashMap<>();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetmetaData metaData = resultSet.getmetaData();
        int colLen = metaData.getColumnCount();
        while (resultSet.next()) {
            for (int i = 0; i < colLen; i++) {
                String colName = metaData.getColumnName(i + 1);
                Object colValue = resultSet.getObject(colName);
                colValue = (colValue == null ? "" : colValue);
                map.put(colName, colValue);
            }
        }
        return map;
    }

    
    public List> queryList(String sql, List params) throws SQLException {
        List> list = new ArrayList<>();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (Object param : params) {
                pstmt.setObject(index++, param);
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetmetaData metaData = resultSet.getmetaData();
        int colLen = metaData.getColumnCount();
        while (resultSet.next()) {
            Map map = new HashMap<>();
            for (int i = 0; i < colLen; i++) {
                String colName = metaData.getColumnName(i + 1);
                Object colValue = resultSet.getObject(colName);
                colValue = (colValue == null ? "" : colValue);
                map.put(colName, colValue);
            }
            list.add(map);
        }

        return list;
    }

    
    public  T querySingle(String sql, List params,
                             Class cls) throws Exception {
        T resultObject = null;
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetmetaData metaData = resultSet.getmetaData();
        int colLen = metaData.getColumnCount();
        while (resultSet.next()) {
            // 通过反射机制创建一个实例
            resultObject = cls.newInstance();
            for (int i = 0; i < colLen; i++) {
                String colName = metaData.getColumnName(i + 1);
                Object colValue = resultSet.getObject(colName);
                colValue = (colValue == null ? "" : colValue);
                Field field = cls.getDeclaredField(colName);
                // 打开javabean的访问权限
                field.setAccessible(true);
                field.set(resultObject, colValue);
            }
        }
        return resultObject;

    }

    
    public  List queryList(String sql, List params,
                                 Class cls) throws Exception {
        List list = new ArrayList();
        int index = 1;
        pstmt = connection.prepareStatement(sql);
        if (params != null && !params.isEmpty()) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(index++, params.get(i));
            }
        }
        resultSet = pstmt.executeQuery();
        ResultSetmetaData metaData = resultSet.getmetaData();
        int colLen = metaData.getColumnCount();
        while (resultSet.next()) {
            //通过反射机制创建一个实例
            T resultObject = cls.newInstance();
            for (int i = 0; i < colLen; i++) {
                String colName = metaData.getColumnName(i + 1);
                Object colValue = resultSet.getObject(colName);
                colValue = (colValue == null ? "" : colValue);
                Field field = cls.getDeclaredField(colName);
                //打开javabean的访问权限
                field.setAccessible(true);
                field.set(resultObject, colValue);
            }
            list.add(resultObject);
        }
        return list;
    }

    
    public void close() {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new BusinessException(500, "关闭数据库连接异常！");
            }
        }
    }
}
 

其中业务异常类BusinessException
 

package com.grm.exception;


public class BusinessException extends RuntimeException {
    private int code;
    private String message;

    public BusinessException(int code, String message) {
        this.code = code;
        this.message = message;
    }

    // getter setter
}

 

Springboot怎么捕获全局BusinessException呢
 

package com.grm.handler;

import com.grm.common.Result;
import com.grm.exception.BusinessException;
import org.springframework.http.converter.HttpMessageNotReadableException;
import org.springframework.web.bind.MethodArgumentNotValidException;
import org.springframework.web.bind.MissingServletRequestParameterException;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.bind.annotation.RestControllerAdvice;

import javax.servlet.http.HttpServletRequest;
import java.util.StringJoiner;


@RestControllerAdvice(annotations = RestController.class)
public class GlobalExceptionHandler {
    
    @ExceptionHandler(value = Exception.class)
    public Result errorHandler(HttpServletRequest req, Exception e) {
        return Result.failed(500, e.getMessage());
    }

    
    @ExceptionHandler(value = BusinessException.class)
    public Result businessExceptionHandler(HttpServletRequest req, Exception e) {
        return Result.failed(500, e.getMessage());
    }
}

 

 
 

 
 

					
										


					

						
欢迎分享，转载请注明来源：内存溢出
原文地址: http://outofmemory.cn/zaji/5684881.html

					

				
								

										
										
										
										
										
									

								

					
						
							
						 赞
						(0)
					
					

						
							 打赏
						
							
								微信扫一扫 微信扫一扫
							
							
								支付宝扫一扫 支付宝扫一扫
							
						
					

				

				

					

						

							
								嘉立创
								嘉立创
								一级用户组
							
						

						

							

								
									
										
									
									0
								
								
									
										
									
									0
								
							

							
							

								
									
										
									
								
							

						

					

				

			
			
            

            	            

            
			

								

					
					

         				
							
								
							 上一篇
						
						2022-12-17
					

				

												

					
					

         				
							下一篇 
           						
							
						
						2022-12-17
					

				

							


			
			

				

					

						发表评论
					

										

						
						

							

								登录后才能评论
							

							
						

					

									

								
 评论列表（0条）

				
    
									

				
											

		

	

	





	


	

		

			

			

				
				

					

						Copyright © 2022 内存溢出 版权所有
						
							湘ICP备2022025235号						
						Powered by
						
							outofmemory.cn
						
					
					

				

			

			

				

					
					
						
						
					
					
						
					
				

			

		

	




























    
    

    

        
            保存