JDBC Beginner Learning

Introductionwhat ' s JDBC

JDBC stands for JAva Database Connectivity, which are a standard Java API for database- Independent connectivity between the Java programming language and a wide range of databases.

The main uses of the JDBC library (includes APIs) include

• Making a connection to a database. Connecting to a database

• Creating SQL or MySQL statements. Create SQL or MySQL statements

• Executing SQL or MySQL queries in the database. Execute SQL or MySQL query

• viewing & Modifying the resulting records. View or modify Results

JDBC Schema

JDBC supports two-layer or three-layer processing logic. But generally, including two-storey structure

• JDBC API: This provides the APPLICATION-TO-JDBC Manager connection.

• JDBC Driver API: This supports the JDBC manager-to-driver Connection.

The JDBC drivers implements the interfaces defined by the JDBC APIs.

JDBC Connection

There are typically four simple steps to connecting JDBC

• Import JDBC Packages: ADD Import statements to your Java program to import required classes in your Java code.

• Register JDBC Driver: This step causes the JVM to load the desired driver implementation into memory so it can fulfill your JDBC requests.

• Database URL Formulation: This is to create a properly formatted address, points to the database to which you wish to connect.

• Create Connection Object: Finally, code a call to the DriverManagerobject ' s getconnection () method to establish actual database C Onnection.

Registering JDBC Driver

There are two ways to register a JDBC driver:

Approach 1:class.forname () automatically loads the driver related Class into memory

Try {   class.forname ("Oracle.jdbc.driver.OracleDriver");} Catch (ClassNotFoundException ex) {   System.out.println ("error:unable to load driver class!" );   System.exit (1);}

View Code

Approach 2:drivermanager.registerdriver () If you are using the NON-JDK compliant JVM

Try {   new  oracle.jdbc.driver.OracleDriver ();   Drivermanager.registerdriver (mydriver);} Catch (ClassNotFoundException ex) {   System.out.println ("error:unable to load driver class!" );   System.exit (1);}

View CodeDatabase URL Formulation

After loading the driver, you can establish a connection with drivermanager.getconnection ().

Here is the mapping table for the common database and connection URLs

RDBMS	JDBC driver name	URL Format
Mysql	Com.mysql.jdbc.Driver	jdbc:mysql://Hostname/databasename
ORACLE	Oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@Hostname:port number:databasename
DB2	COM.ibm.db2.jdbc.net.DB2Driver	jdbc:db2:hostname:port number/databasename
Sybase	Com.sybase.jdbc.SybDriver	jdbc:sybase:Tds:hostname:port number/databasename

JDBC Statement,preparedstatement & CallableStatement

Depending on the needs, different statement interfaces can be selected.

Use

Interfaces	Recommended
Statement	Use the general-purpose access to your database. Useful when is using static SQL statements at runtime. The Statement interface cannot accept parameters.
PreparedStatement	Use if you plan to use the SQL statements many times. The PreparedStatement interface accepts input parameters at runtime.
CallableStatement	Use if you want to access the database stored procedures. The CallableStatement interface can also accept runtime input parameters.

Statement Creating statement

NULL ; Try {   = conn.createstatement ();   . . .} Catch (SQLException e) {   ...} finally  {   . . .}

View CodeExecutive statement

After you create the statement object, you can use it to execute the SQL statement

• Boolean execute (String SQL): Returns A Boolean value of True if a ResultSet object can be retrieved; Otherwise, it returns false. Use the This method to execute SQL DDL statements or if you need to use truly dynamic SQL.

• int executeupdate (String SQL): Returns The number of rows affected by the execution of the SQL statement. Use the This method to the Execute SQL statements for which your expect to get a number of rows Affected-for example, an INSERT, UPDATE, or DELETE statement.

• ResultSet executeQuery (String SQL): Returns a ResultSet object. The use of this method is expect to get a result set, as you would with a SELECT statement.

Close statement

If close connection saves database resources, close statement can ensure a reasonable recovery of resources.

NULL ; Try {   = conn.createstatement ();   . . .} Catch (SQLException e) {   ...} finally {   stmt.close ();}

View CodeJDBC Batch Processing

Batch processing allows multiple SQL statements to be executed at once. Because JDBC drivers does not require this functionality to be implemented, use databasemetadata.supportsbatchupdates () to detect whether the target database supports batching before using it.

Batching of Statement objects

• Create a Statement object using either createstatement () methods.

• Set Auto-commit to False using Setautocommit ().

• Add as many as SQL statements you to batch using Addbatch ()method on created statement object.

• Execute all of the SQL statements using ExecuteBatch () method on created statement object.

• Finally, commit all the changes using commit () method.

//Create Statement ObjectStatement stmt =conn.createstatement ();//Set Auto-commit to FalseConn.setautocommit (false);//Create SQL StatementString SQL = "INSERT into Employees (ID, First, last, age)" + "VALUES ($, ' Zia ', ' Ali ', 30)";//ADD above SQL statement in the batch.Stmt.addbatch (SQL);//Create One more SQL statementString SQL = "INSERT into Employees (ID, First, last, age)" + "VALUES (201, ' Raj ', ' Kumar ', 35)";//ADD above SQL statement in the batch.Stmt.addbatch (SQL);//Create One more SQL statementString SQL = "UPDATE Employees SET age =" + "WHERE id = 100";//ADD above SQL statement in the batch.Stmt.addbatch (SQL);//Create an int[] to hold returned valuesint[] Count =Stmt.executebatch ();//explicitly commit statements to apply changesConn.commit ();

View CodeBatching of PreparedStatement objects

//Create SQL StatementString SQL = "INSERT into Employees (ID, First, last, age)" + "VALUES (?,?,?,?)";//Create Preparestatement ObjectPreparedstatemen pstmt =conn.preparestatement (SQL);//Set Auto-commit to FalseConn.setautocommit (false);//Set the variablesPstmt.setint (1, 400);p stmt.setstring (2, "Pappu");p stmt.setstring (3, "Singh");p Stmt.setint (4, 33 );//ADD It to the batchPstmt.addbatch ();//Set the variablesPstmt.setint (1, 401);p stmt.setstring (2, "Pawan");p stmt.setstring (3, "Singh");p Stmt.setint (4, 31 );//ADD It to the batchPstmt.addbatch ();//Add more Batches....//Create an int[] to hold returned valuesint[] Count =Stmt.executebatch ();//explicitly commit statements to apply changesConn.commit ();

View CodeReferences

Http://www.tutorialspoint.com/jdbc/jdbc-where-clause.htm

Http://stackoverflow.com/questions/2839321/connect-java-to-a-mysql-database

JDBC Beginner Learning