java创建数据库表步骤 java web创建一个数据库

转载

墨染青衫 2023-08-07 10:22:57

文章标签 java创建数据库表步骤 java 开发工具数据库 sql 文章分类 Java 后端开发

一、相关的软件下载和环境配置

1、下载并配置JDK。

2、下载eclipse。

3、下载并配置apache-tomcat（服务器）。

4、下载MySQL（数据库）。

5、下载Navicat for MySQL（数据库可视化工具），方便对数据库的操作。

6、下载jdbc用来实现eclipse中的项目与数据库实现连接。

---以上可在网上查询教程

二、新建web项目

java创建数据库表步骤 java web创建一个数据库_sql

java创建数据库表步骤 java web创建一个数据库_开发工具_02

java创建数据库表步骤 java web创建一个数据库_开发工具_03

然后点击next，

java创建数据库表步骤 java web创建一个数据库_开发工具_04

继续点next

java创建数据库表步骤 java web创建一个数据库_sql_05

勾选Generate web.xml

java创建数据库表步骤 java web创建一个数据库_开发工具_06

然后finish

2.新建两个包

java创建数据库表步骤 java web创建一个数据库_java_07

然后User就是我们封装的数据

package com.better.bean;

public class User {
private int userid;
private String username;
private String password;
public User() {
    
}
public int getUserid() {
    return userid;
}
public void setUserid(int userid) {
    this.userid = userid;
}
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;
}

}

建立类文件时一样

而servlet建立时不一样，如下

java创建数据库表步骤 java web创建一个数据库_java_08

然后servlet代码如下

addservlet.java

package com.better.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class addservlet
 */
@WebServlet("/addservlet")
public class addservlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public addservlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
     *      response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        String username = request.getParameter("username");
        String psword = request.getParameter("password");
        String url = "jdbc:mysql://localhost:3306/demodb?&useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "9527";
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            response.getWriter().print("加载驱动失败");
        } catch (SQLException e) {
            response.getWriter().print("连接数据库失败");
        }
        try {
            String sql = "INSERT INTO user(username,password) VALUES (?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, psword);
            int row = ps.executeUpdate();
            // 判断是否更新成功
            if (row > 0)
                // 更新成输出信息
                response.getWriter().print("成功添加了 " + row + "条数据！");
        } catch (SQLException e) {
            response.getWriter().print("注册用户信息失败");
        }
        try {
            if (ps != null) {
                ps.close();
                ps = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        } catch (Exception e) {
            response.getWriter().print("数据库关闭异常");
        }
        response.sendRedirect("main.jsp");
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
     *      response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

然后你要配置web.xml文件

java创建数据库表步骤 java web创建一个数据库_sql_09

点开它

<servlet>
    <description></description>
    <display-name>addservlet</display-name>
    <servlet-name>addservlet</servlet-name>
    <servlet-class>com.better.servlet.addservlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>addservlet</servlet-name>
    <url-pattern>/com.better.servlet/addservlet</url-pattern>
  </servlet-mapping>

把这段代码插进去

然后就可以使用这个servlet了

接着是deleteservlet.java、searchservlet.java、updateservlet.java、checkservlet.java

package com.better.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class updateservlet
 */
@WebServlet("/updateservlet")
public class updateservlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public updateservlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        String username = request.getParameter("username");
        String psword = request.getParameter("password");
        String url = "jdbc:mysql://localhost:3306/demodb?&useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "9527";
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            response.getWriter().print("加载驱动失败");
        } catch (SQLException e) {
            response.getWriter().print("连接数据库失败");
        }
        try {
            String sql = "UPDATE user SET password=? WHERE username = ? ";
            ps=conn.prepareStatement(sql);
            ps.setString(1, psword);
            ps.setString(2, username);
            int row = ps.executeUpdate();
            // 判断是否更新成功
            if (row > 0)
                // 更新成输出信息
                response.getWriter().print("成功修改了 " + row + "条数据！");
        } catch (SQLException e) {
            response.getWriter().print("修改密码失败");
        }
        try {
            if (ps != null) {
                ps.close();
                ps = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        } catch (Exception e) {
            response.getWriter().print("数据库关闭异常");
        }
        response.sendRedirect("main.jsp");
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

package com.better.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class checkservlet
 */
@WebServlet("/checkservlet")
public class checkservlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public checkservlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        String adminname = request.getParameter("adminname");
        String adminword = request.getParameter("adminword");
        String url = "jdbc:mysql://localhost:3306/demodb?&useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "9527";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            response.getWriter().print("加载驱动失败");
        } catch (SQLException e) {
            response.getWriter().print("连接数据库失败");
        }
        try{
            String sql="SELECT * FROM admin WHERE adminname = ? and adminword=? "; 
            ps=conn.prepareStatement(sql);
            ps.setString(1, adminname);
            ps.setString(2, adminword);
            rs=ps.executeQuery();     //将sql语句传至数据库，返回的值为一个字符集用一个变量接收 
            if(rs.next()){    //next（）获取里面的内容
                request.getRequestDispatcher("main.jsp") 
                .forward(request, response); 
            }
            else request.getRequestDispatcher("login.jsp") 
            .forward(request, response); 
        }catch(SQLException e){    
            response.getWriter().print("数据库操作异常");
        }
        try{
            if(rs!=null){
                rs.close();
                rs=null;
            }
            if(ps!=null){
                ps.close();
                ps=null;
            }
            if(conn!=null){
                conn.close();
                conn=null;
            }
            
        }catch(Exception e){
            response.getWriter().println("数据库关闭异常");
            
        }
        response.getWriter().append("Served at: ").append(request.getContextPath());
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

package com.better.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class deleteservlet
 */
@WebServlet("/deleteservlet")
public class deleteservlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public deleteservlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        String username = request.getParameter("username");
        String url = "jdbc:mysql://localhost:3306/demodb?&useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "9527";
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            response.getWriter().print("加载驱动失败");
        } catch (SQLException e) {
            response.getWriter().print("连接数据库失败");
        }
        try {
            String sql = "DELETE FROM user WHERE username=?";
            ps=conn.prepareStatement(sql);
            ps.setString(1, username);
            int row = ps.executeUpdate();
            // 判断是否更新成功
            if (row > 0)
                // 更新成输出信息
                response.getWriter().print("成功删除了 " + row + "条数据！");
        } catch (SQLException e) {
            response.getWriter().print("删除用户失败");
        }
        try {
            if (ps != null) {
                ps.close();
                ps = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        } catch (Exception e) {
            response.getWriter().print("数据库关闭异常");
        }
        //重定向到main页面
        response.sendRedirect("main.jsp");
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

package com.better.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class searchservlet
 */
@WebServlet("/searchservlet")
public class searchservlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public searchservlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        String username = request.getParameter("username");
        String url = "jdbc:mysql://localhost:3306/demodb?&useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "9527";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            response.getWriter().print("加载驱动失败");
        } catch (SQLException e) {
            response.getWriter().print("连接数据库失败");
        }
        try{
            String sql="SELECT * FROM user WHERE username = ? "; 
            ps=conn.prepareStatement(sql);
            ps.setString(1, username); 
            rs=ps.executeQuery();     //将sql语句传至数据库，返回的值为一个字符集用一个变量接收 
            while(rs.next()){    //next（）获取里面的内容
                response.getWriter().println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
                                 //getString（n）获取第n列的内容
                                    //数据库中的列数是从1开始的
            }
        }catch(SQLException e){
            
            response.getWriter().print("查询用户失败");
        }
        try{
            if(rs!=null){
                rs.close();
                rs=null;
            }
            if(ps!=null){
                ps.close();
                ps=null;
            }
            if(conn!=null){
                conn.close();
                conn=null;
            }
            
        }catch(Exception e){
            response.getWriter().println("数据库关闭异常");
            
        }
        
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        doGet(request, response);
    }

}

然后是完整的web.xml文件配置

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd" id="WebApp_ID" version="4.0">
  <display-name>Better</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  <servlet>
    <description></description>
    <display-name>addservlet</display-name>
    <servlet-name>addservlet</servlet-name>
    <servlet-class>com.better.servlet.addservlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>addservlet</servlet-name>
    <url-pattern>/com.better.servlet/addservlet</url-pattern>
  </servlet-mapping>
  <servlet>
    <description></description>
    <display-name>updateservlet</display-name>
    <servlet-name>updateservlet</servlet-name>
    <servlet-class>com.better.servlet.updateservlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>updateservlet</servlet-name>
    <url-pattern>/com.better.servlet/updateservlet</url-pattern>
  </servlet-mapping>
  <servlet>
    <description></description>
    <display-name>deleteservlet</display-name>
    <servlet-name>deleteservlet</servlet-name>
    <servlet-class>com.better.servlet.deleteservlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>deleteservlet</servlet-name>
    <url-pattern>/com.better.servlet/deleteservlet</url-pattern>
  </servlet-mapping>
  <servlet>
    <description></description>
    <display-name>searchservlet</display-name>
    <servlet-name>searchservlet</servlet-name>
    <servlet-class>com.better.servlet.searchservlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>searchservlet</servlet-name>
    <url-pattern>/com.better.servlet/searchservlet</url-pattern>
  </servlet-mapping>
  <servlet>
    <description></description>
    <display-name>checkservlet</display-name>
    <servlet-name>checkservlet</servlet-name>
    <servlet-class>com.better.servlet.checkservlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>checkservlet</servlet-name>
    <url-pattern>/com.better.servlet/checkservlet</url-pattern>
  </servlet-mapping>
</web-app>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>登录页面</title>
</head>
<body
    background="file:///D:/eclipse-workplace/Better/WebContent/image/星空.jpg">
    <form action="checkservlet" method=post>
        <center>
            <table>
                <tr>
                    <td><font color="white">输入管理员名：</font></td>
                    <td><INPUT type=txt name=adminname></td>
                </tr>
                <tr>
                    <td><font color="white">输入管理员密码：</font></td>
                    <td><INPUT type=password name=adminword></td>
                </tr>
                <tr colspan=2>
                    <td><INPUT type=submit value=登录></td>
                </tr>
            </table>
        </center>
    </form>
</body>
</html>

main.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>主界面</title>
</head>
<body>
<center><h1>主菜单</h1><center>
<table><center>
<h2 align="center"> 
  <a href="Add.jsp">添加用户</a> 
</h2> 
<h2 align="center"> 
  <a href="Delete.jsp">删除用户</a> 
</h2> 
<h2 align="center"> 
  <a href="Update.jsp">修改用户</a> 
</h2> 
<h2 align="center"> 
  <a href="Search.jsp">查询用户</a> 
</h2> 
</table></center>
</body>
</html>

Add.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加</title>
</head>
<script type="text/javascript">
         function check(form) {
              if(form.username.value=='') {
                    alert("请输入用户名!");
                    form.username.focus();
                    return false;
               }
               if(form.password.value==''){
                    alert("请输入登录密码!");
                    form.password.focus();
                    return false;
                 }
                 return true;
         }
</script>
<body>
<form action="addservlet" method="post">
    用户名:  <input type=text name="username" size="18" value="" ><br>
    登录密码:<input type="password" name="password" size="18" value=""/>      
           <input type=submit name="submit1" value="注册" οnclick="return check(this.form)">  
</form>
</body>
</html>

Delete.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>删除</title>
</head>
<script type="text/javascript">
         function check(form) {
              if(form.username.value=='') {
                    alert("请输入用户名!");
                    form.username.focus();
                    return false;
               }
               
         }
</script>
<body>
<form action="deleteservlet" method="post">
    想删除的用户名:  <input type=text name="username" size="18" value="" ><br>     
           <input type=submit name="submit1" value="删除" οnclick="return check(this.form)">  
</form>
</body>
</html>

Update.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>修改</title>
</head>
<script type="text/javascript">
         function check(form) {
              if(form.username.value=='') {
                    alert("请输入用户名!");
                    form.username.focus();
                    return false;
               }
               if(form.password.value==''){
                    alert("请输入新密码!");
                    form.password.focus();
                    return false;
                 }
                 return true;
         }
</script>
<body>
<form action="updateservlet" method="post">
    用户名:  <input type=text name="username" size="18" value="" ><br>
   新密码:<input type="password" name="password" size="18" value=""/>      
           <input type=submit name="submit1" value="修改" οnclick="return check(this.form)">  
</form>
</body>
</html>

Search.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>查找</title>
</head>
<script type="text/javascript">
         function check(form) {
              if(form.username.value=='') {
                    alert("请输入用户名!");
                    form.username.focus();
                    return false;
               }
               
         }
</script>
<body>
<form action="searchservlet" method="post">
    想查找的用户名:  <input type=text name="username" size="18" value="" ><br>     
           <input type=submit name="submit1" value="查找" οnclick="return check(this.form)">  
</form>
</body>
</html>

再就是建一个数据库两张表

java创建数据库表步骤 java web创建一个数据库_java_10