Java Programming JDBC Vyacheslav Grebenyuk CTDE AI dept
Java Programming: JDBC Vyacheslav Grebenyuk CTDE, AI dept. , Kh. NURE (С) ЦТДО, каф. Искусственного интеллекта, ХНУРЭ, 2006
JDBC n JDBC (Java Database Connectivity) API allows Java programs to connect to databases n Database access is the same for all database vendors n The JVM uses a JDBC driver to translate generalized JDBC calls into vendor specific database calls n There are four general types of JDBC drivers ¨ 9/17/2021 We will look at Type 4 … (С) ЦТДО, каф. Искусственного интеллекта, ХНУРЭ, 2006 3
Pure Java Driver (Type 4) n n n These drivers convert the JDBC API calls to direct network calls using vendor-specific networking protocols by making direct socket connections with the database It is the most efficient method to access database, both in performance and development time It is the simplest to deploy All major database vendors provide pure Java JDBC drivers for their databases and they are also available from third party vendors For a list of JDBC drivers, refer to ¨ 9/17/2021 http: //industry. java. sun. com/products/jdbc/drivers (С) ЦТДО, каф. Искусственного интеллекта, ХНУРЭ, 2006 4
Pure Java Driver (2) DB Client Java Application Server Data Source JDBC API 9/17/2021 JDBC Driver (С) ЦТДО, каф. Искусственного интеллекта, ХНУРЭ, 2006 5
Typical JDBC Programming Procedure 1. 2. 3. 4. 5. 9/17/2021 Load the database driver Obtain a connection Create and execute statements (SQL queries) Use result sets (tables) to navigate through the results Close the connection (С) ЦТДО, каф. Искусственного интеллекта, ХНУРЭ, 2006 6
Driver Manager n n n The purpose of the java. sql. Driver. Manger class in JDBC is to provide a common access layer on top of different database drivers used in an application Driver. Manager requires that each driver required by the application must be registered before use, so that the Driver. Manager is aware of it Load the database driver using Class. Loader : ¨ 9/17/2021 Class. for. Name (“oracle. jdbc. driver. Oracle. Driver”); (С) ЦТДО, каф. Искусственного интеллекта, ХНУРЭ, 2006 7
Connecting to a Database n Type 4 JDBC Driver – Oracle Server Class. for. Name (“oracle. jdbc. driver. Oracle. Driver”); con = Driver. Manager. get. Connection ( “jdbc: oracle: thin: @bonsai. ite. gmu. edu: 1521: ite”, “accountname", “password”); n Type 4 JDBC Driver – My. SQL Server Class. for. Name (“org. gjt. mm. mysql. Driver”); con = Driver. Manager. get. Connection (“jdbc: mysql: //localhost/databasename”, uid, passwd); 9/17/2021 (С) ЦТДО, каф. Искусственного интеллекта, ХНУРЭ, 2006 8
Creating Tables n Creating a Coffee table CREATE TABLE COFFEES (COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, SALES INTEGER, TOTAL INTEGER) • Creating JDBC statements Statement stmt = con. create. Statement (); SQL query • Execute a statement stmt. execute. Update (“CREATE TABLE COFFEES “ + “(COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, “ + “SALES INTEGER, TOTAL INTEGER)”); 9/17/2021 (С) ЦТДО, каф. Искусственного интеллекта, ХНУРЭ, 2006 9
Execute Statements n n n 9/17/2021 This uses execute. Update because the SQL statement contained in create. Table. Coffees is a DDL (data definition language) statement Statements that create a table, alter a table, or drop a table are all examples of DDL statements and are executed with the method execute. Update is also used to execute SQL statements that update a table (С) ЦТДО, каф. Искусственного интеллекта, ХНУРЭ, 2006 10
Execute Statements n n n 9/17/2021 In practice, execute. Update is used far more often to update tables than it is to create them because a table is created once but may be updated many times The method used most often for executing SQL statements is execute. Query is used to execute SELECT statements, which comprise the vast majority of SQL statements (С) ЦТДО, каф. Искусственного интеллекта, ХНУРЭ, 2006 11
Entering Data into a Table Statement stmt = con. create. Statement(); stmt. execute. Update ( "INSERT INTO COFFEES " + "VALUES ('Colombian', 101, 7. 99, 0, 0)"); stmt. execute. Update ( "INSERT INTO COFFEES " + "VALUES ('French_Roast', 49, 8. 99, 0, 0)" ); stmt. execute. Update ( "INSERT INTO COFFEES " + "VALUES ('Espresso', 150, 9. 99, 0, 0)" ); stmt. execute. Update ( "INSERT INTO COFFEES " + "VALUES ('Colombian_Decaf', 101, 8. 99, 0, 0)" ); stmt. execute. Update ( "INSERT INTO COFFEES " + "VALUES ('French_Roast_Decaf', 49, 9. 99, 0, 0)" ); 9/17/2021 (С) ЦТДО, каф. Искусственного интеллекта, ХНУРЭ, 2006 12
Getting Data From a Table Result. Set rs = stmt. execute. Query ("SELECT COF_NAME, PRICE FROM COFFEES"); while (rs. next()) { String s = rs. get. String ("COF_NAME"); float n = rs. get. Float ("PRICE"); System. out. println (s + " " + n); } 9/17/2021 (С) ЦТДО, каф. Искусственного интеллекта, ХНУРЭ, 2006 13
JDBC Data Source Architecture JDBC Application Database JNDI Connection Manager 9/17/2021 (С) ЦТДО, каф. Искусственного интеллекта, ХНУРЭ, 2006 14
- Slides: 15