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

第十讲 PowerPoint PPT Presentation


  • 137 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


5104051


5104051

JDBCJDBCSQL

JDBC

JDBC

JSP

42


5104051

10.1 JDBC

10.1.1 JDBCDriver

(1)JDBC-ODBC BridgeODBC Driver

ODBCODBC

(2)Native-API partly-Java Driver

JDBCDBMSOracleSybaseAccessDBMS


5104051

(3)JDBC-Net All-Java Driver

JDBCDBMSDBMSDBMSJDBC


5104051

(4)Native-protocol All-Java Driver

JDBCDBMS

Java(All-Java)

10.1.2 JDBC Url

jdbc:<>:<>


5104051

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

<>---

jdbc:dbnet//womat:356//fff

ODBC:

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

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


5104051

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";


5104051

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);


5104051

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());


5104051

ex=ex.getNextException();

System.out.println("");

}

}catch(java.lang.Exception ex){

ex.printStackTrace();

}

}

}

22 Statement

1)Statement


5104051

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(?,?)");


5104051

//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();


5104051

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++)

{


5104051

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++){


5104051

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()


5104051

//

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()


5104051

10.2.2 JDBC

JDBC

JDBCsqlDriverManagerDriverPropertyInfoSQL


5104051

sql.Date

sqlDateutilDateutil.DateSQLDATE

Date

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

util.Date19000111311998123


5104051

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

DateString

public static Date valueOf(String s)

StringS--1997-04-12

public String toString()

String--


5104051

sql.Time

util.DateTimeSQLTIMEsql.Dateutil.Date

Time

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

023059

sql.DateTimeStringValueOfStringString122606


5104051

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)


5104051

sql.Types

TypesObjectSQL

public final static int11.1OTHERgetObjectsetObjectJavaObject


5104051

1 TypesSQL


5104051

SQLJava

SQLJavaJavaSQLResultSetgetStatementsetregisterOutParameter

ResultSetSQLgetJDBCSQLJavaSQLJava11-2


5104051

SQLJava

SQLJavaJavaSQLResultSetgetStatementsetregisterOutParameter

ResultSetSQLgetJDBCSQLJavaSQLJava11-2


5104051

2 SQLJava


5104051

JDBC CHARVARCHAR LONGVARCHAR


5104051

JDBC BINARYVARBINARY LONGVARBINARY


5104051

JDBC SQL Server


5104051

JDBC REALDOUBLE FLOAT


5104051

JDBC TIMESTAMP SQL Server datetime smalldatetime datetime 4 smalldatetime 2


5104051

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 );


5104051

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 );


5104051

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";


5104051

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");


5104051

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>

<%


5104051

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())

{


5104051

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>");

}


5104051

rst.close();

stmt.close();

con.close();

%>

</table>

</BODY>

</HTML>


5104051

10.4

JDBC


5104051


5104051

Reference Counting


5104051

1maxConnmaxWaitTimemaxWaitTime


5104051

minConn


5104051

minConnmaxConnminConnminConnminConnmaxConnConnectionPoolgetActiveSizegetOpenSizeActiveSize OpenSize minConnActiveSizemaxConnactiveSizeOpenSize


5104051

"All-All-Nothing"ConnectionConnectionAutoCommitfalse commitrollback


5104051

commitrollback


5104051

Javasynchronized


5104051

DBConnectionPool()DBConnectionManagerJDBCDBConnectionPool


5104051

DBConnectionManager(DBConnectionPool)JDBCConnJDBCConnModel

Conn

Class Conn {

Private java. sgl .Connection con; //

Public Boolean inUse ; //

Public long lastAccess; //

Public int useCount; //

}


5104051

//

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);

} } }


5104051

//

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();


5104051

// ()

.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;

}

}


5104051

//

conn = createConnection();

pool.addElement(conn);

pool1.addElement("true");

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

}

catch (Exception e) {

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

throw new Exception(e.getMessage());

}


5104051

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; }


5104051

JDBCMySQL

JSPJDBCSQL SERVER


5104051

JDBCSQL2

1JDBC

2JSPSQLserver

3JDBC

MyeclipseTomcatJDK


5104051

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;


5104051

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();


5104051

while (rs.next())

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

} catch (Exception ex ){

ex.printStackTrace();

}

%></html>

3SQL serverintputoutputinoutJSP


5104051

1.WEBInternetJDBC

2


  • Login