Coding Standards and Guidelines for JDBC Application

We should follow some Coding standards and guidelines at the time of developing a JDBC application. A good programmer always follows Java coding standards while developing the application.

Any fool can write code that a computer can understand. Good programmers write code that humans can understand.

Martin Fowler

These coding standards are not separately given only for the JDBC applications, these are also the part of Java programming language. But for JDBC application we need not worry about all the coding standards, only a few coding standards and guidelines are enough for JDBC application.

Important JDBC Coding Standards

1) Always place java classes/interfaces/enums/annonation in a package. Syntax:-

org/com.<project-name>.<module-name>

It is recommended to use at least three words for the package name, the first word should be the top-level domain name, the second word should be the project name, the third word should be module-name. Example:- com.opnfx.shares

2) Avoid implicit package import and prefer explicit package import to improve the readability of the application.

// implicit package import
import java.sql.*; // not recommeded

// explicit package import
// recommeded
import java.sql.DriverManager; 

3) Write classes, interfaces, method, variable, enum and etc.. names with self description and having Hungarian notaions.

4) Declare all the local variables of the method at the top of the method definition. It improves the readability of the application.

// declaring the variables
Scanner scan = null;
Connection con = null;
Statement st = null;
String query = null;
ResultSet rs = null;

5) Write proper comment in application development as part of the logics.

6) To avoid NullPointerException from application check for not null before invoking methods or variables on the reference variable. If you follow this standard then you will never get NullPointerException.

if( scan != null ) {
    // read inputs
}

// create Statement
if( con != null ) 
st = con.createStatement();

// send and execute SQL query
// in database software
if( st != null ) 
rs = st.executeQuery(query);

// process the data
if( rs != null ) {
     // logic
}

7) Don’t declare the exception to be thrown by using throws keyword. Always catch and handle the exception by using try and catch block.

// not recommeded
public static void main(String[] args ) throws Exception {
}
// in main method always catch and handle exception
public static void main(String[] args ) {
   ...
   try {
      // logic which can cause exception
   } catch( ExceptionName variable ) {
      // exception handling statements
   }
}

8) Place multiple catch blocks followed by the try block to handle multiple Exceptions. Place the initial catch block to handle known Exception and place the last catch block to handle the unknown Exceptions.

The Class.forName() method may raises ClassNotFoundException, and other methods can cause SQLException. So first, handle these two known exceptions and then place the last catch block to handle unknown Exceptions.

try {
     // logic which can cause exception
} catch(ClassNotFoundException cnfe) {
     cnfe.printStackTrace();
} catch(SQLException se) {
     se.printStackTrace();
} catch(Exception e) {
     e.printStackTrace();
}

9) Close the JDBC object/stream in finally block because it executes irrespective of Exception that is raised in the try block and etc. You can also use try-with-resources, but to avoid NullPointerException, we need to check for null, and due to indentations code will looks heavy. So, we will use finally block. Learn more:- The best way to close the JDBC connection object

SQL queries

1) For Simple Statement, always gather simple input values from the end-user and convert them as required for the SQL queries inside the application. For example:- Don’t ask to give input in uppercase/lowercase or within brackets etc..

2) Typing input values directly in the application is called hard coding and it is bad practice. So, always gather the input from the end-user through softcoding.

3) It is recommended to write SQL query in uppercase letters to separate it from Java code.

4) While preparing the SQL query for the JDBC application, it is not recommended to place the star (*) symbol in the query. As the alternate write the column names in the query.

// not recommended
query = "SELECT * FROM EMP";

// recommended
query = "SELECT EMPNO, ENAME, JOB, SAL FROM EMP";

5) While developing the JDBC application, don’t place a semicolon at the end of the SQL query. In the previous example, the semicolon is for the Java statement not for the SQL query.

// wrong
query = "SELECT EMPNO, ENAME, JOB, SAL FROM EMP; ";

While using SQL Plus Tool, the semicolon is used for SQL Plus Tool to tell the statement has been completed, the semicolon is not used for the Oracle database.

6) While processing the ResultSet, it is not recommended to give the column indexes in the order they are available in the database table. Always give the database table column indexes in the order they are stored in the ResultSet object.

// not recommended
System.out.println( 
           rs.getInt(1) + " " 
         + rs.getString(2) + " " 
         + rs.getString(3) + " " 
         + rs.getFloat(4) );

// recommended
System.out.println( 
           rs.getInt(EMPNO) + " " 
         + rs.getString(ENAME) + " " 
         + rs.getString(JOB) + " " 
         + rs.getFloat(SAL) );

7) SQL keywords, table-name, column-names are not case-sensitive but data/records are case sensitive.

// no result
query = "SELECT JOB, SAL FROM EMP WHERE ENAME = 'scott';

The SCOTT is an employee name in the emp table of the Oracle database of Scott user i.e. it is a record/data of emp table in Oracle database. So, it is case-sensitive.

// By default, emp table records are in upper case
query = "SELECT JOB, SAL FROM EMP WHERE ENAME = 'SCOTT';

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Leave a Comment

Your email address will not be published. Required fields are marked *