DAO 设计模式与 AJAX 实现数据库表 user 增删改查完整代码示例

这里给出一个简单的例子来演示如何利用 DAO 设计模式和 ajax 技术实现数据库表 user 的增删改查操作。

首先，我们需要创建一个数据库表 user，包含 id、name 和 age 字段。

CREATE TABLE user (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  age INT
);

接下来，我们创建一个 User 类来表示数据库表 user 的记录。

public class User {
  private int id;
  private String name;
  private int age;

  // 构造函数
  public User(int id, String name, int age) {
    this.id = id;
    this.name = name;
    this.age = age;
  }

  // getter 和 setter 方法
  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public int getAge() {
    return age;
  }

  public void setAge(int age) {
    this.age = age;
  }
}

接下来，我们创建一个 UserDAO 类来处理数据库表 user 的增删改查操作。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {
  private static final String URL = 'jdbc:mysql://localhost:3306/test';
  private static final String USERNAME = 'root';
  private static final String PASSWORD = 'password';

  // 获取数据库连接
  private Connection getConnection() throws SQLException {
    return DriverManager.getConnection(URL, USERNAME, PASSWORD);
  }

  // 关闭数据库连接
  private void closeConnection(Connection connection) throws SQLException {
    if (connection != null) {
      connection.close();
    }
  }

  // 添加用户
  public void addUser(User user) {
    Connection connection = null;
    PreparedStatement statement = null;
    try {
      connection = getConnection();
      String sql = 'INSERT INTO user (name, age) VALUES (?, ?)';
      statement = connection.prepareStatement(sql);
      statement.setString(1, user.getName());
      statement.setInt(2, user.getAge());
      statement.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        if (statement != null) {
          statement.close();
        }
        closeConnection(connection);
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  // 删除用户
  public void deleteUser(int id) {
    Connection connection = null;
    PreparedStatement statement = null;
    try {
      connection = getConnection();
      String sql = 'DELETE FROM user WHERE id = ?';
      statement = connection.prepareStatement(sql);
      statement.setInt(1, id);
      statement.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        if (statement != null) {
          statement.close();
        }
        closeConnection(connection);
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  // 更新用户
  public void updateUser(User user) {
    Connection connection = null;
    PreparedStatement statement = null;
    try {
      connection = getConnection();
      String sql = 'UPDATE user SET name = ?, age = ? WHERE id = ?';
      statement = connection.prepareStatement(sql);
      statement.setString(1, user.getName());
      statement.setInt(2, user.getAge());
      statement.setInt(3, user.getId());
      statement.executeUpdate();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        if (statement != null) {
          statement.close();
        }
        closeConnection(connection);
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  // 查询用户
  public List<User> getUsers() {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List<User> users = new ArrayList<>();
    try {
      connection = getConnection();
      String sql = 'SELECT * FROM user';
      statement = connection.prepareStatement(sql);
      resultSet = statement.executeQuery();
      while (resultSet.next()) {
        int id = resultSet.getInt('id');
        String name = resultSet.getString('name');
        int age = resultSet.getInt('age');
        User user = new User(id, name, age);
        users.add(user);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        if (resultSet != null) {
          resultSet.close();
        }
        if (statement != null) {
          statement.close();
        }
        closeConnection(connection);
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    return users;
  }
}

最后，我们可以使用 ajax 技术来实现前端与后端的交互，通过发送 HTTP 请求来调用后端的增删改查方法。

// 添加用户
function addUser(name, age) {
  $.ajax({
    url: 'user',
    type: 'POST',
    data: {
      name: name,
      age: age
    },
    success: function(response) {
      console.log(response);
    }
  });
}

// 删除用户
function deleteUser(id) {
  $.ajax({
    url: 'user/' + id,
    type: 'DELETE',
    success: function(response) {
      console.log(response);
    }
  });
}

// 更新用户
function updateUser(id, name, age) {
  $.ajax({
    url: 'user/' + id,
    type: 'PUT',
    data: {
      name: name,
      age: age
    },
    success: function(response) {
      console.log(response);
    }
  });
}

// 查询用户
function getUsers() {
  $.ajax({
    url: 'user',
    type: 'GET',
    success: function(response) {
      console.log(response);
    }
  });
}

以上就是利用 DAO 设计模式和 ajax 技术实现数据库表 user 的增删改查操作的完整代码。