JSP 06课（JDBC）

package cn.itcast.jdbc.mainjdbc;

import java.util.Date;

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;
    }

}

package cn.itcast.jdbc.mainjdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

public class JDBCUtils {
    // 获取连接对象的方法
    public static Connection getConnection() throws Exception {
        // 注册并加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 获取链接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "root");
        return connection;

    }

    // 关闭连接，并释放资源
    public static void realse(ResultSet resultSet,Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException exception) {
                exception.printStackTrace();
            }
            resultSet = null;
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException exception) {
                exception.printStackTrace();
            }
            statement = null;
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (Exception exception) {
                exception.printStackTrace();
            }
            connection = null;
        }
    }

}

package cn.itcast.jdbc.mainjdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/*
 * 完成对数据库的crud操作
 */

public class UsersDao {
    // 1.提供添加方法
    public boolean insert(User user) {
        Connection connection = null;
        Statement statement = null;
        try {
            
            // 1.获取连接对象
            connection = JDBCUtils.getConnection();
            
            // 2.获取执行sql语句的对象
            statement = connection.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 = statement.executeUpdate(sql);
            if (row > 0) {
                
                // 插入成功
                return true;
            }

        } catch (Exception exception) {
            throw new RuntimeException(exception);
        } finally {
            JDBCUtils.realse(null, statement, connection);
        }

        return false;
    }

    // 2.提供查询方法
    public List<User> findAllUser() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            
            // 1.获取连接对象
            connection = JDBCUtils.getConnection();
            
            // 2.获取执行sql语句的对象
            statement = connection.createStatement();
            
            // 3.执行sql
            String sql = "select * from users";
            resultSet = statement.executeQuery(sql);
            
            // 4.遍历
            List<User> list = new ArrayList<User>();
            while (resultSet.next()) {
                
                // 一行数据对应一个对象，获取每一行的数据，就设置给一个user对象
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setEmail(resultSet.getString("email"));
                java.sql.Date birthday = resultSet.getDate("birthday");
                user.setBirthDay(birthday);
                
                // 把对象添加到集合中
                list.add(user);
            }
            return list;

        } catch (Exception exception) {
            throw new RuntimeException(exception);
        } finally {
            JDBCUtils.realse(resultSet, statement, connection);
        }
    }

    // 3.根据id查询记录
    public User findUserById(int id) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            
            // 1.获取连接对象
            connection = JDBCUtils.getConnection();
            
            // 2.获取执行sql语句的对象
            String sql = "select * from users where id = ?";
            statement = connection.prepareStatement(sql);
            
            // 3.执行sql，给id赋值
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            
            // 4.遍历
            if (resultSet.next()) {
                
                // 一行数据对应一个对象，获取每一行的数据，就设置给一个user对象
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("name"));
                user.setPassword(resultSet.getString("password"));
                user.setEmail(resultSet.getString("email"));
                java.sql.Date birthday = resultSet.getDate("birthday");
                user.setBirthDay(birthday);
                return user;
                
                // 把对象添加到集合中
            }
        } catch (Exception exception) {
            throw new RuntimeException(exception);
        } finally {
            JDBCUtils.realse(resultSet, statement, connection);

        }
        return null;
    }

    // 4.修改方法，根据id修改记录
    public boolean update(User user) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {

            // 1.获取连接对象
            connection = JDBCUtils.getConnection();

            // 2.获取执行sql语句的对象
            String sql = "update users set name = ?,password = ?,email =? where id = ?";
            statement = connection.prepareStatement(sql);

            // 3.执行sql,给占位符赋值
            statement.setString(1, user.getUsername());
            statement.setString(2, user.getPassword());
            statement.setString(3, user.getEmail());
            statement.setInt(4, user.getId());

            int row = statement.executeUpdate();
            if (row > 0) {
                
                // 插入成功
                return true;
            }

        } catch (Exception exception) {
            throw new RuntimeException(exception);
        } finally {
            JDBCUtils.realse(null, statement, connection);
        }

        return false;
    }
    
    //5.删除
    public boolean delete(int id) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {

            // 1.获取连接对象
            connection = JDBCUtils.getConnection();

            // 2.获取执行sql语句的对象
            String sql = "delete from users  where id = ?";
            statement = connection.prepareStatement(sql);

            // 3.执行sql,给占位符赋值
            statement.setInt(1, id);

            int row = statement.executeUpdate();
            if (row > 0) {
                
                // 插入成功
                return true;
            }

        } catch (Exception exception) {
            throw new RuntimeException(exception);
        } finally {
            JDBCUtils.realse(null, statement, connection);
        }

        return false;
        
    }

}