第六次作业---JDBC连接数据库
截图:
所建的类:
所建数据库:
users表:
添加完:
查询所有数据:
通过id查数据:
删除数据:
代码块:
user.java:
public class User { private int id; private String username; private String password; private String email; private Date birthday; 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 getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } }
UsersDao.java:
//完成对数据库crud操作 public class UsersDao { public boolean insert(User user){ //1.提供添加方法 Connection con = null; Statement st =null; try{ //1.获取连接对象 con = JDBCUtils.getCon(); //2.获取执行sql语句的对象 st = con.createStatement(); //3.执行sql java.util.Date birthday = user.getBirthday(); String sqlBirthday = String.format("%tF", birthday); String sql = "insert into users(id,name,password,email,birthday)"+"values('"+user.getId()+"','" +user.getUsername()+"','" +user.getPassword()+"','" +user.getEmail()+"','" +sqlBirthday+"'" +")"; int row = st.executeUpdate(sql); if(row>0){ //插入失败 return true; } }catch(Exception e){ throw new RuntimeException(e); }finally{ JDBCUtils.realse(null, st, con); } return false; } //2.提供查询所有方法 public List<User>findAllUser(){ Connection con = null; Statement st = null; ResultSet rs = null; try{ //1.获取连接对象 con = JDBCUtils.getCon(); //2.获取执行SQL语句对象 st = con.createStatement(); //3.执行SQL String sql = "select * from users"; rs = st.executeQuery(sql); //4.遍历rs List<User>list = new ArrayList<User>(); while(rs.next()){ User user = new User(); user.setId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setPassword(rs.getString("password")); user.setEmail(rs.getString("email")); user.setBirthday(rs.getDate("birthday")); //把对象添加到集合中 list.add(user); } return list; }catch(Exception e){ throw new RuntimeException(e); }finally{ JDBCUtils.realse(rs, st, con); } } //3.提供根据id查询方法 public User findUserById(int id){ Connection con = null; ResultSet rs= null; PreparedStatement st = null; try{ //1.获取连接 con = JDBCUtils.getCon(); //2.获取执行sql语句对象 String sql = "select * from users where id =?"; st = con.prepareStatement(sql); //3.执行aql st.setInt(1, id); rs = st.executeQuery(); //4.遍历rs if(rs.next()){ User user = new User(); user.setId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setPassword(rs.getString("password")); user.setEmail(rs.getString("email")); user.setBirthday(rs.getDate("birthday")); return user; } }catch(Exception e){ throw new RuntimeException(e); }finally{ JDBCUtils.realse(rs, st, con); } return null; } //4.提供修改方法 public boolean update(User user){ Connection con = null; PreparedStatement st = null; ResultSet rs = null; try{ //1.获取连接 con = JDBCUtils.getCon(); //2.执行SQL语句对象 String sql = "update from users name=?,password=? where id=? " ; st = con.prepareStatement(sql); //3.执行SQL,给占位符赋值 st.setString(1, user.getUsername()); st.setString(2, user.getPassword()); st.setInt(3, user.getId()); int row = st.executeUpdate(); if(row>0){ //修改成功 return true; } }catch(Exception e){ throw new RuntimeException(e); }finally{ JDBCUtils.realse(rs, st, con); } return false; } //5.提供删除方法 public boolean delete(int id){ Connection con = null; PreparedStatement st =null; ResultSet rs = null; try{ //1.获取连接 con = JDBCUtils.getCon(); //2.执行SQL语句对象 String sql = "delete from users where id=?"; st = con.prepareStatement(sql); //3.执行SQL,给占位符赋值 st.setInt(1, id); int row = st.executeUpdate(); if(row>0){ //删除成功 return true; } }catch(Exception e){ throw new RuntimeException(e); }finally{ JDBCUtils.realse(rs, st, con); } return false; } }
JDBCUtils.java:
public class JDBCUtils { //获取连接对象方法 public static Connection getCon() throws Exception{ //1.注册和加载驱动 Class.forName("com.mysql.jdbc.Driver"); //2.获取连接 Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc","root","123456"); return con; } //关闭连接,释放资源 public static void realse(ResultSet rs,Statement st, Connection con){ if (rs!=null) { try{ rs.close(); }catch (SQLException e){ e.printStackTrace(); } rs=null; } if (st!=null) { try{ st.close(); }catch (SQLException e){ e.printStackTrace(); } st=null; } if (con!=null) { try{ con.close(); }catch (SQLException e){ e.printStackTrace(); } con=null; } } }