idea连接数据库-526互联

首先点击右边的数据库，然后数据源，选择mysql，输入账号，密码，测试链接，确定。

1.创建数据库

CREATE DATABASE database_name;

2.选择数据库

use database_name;

3.创建一个表

create table biao_name();

比如创建一个表名为student的表，里面有学号，姓名，性别

这个表就建好了。

实现增删改查

1.增

增加学生信息

做了一个增加的界面，包括学生的学号，姓名，性别

这是addStudent.jsp界面的代码

<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%
    String studentId = request.getParameter("studentId");
    String studentName = request.getParameter("studentName");
    String studentGender = request.getParameter("studentGender");

    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school", "root", "123456");//这里school就是刚才的数据库，后面输入账号密码
        pstmt = conn.prepareStatement("INSERT INTO student (id, name, sex) VALUES (?, ?, ?)");//这里student就是创建的表，然后添加的三个选项id,name,sex
        pstmt.setString(1, studentId);
        pstmt.setString(2, studentName);
        pstmt.setString(3, studentGender);
        pstmt.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (pstmt != null) {
            pstmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
%>

　2.删

删除学生信息

还是做一个删除界面

deleteStudent.jsp界面代码是

<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%
    String studentId = request.getParameter("studentId");

    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school", "root", "123456");
        pstmt = conn.prepareStatement("DELETE FROM student WHERE id = ?");//from就是从student这个表里查id
        pstmt.setString(1, studentId);
        pstmt.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (pstmt != null) {
            pstmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
%>

3.改

修改学生信息

<%
    String studentId = request.getParameter("studentId");
    String name = request.getParameter("name");
    String gender = request.getParameter("gender");

    // 更新学生信息到数据库
    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school", "root", "123456");
        String sql = "UPDATE student SET name=?, sex=? WHERE id=?";//通过id查找有没有这个学生，然后修改学生信息
        stmt = conn.prepareStatement(sql);
        stmt.setString(1, name);
        stmt.setString(2, gender);
        stmt.setString(3, studentId);
        int rows = stmt.executeUpdate();
        if (rows > 0) {
            System.out.println("学生信息更新成功！");
        } else {
            System.out.println("学生信息更新失败！");
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
%>

4.查询

<%@ page import="java.sql.*" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>查询学生信息</title>
</head>
<body>
<h1>查询学生信息</h1>
<form action="search.jsp" method="post">
    <label for="studentId">请输入学生学号：</label>
    <input type="text" id="studentId" name="studentId">
    <input type="submit" value="查询">
</form>

<%
    String studentId = request.getParameter("studentId");
    if (studentId != null) {
        // 查询数据库中的学生信息
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school", "root", "123456");
            String sql = "SELECT * FROM student WHERE id=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, studentId);
            rs = stmt.executeQuery();
            if (rs.next()) {
                String name = rs.getString("name");
                String gender = rs.getString("gender");
%>
<h2>学生信息：</h2>
<p>学号：<%= studentId %></p>
<p>姓名：<%= name %></p>
<p>性别：<%= gender %></p>
<%
} else {
%>
<p>未找到学号为<%= studentId %>的学生信息</p>
<%
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
%>
</body>
</html>

开始是一个输入学号的表单，输入学号后点击查询按钮，然后根据学号查询数据库中的学生信息，并显示在页面上。如果查询到了学生信息，就会在页面上显示学号、姓名和性别信息；如果未找到对应学号的学生信息，则会显示相应的提示信息