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));
}
}