Java---一个简单的JdbcUtil

一个简单的JdbcUtil，仅包含insertOrUpdate，selectSqlToList，selectSqlToMap方法，可实现简单的增删改查。查询可以根据字段顺序包装成List或者根据字段名称作为键包装成Map。仅是方便快捷使用，直接写个main方法就可以使用。不建议用于生产，因为没有考虑事务，没有做连接池，效率也不大高。

一、JdbcUtil

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;

/**
 * 
 * @author LAN
 * @date 2020年3月19日
 */
public final class JdbcUtil {

	static final String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/my_test?useUnicode=true"
			+ "&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=false";
	static final String userName = "root";
	static final String password = "root";
	
	private static Connection getConn(){
		return getConn0();
	}
	
	private static void releaseConn(Connection conn){
		try {conn.close();} catch (SQLException e) {e.printStackTrace();}
	}
	
	private static Connection getConn0(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(jdbcUrl, userName, password);
			return conn;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException("未找到JDBC驱动",e);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("获取数据库连接失败",e);
		}
	}
	
	/**
	 * 执行sql，根据字段顺序返回表记录,每条记录包装成List<Object>，不包含字段名称
	 * @author LAN
	 * @date 2020年3月25日
	 * @param sql
	 * @param param
	 * @return
	 */
	public static List<List<Object>> selectSqlToList(String sql, Object... param){
		sql = sql.trim();
		if(!"select".equals(sql.substring(0, 6).toLowerCase())){
			throw new RuntimeException("查询必须以select开始");
		}
		Connection conn = getConn();
		try {
			PreparedStatement pstm = conn.prepareStatement(sql);
			if(param!=null){
				for (int i = 0; i < param.length; i++) {
					pstm.setObject(i + 1, param[i]);
	            }
			}
			ResultSet rs = pstm.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			List<List<Object>> list = new ArrayList();
			while (rs.next()) {
				List<Object> row = new ArrayList();
				for(int i=1; i<=columnCount; i++){
					Object cell = rs.getObject(i);
					row.add(cell);
				}
				list.add(row);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("查询数据库失败", e);
		} finally {
			releaseConn(conn);
		}
	}
	
	/**
	 * 执行sql，根据字段名称包装成Map<String,Object>,键是字段名称
	 * @author LAN
	 * @date 2020年3月25日
	 * @param sql
	 * @param param
	 * @return
	 */
	public static List<Map<String,Object>> selectSqlToMap(String sql, Object... param){
		sql = sql.trim();
		if(!"select".equals(sql.substring(0, 6).toLowerCase())){
			throw new RuntimeException("查询必须以select开始");
		}
		Connection conn = getConn();
		try {
			PreparedStatement pstm = conn.prepareStatement(sql);
			if(param!=null){
				for (int i = 0; i < param.length; i++) {
					pstm.setObject(i + 1, param[i]);
	            }
			}
			ResultSet rs = pstm.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();
			int columnCount = metaData.getColumnCount();
			List<Map<String,Object>> list = new ArrayList();
			while (rs.next()) {
				Map<String, Object> row = new HashMap();
				for(int i=1; i<=columnCount; i++){
					String columnName = metaData.getColumnName(i);
					Object cell = rs.getObject(i);
					row.put(columnName, cell);
				}
				list.add(row);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("查询数据库失败", e);
		} finally {
			releaseConn(conn);
		}
	}
	
	/**
	 * 增删改操作
	 * @author LAN
	 * @date 2020年3月25日
	 * @param sql
	 * @param param
	 * @return
	 */
	public static int insertOrUpdate(String sql, Object... param){
		Connection conn = getConn();
		try {
			PreparedStatement pstm = conn.prepareStatement(sql);
			if(param!=null){
				for (int i = 0; i < param.length; i++) {
					pstm.setObject(i + 1, param[i]);
	            }
			}
			boolean execute = pstm.execute();
			int updateCount = pstm.getUpdateCount();
			return updateCount;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("操作数据库失败", e);
		} finally {
			releaseConn(conn);
		}
	}
	
}

二、测试代码

import java.util.List;
import java.util.Map;

public class Main {
	public static void main(String[] args) {
		//单条插入数据
		String sql = "insert into t_customer (mobile,name,age,remark,status) values (?, ?, ?, ?, ?)";
		JdbcUtil.insertOrUpdate(sql, "17777077707", "黄一芝", 23, "聪明人", "01");
		JdbcUtil.insertOrUpdate(sql, "17777077708", "黄一芝", 24, "漂亮", "01");
		int c = JdbcUtil.insertOrUpdate(sql, "81570413", "林平之", 21, "辟邪剑谱", "02");
		System.out.println(c);
		//更新数据
		sql = "update t_customer set remark=? where name=?";
		c = JdbcUtil.insertOrUpdate(sql, "老实人", "黄一芝");
		System.out.println(c);
		//删除数据
		sql = "delete from t_customer where name=? and age=?";
		c = JdbcUtil.insertOrUpdate(sql, "黄一芝", 24);
		//查询数据每条包装成List<Object>
		sql = "select * from t_customer limit ? ";
		List<List<Object>> list1 = JdbcUtil.selectSqlToList(sql, 10);
		for(List<Object> row : list1){
			StringBuilder sbr = new StringBuilder();
			for(Object cell:row){
				sbr.append(cell+",");
			}
			System.out.println(sbr);
		}
		//查询每条包装成Map<String, Object>
		List<Map<String, Object>> list2 = JdbcUtil.selectSqlToMap(sql, 10);
		for(Map<String, Object> row : list2){
			System.out.println(row);
		}
	}
}

三、结果

 

 

author：蓝何忠

email：lanhezhong@163.com