课外天地 李树青 → 学习天地 → JavaEE网站开发课件 → [推荐]第六部分课堂讲稿——JDBC


  共有14592人关注过本帖树形打印复制链接

主题：[推荐]第六部分课堂讲稿——JDBC
帅哥哟，离线，有人找我吗？
admin
 小大  1楼 博客 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信 管理员
等级：管理员 帖子：1939 积分：26594 威望：0 精华：34 注册：2003/12/30 16:34:32 		[推荐]第六部分课堂讲稿——JDBC  发帖心情 Post By：2010/10/28 17:54:13 [只看该作者]

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>

[此贴子已经被作者于2011-11-23 08:06:14编辑过]
 回到顶部
RSS2.0 | Xhtml无图版 | Xslt无图版 Copyright ©2002 - 2016 课外天地.Net   Powered By Dvbbs Version 8.3.0
页面执行时间 0.09375 秒, 6 次数据查询