BaseJDBC工具类

BaseJDBC工具类，将结果集转为String[],Map，Bean的List，其中转为Bean的List使用了反射与泛型，实现的代码的通用性。

执行时会打印sql语句与参数，便于发现错误。

package util;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jdk.nashorn.internal.ir.Flags;

public class BaseJDBC {
	
	private Connection connection;
	private static final String DRIVER="com.mysql.jdbc.Driver";
	private static final String URL="jdbc:mysql://localhost:3306/test";
	private static final String ROOT="root";
	private static final String PASSWORD="mysql";
	
	
	public BaseJDBC(){
		
		try {
			
			Class.forName(DRIVER);
			connection=DriverManager.getConnection(URL,ROOT,PASSWORD);
			connection.setAutoCommit(false);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	
	public ResultSet query(String sql,String[] parameters){
		
		try {
			
			if (connection.isClosed()) {
				connection=DriverManager.getConnection(URL,ROOT,PASSWORD);
				connection.setAutoCommit(false);
			}
			
			PreparedStatement preparedStatement=connection.prepareStatement(sql);
			System.out.println("sql -----> "+sql);
			if (preparedStatement!=null) {
				for (int i = 0; i < parameters.length; i++) {
					System.out.println("parameters -----> "+i+" --> "+parameters[i]);
					preparedStatement.setString(i+1, parameters[i]);
				}
			}
			ResultSet resultSet=preparedStatement.executeQuery();
			
			return resultSet;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	
	public List<String[]> queryToListArray(String sql,String[] parameters){
		
		List<String[]> list=new ArrayList<>();
		ResultSet resultSet=query(sql, parameters);
		
		try {
			
			while(resultSet.next()){
				ResultSetMetaData metaData=resultSet.getMetaData();
				int columnCount=metaData.getColumnCount();
				String[] strs=new String[columnCount];
				
				for (int i = 0; i < columnCount; i++) {
					String string=resultSet.getString(i+1);
					strs[i]=string;
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	public List<Map<String, String>> queryToListMap(String sql, String[] parameters){
		
		List<Map<String, String>> list=new ArrayList<>();
		ResultSet resultSet=query(sql, parameters);
		Map<String, String> map = new HashMap<>();
		try {
			
			while(resultSet.next()){
				ResultSetMetaData metaData=resultSet.getMetaData();
				int columnCount = metaData.getColumnCount();
				for(int i=0; i<columnCount; i++) {
					map.put(metaData.getColumnLabel(i+1), resultSet.getString(i+1));
				}
				list.add(map);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	public <T> List<T> queryToListBean(String sql,String[] parameters,Class<T> clazz){
		List<T> list=new ArrayList<T>();
		ResultSet resultSet=query(sql, parameters);
		try {			
			
			while(resultSet.next()){
				T newinstance=clazz.newInstance();
				ResultSetMetaData metaData=resultSet.getMetaData();
				int columnCount=metaData.getColumnCount();
				for (int i = 0; i < columnCount; i++) {
					
					String columnLabel = metaData.getColumnLabel(i+1);
					String columnName="";
					
					String[] columnLabelSplit=columnLabel.split("_");
					for (String split : columnLabelSplit) {
						columnName+=split.substring(0,1).toUpperCase()+split.substring(1);
					}
					
					
					Field field=null;
					try {
						
						field=clazz.getDeclaredField(columnName.substring(0,1).toLowerCase()+columnName.substring(1));
						
					} catch (NoSuchFieldException e) {
						e.printStackTrace();
						continue;
					} catch (SecurityException e) {
						e.printStackTrace();
						continue;
					}
					
					
					String methodName="set"+columnName;
					
					try {
						
						Method method = clazz.getMethod(methodName, field.getType());
						method.invoke(newinstance, resultSet.getString(i+1));
						
					} catch (NoSuchMethodException e) {
						e.printStackTrace();
						continue;
					} catch (SecurityException e) {
						e.printStackTrace();
						continue;
					} catch (IllegalArgumentException e) {
						e.printStackTrace();
						continue;
					} catch (InvocationTargetException e) {
						e.printStackTrace();
						continue;
					}
									
					
				}
				
				list.add(newinstance);
			}
						
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
		
	}
	
	public int execute(String sql,String[] parameters){
		
		try {
			
			if (connection.isClosed()) {
				connection=DriverManager.getConnection(URL,ROOT,PASSWORD);
				connection.setAutoCommit(false);
			}
			
			PreparedStatement preparedStatement=connection.prepareStatement(sql);
			System.out.println("sql -----> "+sql);
			if (parameters!=null) {
				for (int i = 0; i < parameters.length; i++) {
					System.out.println("parameters ----->"+i+" --> "+parameters[i] );
					preparedStatement.setString(i+1, parameters[i]);
				}
			}
			
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return 0;
		
	}
	
	public boolean commit(){
		
		try {
			connection.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
		
	}
	
	public boolean rollback(){
		
		try {			
			connection.rollback();			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public boolean close(){
		
		try {			
			connection.close();			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	
}