使用jdbc操作mysql数据库

下面是本人在工作写的一个使用jdck操作mysql数据库的工具类，希望对大家有所帮助，如有错误或不妥之处，欢迎大家留言指正！

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.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MysqlDBHelper {
	private static Connection conn = null;

	// 获得连接对象
	public static Connection getConn() {
		String sDriverName = "com.mysql.jdbc.Driver";
		String sDBUrl = "jdbc:mysql://127.0.0.1/phonebook";
		String userName = "root"; // 默认用户名
		String userPwd = "test"; // 密码
		try {
			Class.forName(sDriverName);
			conn = DriverManager.getConnection(sDBUrl, userName, userPwd);
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(e);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		return conn;
	}

	// 关闭连接
	public static void closeConn(Statement statement) {
		try {
			statement.close();
			statement = null;
			conn.close();
			conn = null;
		} catch (Exception e) {
			e.printStackTrace();
			conn = null;
		}
	}

	// 获得查询结果集
	public static List<Map<String, Object>> queryList(String sql, Object[] params) {
		getConn();
		PreparedStatement ps = null;
		List<Map<String, Object>> resultList = null;
		try {
			ps = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			resultList = resultSetConvertList(ps.executeQuery());
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		closeConn(ps);
		return resultList;
	}

	// 用于对表的增删改操作
	public static int update(String sql, Object[] params) {
		getConn();
		PreparedStatement ps = null;
		int updateResult = 0;
		try {
			ps = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i + 1, params[i]);
				}
			}
			updateResult = ps.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		closeConn(ps);
		return updateResult;
	}

	// ResultSet转换成List<Map<String, Object>>
	private static List<Map<String, Object>> resultSetConvertList(ResultSet resultSet) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		ResultSetMetaData rmd = resultSet.getMetaData();
		int columnCount = rmd.getColumnCount();
		while (resultSet.next()) {
			Map<String, Object> rowData = new HashMap<String, Object>();
			for (int i = 1; i <= columnCount; i++) {
				rowData.put(rmd.getColumnName(i), resultSet.getObject(i));
			}
			list.add(rowData);
		}
		return list;
	}
}

特别提醒大家，使用jdbc操作mysql数据还需要依赖mysql驱动包(mysql-connector-java-5.1.29.jar)。