今日学习成果

1.安装MySQL，安装Navicat工具
dos模式下输入MySQL -uroot -p，输入密码即可进入数据库

Navicat可视工具
连接MySQL，新建数据库

完成后双击打开
在新建的表中新建字段，创建用户表、角色表、菜单表和权限表，并设置相应的类型、长度、键值

其中角色名需要关联外键。（外键：从表（sub）的某列引用（ref）主表（main）的某列的值。从表也叫外键表，主表也叫主键表、外表，列也叫字段，能保持数据的一致性、完整性）

2.实现用户的增删改查
在sservlet目录下新建userservlet类，
package com.dpb.servlet;

import javax.servlet.http.HttpServlet;

public class UserServlet extends HttpServlet {
}
并到web.xml中进行相应配置
<servlet>
<servlet-name>loginServlet</servlet-name>
<servlet-class>LoginServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>loginServlet</servlet-name>
<url-pattern>/loginServlet</url-pattern>
</servlet-mapping>

<servlet>
<servlet-name>userServlet</servlet-name>
<servlet-class>com.dpb.servlet.UserServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>userServlet</servlet-name>
<url-pattern>/userServlet</url-pattern>
</servlet-mapping>
在UserServlet中利用doPost实现查询并返回用户信息
public class User {

private String userId;
private String userName;
private String userPassword;
private Integer roleId;
private String roleName;
}
这里记得要在最后加上rolename，因为在实际运用中，调取的是用户名称而不是用户ID
Generate中设置get和set方法

public String getUserId() {
    return userId;
}

public void setUserId(String userId) {
    this.userId = userId;
}

public String getUserName() {
    return userName;
}

public void setUserName(String userName) {
    this.userName = userName;
}

public String getUserPassword() {
    return userPassword;
}

public void setUserPassword(String userPassword) {
    this.userPassword = userPassword;
}

public Integer getRoleId() {
    return roleId;
}

public void setRoleId(Integer roleId) {
    this.roleId = roleId;
}

public String getRoleName() {
    return roleName;
}

public void setRoleName(String roleName) {
    this.roleName = roleName;
}
在userservlet中调用list

List<User> list = null;


服务层的实现

新建UserService，设置为Interface类

package com.dpb.servlet.service;

import com.dpb.servlet.vo.User;

import java.util.List;

public interface UserService {
    
    List<User> queryUsers();
}
新建impl类完成实现

调用dao

package com.dpb.servlet.dao;

import com.dpb.servlet.vo.User;

import java.util.List;

public interface UserDao {

    List<User> queryUsers();
}
在userServlet里关联dao

UserDao userDao = null;

新建UserDaoimpl类，实现数据返回

UserDao userDao = new UserDaoimpl();
servlet中同样设置

UserService userService = new UserServiceimpl();
List<User> list = userService.queryUsers();

在userdao中的具体实践
连接数据库并加载驱动

package com.dpb.servlet.bd;

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

public class MySqlDB {
    private final static String DB_DRIVER ="";
    private final static String DB_URL ="";
    private final static String DB_USER ="";
    private final static String DB_PASSWORD ="";

    public Connection getConn() {
        Connection conn = null;
        try {
            Class.forName(DB_DRIVER);
            conn = DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD)
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }
}

数据库的查询

MySqlDB mySqlDB = new MySqlDB();
Connection conn = mySqlDB.getConn();
Statement stmt = null;
ResultSet rs = null;
try {
    stmt = conn.createStatement();
    rs = stmt.executeQuery("")