??JDBC??????

1
??JDBC??????

• 1-1??
• 1-2???????
• 1-3SQL??
• 1-4SQL???????
• 1-5MySQL Connector/J????
• 1-6 MySQL???
• 1-7???????JDBC
• 1-8??JDBC???ACCESS???
• 1-9?????

User 2006/12/12 ???? ??? http//www.aasir.com.tw C
??????
2
1-1??

• ??Java???????JDBC????????????,??????Java??JDBC???M
  ySQL???????Java????JDBC???????????????Oracle????Sy
  base????MSQL????MySQL????Access???????????????Java
  ??????JDBC?????MySQL????Access????

3
(No Transcript)
4
1-2???????

• ??Java???????,JDBC?????????????,????SQL?????????,?
  ??????????????ResultSet,?????Java?????

5
(No Transcript)
6
1-3SQL??

• ??????DDL(data definition language)????????schema(
  ??),??????????????????????????????????????????????
  ????,???schema??????????????

7
1-3-1????????????

• ????create database ???????????????
• ??create database ?????
• ?MySQL???create????????

8
????use???????server??????use database ?????
9

• ????drop??????
• ??drop database ?????
• ??drop????????
• ????show??????????,show databases???????????

10
1-3-2?????

• ???????,???????????(?????)????????????????????????
  ???(?????)?
• ???????????????????????MySQL??????????????????????
  ????MySQL?????????

11
1-3-3??????????????

• ???????????????????
• (1)????????
• ????create??????
• ??create table ?????
• ( ???? ????(????),
• ???? ????(????),
• ???? ????(????),
• ???? ????(????),
• primary key(????) )

12
????create??????,??primary key(??)?????????,??????
TYPE?MyISAM?

• mysqlgtuse tutorb
• mysqlgtcreate table student
• -gt(number int not null,
• -gtname char(20) not null,
• -gtgrade int not null,
• -gtprimary key(number)) TYPEMyISAM

13
???number?????,name?????,grade???????????,???numbe
r,name,?grade,?primary key?number
14
1-4SQL???????

• ??????DML(data manipulation language)??????schema?
  ????,?????????????DML?????????????????????????????
  ????????????
• ??????????(select)?????(insert)???????(update)????
  ?(delete),????SQL??????

15
1-4-1????select

• select ??????
• from ?????
• where ??
• group by ????
• order by ????
• having by ????group by ????????
• ??select???total_tutor??????????3?????
• select from total_tutor where number3

16
(No Transcript)
17

• ??total_tutor????number?name??,????total_tutor.Num
  ber?total_tutor.name?????,??????number3????
• select total_tutor.Number,total_tutor.name from
  total_tutor where number3

18
1-4-2SQL?????

• ??SQL???????????????,??select?from?????
• select lt???????gt
• from lt????gt
• wherelt??gt
• group by lt????gt
• having lt????gt
• order by lt??gt
• ????tutor.number as n?????????n,????having????????
  ??,???????????n?????
• Select tutor.number as n,tutor.name
• From tutor
• Where tutor.number lt256 and tutor.number gt250
• Group by experience
• Having n gt250 and nlt256
• Order by salary

19
????250?256???,??????????????
20
1-4-3insert????

• ????insert?????????????
• ??
• ????????,800,??????????(name,salary,location)?
  ????tutor?????
• insert into tutor
• (name,salary,location)
• values
• (???,800,??????)

21

• ???
• insert into ???
• values
• (??1??,??2??,??3??)

22

• ????insert?????(1,'???',98)??student?????
• INSERT INTO student VALUES (1,'???',98)

23
1-4-4delete????

• ????delete????????????
• ??
• ??????tutor???????321?323????????
• delete from tutor
• where numberlt323 and number gt321
• ??????delete?????????,??????tutor?????
• delete from tutor

24
1-4-5update

• ???????????????update??
• ??
• update ???
• set ?? ?????
• where ??

25

• ????update total_tutor????????3???,????depar?????
  ????
• update total_tutor
• set depar"????"
• where Number3

26

• ????update???,?depar?????????
• ????????????,depar????????

27
1-5MySQL Connector/J????

• ????Java???MySQL???,??????JDBC?????MySQL?????????w
  ww.mysql.com/products/connector/j/???JDBC???mysql-
  connector-java-3.1.12?
• ??mysql-connector-java-3.1.12???MySQL5???,????????
  ?????????JDBC???Access???????????????????JDBC?????
  MySQL4?MySQL3???,????????????????www.mysql.com/pro
  ducts/connector/j/??MySQL????????????
• ?????mysql-connector-java-3.1.12-bin.jar????????C
  \Program Files\Java\jdk1.\jre\lib\ext???,?????J
  ava???jre\lib\ext??????JSP? Servlet?????JDBC??????
  ???????????classpath??????????

28
1-6 MySQL???

• ????MySQL?????,???????????????Next?

29
???????MySQL??????,????????Skip
Sign-UP?????Next????
30
?????????Console?????MySQL????????Next????
31
?????????root?????????????root??????MySQL???,?????
?Next???????????322739?
32
????Finish???
33
????????????MySQL?MySQL Server5.0?MySQL Command
Line Client,?????

• ?????????root????????MySQL?????

34
1-6-1?????

• ??????MySQL??????My_Books???,??????????authors????
  ??publisher??????,?????insert????????????????
• ??My_Books.sql????

35
???My_Books.sql?SQL?????MySQL?????-u????????????ro
ot,-p???????lt???????
36

• ??????My_Books.sql?,???????MySQL????,?????my_books
  ????

37
?????my_books????,?authors????publisher????
38
1-7???????JDBC

• JDBC??????????JAVA?????????JAVA?????????
• (1).?????,????Class.forName(JDBCDriverClass)
• ?????????,????java.sql.Driver ??????Access?MySQL?O
  racle??????

39
(No Transcript)
40
classpathclasspathc\ mysql-connector-java-3.1
.12-bin.jarc\classes12.jar

• Access????JDBC-ODBC?????????JDK??MySQL????JDBC????
  ??mysql-connector-java-3.1.12-bin.jar??Oracle????J
  DBC??????classes12.jar???????MySQL????Oracle??????
  ?JDBC?????
• ????mysql-connector-java-3.1.12-bin.jar?classes12.
  jar?????classpath???DOS???,??????MySQL????Oracle??
  ??????mysql-connector-java-3.1.12-bin.jar???classe
  s12.jar?????c\????

41
??MySQL?????????

• static final String JDBC_DRIVER
  "com.mysql.jdbc.Driver"
• Class.forName( JDBC_DRIVER )

42
??Access?????????

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

43

• ?????mysql-connector-java-3.1.12-bin.jar????????C
  \Program Files\Java\jdk1.5.0\jre\lib\ext???,?????J
  ava?jre\lib\ext??????JSP? Servlet?????JDBC????????
  ??

44
(2)????

• Connection????????,????Java????????
• ????DriverManager???getConnection()?????????????My
  SQL???????
• jdbcmysql//localhost/My_Books?????????My_Books??
  ??
• root???MySQL???????,???????????322739?
• static final String DATABASE_URL
  "jdbcmysql//localhost/My_Books"
• Connection connection DriverManager.getConnecti
  on( DATABASE_URL, "root", "322739" )

45

• ????DriverManager???getConnection()?????????????Ac
  cess???????
• jdbc.odbcbooks?????JDBC-ODBC?Access???books?
• String sourceURL "jdbcodbcbooks"
• Connection databaseConnection
  DriverManager.getConnection(sourceURL)

46
DATABASE_URL??????????????
47
(3)????

• ????????statement?Statement??????SQL??????????????
  ??????????????Conection?,??????statement?????creat
  eStatement()???

48
(4)????

• ???????executeQuery()???SQL?????DDL?????????????Re
  sultSet?????????????authors?????????ResultSet????
• ResultSet resultSet statement.executeQuery(
  SELECT authorID, Name FROM authors" )

49
(5)???????

• ResultSet??????,?????????,????????????????????next
  ()???????????,next()?????ResultSet???????????Resul
  tSet?????get()?????ResultSet???????
• authorResults.getString("authid")?getString()?????
  ?????authid?????authorResults.getString("firstname
  ")?getString()??????????firstname?????authorResult
  s.getString("lastname")?getString()???????????last
  name?????

50

• ResultSet authorResults statement.executeQuery(q
  ueryWildcard)
• int row 0
• while(authorResults.next())
• System.out.println("Row " (row) ") "
• authorResults.getString("authId") " "
  
• authorResults.getString("Name")

51

• ??Authors.java
• ??????java.sql.Connection????Connection?
• ??????java.sql.Statement????Statement?
• ??????java.sql.DriverManager????????DriverManager?
  
• ??????java.sql.ResutlSet?????ResultSet?
• ??????java.sql.ResultSetMetaData???????ResultSetMe
  taData?
• ??????java.sql.SQLException,SQL????SQLExceptin?
• ???????MySQL?????,??Class.forName()????????
• ????????????????????,??Connection?????connection?
• ????????????????,??Statement?????statement?
• ???????????????????statement?????executeQuery()???
  
• ???????????????????
• ?????ResultSetMetaData???metadata??,????ResultSet?
  ??????
• ??????metaData.getColumnCount()????ResultSet??????
  ???
• ??????metaData.getColumnName()????ResultSet???????
  ??
• ??????ResultSet.getObject()??????ResultSet????????
  
• ????????????statement?
• ???????????connection?

52
(No Transcript)
53
(No Transcript)
54
(No Transcript)
55
1-7-1??Authors.java

• ????????,?????Authors.java???

56
1-7-2??

• ????Authors??????JDBC?????,E\Java2\database\examp
  le\mysql\mysql-connector-java-3.1.12\mysql-connect
  or-java-3.1.12\ mysql-connector-java-3.1.12-bin.ja
  r?mysql-connector-java-3.1.12-bin.jar????MySQL????
  JDBC????????????????,???????,???????E\????,??????
  ?????,????MySQL??????Access?????????JDK?,?????????
  ?

57
(No Transcript)
58
1-8??JDBC???ACCESS???

• ??Java???,??JDBC?ODBC??????Windows??????Access????

59
1-8-1??Windows Xp?????

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

60
?????????????????
61
????Microsoft Access Driver(.mdb),????????
62
??????????books,?????????????????????,???????
63

• ????books.mdb???????????????Access??????????,?????
  ???????

64

• ???????????????,?????Windows Xp??ODBC?????Access??
  ??

65
????????ODBC??????????????
66

• ???????JDBC-ODBC?????sun.jdbc.odbc.JdbcOdbcDriver?
  ?????Access???books.mdb?????????,???????Access????
  ODBC???????books.mdb??

67
1-8-2??????

• ?????????sun.jdbc.odbc.JdbcOdbcDriver,??Access????
  ?????Class.forName()???ODBC???????????Class???????
  forName()???????????????????,forName()???????Class
  NotFoundException????????????,?????????????,??????
  ?DriverManager????????????DriverManager???????????
  
• // ??????
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"
  )

68
1-8-3????????

• DriverManger???????getConnection()???Connection???
  ??sourceURL???Access???books??????
• // ???????????
• String sourceURL "jdbcodbcbooks"
• // ??DriverManager ?????
• Connection databaseConnection
  DriverManager.getConnection(sourceURL)

69
????Access??????,?????Access??????????

• // ???????????
• String sourceURL "jdbcodbcbooks"
• // ??DriverManager ?????
• Connection databaseConnection
  DriverManager.getConnection(sourceURL,??,??)

70

• ??JDBC_Connection.java
• ??????sql????????Connection?
• ??????sql????????????DriverManager?
• ??????sql????SQLException?????
• ?????????trycatch???????????????try????
• ??????JDBC-ODBC?????
• ??????DriverManager???getConnection()???????????

71
(No Transcript)
72
????Eclipse??????
73

• ???????????????????????????????,???????Classpath??
  ??,??????.????????????????????

74
????????
75
1-8-4URLs?JDBC

• JDBC-ODBC??????JDK??,?????sun.jdbc.odbc.JdbcOdbcD
  river,??Java?????????????????JDBC-ODBC???JDBC????
  ?ODBC?????

76

• ??EssentialJDBC.java
• ??????????????EssentialJDBC???
• ???????????????main()?
• ???????SQLExample???getResultsByColumnName()???
• ???????SQLExample???getResultsByColumnPosition()??
  ?
• ???????SQLExample???closeConnection()???
• ????????????EssentialJDBC()???,????books????
• ????????connection.createStatement()?????statement
  ?????
• ???????????????????????????
• ???????statement???executeQuery()?????SQL?????????
  ?????java.sql.RessultSet???????
• ?????authorResults.next()???????????
• ?????authorResults.getString(authid)???books????
  authid??????
• ?????authorResults.close()?????????
• ???????????getResultsByColumnPostion()????????????
  ??????
• ?????authorResults.getString(i)???books????,?i????
  ??
• ???????????????closeConnection()?
• ?????????????connection.close()?

77
(No Transcript)
78
(No Transcript)
79
(No Transcript)
80
????????
81
??MakingAStatement.java????????????????statement
???executeQuery()?????SQL???
82
???Eclipse??????
83
????????????????
84
1-9?????

• ?????????,???????????SQL??,????,???????????????,??
  ???

85
(No Transcript)
86
(No Transcript)
87
(No Transcript)
88
(No Transcript)
89
(No Transcript)
90
(No Transcript)
91
(No Transcript)