1 / 30

Database Access with JDBC

Database Access with JDBC. JDBC Introduction. If you're interested in connecting your Java applications to standard SQL databases like Oracle, MySQL, SQL Server, and others, the Java JDBC technology is exactly what you need.

derron
Download Presentation

Database Access with JDBC

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Accesswith JDBC

  2. JDBC Introduction • If you're interested in connecting your Java applications to standard SQL databases like Oracle, MySQL, SQL Server, and others, the Java JDBC technology is exactly what you need. • The combination of Java JDBC and standard SQL queries creates a simple and powerful database solution. • JDBC makes the simple things easy -- without making the complex tasks too difficult either.

  3. JDBC Introduction • JDBC provides a standard library for accessingrelational databases • – API standardizes • • Way to establish connection to database • • Approach to initiating queries • • Method to create stored (parameterized) queries • • The data structure of query result (table) • - Determining the number of columns • - Looking up metadata, etc. • – API does not standardize SQL syntax • • JDBC is not embedded SQL • – JDBC classes are in the java.sql package • • Note: JDBC is not officially an acronym;unofficially, “Java DataBase Connectivity” iscommonly used

  4. JDBC Introduction • JDBC consists of two main layers: • the JDBC API supports application-to-JDBC Manager communications; • the JDBC Driver API supports JDBC Manager-to-Driver implementation communications. • The Manager handles communications with multiple drivers of different types from direct-interface implementations in Java to network drivers and ODBC-based drivers.

  5. JDBC Introduction • In terms of Java classes, the JDBC API consists of: • java.sql.Environment - allows the creation of new database connections; • java.sql.Connection - connection-specific data structures; • java.sql.Statement - container class for embedded SQL statements; • java.sql.ResultSet - access control to results of a statement.

  6. JDBC connections • Before you start working with JDBC, you'll need a copy of the Java JDK. • If you don't have it already, you can get the JDK/SDK for free at Sun's Java web site, or it will also be included with many IDEs that you can use, including Eclipse and NetBeans. • Once you have the JDK, the next thing you need to do is to get the correct JDBC driver for your database. • In most cases the JDBC driver will be provided by your database vendor.

  7. JDBC connections • Establishing a JDBC database connection in two steps • Once you have the correct JDBC driver installed, establishing a JDBC connection from your Java programs to your SQL database is pretty easy. • Regardless of whether you're trying to connect to Oracle, SQL Server, MySQL, Postgres,mSQL, or Interbase (or any other JDBC data source), establishing a connection to an SQL database with Java JDBC is a simple two-step process: • Load the JDBC driver. • Establish the JDBC connection to your database. Listing 1 (the next slide) provides the full source code required to establish a JDBC connection to a mSQL database on a server named "www.myserver.com".

  8. A Java JDBC Connection example • // Establish a connection to a mSQL database using JDBC. • import java.sql.*; • class JdbcTest1 { • public static void main (String[] args) { • try { // Step 1: Load the JDBC driver. Class.forName("com.imaginary.sql.msql.MsqlDriver"); • // Step 2: Establish the connection to the database. • String url = "jdbc:msql://www.myserver.com:1114/contact_mgr"; Connection conn = DriverManager.getConnection(url,"user1","password"); } • catch (Exception e) • { System.err.println("Got an exception! "); System.err.println(e.getMessage()); } • } • }

  9. JDBC URL • The syntax of the DriverManager.getConnection() method is: • DriverManager.getConnection(String url, String username, String password); • The username and password are the normal names you use to log into your database. • The URL you use will again vary with the database you use. • In above example shown, it is establishing a connection to a database named contact_mgr.

  10. MySQL JDBC connection string(JDBC URL) • Here's a sample MySQL JDBC connection string and JDBC driver string, taken from a Java properties file: • db_url = jdbc:mysql://HOST/DATABASE • db_driver = com.mysql.jdbc.Driver • db_username = USERNAME • db_password = PASSWORD

  11. MySQL JDBC connection string(JDBC URL) • Here's a similar MySQL JDBC connection string (URL) and driver inside of a little Java source code: • try { • Class.forName("com.mysql.jdbc.Driver").newInstance(); • String url = "jdbc:mysql://HOST/DATABASE"; • conn = DriverManager.getConnection(url, "username", "password"); doTests(); • conn.close(); } • catch (ClassNotFoundException ex) {System.err.println(ex.getMessage()); • } • catch (IllegalAccessException ex) {System.err.println(ex.getMessage());} catch (InstantiationException ex) {System.err.println(ex.getMessage());} catch (SQLException ex) {System.err.println(ex.getMessage());}

  12. Seven Basic Steps inUsing JDBC • Load the driver • Define the Connection URL • Establish the Connection • Create a Statement object • Execute a query • Process the results • Close the connection

  13. JDBC: Details of Process JDBC: • Load the driver • try { • Class.forName("connect.microsoft.MicrosoftDriver"); • Class.forName("oracle.jdbc.driver.OracleDriver"); • } catch { ClassNotFoundException cnfe) { • System.out.println("Error loading driver: " cnfe); • } • Define the Connection URL • String host = "dbhost.yourcompany.com"; • String dbName = "someName"; • int port = 1234; • String oracleURL = "jdbc:oracle:thin:@" + host + ":" + port + ":" + dbName; • String sybaseURL = "jdbc:sybase:Tds:" + host +":" + port + ":" + • "?SERVICENAME=" + dbName;

  14. JDBC: Details of Process JDBC: • Establish the Connection • String username = "jay_debesee"; • String password = "secret"; • Connection connection = DriverManager.getConnection(oracleURL, username,password); • Optionally, look up information about the database • DatabaseMetaData = • connection.getMetaData(); • String productName = • dbMetaData.getDatabaseProductName(); • System.out.println("Database: " + productName); • String productVersion = • dbMetaData.getDatabaseProductVersion(); • System.out.println("Version: " + productVersion);

  15. JDBC: Details of Process JDBC: • Create a Statement • Statement statement = • connection.createStatement(); • • Execute a Query • String query = • "SELECT col1, col2, col3 FROM sometable"; • ResultSet resultSet = • statement.executeQuery(query); • – To modify the database, use executeUpdate, • supplying a string that uses UPDATE, INSERT, or • DELETE • – Use setQueryTimeout to specify a maximum delay • to wait for results

  16. JDBC: Details of Process JDBC: • Process the Result • while(resultSet.next()) { • System.out.println(resultSet.getString(1) + " " + • resultSet.getString(2) + " " + • resultSet.getString(3)); • } • – First column has index 1, not 0 • – ResultSet provides various getXxx methods that • take a colu index or column name and returns the data • – You can also access result meta data (column names, • etc.)

  17. JDBC connections • MySQL JDBC driver • MySQL Connector/J is the official MySQL JDBC driver. Here's a link to their JDBC driver:  • http://dev.mysql.com/downloads/connector/j/5.1.html • Once you have the correct JDBC driver for your database, install it according to the instructions that came with it. • Installation instructions will vary somewhat for each vendor.

  18. Installing Connector/J MySQL JDBC Driver • Among the extracted files will be a JAR file with a name like 'mysql-connector-java-5.1.19-bin.jar'. • Copy this file to your %JAVA_HOME%/jre/lib/ext folder • You should also add the complete path to this JAR file in your CLASSPATH environment variable. • In case if you don't know how to do that, go to Start -> Settings -> Control Panel -> System -> Advanced (tab) -> Environment Variables • If you've correctly added the path to Connector/J JDBC driver's JAR file in your CLASSPATH environment variable then you are done with installing MySQL Connector/J JDBC driver.

  19. How to test if Connector/J has been installed and configured correctly? • We will now create a simple Java program which will try to connect to our MySQL database server using Connector/J JDBC driver. • Create a new JdbcExample.java file and copy/paste the code from web site (JdbcExample). • All this code does is to connect to our MySQL database server (if it is running) using the account 'root', password '' and database 'test'. • Once connected it prints a success message on the console.

  20. How to test if Connector/J has been installed and configured correctly? • Compile this class but before running it make sure that MySQL database server is running on your system. • If not already running, you can start it by issueing a command like "mysqld-nt --console" or if you've installed it as a service then "net start mysql". • Running this program gave following success message:

  21. Description of code: • Connection:This is an interface in  java.sql package that specifies connection with specific database like: MySQL,Ms-Access, Oracle etc and java files. • The SQL statements are executed within the context of the Connection interface. • Class.forName(String driver) • DriverManager:It is a class of java.sqlpackage that controls a set of JDBC drivers. • Each driver has to be register with this class. • getConnection(String url, String userName, String password):

  22. Description of code: • This method establishes a connection to specified database url. • It takes three string types of arguments like:  • url: - Database url where stored or created your databaseuserName: - User name of MySQLpassword: -Password of MySQL  • con.close():This method is used for disconnecting the connection. It frees all the resources occupied by the database. • Show example: MySqlConnect

  23. Creating a Database in MySQL • A database is a  large collection of data or information stored in computer in an arranged way. • It helps us for accessing, managing and updating the data easily. • In this example we are going to create a  database by MySQL and with the help of some java methodsand SQL statement. • A RDBMS (Relational Database Management System) is a type of DBMS (Database Management System) which stores the data in the form of tables.

  24. Description of program: • This program: • Establishes the connection with MySQL database • Takes a database name as its input in the database query • After that itcreate a new database • Show a message "1 row(s) affected" otherwise, it displays "SQL statement is not executed!". • Description of code: • CREATE  DATABASE  db_name;   • Download ‘CreateDatabase’

  25. Creating a Database Table • Statement:It is a interface. Statement object executes the SQL statement and returns the result it produces. • createStatement():It is a method of Connection interface, whichreturns Statement object. • CREATE TABLE table_name(field_name):An appropriate code used for creating a table with given field name. • executeUpdate(String table):This method also executes SQL statement that may be INSERT, UPDATE OR DELETE statement are used in the code. It takes string types parameters for SQL statement. It returns int.  • Download : CreateTable

  26. Inserting values in MySQL database table • Description of program: • First of all this program establishes the connection with MySQL database through the JDBC driver, after only that we will be able to insert the values in specific table with the help of some APIs and methods. • If any values get inserted in the table then shows a message "1 row affected" but if any problems comes while inserting the data  in the table then it will displays the message "SQL statement is not executed!". • Description of code: • INSERT table_name VALUES(field_values): • Download ‘InsertValues’

  27. Description of Database Table • Description of program: • While making this program firstly we should establish the connection with MySQL database through theJDBC driver. • When the connection has been established, pass the table name in the database query and use some java methods to get the detail description of table. • When the program will gets execute then it will show field name,  type and null of the database table. • If any field have null value then shows "YES" and if not null then shows "NO". • If any problem is created at the time of query, it will show "SQL statement is not executed!". • Download ‘DescriptionTable’

  28. Count Rows from a Database Table • Description of program: • For this program to work firstly we need to establish the connection with MySQL database by the help of JDBC driver. • When the connection has been established we need to pass a table name from the given database in the query and the rows will be counted and the result will be displayed. • If any exception is thrown then it will show "SQL statement is not executed!" • Description of code: • SELECT COUNT(*) FROM table_name; • Download ‘CountRows’

  29. NetBeans • The NetBeans IDE is open source and is written in the Java programming language. • It provides the services common to creating desktop applications -- such as window and menu management, settings storage -- and is also the first IDE to fully support JDK 5.0 features. • The NetBeans platform and IDE are free for commercial and non-commercial use, and they are supported by • Sun Microsystems. • It can be downloaded from http://www.netbeans.org/

  30. Creating a Simple Web Application Using a MySQL Database • http://netbeans.org/kb/docs/web/mysql-webapp.html • This document describes how to create a simple web application that connects to a MySQL database server. • It also covers some basic ideas and technologies in web development, such as JavaServerPages (JSP), JavaServer Pages Standard Tag Library (JSTL), the Java Database Connectivity (JDBC) API, and two-tier, client-server architecture. • This tutorial is designed for beginners who have a basic understanding of web development and are looking to apply their knowledge using a MySQL database.

More Related