JDBC连接MySQL
加载及注册JDBC驱动程序
Class.forName("com.mysql.jdbc.Driver");Class.forName("com.mysql.jdbc.Driver").newInstance();
JDBC URL 定义驱动程序与数据源之间的连接
标准语法：
<protocol（主要通讯协议）>:<subprotocol（次要通讯协议，即驱动程序名称）>:<data source identifier（数据源）>
MySQL的JDBC URL格式：
jdbc:mysql//[hostname][:port]/[dbname][?param1=value1][¶m2=value2]….
示例：
常见参数：
user 用户名password 密码autoReconnect 联机失败，是否重新联机（true/false）maxReconnect尝试重新联机次数initialTimeout 尝试重新联机间隔maxRows传回最大行数useUnicode 是否使用Unicode字体编码（true/false）characterEncoding何种编码（GB2312/UTF-8/…）relaxAutocommit 是否自动提交（true/false）capitalizeTypeNames 数据定义的名称以大写表示
package mysqlmanage;import datastructures.QueueArray;import java.sql.SQLException;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import java.util.Map;import tools.Time;/*** 执行数据库操作之前的业务逻辑 主要执行jdbcUtils类中的命令** @author timeless <834916321@>* 2.26*/public class ExecCmd {/*** 获取 mysql 连接** @return JdbcUtils对象*/public static JdbcUtils getJdbcUtil() {//数据库用户名String USERNAME = "root";//数据库密码String PASSWORD = "xxxxxx";//驱动信息String URL = "jdbc:mysql://localhost:3306/mxManage";JdbcUtils jdbcUtils = new JdbcUtils(USERNAME, PASSWORD, URL);jdbcUtils.getConnection();return jdbcUtils;}/*** 执行更新操作 前提是 sql 语句中的数值不为空** @param sql 要执行的 insert 或者 update 语句*/public static void updateInsertData(String sql, JdbcUtils jdbcUtils) {try {System.out.println(sql);jdbcUtils.updateByPreparedStatement(sql, null);} catch (Exception ex) {System.out.println("sql 语句问题：语句为" + sql + "异常为：" + ex.toString());} finally {jdbcUtils.releaseConn();}}/*** 执行更新操作 前提是 sql 语句中的数值不为空** @param sql 要执行的 insert 或者 update 语句* @param table 批量出入的表* @param field 数据表格中的字段 格式为：示例 (`id`, `name`) 每一个字段都需要写上*/public static String formInsertSql(ArrayList<String> list, String table, String field) {if (list.size() == 0) {return null;}String sql = "INSERT INTO `" + table + "`" + field + " VALUES ";//(null,'dsadsad'),(null,'dss')try {for (String perdomain : list) {sql = sql + "(null,'" + perdomain + "'),";}//把最后一个字符串","去掉int end = sql.length();sql = sql.substring(0, end - 1);} catch (Exception ex) {System.out.println("形成sql语句问题" + ex.toString());}return sql;}}
package mysqlmanage;import java.lang.reflect.Field;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;public class JdbcUtils {//数据库用户名 private String USERNAME = "";//数据库密码 private String PASSWORD = "";//驱动信息 private String DRIVER = "com.mysql.jdbc.Driver";//数据库地址 private String URL = "";private Connection connection;private PreparedStatement pstmt;private ResultSet resultSet;public JdbcUtils(String username, String password, String url) {// TODO Auto-generated constructor stub this.USERNAME = username;this.PASSWORD = password;this.URL = url;try {Class.forName(DRIVER);} catch (Exception ex) {System.out.println("数据库连接失败！" + ex.toString());}}/*** 获得数据库的连接** @return*/public Connection getConnection() {try {connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);} catch (SQLException e) {// TODO Auto-generated catch block e.printStackTrace();}return connection;}/*** 增加、删除、改** @param sql* @param params* @return* @throws SQLException*/public boolean updateByPreparedStatement(String sql, List<Object> params) throws SQLException {boolean flag = false;int result = -1;pstmt = connection.prepareStatement(sql);int index = 1;if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {pstmt.setObject(index++, params.get(i));}}result = pstmt.executeUpdate();flag = result > 0 ? true : false;return flag;}/*** 查询单条记录** @param sql* @param params* @return* @throws SQLException*/public Map<String, Object> findSimpleResult(String sql, List<Object> params) throws SQLException {Map<String, Object> map = new HashMap<String, Object>();int index = 1;pstmt = connection.prepareStatement(sql);if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {pstmt.setObject(index++, params.get(i));}}resultSet = pstmt.executeQuery();//返回查询结果 ResultSetMetaData metaData = resultSet.getMetaData();int col_len = metaData.getColumnCount();while (resultSet.next()) {for (int i = 0; i < col_len; i++) {String cols_name = metaData.getColumnLabel(i + 1);Object cols_value = resultSet.getObject(cols_name);if (cols_value == null) {cols_value = "";}map.put(cols_name, cols_value);}}return map;}/*** 查询单条记录** @param sql* @param params* @return 是不是含有某条记录* @throws SQLException*/public boolean verifyDataIsInDatabase(String sql, List<Object> params) throws SQLException {boolean status = false;int index = 1;pstmt = connection.prepareStatement(sql);if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {pstmt.setObject(index++, params.get(i));}}resultSet = pstmt.executeQuery();//返回查询结果 if (resultSet.next()) {status = true;}return status;}/*** 查询多条记录** @param sql* @param params* @return * @throws SQLException*/public List<Map<String, Object>> findModeResult(String sql, List<Object> params) throws SQLException {List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();int index = 1;pstmt = connection.prepareStatement(sql);if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {pstmt.setObject(index++, params.get(i));}}resultSet = pstmt.executeQuery();ResultSetMetaData metaData = resultSet.getMetaData();int cols_len = metaData.getColumnCount();while (resultSet.next()) {Map<String, Object> map = new HashMap<String, Object>();for (int i = 0; i < cols_len; i++) {String cols_name = metaData.getColumnLabel(i + 1);Object cols_value = resultSet.getObject(cols_name);if (cols_value == null) {cols_value = "";}map.put(cols_name, cols_value);}list.add(map);}return list;}/*** 通过反射机制查询单条记录* * @param sql* @param params* @param cls* @return* @throws Exception*/public <T> T findSimpleRefResult(String sql, List<Object> params, Class<T> cls) throws Exception {T resultObject = null;int index = 1;pstmt = connection.prepareStatement(sql);if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {pstmt.setObject(index++, params.get(i));}}resultSet = pstmt.executeQuery();ResultSetMetaData metaData = resultSet.getMetaData();int cols_len = metaData.getColumnCount();while (resultSet.next()) {// 通过反射机制创建一个实例 // cls.class;resultObject = cls.newInstance();for (int i = 0; i < cols_len; i++) {String cols_name = metaData.getColumnName(i + 1);Object cols_value = resultSet.getObject(cols_name);if (cols_value == null) {cols_value = "";}Field field = cls.getDeclaredField(cols_name);field.setAccessible(true); //打开javabean的访问权限 field.set(resultObject, cols_value);}}return resultObject;}/*** 通过反射机制查询多条记录** @param sql* @param params* @param cls* @return* @throws Exception*/public <T> List<T> findMoreRefResult(String sql, List<Object> params, Class<T> cls) throws Exception {List<T> list = new ArrayList<T>();int index = 1;pstmt = connection.prepareStatement(sql);if (params != null && !params.isEmpty()) {for (int i = 0; i < params.size(); i++) {pstmt.setObject(index++, params.get(i));}}resultSet = pstmt.executeQuery();ResultSetMetaData metaData = resultSet.getMetaData();int cols_len = metaData.getColumnCount();while (resultSet.next()) {//通过反射机制创建一个实例 T resultObject = cls.newInstance();for (int i = 0; i < cols_len; i++) {String cols_name = metaData.getColumnName(i + 1);Object cols_value = resultSet.getObject(cols_name);if (cols_value == null) {cols_value = "";}Field field = cls.getDeclaredField(cols_name);field.setAccessible(true); //打开javabean的访问权限 field.set(resultObject, cols_value);}list.add(resultObject);}return list;}/*** 释放数据库连接*/public void releaseConn() {if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}}}
