JDBC
题目1：编写一个应用程序，输入用户名和密码，访问test数据库中t_login表（字段包括id、username、password），验证登录是否成功。
题目2：在上一题基础上，当登录成功后，将t_user表（id、name、sex、birthday）的信息进行显示（要求使用DB.java完成登录和获取t_user表中数据的操作），最后再对t_user表进行一条记录的添加操作。
package Test; 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 xiaofei.StudentEntity.Login; import xiaofei.StudentEntity.User; public class JDBCTest { private static final String URL = "jdbc:mysql://localhost:3306/test"; private static final String USERNAME = "root"; private static final String PASSWORD = "xiaofei"; //根据学号查询学生 public Login queryStudentBySno(int id) { Connection connection = null; PreparedStatement pstat = null; ResultSet rs = null; Login login = null; try { Class.forName("com.mysql.jdbc.Driver"); connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); String sql = "select * from t_login where id = ?"; pstat = connection.prepareStatement(sql); pstat.setInt(1, id); rs = pstat.executeQuery(); if(rs.next()) { String username = rs.getString("username"); int id1 = rs.getInt("id"); String password = rs.getString("password"); login = new Login(id1,username,password); } return login; } catch (ClassNotFoundException e) { e.printStackTrace(); return null; }catch(SQLException e) { e.printStackTrace(); return null; }catch(Exception e) { e.printStackTrace(); return null; }finally { try { if(rs!=null)rs.close(); if(connection!=null)connection.close(); if(pstat!=null)pstat.close(); }catch(SQLException e) { e.printStackTrace(); }catch(Exception e) { e.printStackTrace(); } } } //查询所有的用户 public List<User> queryAllUsers(){ List<User> users = new ArrayList<User>(); Connection connection = null; PreparedStatement pstat = null; ResultSet rs = null; User user = null; try { Class.forName("com.mysql.jdbc.Driver"); connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); String sql = "select * from t_user"; pstat = connection.prepareStatement(sql); rs = pstat.executeQuery(); while(rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); String sex = rs.getString("sex"); String birthday = rs.getString("birthday"); user = new User(id,name,sex,birthday); users.add(user); } return users; } catch (ClassNotFoundException e) { e.printStackTrace(); return null; }catch(SQLException e) { e.printStackTrace(); return null; }catch(Exception e) { e.printStackTrace(); return null; }finally { try { if(rs!=null)rs.close(); if(connection!=null)connection.close(); if(pstat!=null)pstat.close(); }catch(SQLException e) { e.printStackTrace(); }catch(Exception e) { e.printStackTrace(); } } } //添加用户 public boolean addUser(User user) { Connection connection = null; PreparedStatement pstat = null; try { Class.forName("com.mysql.jdbc.Driver"); connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); String sql = "insert into t_user values(?,?,?,?)"; pstat = connection.prepareStatement(sql); pstat.setInt(1, user.getId()); pstat.setString(2, user.getName()); pstat.setString(3, user.getSex()); pstat.setString(4, user.getBirthday()); int count = pstat.executeUpdate(); if(count>0) return true; else return false; } catch (ClassNotFoundException e) { e.printStackTrace(); return false; }catch(SQLException e) { e.printStackTrace(); return false; }catch(Exception e) { e.printStackTrace(); return false; }finally { try { if(connection!=null)connection.close(); if(pstat!=null)pstat.close(); }catch(SQLException e) { e.printStackTrace(); }catch(Exception e) { e.printStackTrace(); } } } }
package xiaofei.StudentEntity; public class Login { private int id; private String username; private String passwrod; public Login() {} public Login(int id, String username, String passwrod) { this.id = id; this.username = username; this.passwrod = passwrod; } public Login( String username, String passwrod) { this.username = username; this.passwrod = passwrod; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPasswrod() { return passwrod; } public void setPasswrod(String passwrod) { this.passwrod = passwrod; } }
package xiaofei.StudentEntity; public class User { private int id; private String name; private String sex; private String birthday; public User(int id, String name, String sex, String birthday) { super(); this.id = id; this.name = name; this.sex = sex; this.birthday = birthday; } public User() {} public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getBirthday() { return birthday; } public void setBirthday(String birthday) { this.birthday = birthday; } }
进行登录的验证代码
package Test; import java.util.Scanner; import xiaofei.StudentEntity.Login; public class Test19 { public static void main(String[] args) { // TODO Auto-generated method stub Scanner scanner = new Scanner(System.in); System.out.println("请输入您的id"); int id = scanner.nextInt(); System.out.println("请输入您的姓名"); String username = scanner.next(); System.out.println("请输入您的密码"); String password = scanner.next(); JDBCTest jdbc = new JDBCTest(); Login login = jdbc.queryStudentBySno(id); if(login!=null) { if(id==login.getId()&&username.equals(login.getUsername())&&password.equals(login.getPasswrod())) { System.out.println("用户登录成功！！！"); }else { System.out.println("用户登录失败！！！"); } }else { System.out.println("用户登录失败！！！"); } } }
进行展示表中全部数据的代码
package Test; import java.util.ArrayList; import java.util.List; import xiaofei.StudentEntity.User; public class Test20 { public static void main(String[] args) { // TODO Auto-generated method stub System.out.println("显示全部信息！！！"); JDBCTest jdbc = new JDBCTest(); List<User> users = new ArrayList<User>(); users = jdbc.queryAllUsers(); for(User user : users) { System.out.println(user.getId()+" "+user.getName()+" "+user.getSex()+" "+user.getBirthday()); } } }
进行添加用户的代码
package Test; import java.util.Scanner; import xiaofei.StudentEntity.User; public class Tset21 { public static void main(String[] args) { // TODO Auto-generated method stub Scanner scanner = new Scanner(System.in); System.out.println("请输入您要添加用户的id"); int id = scanner.nextInt(); System.out.println("请输入您要添加用户的姓名"); String name = scanner.next(); System.out.println("请输入您要添加用户的性别"); String sex = scanner.next(); System.out.println("请输入您要添加用户的生日"); String birthday = scanner.next(); User user = new User(id,name,sex,birthday); JDBCTest jdbc = new JDBCTest(); boolean flag = jdbc.addUser(user); if(flag) { System.out.println("用户添加成功！！！"); }else { System.out.println("用户添加失败！！！"); } } }
验证登录成功显示的图片
显示表中全部信息的图片
添加新的用户
