JDBC-Java连接Mysql工具类(JDBC工具类【全】)

前言

java JDBC工具类

---

1、pom文件加入mysql依赖

		<!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>

2、resources目录下编写config.properties配置文件 :

gjSass.jdbc.driverClassName = com.mysql.cj.jdbc.Driver
gjSass.jdbc.url = jdbc:mysql://172.0.0.1:3306/data_18_ind_poscal?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true
gjSass.jdbc.username = root
gjSass.jdbc.password = root@123

3、编写 GJMGJdbcTemplate 类

package com.springblade.positionCalculate.utils;

import lombok.extern.slf4j.Slf4j;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

/**
 * mysql-jdbc连接工具
 *
 */
@Slf4j
public class GJMGJdbcTemplate {
	private static String url;
	private static String username;
	private static String password;
	private static String driver;

	static {

		// 加载配置文件
		InputStream is = GJMGJdbcTemplate.class.getClassLoader().getResourceAsStream("config.properties");
		Properties pro = new Properties();
		try {
			pro.load(is);
			url = pro.getProperty("gjSass.jdbc.url");
			username = pro.getProperty("gjSass.jdbc.username");
			password = pro.getProperty("gjSass.jdbc.password");
			driver = pro.getProperty("gjSass.jdbc.driverClassName");

		} catch (IOException e) {
			e.printStackTrace();
		}
		// 注册驱动
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			log.info(e.getMessage(), e);
		}

	}

	public static Connection getConnection() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, username, password);
			// 把jdbc 链接 设置成 非自动提交
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
			log.info(e.getMessage(), e);
			log.info("获得链接失败!");
		}
		return conn;
	}

	public static void closeAll(Statement stmt, Connection conn) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();// 关闭
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void closeResAll(ResultSet rs, Statement stmt, Connection conn) {

		if (rs != null) {
			try {
				rs.close();// 关闭
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();// 关闭
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static List<Map<String, Object>> selectAll(String querysql) {
		List<Map<String, Object>> list = new ArrayList<>();
		long starttime = System.currentTimeMillis();
		Connection conn = getConnection();
		Statement statement = null;
		ResultSet rs = null;
		try {
			statement = conn.createStatement();
			rs = statement.executeQuery(querysql);
			ResultSetMetaData data = rs.getMetaData();
			int columnCount = data.getColumnCount();
			while (rs.next()) {
				int i = 1;
				Map<String, Object> map = new HashMap<String, Object>();
				while (i <= columnCount) {
					String name = data.getColumnName(i);
					// System.out.println(name + " : " + rs.getObject(i));
					if (rs.getObject(i) == null || "".equals(rs.getObject(i))) {
						map.put(name, "");
					} else {
						map.put(name, rs.getObject(i));
					}
					i++;
				}
				list.add(map);
			}
			log.info(" 执行成功一次,总使用时间：" + (System.currentTimeMillis() - starttime) + "ms");
		} catch (SQLException e) {
			e.printStackTrace();
			log.info(e.getMessage(), e);
		} finally {
			closeResAll(rs, statement, conn);
		}

		return list;
	}

	public static void main(String[] args) {
		String sql = "select * from table";
		List<Map<String, Object>> maps = selectAll(sql);
		JSONArray jsonArray = new JSONArray();
		jsonArray.addAll(maps);
		List<xxx> xxx = jsonArray.toJavaList(xxx.class);
	}


	/*public static void batchUpdate(String sql) {
		Connection conn = null;
		Statement ps = null;
		try {
			conn = getConnection();
			ps = conn.createStatement();
			int executeUpdate = ps.executeUpdate(sql);
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			log.error(e.getMessage(), e);
			try {
				conn.rollback();
			} catch (SQLException e1) {
				log.error(e1.getMessage(), e1);
			}
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				log.error(e.getMessage(), e);
			}
			try {
				conn.close();
			} catch (SQLException e) {
				log.error(e.getMessage(), e);
			}
		}
	}*/

}

---

总结

如果此篇文章有帮助到您, 希望打大佬们能关注、点赞、收藏、评论支持一波，非常感谢大家！
如果有不对的地方请指正!!!