JDBC 操作数据库

JDBC 操作数据库

JDBCUtils.java

/* * Copyright (c) 2012-2022 mayi.com * All rights reserved. *  */package com.mayi.util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;/** * @author shenshouwei * */public final class JDBCUtils {private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";private static final String PORT = "3306";private static final String IP = "localhost";private static final String URL = "jdbc:mysql://"+IP+":"+PORT+"/new_mayi?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull";private static final String USER_NAME = "root";private static final String PASS_WORD = "123456";static {try {Class.forName(DRIVER_CLASS);} catch (ClassNotFoundException e) {e.printStackTrace();}}private JDBCUtils(){}/** * 获取 Connection 对象(默认链接:172.16.17.23(数据库)) * @return * @throws SQLException */public static Connection getConnection() throws SQLException{return DriverManager.getConnection(URL, USER_NAME, PASS_WORD);}/** * 获取 Connection 对象 * @return * @throws SQLException */public static Connection getConnection(String url,String userName,String passWord) throws SQLException{return DriverManager.getConnection(url, userName, passWord);}/** * 获取 PreparedStatement 对象 * @param conn * @param sql * @return * @throws SQLException */public static PreparedStatement getPrepareStatement(Connection conn,String sql) throws SQLException{return conn.prepareStatement(sql);}/** * 获取 ResultSet 对象 * @param ps * @return * @throws SQLException */public static ResultSet getResultSet(PreparedStatement ps) throws SQLException{return ps.executeQuery();}/** * 执行 update save delete * @param ps * @return * @throws SQLException */public static int execute(PreparedStatement ps) throws SQLException{return ps.executeUpdate();}/** * 关闭连接资源 * @param conn * @param ps * @param rs */public static void close(Connection conn,PreparedStatement ps,ResultSet rs){try {if(null != rs) rs.close();if(null != ps) ps.close();if(null != conn) conn.close();} catch (SQLException e) {e.printStackTrace();}}}

QueryDBUtils.java

package com.mayi.util;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;public class QueryDBUtils {private static String DB_COLUMN_DATE = "DATETIME";private QueryDBUtils(){}/** * 根据SQL 查询数据 <br> * @param sql <br> * @return List */public static List<Map<String,Object>> QuerySqlForList(String sql){List<Map<String,Object>> lists = new ArrayList<Map<String,Object>>();Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try{conn = JDBCUtils.getConnection();ps = JDBCUtils.getPrepareStatement(conn, sql);rs = JDBCUtils.getResultSet(ps);lists = getResultSetToList(rs);}catch(Exception e){e.printStackTrace();}finally{JDBCUtils.close(conn, ps, rs);}return lists;}/** * 更新、删除、添加 <br> * @param sql <br> * @return int 1:执行成功0:执行失败 */public static int executeSql(String sql){int result = 0;Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try{conn = JDBCUtils.getConnection();conn.setAutoCommit(false);ps = JDBCUtils.getPrepareStatement(conn, sql);result = JDBCUtils.execute(ps);conn.commit();}catch(Exception e){result = 0;e.printStackTrace();if(null!=conn){                 try {                     conn.rollback();                 } catch (SQLException e1) {                     e1.printStackTrace();                 }              }  }finally{JDBCUtils.close(conn, ps, rs);}return result;}/** * 批量添加、批量删除、批量删除 <br>  * @param sql * @param params * @return int 1:执行成功0:执行失败 */public static int executeBatch(String sql,List<List<Object>> params){int result = 0;Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try{if(null == sql || "".equals(sql.trim()) || null==params || params.size()<= 0) {System.out.println("参数不能为空！");return result;}conn = JDBCUtils.getConnection();conn.setAutoCommit(false);ps = JDBCUtils.getPrepareStatement(conn, sql);for(int i=0;i<params.size();i++){List<?> lists = params.get(i);for(int j=1;j<=lists.size();j++){ps.setObject(j, lists.get(j-1));}ps.addBatch();if(i%1000==0){ps.executeBatch();}}ps.executeBatch();conn.commit();result=1;}catch(Exception e){result = 0;e.printStackTrace();if(null!=conn){                 try {                     conn.rollback();                 } catch (SQLException e1) {                     e1.printStackTrace();                 }              }  }finally{JDBCUtils.close(conn, ps, rs);}return result;}/** * 根据 ResultSet 返回 list对象 * @param rs * @return * @throws SQLException * @throws ParseException  */private static List<Map<String,Object>> getResultSetToList(ResultSet rs) throws SQLException, ParseException{List<Map<String,Object>> lists = new ArrayList<Map<String,Object>>();ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据        int columnCount = md.getColumnCount();   //获得列数        int count = 1;        while (rs.next()) {              Map<String,Object> rowData = new HashMap<String,Object>();              for (int i = 1; i <= columnCount; i++) {              String columnName = md.getColumnName(i);            Object obj = rs.getObject(i);            String type =md.getColumnTypeName(i);            System.out.print((i==1?(count + "--->"):"") + columnName + "=" + obj + " 类型=" + type + "\t");            if(null!=obj && DB_COLUMN_DATE.equals(type.toUpperCase())){            String date = String.valueOf(obj).trim();            String format = "yyyy-MM-dd";            if(date.indexOf(":")!=-1){            format = "yyyy-MM-dd HH:mm:ss";            }            SimpleDateFormat sdf = new SimpleDateFormat(format);            Date d = sdf.parse(date);            rowData.put(columnName, sdf.format(d));              } else {            rowData.put(columnName, obj==null?"":obj);              }            }              lists.add(rowData);              count++;            System.out.println();        }return lists;}}