JDBC??????

1
JDBC??????
2
????

• JDBC?ODBC
• JDBC??????
• ???????
• ????
• ???
• JDBC??????
• Tomcat???JNDI???????

3
JDBC?ODBC

• ????????,?Oracle?Sybase?Microsoft SQL Server?IBM
  DB2?,?????????????????????????????????????????????
  ????????????????????????????????????????

4
?????????????
5
????????
6

• ??????????API????,???????API???????????????,??????
  ??????????????,??????
• ??????????????????????????API?????????API?????????
  ?????????,??????????????API??????
• ???????Microsoft?ODBC(Open Database Connectivity)

7
ODBC
8
JDBC

• JDBC(Java Database Connectivity)????????Java
  API,????SQL???ODBC??????????????,JDBC???Java??,???
  Java?????????JDBC????????,?????????????

9
JDBC???????

• JDBC-ODBC?(??1????)
• Native API(??2????)
• JDBC NET(??3????)
• ????(??4????)

10
??1????
11
??2????
12
??3????
13
??4????
14
JDBC??????

• JDBC-ODBC?,????????,????JDK????,???Windows???????O
  DBC???,???????System DSN?
• ??????,????????????JDBC????,????????????,?jar?????
  CLASSPATH??????Tomcat???,??????????Web?????WEB-INF
  /lib??,??common/lib??,???shared/lib????

15
???????

• ???????
• ??????????
• ??????
• ????
• ????
• ??????
• ????

16
????

• ???????
• ??SQL??
• ??JDBC Statement??
• ???????
• ???????

17
???????

• ????java.sql?
• ??JDBC????
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
• ????,??DriverManager??????getConnection(),????????
  ??(Connection??)
• Connection conn DriverManager.getConnection(jdb
  codbcmydatabse,username,password)

18
????????

• ????????????????,???????????????????????????????
• ??,???????????????????????????????finally?????,???
  ????????????????,???????????

19

• Connection connnull
• try
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
• connDriverManager.getConnection("jdbcodbcmydat
  abase")
• //?????
• catch(SQLException e)
• System.out.println(e)
• catch(ClassNotFoundException e)
• System.out.println(e)

20

• finally
• if(conn!null)
• try
• conn.close()
• catch (SQLException e)
• System.out.println(e)

21
??JDBC Statement??

• ???JDBC??SQL????,??????Statement??????Connection??
  ?createStatement()??,???????Statement???
• ????
• Statement stmt conn.createStatement()
• ????executeUpdate()??????????????(???,???????????)
  ,?????????????????
• ??executeQuery()?????????,????ResultSet??

22
???

• ???????SQL?????????????
• String createTableCoffees "CREATE TABLE COFFEES
  (COFFEE_NAME VARCHAR(32), SUP_ID INTEGER, PRICE
  FLOAT, SALES INTEGER, TOTAL INTEGER)"
• ??JDBC Statement??,??executeUpdate()????SQL??,????
  ?????
• Statement stmt conn.createStatement()
• stmt.executeUpdate(createTableCoffees)

23
????

• ????Statement???executeUpdate()????SQL??????????
• Statement stmt con.createStatement()
• String sql"INSERT INTO COFFEES(COFFEE_NAME,
  SUP_ID, PRICE, SALES, TOTAL) VALUES ('Colombian',
  101, 7.99, 0, 0)
• int row stmt.executeUpdate(sql)

24
??

• ??Statement???executeQuery()????SQL????,??????????
  (ResultSet),????????????????????
• ??ResultSet??next????????????????,???????????next(
  )????false?,???????????????
• ????????????close()?????
• ??ResultSet??getXXX()?????????????,??XXX??????????
  ?

25

• String query "SELECT COFFEE_NAME, PRICE FROM
  COFFEES"
• ResultSet rs stmt.executeQuery(query)
• while (rs.next())
• String s rs.getString("COFFEE_NAME")
• float n rs.getFloat("PRICE")
• out.println("ltpgt"s" "n)
• rs.close()

26

• ?getXXX()????????????????,???????????,1???????,2??
  ?????,???????????????
• String s rs.getString(1)
• float n rs.getFloat(2)
• ??????????????,??????getString()?????????????

27
ResultSet???????

• first()
• beforeFirst()
• lastLast()
• afterLast()
• getRow()
• absolute()
• next()
• previous()

28
????

• String updateString "UPDATE COFFEES SET SALES
  75 WHERE COFFEE_NAME LIKE 'Colombian'"
• int row stmt.executeUpdate(updateString)

29
????

• String deleteString "DELETE FROM COFFEES "
  "WHERE COFFEE_NAME 'Colombian'"
• int row stmt.executeUpdate(deleteString)

30
???(MetaData)

• ?????????(???)
• ?????
• ??????

31
?????????

• ResultSet rs stmt.executeQuery("select from
  COFFEES")
• ResultSetMetaData rsmd rs.getMetaData()
• int numberOfColumns rsmd.getColumnCount()

32
??????????

• String columnName rsmd.getColumnLabel(i)
• int columns rsmd.getColumnCount()
• for (int i 1 i lt columns i)
• String name rsmd.getColumnTypeName(i)
• out.print("The type of column " i " is "
  name)

33
??preparedStatement

• ???????????SQL??,????praparedStatement????????????
  ???
• PreparedStatement updateSales
  con.prepareStatement("UPDATE COFFEES SET SALES
  ? WHERE COFFEE_NAME LIKE ? ")
• updateSales.setInt(1, 75)
• updateSales.setString(2, "Colombian")
• updateSales.executeUpdate()
• ????(??)

34
???userinfo

• Create Table userinfo(
• userid varchar(50),
• passwd varchar(50),
• username varchar(50),
• gender varchar(50)
• )

35
????

• ??(Transaction)?DBMS?????,???????????????????4???
  
• ???(Atomic)
• ???(Consistency)
• ???(Isolation)
• ???(Durability)

36

• ?????????JDBC?,?????,??SQL????????????????,??????
  ?SQL???????????,?????????????????????????
• con.setAutoCommit(false)
• ????????????????,???commit()???????????SQL?????
• con.commit()

37
Rollback

• ????(rollback)???????SQL???????,????????????SQL??
  ?????????SQLException,????trycatch???????,?catch?
  ?????
• ????????

38
??JavaBeans???????

• ??????????????????Java Bean?,????JSP????Java??
• ??JSP??????????????,??????????????????????????(?be
  an?????session??application????),?????????????????
  
• ?????????????????close()?????????
• ????????

39
JDBC???

• ???????????????????,???????????,??????????????????
  ????????,?????????????????????????????,??????????,
  ?????????
• ??????n??????
• ??????????????????
• ??????n1???,???????????,??????????
• ???????,?????????

40
?bean?Servlet?????

• PooledConnection????????,???????????inuse
• ConnectionPool???????????
• ???
• ????(?????????)
• ????????
• ???????????

41

• ConnectionPoolServlet?Servlet,????????,???????,??
  ????????ServletContext????????????????,??Connecti
  onPool?????????????
• ?web.xml???servlet?????,??????????????????
• ???????????????????????????

42
??JNDI?DBCP????????
43
????

• ?server.xml???context??,??????JNDI JDBC???
• ?web.xml???JNDI???(ltresoure-refgt)
• ?Web?????????Bean,?????JNDI???,????????,??????????
  
• ?JSP???????Java Bean????????
• ??????????