-- 作者：admin
-- 发布时间：2009/4/14 19:29:59
-- JSP中的JDBC
1、登录练习
1、1 建立集成认证的ODBC桥
<%@ page c%>
<%@page import="java.sql.*"%>
<html>
<head>
</head>
<body>
<%
Connection con;
Statement stm;
ResultSet res;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:StuSQL");
stm = con.createStatement();
res = stm.executeQuery("select * from stu");
while (res.next()) {
out.print(res.getString(1)+"<br>");
}
con.close();
} catch (Exception ex) {
System.out.println(ex.getMessage());
}
%>
</body>
</html>
1、2 建立独立认证的ODBC桥
<%@ page c%>
<%@page import="java.sql.*"%>
<html>
<head>
</head>
<body>
<%
Connection con;
Statement stm;
ResultSet res;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:StuSQL","sa","");
stm = con.createStatement();
res = stm.executeQuery("select * from stu");
while (res.next()) {
out.print(res.getString(1)+"<br>");
}
con.close();
} catch (Exception ex) {
System.out.println(ex.getMessage());
}
%>
</body>
</html>
1、3 使用本地api
<%@ page c%>
<%@page import="java.sql.*"%>
<html>
<head>
</head>
<body>
<%
Connection con;
Statement stm;
ResultSet res;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=Students","sa","");
stm = con.createStatement();
res = stm.executeQuery("select * from stu");
while (res.next()) {
out.print(res.getString(1)+"<br>");
}
con.close();
} catch (Exception ex) {
System.out.println(ex.getMessage());
}
%>
</body>
</html>
2、显示输出
2、1 输出全部字段内容
<%@ page c%>
<%@page import="java.sql.*"%>
<html>
<head>
</head>
<body>
<%
Connection con;
Statement stm;
ResultSet res;
ResultSetMetaData rsmd;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=Students","sa","");
stm = con.createStatement();
res = stm.executeQuery("select * from stu");
rsmd = res.getMetaData();
while (res.next())
{
for(int i=1;i<=rsmd.getColumnCount();i++)
out.print(res.getObject(i)+"\\t");
out.print("<br>");
}
con.close();
} catch (Exception ex) {
System.out.println(ex.getMessage());
}
%>
</body>
</html>
进行表格格式化
<%@ page c%>
<%@page import="java.sql.*"%>
<html>
<head>
</head>
<body>
<%
Connection con;
Statement stm;
ResultSet res;
ResultSetMetaData rsmd;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager
.getConnection(
"jdbc:sqlserver://localhost:1433; DatabaseName=Students",
"sa", "");
stm = con.createStatement();
res = stm.executeQuery("select * from stu");
rsmd = res.getMetaData();
out.print("<table bgColor=\'yellow\' border=\'1\' align=\'center\'>");
out.print("<thead>");
for (int i = 1; i <= rsmd.getColumnCount(); i++)
out.print("<td>" + rsmd.getColumnName(i) + "</td>");
while (res.next()) {
out.print("<tr>");
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
out.print("<td>" + res.getString(i) + "</td>");
}
out.print("</tr>");
}
out.print("</table>");
con.close();
} catch (Exception ex) {
System.out.println(ex.getMessage());
}
%>
</body>
</html>
练习：以字段为行，记录为列显示表
2、2 利用SQL进行的高级格式化
<%@ page c%>
<%@page import="java.sql.*"%>
<html>
<head>
</head>
<body>
<%
Connection con;
Statement stm;
ResultSet res;
ResultSetMetaData rsmd;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager
.getConnection(
"jdbc:sqlserver://localhost:1433; DatabaseName=Students",
"sa", "");
stm = con.createStatement();
res = stm
.executeQuery("SELECT number, name, CASE WHEN sex =1 THEN \'男生\' WHEN sex =0 THEN \'女生\' ELSE \'null\' END AS \'sex\', year(getDate())-year(birthday) as \'年龄\', round(height,2) AS \'height\' FROM stu");
rsmd = res.getMetaData();
out.print("<table bgColor=\'yellow\' border=\'1\' align=\'center\'>");
out.print("<thead>");
for (int i = 1; i <= rsmd.getColumnCount(); i++)
out.print("<td>" + rsmd.getColumnName(i) + "</td>");
while (res.next()) {
out.print("<tr>");
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
out.print("<td>" + res.getString(i) + "</td>");
}
out.print("</tr>");
}
out.print("</table>");
con.close();
} catch (Exception ex) {
System.out.println(ex.getMessage());
}
%>
</body>
</html>
2、3 逆序输出
<%@ page c%>
<%@page import="java.sql.*"%>
<html>
<head>
</head>
<body>
<%
Connection con;
Statement stm;
ResultSet res;
ResultSetMetaData rsmd;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager
.getConnection(
"jdbc:sqlserver://localhost:1433; DatabaseName=Students",
"sa", "");
stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
res = stm
.executeQuery("SELECT number, name, CASE WHEN sex =1 THEN \'男生\' WHEN sex =0 THEN \'女生\' ELSE \'null\' END AS \'sex\', year(getDate())-year(birthday) as \'年龄\', round(height,2) AS \'height\' FROM stu");
rsmd = res.getMetaData();
res.afterLast();
out.print("<table bgColor=\'yellow\' border=\'1\' align=\'center\'>");
out.print("<thead>");
for (int i = 1; i <= rsmd.getColumnCount(); i++)
out.print("<td>" + rsmd.getColumnName(i) + "</td>");
while (res.previous()) {
out.print("<tr>");
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
out.print("<td>" + res.getString(i) + "</td>");
}
out.print("</tr>");
}
out.print("</table>");
con.close();
} catch (Exception ex) {
System.out.println(ex.getMessage());
}
%>
</body>
</html>
3、参数查询
3、1 SQL生成方法
index.jsp文件为：
<%@ page c%>
<%@page import="java.sql.*"%>
<html>
<head>
</head>
<body>
<form action=\'getDetail.jsp\'><input type="text" name=\'number\'>
<input type="submit" name=\'submit\' value=\'确认\'></form>
</body>
</html>
getDetail.jsp文件为：
<%@ page c%>
<%@page import="java.sql.*"%>
<html>
<head>
</head>
<body>
<%
String number = (String) request.getParameter("number");
Connection con;
Statement stm;
ResultSet res;
ResultSetMetaData rsmd;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager
.getConnection(
"jdbc:sqlserver://localhost:1433; DatabaseName=Students",
"sa", "");
stm = con.createStatement();
System.out.println("SELECT * from stu where number=\'" + number
+ "\'");
res = stm.executeQuery("SELECT * from stu where number=\'"
+ number + "\'");
rsmd = res.getMetaData();
out.print("<table bgColor=\'yellow\' border=\'1\' align=\'center\'>");
out.print("<thead>");
for (int i = 1; i <= rsmd.getColumnCount(); i++)
out.print("<td>" + rsmd.getColumnName(i) + "</td>");
while (res.next()) {
out.print("<tr>");
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
out.print("<td>" + res.getString(i) + "</td>");
}
out.print("</tr>");
}
out.print("</table>");
con.close();
} catch (Exception ex) {
System.out.println(ex.getMessage());
}
%>
</body>
</html>
但是此时存在SQL注入攻击的可能，如：000001\' or 1=1 or name=\'
输出SQL语句：System.out.println("SELECT * from stu where number=\'" + number + "\'");
为：SELECT * from stu where number=\'000001\' or 1=1 or name=\'\'
3、2 调用预处理语句（PreparedStatement）
<%@ page c%>
<%@page import="java.sql.*"%>
<html>
<head>
</head>
<body>
<%
String number = (String) request.getParameter("number");
Connection con;
PreparedStatement pstm;
ResultSet res;
ResultSetMetaData rsmd;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager
.getConnection(
"jdbc:sqlserver://localhost:1433; DatabaseName=Students",
"sa", "");
pstm = con.prepareStatement("select * from stu where number=?");
pstm.setString(1,number);
res=pstm.executeQuery();
rsmd = res.getMetaData();
out.print("<table bgColor=\'yellow\' border=\'1\' align=\'center\'>");
out.print("<thead>");
for (int i = 1; i <= rsmd.getColumnCount(); i++)
out.print("<td>" + rsmd.getColumnName(i) + "</td>");
while (res.next()) {
out.print("<tr>");
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
out.print("<td>" + res.getString(i) + "</td>");
}
out.print("</tr>");
}
out.print("</table>");
con.close();
} catch (Exception ex) {
System.out.println(ex.getMessage());
}
%>
</body>
</html>
注意：
1）此时采用同样的SQL注入攻击将失效
2）可以使用SQL本身的模糊匹配实现模糊检索，如：pstm = con.prepareStatement("select * from stu where number like ?");
4 调用存储过程（CallableStatement）
CallableStatement是PreparedStatement子类，可以执行存储过程
4、1 一般的调用
建立存储过程：
create proc stuCount
as select count(*) from stu
<%@ page c%>
<%@page import="java.sql.*"%>
<html>
<head>
</head>
<body>
<%
Connection con;
CallableStatement cstm;
ResultSet res;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager
.getConnection(
"jdbc:sqlserver://localhost:1433; DatabaseName=Students",
"sa", "");
cstm = con.prepareCall("{call stuCount}");
res = cstm.executeQuery();
res.next();
out.print(res.getString(1) + "<br>");
con.close();
} catch (Exception ex) {
System.out.println(ex.getMessage());
}
%>
</body>
</html>
注意：
{call stu_proc1}为调用存储过程的语法，不是标准SQL的方法（所以不能用exec stu_proc1），而是JDBC的转义语法，它会自动将其转换成特定的DBMS格式
4、2 使用传入参数
建立存储过程：
create proc getStuInfoByNumber
@number char(6)
as select * from stu where number=@number
<%@ page c%>
<%@page import="java.sql.*"%>
<html>
<head>
</head>
<body>
<%
String number = new String(request.getParameter("number").getBytes(
"ISO-8859-1"), "GBK");
Connection con;
CallableStatement cstm;
ResultSet res;
ResultSetMetaData rsmd;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager
.getConnection(
"jdbc:sqlserver://localhost:1433; DatabaseName=Students",
"sa", "");
cstm = con.prepareCall("{call getStuInfoByNumber(?)}");
cstm.setString(1, number);
res = cstm.executeQuery();
rsmd = res.getMetaData();
while (res.next()) {
for (int i = 1; i <= rsmd.getColumnCount(); i++)
out.print(res.getObject(i) + "\\t");
out.print("<br>");
}
con.close();
} catch (Exception ex) {
System.out.println(ex.getMessage());
}
%>
</body>
</html>
4、3 使用传出参数
建立存储过程：
create proc getStuNameByNumber
@number char(6),
@name char(8) output
as select @name=name from stu where number=@number
<%@ page c%>
<%@page import="java.sql.*"%>
<html>
<head>
</head>
<body>
<%
String number = new String(request.getParameter("number").getBytes(
"ISO-8859-1"), "GBK");
Connection con;
CallableStatement cstm;
ResultSet res;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager
.getConnection(
"jdbc:sqlserver://localhost:1433; DatabaseName=Students",
"sa", "");
cstm = con.prepareCall("{call getStuNameByNumber(?,?)}");
cstm.setString(1, number);
cstm.registerOutParameter(2, Types.VARCHAR);//重要，指定传出参数的类型
cstm.execute();
out.print(cstm.getString(2));//重要，得到传出参数
con.close();
} catch (Exception ex) {
System.out.println(ex.getMessage());
}
%>
</body>
</html>
注意：Oracle之类的数据库通常做法为
String pcall="{?= call stu_proc3(?)}";//?=表示传出参数
CallableStatement cstmt=conn.prepareCall(pcall);
cstmt.setString(2,"000001");
cstmt.registerOutParameter(1,Types.CHAR);//重要，指定传出参数的类型
cstmt.execute();
System.out.println(cstmt.getString(1));//重要，得到传出参数
5 数据操纵语句
不论什么操纵语句，都可以直接使用Statement来处理
<%@ page c%>
<%@page import="java.sql.*"%>
<html>
<head>
</head>
<body>
<%
Connection con;
Statement stm;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager
.getConnection(
"jdbc:sqlserver://localhost:1433; DatabaseName=Students",
"sa", "");
stm = con.createStatement();
stm
.executeUpdate("insert into stu values(\'000111\',\'Ben\',1,\'1985-10-10\',1.76)");
con.close();
} catch (Exception ex) {
System.out.println(ex.getMessage());
}
%>
</body>
</html>
[此贴子已经被作者于2010-12-12 10:58:00编辑过]
|