课程管理系统基本搭建

拿到一个题目，关于数据的增删改查，要求在网页中实现

大体分为以下几个步骤：

1、连接数据库：

要连接数据库，首先得先建立一个数据库表，建立表头信息。然后在eclipse里进行操作。先建一个.java文件，将数据库连接，分为四步：1.加载驱动程序2.数据库连接字符串3.数据库登录名和密码4.最后关闭。代码如下：

package com.jaovo.msg.Util;

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

public class DBUtil {
    
    public  static  Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
            e.printStackTrace();
        }
        String user = "root";
        String password = "root";
        String url = "jdbc:mysql://localhost:3306/login? ?characterEncoding=utf-8&useSSL=true";
        Connection connection = null;
        try {
             connection = DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    
    public static void close(Connection connection ) {
        try {
            if (connection != null) {
                connection.close();
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void close(PreparedStatement preparedStatement ) {
        try {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void close(ResultSet resultSet ) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
}

2、然后再建一个新类，里面创建对象，属性设为private，然后分别建立set和get函数。

package com.jaovo.msg.model;

public class infor {
    private String name;
    private String teacher;
    private String address;
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getTeacher() {
        return teacher;
    }
    public void setTeacher(String teacher) {
        this.teacher = teacher;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    
    public infor() {}
    
    public infor(String name,String teacher,String address) {
        this.name = name;
        this.teacher = teacher;
        this.address = address;
    }

}

作为JavaBean，必须要注意的是，必须要有一个空的构造函数，要不然会报错，就像第26行public infor ( ) { };

3、新建一个操作类，专门写对数据库的操作，在里面写增删改查的函数，根据功能来写不同的函数，每个函数都得调用数据库的连接语句：

Connection connection = DBUtil.getConnection();

用到对数据库操作的语句：

              String sql = "select count(*) from class1 where name = ?";//选择语句

              sql = "insert into class1(name,teacher,address) value (?,?,?)";//插入语句

语句有很多种，基本用法也不尽相同。

同时还得新定义这两个语句

              PreparedStatement preparedStatement = null;

              ResultSet resultSet = null;

然后就是写函数体。

根据不同的要求写不同的函数。

package com.jaovo.msg.dao;

import javax.swing.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jaovo.msg.Util.DBUtil;
import com.jaovo.msg.model.infor;


public class StudentDaoImpl {

    public void add1(String name, String teacher,String address) {
    
        Connection connection = DBUtil.getConnection();
        
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            String sql = "insert into class1(name,teacher,addess) value (?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            preparedStatement.setString(2, teacher);
            preparedStatement.setString(3, address);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet);
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }

    }


    public void update(String name, String newname,String newteacher,String newaddress) {
        Connection connection = DBUtil.getConnection();
        
        String sql = "select count(*) from class1 where name = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                if (resultSet.getInt(1) > 0)
                {
                    System.out.println("开始修改");
                    sql = "update class1 set name=?,teacher=?,address=?  where name=?";
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setString(1, newname);
                    preparedStatement.setString(2, newteacher);
                    preparedStatement.setString(3, newaddress);
                    preparedStatement.setString(4, name);
                } preparedStatement.executeUpdate();
        }
            } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet);
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }

    }


    public void delete(String username) {
        Connection connection = DBUtil.getConnection();
        String sql = "delete from class1 where name = ?";
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }

    }
    
    public int add(String name,String teacher,String address) {
        
        Connection connection = DBUtil.getConnection();
        
        String sql = "select count(*) from class1 where name = ?";
        
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            
            resultSet = preparedStatement.executeQuery();
            
            System.out.println("hello");
            while(resultSet.next()) {
                if (resultSet.getInt(1) > 0) 
                {
                    
                    return 0;
                }
            
                else 
                {
                    sql = "insert into class1(name,teacher,address) value (?,?,?)";
                    preparedStatement = connection.prepareStatement(sql);
                    preparedStatement.setString(1, name);
                    preparedStatement.setString(2, teacher);
                    preparedStatement.setString(3, address);
                    preparedStatement.executeUpdate();
                    return 1;
                }
                }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            //e.printStackTrace();
            e.getMessage();
        }finally {
            
            DBUtil.close(resultSet);
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }
        return 0;
        
    }
    
    public int load(String name) {
        // TODO Auto-generated method stub
        
        Connection connection = DBUtil.getConnection();
        String sql = "select * from class1 where name = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println("课程名称：" + resultSet.getString("name"));
                System.out.println("任课教师：" + resultSet.getString("teacher"));
                System.out.println("上课地点：" + resultSet.getString("address"));
                return 1;
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet);
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }
        return 0;
    }
    public String load_del(String name) {
        // TODO Auto-generated method stub
        Connection connection = DBUtil.getConnection();
        String sql = "select * from class1 where name = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println("课程名称：" + resultSet.getString("name"));
                System.out.println("任课教师：" + resultSet.getString("teacher"));
                System.out.println("上课地点：" + resultSet.getString("address"));
                return ("课程名称：" + resultSet.getString("name")+"      任课教师：" + resultSet.getString("teacher")+"        上课地点：" + resultSet.getString("address"));
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet);
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }
        return "课程不存在！";
    }
    
    public String load_delete(String name) {
        // TODO Auto-generated method stub
        Connection connection = DBUtil.getConnection();
        //System.out.println(111);
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            String sql = "select * from class1 where name like '%"+name+"%'";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println("课程名称：" + resultSet.getString("name"));
                System.out.println("任课教师：" + resultSet.getString("teacher"));
                System.out.println("上课地点：" + resultSet.getString("address"));
                return ("课程名称：" + resultSet.getString("name")+"      任课教师：" + resultSet.getString("teacher")+"        上课地点：" + resultSet.getString("address"));
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet);
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }
        return "课程不存在！";
    }
    
    
    public List<infor> loadlist(String name,String teacher,String address) {
        // TODO Auto-generated method stub
        String sql = "select * from class1 where ";
        if (name != "") {
            sql += "name like '%" + name + "%'";
        }
        if (teacher != "") {
            sql += "teacher like '%" + teacher + "%'";
        }
        if (address != "") {
            sql += "classroom like '%" + address + "%'";
        }
        List<infor> list = new ArrayList<>();
        Connection connection = DBUtil.getConnection();
        //System.out.println(111);
        infor bean = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //String sql = "select * from class1 where name like '%"+name+"%'";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String name2 = resultSet.getString("name");
                String teacher2 = resultSet.getString("teacher");
                String address2 = resultSet.getString("address");
                bean = new infor(name2,teacher2,address2);
                list.add(bean);
                System.out.println("课程名称：" + resultSet.getString("name"));
                System.out.println("任课教师：" + resultSet.getString("teacher"));
                System.out.println("上课地点：" + resultSet.getString("address"));
                //return ("课程名称：" + resultSet.getString("name")+"      任课教师：" + resultSet.getString("teacher")+"        上课地点：" + resultSet.getString("address"));
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBUtil.close(resultSet);
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }
        return list;
    }
}

4、搭建界面

主要用的是jsp，先建立一个主页，展示操作，再分别建立增删改查的jsp：

然后在jsp里面进行传值，调用Java文件的函数，进而操作数据库。

这些jsp文件里并不是都是显示页面，有一些只是进行判断的页面，调用函数进行操作数据库。

这是主页面，分成了上左右三块，左边菜单栏，上边是名称，右边是各种操作界面及结果显示。

代码：

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>主页</title>
<link rel="stylesheet" href="css/page.css" />
<script type="text/javascript" src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/index.js"></script>
</head>

<frameset rows="20%,*">
<frame class="top" src="top.jsp">
<frameset cols="20%,*">
<frame src="main_left.jsp">
<frame src="main_right.jsp" name="main_right">
</frameset>
</frameset>
<body>

</body>
</html>

其他分界面以添加为例

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
    <%@ page import="com.jaovo.msg.dao.*" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
<form method="post" action="inputstuinfo_result.jsp">

<div align="center">
<h5>课程名称：<input name="input_name" type="text" placeholder="请输课程名称"></h5>
<h5>任课教师：<input name="input_teacher" type="text" placeholder="请输任课教师"></h5>
<h5>上课地点：<input name="input_address" type="text" placeholder="请输上课地点"></h5>
<input name="submit" type="submit" >
</div>
</form>
</body>
</html>

界面：

其他只是界面处理不同，样式一样。

大体上就是这样的步骤，其他具体实现操作已略去。