Import JDBC Packages
The Import statements tell the Java compiler where to find the classes you reference in your code and are placed at the very beginning of your source code.
To use the standard JDBC package, which allows you to select, insert, update, and delete data in SQL tables, add the following imports to your source code −
import java.sql.* ; // for standard JDBC programs
import java.math.* ; // for BigDecimal and BigInteger support</span>
Register JDBC Driver
The most common approach to register a driver is to use Java's Class.forName() method, to dynamically load the driver's class file into memory, which automatically registers it. This method is preferable because it allows you to make the driver registration configurable and portable.
The following example uses Class.forName( ) to register the Oracle driver −
Approach I - Class.forName()
<span style="font-size:14px;">try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}</span>
You can use getInstance() method to work around noncompliant JVMs, but then you'll have to code for two extra Exceptions as follows −
try {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
catch(IllegalAccessException ex) {
System.out.println("Error: access problem while loading!");
System.exit(2);
catch(InstantiationException ex) {
System.out.println("Error: unable to instantiate driver!");
Approach II - DriverManager.registerDriver()
try {
Driver myDriver = new oracle.jdbc.driver.OracleDriver();
DriverManager.registerDriver( myDriver );
}
catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}
Database URL Formulation
This is to create a properly formatted address that points to the database to which you wish to connect.
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 |
Create Connection Object
Finally, code a call to the DriverManager object's getConnection( ) method to establish actual database connection.
-
getConnection(String url)
-
getConnection(String url, Properties prop)
-
getConnection(String url, String user, String password)
Using a Database URL with a username and password
String URL = "jdbc:oracle:thin:@amrood:1521:EMP";
String USER = "username";
String PASS = "password"
Connection conn = DriverManager.getConnection(URL, USER, PASS);
Using Only a Database URL
String URL = "jdbc:oracle:thin:username/password@amrood:1521:EMP";
Connection conn = DriverManager.getConnection(URL);
Using a Database URL and a Properties Object
import java.util.*;
String URL = "jdbc:oracle:thin:@amrood:1521:EMP";
Properties info = new Properties( );
info.put( "user", "username" );
info.put( "password", "password" );
Connection conn = DriverManager.getConnection(URL, info);
Creating a Statement
Statement stmt = con.createStatement() ;
PreparedStatement pstmt = con.prepareStatement(sql) ;
CallableStatement cstmt =
con.prepareCall("{CALL demoSp(? , ?)}") ;
Executing SQL
ResultSet rs = stmt.executeQuery("SELECT * FROM ...") ;
int rows = stmt.executeUpdate("INSERT INTO ...") ;
boolean flag = stmt.execute(String sql) ;
</pre></div><h2 style="color:rgb(18,18,20); letter-spacing:-1px; margin:0.2em 0.2em 0.2em 0px; font-size:1.7em; line-height:1.5em; padding:0px; position:relative; left:0px; font-family:'Open Sans',Arial,sans-serif">Closing JDBC Connections</h2><pre name="code" class="java">conn.close();
<ol style="margin: 0px; padding: 0px; orphans: 2; widows: 2; border-width: 0px;"><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><span color="#7f0055" style="margin: 0px; padding: 0px;">if</span></span></span><span style="margin: 0px; padding: 0px;">(rs != </span><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><span color="#7f0055" style="margin: 0px; padding: 0px;">null</span></span></span><span style="margin: 0px; padding: 0px;">){ </span><span style="margin: 0px; padding: 0px;"><span color="#008200" style="margin: 0px; padding: 0px;">// 关闭记录集<span style="margin: 0px; padding: 0px;"> </span></span></span><span style="margin: 0px; padding: 0px;"> </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> </span><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><span color="#7f0055" style="margin: 0px; padding: 0px;">try</span></span></span><span style="margin: 0px; padding: 0px;">{ </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> rs.close() ; </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> }</span><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><span color="#7f0055" style="margin: 0px; padding: 0px;">catch</span></span></span><span style="margin: 0px; padding: 0px;">(SQLException e){ </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> e.printStackTrace() ; </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> } </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> } </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> </span><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><span color="#7f0055" style="margin: 0px; padding: 0px;">if</span></span></span><span style="margin: 0px; padding: 0px;">(stmt != </span><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><span color="#7f0055" style="margin: 0px; padding: 0px;">null</span></span></span><span style="margin: 0px; padding: 0px;">){ </span><span style="margin: 0px; padding: 0px;"><span color="#008200" style="margin: 0px; padding: 0px;">// 关闭声明<span style="margin: 0px; padding: 0px;"> </span></span></span><span style="margin: 0px; padding: 0px;"> </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> </span><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><span color="#7f0055" style="margin: 0px; padding: 0px;">try</span></span></span><span style="margin: 0px; padding: 0px;">{ </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> stmt.close() ; </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> }</span><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><span color="#7f0055" style="margin: 0px; padding: 0px;">catch</span></span></span><span style="margin: 0px; padding: 0px;">(SQLException e){ </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> e.printStackTrace() ; </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> } </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> } </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> </span><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><span color="#7f0055" style="margin: 0px; padding: 0px;">if</span></span></span><span style="margin: 0px; padding: 0px;">(conn != </span><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><span color="#7f0055" style="margin: 0px; padding: 0px;">null</span></span></span><span style="margin: 0px; padding: 0px;">){ </span><span style="margin: 0px; padding: 0px;"><span color="#008200" style="margin: 0px; padding: 0px;">// 关闭连接对象<span style="margin: 0px; padding: 0px;"> </span></span></span><span style="margin: 0px; padding: 0px;"> </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> </span><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><span color="#7f0055" style="margin: 0px; padding: 0px;">try</span></span></span><span style="margin: 0px; padding: 0px;">{ </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> conn.close() ; </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> }</span><span style="margin: 0px; padding: 0px;"><span style="margin: 0px; padding: 0px;"><span color="#7f0055" style="margin: 0px; padding: 0px;">catch</span></span></span><span style="margin: 0px; padding: 0px;">(SQLException e){ </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> e.printStackTrace() ; </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> } </span></li><li style="margin: 0px; padding: 0px; list-style: none; border-width: 0px;"><span style="margin: 0px; padding: 0px;"> } </span></li></ol>