数据库小学期六
连接数据库的代码
package DBUtil; import java.sql.*; /** * 数据库连接工具 * @author Hu * */ public class DBUtil { public static String url = "jdbc:mysql://localhost:3306/shujuku?serverTimezone=UTC"; public static String user = "root"; public static String password = "152486"; public static Connection getConn () { Connection conn = null; try { Class.forName("com.mysql.cj.jdbc.Driver");//加载驱动 conn = DriverManager.getConnection(url, user, password); } catch (Exception e) { e.printStackTrace(); } return conn; } /** * 关闭连接 * @param state * @param conn */ public static void close (PreparedStatement preparedState, Connection conn) { if (preparedState != null) { try { preparedState.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close (ResultSet rs, PreparedStatement preparedState, Connection conn) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (preparedState != null) { try { preparedState.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 关闭连接 * @param state * @param conn */ public static void close (Statement state, Connection conn) { if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close (ResultSet rs, Statement state, Connection conn) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (state != null) { try { state.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] args) throws SQLException { Connection conn = getConn(); PreparedStatement preparedStatement = null; ResultSet rs = null; String sql ="select * from line1"; preparedStatement = conn.prepareStatement(sql); rs = preparedStatement.executeQuery(); if(rs.next()){ System.out.println("数据库为空"); } else{ System.out.println("数据库不为空"); } } }
登录界面
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%> <%@ page import="com.lero.model.Admin" %> <%@ page import="com.lero.model.DormManager" %> <%@ page import="com.lero.model.Student" %> <% if(request.getAttribute("user")==null){ String userName=null; String password=null; String userType=null; String remember=null; Cookie[] cookies=request.getCookies(); for(int i=0;cookies!=null && i<cookies.length;i++){ if(cookies[i].getName().equals("dormuser")){ userName=cookies[i].getValue().split("-")[0]; password=cookies[i].getValue().split("-")[1]; userType=cookies[i].getValue().split("-")[2]; remember=cookies[i].getValue().split("-")[3]; } } if(userName==null){ userName=""; } if(password==null){ password=""; } if(userType==null){ userType=""; } else if("admin".equals(userType)){ pageContext.setAttribute("user", new Admin(userName,password)); pageContext.setAttribute("userType", 1); } else if("dormManager".equals(userType)) { pageContext.setAttribute("user", new DormManager(userName,password)); pageContext.setAttribute("userType", 2); } else if("student".equals(userType)) { pageContext.setAttribute("user", new Student(userName,password)); pageContext.setAttribute("userType", 3); } if("yes".equals(remember)) { pageContext.setAttribute("remember", 1); } } %> <html lang="zh"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>宿舍管理系统登录</title> <link href="${pageContext.request.contextPath}/bootstrap/css/bootstrap.css" rel="stylesheet"> <link href="${pageContext.request.contextPath}/bootstrap/css/bootstrap-responsive.css" rel="stylesheet"> <script src="${pageContext.request.contextPath}/bootstrap/js/jQuery.js"></script> <script src="${pageContext.request.contextPath}/bootstrap/js/bootstrap.js"></script> <script type="text/javascript"> function checkForm() { var userName = document.getElementById("userName").value; var password = document.getElementById("password").value; var userTypes = document.getElementsByName("userType"); var userType = null; for(var i=0;i<userTypes.length;i++) { if(userTypes[i].checked) { userType=userTypes[i].value; break; } } if (userName == null || userName == "") { document.getElementById("error").innerHTML = "用户名不能为空"; return false; } if (password == null || password == "") { document.getElementById("error").innerHTML = "密码不能为空"; return false; } if (userType == null || userType == "") { document.getElementById("error").innerHTML = "请选择用户类型"; return false; } return true; } </script> <style type="text/css"> body { padding-top: 200px; padding-bottom: 40px; background-image: url('images/bg.jpg'); background-position: center; background-repeat: no-repeat; background-attachment: fixed; } .radio { padding-top: 10px; padding-bottom:10px; } .form-signin-heading{ text-align: center; } .form-signin { max-width: 300px; padding: 19px 29px 0px; margin: 0 auto 20px; background-color: #fff; border: 1px solid #e5e5e5; -webkit-border-radius: 5px; -moz-border-radius: 5px; border-radius: 5px; -webkit-box-shadow: 0 1px 2px rgba(0,0,0,.05); -moz-box-shadow: 0 1px 2px rgba(0,0,0,.05); box-shadow: 0 1px 2px rgba(0,0,0,.05); } .form-signin .form-signin-heading, .form-signin .checkbox { margin-bottom: 10px; } .form-signin input[type="text"], .form-signin input[type="password"] { font-size: 16px; height: auto; margin-bottom: 15px; padding: 7px 9px; } </style> </head> <body> <div class="container"> <form name="myForm" class="form-signin" action="login" method="post" onsubmit="return checkForm()"> <h2 class="form-signin-heading"><font color="gray">宿舍管理系统</font></h2> <input id="userName" name="userName" value="${user.userName }" type="text" class="input-block-level" placeholder="用户名..."> <input id="password" name="password" value="${user.password }" type="password" class="input-block-level" placeholder="密码..." > <label class="radio inline"> <input id="admin" type="radio" name="userType" value="admin" checked/> 系统管理员 </label> <label class="radio inline"> <input id="dormManager" type="radio" name="userType" value="dormManager" ${userType==2?'checked':''} /> 宿舍管理员 </label> <label class="radio inline"> <input id="student" type="radio" name="userType" value="student" ${userType==3?'checked':''}/> 学生 </label> <label class="checkbox"> <input id="remember" name="remember" type="checkbox" value="remember-me" ${remember==1?'checked':''}>记住我 <font id="error" color="red">${error }</font> </label> <button class="btn btn-large btn-primary" type="submit">登录</button> <button class="btn btn-large btn-primary" type="button" >重置</button> </form> </div> </body> </html>
楼层Dao层
package com.lero.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import com.lero.model.DormBuild; import com.lero.model.DormManager; import com.lero.model.PageBean; import com.lero.util.StringUtil; public class DormBuildDao { public List<DormBuild> dormBuildList(Connection con, PageBean pageBean, DormBuild s_dormBuild)throws Exception { List<DormBuild> dormBuildList = new ArrayList<DormBuild>(); StringBuffer sb = new StringBuffer("select * from t_dormBuild t1"); if(StringUtil.isNotEmpty(s_dormBuild.getDormBuildName())) { sb.append(" where t1.dormBuildName like '%"+s_dormBuild.getDormBuildName()+"%'"); } if(pageBean != null) { sb.append(" limit "+pageBean.getStart()+","+pageBean.getPageSize()); } PreparedStatement pstmt = con.prepareStatement(sb.toString()); ResultSet rs = pstmt.executeQuery(); while(rs.next()) { DormBuild dormBuild=new DormBuild(); dormBuild.setDormBuildId(rs.getInt("dormBuildId")); dormBuild.setDormBuildName(rs.getString("dormBuildName")); dormBuild.setDetail(rs.getString("dormBuildDetail")); dormBuildList.add(dormBuild); } return dormBuildList; } public static String dormBuildName(Connection con, int dormBuildId)throws Exception { String sql = "select * from t_dormBuild where dormBuildId=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setInt(1, dormBuildId); ResultSet rs = pstmt.executeQuery(); if(rs.next()) { return rs.getString("dormBuildName"); } return null; } public int dormBuildCount(Connection con, DormBuild s_dormBuild)throws Exception { StringBuffer sb = new StringBuffer("select count(*) as total from t_dormBuild t1"); if(StringUtil.isNotEmpty(s_dormBuild.getDormBuildName())) { sb.append(" where t1.dormBuildName like '%"+s_dormBuild.getDormBuildName()+"%'"); } PreparedStatement pstmt = con.prepareStatement(sb.toString()); ResultSet rs = pstmt.executeQuery(); if(rs.next()) { return rs.getInt("total"); } else { return 0; } } public DormBuild dormBuildShow(Connection con, String dormBuildId)throws Exception { String sql = "select * from t_dormBuild t1 where t1.dormBuildId=?"; PreparedStatement pstmt=con.prepareStatement(sql); pstmt.setString(1, dormBuildId); ResultSet rs=pstmt.executeQuery(); DormBuild dormBuild = new DormBuild(); if(rs.next()) { dormBuild.setDormBuildId(rs.getInt("dormBuildId")); dormBuild.setDormBuildName(rs.getString("dormBuildName")); dormBuild.setDetail(rs.getString("dormBuildDetail")); } return dormBuild; } public int dormBuildAdd(Connection con, DormBuild dormBuild)throws Exception { String sql = "insert into t_dormBuild values(null,?,?)"; PreparedStatement pstmt=con.prepareStatement(sql); pstmt.setString(1, dormBuild.getDormBuildName()); pstmt.setString(2, dormBuild.getDetail()); return pstmt.executeUpdate(); } public int dormBuildDelete(Connection con, String dormBuildId)throws Exception { String sql = "delete from t_dormBuild where dormBuildId=?"; PreparedStatement pstmt=con.prepareStatement(sql); pstmt.setString(1, dormBuildId); return pstmt.executeUpdate(); } public int dormBuildUpdate(Connection con, DormBuild dormBuild)throws Exception { String sql = "update t_dormBuild set dormBuildName=?,dormBuildDetail=? where dormBuildId=?"; PreparedStatement pstmt=con.prepareStatement(sql); pstmt.setString(1, dormBuild.getDormBuildName()); pstmt.setString(2, dormBuild.getDetail()); pstmt.setInt(3, dormBuild.getDormBuildId()); return pstmt.executeUpdate(); } public boolean existManOrDormWithId(Connection con, String dormBuildId)throws Exception { boolean isExist = false; // String sql="select * from t_dormBuild,t_dormManager,t_connection where dormManId=managerId and dormBuildId=buildId and dormBuildId=?"; String sql = "select *from t_dormManager where dormBuildId=?"; PreparedStatement pstmt=con.prepareStatement(sql); pstmt.setString(1, dormBuildId); ResultSet rs = pstmt.executeQuery(); if(rs.next()) { isExist = true; } else { isExist = false; } String sql1="select * from t_dormBuild t1,t_dorm t2 where t1.dormBuildId=t2.dormBuildId and t1.dormBuildId=?"; PreparedStatement p=con.prepareStatement(sql1); p.setString(1, dormBuildId); ResultSet r = pstmt.executeQuery(); if(r.next()) { return isExist; } else { return false; } } public List<DormManager> dormManWithoutBuild(Connection con)throws Exception { List<DormManager> dormManagerList = new ArrayList<DormManager>(); String sql = "SELECT * FROM t_dormManager WHERE dormBuildId IS NULL OR dormBuildId=0"; PreparedStatement pstmt = con.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); while(rs.next()) { DormManager dormManager=new DormManager(); dormManager.setDormBuildId(rs.getInt("dormBuildId")); dormManager.setDormManagerId(rs.getInt("dormManId")); dormManager.setName(rs.getString("name")); dormManager.setUserName(rs.getString("userName")); dormManager.setSex(rs.getString("sex")); dormManager.setTel(rs.getString("tel")); dormManagerList.add(dormManager); } return dormManagerList; } public List<DormManager> dormManWithBuildId(Connection con, String dormBuildId)throws Exception { List<DormManager> dormManagerList = new ArrayList<DormManager>(); String sql = "select *from t_dormManager where dormBuildId=?"; PreparedStatement pstmt=con.prepareStatement(sql); pstmt.setString(1, dormBuildId); ResultSet rs = pstmt.executeQuery(); while(rs.next()) { DormManager dormManager=new DormManager(); dormManager.setDormBuildId(rs.getInt("dormBuildId")); dormManager.setDormManagerId(rs.getInt("dormManId")); dormManager.setName(rs.getString("name")); dormManager.setUserName(rs.getString("userName")); dormManager.setSex(rs.getString("sex")); dormManager.setTel(rs.getString("tel")); dormManagerList.add(dormManager); } return dormManagerList; } public int managerUpdateWithId (Connection con, String dormManagerId, String dormBuildId)throws Exception { String sql = "update t_dormManager set dormBuildId=? where dormManId=?"; PreparedStatement pstmt=con.prepareStatement(sql); pstmt.setString(1, dormBuildId); pstmt.setString(2, dormManagerId); return pstmt.executeUpdate(); } }
