JAVA JDBC 常规增删改查简单封装

JAVA JDBC 常规增删改查简单封装,可满足大多基本要求

       作用：

        1， 查询列表是直接返回List<Map>对象，不必再遍历；

        2， 单条查询直接返回<Map>对象；

        3， 执行sql仅需一个方法搞定；

package com.Main.Tools;

import com.Main.DB.MySql.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description: JDBC辅佐工具类
 * @author: Adobe Chow
 * @date: 2018/12/29 16:02
 * @Copyright: www.winshang.com Inc. All rights reserved.
 */
public class SqlUtils {

    private static final String LogName = "SqlUtils----> ";

    /**
     * 执行sql
     * @param conn
     * @param sql
     * @param args
     * @throws SQLException
     */
    public static void execute(Connection conn,String sql,Object ...args) throws SQLException {
        PreparedStatement ptmt = null;
            ptmt = conn.prepareStatement(sql);

            String para = setParam(ptmt,args);
            System.out.println(LogName+"sql: "+sql);
            System.out.println(LogName+"参数: "+para);


            ptmt.execute();
            ptmt.close();

            System.out.println(LogName+"执行成功！");
    }


    /**
     * 查询列表
     * @param conn
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public static List<Map<String,Object>> findList(Connection conn,String sql,Object...args) {
        List<Map<String,Object>> list = new ArrayList<>();
        PreparedStatement ptmt = null;
        try {
            ptmt = conn.prepareStatement(sql);

            System.out.println(LogName+"sql: "+sql);

            String para = setParam(ptmt,args);
            System.out.println(LogName+"sql: "+sql);
            System.out.println(LogName+"参数: "+para);

            ResultSet resultSet = ptmt.executeQuery();
            ResultSetMetaData rsmd=resultSet.getMetaData();


            while (resultSet.next()){
                Map<String,Object> map = new HashMap<>();
                for(int i=0;i<rsmd.getColumnCount();i++){
                    String columnLabel=rsmd.getColumnLabel(i+1);
                    Object columnValue=resultSet.getObject(columnLabel);
                    map.put(columnLabel, columnValue);
                }
                list.add(map);
            }

            if (list.size()==0){
                return null;
            }

            return list;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                ptmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }



    /**
     * 查询单条数据
     * @param conn
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public static Map<String,Object> findFirst(Connection conn,String sql,Object...args)  {
        List<Map<String,Object>> list = findList(conn,sql+" limit 1",args);
        return list==null?null:list.get(0);
    }



    /**
     * 设置参数
     * @param ptmt
     * @param args
     * @return
     */
    private static String setParam(PreparedStatement ptmt, Object[] args) throws SQLException {
        //加入参数
        String parasStr = "";
        if (args!=null){
            for(int i=0;i<args.length;i++){
                ptmt.setObject(i+1, args[i]);
                parasStr=parasStr+"  "+args[i];
            }
        }
        return parasStr;
    }


    public static void main(String[] args)  {
        //执行sql
        String sql = "INSERT INTO `biz_t_error` ( `uid`, `bunkId`, `errType`, `errMsg`, `errUrl`, `addtime`, `errSql`) VALUES (?, '3250', '匹配错误', '测试插入啦啦啦！', 'http://data.winshang.com/Pipei_Pp.aspx', ?, 'zdb 测试数据');";
        try {
            execute(DBUtil.getConnection(),sql,"1314",new Date());
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //查询列表
        List<Map<String,Object>> list = findList(DBUtil.getConnection(),"select * from biz_t_error LIMIT 100",null);
        for (int i=0;i<list.size();i++){
            System.out.println("多条记录："+list.get(i).get("errMsg"));
        }

        //查询单条记录
        Map<String,Object> map = findFirst(DBUtil.getConnection(),"select * from biz_t_error order by addtime desc",null);
        System.out.println( "单条记录： "+ map.get("uid")+" "+map.get("errMsg") );
    }

}