数据库连接操作(一)，数据库连接操作(

和通数据库htsjk.Com2019-03-07 22:42 来源:未知阅读:2942 评论 224 热度5

标签：数据库连接

数据库连接操作(一)，数据库连接操作(

用纯JSP写的连接数据库操，具体包括数据查询、删除、修改，数据的添加在数据库里操作，数据库为MySQL

文件包括login.jsp,do_login.jsp(处理登录信息),index.jsp(显示登录用户名、密码和编辑删除操作),edit.jsp,do_edit.jsp,delete.jsp

登录页面

<%@ page language="java" pageEncoding="utf-8"%>
<html>
  <head>
    <title>登录页面</title>
  </head> 
  <body> 
   <%
     request.setCharacterEncoding("gb2312");
     response.setContentType("text/html;charset=gb2312");
   %>
  <center>
  <h3>欢迎注册该网站</h3>
  <form action="do_login.jsp" method="post">
  <table>
  <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="submit" value="登录">
          <input type="reset" value="取消">
      </td>   
  </tr>
  </table>
  </form>
  </center>
  </body>
</html>

do_login.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<html>
  <head>  
    <title>登录处理页面</title>
  </head> 
  <body>
    <%
     request.setCharacterEncoding("gb2312");
     response.setContentType("text/html;charset=gb2312");
     String user=request.getParameter("username");
     String pwd=request.getParameter("password");
     
     final String DBDriver="com.mysql.jdbc.Driver";
     final String DBUrl="jdbc:mysql://localhost:3306/mqsqltest";
     final String DBUser="root";
     final String DBPassword="686175";
     Connection conn=null;
     Statement stmt=null;
     ResultSet rs=null;
     Class.forName(DBDriver);
     conn=DriverManager.getConnection(DBUrl,DBUser,DBPassword);
     String sql="select * from user where username='"+user+"'&&password='"+pwd+"'";
     stmt=conn.createStatement();
     rs=stmt.executeQuery(sql);
     if(rs.next()){
        out.print("登录成功!");
        response.setHeader("refresh","2;url=index.jsp");
     }
     else{
        out.println("您登录的账号或密码错误!");
        response.setHeader("refresh","2;url=login.jsp");
     }
     stmt.close();
     conn.close();
  %> 
  </body>
</html>

index.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<html>
  <head>
    <title>数据列表</title>
  </head>
  <body>
  <%
     request.setCharacterEncoding("gb2312");
     response.setContentType("text/html;charset=gb2312");
     
     final String DBDriver="com.mysql.jdbc.Driver";
     final String DBUrl="jdbc:mysql://localhost:3306/mqsqltest";
     final String DBUser="root";
     final String DBPassword="686175";
     Connection conn=null;
     Statement stmt=null;
     ResultSet rs=null;
     Class.forName(DBDriver);
     conn=DriverManager.getConnection(DBUrl,DBUser,DBPassword);
     String sql="select * from user";
     stmt=conn.createStatement();
     rs=stmt.executeQuery(sql);
  %> 
     <table border="1">
     <tr>
       <td>编号</td>
       <td>帐号</td>
       <td>密码</td>
       <td>编辑</td>
       <td>删除</td>
     </tr>
     <% 
     while(rs.next()){
     %>
     <tr>
     <td><%=rs.getInt(1) %></td>
     <td><%=rs.getString(2) %></td>
     <td><%=rs.getString(3) %></td>
     <td><a href="edit.jsp?id=<%=rs.getInt(1)%>">更新</a></td>
     <td><a href="delete.jsp?id=<%=rs.getInt(1)%>">删除</a></td>
     </tr>
     <% 
     } 
     stmt.close();
     conn.close(); 
     %>  
     </table>
  </body>
</html>

编辑页面

编辑后

edit.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<html>
  <head>
    <title>编辑页面</title>
  </head>  
  <body>
   <%
     request.setCharacterEncoding("gb2312");
     response.setContentType("text/html;charset=gb2312");
     int id=Integer.parseInt(request.getParameter("id"));
     session.setAttribute("id", id);
     
     final String DBDriver="com.mysql.jdbc.Driver";
     final String DBUrl="jdbc:mysql://localhost:3306/mqsqltest";
     final String DBUser="root";
     final String DBPassword="686175";
     Connection conn=null;
     Statement stmt=null;
     ResultSet rs=null;
     Class.forName(DBDriver);
     conn=DriverManager.getConnection(DBUrl,DBUser,DBPassword);
     String sql="select * from user where id='"+id+"'";
     stmt=conn.createStatement();
     rs=stmt.executeQuery(sql);
     rs.next();
    %>
   <center>
  <h3>编辑页面</h3>
  <form action="do_edit.jsp" method="post">
  <table>
  <tr>
      <td>帐号</td>
      <td><input type="text" name="username" value=<%=rs.getString(2)%>></td>
  </tr>
  <tr>
      <td>密码</td>
      <td><input type="password" name="password" value=<%=rs.getString(3)%>></td>
  </tr>
  <tr>
      <td></td>
      <td>
          <input type="submit" value="保存">
          <input type="reset" value="取消">
      </td>   
  </tr>
  </table>
  </form>
  </center>
  </body>
</html>

处理编辑页面

do_edit.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<%@ page import="java.util.*" %>
<html>
  <head>  
    <title>编辑保存页面</title>
  </head> 
  <body>
   <%
     request.setCharacterEncoding("gb2312");
     response.setContentType("text/html;charset=gb2312");
     int id=Integer.parseInt(session.getAttribute("id").toString());
     String user=request.getParameter("username");
     String pwd=request.getParameter("password");
     
     final String DBDriver="com.mysql.jdbc.Driver";
     final String DBUrl="jdbc:mysql://localhost:3306/mqsqltest";
     final String DBUser="root";
     final String DBPassword="686175";
     Connection conn=null;
     Statement stmt=null;
     ResultSet rs=null;
     Class.forName(DBDriver);
     conn=DriverManager.getConnection(DBUrl,DBUser,DBPassword);
     stmt=conn.createStatement();
     if(user!=null&&pwd!=null){
      String sql="update user set username='"+user+"',password='"+pwd+"' where id='"+id+"'";
      stmt.executeUpdate(sql);
      out.print("修改成功!");
      response.setHeader("refresh","2;url=index.jsp");
     }else{
      out.print("帐号或密码不能为空!");
      response.setHeader("refresh","2;url=edit.jsp");
     }
   %>
  </body>
</html>

删除页面

delete.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<html>
  <head> 
    <title>删除页面</title>
  </head> 
  <body>
   <%
     request.setCharacterEncoding("gb2312");
     response.setContentType("text/html;charset=gb2312");
     int id=Integer.parseInt(request.getParameter("id"));
     
     final String DBDriver="com.mysql.jdbc.Driver";
     final String DBUrl="jdbc:mysql://localhost:3306/mqsqltest";
     final String DBUser="root";
     final String DBPassword="686175";
     Connection conn=null;
     Statement stmt=null;
     Class.forName(DBDriver);
     conn=DriverManager.getConnection(DBUrl,DBUser,DBPassword);
     String sql="delete from user where id='"+id+"'";
     stmt=conn.createStatement();
     stmt.executeUpdate(sql);
     stmt.close();
     conn.close();
     out.print("删除成功!");
     response.setHeader("refresh","2;url=index.jsp");
   %> 
  </body>
</html>

在编译运行前还的将MySQL的驱动包导入WebRoot/WEB-INF/lib下

数据库连接操作的基本代码

下面是对sqlserver 2005的连接,用的是java语言

/**
* 此类用于获得数据库连接对象以及关闭
* @author student
*
*/
public class DBConnection {

private static final String DRIVER_CLASS = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String DATABASE_URL = "jdbc:sqlserver://localhost:1035;databaseName=books";
private static final String DATABASE_USER = "sa";
private static final String DATABASE_PASSWORD = "123456";

/**
* 获得一个数据连接对象
* @return
*/
public Connection getConn(){
Connection con = null;
try{
Class.forName(DRIVER_CLASS);
con = DriverManager.getConnection(DATABASE_URL,DATABASE_USER,DATABASE_PASSWORD);
}catch(ClassNotFoundException ex){
ex.printStackTrace();
}catch(SQLException ex){
ex.printStackTrace();
}
return con;
}

/**
* 关闭数据库相关对象
*/
public void closeConn(ResultSet rs,PreparedStatement ps,Connection con){
try{
if(rs!=null)
rs.close();
if(ps!=null)
ps.close();
if(con!=null)
con.close();
}catch(SQLException ex){
ex.printStackTrace();
}
}
}

下面是对sqlserver 2000的连接,用的是java语言

public class BaseJdbcDAO {
protected Connection conn=null;
protected Statement stmt=null;
protected PreparedStatement pstmt=null;
protected ResultSet rs=null;
protected void openConn(){
Connection aConn=null;
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
aConn=DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DataBaseName......余下全文>>

应用程序与数据库连接需要什操作？

要看你是哪种语言，不用步鄹都差不多。建立连接，打开数据库，操作数据库，关闭数据库。具体的看一下Ado.net如果是.net或者jdbc如果是java

本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。

返回首页

评论暂时关闭