用框架和不用框架对比之DAO

 用框架的Mapper接口

package study.heimamybts.mapper;

import org.apache.ibatis.annotations.Param;
import study.heimamybts.pojo.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper //应用程序在运行时, 会自动的为该接口创建一个实现类对象(代理对象), 并且会自动将该实现类对象存入IOC容器 - bean
public interface UserMapper {

    /**
     * 查询所有用户
     */
//    @Select("Select id, username, password, name, age from user")//已经用XML配置的SQL语句替代
    public List<User> findAll();

    /**
     * 根据ID删除用户
     */
    @Delete("delete from user where id = #{id}")
    //public void deleteById(Integer id);
    public Integer deleteById(@Param("id") Integer id);

    /**
     * 新增用户
     */
    @Insert("insert into user(username, password, name, age) values (#{username}, #{password}, #{name}, #{age})")
    public void insert(User user);

    /**
     * 更新用户
     */
    @Update("update user set username = #{username}, password = #{password}, name = #{name}, age = #{age} where id = #{id}")
    public void update(User user);

    /**
     * 根据用户名和密码查询用户
     */
    @Select("select * from user where username = #{username} and password = #{password}")
    public User findByUsernameAndPassword(@Param("username") String username,@Param("password")  String password);
    //public User findByUsernameAndPassword(String username,String password);//在aliyun上建的项目@Param不能省略

}

 

不用框架时的DAO(这里没用接口)

package study.jdbc.model;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class UserDAO {
    private Connection connection;
    
    public UserDAO() {
        connectToDatabase();
        createTableIfNotExists();
    }
    
    private void connectToDatabase() {
        try {
            // 从属性文件加载数据库配置
            Properties props = new Properties();
            props.load(getClass().getClassLoader().getResourceAsStream("db.properties"));
            
            String url = props.getProperty("db.url");
            String user = props.getProperty("db.user");
            String password = props.getProperty("db.password");
            
            // 注册驱动并建立连接
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(url, user, password);
            System.out.println("数据库连接成功");
        } catch (Exception e) {
            System.err.println("数据库连接失败: " + e.getMessage());
        }
    }
    
    private void createTableIfNotExists() {
        String sql = "CREATE TABLE IF NOT EXISTS users (" +
                     "id INT AUTO_INCREMENT PRIMARY KEY, " +
                     "username VARCHAR(50) NOT NULL UNIQUE, " +
                     "password VARCHAR(50) NOT NULL, " +
                     "name VARCHAR(100) NOT NULL, " +
                     "age INT NOT NULL)";
        
        try (Statement stmt = connection.createStatement()) {
            stmt.execute(sql);
            System.out.println("表创建/验证成功");
        } catch (SQLException e) {
            System.err.println("表创建失败: " + e.getMessage());
        }
    }
    
    // 添加用户
    public boolean addUser(User user) {
        String sql = "INSERT INTO user (username, password, name, age) VALUES (?, ?, ?, ?)";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, user.getName());
            pstmt.setInt(4, user.getAge());
            
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                try (ResultSet rs = pstmt.getGeneratedKeys()) {
                    if (rs.next()) {
                        user.setId(rs.getInt(1));
                    }
                }
                return true;
            }
        } catch (SQLException e) {
            System.err.println("添加用户失败: " + e.getMessage());
        }
        return false;
    }
    
    // 删除用户
    public boolean deleteUser(int id) {
        String sql = "DELETE FROM user WHERE id = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            System.err.println("删除用户失败: " + e.getMessage());
        }
        return false;
    }
    
    // 更新用户
    public boolean updateUser(User user) {
        String sql = "UPDATE user SET username = ?, password = ?, name = ?, age = ? WHERE id = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, user.getName());
            pstmt.setInt(4, user.getAge());
            pstmt.setInt(5, user.getId());
            
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            System.err.println("更新用户失败: " + e.getMessage());
        }
        return false;
    }
    
    // 获取所有用户
    public List<User> getAllUsers() {
        List<User> users = new ArrayList<>();
        String sql = "SELECT * FROM user";
        
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setName(rs.getString("name"));
                user.setAge(rs.getInt("age"));
                users.add(user);
            }
        } catch (SQLException e) {
            System.err.println("获取用户列表失败: " + e.getMessage());
        }
        return users;
    }
    
    // 按ID获取用户
    public User getUserById(int id) {
        String sql = "SELECT * FROM user WHERE id = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setUsername(rs.getString("username"));
                    user.setPassword(rs.getString("password"));
                    user.setName(rs.getString("name"));
                    user.setAge(rs.getInt("age"));
                    return user;
                }
            }
        } catch (SQLException e) {
            System.err.println("获取用户失败: " + e.getMessage());
        }
        return null;
    }
    
    // 关闭连接
    public void close() {
        if (connection != null) {
            try {
                connection.close();
                System.out.println("数据库连接已关闭");
            } catch (SQLException e) {
                System.err.println("关闭连接失败: " + e.getMessage());
            }
        }
    }
}