Java 简单的复习下JDBC 工具类-白红宇

Java 简单的复习下JDBC 工具类

阅读量：2385 次

发布时间：2019-05-10

本文共 4801 字，大约阅读时间需要 16 分钟。


           
               
    
     com.microsoft.sqlserver
                
    
     mssql-jdbc
                
    
     6.2.0.jre8
            
           
               
    
     com.microsoft.sqlserver
                
    
     sqljdbc4
                
    
     4.2

引入相关依赖之后，编写工具类（现在框架都将这个封装进去了，简单的瞎写了下，就当熟悉）

@Slf4jpublic class JdbcUtil {    public static boolean testConnection(AppProperties appProperties){        Connection conn = null;        try {            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");            //注意url中没有microsoft才能使用，网上增加了microsoft的会出现No suitable driver found for//jdbc:microsoft:sqlserver:……的错误            //若安装多个实例时，要确认相应的port及实例名            String url="jdbc:sqlserver://"+appProperties.getDbsrc()+":"+appProperties.getDbport()+";DatabaseName="+appProperties.getDbname();            String user=appProperties.getDbuser();            String password=appProperties.getDbpwd();            conn= DriverManager.getConnection(url,user,password);            log.info("数据库连接成功！");            return true;        } catch (Exception e) {            // TODO Auto-generated catch block            // e.printStackTrace();        }finally {            if(conn!=null) {                try {                    conn.close();                } catch (Exception e) {                    // TODO Auto-generated catch block                    //e.printStackTrace();                }                conn = null;            }        }        return false;    }/** 获取链接 */    public static Connection getConn() {        return conn;    }     /** 关闭链接,释放资源 */    public static void close() {        try {            if (rs != null) {                rs.close();                rs = null;            }            if (stmt != null) {                stmt.close();                stmt = null;            }             if (conn != null) {                conn.close();                conn = null;            }        } catch (SQLException e) {            System.err.println("资源释放发生异常");        }    }     /**     * 获取指定数据库下所有的表名     * @param dbNm     * @return     */    public static List
   
     getAllTableName(String dbNm) {        List
    
      result = new ArrayList
     
      ();        Statement st = null;        try {            st = conn.createStatement();            ResultSet rs = st.executeQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA='" + dbNm + "'");            while (rs.next()) {                result.add(rs.getString(1));            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            if (st != null) {                try {                    st.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }            close();        }        return result;    }     /** 执行SQL返回ResultSet */    public static ResultSet executeSql(String sql, Object... args) {        try {//            System.out.println("准备执行SQL : \n" + sql);            stmt = conn.prepareStatement(sql);            if (null != args && args.length != 0) {                for (int i = 0; i < args.length; i++) {                    stmt.setObject(i + 1, args[i]);                }            }             rs = stmt.executeQuery();        } catch (SQLException e) {            System.err.println("数据查询异常");            e.printStackTrace();        }        return rs;    }     /**     * @title 查询数据结果 , 并封装为对象     * @author Xingbz     */    private static 
      
        T excuteQuery(Class
       
         klass, String sql, Object... args) {        try {            rs = executeSql(sql, args);            ResultSetMetaData metaData = rs.getMetaData();             Map
        
          resultMap = new HashMap<>(); if (rs.next()) { for (int i = 1; i <= metaData.getColumnCount(); i++) { String columnname = metaData.getColumnLabel(i); Object obj = rs.getObject(i); resultMap.put(columnname, obj); } } return JSON.parseObject(JSON.toJSONString(resultMap), klass); } catch (Exception e) { System.err.println("数据查询异常"); e.printStackTrace(); } finally { close(); } return JSON.toJavaObject(new JSONObject(), klass); } /** * @title 查询数据结果 , 并封装为List * @author Xingbz */ private static 
         
           List
          
            excuteQueryToList(Class
           
             klass, String sql, Object... args) { try { rs = executeSql(sql, args); List
            
             
              > resultList = new ArrayList<>(); Map
              
                resultMap = new HashMap<>(); while (rs.next()) { ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); for (int i = 1; i <= columnCount; i++) { resultMap.put(metaData.getColumnName(i), rs.getString(i)); } resultList.add(resultMap); } return JSON.parseArray(JSON.toJSONString(resultList), klass); } catch (Exception e) { System.err.println("数据查询异常"); e.printStackTrace(); } finally { close(); } return JSON.parseArray("[]", klass); }}