java+jsp+sqlserver实现简单的增删改查操作 连接数据库代码

1，网站系统开发需要掌握的技术

（1）网页设计语言，html语言css语言等

（2）Java语言

（3）数据库

（4）等

2，源程序代码

（1） 连接数据库代码

package com.jaovo.msg.Util;
import java.sql.*;
public class DBUtil {
    
    public  static  Connection getConnection() {
        try {
            //1 加载驱动
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
        } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        String user1 = "sa";
        String password = "123456";
        String url = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=user";
        Connection connection = null;
        try {
            //2 创建链接对象connection
             connection = DriverManager.getConnection(url,user1,password);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return connection;
    }
    
    //关闭资源的方法
    public static void close(Connection connection ) {
        try {
            if (connection != null) {
                connection.close();
            }
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public static void close(PreparedStatement preparedStatement ) {
        try {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public static void close(ResultSet resultSet ) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
}

（2）实现增删改查的源代码

package com.jaovo.msg.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


import com.jaovo.msg.Util.DBUtil;
import com.jaovo.msg.Util.UserException;
import com.jaovo.msg.model.User;

import sun.net.www.content.text.plain;

public class UserDaoImpl implements IUserDao {

    public void add(User user) {
        Connection connection = DBUtil.getConnection();
        try {
            String sql="insert into nlc1(id,username,password,nickname)values('"+user.getId()+"','"+user.getUsername()+"','"+user.getPassword()+"','"+user.getNickname()+"')";
            Statement stmt=connection.createStatement();
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection);
        }
        
    }

    @Override
    public void delete(User user) {
        Connection connection = DBUtil.getConnection();
        //System.out.println("执行了");
        try {
            Statement stmt=connection.createStatement();
            String sql = "delete from nlc1 where id = "+user.getId();
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection);
        }
        
    }

    @Override
    public void update(User user) {
        Connection connection = DBUtil.getConnection();
        try {
            //String n=user.getId1();
            Statement stmt=connection.createStatement();
            String username=user.getUsername();
            String id=user.getId();
            String password=user.getPassword();
            String nickname=user.getNickname();
            String id1=user.getId1();
            String sql="update nlc1 set id='"+id+"',username='"+username+"',password='"+password+"',nickname='"+nickname+"' where id="+user.getId1();
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            //DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }
    }
    public void check(User user) {                
        Connection connection = DBUtil.getConnection();
        Statement stmt;
        try {
            stmt = connection.createStatement();
            ResultSet rs=stmt.executeQuery("SELECT * FROM nlc1 where id="+user.getId());
            //while(rs.next())
            //System.out.println(rs.getString("id")+"	"+rs.getString("username")+"	"+rs.getString("password")+"	"+rs.getString("nickname"));
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    public List<User> load() {
        Connection connection = DBUtil.getConnection();
        //准备sql语句
        String sql = "select * from nlc1 ";
        //创建语句传输对象
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //集合中只能放入user对象
        List<User> users = new ArrayList<User>();
        User user = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                user = new User();
                user.setId(resultSet.getString("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setNickname(resultSet.getString("nickname"));
                users.add(user);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBUtil.close(resultSet);
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }
        return  users;
    }

    @Override
    public User load(int id) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public User load(String username) {
        // TODO Auto-generated method stub
        Connection connection = DBUtil.getConnection();
        //准备sql语句
        String sql = "select * from nlc1  where username = ?";
        //创建语句传输对象
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                user = new User();
                user.setId(username);
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setNickname(resultSet.getString("nickname"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBUtil.close(resultSet);
            DBUtil.close(preparedStatement);
            DBUtil.close(connection);
        }
        return  user;
    }
    

}

（3）登录界面代码login.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=ISO-8859-1">
<title>登录界面</title>
</head>
<body background="D:图片动漫20150422H2607_fKHLB.jpeg">
<%if(request.getAttribute("error1")!=null){ %>
<h4 >用户名或密码不能为空</h4>
<%} %>
<%if(request.getAttribute("error2")!=null){ %>
<h4>账号或密码错误</h4>
<%} %>
<form action="enter.jsp" method="get">
<table align="center" border="4" width="400">
            <h4 align="center">登录界面</h4>
            <tr>
                <td>用户名称 : </td>
                <td>
                    <input type="text" name="username" />
                </td>
            </tr>
                <tr>
                <td>用户密码:</td>
                <td>
                    <input type="password" name="password" />
                </td>
            </tr>
            <script>
            function a(){window.open("addInput.jsp",'_blank')}
            </script>
            <script>
            function b(){window.open("deleteInput.jsp",'_blank')}
            </script>
            <tr align="center">
                <td colspan="2">
                    <input type="submit" value="登录" />
                    <input type="button" value="注册"onclick="a()"/>
<!--                     <input type="button" value="修改密码"onclick="b()"/> -->
                    <input type="reset" value="重置" />
                </td>
            </tr>
        </table>
        </form>
</body>
</html>

<%@page import="com.jaovo.msg.Util.UserException"%>
<%@page import="com.jaovo.msg.dao.UserDaoImpl"%>
<%@page import="com.jaovo.msg.model.User"%>
<%@ 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=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
    <% 
    String username = request.getParameter("username");
    String password = request.getParameter("password");
    User user = new User();
    UserDaoImpl userDao = new UserDaoImpl();
    user.setUsername(username);
    user.setPassword(password);
    if(username.equals(user.getUsername())&&password.equals(user.getPassword())){
    %>
      <h4 align="center">登录成功</h4>
    <div ><div style="text-align:center;">
      <a href="login.jsp">返回登录界面</a> 
      <a href="list.jsp">查看所有用户</a>
    </div>
      <%
      }
      %>
    <%
    if(username == null || "".equals(username.trim())){
        request.setAttribute("error1", "用户名不能为空");
    
%>
    <jsp:forward page="login.jsp"></jsp:forward>
<%
}
%>
    <%
    user=userDao.load(username);
    if(user==null){
        request.setAttribute("error2", "用户名不存在或密码错误");
        %>
        <jsp:forward page="login.jsp"></jsp:forward>
    <%
    }
    %>
</body>
</html>

（4）增删改查界面源代码

<%@ 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>
    <title>用户注销页面</title>
</head>
<body background="D:图片动漫20150422H2607_fKHLB.jpeg">
    <%=request.getAttribute("error") %>
    <form action="delete.jsp" method="get">
    <h4 align="center">删除界面</h4>
        <table align="center" border="4" width="400">

            <tr>
                <td>请输入要删除的用户id:</td>
                <td>
                    <input type="text" name="id" />
                </td>
            </tr>
            <tr align="center">
                <td colspan="2">
                    <input type="submit" value="删除" />
                    <input type="reset" value="重置" />
                </td>
            </tr>
        </table>
    </form>
</body>
</html>

<%@page import="com.jaovo.msg.Util.UserException"%>
<%@page import="com.jaovo.msg.dao.UserDaoImpl"%>
<%@page import="com.jaovo.msg.model.User"%>
<%@ 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>
    <title>删除页面</title>
</head>
<body background="D:图片动漫20150422H2607_fKHLB.jpeg" >
<%
    String id=request.getParameter("id");
    if(id == null || "".equals(id.trim())){
        request.setAttribute("error", "用户名不能为空");
%>
     <jsp:forward page="deleteInput.jsp"></jsp:forward> 
<%

}  
    User user = new User();
    user.setId(id);
    UserDaoImpl userDao = new UserDaoImpl();
    try{
        userDao.delete(user);
%>


    <h4 align="center">注销成功</h4>
    <div style="text-align:center;">
    <a  href="addInput.jsp">注册用户</a>
    <a href="deleteInput.jsp">删除用户</a>
    <a href="updataInput.jsp">修改用户</a>
<!--     <a href="checkInput.jsp">查询用户</a> -->
    <a href="list.jsp">查看列表信息</a>
    <a href=>
    </div>
<%
    }catch(UserException e){
%>
    <h2 style="color:red ; font-size:50px">发生错误 : <%=e.getMessage() %></h2>
    <%
    }
    %>
    </body>
</html>

<%@ 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>
    <title>登录注册</title>
</head>
<body background="D:图片动漫57Z58PICJBv_1024.jpg" >
<%=request.getAttribute("error") %>
    <form action="add.jsp" method="get">
        <table align="center" border="4" width="400">
            <h4 align="center">注册界面</h4>
            <tr>
                <td>用户id : </td>
                <td>
                    <input type="text" name="id" />
                </td>
            </tr>
            <tr>
                <td>用户名称 : </td>
                <td>
                    <input type="text" name="username" />
                </td>
            </tr>
                <tr>
                <td>用户密码:</td>
                <td>
                    <input type="password" name="password" />
                </td>
            </tr>
            <tr>
                <td>用户昵称:</td>
                <td>
                    <input type="text" name="nickname" />
                </td>
            </tr>
            <tr align="center">
                <td colspan="2">
                    <input type="submit" value="注册" />
                    <input type="reset" value="重置" />
                </td>
            </tr>
        </table>
    </form>
</body>
</html>

<%@page import="com.jaovo.msg.Util.UserException"%>
<%@page import="com.jaovo.msg.dao.UserDaoImpl"%>
<%@page import="com.jaovo.msg.model.User"%>
<%@ 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>
    <title>注册</title>
</head>
<body background="D:图片动漫57Z58PICJBv_1024.jpg" >
<%
    //接收客户端传递过来的参数
    String id = request.getParameter("id");
    String username = request.getParameter("username");
    String password = request.getParameter("password");
    String nickname = request.getParameter("nickname");
    if(username == null || "".equals(username.trim())){
        request.setAttribute("error", "用户名不能为空");
    
%>
    <jsp:forward page="addInput.jsp"></jsp:forward>
<%
}   User user = new User();
    user.setId(id);
    user.setUsername(username);
    user.setPassword(password);
    user.setNickname(nickname);
    
    UserDaoImpl userDao = new UserDaoImpl();
    try{
    userDao.add(user);
%>

    <h2 align="center">注册成功</h2>

    <div style="text-align:center;">
    <a  href="addInput.jsp">注册用户</a>
    <a href="deleteInput.jsp">删除用户</a>
    <a href="updataInput.jsp">修改用户</a>
<!--     <a href="checkInput.jsp">查询用户</a> -->
    <a href="list.jsp">查看列表信息</a>
    </div>

<%
    }catch(UserException e){
%>
    <h2 style="color:red ; font-size:50px">发生错误 : <%=e.getMessage() %></h2>
    <%
    }
    %>
    </body>
</html>

<%@ 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>
    <title>用户修改页面</title>
</head>
<body background="D:图片动漫20150422H2607_fKHLB.jpeg">
    <%=request.getAttribute("error") %>
    <form action="updata.jsp" method="get">
    <h4 align="center">修改界面</h4>
        <table align="center" border="4" width="400">
            <tr>
                <td>要修改的用户id : </td>
                <td>
                    <input type="text" name="id1" />
                </td>
            </tr>
            <tr>
                <td>修改后的用户id : </td>
                <td>
                    <input type="text" name="id" />
                </td>
            </tr>
            <tr>
                <td>修改后的用户name : </td>
                <td>
                    <input type="text" name="username" />
                </td>
            </tr>
            <tr>
                <td>修改后的用户password : </td>
                <td>
                    <input type="password" name="password" />
                </td>
            </tr>
            <tr>
                <td>修改后的用户nickname : </td>
                <td>
                    <input type="text" name="nickname" />
                </td>
            </tr>
            <tr align="center">
                <td colspan="2">
                    <input type="submit" value="修改" />
                    <input type="reset" value="重置" />
                </td>
            </tr>
        </table>
    </form>
</body>
</html>

<%@page import="com.jaovo.msg.Util.UserException"%>
<%@page import="com.jaovo.msg.dao.UserDaoImpl"%>
<%@page import="com.jaovo.msg.model.User"%>
<%@ 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>
    <title>修改页面</title>
</head>
<body background="D:图片动漫20150422H2607_fKHLB.jpeg">
<%
    //接收客户端传递过来的参数
    String id1 = request.getParameter("id1");
    String id = request.getParameter("id");
    String username = request.getParameter("username");
    String password = request.getParameter("password");
    String nickname = request.getParameter("nickname");
if(id == null || "".equals(id.trim())){
    request.setAttribute("error", "id不能为空");
%>
<%--     <jsp:forward page="updataInput.jsp"></jsp:forward> --%>
<%

} 
   User user = new User();
    user.setId1(id1);
    user.setId(id);
    user.setUsername(username);
    user.setPassword(password);
    user.setNickname(nickname);
    UserDaoImpl userDao = new UserDaoImpl();
    try{
    userDao.update(user);
%>

<%  
%>

    <h4 align="center">用户修改成功</h4>
    <div style="text-align:center;">
    <a  href="addInput.jsp">注册用户</a>
    <a href="deleteInput.jsp">删除用户</a>
    <a href="updataInput.jsp">修改用户</a>
<!--     <a href="checkInput.jsp">查询用户</a> -->
    <a href="list.jsp">查看列表信息</a>
    </div>
<%
    //userDao.add(user);

    }catch(UserException e){
%>
    <h2 style="color:red ; font-size:50px">发生错误 : <%=e.getMessage() %></h2>
    <%
    }
    %>
    </body>
</html>

<%@page import="com.jaovo.msg.Util.UserException"%>
<%@page import="com.jaovo.msg.dao.UserDaoImpl"%>
<%@page import="com.jaovo.msg.model.User"%>
<%@page import="java.util.List" %>
<%@ 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=ISO-8859-1">
<title>列表</title>
</head>
<body>
<%
    UserDaoImpl userDao = new UserDaoImpl();
    List<User> users = userDao.load();
%>

<body>

    <table align="center" border="1" width="500">
        <tr>
            <td>用户编号</td>
            <td>用户名称</td>
            <td>用户密码</td>
            <td>用户昵称</td>
        </tr>
        <%
            for( User user : users ){
        %>
        <tr>
            <td> <%=user.getId() %></td>
            <td> <%=user.getUsername() %></td>
            <td> <%=user.getPassword() %></td>
            <td> <%=user.getNickname() %></td>
            <td> <a href="deleteInput.jsp ">删除</a></td>
            
        </tr>
        <%
            }
        %>
    </table>
</body>
</body>
</html>

3，运行结果截图

 

4，这门课的希望和自己的目标，列出计划每周花费在这门课上的时间

希望：希望学习完这门课之后能够熟练写出一个网站系统，尤其是在网站设计上

目标：熟练写出一个网站系统

计划：在完成其它课程作业的其余时间都扑身于这门课上