第六次作业---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;
        }
    }
}