Using JDBC technology to achieve rapid addition, deletion and modification (small framework)

Introduction:

First of all, it's probably just two lines of concise code in Java to complete the operation of the database (the primary version). To achieve this function, we need several essential jar packages, one is the druid of the database connection pool, the other is mysql-connector-java, and the other is the configuration file jdbc.properties, which will be provided at the end.

The first is the configuration file.

public class DBUtils {
	private static String driverClass;
	private static String url;
	private static String user;
	private static String password;
	/** Maximum number of active connections */
	private static int maxActive;
	/** Minimum number of idle connections */
	private static int minIdle;
	/** The longest time to wait for the connection to get. */
	private static long maxWait;
	/** Database connection pool */
	private static DruidDataSource dataSource;
}
/** Static initialization configuration information */
static {
	init();
}

/** configuration information */
public static void init(){
	try {
		Properties prop = new Properties();
		prop.load(new FileInputStream("src/jdbc.properties"));
		driverClass = prop.getProperty("driverClass");
		url = prop.getProperty("url");
		user = prop.getProperty("user");
		password = prop.getProperty("password");
		maxActive = Integer.parseInt(prop.getProperty("maxActive"));
		minIdle = Integer.parseInt(prop.getProperty("minIdle"));
		maxWait = Long.parseLong(prop.getProperty("maxWait"));
		// Create data and configure it
		dataSource = new DruidDataSource();
		dataSource.setDriverClassName(driverClass);
		dataSource.setUrl(url);
		dataSource.setUsername(user);
		dataSource.setPassword(password);
		dataSource.setMaxActive(maxActive);
		dataSource.setMinIdle(minIdle);
		dataSource.setMaxWait(maxWait);
	} catch (FileNotFoundException e) {
		e.printStackTrace();`Insert code slices here`
	} catch (IOException e) {
		e.printStackTrace();
	}
}

Get connection

public static synchronized Connection getConnection() {
		if (dataSource == null || dataSource.isClosed()) {
			init();
		}
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

Close resources and connection pools

/** close resource */
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
		try {
			if (resultSet != null)resultSet.close();
			if (statement != null)statement.close();
			if (connection != null)connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
/** Close connection pool */
public static synchronized void closePool() {
		if (dataSource != null) {
			dataSource.close();
		}
	}

Database add data

/** Required parameters: 1. Add object, add table name */
public static int Insert(Object obj, String tableName) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		Class c = obj.getClass();
		Field[] fields = c.getDeclaredFields();
		int size = fields.length;
		String ss = "";
		String name = "";
		for (int i = 0; i < size - 1; i++) {
			name = fields[i + 1].getName();
			ss = ss + name + ",";
		}
		ss = ss.substring(0, ss.length() - 1);
		String s = "";
		for (int i = 0; i < size - 1; i++) {
			s = s + "?,";
		}
		s = s.substring(0, s.length() - 1);
		String sql = "insert into " + tableName + " (" + ss + ") values(" + s + ")";
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement(sql);
			String fname = null;
			String getMethodName = null;
			Method getMethod = null;
			for (int i = 0; i < size - 1; i++) {
				fname = fields[i + 1].getName();
				getMethodName = "get" + fname.substring(0, 1).toUpperCase() + fname.substring(1);
				getMethod = c.getMethod(getMethodName);
				Object invoke = getMethod.invoke(obj);
				preparedStatement.setObject(i + 1, invoke);
			}
			int a = (int) preparedStatement.executeUpdate();
			return a;
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} finally {
			close(null, preparedStatement, connection);
		}
		return 0;
	}

Addition, deletion and modification of database

public static int exeUpdate(String sql, Connection connection, Object... params) {
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			if (Objects.nonNull(params)) {
				for (int i = 0; i < params.length; i++) {
					preparedStatement.setObject(i + 1, params[i]);
				}
			}
			int i = preparedStatement.executeUpdate();
			return i;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(null, preparedStatement, null);
		}
		return 0;
	}

General Query Set Operations

/** Encapsulation of general query set operations, for any form of query, can return a collection of objects */
public static <T> List<T> queryList(Class<T> t, String sql, Object... params) {
		// Declare empty sets
		List<T> data = new ArrayList<>();
		// Obtain query result information
		List<Map<String, Object>> list = getDataPair(sql, params);
		if (list.isEmpty()) {
			return data;
		}
		// Ergodic set
		for (Map<String, Object> map : list) {
			T obj = parseMapToBean(map, t);
			data.add(obj);
		}
		return data;
	}

/** A general query object operation can return a definite object for any form of query. */
public static <T> T queryOne(Class<T> t, String sql, Object... params) {
		List<Map<String, Object>> list = getDataPair(sql, params);
		if (!list.isEmpty()) {
			Map<String, Object> map = list.get(0);
			T obj = parseMapToBean(map, t);
			return obj;
		}
		return null;
	}

/** Converting a Map collection object to JavaBean and returning */
private static <T> T parseMapToBean(Map<String, Object> map, Class<T> t) {
		T obj = null;
		try {
			// Create an empty instance
			obj = t.newInstance();
			// Gets the key set of the Map collection (all column names, that is, the attribute names of the objects to be returned)
			Set<String> keys = map.keySet();
			for (String cname : keys) {
				// Getting attribute objects
				Field f = t.getDeclaredField(cname);
				// Get the name of the set method
				String setMethodName = "set" + cname.substring(0, 1).toUpperCase() + cname.substring(1);
				// Getting set Method Objects
				Method method = t.getMethod(setMethodName, f.getType());
				// Execution method
				method.invoke(obj, map.get(cname));
			}
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		return obj;
	}

/** Used to parse the specified query statement and return the acquired data (list, column value) as a collection */
private static List<Map<String, Object>> getDataPair(String sql, Object... params) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		// Declare the table data stored in the collection (column name, column value)
		List<Map<String, Object>> list = new ArrayList<>();
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement(sql);
			if (Objects.nonNull(params)) {
				for (int i = 0; i < params.length; i++) {
					preparedStatement.setObject(i + 1, params[i]);
				}
			}
			resultSet = preparedStatement.executeQuery();
			// Getting result set metadata
			ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
			// Get the total number of columns, get the column name, get the label name, get the column value, and store the relevant data into the Map collection
			// Get the total number of columns for the query
			int count = resultSetMetaData.getColumnCount();

			while (resultSet.next()) {
				// Traverse the result set once to get a data (that is, a Map object)
				Map<String, Object> map = new HashMap<>();
				// Traveling through each column
				for (int i = 1; i <= count; i++) {
					// Get the column name
					String columnName = resultSetMetaData.getColumnName(i);
					// Get the label name
					// String columnLabel = resultSetMetaData.getColumnLabel(i);
					// Get column values
					// Data can be stored in Map collections when column values are not null
					Object value = resultSet.getObject(i);
					if (Objects.nonNull(value)) {
						// Store data in Map
						map.put(columnName, value);
					}
				}
				// Save the Map collection into List
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(resultSet, preparedStatement, connection);
		}
		return list;
	}

Configuration file, jar cloud link
Extraction code: lu9x