000000

package util;

import com.csi.domain.Order;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class JDBCUtils {
    /*
    * 获取连接
    * */
    //构建对象
    Properties properties=new Properties();
    public Connection getConnection() throws SQLException {
        //通过类加载器找到classes目录，继而找到db.properties文件
        try {
            properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties"));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        //创建连接
        try {
            Class.forName(getValue("classname"));

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        //建立连接
        Connection connection=DriverManager.getConnection(getValue("url"),getValue("username"),getValue("password"));
        return connection;
    }

    public String getValue(String key){
        return properties.getProperty(key);
    }

    /*
    * 释放资源
    * */
    protected void release(Connection connection){
        try {
            if(connection !=null){
                connection.close();
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    protected void release(Statement st){
        try {
            if(st !=null){
                st.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    protected void release(ResultSet rs){
        try {
            if(rs !=null){
                rs.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    protected void release(PreparedStatement ps,Connection connection){
        release(ps);
        release(connection);
    }
    protected void release(ResultSet rs,PreparedStatement ps,Connection connection){
        release(rs);
        release(ps,connection);
    }

    public List<Order> findByScheuleId(long schedule_id) throws SQLException {
        final String sql = "select * from orderinfo where schedule_id = ?";
        Connection connection=this.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setLong(1,schedule_id);
        ResultSet rs = ps.executeQuery();
        List<Order> orderlist=new ArrayList<>();
        while (rs.next()){
            Order order=new Order();
            order.setOrder_id(rs.getString("order_id"));
            order.setUser_id(rs.getLong("user_id"));
            order.setSchedule_id(rs.getLong("schedule_id"));
            order.setOrder_position(rs.getString("order_position"));
            order.setOrder_state(rs.getInt("order_state"));
            order.setOrder_price(rs.getInt("order_price"));
            order.setOrder_time(rs.getTime("order_time"));
        }
        this.release(rs,ps,connection);
        return orderlist;
    }

    public int totalOrderByScheduleId(long schedule_id) throws SQLException {
        final String sql="SELECT COUNT(*) FROM orderinfo WHERE schedule_id=?";
        Connection connection=this.getConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setLong(1,schedule_id);
        ResultSet rs = ps.executeQuery();
        int count=0;
        if(rs.next()){
            count=rs.getInt(1);
        }
        this.release(rs,ps,connection);
        return count;
    }
}

package com.csi.dao.impl;

import com.csi.dao.UserDao;
import util.PageInfo;
import com.csi.domain.User;
import util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class UserDaoImpl extends JDBCUtils implements UserDao {
    @Override
    public User findUserById(long user_id) throws SQLException {
        final String SQL= "select * from user where user_id=?";
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement(SQL);

        ps.setLong(1,user_id);
        ResultSet rs = ps.executeQuery();
        User user = null;
        if(rs.next()) {
            user = new User();
            user.setUser_id(rs.getLong("user_id"));
            user.setUser_name(rs.getString("user_name"));
            user.setUser_pwd(rs.getString("user_pwd"));
            user.setUser_role(rs.getInt("user_role"));
            user.setUser_email(rs.getString("user_email"));
            user.setUser_headImg(rs.getString("user_headImg"));
        }
        release(rs,ps,connection);
        return user;
    }

    @Override
    public Integer addUser(User user) throws SQLException {
        final String SQL = "insert into user(user_name,user_pwd,user_email,user_role,user_headImg) values(?,?,?,?,?)";
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement(SQL);
        ps.setString(1,user.getUser_name());
        ps.setString(2,user.getUser_pwd());
        ps.setString(3,user.getUser_email());
        ps.setInt(4,user.getUser_role());
        ps.setString(5,user.getUser_headImg());
        int num = ps.executeUpdate();
        release(ps,connection);
        return num;
    }

    @Override
    public Integer updateUser(User user) throws SQLException {
        final String SQL = "update user set user_name=?,user_pwd=?, user_email=? , user_role=?, user_headImg=? where user_id=?";
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement(SQL);
        ps.setString(1,user.getUser_name());
        ps.setString(2,user.getUser_pwd());
        ps.setString(3,user.getUser_email());
        ps.setInt(4,user.getUser_role());
        ps.setString(5,user.getUser_headImg());
        ps.setLong(6,user.getUser_id());
        int num = ps.executeUpdate();
        release(ps,connection);
        return num;
    }

    @Override
    public Integer deleteUser(long user_id) throws SQLException {
        final String SQL = "delete from user where user_id = ? ";
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement(SQL);
        ps.setLong(1,user_id);
        int num = ps.executeUpdate();
        release(ps,connection);
        return num;
    }

    @Override
    public PageInfo<User> findAllUser(PageInfo pageInfo) throws SQLException {

        String sql1 = "select count(*)  from user ";
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement(sql1);
        ResultSet rs = ps.executeQuery();
        int ret = 0;
        if(rs.next()){
            ret = rs.getInt(1);
        }
        pageInfo.setTotalRecords(ret);
        String sql2="select * from user limit ?,?";
        ps = connection.prepareStatement(sql2);
        ps.setInt(1,(pageInfo.getCurrentPageNo()-1)*pageInfo.getPerPageNo());
        ps.setInt(2,pageInfo.getPerPageNo());
        rs = ps.executeQuery();
        Set<User> set = new HashSet<>();
        while(rs.next()) {
            User user = new User();
            user.setUser_id(rs.getInt("user_id"));
            user.setUser_name(rs.getString("user_name"));
            user.setUser_pwd(rs.getString("user_pwd"));
            user.setUser_role(rs.getInt("user_role"));
            user.setUser_email(rs.getString("user_email"));
            user.setUser_headImg(rs.getString("user_headImg"));
            set.add(user);
        }
        List<User> userList = new ArrayList<>(set);
        pageInfo.setList(userList);
        release(rs,ps,connection);
        return pageInfo;
    }

    @Override
    public List<User> findUserByName(String name) throws SQLException {
        final String SQL = "select * from user where user_name=?";
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement(SQL);
        ps.setString(1,name);
        ResultSet rs = ps.executeQuery();
        List<User> list = new ArrayList<>();
        while(rs.next()) {
            User user = new User();
            user.setUser_id(rs.getInt("user_id"));
            user.setUser_name(rs.getString("user_name"));
            user.setUser_pwd(rs.getString("user_pwd"));
            user.setUser_role(rs.getInt("user_role"));
            user.setUser_email(rs.getString("user_email"));
            user.setUser_headImg(rs.getString("user_headImg"));
            list.add(user);
        }
        release(rs,ps,connection);
        return list;
    }



    @Override
    public List<User> findLikeUserName(String name) throws SQLException {
        String sql1 = "select * from user where user_name Like ?";
        Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement(sql1);
        ps.setString(1,"%"+name+"%");
        ResultSet rs = ps.executeQuery();
        List<User> list = new ArrayList<>();
        while(rs.next()) {
            User user = new User();
            user.setUser_id(rs.getLong("user_id"));
            user.setUser_name(rs.getString("user_name"));
            user.setUser_pwd(rs.getString("user_pwd"));
            user.setUser_role(rs.getInt("user_role"));
            user.setUser_email(rs.getString("user_email"));
            user.setUser_headImg(rs.getString("user_headImg"));
            list.add(user);
        }
        return list;
    }


//    @Override
//    public PageInfo<User> findUserLikeName(PageInfo pageInfo ,String name) throws SQLException {
//        String sql1 = "select count(*) from user where user_name user_name like ? ";
//        Connection connection = getConnection();
//        PreparedStatement ps = connection.prepareStatement(sql1);
//        ResultSet rs = ps.executeQuery();
//        int ret = 0;
//        if(rs.next()){
//            ret = rs.getInt(1);
//        }
//        pageInfo.setTotalRecords(ret);
//        Set<User> set = new HashSet<>();
//
//        String sql2 = "select * from user where user_name like ? limit ?,?";
//        ps = connection.prepareStatement(sql2);
//        name = "%"+name+"%";
//        ps.setString(1,name);
//        ps.setInt(2,(pageInfo.getCurrentPageNo()-1)*pageInfo.getPerPageNo());
//        ps.setInt(3,pageInfo.getPerPageNo());
//        rs = ps.executeQuery();
//        while(rs.next()) {
//            User user = new User();
//            user.setUser_id(rs.getInt("user_id"));
//            user.setUser_name(rs.getString("user_name"));
//            user.setUser_pwd(rs.getString("user_pwd"));
//            user.setUser_role(rs.getInt("user_role"));
//            user.setUser_email(rs.getString("user_email"));
//            user.setUser_headImg(rs.getString("user_headImg"));
//            set.add(user);
//        }
//        List<User> userList = new ArrayList<>(set);
//        pageInfo.setList(userList);
//        release(rs,ps,connection);
//        return pageInfo;
//    }

    @Override
    public User login(String username, String password) throws SQLException {
        Connection connection= getConnection();
        PreparedStatement ps=connection.prepareStatement("select * from user where user_name=? and user_pwd=?");
        ps.setString(1,username);
        ps.setString(2,password);
        ResultSet rs=ps.executeQuery();
        User user=null;
        if (rs.next()){
            user=new User();
            user.setUser_id(rs.getLong("user_id"));
            user.setUser_name(rs.getString("user_name"));
            user.setUser_pwd(rs.getString("user_pwd"));
            user.setUser_role(rs.getInt("user_role"));
            user.setUser_email(rs.getString("user_email"));
            user.setUser_headImg(rs.getString("user_headImg"));
        }
        release(rs,ps,connection);
        return user;
    }






    @Override
    public int remove(int user_id) throws SQLException {
        Connection connection=getConnection();
        PreparedStatement ps=connection.prepareStatement("delete from user where user_id=?");
        ps.setInt(1,user_id);
        int num=0;
        num=ps.executeUpdate();
        release(ps,connection);
        return num;
    }

    @Override
    public PageInfo<User> users(PageInfo pageInfo) throws SQLException {
        Connection connection= getConnection();
        PreparedStatement ps=connection.prepareStatement("select count(*) from user");
        ResultSet rs=ps.executeQuery();
        int num=0;
        if(rs.next()){
            num=rs.getInt(1);
        }
        pageInfo.setTotalRecords(num);
        ps=connection.prepareStatement("select * from user limit ?,?");
        ps.setInt(1,(pageInfo.getCurrentPageNo()-1)*pageInfo.getPerPageNo());
        ps.setInt(2,pageInfo.getPerPageNo());
        rs=ps.executeQuery();
        List<User> users=new ArrayList<>();
        User user=null;
        while (rs.next()){
            user=new User();
            user.setUser_email(rs.getString("user_email"));
            user.setUser_headImg(rs.getString("user_headImg"));
            user.setUser_id(rs.getLong("user_id"));
            user.setUser_name(rs.getString("user_name"));
            user.setUser_role(rs.getInt("user_role"));
            users.add(user);
        }
        pageInfo.setList(users);
        release(rs,ps,connection);
        return pageInfo;

    }

    @Override
    public int update(User user) throws SQLException {
        Connection connection=getConnection();
        PreparedStatement ps=connection.prepareStatement("update user set user_name=?,user_pwd=?,user_email=?,user_headImg=? where user_id=?");
        ps.setString(1,user.getUser_name());
        ps.setString(2,user.getUser_pwd());
        ps.setString(3,user.getUser_email());
        ps.setString(4,user.getUser_headImg());
        ps.setLong(5,user.getUser_id());
        int num=0;
        num = ps.executeUpdate();
        release(ps,connection);
        return num;


    }

    @Override
    public List<User> userss(String T) throws SQLException {
        Connection connection=getConnection();
        PreparedStatement ps=connection.prepareStatement("select * from user where user_name like ?");
        ps.setString(1,"%"+T+"%");
        ResultSet rs=ps.executeQuery();
        List<User> users=new ArrayList<>();
        User user=null;
        while (rs.next()){
            user=new User();
            user.setUser_email(rs.getString("user_email"));
            user.setUser_headImg(rs.getString("user_headImg"));
            user.setUser_id(rs.getLong("user_id"));
            user.setUser_name(rs.getString("user_name"));
            user.setUser_role(rs.getInt("user_role"));
            users.add(user);
        }
        release(rs,ps,connection);
        return users;
    }

}