JDBC Tutorial
Before Going further , you need to examine 2 packages for database communication. i.e
These 2 packages has common set of classes and interfaces , to connect to any relational database. These(classes and interfaces) are specifications only. Actual implementation done at JDBC driver jar side. Applications should use vendor specific JDBC driver.
Java Clients Can be SWING Application or AWT application, Android App or webAPP.
java.sql package is core API for JDBC, mainly used in Desktop Applications/Stand alone applications
javax.sql package has classes for connection pool,rowsets etc., used in WebApplications or Server side applications
Jdbc driver should be registered with DriverManager class. Same Driver is responsible for database communication and processing SQL statements. Registering driver is essential for external running databases like Oracle,mySQL, DB2 etc., For in-memory databases Driver Registeration may be optional. If you are using older version of java java 6 below versions this step is mandatory, for other java releases this step is optional.
Registering Oracle JDBC Driverthere are 2 ways to register JDBC Driver
1. Class.forName("oracle.jdbc.driver.OracleDriver")
2. DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver())
JDBC Driver downloadable from oracle.com website
Connecting to Database requires JDBC URL, and username nd password. JDBC URL differs from one database to another database. Programmer has to check corresponding database vendor specific website for JDBC URL format:
for ex: Oracle has thin and thick JDBC Drivers. thin drivers are pure java drivers. Thick drivers depends on odbc(open database connectivity). Latest versions of java uses thin drivers.
oracle jdbc format: jdbc:oracle:thin:@localhost:1521:xe
jdbc:oracle is a protocol which commuincates with Oracle server.
thin tells, thin JDBC driver, i.e pure java implementation.
localhost is a machine name.
1521 is default port number for Oracle
here XE is the database instance name.
Connection Object has method called CreateStatment, which returns statement object. This Statement Object reponsible for sending SQL statements to Database Server. Database Server parses the Query, checks syntax and semantics of the QUERY and finally executes the SQL statement.
If there is any Syntax errors in the SQL Query java.sql.SQLSyntaxErrorException: will be fired.
Same statement object can be used to for DDL command and DML commands. for ex: Programmer can create a one Table and fetch data from another Table etc.,
Statement stmt = conn.createStatement();
Statement Object has method called executeQuery to execute DRL command. i.e SELECT statement. executeQuery executes SQL Query ,returns single resultset Object.
ResultSet object is a snap shot of the given SQL statement. It has rows and columns and Data. Each column has type. i.e Resultset Object has Data+metadata. And also Resultset object has cursor object, Intially it points to before the first row of the data. Using next method cursor moves to first row of the data.
Once cursor points to first row of the Data, programmer can fetch records. Each SQL type has equivalent Java class.
for ex: Integer datatype in Oracle, rs.getInt returns Integer value.
for Date class in Oracle equivalent class in Java is java.util.Date class rs.getDate() etc.,
Boolean ret=stmt.execute("create table table_name(col1 type, col2 type ...)");
Execute SQL statementResultSet rs=stmt.executeQuery("select * from Table_name");
Once Database operations are done, Programmer has to close the resources.
using stmt.close()
conn.close()
import java.sql.*;
public class JDBCDemo{
public static void main(String[] args)
{
String URL = "jdbc:oracle:thin:@localhost:1521/XE";
String user = "scott";
String passwd = "tiger";
String sql = " SELECT * FROM EMP " ;
//STEP 1
try
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//STEP 2
try( Connection conn = DriverManager.getConnection( URL, user, passwd ))
{
//STEP 3
try( Statement stmt = conn.createStatement() )
{
//STEP 4
ResultSet rs = stmt.executeQuery( sql );
while ( rs.next() )
{
System.out.println( rs.getInt(1) + " "+ rs.getString(2) +" "+
rs.getString(3) +" "+ rs.getInt(4) +" "+ rs.getDate(5) +" "+
rs.getFloat(6) +" "+rs.getFloat(7)+ " "+rs.getInt(8) );
}
}
}
}
catch(SQLException ex)
{
System.out.println(ex);
}
}
}
javac to compile java program, java to execute compiled program. Here you need to set classpath for jdbc driver. In Linux -cp .~/jdbctutorial/ojdbc8.jar , assuming ojdbc8.jar file stored in home directory/jdbctutorial directory. Both compile time and execution time jar file must be specified in classpath. Because Driver class located in ojdbc8.jar file. i.e DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
$javac -cp .:~/jdbctutorial/ojdbc8.jar JDBCDemo.java $java -cp .:~/jdbctutorial/ojdbc8.jar JDBCDemo
$javac JDBCDemo.java $java .:~/jdbctutorial/ojdbc8.jar -Ddriver=oracle.jdbc.driver.OracleDriver -Duser=scott -Dpassword=tiger JDBCDemo
ADS