Sponsored Links
This presentation is the property of its rightful owner.
1 / 67

第十讲 PowerPoint PPT Presentation


  • 164 Views
  • Uploaded on
  • Presentation posted in: General

第十讲. 课  题  JDBC 与数据库( JDBC 与 SQL 语句、存储过程的应用) 目的要求 掌握 JDBC 存取各种不同数据库的方法   教学重点 掌握 JDBC 与不同数据库的连接字符串、连接池 教学难点 JSP 如何存取存储过程中输入参数、输出参数、输入输出 参数 教学课时 4 (含 2 课时上机) 教学方法 讲解、示例与启发式教学相结合  . 教学内容和步骤 10.1 JDBC 与数据库编程 10.1.1 JDBC 的 Driver 可分为以下四种类型

Download Presentation

第十讲

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript



JDBCJDBCSQL

JDBC

JDBC

JSP

42


10.1 JDBC

10.1.1 JDBCDriver

(1)JDBC-ODBC BridgeODBC Driver

ODBCODBC

(2)Native-API partly-Java Driver

JDBCDBMSOracleSybaseAccessDBMS


(3)JDBC-Net All-Java Driver

JDBCDBMSDBMSDBMSJDBC


(4)Native-protocol All-Java Driver

JDBCDBMS

Java(All-Java)

10.1.2 JDBC Url

jdbc:<>:<>


<>---ODBC ,jdbc:odbc:fff

<>---

jdbc:dbnet//womat:356//fff

ODBC:

Jdbc:odbc:<>[<>=<>]

jdbc:odbc:sqlconn:UID=sa;PWD=ww


10.2 JDBC

10.2.1

JDBCSQL

1: Creage.javaJDBCtestTableidname

import java.net.URL;

import java.sql.*;

class Create{

public static void main (String[] args){

String url="jdbc:odbc:demo";


String query="CREATE TABLE testTable" + "(id INT,name CHAR(10))";

try{

//jdbc-odbc bridge

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//Cyclone

// ,jdbcfororaclejdbcforsqlserver

Connection con=DriverManager.getConnection(url,"user","password");

//Statement

Statement stmt=con.createStatement();

//SQL

stmt.executeUpdate(query);


System.out.println("Create successfully!");

// stm

stmt.close();

//

con.close();

}catch(SQLException ex){

//SQL

System.out.println("\n***SQLException caught ***\n");

while(ex!=null){

System.out.println("SQLState:"+ex.getSQLState());

System.out.println("Message:"+ex.getMessage());

System.out.println("Vendor:"+ex.getErrorCode());


ex=ex.getNextException();

System.out.println("");

}

}catch(java.lang.Exception ex){

ex.printStackTrace();

}

}

}

22 Statement

1)Statement


Statement stmt=con.createStatement();

//SQL

int count1=stmt.executeUpdate("INSERT INTO testTable(id,name) VALUES(1,'wu')");

int count2=stmt.executeUpdate("INSERT INTO testTable(id,name) VALUES(2,'wang')");

2PreparedStatement

String data[][]={{"5","xu"},{"6","yan"}};

PreparedStatement pstmt=con.prepareStatement("INSERT INTO testTable (id,name) VALUES(?,?)");


//SQL

for (int i=0;i<data.length;i++){

pstmt.setInt(1,Integer.parseInt(data[i][0]));

pstmt.setString(2,data[i][1]);

pstmt.executeUpdate();

}

3CallableStatement

CallableStatement cstmt=con.prepareCall("{call Search(?)}");

//

cstmt.setInt(1,934678);

//

cstmt.execute();


2.3 ResultSetResultSetMetaData

String query = "SELECT * FROM testTable";

Statement stmt=con.createStatement();

//

ResultSet rs=stmt.executeQuery(query);

//

System.out.println("The detail of testTable is:");

ResultSetMetaData rsmd=rs.getMetaData();

//

in numCols=rsmd.getColumnCount();

//

for(int i=1;i<=numCols;i++)

{


if(i>1) System.out.print(",");

System.out.print(rsmd.getColumnLabel(i));

}

System.out.println("");

//

while(rs.next())

{

//

for(int i=1;i<=numCols;i++){


if(i>1) System.out.print(",");

System.out.print(rs.getString(i));

}

2.4 DatabaseMetaData

DatabaseMetaData dma = con.getMetaData();

//URL

System.out.println("\nConnected to" + dma.getURL());

System.out.println("Driver" + dma.getDriverName());

System.out.println("Version" + dma.getDriverVersion()


//

System.out.println("\nDataBase name:" + dma.getDatabaseProductName()+dma.getDatabaseProductVersion());

System.out.println("DataBase supports SQL keywords:\n\t" + dma.getSQLKeywords());

//

dma.supportsANSI92EntryLevelSQL()

dma.supportsANSI92FullSQL()

dma.supportsStoredProcedures()


10.2.2 JDBC

JDBC

JDBCsqlDriverManagerDriverPropertyInfoSQL


sql.Date

sqlDateutilDateutil.DateSQLDATE

Date

public Date(int year, int mouth, int day)

util.Date19000111311998123


Date d=new Date(98,0,23);

DateString

public static Date valueOf(String s)

StringS--1997-04-12

public String toString()

String--


sql.Time

util.DateTimeSQLTIMEsql.Dateutil.Date

Time

public Time(int hour,int minute,int second)

023059

sql.DateTimeStringValueOfStringString122606


sql.Timestamp

util.Date(nanosecond)11TimestampSQL(Timestamp)

Timestamp

public Timestamp(int year, int mouth, int date, int hour, int minute, int second, int nano)0999,999,999

Timestamp

public getnanos()

public void setNanos(int n)


sql.Types

TypesObjectSQL

public final static int11.1OTHERgetObjectsetObjectJavaObject


1 TypesSQL


SQLJava

SQLJavaJavaSQLResultSetgetStatementsetregisterOutParameter

ResultSetSQLgetJDBCSQLJavaSQLJava11-2


SQLJava

SQLJavaJavaSQLResultSetgetStatementsetregisterOutParameter

ResultSetSQLgetJDBCSQLJavaSQLJava11-2


2 SQLJava


JDBC CHARVARCHAR LONGVARCHAR


JDBC BINARYVARBINARY LONGVARBINARY


JDBC SQL Server


JDBC REALDOUBLE FLOAT


JDBC TIMESTAMP SQL Server datetime smalldatetime datetime 4 smalldatetime 2


10.3 JDBC

MySQL(http://www.mysql.com)mm.mysql-2.0.2-bin.jar

Class.forName( "org.gjt.mm.mysql.Driver" );

cn= DriverManager.getConnection( "jdbc:mysql://MyDbComputerNameOrIP:3306

/myDatabaseName", sUsr, sPwd );

Oracle(http://www.oracle.com/ip/deploy/database/oracle9i/)classes12.zip

Class.forName( "oracle.jdbc.driver.OracleDriver" );

cn= DriverManager.getConnection( "jdbc:oracle:thin:@

MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );


Sybase(http://jtds.sourceforge.net)jconn2.jar

Class.forName( "com.sybase..jdbc.SybDriver" );

cn = DriverManager.getConnection( "jdbc:sybase:Tds:MyDbComputerNameOrIP:2638", sUsr, sPwd );

//(Default-Username/Password: "dba"/"sql")

Microsoft SQLServer(http://www.microsoft.com)

Class.forName( "com.microsoft.jdbc.sqlserver.SQLServerDriver" );

cn= DriverManager.getConnection( "jdbc:microsoft:sqlserver://

MyDbComputerNameOrIP:1433;databaseName=master", sUsr, sPwd );


ODBC

Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );

Connection cn = DriverManager.getConnection( "jdbc:odbc:" + sDsn, sUsr, sPwd );

informix

Class.forName("com.informix.jdbc.IfxDriver").newInstance();

String url ="jdbc:informix-sqli://123.45.67.89:1533/testDB:INFORMIXSERVER

=myserver; user=testuser;password=testpassword";


sybase

Class.forName( "com.sybase.jdbc.SybDriver" )

url="jdbc:sybase:Tds:127.0.0.1:2638/asademo";

SybConnection connection= (SybConnection)

DriverManager.getConnection(url,"dba","sql");


2: queryBook.jsp

<[email protected] contentType="text/html;charset=gb2312"%>

<[email protected] language="java" import="java.sql.*,java.io.*"%>

<HTML>

<BODY>

<CENTER>

<FONT SIZE = 5 COLOR = blue></FONT>

<table border=3>

<tr><td><b><center>ID</td> <td><b><center></td>

<td><b><center></td> <td><b><center></td></tr>

<%


Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();

Connection con=java.sql.DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=jspdb","sa","");

Statement stmt=con.createStatement();

ResultSet rst=stmt.executeQuery("select * from book;");

while(rst.next())

{


out.println("<tr>");

out.println("<td>"+rst.getString("bkld")+"</td>");

out.println("<td>"+rst.getString("bkName")+"</td>");

out.println("<td>"+rst.getString("bkPublisher")+"</td>");

out.println("<td>"+rst.getFloat("bkPrice")+""+"</td>");

out.println("</tr>");

}


rst.close();

stmt.close();

con.close();

%>

</table>

</BODY>

</HTML>


10.4

JDBC



Reference Counting


1maxConnmaxWaitTimemaxWaitTime


minConn


minConnmaxConnminConnminConnminConnmaxConnConnectionPoolgetActiveSizegetOpenSizeActiveSize OpenSize minConnActiveSizemaxConnactiveSizeOpenSize


"All-All-Nothing"ConnectionConnectionAutoCommitfalse commitrollback


commitrollback


Javasynchronized


DBConnectionPool()DBConnectionManagerJDBCDBConnectionPool


DBConnectionManager(DBConnectionPool)JDBCConnJDBCConnModel

Conn

Class Conn {

Private java. sgl .Connection con; //

Public Boolean inUse ; //

Public long lastAccess; //

Public int useCount; //

}


//

public static synchronized void FastInitPool()

throws Exception {

try { Class.forName(driver);

for (int i=0; i<size; i++) {

Connection con = createConnection();

if (con!=null) addConnection(con);

} } }


//

private static void addConnection(Connection con) {

if (pool=null||pool1=null) {

pool=new Vector(size);

pool1=new Vector(size); }

pool.addElement(con);

pool1.addElement("false"); }

//

public static synchronized Connection getConn()

throws Exception {

Connection conn = null;

try { if (driver = null)

FastInitPool();


// ()

.for (int i = 0; i < pool.size(); i++) {

conn = (Connection)pool.elementAt(i);

if (pool1.elementAt(i)=="false") {

pool1.set(i,"true");

//System.out.println(""+(i+1)+"");

return conn;

}

}


//

conn = createConnection();

pool.addElement(conn);

pool1.addElement("true");

// System.out.println(" ,");

}

catch (Exception e) {

System.err.println(e.getMessage());

throw new Exception(e.getMessage());

}


return conn; //

}

public Connection getConnection(String strDriver, String strUrl, String strUserName, String strPassWord)

throws SQLException{

try{ Class.forName(strDriver);

conn = DriverManager.getConnection(strUrl, strUserName, strPassWord); }

return conn; }


JDBCMySQL

JSPJDBCSQL SERVER


JDBCSQL2

1JDBC

2JSPSQLserver

3JDBC

MyeclipseTomcatJDK


1.

2

<%@ page contentType="text/html;charset=GBK" %>

<%@ page import="java.sql.*" %>

<html>

<head>

</head>

<% try {

String url="jdbc:odbc:xsfsql" ;

Connection conn;

java.sql.CallableStatement st;


Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

conn=DriverManager.getConnection(url,"sa","");

out.println("<p>Join Success</p>");

st=conn.prepareCall(" {call queryxm1(?) }");

st.setString(1,"xxyyzz");

//st.setString(2,"");

//st.setDouble(3,0);

ResultSet rs=st.executeQuery();


while (rs.next())

out.println(rs.getString("xm"));

} catch (Exception ex ){

ex.printStackTrace();

}

%></html>

3SQL serverintputoutputinoutJSP


1.WEBInternetJDBC

2


  • Login