在JSP和Servlet中使用JDBC
张彦星
1 在JSP中使用JDBC访问数据库
本节将介绍在JSP页面中通过JDBC访问数据库，并且介绍在数据库中执行不同操作的方法。在数据库编程中，主要涉及到的操作有：
- 查询数据；
- 更新数据；
- 删除数据；
- 创建数据表、更改表、获得表本身的信息；
在介绍编程之前，请使用下列脚本在数据库中创建一个表。
use jspdev;
drop table contact;
create table contact(userName varchar(20),mobile int,phone varchar(20),mail varchar(50),lastcontact datetime,mem varchar(100),constraint pk_contact primary key(userName));
1.1 添加数据
insert.jsp
<%@ page language="java" import="java.util.*,java.sql.*,java.io.*" pageEncoding="GB2312"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%><%@page contentType="text/html; charset=gb2312"%><%@ page import="java.sql.DriverManager" %><%@ page import="java.sql.PreparedStatement" %><%@ page import="java.sql.Date" %><%request.setCharacterEncoding("GB2312");%> My JSP 'insert.jsp' starting page <% try { Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance(); Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jspdev","sa","starxing"); //使用preparedStatement PreparedStatement pstmt = con.prepareStatement("insert into contact values(?,?,?,?,?,?)"); pstmt.setString(1,"张彦星"); pstmt.setInt(2,136234243); pstmt.setString(3,"010455554"); pstmt.setString(4,"test@test.com"); pstmt.setDate(5,new Date(new java.util.Date().getTime())); pstmt.setString(6,"啊水电费搭撒"); pstmt.execute(); pstmt.setString(1,"aaas"); pstmt.setInt(2,2343); pstmt.setString(3,"123"); pstmt.setString(4,"tessst@test.com"); pstmt.setDate(5,new Date(new java.util.Date().getTime())); pstmt.setString(6,"嗄撒旦发"); pstmt.execute(); pstmt.setString(1,"ssf"); pstmt.setInt(2,123); pstmt.setString(3,"asfwef"); pstmt.setString(4,"test@test.com"); pstmt.setDate(5,new Date(new java.util.Date().getTime())); pstmt.setString(6,"文风"); pstmt.execute(); pstmt.setString(1,"文风嗄撒旦发"); pstmt.setInt(2,24124); pstmt.setString(3,"文人贴"); pstmt.setString(4,"test@test.com"); pstmt.setDate(5,new Date(new java.util.Date().getTime())); pstmt.setString(6,"奥斯丁"); pstmt.execute(); pstmt.setString(1,"案犯问改"); pstmt.setInt(2,451145); pstmt.setString(3,"文人贴统一"); pstmt.setString(4,"test@test.com"); pstmt.setDate(5,new Date(new java.util.Date().getTime())); pstmt.setString(6,"文风撒肥"); pstmt.execute(); } catch(Exception e) { e.printStackTrace(); } %>
1.2 查询数据库
查询数据库的操作和执行更新的操作基本相似。不同的是使用的SQL语句不同，并且操作完成后往往需要对查询的结果集进行处理。
query.jsp
<%@ page language="java" import="java.util.*,java.sql.*,java.io.*" pageEncoding="GB2312"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%><%! String trans(String chi){ String result = null; byte temp[]; try { temp = chi.getBytes("iso-8859-1"); result = new String(temp); }catch(Exception e) { e.printStackTrace(); } return result;}%> My JSP 'query.jsp' starting page 以下是从Mssql数据库读取的数据：
姓 名 |
手 机 |
电 话 |
Email |
最后的联系时间 |
备 注 |
|---|
<%
try
{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
Connection con =
DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jspdev","sa","starxing");
Statement stmt = con.createStatement();
ResultSet rst = stmt.executeQuery("select * from contact;");
while(rst.next())
{
out.println("");
out.println(""+rst.getString("userName")+" | ");
out.println(""+rst.getInt("mobile")+" | ");
out.println(""+rst.getString("phone")+" | ");
out.println(""+rst.getString("mail")+" | ");
out.println(""+rst.getString("lastcontact")+" | ");
out.println(""+rst.getString("mem")+" | ");
out.println("
");
}
rst.close();
stmt.close();
con.close();
}catch(Exception e)
{
e.printStackTrace();
}
%>
1.3 更新数据
更新的操作和前面的操作基本一样，
update.jsp
<%@ page language="java" import="java.util.*,java.sql.*,java.io.*" pageEncoding="GB2312"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%> My JSP 'update.jsp' starting page <% try { Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance(); Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jspdev","sa","starxing"); Statement stmt = con.createStatement(); int col = stmt.executeUpdate("update contact set mem ='他在北京' where userName='张彦星'"); out.println("成功更新"+col+"条数据"); stmt.close(); con.close(); }catch(Exception e) { e.printStackTrace(); } %> 1.4 删除数据
delete.jsp
<%@ page language="java" import="java.util.*,java.sql.*,java.io.*" pageEncoding="GB2312"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%> My JSP 'delete.jsp' starting page <% try { Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance(); Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jspdev","sa","starxing"); Statement stmt = con.createStatement(); boolean col= stmt.execute("delete from contact where userName ='aaas'"); out.println("成功更新"+col+"条数据"); stmt.close(); con.close(); }catch(Exception e) { e.printStackTrace(); } %> 1.5 高级操作
除了能查询、删除、更新数据表中的信息外，我们还可以获得数据表本身的信息。
下面我们开发一个JSP，只要提供数据库名和表的名称，就可以把数据表的所有信息（包括字段名，字段的类型和所有记录）在浏览器里显示。
proxy.jsp
<%@ page language="java" import="java.util.*,java.sql.*,java.sql.*" pageEncoding="GB2312"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%><%@page contentType="text/html; charset=gb2312"%><%request.setCharacterEncoding("GB2312");%> My JSP 'proxy.jsp' starting page <%! String typeToString(int i) { String ret = ""; switch(i) { case(1):ret="CHAR";break; case(2):ret="NUMERIC";break; case(3):ret="DECIMAL";break; case(4):ret="INTEGER";break; case(5):ret="SMALLINT";break; case(8):ret="FLOAT";break; case(12):ret="DOUBLE";break; case(91):ret="VARCHAR";break; default:ret="other"; } return ret; } %> <%! String getCol(ResultSet rst,int type,int colNum)throws Exception { String ret=""; switch(type) { case(1):ret=rst.getString(colNum);break; case(4):ret=String.valueOf(rst.getInt(colNum));break; case(5):ret=String.valueOf(rst.getInt(colNum));break; case(6):ret=String.valueOf(rst.getFloat(colNum));break; case(8):ret=String.valueOf(rst.getDouble(colNum));break; case(12):ret=rst.getString(colNum);break; default:ret="not know"; } return ret; } %> <%! String trans(String chi) { String result=null; byte temp[]; try { temp=chi.getBytes("iso-8859-1"); result = new String(temp); } catch(Exception e) { e.printStackTrace(); } return result; } %> 数据库动态代理v0.5:
<% String database=(String)request.getParameter("database"); String table = (String)request.getParameter("table"); // out.println(database+table); %>
<%
try
{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
Connection con =
DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jspdev","sa","starxing");
Statement stmt = con.createStatement();
ResultSet rst = stmt.executeQuery("use "+database+";select * from "+table);
ResultSetMetaData meta = rst.getMetaData();
int col = meta.getColumnCount();
out.println("表"+table+"共有："+col+"个字段，这些字段：
");
out.println("");
for(int i=1;i
{
out.println("字段名：");
out.println(meta.getColumnName(i));
out.println(" 类型：");
out.println(typeToString(meta.getColumnType(i)));
out.println(" | ");
}
out.println("
");
while(rst.next())
{
out.println("");
for(int i=1;i
{
out.println(""+getCol(rst,meta.getColumnType(i),i)+" | ");
}
out.println("
");
}
rst.close();
stmt.close();
con.close();
}catch(Exception e)
{
e.printStackTrace();
}
%>
1.6 其他操作
除了在数据库中对表进行操作外，在JDBC中还可以执行许多其他的操作，比如创建、删除数据库，增加、删除一个数据库用户，创建、删除表、更新表的结构等。
misc_operation.jsp
<%@ page language="java" import="java.util.*,java.sql.*,java.io.*" pageEncoding="GB2312"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%> My JSP 'misc_operation.jsp' starting page <% try { Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance(); Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jspdev","sa","starxing"); Statement stmt = con.createStatement(); stmt.execute("create table test(t int,t2 varchar(20))"); stmt.execute("alter table add t3 varchar(20) null"); stmt.execute("insert into test values(12,'starxing','asiapower')"); stmt.execute("insert into test values(11,'star1xing','aaa1');"); out.print("执行完成！"); stmt.close(); con.close(); }catch(Exception e) { e.printStackTrace(); } %>
阅读(3797) | 评论(0) | 转发(0) |
