Java Database access

Java Database Programming

Databases and JAVA

Various class libraries exist that allow Java programs to interact with relational databases.
The Java Database Connection (JDBC) is a common one.
When JDBC is used with an Access database, then a data source name must be created via the ODBC tool within the Windows control panel. Reference the notes on SQL for instructions on doing this.

JDBC uses several objects, all of which are part of the java.sql. library. (You'll need to import java.sql.*;)

Connection - this object provides a link (via a driver to a specific database)
Statement - this objectis used for executing SQL statements and obtaining the results produced by it.
PreparedStatement - this object allows you to create a template of an SQL statement, insert data into it, then execute it
ResultSet - this object holds the results of an SQL query operation.

Basic Steps

To use the JDBC in a program would do the following:

Open a connection to the database

Notes:

you must first load the driver using Class.forName, Class.forName could generate a ClassNotFoundException (you will need to catch it)

for MS access use Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // load the driver
for MS SQL Server use Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // load the driver
for Oracle use Class.forName("oracle.jdbc.driver.OracleDriver"); // load the driver
for MySql use Class.forName("com.mysql.jdbc.Driver"); // load the driver
For JDBC 4.0 you won't have to do this. The connection has automatic driver loading.

For all these except MS Access you will need to down load a copy of the driver. These drivers usually come in a JAR file that you can include in your project

To download the MS Sqlserver jar library, click here
For instructions on adding the driver to your netbeans project, click here

then create the connection using the DriverManager.getConnection method

getConnection could generate an SQLException, you should catch it

DriverManager.getConnection can be called in serveral ways, the two most useful are:

public static Connection getConnection(String url,String user,String password)

where the url has the form jdbc:subprotocol:subname

for MS access use: jdbc:odbc:dsn where dsn is your data source name you setup in the control panel
for MS Sql Server use jdbc:microsoft://hostname or ipaddress:port , ip addressess is the tcp ip address of the database server, the port is usually 1433
for Oracle use jdbc:oracle:thin@hostname:port#:oracleDBSID
for MySql use jdbc:mysql://hostname:port/databasename (port is usually 3306)

public static Connection getConnection(String url)

where the url has the form jdbc:subprotocol:subname

subname takes the form url:port;databaseName=name;user=userName;password=passWord;
if there is no username or password , just leave it off
Examples:

For MS Access with a username and password, "jdbc:odbc:samplebusiness;databaseName=;user=fred;password=bedrock;"
For MS Access without a username and password, "jdbc:odbc:samplebusiness;databaseName=;user=;password=;"
For MS SQLServer with a username and password, "jdbc:sqlserver://rubble.student.ad.fgcu.edu:1433;databaseName=rockdata;user=fred;password=bedrock;"
For My Sql with a user name and password (note no ; on the end, and a ? and & to separate fields)
" jdbc:mysql://localhost:3306/rockdata?user=fred&password=bedrock"

Examples:

private Connection con = null;

....

   try{
     Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // load the driver
     con = DriverManager.getConnection("jdbc:sqlserver://rubble.student.ad.fgcu.edu:1433;databaseName=sp12ism3232x;user=s12ISM3232;password=s12ISM3232;"); //
   }
   catch(SQLException sqle) {

      System.out.println("Sql Exception :"+sqle.getMessage());
   }
   catch(ClassNotFoundException e) {
    System.out.println("Class Not Found Exception :" + e.getMessage());
   }

Create a Statement or PreparedStatement object

Refer to a book on sql statements or the SQL notes for exact phrasing.

Statement Object - use this if the sql command has no variable data

Statement sqlStatement = con.createStatement(); // create a statement object

PreparedStatement - use this if the parameters of the sql statement might change from one call to the next

                                  PreparedStatement prep = con.prepareStatement("Select * from Products where name like ? and price <= ?");
                                  prep.setString(1,input1); // replace ? 1 with a value
                                  prep.setDouble(2,input2); // replace ? 2 with a value

Execute the Statement or PreparedStatement

For Statements, call executeQuery for sql Select, and executeUpdate for all other commands

ResultSet rs = sqlStatement.executeQuery("select * from products where price <= 100");
int n = sqlStatement.executeUpdate("Delete from products where name='Hammers'");

for PreparedStatements call, executeQuery for sql Select, and executeUpdate for all other commands

ResultSet rs = prep.executeQuery();
int n = prep.executeUpdate();

Process the results

Depending on the type of statement this could take on serveral forms.
For queries you will be looking at the ResultSet Object that is returned by executeQuery() method

You should view the ResultSet object as a temporary table in memory. Initially the table has an index pointing to a spot just before the first entry. Each time you call the next() method, it moves the pointer down to the next row. next() returns true if another row is there, false if not. Thus the first call to next actually positions you at the first record.
Once positioned at a row (remember, initially it is positioned just above the table, so you always have to call next at least once) you can access fields in the table using getString, getInt, getFloat, getLong, getDouble, etc. The number and order of the fields depends on how you set up your SQL select statement (not necessarily the same as your actual table in the database). These routines take either a column number (starting with 1 not 0) or a column name.
ResultSet also has methods like previous, first, last, beforeFirst, etc to allow you to move around in the result table. In order to use these methods you must specify a result set type of TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE when asking the connection to create the Statement object. The default is TYPE__FORWARD_ONLY, this allows you to only use the next() method toaccess rows in the resultset (therefore you can make just one pass over the result set). More on this later.
This example looks prints some data from each entry to the System window.

while(rs.next()) {

For other operations, executeUpdate will return an integer telling how many rows in the table were changed (added, deleted, modified).

Close the connection to the database

Notes:

you should close your Statement object when you are done with it and close the connection object when you are done with it. Closing the statement also gets rid of any associated ResultSet objects. therefore don't close them too soon!
Its best to release these resources so you don't tie up the database and piss off your database administrator

sqlStatement.close(); // release the statement resources

.....

con.close(); // close and release the database (do this before the program exits)

Note: you can add a finalize method for your class to close the connection to the database when the class is destroyed,e .g.

protected void finalize()
{
   try{
        con.close();
    }
    catch(Exception e)
{
   }
}

All the sql methods have the ability to throw an SQLException. I didn't show it in the examples above, but you should always use the try and catch around these methods.
In order to use the sql objects you will need to import the sql library. See example below.

SearchForm.html

<!DOCTYPE html>

<html>

    <head>

        <title></title>

        <meta
http-equiv="Content-Type" content="text/html; charset=UTF-8">

<SCRIPT
TYPE="text/javascript">

<!--

  function checkForm()

  {

    var valid =
true;

    var message =
"Errors: \n";



   
with(document.ClassSearch)

    {

    
if(title.value == "")

     {

      
valid = false;

      
message = message + "title field is blank\n";

      
title.style.borderColor = "red"; // red border

     }

     else

       
title.style.borderColor = "gray"; // gray border 

     

    }



    if(!valid)

     
alert(message);

    return valid;



  }



// -->

</SCRIPT>

    </head>

    <body>

        <div>Search
Form</div>

        <form name="ClassSearch"
method="get" action="SearchCourse.jsp"
onSubmit ="return checkForm();">

           
Enter part or all of the title : <input type="text" name="title"
size="30"><br/>

            
Enter part or all of the instructors name: <input type="text" name="instructor"
size="30"><br/>

           
<input type ="submit" value="search"><br/>

           
<input type ="reset" value="clear"><br />

        </form>

    </body>

</html>

SearchCourse.jsp



<%-- 

    Document   : SearchCourse

    Created on : Mar 13, 2012, 1:28:27 PM

    Author     : mpenderg

--%>



<%@page contentType="text/html" pageEncoding="UTF-8"%>

<%@page
import="java.sql.*"%>

<!DOCTYPE html>

<html>

    <head>

        <meta
http-equiv="Content-Type" content="text/html; charset=UTF-8">

        <title>JSP
Page</title>

    </head>

    <body>

        <h1>Courses</h1>

        <%

        try{

          // open a
connection

          Connection con =
null; 

         
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  //
load the driver

          con =
DriverManager.getConnection("
jdbc:sqlserver://

rubble.student.ad.fgcu.edu

:1433;databaseName=sp12ism3232x;user=s12ISM3232;password=s12ISM3232;");

           //

           // get the
parameters from the html form

           String
title = request.getParameter("title");

           String
instructor = request.getParameter("instructor");

           

           if(title
!= null && instructor != null)

           {

            //
create a prepared sql statement

            //

           
PreparedStatement prep = con.prepareStatement("Select crn, course,
title, instructor from CourseSchedule where title like ? and instructor
like ?");

           
prep.setString(1,"%"+title+"%");

           
prep.setString(2,instructor+"%");

            //
execute the query

           
ResultSet rs = prep.executeQuery();

            //
display the results in an html table

           
out.println("<table border=1>");

           
while(rs.next())

            {

            
out.println("<tr>");

            
out.println("<td>"+rs.getString(1)+"</td>");  // crn

            
out.println("<td>"+rs.getString(2)+"</td>");  // course

            
out.println("<td>"+rs.getString(3)+"</td>");  // title

             
out.println("<td>"+rs.getString(4)+"</td>");  //
instructor

            
out.println("</tr>");                                      


            }

           
out.println("</table>"); 

           
prep.close();    // close the prepared statement
                


           }

          
con.close(); // close the connection

           

          }

          catch(Exception
ex)

          {

          
out.println("Sorry, system is not currently available<br/>");

          
out.println(ex.toString());

          }

        

        %>

    </body>

</html>

Connection

A connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection. To create a connection, perform the following two statements:

    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");   // load the driver

    Connection con = DriverManager.getConnection("jdbc:sqlserver://rubble.student.ad.fgcu.edu:1433;databaseName=sp12ism3232x;user=s12ISM3232;password=s12ISM3232"); // connect

A Connection object's database is able to provide information describing its tables, its supported SQL grammar, its stored procedures, the capabilities of this connection, and so on. This information is obtained with the getMetaData method.
By default a Connection object is in auto-commit mode, which means that it automatically commits changes after executing each statement. If auto-commit mode has been disabled, the method commit must be called explicitly in order to commit changes; otherwise, database changes will not be saved.
Transactions - in many cases you will want to perform changes to a database in sets of statements called transactions. For example, if a banck customer wants to transfer money from savings to checking, both accounts will require an update statement. Ideally, the program should ensure that both statements are always done (you'd hate to have the system crash after removing money from savings and before adding it to checking). To perform updates as transactions you need to set the auto commit mode to false, perform the updates that are part of a transaction, then perform the commit function. If an error occurs during one of the updates, then rollback can be called (instead of commit) to undo the other updates performed as part of the transaction. Example:

 

       //
       // add three new orders as part of a transaction
       //
       try{
       sqlStatement = con.createStatement(); // create a statement object
       con.setAutoCommit(false); // turn off autocommit mode, updates will be held until commit is called
       }
       catch(SQLException sqle) {
          System.out.println("Sql Exception :"+sqle.getMessage());
       }

       String addString1 = "INSERT INTO Orders (CustomerPhone, ProductName, Quantity, PriceEach, OrderDate)" +
                               " VALUES('555-1234','Nails',24,.13, #2004-02-03#);";
       try{
         result = sqlStatement.executeUpdate(addString1);
         System.out.println(result+" records added to products table");
       }
       catch(SQLException sqle) {
          System.out.println("Sql Exception :"+sqle.getMessage());
          con.rollback(); // operation failed, rollback to previous state
       }


       String addString2 = "INSERT INTO Orders (CustomerPhone, ProductName, Quantity, PriceEach, OrderDate)" +
                              " VALUES('555-1234','Nails',24,.13, #2004-02-03#);";
       try{
         result = sqlStatement.executeUpdate(addString2);
         System.out.println(result+" records added to products table");
       }
       catch(SQLException sqle) {
          System.out.println("Sql Exception :"+sqle.getMessage());
          con.rollback(); // operation failed, rollback to previous state
       }

       String addString3 = "INSERT INTO Orders (CustomerPhone, ProductName, Quantity, PriceEach, OrderDate)" +
                                " VALUES('555-1234','Nails',24, .13, #2004-02-03#);";
       try{
         result = sqlStatement.executeUpdate(addString3);
         System.out.println(result+" records added to products table");
       }
       catch(SQLException sqle) {
          System.out.println("Sql Exception :"+sqle.getMessage());
          con.rollback();// operation failed, rollback to previous state
       }

       try{
         con.commit();  // commit all three updates to the database
       }
       catch(SQLException sqle) {
          System.out.println("Sql Exception :"+sqle.getMessage());
          con.rollback();// operation failed, rollback to previous state
       }

Methods:

public void setAutoCommit(boolean autoCommit)
                   throws SQLException
Sets this connection's auto-commit mode to the given state. If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback. By default, new connections are in auto-commit mode.
The commit occurs when the statement completes or the next execute occurs, whichever comes first. In the case of statements returning a ResultSet object, the statement completes when the last row of the ResultSet object has been retrieved or the ResultSet object has been closed.

NOTE: If this method is called during a transaction, the transaction is committed.

Parameters:

autoCommit - true to enable auto-commit mode; false to disable it

Throws:

SQLException - if a database access error occurs
public void commit()
            throws SQLException
Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object. This method should be used only when auto-commit mode has been disabled.

Throws:

SQLException - if a database access error occurs or this Connection object is in auto-commit mode
public void rollback()
              throws SQLException
Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object. This method should be used only when auto-commit mode has been disabled.

Throws:

SQLException - if a database access error occurs or this Connection object is in auto-commit mode
public void close()
           throws SQLException
Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released. Calling the method close on a Connection object that is already closed is a no-op.
Note: A Connection object is automatically closed when it is garbage collected. Certain fatal errors also close a Connection object. If autocommit mode is off, then you must commit or rollback all operations before calling close or an exception will be generated. This includes queries.

Throws:

SQLException - if a database access error occurs or this Connection object is in auto-commit mode
public Statement createStatement()
 throws SQLException
Creates a Statement object for sending SQL statements to the database. SQL statements without parameters are normally executed using Statement objects. If the same SQL statement is executed many times, it may be more efficient to use a PreparedStatement object.

Returns:

a new default Statement object

Throws:

SQLException - if a database access error occurs
public PreparedStatement prepareStatement(String sql)
   throws SQLException
Creates a PreparedStatement object for sending parameterized SQL statements to the database.
A SQL statement with or without IN parameters can be pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

Note: This method is optimized for handling parametric SQL statements that benefit from precompilation. If the driver supports precompilation, the method prepareStatement will send the statement to the database for precompilation. Some drivers may not support precompilation. In this case, the statement may not be sent to the database until the PreparedStatement object is executed. This has no direct effect on users; however, it does affect which methods throw certain SQLException objects.

Parameters:

sql - an SQL statement that may contain one or more '?' IN parameter placeholders

Returns:

a new default PreparedStatement object containing the pre-compiled SQL statement

Throws:

SQLException - if a database access error occurs

Statement

The object used for executing a static SQL statement and returning the results it produces.Only one ResultSet object per Statement object can be open at the same time. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.
Methods:

public ResultSet  executeQuery(String sql)    
             throws SQLException

Executes the given SQL statement, which returns a single ResultSet object.

Parameters:

sql - an SQL statement to be sent to the database, typically a static SQL SELECT statement

Returns:

a ResultSet object that contains the data produced by the given query; never null

Throws:

SQLException - if a database access error occurs or the given SQL statement produces anything other than a single ResultSet object

public int executeUpdate(String sql)
                  throws SQLException
Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.

Parameters:

sql - an SQL INSERT, UPDATE or DELETE statement or an SQL statement that returns nothing

Returns:

either the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing

Throws:

SQLException - if a database access error occurs or the given SQL statement produces a ResultSet object
public void close()
           throws SQLException
Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources.
Calling the method close on a Statement object that is already closed has no effect.

Note: A Statement object is automatically closed when it is garbage collected. When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

Throws:

SQLException - if a database access error occurs

Notes:

To insert records into a table with autonumber fields you must use the form of the INSERT INTO statement where you specify the column names. Specify all column names and values except for the column with the autonumber field. Leave that column name out of the column list and value list.

PreparedStatement

An object that represents a precompiled SQL statement. It can be used instead of a standard Statement object to efficiently execute a given statement multiple times. It also makes creating SQL commands easier as it takes care of formatting the data.
PreparedStatement objects are created by the Connection object prepareStatement() method.
The prepareStatement() method is passed a string containing the SQL command. ? characters are inserted where data values belong. The code can then use "setter" methods to insert the actual data. These setter methods take care of formatting the data in the proper fashion, e.g. adding single quotes around strings and putting dates into proper format. Example:

    try{
        PreparedStatement prep = 
                 con.prepareStatement("INSERT INTO Orders(CustomerPhone, ProductName, Quantity, PriceEach, OrderDate)"+
                                           " VALUES (?, ?, ?, ?, ?)");
        prep.setString(1,"555-7777");
        prep.setString(2,"Screws");
        prep.setInt(3,44);
        prep.setDouble(4,3.23);
        prep.setTimestamp(5,new Timestamp(System.currentTimeMillis()));
        prep.executeUpdate();
        con.commit();
      }

      catch(SQLException sqle) {
         System.out.println("Sql Exception :"+sqle.getMessage());
      }

Once the data has been "set" into the SQL command, then you can call executeQuery or executeUpdate to perform the function.
Setter methods generally take two arguments, the first being the index number of the data item (question mark), indexes are numbered starting with 1, the second argument is the actual data to be inserted into the SQL command in place of the question marks.

Setter methods:

Setter Method Name	SQL Data Type	MS Access-97 Data Type	value data type
setBoolean(int index, boolean value )	BIT	Yes/No	boolean
setByte(int index, byte value )	BYTE	Number-Byte	byte
setDate(int index, Date value)	DATETIME	Date/Time	java.sql.Date object
setDouble(int index, double value)	DOUBLE	Number-Double	double
setDouble(int index, double value)	CURRENCY	Currency	double
setFloat(int index, float value)	REAL	Number-Single	float
setInt(int index, int value)	INTEGER	Number-Long	int (32 bit integer)
setInt(int index, int value)	COUNTER	AutoNumber	int (32 bit integer)
setLong(int index, long value)	LONGNINT	not supported	long (64 bit integer)
setShort(int index, short value)	SMALLINT	Number-Integer	short (16 bit integer)
setString(int index, String value)	VARCHAR	Text	String object
setString(int index, String value)	LONGCHAR	Memo	String object
setTime(int index, Time value)	DATETIME	Date/Time	java.sql.Time object
setTimestamp(int index, Timestamp value)	DATETIME	Date/Time	java.sql.Timestamp object

ResultSet

A table of data representing a database result set, which is usually generated by executing a statement that queries the database.
A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set. E.g.

while(rs.next())
        {
          System.out.println("Name = " + rs.getString(1));
          System.out.println("Quantity on hand = "+rs.getInt(2));
          System.out.println("Cost = "+rs.getDouble(3));
          System.out.println("");
        }

A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row.
You can access column values for the current row using one of the ResultSet "getter" methods. The getter methods take either a column number (numbered starting at 1, not 0) or a column name.

In general, using the column index will be more efficient. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.
Column names used as input to getter methods are case insensitive. When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. The column name option is designed to be used when column names are used in the SQL query that generated the result set (using the AS keyword). For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, there is no way for the programmer to guarantee that they actually refer to the intended columns.

For the getter methods, a JDBC driver attempts to convert the underlying data to the Java type specified in the getter method and returns a suitable Java value. Therefore, you need to use the proper getter method when retrieving the value of each column. Note, getString will work on any column, no matter what type the data is.
Commonly used getter methods and their equivalent data types:

Getter Method Name	SQL Data Type	MS Access-97 Data Type	Java Return type
getBoolean(int column)	BIT	Yes/No	boolean
getByte(int column)	BYTE	Number-Byte	byte
getDate(int column)	DATETIME	Date/Time	java.sql.Date object
getDouble(int column)	DOUBLE	Number-Double	double
getDouble(int column)	CURRENCY	Currency	double
getFloat(int column)	REAL	Number-Single	float
getInt(int column)	INTEGER	Number-Long	int (32 bit integer)
getInt(int column)	COUNTER	AutoNumber	int (32 bit integer)
getLong(int column)	LONGNINT	not supported	long (64 bit integer)
getShort(int column)	SMALLINT	Number-Integer	short (16 bit integer)
getString(int column)	VARCHAR	Text	String object
getString(int column)	LONGCHAR	Memo	String object
getTime(int column)	DATETIME	Date/Time	java.sql.Time object
getTimestamp(int column)	DATETIME	Date/Time	java.sql.Timestamp object

It is possible to produce ResultSet objects that are scrollable and/or updatable. To do this you need to create a Statement object specifying the result set type and the concurrency type. At this time, this function is not supported by Windows MS Access drivers.