資料庫連接操作(一)，資料庫連接操作(
用純JSP寫的串連資料庫操，具體包括資料查詢、刪除、修改，資料的添加在資料庫裡操作，資料庫為MySQL
檔案包括login.jsp,do_login.jsp(處理登入資訊),index.jsp(顯示登入使用者名稱、密碼和編輯刪除操作),edit.jsp,do_edit.jsp,delete.jsp
登入頁面
login.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
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
