Java JDBC
1 public class DAO { 2 3 //表名 4 public static String TABLENAME = "USERLOGIN"; 5 //创建表 6 public static boolean createTable(){ 7 String sql = "CREATE TABLE "+TABLENAME+"(ID INTEGER PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(3) NOT NULL,PWD VARCHAR(15) NOT NULL)"; 8 Connection conn = getConnection(); 9 10 Statement st = null; 11 try { 12 st = conn.createStatement(); 13 int row = st.executeUpdate(sql); 14 15 //创建成功，返回受影响的行数，如果为-1，则创建不成功 16 System.out.println("创建表成功："+row); 17 if(row!=-1){ 18 return true; 19 } 20 } catch (SQLException e) { 21 // TODO Auto-generated catch block 22 e.printStackTrace(); 23 } 24 25 26 try { 27 st.close(); 28 } catch (SQLException e) { 29 // TODO Auto-generated catch block 30 e.printStackTrace(); 31 } 32 return false; 33 } 34 35 //创建JDBC URL 36 static String jdbc = "jdbc:mysql://localhost:3306/student?user=root&password=&characterEncoding=utf-8"; 37 public static Connection getConnection(){ 38 39 //加载jdbc驱动 40 try { 41 //驱动地址全名，包名+类名 42 Class.forName("com.mysql.jdbc.Driver"); 43 44 //连接数据库 45 Connection conn = DriverManager.getConnection(jdbc); 46 return conn; 47 // if(conn!=null){ 48 // System.out.println("连接成功"); 49 // }else{ 50 // System.out.println("连接失败"); 51 //// } 52 } catch (Exception e) { 53 // TODO Auto-generated catch block 54 e.printStackTrace(); 55 } 56 return null; 57 } 58 59 //增 删 改 不带参 60 public static boolean 执行(String sql) throws SQLException{ 61 Connection conn = getConnection(); 62 Statement statement = conn.createStatement(); 63 int row = statement.executeUpdate(sql); 64 System.out.println("受影响行："+row); 65 if(row>0){ 66 67 return true; 68 } 69 conn.close(); 70 return false; 71 } 72 //增 删 改 带参 73 public static boolean 执行(String sql,String[] args) throws SQLException{ 74 Connection conn = getConnection(); 75 PreparedStatement pst = conn.prepareStatement(sql); 76 for (int i = 0; i < args.length; i++) { 77 pst.setString(i+1, args[i]); 78 } 79 int row = pst.executeUpdate(); 80 System.out.println("受影响行："+row); 81 if(row>0) 82 return true; 83 conn.close(); 84 return false; 85 } 86 87 //查询 不带参 88 public static ResultSet 查询(String sql) throws SQLException{ 89 Connection conn = getConnection(); 90 Statement st = conn.createStatement(); 91 ResultSet rs = st.executeQuery(sql); 92 conn.close(); 93 return rs; 94 } 95 //查询 带参 96 public static ResultSet 查询(String sql,String[] args) throws SQLException{ 97 Connection conn = getConnection(); 98 PreparedStatement pst = conn.prepareStatement(sql); 99 for (int i = 0; i < args.length; i++) { 100 pst.setString(i+1, args[i]); 101 } 102 ResultSet rs = pst.executeQuery(); 103 conn.close(); 104 return rs; 105 } 106 107 }
1 public class User { 2 private String id; 3 private String name; 4 private String pwd; 5 private String age; 6 private String sex; 7 private String sign; 8 9 public User(String name, String pwd) { 10 super(); 11 this.name = name; 12 this.pwd = pwd; 13 } 14 15 public User(String id, String name, String pwd, String age, String sex, 16 String sign) { 17 super(); 18 this.id = id; 19 this.name = name; 20 this.pwd = pwd; 21 this.age = age; 22 this.sex = sex; 23 this.sign = sign; 24 } 25 26 @Override 27 public String toString() { 28 return "User [id=" + id + ", name=" + name + ", pwd=" + pwd + ", age=" 29 + age + ", sex=" + sex + ", sign=" + sign + "]"; 30 } 31 32 public String getId() { 33 return id; 34 } 35 36 public void setId(String id) { 37 this.id = id; 38 } 39 40 public String getName() { 41 return name; 42 } 43 44 public void setName(String name) { 45 this.name = name; 46 } 47 48 public String getPwd() { 49 return pwd; 50 } 51 52 public void setPwd(String pwd) { 53 this.pwd = pwd; 54 } 55 56 public String getAge() { 57 return age; 58 } 59 60 public void setAge(String age) { 61 this.age = age; 62 } 63 64 public String getSex() { 65 return sex; 66 } 67 68 public void setSex(String sex) { 69 this.sex = sex; 70 } 71 72 public String getSign() { 73 return sign; 74 } 75 76 public void setSign(String sign) { 77 this.sign = sign; 78 } 79 80 }
1 public interface IUser { 2 ResultSet 查询用户(User user); 3 4 boolean 注册用户(User user); 5 6 boolean 登录用户(User user); 7 8 boolean 修改用户(User user,String pwd); 9 10 boolean 删除用户(User user); 11 }
1 public class UserDAO extends DAO implements IUser{ 2 3 4 public UserDAO(){ 5 createTable(); 6 7 } 8 9 @Override 10 public boolean 注册用户(User user) { 11 String sql2 = "INSERT INTO "+TABLENAME+"(NAME,PWD) VALUES(?,?)"; 12 String[] args2 = new String[]{user.getName(),user.getPwd()}; 13 14 try { 15 boolean b = 执行(sql2,args2); 16 return b; 17 } catch (SQLException e) { 18 System.out.println("注册失败"); 19 } 20 return false; 21 } 22 23 //查询，然后再遍历 24 @Override 25 public boolean 登录用户(User user) { 26 //根据名字查询 27 ResultSet rs = 查询用户(user); 28 29 try { 30 while(rs.next()){ 31 String name = rs.getString("NAME"); 32 String pwd = rs.getString("PWD"); 33 if(name.equals(user.getName()) && pwd.equals(user.getPwd())){ 34 System.out.println("登录成功，欢迎您！"+name); 35 return true; 36 }else{ 37 System.out.println("登录失败，密码错误"); 38 return false; 39 } 40 41 } 42 } catch (SQLException e) { 43 // TODO Auto-generated catch block 44 e.printStackTrace(); 45 } 46 //查询不到，提示先注册 47 System.out.println("登录失败！请先注册"); 48 49 50 return false; 51 } 52 53 @Override 54 public boolean 修改用户(User user,String pwd) { 55 //用户名不允许修改，只允许修改密码 56 String sql = "UPDATE "+TABLENAME+" SET PWD=? WHERE NAME=?"; 57 String[] args = new String[]{pwd,user.getName()}; 58 59 try { 60 boolean b = 执行(sql,args); 61 System.out.println("修改成功！"); 62 return b; 63 64 } catch (SQLException e) { 65 // TODO Auto-generated catch block 66 e.printStackTrace(); 67 } 68 69 return false; 70 } 71 72 @Override 73 public boolean 删除用户(User user) { 74 //非管理员，直接删除 75 String sql = "DELETE * FROM "+TABLENAME+" WHERE NAME=? AND PWD=?"; 76 String[] args = new String[]{user.getName(),user.getPwd()}; 77 78 try { 79 boolean b = 执行(sql,args); 80 System.out.println("删除成功"); 81 return b; 82 } catch (SQLException e) { 83 // TODO Auto-generated catch block 84 e.printStackTrace(); 85 } 86 87 return false; 88 } 89 90 @Override//根据名字查询 91 public ResultSet 查询用户(User user) { 92 String sql = "SELECT * FROM "+TABLENAME+" WHERE NAME=?"; 93 String[] args = new String[]{user.getName()}; 94 try { 95 ResultSet rs = 查询(sql,args); 96 if(rs!=null){ 97 return rs; 98 } 99 100 } catch (SQLException e) { 101 // TODO Auto-generated catch block 102 e.printStackTrace(); 103 } 104 return null; 105 } 106 107 108 }
1 public class UserDAOUpadate extends UserDAO{ 2 3 4 public UserDAOUpadate() { 5 super(); 6 } 7 8 //用户注册 9 public boolean 用户注册(User user){ 10 //先查询是否已存在，根据名字 11 ResultSet rs = 查询用户(user); 12 if(rs==null){ 13 //不存在，，直接注册 14 boolean b = 注册用户(user); 15 return b; 16 } 17 18 return false; 19 } 20 21 //用户登录,登录成功，返回用户信息 22 public void 用户登录(User user){ 23 if(登录用户(user)) 24 System.out.println(user); 25 26 } 27 28 //修改密码 29 public boolean 用户修改密码(User user,String pwd){ 30 if(登录用户(user)){ 31 if(pwd.equals(user.getPwd())){ 32 System.out.println("密码不能与原始密码相同!"); 33 return false; 34 } 35 boolean b = 修改用户(user,pwd); 36 return b; 37 } 38 39 return false; 40 } 41 42 //管理员删除其他账户 43 public boolean 用户删除(User user,boolean isDelete){ 44 //账户是管理员，不允许删除 45 if("admin".equals(user.getName())){ 46 System.out.println("管理员账户无法删除"); 47 return false; 48 } 49 //非管理账户直接删除 50 if(isDelete){ 51 boolean b = 删除用户(user); 52 return b; 53 } 54 return false; 55 } 56 57 58 59 60 }
