1.6.4 Executing Statements using Statement Objects
|
Method
|
Statements Executed
|
|
ExecuteQuery
|
SELECT
|
|
ExecuteUpdate
|
INSERT, UPDATE, DELETE
CREATE TABLE, DROP TABLE.
|
Example:
Statement artifactStatement = myConnection.createStatement();
ResultSet artifactResultSet = artifactStatement.executeQuery("select artifactname from artifacts ");
Note:
· It is recommended as good programming practice that all statement objects be closed explicitly when they are no longer needed. This frees DBMS resources immediately and helps avoid potential memory problems. It is best to create a separate Statement object for each SQL statement to be processed.
1.6.5 Executing Prepared Statements
The PreparedStatement Object can be created using the following piece of code:
PreparedStatement artifactPreparedStatement = myConnection.prepareStatement(<SelectStatement>) ;
where myConnection is the Connection object.
Example:
String p_SubmittedBy ;
PreparedStatement artifactPreparedStatement = myConnection .prepareStatement("
Update Artifacts Set SubmittedBy = ? where artifactid = '6871' " ) ;
artifactPreparedStatement.setDate(1,p_SubmittedBy);
artifactPreparedStatement.executeUpdate() ;
In the above example, the value is passed to the PreparedStatement using the setXXX() method. The appropriate method should be used depending upon the data type of the column in the database. Using getInt() while fetching a String from the database will result in an error. It is always a good practice to check with the data types of the column in the database and use the appropriate method.
1.6.6 ResultSet
ResultSet contains all the rows which satisfied the conditions in an SQL statement, and it provides access to the data in these rows through a set of “get” methods that allow access to the various columns of the current row. The “ResultSet.next” method is used to move to the next row of the ResultSet, making the next row become the current row.
Example:
String l_artifactname ;
Statement myStatement = myConnection.createStatement() ;
ResultSet artifactResultSet = myStatement.executeQuery(" select artifactid, artifactname from artifacts ");
while(artifactResultSet.next()) {
l_aritifactid = artifactResultSet.getInt(1) ;
l_artifactname = artifactResultSet.getString(2) ;
}
In the above example, the value is fetched from the resultset using the getXXX() method. The appropriate method should be used depending upon the data type of the column in the database.
The columns should be fetched in the correct order from the resultset using the getXXX() method. In the above example first artifactid column has to be fetched and only then artifactname column should be fetched. The order of fetching is immaterial when working with Oracle thin driver but is very important when working with Microsoft SQLServer driver. In order to have the same code running for both the drivers it is best to fetch the columns in the same order as given in the select statement.
Setting and getting values for LONG datatype of Oracle database can be done by setString() and getString() functions. But the same does not work fine when working with TEXT datatype of SQLServer database. In order to have the same code working for both the Oracle driver and the SQLServer driver the following logic can be used when fetching values for LONG or TEXT datatype:
Replace Statement.setString (2, str) call with following lines of code:
StringBufferInputStream sbi = new StringBufferInputStream(str); Statement.setAsciiStream(2,sbi, str.length());
Replace String str = Statement.getString("name") with following lines of code:
InputStream in = Statement.getAsciiStream("name");
BufferedReader dis = new BufferedReader (in);
While((temp = dis.readLine()) != null) str = str + temp;
Note:
· It is recommended that all ResultSet objects be closed explicitly when they are no longer needed. This is all the more important when they are accessed in loops. If this is not done then the error “Too many Cursors Open” is encountered. It is best to create a separate ResultSet object for each SQL statement to be processed.
· It is recommended to have only one resultset open at any given time. It is very common to have the need for multiple resultsets open while developing applications. In such cases we would be forced to use one resultset inside the while of the first resultset when both the resultsets are open. This works fine with the Oracle thin driver but does not work when working with the Microsoft SQL Server driver. The solution to this is to use a join and use only one resultset. The other alternative is to first store the results of the first resultset in array or vector and close the resultset. The values stored in the array or vector can then be used in the second resultset.
1.6.7 Commit
To make permanent all the changes made to the database the Commit Statement has to be executed. This can be done using the following line of code
myConnection.commit() ;
where myConnection is the connection object.
1.6.8 Close
It is always a good practice to close a Connection object as and when it is used and is no longer needed. The recommended programming style is to explicitly close any Connection objects when they are no longer need, this releases DBMS resources as soon as possible. This can be done using the following line of code
myConnection.close() ;
where myConnection is the connection object.
1.6.9 Querying Database Specific Information
JDBC provides the DatabaseMetaData interface that provides specific information about a database. Information specific to a database like the data types supported, database version and so on can be found using the MetaData interface.
This has been found very useful while coding scripts for creating tables. As discussed in section 1.4 there are quite a few data types that are specific to the database like Long Varchar in Oracle and Text in Microsoft SQL Server. We can use these data types in the application and use the JDBC equivalent for these data types while fetching and setting values in the database. However we will end up having separate scripts for creating tables for each of the database. It is in such cases that the DatabaseMetaData interface is very handy and can be used to query the database and the tables can be created based on the return values.
1.6.10 Guidelines for naming Tables & Columns
· Give meaningful names to Tables and it should closely relate to the data being stored in the table.
· It is a good practice to give full name to table like Employee, Invoice, Department instead of using short names like Emp, Inv, Dept and so on.
· Do not give spaces in between words if the table name is made up of two words like InvoiceDetails, EmployeeDetails and so on.
· It is better to avoid symbols like “_” in the table names like Invoice_Details, Employee_Details and so on. This is also the case with column names, it is better to avoid names like Inv_No, Date_Of_Birth and so on. It is just that remembering them becomes difficult.
· The names of VIEWS should start with V_<Name>. Views are based on joins on two or more tables. Names should actually reflect on the tables used in the join condition. For example, if there is VIEW based on the EMPLOYEE table then the name should be named as V_Employee.
· Incase you are using some temporary tables then their names should start with T_<name>.
· Only use the underscore and alphanumerics in table and column names. Start all table and column names with an alphabetic letter.
1.6.11 Debugging and Tracing
· It is best to code each SELECT statement in a separate “Try” block. Code appropriate error message in the corresponding “Catch” block. You could always put all the SELECT statements in one single “Try” block but the disadvantage with this is that in case of errors it will be difficult to point out which SELECT statement is causing the error condition.
· It is also a good practice to trap the database specific errors along with your own error message. This helps in pointing to the exact reason for the error. This can be done by printing <Errorname>.toString() in the exception.
Example:
try{
String l_artifactname ;
Statement myStatement = myConnection.createStatement() ;
ResultSet artifactResultSet = myStatement.executeQuery("select artifactname from artifacts ");
while(artifactResultSet.next()) {
l_artifactname = artifactResultSet.getString(1) ;
}
}
catch (Exception myError)
{
System.out.println(myError.toString());
System.out.println("Error connecting to database. Please inform the administrator.") ;
return ;
}
· Errors while processing SQL statements generally will be due to very simple mistakes like a missing comma between column names, no space between column names and so on. In such cases it is best to copy the statement and try the same statement in a tool like SQL*PLUS (Oracle) or ISQL (Microsoft SQL Server). It is easy to trap such mistakes while working with these tools.
1.6.12 Do’s and Don’ts while using JDBC
· Execute each SQL statement in a separate try block and give appropriate error messages in the exceptions. These helps you point to the exact block incase of errors, especially if you are using nested loops.
· For SQL statements that are executed again and again, use PreparedStatements as they are precompiled and executed faster.
· For processing each SQL statement use a separate Statement object and ResultSet.
· Close all ResultSets and Statements after use. If you don’t do this you will end up getting errors like “Too many cursors open”.
· Make sure that the type you try to retrieve matches the type in the database, or else you may get a poorly documented exception like ‘ISO 1920’ which gives no clue to the error. This commonly happens when a getInt() is used instead of a getString()
· Similar care should be taken whilst setting parameters in a PreparedStatement.
· If executing a Statement in a loop, make it a prepared statement and initialize it before the loop. Close it once the loop is over.
· All string values should be enclosed in single quotes. If you use double quotes or no quote at all, the database will assume it’s a column name and not a value and will throw an exception.
APPENDIX A: JDBC Types Mapped to Database-specific SQL Types
|
JDBC Type Name
|
Oracle 7.2
|
Sybase 11.0
|
Informix 7.12
|
IBM DB2 2.1
|
Microsoft SQL Server 6.5
|
Microsoft Access 7.0
|
Sybase SQL Anywhere 5.5
|
|
BIT
|
|
BIT
|
|
|
BIT
|
BIT
|
BIT
|
|
TINYINT
|
|
TINYINT
|
|
|
TINYINT
|
BYTE
|
TINYINT
|
|
SMALLINT
|
SMALLINT
|
SMALLINT
|
SMALLINT
|
SMALLINT
|
SMALLINT
|
SMALLINT
|
SMALLINT
|
|
INTEGER
|
INTEGER
|
INTEGER
|
INTEGER
|
INTEGER
|
INTEGER
|
INTEGER
|
INTEGER
|
|
BIGINT
|
|
|
|
|
|
|
|
|
REAL
|
REAL
|
REAL
|
REAL
|
|
REAL
|
REAL
|
REAL
|
|
FLOAT
|
FLOAT
|
FLOAT
|
FLOAT
|
FLOAT
|
FLOAT
|
FLOAT
|
FLOAT
|
|
DOUBLE
|
DOUBLE PRECISION
|
DOUBLE PRECISION
|
DOUBLE PRECISION
|
DOUBLE PRECISION
|
DOUBLE PRECISION
|
DOUBLE
|
DOUBLE PRECISION
|
|
NUMBERIC(p,s)
|
NUMBERIC(p,s)
|
NUMBERIC(p,s)
|
NUMBERIC(p,s)
|
NUMBERIC(p,s)
|
NUMBERIC(p,s)
|
|
NUMBERIC(p,s)
|
|
DECIMAL(p,s)
|
DECIMAL(p,s)
|
DECIMAL(p,s)
|
DECIMAL(p,s)
|
DECIMAL(p,s)
|
DECIMAL(p,s)
|
|
DECIMAL(p,s)
|
|
CHAR(n)
|
CHAR(n)
n<=255
|
CHAR(n)
n<=255
|
CHAR(n)
n<=32,767
|
CHAR(n)
n<=254
|
CHAR(n)
n<=255
|
CHAR(n)
n<=255
|
CHAR(n)
n<=32,767
|
|
VARCHAR(n)
|
VARCHAR(n)
n<=2000
|
VARCHAR(n) n<=255
|
VARCHAR(n) n<=255
|
VARCHAR(n)
n<4000
|
VARCHAR(n)
n<=255
|
VARCHAR(n)
n<=255
|
VARCHAR(n)
n<=32,767
|
|
LONGVARCHAR
|
LONG VARCHAR
Limit is 2 Gigabytes
|
TEXT
Limit is 2 Gigabytes
|
TEXT
Limit is 2 Gigabytes
|
LONG VARCHAR
Limit is 32,700 bytes
|
TEXT
Limit is 2 Gigabytes
|
LONGTEXT
Limit is 1.2 Gigabytes
|
LONG VARCHAR
Limit is 2 Gigabytes
|
|
BINARY(n)
|
|
BINARY(n)
n<=255
|
|
CHAR(n) FOR BIT DATA
n<=254
|
BINARY(n)
n<=255
|
BINARY(n)
n<=255
|
BINARY
n<=32,767
|
|
VARBINARY
|
RAW(n)
n<=255
|
VARBINARY(n)
n<=255
|
|
VARCHAR(n) FOR BIT DATA
n<=4000
|
VARBINARY(n)
n<=255
|
VARBINARY(n)
n<=255
|
|
|
LONGVARBINARY
|
LONG RAW
Limit is 2 Gigabytes
|
IMAGE
Limit is 2 Gigabytes
|
BYTE
Limit is 2 Gigabytes
|
LONG VARCHAR FOR BIT DATA
Limit is 32,700 bytes
|
IMAGE
Limit is 2 Gigabytes
|
LONGBINARY
Limit is 1.2 Gigabytes
|
IMAGE
Limit is 2 Gigabytes
|
|
DATE
|
DATE
|
DATETIME
|
DATE
|
DATE
|
|
DATE
|
DATE
|
|
TIME
|
DATE
|
DATETIME
|
|
TIME
|
|
TIME
|
TIME
|
|
TIMESTAMP
|
|
|
|
TIMESTAMP
|
DATETIME
|
|
TIMESTAMP
|
Appendix B: DBMS Limitations
You also need to be aware of the limitations of each DBMS. Take particular note of the maximum length of column and table names, because if you use a 34 character table name on one DBMS, the name may be too long for the other DBMS. Along with that, try very hard to avoid any table or column names that could be a keyword on any DBMS.
|
Limitations
|
Recommendation
|
Access 97
|
Microsoft SQL Server 6.5
|
Oracle 8.04
|
DB2 7.2
|
Informix 7.2
|
|
Column name lengths
|
Maximum of 14
|
64
|
30
|
?
|
18
|
18
|
|
Number of fields in a table
|
|
255
|
250 (1024 in 7.0)
|
1000
|
500
|
|
|
Number of characters in a record (excluding Memo and OLE Object fields)
|
|
2000
|
1962 (8060 in SQL 7.0)
|
|
4005
|
|
|
Table size
|
|
1 gigabyte
|
1 terabyte (1,000,000 TB 7)
|
|
64 * 999 gigabytes
|
|
|
Number of characters in a Text field
|
|
255
|
255 (8000 in SQL 7.0)
|
char = 2k, varchar = 4k
|
char=254 varchar=4k longvarchar=32k
|
255
|
|
Number of characters in a Memo field
|
|
65,535 or 1 gigabyte
|
2 gigabytes
|
4 gigabytes
|
2 gigabytes
|
2 gigabytes
|
|
Number of indexes in a table
|
|
32
|
250
|
|
32767
|
|
|
Number of fields in an index
|
|
10
|
16
|
|
16
|
|
|
Longest SQL Stmt
|
2k - use insert followed by update for more
|
64k
|
128k
|
64k
|
32767
|
|
|
Most columns in select list
|
|
255
|
4096
|
250?
|
500
|
|
