(UserDao)JDBC连接数据库的方法类

作者: 张威先森 | 来源:发表于2018-09-21 14:57 被阅读0次
package com.neuedu.manager;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
/**
该类用于访问数据库，执行出来数据的方法
@author Administrator
*/
public class UserDao {
//登录判断
    public UserInfo login(String username,String password){
    
    Connection conn=DBUtil.getConnection();
    
    String sql="Select * from userinfo where username=? and password=?";
    
    try {
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,username);
        ps.setString(2,password);
        
        ResultSet rs=ps.executeQuery();
        while(rs.next()){//如果有内容 就建立一个UserInfo对象 从而返回一个该对象 去建立登录标识
            UserInfo userinfo=new UserInfo();
            userinfo.setUserid(rs.getInt("userid"));
            userinfo.setUsername(rs.getString("username"));
            userinfo.setPassword(rs.getString("password"));
            userinfo.setNikename(rs.getString("nikename"));
            userinfo.setBalance(rs.getDouble("balance"));
            userinfo.setOuttime(rs.getInt("outtime"));
            userinfo.setVip(rs.getInt("vip"));
            return userinfo;
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        DBUtil.closeConn(conn); 
    }
    
    //如果没有则返回空 说明账户名或密码不正确
    return null;
    
}

//查看用户名是否重复
public String login(String username){
    
    Connection conn=DBUtil.getConnection();
    
    String sql="Select * from userinfo where username=?";
    
    try {
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,username);
        
        ResultSet rs=ps.executeQuery();
        while(rs.next()){
            return rs.getString("username");
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        DBUtil.closeConn(conn); 
    }
    return null;
    
}

//获取随机姓名表的姓名
public String getNikeName(int a){
    
    Connection conn=DBUtil.getConnection();

    String sql="Select * from ranname where nid=?";

    try {
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setInt(1,a);
    
        ResultSet rs=ps.executeQuery();
        while(rs.next()){
            return rs.getString("name");
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        DBUtil.closeConn(conn); 
    }
    return null;

}

//注册用户
public void putUser(String username,String password,String nikename){
    
    Connection conn=DBUtil.getConnection();

    String sql="insert into userinfo(username,password,nikename) values (?,?,?)";

    try {
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,username);
        ps.setString(2,password);
        ps.setString(3,nikename);
        ps.executeLargeUpdate();
        
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        DBUtil.closeConn(conn); 
    }

}   

//修改用户密码
public void updatePwd(String username,String newPwd){
    
    Connection conn=DBUtil.getConnection();

    String sql="update userinfo set password=? where username=?";

    try {
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,newPwd);
        ps.setString(2,username);
        
    
        ps.executeLargeUpdate();
        
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        DBUtil.closeConn(conn); 
    }

}   

//修改用户昵称
public void updateNikeName(String username,String nikename){
    
    Connection conn=DBUtil.getConnection();

    String sql="update userinfo set nikename=? where username=?";

    try {
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,nikename);
        ps.setString(2,username);
        
    
        ps.executeLargeUpdate();
        
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        DBUtil.closeConn(conn); 
    }

}   

//查询用户余额
    public double findBalance(String username){
        
        Connection conn=DBUtil.getConnection();
    
        String sql="Select balance from userinfo where username=?";
    
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,username);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                return rs.getInt("balance");
            }
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.closeConn(conn); 
        }
        
        return 0;
    
    }
    
    //充值
    public void putMoney(String username,double money){
        
        Connection conn=DBUtil.getConnection();
    
        String sql="update userinfo set balance=balance+? where username=?";
    
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setDouble(1,money);
            ps.setString(2,username);
            ps.executeLargeUpdate();
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.closeConn(conn); 
        }
    
    }
    
    //向userpay表中插入数据
    public void putUserPay(int userid,double paymoney){
        
        Connection conn=DBUtil.getConnection();
    
        String sql="insert into userpay(userid,paymoney,paytime) values(?,?,Now())";
    
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,userid);
            ps.setDouble(2,paymoney);
            ps.executeLargeUpdate();
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.closeConn(conn); 
        }
    
    }
    

    //通过userid来获取消费集合
    public List<UserPay> getPay(int userid){
        
        Connection conn=DBUtil.getConnection();
    
        String sql="select * from userpay where userid=?";
        
        List<UserPay> list= new ArrayList<UserPay>();
        
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,userid);
            
            ResultSet rs=ps.executeQuery();
            
            while(rs.next()){
                UserPay up=new UserPay();
                up.setUserid(rs.getInt("userid"));
                up.setPid(rs.getInt("pid"));
                up.setPaytime(rs.getTimestamp("paytime"));
                up.setPaymoney(rs.getDouble("paymoney"));
                list.add(up);
            }
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.closeConn(conn); 
        }
        return list;
    }
    
    //给出消费金额 在数据库中比较 看余额是否充足
    public static boolean enoughMoney(String username,int x){
        
        Connection conn=DBUtil.getConnection();
        String sql="Select balance from userinfo where username=?";
        
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1, username);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                if(rs.getDouble("balance")>=x){
                    return true;
                }else{
                    return false;
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.closeConn(conn);
        }
        return false;
    }
    
    //充值vip 插入
    public static void vip(String username,int x){
        Connection conn=DBUtil.getConnection();
        String sql="update userinfo set vip=vip+? where username=?";
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,x);
            ps.setString(2, username);
            ps.executeLargeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.closeConn(conn);
        }
    }
    
    //充值VIP后 在userinfo表中 减少balance余额
    public static void buy(String username,int num){
        Connection conn=DBUtil.getConnection();
        String sql="update userinfo set balance=balance-? where username=?";
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,num);
            ps.setString(2, username);
            ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.closeConn(conn);
        }
    }
    
    //通过传入用户名 看是否处于冻结
    public static int outTime(String username){
        
        Connection conn=DBUtil.getConnection();
        
        String sql="Select outtime from userinfo where username=?";
        
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,username);
            
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                return rs.getInt("outtime");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.closeConn(conn); 
        }
        return 0;
        
    }
    
    //传入用户姓名 更改用户冻结状态
    public static void updateOutTime(String username){
        Connection conn=DBUtil.getConnection();
        
        String sql="update userinfo set outtime=0 where username=?";
        
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,username);
            
            ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.closeConn(conn); 
        }
    }
    
    //向userinfo表中插入退出时间
    public static void putOutTime(String username){
        Connection conn=DBUtil.getConnection();
        String sql="update userinfo set goouttime=now() where username=?";
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1, username);
            
            ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.closeConn(conn);
        }
    }
    
    //传入时间 返回该用户是否超过规定时长
    public static boolean wheretime(String s){
        Connection conn=DBUtil.getConnection();
        String sql="select extract(minute from timediff(now(),goouttime)) time from userinfo where username=?";
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1, s);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                if(rs.getInt("time")>1){
                    return true;
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.closeConn(conn);
        }
        return false;
    }
    
    //冻结账号
    public static void coolUserid(String username){
        Connection conn=DBUtil.getConnection();
        
        String sql="update userinfo set outtime=1 where username=?";
        
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,username);;
            
            ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBUtil.closeConn(conn); 
        }
    }
}
(UserDao)JDBC连接数据库的方法类

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读
