package DAL;
import com.mysql.jdbc.StatementImpl;
import java.sql.*;

public class SqlHelper {
    public static Connection getConnection() {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/servletdata?useSSL=true&characterEncoding=utf-8&user=root&password=");
            System.out.println("数据库连接成功！");
            System.out.println("-------------------------------");
            return connection;
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("创建连接失败！");
        System.out.println("-------------------------------");
        return null;
    }
    //select
    public static ResultSet executeQuery(String SQL)
    {
        try
        {
            Connection conn=getConnection();
            Statement stmt=conn.createStatement();
            ResultSet rs=stmt.executeQuery(SQL);
            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("查询失败！");
            System.out.println("-------------------------------");
            return null;
        }
    }
    //insert update delete
    public static boolean executeUpdate(String SQL)
    {
        try
        {
            Connection conn=getConnection();
            Statement stmt=conn.createStatement();
            int rs=stmt.executeUpdate(SQL);
            if (rs>0)
                return true;
            else
                return false;
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("更新失败！");
            System.out.println("-------------------------------");
            return false;
        }
    }
    public static void ShutDown(StatementImpl statement, Connection connection) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }
}

package Entity;

import java.sql.Date;

public class News {
    private int idnews;
    private String category;
    private String title;
    private String content;
    private String author;
    private Date newsdate;

    public int getIdnews() {
        return idnews;
    }
    public void setIdnews(int idnews) {
        this.idnews = idnews;
    }
    public String getCategory() {
        return category;
    }
    public void setCategory(String category) {
        this.category = category;
    }
    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 getAuthor() {
        return author;
    }
    public void setAuthor(String author) {
        this.author = author;
    }
    public Date getNewsdate() {
        return newsdate;
    }
    public void setNewsdate(Date newsdate) {
        this.newsdate = newsdate;
    }

}

package Entity;

public class Users {
    String name="";//用户名
    String password="";//用户密码

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
}

package Service;

import DAL.SqlHelper;
import Entity.News;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NewsService {
    public void AddNews(){}
    public void DeleteNews(){}
    public void UpdateNews(){}
    public List<News> QueryNews() throws SQLException {
        String sql="Select * from news";
        ResultSet rs= SqlHelper.executeQuery(sql);
        List<News> lsNews=new ArrayList<News>();
        while (rs.next())
        {
            News news=new News();
            news.setIdnews(rs.getInt(1));
            news.setCategory(rs.getString(2));
            news.setTitle(rs.getString(3));
            news.setContent(rs.getString(4));
            news.setAuthor(rs.getString(5));
            news.setNewsdate(rs.getDate(6));
            lsNews.add(news);
        }
        return lsNews;
    }
    public void GetNews(){}
}

package Service;

import DAL.SqlHelper;
import Entity.News;
import Entity.Users;
import com.mysql.jdbc.PreparedStatement;

import java.sql.ResultSet;
import java.sql.SQLException;

public class UsersService {
    public void AddUsers(){}
    public void DeleteUsers(){}
    public void UpdateUsers(){}
    public boolean QueryUsers(Users user) throws SQLException {
        //验证用户名存在性
        PreparedStatement pstmt;
        String sql = "select * from  users where username=‘"+user.getName()+"‘ and password=‘"+user.getPassword()+"‘";
        ResultSet rs= SqlHelper.executeQuery(sql);
        if (rs.next()) {
            return true;
        }
        return false;
    }
    public void GetUsers(){}
}