{"id":231,"date":"2018-07-27T05:20:58","date_gmt":"2018-07-27T05:20:58","guid":{"rendered":"http:\/\/csp12.epgpbooks.inflibnet.ac.in\/?post_type=chapter&p=231"},"modified":"2018-08-02T08:26:34","modified_gmt":"2018-08-02T08:26:34","slug":"jdbc-introduction","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/csp12\/chapter\/jdbc-introduction\/","title":{"rendered":"JDBC \u2013 INTRODUCTION"},"content":{"raw":"
\r\n\r\n\u00a0 \u00a0 JDBC<\/strong>\r\n\r\n \r\n

JDBC stands for Java Database Connectivity. It is used for accessing databases from Java applications.Java is a very standardized programming language, but there are many versions of SQL databases.<\/p>\r\n \r\n\r\nODBC vs. JDBC<\/strong>\r\n\r\n \r\n

Open DataBase Connectivity API can be used to access relational databases. ODBC can be used with Java applied as the form of JDBC-ODBC Bridge. ODBC is not appropriate for direct use from the Java programming language. It uses C interface. There occur a number of drawbacks in the security, implementation, robustness, automatic portability during the calls from Java to native C code.<\/p>\r\n \r\n

Programs that are developed using Java-JDBC API are platform and vendor independent. JDBC involves the logical quote of \u201cwrite once, compile once, run anywhere\u201d. It provides a standard API for tool or database developers and makes it possible to write database applications using pure Java API. JDBC driver manager and\u00a0 JDBC drivers provide the bridge between the database and Java world.<\/span><\/p>\r\n\r\n<\/div>\r\n

\r\n\r\n\u00a0<\/strong>\r\n\r\n JDBC Architecture<\/strong>\r\n\r\n \r\n\r\n<\/div>\r\n

\"\"<\/p>\r\n\r\n

\r\n

Figure 26.1 Architecture of JDBC<\/strong><\/p>\r\n \r\n

Figure 26.1 shows the architecture of the working of JDBC. The application program written in Java code calls the JDBC library. JDBC loads a driver and through the driver, the application code talks to a particular database. A machine can have more than one driver and more than one database. The ideal thing about JDBC is that it can change database engines without changing any application code.<\/p>\r\n \r\n\r\nJDBC Drivers<\/strong>\r\n\r\n \r\n\r\nThere are four types of JDBC Drivers listed below,\r\n\r\n \r\n\r\n\u2022 Type I: Bridge\r\n\r\n\u2022 Type II: Native\r\n\r\n\u2022 Type III: Middleware\r\n\r\n\u2022 Type IV: Pure\r\n\r\n<\/div>\r\n\u00a0 \u00a0 JDBC Driver Types<\/strong>\r\n\r\n \r\n\r\nFigure 26.2 shows the types of Drivers.\r\n

\"\"<\/p>\r\n \r\n

\r\n

Figure 26.2 Types of JDBC Drivers<\/strong><\/p>\r\n \r\n

The Type I driver is an JDBC-ODBC bridge driver that uses ODBC driver to connect to the database. This JDBC-ODBC bridge driver converts JDBC method calls into the ODBC function calls.<\/p>\r\n

The Type II drivers are the Native API driver that uses the client-side libraries of the database. This driver converts JDBC method calls into native calls of the database API. It is not written entirely in Java.<\/p>\r\n

The Type III drivers are called the Network Protocol driver that uses middleware which is an application server that converts JDBC calls directly or indirectly into the vendor-specific database protocol. It is fully written in Java.<\/p>\r\n

The Type IV drivers are the thin driver that converts JDBC calls directly into the vendor-specific database protocol. That is why it is known as thin driver. It is fully written in Java language.<\/p>\r\n \r\n\r\nType I Drivers<\/strong>\r\n\r\n \r\n

These drivers uses bridging technology. They require installation\/configuration on client machines.These drivers are not recommended for Web. Example of Type I Driver is ODBC Bridge.<\/p>\r\n\r\n<\/div>\r\n\u00a0 \u00a0Type II Drivers<\/strong>\r\n

\r\n\r\n \r\n

These drivers are native API drivers. They require installation\/configuration on client machines. Type II drivers are used to leverage existing CLI libraries and are usually not thread-safe, mostly obsolete now. Examples include Intersolv Oracle Driver, WebLogic drivers.<\/p>\r\n \r\n\r\nType III Drivers<\/strong>\r\n\r\n \r\n

These drivers call middleware server, usually on database host. They are very flexible and allow access to multiple databases using one driver. There is only the need to download one driver, but it is another server application to install and maintain. An example of it is Symantec DBAnywhere.<\/p>\r\n \r\n\r\nType IV Drivers<\/strong>\r\n\r\n \r\n

There is full 100% percentile Pure Java referred as the Holy Grail. These drivers use Java networking libraries to talk directly to database engines. A disadvantage here is the need to download a new driver for each database engine. Examples include Oracle, mySQL etc.<\/p>\r\n \r\n\r\nLet us now look in detail about JDBC Drivers.\r\n\r\n \r\n\r\nJDBC Driver<\/strong>\r\n\r\n \r\n\r\nThe Driver class names for each of the database is given below,\r\n\r\nIn Oracle, the driver class name can be given as,\r\n\r\noracle.jdbc.driver.OracleDriver\r\n\r\nIn MySQL, the driver class name can be given as,\r\n\r\ncom.mysql.jdbc.Driver\r\n\r\nIn MS SQL, the driver class name can be given as,\r\n\r\ncom.microsoft.jdbc.sqlserver.SQLServerDriver\r\n\r\n<\/div>\r\n

\r\n\r\n\u00a0 <\/strong>\r\n\r\nJDBC Limitations\u00a0<\/strong>\r\n\r\n \r\n

The limitations of working with JDBC is that there are no scrolling cursors available and no bookmarks can be made while processing the records thereby it allows only sequential processing.<\/p>\r\n \r\n\r\nWorking with JDBC<\/strong>\r\n\r\n \r\n\r\nPackage to be Imported<\/strong>\r\n\r\n \r\n\r\nJDBC is implemented via classes in the java.sql package.\r\n\r\nimport java.sql.*\r\n\r\n \r\n\r\nLoading a Driver Directly<\/strong>\r\n\r\n \r\n\r\nDriver d = new foo.bar.MyDriver();\r\n\r\nConnection c = d.connect(...);\r\n\r\n \r\n

We can create a Driver and load the driver. Using the Driver object we need to create a Connection object . This is not recommended but instead we can use a Driver Manager but this is useful if we know that we need this particular driver.<\/p>\r\n \r\n\r\nDriverManager<\/strong>\r\n\r\n \r\n

A Driver Manager tries all the drivers. It uses the first one that works. When a driver class is first loaded, it registers itself with the Driver Manager. Therefore, to register a driver, we need to just load the driver.<\/p>\r\n \r\n\r\nRegistering a Driver<\/strong>\r\n\r\n \r\n\r\nEither of the below two methods can be used to register a driver.\r\n\r\n \r\n\r\n1. Statically load driver\r\n\r\nClass.forName(\u201cfoo.bar.MyDriver\u201d);\r\n\r\nConnection c = DriverManager.getConnection(...);\r\n\r\n2. Use the jdbc.drivers system property\r\n\r\n<\/div>\r\n\u00a0 \u00a0JDBC Object Classes<\/strong>\r\n

\r\n\r\n \r\n\r\nThe classes that are used from the imported java.sql.* package is listed below.\r\n\r\n \r\n\r\n\u2022 DriverManager\r\n\r\nThis class is used for loading and choosing the drivers.\r\n\r\n\u2022 Driver\r\n\r\nThis class performs the connection to actual database.\r\n\r\n\u2022 Connection\r\n

\u00a0 \u00a0The Connection class creates the connectivity through a series of SQL statements to and from the database.<\/p>\r\n\u2022 Statement\r\n\r\nThe Statement object created using this class represents a single SQL statement.\r\n\r\n\u2022 ResultSet\r\n\r\nThe ResultSet object represents a set of the records returned from a statement.\r\n\r\n \r\n\r\nJDBC URL's<\/strong>\r\n\r\n \r\n\r\nThe format of JDBC URL is given below that has components like subprotocol and source.\r\n\r\n \r\n\r\nFor example,\r\n\r\n \r\n\r\njdbc:subprotocol:source\r\n\r\nHere each driver has its own subprotocol . Each subprotocol has its own syntax for the source.\r\n\r\nThe URL for Type I Driver which is an ODBC Bridge is given below.\r\n\r\njdbc:odbc:DataSource\r\n\r\nExample: jdbc:odbc:jnf\r\n\r\nThe URL to connect to MySQL database is given below,<\/span>\r\n\r\njdbc:mysql:\/\/host[:port]\/database<\/span>\r\n\r\nExample: jdbc:mysql:\/\/foo.nowhere.com:4333\/jnf<\/span>\r\n\r\n<\/div>\r\n

\r\n\r\n\u00a0 \u00a0 \u00a0DriverManager<\/strong>\r\n\r\n \r\n\r\nConnection getConnection (String url, String user, String password)\r\n

This connects to given JDBC URL with given user name and password and returns a Connection object. It can throw java.sql.SQLException when connection cannot be established.<\/p>\r\n \r\n\r\nConnection<\/strong>\r\n\r\n \r\n

A Connection represents a session with a specific database. Within the context of a Connection, SQL statements are executed and results are returned. A program can have multiple connections to a database. A connection provides \u201cmetadata\u201d -- information about the database, tables, and fields, along with the methods to deal with transactions.<\/p>\r\n \r\n\r\nObtaining a Connection<\/strong>\r\n\r\n \r\n

The snippet code for obtaining the connection with JDBC-ODBC bridge is given below. First we need to dynamically load and register the driver. Then establish a connection by creating a Connection object using the DriverManager class.<\/p>\r\n \r\n\r\nString url = \"jdbc:odbc:jnf\";\r\n\r\ntry {\r\n\r\nClass.forName(\"sun.jdbc.odbc.JdbcOdbcDriver\");\r\n\r\nConnection con = DriverManager.getConnection(url);\r\n\r\n}\r\n\r\ncatch (ClassNotFoundException e)<\/span>\r\n\r\n{ e.printStackTrace(); } catch (SQLException e)<\/span>\r\n\r\n{ e.printStackTrace(); }<\/span>\r\n\r\n<\/div>\r\n

\r\n\r\n\u00a0 \u00a0<\/strong>\r\n\r\nConnection Methods<\/strong>\r\n\r\n \r\n

In the context of a Connection, SQL statements are executed and results are returned. Statement objects can be created using createStatement() method on the Connection object. Similarly PreparedStatement object can be created when we use prepareStatement() method on the Connection object and CallableStatement object can be created when we use prepareCall() method on the Connection object. The syntax of these methods are given below.<\/p>\r\n \r\n\r\n1. Statement createStatement()\r\n\r\nThis returns a new Statement object.\r\n\r\n2. PreparedStatement prepareStatement(String sql)\r\n\r\nThis returns a new PreparedStatement object.\r\n\r\n3. CallableStatement prepareCall(String sql)\r\n\r\nThis returns a new CallableStatement object.\r\n\r\n \r\n\r\nStatement<\/strong>\r\n\r\n \r\n

A Statement object is used for executing a static SQL statement and obtaining the results produced by it.<\/p>\r\n \r\n\r\nStatement Methods<\/strong>\r\n\r\n \r\n\r\nThere are three methods which works with the Statement object.\r\n

ResultSet executeQuery(String)<\/p>\r\n

This method executes an SQL SELECT statement that returns a single ResultSet.<\/p>\r\n

int executeUpdate(String)<\/span><\/p>\r\n

This method is used to execute an SQL INSERT, UPDATE or DELETE statement and returns the number of rows changed.<\/span><\/p>\r\n

boolean execute(String)<\/span><\/p>\r\n

This method is used to execute an SQL statement that may return multiple results.<\/span><\/p>\r\n\r\n<\/div>\r\n

\r\n\r\n\u00a0 \u00a0 <\/strong>\r\n\r\nResultSet<\/strong>\r\n\r\n \r\n

A ResultSet provides access to a table of data generated by executing a Statement. Only one ResultSet per Statement can be open at once. The table rows are retrieved in sequence. A ResultSet maintains a cursor pointing to its current row of data. The 'next()' method is used to move the cursor to the next row.<\/p>\r\n \r\n\r\nResultSet Methods<\/strong>\r\n\r\n \r\n\r\nBelow is the list of methods to work with the ResultSet object.\r\n