mysql失败连接重试

最新推荐文章于 2023-12-27 15:20:42 发布

wppwpp1

最新推荐文章于 2023-12-27 15:20:42 发布

阅读量194

点赞数

分类专栏： java 文章标签： java

本文链接：https://blog.csdn.net/wppwpp1/article/details/124357414

版权

java 专栏收录该内容

39 篇文章 1 订阅

订阅专栏

该类实现了JDBC的数据库连接管理，包括初始化连接、重连机制、预编译SQL语句、查询结果处理以及事务操作。同时，包含了异常处理，如ClassNotFoundException、SQLException等，并提供了查询和更新数据的方法。

摘要由CSDN通过智能技术生成

package com.vip.dataeye.base.mysql;

import com.vip.dataeye.common.Constants;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * jdbc template
 * @author yanfeng.dong
 *
 */
public class JdbcBuilder {
	
	private Connection conn;
	private long startTime = System.currentTimeMillis(); 
	protected static Logger logger = LoggerFactory.getLogger(JdbcBuilder.class);
	protected String errorMsg = "jdbc connection error!!";
	protected String insertErrorMsg = "insert error msg!!!";
	protected String queryErrorMsg = "query error msg!!!";
	
	private int fetchSize = 10000;
	
	private String driver;
	private String url;
	private String userName;
	private String password;
	
	public JdbcBuilder(String driver,String url,String userName,String password){
		this.driver = driver;
		this.url = url;
		this.userName = userName;
		this.password = password;
		try {
			Class.forName(driver).newInstance();
			logger.info("connecting db....{}",url);
			conn= DriverManager.getConnection(url,userName,password);
		} catch (InstantiationException e) {
			logger.error(errorMsg,e);
		} catch (IllegalAccessException e) {
			logger.error(errorMsg,e);
		} catch (ClassNotFoundException e) {
			logger.error(errorMsg,e);
		} catch (SQLException e) {
			logger.error(errorMsg,e);
			for(int i=0;i<10;i++){
				try {
					Thread.sleep(10);
				} catch (InterruptedException e1) {
					logger.error(errorMsg, e);
				}
				try {
					conn = DriverManager.getConnection(url,userName,password);
					if(conn != null){
						break;
					}
				} catch (SQLException e1) {
					logger.error(errorMsg, e);
					//System.exit(-1);
				}
			}
		}
	}
	
	public void  reConn(){
		try {
			Class.forName(driver).newInstance();
			logger.info("connecting db....{}",url);
			conn= DriverManager.getConnection(url,userName,password);
		} catch (InstantiationException e) {
			logger.error(errorMsg,e);
		} catch (IllegalAccessException e) {
			logger.error(errorMsg,e);
		} catch (ClassNotFoundException e) {
			logger.error(errorMsg,e);
		} catch (SQLException e) {
			logger.error(errorMsg,e);
		}
	}
	
	public void  reConn(String driver,String url,String userName,String password){
		try {
			Class.forName(driver).newInstance();
			logger.info("connecting db....{}",url);
			conn= DriverManager.getConnection(url,userName,password);
		} catch (InstantiationException e) {
			logger.error(errorMsg,e);
		} catch (IllegalAccessException e) {
			logger.error(errorMsg,e);
		} catch (ClassNotFoundException e) {
			logger.error(errorMsg,e);
		} catch (SQLException e) {
			logger.error(errorMsg,e);
		}
	}
	
	
	/**
	 * create PreparedStatement;
	 * @param sql
	 * @return
	 */
	public PreparedStatement getPreStatement(String sql){
		
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			ps.setFetchSize(fetchSize);
			ps.setFetchDirection(ResultSet.FETCH_REVERSE);
		} catch (SQLException e) {
			logger.error("method:getPreStatement error:{}",e.getMessage());
			//System.exit(-1);
		}
		return ps;
	}
	
	/**
	 * query list
	 * @param sql
	 * @param args
	 * @return
	 */
	public List<String> getResultList(String sql,String[] args)throws MysqlConnectionOrSqlException{
		
		List<String> list = new ArrayList<String>();
		ResultSet resultSet = null;
		PreparedStatement ps = getPreStatement(sql);
		try {
			for(int i=0;i<args.length;i++){
				ps.setObject(i+1, args[i]);
			}
			resultSet = ps.executeQuery();
			resultSet.setFetchSize(fetchSize);
			list = OrderResultSetParser.parseResultSet(resultSet);
			return list;
		} catch (SQLException e) {
			logger.error("method:getResultList error:{}",e.getMessage());
			reConn(driver, url, userName, password);
			throw new MysqlConnectionOrSqlException("method:getResultList error");
		}finally{
			try {
				DbUtils.close(resultSet);
				DbUtils.close(ps);
			} catch (SQLException e) {
				logger.error("error:{}",e.getMessage());
			}
		}
	}
	
	
	/**
	 * resultSet to list<String>
	 * @return
	 */
    public ResultSetHandler<List<String>> getResultHandler(){
    	
    	ResultSetHandler<List<String>> resultHandler = new ResultSetHandler<List<String>>() {
    	    public List<String> handle(ResultSet rs) throws SQLException {
    	    	//rs.setFetchSize(500);
    	    	List<String> list = new ArrayList<String>();
    			StringBuilder sb = null;
    			try {
    				ResultSetMetaData meta = rs.getMetaData();
    				int columnCount = meta.getColumnCount();
    				while (rs.next()) {
    					sb = new StringBuilder();
    					for (int i = 1; i <= columnCount; i++) {
    						sb.append(rs.getString(i));
    						sb.append(Constants.splitTab);
    					}
    					list.add(sb.toString());
    				}
    			} catch (SQLException e) {
    				e.printStackTrace();
    				System.exit(-1);
    			} finally {
    				
    			}
    			return list;
    	    }
    	};
    	return resultHandler;
    }
	
	
	/**
	 * 得到查询结果。
	 * @param sql
	 * @param args
	 * @return
	 */
	public List<String> getListByResultSet(String sql,Object[] args){
		
		List<String> resultList = null;
		try{
			ResultSetHandler<List<String>> resultHandler = getResultHandler();
			QueryRunner runner = new QueryRunner();
			resultList = runner.query(conn, sql, resultHandler, args);
		}catch(SQLException e){
			reConn(driver, url, userName, password);
		}
		return resultList;
	}
	
	public void closeConnection(){
		try {
			DbUtils.close(conn);
		} catch (SQLException e) {
			logger.info("closeConnection error!!!",e);
		}
	}
	
	/**
	 * 插入数据。
	 * @param sql
	 * @param args
	 */
	public void saveList(String sql,Object[][] args) throws MysqlConnectionOrSqlException{
		QueryRunner runner = new QueryRunner();
		try {
			int[] result = runner.batch(conn,sql, args);
			logger.info("insert or update size:{}",result.length);
		} catch (SQLException e) {
			logger.error(insertErrorMsg, e);
			//e.printStackTrace();
			//reConn(driver, url, userName, password);
			throw new MysqlConnectionOrSqlException("method:saveList error");
		}
	}
	
	
	
	/**
	 * del数据。
	 * @param sql
	 * @param args
	 */
	public void deleteList(String sql,Object[][] args){
		QueryRunner runner = new QueryRunner();
		try {
			int[] result = runner.batch(conn,sql, args);
			logger.info("delete {}",result.length);
		} catch (SQLException e) {
			logger.error(insertErrorMsg, e);
			System.exit(-1);
		}
	}
	
	/**
	 * 插入数据之前将以前的删除。
	 * @param deleteSql
	 * @param deleteArgs
	 * @param sql
	 * @param args
	 */
	public void deleteAndInsertList(String deleteSql,Object[][] deleteArgs,String sql,Object[][] args){
		QueryRunner runner = new QueryRunner();
		try {
			int[] result = runner.batch(conn,deleteSql, deleteArgs);
			result = runner.batch(conn,sql, args);
			logger.info("delete {}",result.length);
		} catch (SQLException e) {
			logger.error(insertErrorMsg, e);
			System.exit(-1);
		}
	}


	public List<String[]> getResultArray(String sql,String[] args) throws MysqlConnectionOrSqlException{

		List<String[]> list = new ArrayList<String[]>();
		ResultSet resultSet = null;

		PreparedStatement ps = getPreStatement(sql);
		try {
			//给sql中的占位符(?)赋值，查询时不需要，主要在insert、update
			for(int i=0;i<args.length;i++){
				ps.setObject(i+1, args[i]);
			}
			resultSet = ps.executeQuery();
			resultSet.setFetchSize(fetchSize);
			list = OrderResultSetParser.parseResultArray(resultSet);
		} catch (Exception e) {
			logger.error("query with error", e);
			throw new MysqlConnectionOrSqlException("query data error");
		}finally {
			DbUtils.closeQuietly(conn, ps, resultSet);
		}
		return list;
	}


	
}

重试连接代码