1.在src下建包,然后包中建javabean类,代码如下(我的包名为aa)
package aa;
import java.sql.*;
public class bean {
private final String dbDriver ="com.microsoft.sqlserver.jdbc.SQLServerDriver"; //连接sql数据库的方法
private final String url ="jdbc:sqlserver://localhost:1433;DatabaseName=student";
private final String userName = "sa";
private final String password = "1";
private Connection con = null;
private Statement stmt = null;
public bean() {
try {
Class.forName(dbDriver).newInstance(); //加载数据库驱动
} catch (Exception ex) {
System.out.println("数据库加载失败");
}
}
//创建数据库连接
public boolean creatConnection() {
try {
con = DriverManager.getConnection(url, userName, password);
con.setAutoCommit(true);
} catch (SQLException e) {
System.out.println(e.getMessage());
System.out.println("creatConnectionError!");
}
return true;
}
//对数据库的增加、修改和删除的操作
public boolean executeUpdate(String sql) {
if (con == null) {
creatConnection();
}
try {
/*Statement stmt = con.createStatement();*/
stmt = con.createStatement();
int iCount = stmt.executeUpdate(sql);
System.out.println("操作成功,所影响的记录数为" + String.valueOf(iCount));
return true;
} catch (SQLException e) {
System.out.println(e.getMessage());
System.out.println("executeUpdaterError!");
return false;
}
}
//对数据库的查询操作
public ResultSet executeQuery(String sql) {
ResultSet rs;
try {
if (con == null) {
creatConnection();
}
/*Statement stmt = con.createStatement();*/
stmt = con.createStatement();
try {
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
System.out.println(e.getMessage());
return null;
}
} catch (SQLException e) {
System.out.println(e.getMessage());
System.out.println("executeQueryError!");
return null;
}
return rs;
}
//关闭数据库的操作
public void closeConnection() {
if (con != null) {
try {
stmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
System.out.println("Failed to close connection!");
} finally {
con = null;
}
}
}
}
2.然后再jsp中调用javabean类查询数据库
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<jsp:useBean id="abc" scope="session" class="aa.bean"/>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'index.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<link rel="stylesheet" type="text/css" href="NewFile.css">
</head>
<body>
<%
String sql1="select * From userr";
abc.creatConnection();
ResultSet rs=abc.executeQuery(sql1);//abc.executeUpdate(sql2);
%>
<center>
<table border=1>
<tr>
<td>用户名</td>
<td>手机号</td>
<td>密码</td>
</tr>
<%while (rs.next()) { %>
<tr>
<td><%=rs.getString("username") %></td>
<td><%=rs.getString("phonenumber") %></td>
<td><%=rs.getString("password") %></td>
</tr>
<% }
rs.close();
abc.closeConnection();
%>
</table>
<a href="userrAdd.jsp">注册</a>
<br>
<form name="f1" id="f1" action="login.jsp" method="post" >
<table border="0">
<tr>
<td><strong>手机号</strong>:</td>
<td><input type="text" name="phonenumber" id="phonenumber" value="" maxlength="11"></td>
</tr>
<tr>
<td><strong>密码:</strong></td>
<td><input type="password" name="password" id="password" value="" maxlength="11"></td>
</tr>
<tr>
<td colspan="2" align="center"><input type="button" value="登录 " οnclick="validate()"></td>
</tr>
</table>
</form>
</center>
</body>
</html>