jdbc进行用户信息管理

深碍√TFBOYSˉ_ 2022-06-07 13:58 155阅读 0赞

import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Scanner;
    
    public class T_UsersTest {
    
    	static Scanner s=new Scanner(System.in);
    	static Connection conn=getConn();
    	public static void main(String[] args) {
    		
    		logIn();//登录之后才具有操作权限
    		try {
    			conn.close();//关闭Connection
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		s.close();
    	}
    	/**
    	 * 获得Connection
    	 * @return
    	 */
    	public static Connection getConn() {
    		
    		try {
    			//加载驱动
    			Class.forName("com.mysql.jdbc.Driver");
    			//获得连接
    			String url="jdbc:mysql://localhost:3306/java1710";
    			String name="root";
    			String password="0325";
    			Connection conn=DriverManager.getConnection(url,name,password);
    			return conn;
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		return null;
    	}
    	/**
    	 * 登录系统，获得对数据库的操作权限
    	 */
    	public static void logIn() {
    		System.out.println("请输入用户名");
    		String name=s.next();
    		System.out.println("请输入password");
    		String pw=s.next();
    		PreparedStatement ps=null;
    		ResultSet rs=null;
    		try {
    			//查询数据库中是否存在该用户
    			String sql="SELECT * FROM T_USERS WHERE LOGINNAME=? AND USERPWD=?";
    			ps=conn.prepareStatement(sql);
    			ps.setString(1, name);
    			ps.setString(2, pw);
    			 rs=ps.executeQuery();
    			if(rs.next()) {
    				rs.close();
    				System.out.println("登陆成功");
    				System.out.println("此处可以调用插入删除操作的方法");
    				UserDao.update(1, "li", "123456", "李白");
    			}else {
    				System.out.println("用户名或密码错误,清重新登录");
    				logIn();
    			}
    		}catch(Exception e) {
    			e.printStackTrace();
    		}finally {
    			try {
    				rs.close();
    			} catch (SQLException e1) {
    				// TODO Auto-generated catch block
    				e1.printStackTrace();
    			}
    			try {
    				ps.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    	}
    	/**
    	 * 方法类
    	 * @author dingshuangen
    	 *
    	 */
    	public static class UserDao{
    		
    		
    		/**
    		 * 插入数据
    		 */
    		public static void insert(int userid,String loginname,String userpwd,String realname) {
    			String sql="INSERT INTO T_USERS VALUES(?,?,?,?)";//插入sql语句
    			PreparedStatement ps=null;
    			try {
    				ps=conn.prepareStatement(sql);
    				ps.setInt(1, userid);
    				ps.setString(2, loginname);
    				ps.setString(3, userpwd);
    				ps.setString(4, realname);
    				ps.execute();
    			}catch(Exception e) {
    				e.printStackTrace();
    			}finally {
    				try {
    					ps.close();
    				} catch (SQLException e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    			}
    		}
    		
    		/**
    		 * 更新用户数据
    		 * @param userid
    		 * @param loginname
    		 * @param password
    		 * @param name
    		 */
    		public static void update(int userid,String loginname,String password,String realname) {
    			String sql="UPDATE T_USERS SET LOGINNAME=?,USERPWD=?,REALNAME=? WHERE USERID=?";//根据用户ID修改用户信息的sql语句
    			PreparedStatement ps=null;
    			try {
    				ps=conn.prepareStatement(sql);
    				ps.setString(1, loginname);
    				ps.setString(2, password);
    				ps.setString(3, realname);
    				ps.setInt(4, userid);
    				ps.execute();
    			}catch(Exception e) {
    				e.printStackTrace();
    			}finally {
    				try {
    					ps.close();
    				} catch (SQLException e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    			}
    		}
    		/**
    		 * 删除用户数据
    		 * @param userid
    		 */
    		public static void delete(int userid) {
    			String sql="DELETE FROM T_USERS WHERE USERID=?";//根据用户id进行删除操作的sql语句
    			PreparedStatement ps=null;
    			try {		
    				
    				ps=conn.prepareStatement(sql);
    				ps.setInt(1, userid);
    				ps.execute();
    				
    			}catch(Exception e) {
    				e.printStackTrace();
    			}finally {
    				try {
    					ps.close();
    				} catch (SQLException e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    			}
    		}
    		
    		
    		/**
    		 * 查询所有用户信息
    		 * @return
    		 */
    		public static List<String> select() {
    			
    			String sql="SELECT * FROM T_USERS";
    			PreparedStatement ps=null;
    			ResultSet rs=null;
    			ArrayList<String>list=new ArrayList<String>();
    			try {
    				ps=conn.prepareStatement(sql);
    				rs=ps.executeQuery();
    				while(rs.next()) {
    					String info="用户ID:"+rs.getInt(1)+"\t用户名:"+rs.getString(2)+"\t真实姓名:"+rs.getString(4);
    					list.add(info);//将查询到的用户信息存储到List中
    				}
    			}catch(Exception e) {
    				e.printStackTrace();
    			}finally {
    				try {
    					rs.close();
    				} catch (SQLException e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    				try {
    					ps.close();
    				} catch (SQLException e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    			}
    			
    			return list;
    		}
    	}
    
    }