9. 基于javabean操作数据库SQL server 2005-优快云博客

本文链接：https://blog.youkuaiyun.com/lzy600/article/details/84301611

本文介绍了一个使用Java Bean进行数据库操作的实例，包括连接数据库、执行SQL查询和更新、关闭资源等关键步骤。通过调用Java Bean，实现了数据的读取、添加、删除和编辑功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、连接数据库的javabean

dataconn.java

package dataconn;  
import java.sql.*;  
  
public class  dataconn{  
      
    String sConnStr = "jdbc:sqlserver://localhost:1433;DatabaseName=database";  
    String user="sa";   
    String password="sa";   
    Connection conn = null;  
    Statement stmt = null;  
    ResultSet rs = null;  
  
public dataconn() {  
	try {  
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");   
		}  
	catch(java.lang.ClassNotFoundException e) {  
		System.err.println("news_conn(): " + e.getMessage());  
	}  
}  
  
public ResultSet executeQuery(String sql) {  
	rs = null;  
	try {  
		conn = DriverManager.getConnection(sConnStr,user,password);   
		stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);  
		rs = stmt.executeQuery(sql);  
		}   
	catch(SQLException ex) {   
		System.err.println("aq.executeQuery: " + ex.getMessage());  
		System.err.println("aq.executeQuerystrSQL: " + sql);  
	}  
	return rs;  
}  
  
          
public void executeUpdate(String sql) {  
	try {  
		conn = DriverManager.getConnection(sConnStr,user,password);   
		stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);  
		stmt.executeUpdate(sql);  
		}   
	catch(SQLException ex) {   
		System.err.println("aq.executeUpdate: " + ex.getMessage());  
		System.err.println("aq.executeUpadatestrSQL: " + sql);  
		}  
}  
          
public void closeStmt()  
    {  
	try{  
		stmt.close();  
	   }  
	catch(SQLException e){  
		e.printStackTrace();  
	   }  
    }  
  
      
public void closeConn()  
	{  
	try{  
		conn.close();  
	   }  
	catch(SQLException e){  
		e.printStackTrace();  
	   }  
	}  
  
}

2、调用javabean

<jsp:useBean id="connDbBean" scope="page" class="dataconn.dataconn"/>

3、read.jsp

<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<jsp:useBean id="connDbBean" scope="page" class="dataconn.dataconn"/>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>read</title>
</head>  
<body> 
<a href="add.jsp">add</a> 
<form method='post' action="search.jsp">
<input type="text" name="str">
<input type="submit" value="search">
</form> 
<table border="1" cellspacing="0" cellpadding="0">
  <tr>
    <td>id</td>
    <td>title</td>
    <td>content</td>
    <td>删除</td>
    <td>编辑</td>
  </tr>
<%    
String sql="select * from dataTableName";       
ResultSet rs=connDbBean.executeQuery(sql);       
while(rs.next()) {
%>       
  <tr>
    <td><%=rs.getString(1)%> </td>
    <td><%=rs.getString(2)%></td>
    <td><%=rs.getString(3)%></td>
    <td><a href="del.jsp?id=<%=rs.getString("id")%>">删除</a></td>
    <td><a href="edit.jsp?id=<%=rs.getString("id")%>">编辑</a></td>
  </tr>
<%}
rs.close();
connDbBean.closeStmt();   
connDbBean.closeConn();          
%> 
</table> 
</body>
</html>

4、add.jsp

<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
</head>

<body>
<form id="form1" name="form1" method="post" action="save.jsp">
  <label>
  title:
  <input name="title" type="text" id="title" value="这是title" size="25" />
  </label>
  <p>
    content:
      <textarea name="content" cols="50" rows="15" id="content">这是content</textarea>
  </p>
  <p>
    <label>
    <input type="submit" name="Submit" value="提交" />
    </label>
  </p>
</form>
</body>
</html>

5、save.jsp

<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<jsp:useBean id="connDbBean" scope="page" class="dataconn.dataconn"/>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>save</title>
</head>

<body>
<%
	request.setCharacterEncoding("utf-8");
	String title=request.getParameter("title");
	String content=request.getParameter("content");
	String sql="insert into dataTableName(title,content) values('"+ title +"','"+ content +"')";
	connDbBean.executeUpdate(sql); 
	connDbBean.closeStmt();       
	connDbBean.closeConn(); 
	response.sendRedirect("read.jsp");     
%>

</body>
</html>

6、del.jsp

<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<jsp:useBean id="connDbBean" scope="page" class="dataconn.dataconn"/>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>save</title>
</head>

<body>
<%
	request.setCharacterEncoding("utf-8");
	String id=request.getParameter("id");//应该判断一下id的合法性
	String sql="delete  from dataTableName where id="+ id +"";
	connDbBean.executeUpdate(sql); 
	connDbBean.closeStmt();       
	connDbBean.closeConn(); 
	response.sendRedirect("read.jsp");     
%>

</body>
</html>

7、edit.jsp

<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<jsp:useBean id="connDbBean" scope="page" class="dataconn.dataconn"/>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
</head>

<body>
<%
String id=request.getParameter("id");
String sql="select * from dataTableName where id="+ id;       
ResultSet rs=connDbBean.executeQuery(sql);  
rs.next();
%>
<form id="form1" name="form1" method="post" action="update.jsp?id=<%=id%>">
  <label>
  title:
  <input name="title" type="text" id="title" value="<%=rs.getString("title")%>" size="25" />
  </label>
  <p>
    content:
      <textarea name="content" cols="50" rows="15" id="content"><%=rs.getString("content")%></textarea>
  </p>
  <p>
    <label>
    <input type="submit" name="Submit" value="更新" />
    </label>
  </p>
</form>

<%
rs.close();       
connDbBean.closeStmt();       
connDbBean.closeConn();         

%>
</body>
</html>

8、update.jsp

<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<jsp:useBean id="connDbBean" scope="page" class="dataconn.dataconn"/>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>save</title>
</head>

<body>
<%
	request.setCharacterEncoding("utf-8");
	String id=request.getParameter("id");//应该判断一下id的合法性
	String title=request.getParameter("title");
	String content=request.getParameter("content");
	
	String sql="update dataTableName set title='"+ title +"', content='"+ content +"' where id="+ id;
	connDbBean.executeUpdate(sql); 
	connDbBean.closeStmt();       
	connDbBean.closeConn(); 
	response.sendRedirect("read.jsp");     
%>

</body>
</html>

判断传递的参数的合法性是必要的。

判断是否为空

<%
String id=request.getParameter("id");   
String username=request.getParameter("username");   
String pwd=request.getParameter("pwd");  
if (id == null || id.equals(""))   
{   
out.print("参数错误！");   
return;   
}   
 
if (username == null || username.equals("") || pwd == null || pwd.equals(""))   
{   
out.print("<br>用户名或密码不能为空");   
return;   
}   
 
%>