package com.joey.jdbc;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.joey.jdbc.common.DbUtils;
import com.joey.jdbc.entity.News;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Properties;
import java.util.Scanner;

public class ProcessNews {

    public static void menu() {
        System.out.println("   欢迎来到新闻管理系统    ");
        System.out.println("========================");
        System.out.println("    1-添加新闻            ");
        System.out.println("    2-查看新闻            ");
        System.out.println("    3-编辑新闻            ");
        System.out.println("    4-删除新闻            ");
        System.out.println("    5-退出系统            ");
        System.out.println("  请输入1-5之间的数字      ");

    }

    public static void query() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        Properties properties = new Properties();
        String propertyFile = ProcessNews.class.getResource("/druid-config.properties").getPath();
        try {
            propertyFile = new URLDecoder().decode(propertyFile, "utf-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            conn = dataSource.getConnection();
            QueryRunner qr = new QueryRunner(dataSource);
            String sql = "select * from news";
            List<News> list = qr.query(sql, new BeanListHandler<>(News.class));
            for (int i = 0; i <list.size() ; i++) {
                System.out.println(list.get(i).toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DbUtils.closeConnection(null,pstmt,conn);
        }
    }


    public static void add() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入新闻标题");
        String title = sc.next();
        System.out.println("请输入新闻内容");
        String content = sc.next();
        System.out.println("请输入新闻日期：格式为yyyy-MM-dd");
        String strtime = sc.next();
        java.util.Date udtime = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            udtime = sdf.parse(strtime);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        long time = udtime.getTime();
        java.sql.Date sdtime = new java.sql.Date(time);


        Properties properties = new Properties();
        String propertyFile = ProcessNews.class.getResource("/druid-config.properties").getPath();
        try {
            propertyFile = new URLDecoder().decode(propertyFile, "utf-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            conn = dataSource.getConnection();
            try {
                conn.setAutoCommit(false);
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("getConnection出错");
            }
            QueryRunner qr = new QueryRunner(dataSource);

            String sql = "insert into news (title,content,creat_time) values" +
                    "(?,?,?)";
            qr.update(conn, sql, new Object[]{title, content, sdtime});
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            DbUtils.closeConnection(null,pstmt,conn);
        }
    }

    public static void update() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        System.out.println("请输入你要查询的新闻id");
        Scanner sc = new Scanner(System.in);
        Integer id = sc.nextInt();
        Properties properties = new Properties();
        String propertyFile = ProcessNews.class.getResource("/druid-config.properties").getPath();
        try {
            propertyFile = new URLDecoder().decode(propertyFile, "utf-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            conn = dataSource.getConnection();
            try {
                conn.setAutoCommit(false);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            QueryRunner qr = new QueryRunner(dataSource);
            String sql = "select * from news";
            List<News> list = qr.query(sql, new BeanListHandler<>(News.class));
            for (News n : list) {
                if (n.getId() == id) {
                    System.out.println("请输入新的新闻标题：");
                    String title = sc.next();
                    System.out.println("请输入新的新闻内容");
                    String content = sc.next();
                    String sql2 = "update news set title = ? ,content = ? where id = ?";
                    qr.update(conn, sql2, new Object[]{title, content, n.getId()});

                }
            }
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            DbUtils.closeConnection(null,pstmt,conn);
        }
    }


    public static void delete() {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入要删除的新闻编号：");
        Integer id = sc.nextInt();
        Connection conn = null;
        PreparedStatement pstmt = null;
        Properties properties = new Properties();
        String propertyFile = ProcessNews.class.getResource("/druid-config.properties").getPath();
        try {
            propertyFile = new URLDecoder().decode(propertyFile, "utf-8");
            properties.load(new FileInputStream(propertyFile));
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            conn = dataSource.getConnection();
            try {
                conn.setAutoCommit(false);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            QueryRunner qr = new QueryRunner(dataSource);
            String sql = "select * from news";
            List<News> list = qr.query(sql, new BeanListHandler<>(News.class));
            for (News n : list) {
                if (n.getId() == id) {
                    String sql2 = "delete from news where id = ?";
                    qr.update(conn, sql2, new Object[]{id});

                }
            }
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            DbUtils.closeConnection(null,pstmt,conn);
        }
    }


    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        Integer input  = 0;
        while (true) {
            ProcessNews.menu();
            try {
                input= sc.nextInt();
            } catch (Exception e) {
                e.printStackTrace();
                System.out.println("请输入1-5之间的整数");
                sc.next();
                continue;
            }
            if (input == 5) {
                System.out.println("已退出");
                break;
            }
            switch (input) {
                case 1:
                    ProcessNews.add();
                    break;
                case 2:
                    ProcessNews.query();
                    break;
                case 3:
                    ProcessNews.update();
                    break;
                case 4:
                    ProcessNews.delete();
                    break;
                default:
                    System.out.println("输入有误请重新输入(1-5)");
                    break;
            }

        }
    }
}

package com.joey.jdbc.entity;

public class News {
    private Integer id;
    private String title;
    private String content;
    private String creat_time;

    public News() {
    }

    public News(Integer id, String title, String content, String creat_time) {
        this.id = id;
        this.title = title;
        this.content = content;
        this.creat_time = creat_time;
    }

    @Override
    public String toString() {
        return "News{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", content='" + content + '\'' +
                ", creat_time='" + creat_time + '\'' +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public String getCreat_time() {
        return creat_time;
    }

    public void setCreat_time(String creat_time) {
        this.creat_time = creat_time;
    }
}