原生jdbc连数据库

package com.example.demo;

import org.apache.commons.lang.StringUtils;

import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ReadDB {
    public List<ArticleDTO> getMessage(String channelId) throws SQLException, ClassNotFoundException {
        List<ArticleDTO> list = new ArrayList<ArticleDTO>();

            List<AttachFileDTO> articleAttachmentsList = new ArrayList<>();
            List<ArticleChannelExtFieldDTO> extFieldsList =new ArrayList<>();
            List<AttachFileDTO> coverImagesList = new ArrayList<>();
            AttachFileDTO attachFileDTO = new AttachFileDTO();
            List<ArticleTagDTO> tagIdsList = new ArrayList<>();
            ArticleDTO dto = new ArticleDTO();
            String driver = "com.mysql.jdbc.Driver";
            // 数据库连接串
            String url = "jdbc:mysql://172.20.5.3:3308/nms?characterEncoding=UTF-8&useSSL=false";
            // 用户名
            String username = "liyanan";
            // 密码
            String password = "VeN814Coja";
            Connection conn = null;
            Statement stmt = null;
            Statement stmt1 = null;
            ResultSet rs1 = null;
            ResultSet rs2 = null;

        try {
            Class.forName(driver);
            // 2、获取数据库连接
            conn = DriverManager.getConnection(url, username, password);
            String name="新闻";
            // 4、定义操作的SQL语句
            String sqlM = "select moduleid from destoon_module where name ='"+name+"'";
            // 3、获取数据库操作对象
            stmt = conn.createStatement();
            rs1 = stmt.executeQuery(sqlM);
            Integer moduleId =null;
            while (rs1.next()) {
                moduleId = rs1.getInt("moduleid");
                System.out.println("moduleId为:" +moduleId);
            }
            String channel="社会";
            // 4、定义操作的SQL语句
            String sqlC = "select * from destoon_category where catname ='"+channel+"'and moduleid ='"+moduleId+"'";
            stmt = conn.createStatement();
            rs1 = stmt.executeQuery(sqlC);
            Integer catid =null;
            while (rs1.next()) {
                catid = rs1.getInt("catid");
                System.out.println("catid:" +catid);
            }
            //热点用 因为有两个热点
            //catid =241;
            String sqlD = "select * from destoon_article_"+moduleId+" where catid ='"+catid+"'";
            stmt = conn.createStatement();
            // 5、执行数据库操作
            rs1 = stmt.executeQuery(sqlD);
            // 6、获取并操作结果集
            while (rs1.next()) {
                dto = new ArticleDTO();
                String itemId = rs1.getString("itemId");
                String sql1 = "select * from destoon_article_data_"+moduleId+"  where itemid ="+itemId;
                stmt1 = conn.createStatement();
                rs2 = stmt1.executeQuery(sql1);
                while (rs2.next()){
                    dto.setContentBody(rs2.getString("content"));
                }
                dto.setArticleAttachmentsList(articleAttachmentsList);
                dto.setArticleAuthor(rs1.getString("author"));
                dto.setArticleOrigin(rs1.getString("copyfrom"));
                //dto.setArticleShowStyle(11);
                dto.setArticleStatus(11);
                dto.setArticleTitle(rs1.getString("title"));
                int isLink = rs1.getInt("islink");
                if(isLink == 1){
                    dto.setLinkTo(rs1.getString("linkurl"));
                    dto.setArticleType(5);
                }else {
                    dto.setArticleType(0);
                }
                dto.setChannelId(channelId);
                dto.setClickNum((long) rs1.getInt("hits"));
                dto.setContentTitle(dto.getArticleTitle());

                String thump =rs1.getString("thumb");
                if(!StringUtils.isBlank(thump)) {
                    attachFileDTO = new AttachFileDTO();
                    coverImagesList =  new ArrayList<>();
                    attachFileDTO.setUrl(rs1.getString("thumb"));
                    attachFileDTO.setCategory("IMG");
                    coverImagesList.add(attachFileDTO);
                    dto.setCoverImagesList(coverImagesList);
                }
                dto.setPublishTime(date2Str(new Date(),null));
                dto.setExtFieldsList(extFieldsList);
                dto.setHiddenFlag(0);
                dto.setSeoKeywords(rs1.getString("tag"));
                dto.setSeoDescription(rs1.getString("introduce"));
                dto.setSeqNo(0L);
                dto.setTagIdsList(tagIdsList);
                dto.setTopFlag(0);
                list.add(dto);

                System.out.println(rs1.getString("title"));
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }finally {
            if (conn!=null){
                conn.close();
            }
            if (stmt1!=null){
                stmt1.close();
            }
            if (stmt!=null){
                stmt.close();
            }
            if (rs2!=null){
                rs2.close();
            }

        }

        return list;
    }

    private static String DealContentBody(String contentBody) {
        String regex = "(https?|ftp|file)://[-A-Za-z0-9+&@#/%?=~_|!:,.;]+[-A-Za-z0-9+&@#/%=~_|]";
        //sb存放正则匹配的结果
        StringBuffer sb = new StringBuffer();
        //编译正则字符串
        Pattern p = Pattern.compile(regex);
        //利用正则去匹配
        Matcher matcher = p.matcher(contentBody);
        //如果找到了我们正则里要的东西
        while (matcher.find()) {
            //保存到sb中，"\r\n"表示找到一个放一行，就是换行
            sb.append(matcher.group() + "\r\n");
        }
        return sb.toString();
    }
    /**
     * date转化为格式化字符串
     *
     * @param date
     * @param formatStr 默认：yyyy-MM-dd HH:mm:ss
     * @return
     */
    public static String date2Str(Date date, String formatStr) {
        if (date == null) {
            return null;
        }
        if (StringUtils.isBlank(formatStr)) {
            formatStr = "yyyy-MM-dd HH:mm:ss";
        }
        SimpleDateFormat sdf = new SimpleDateFormat(formatStr);
        String str = sdf.format(date);
        return str;
    }

    public static void main(String[] args) {

        System.out.println(StringUtils.isBlank(null));
    }
}