Working with DML and DDL using NetBeans IDE

This section explains the code provided in the previous section.  I would like to explain the code part by part.  Let us look at the first statement:

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");         

The above statement will simply load the Microsoft SQL Server 2000 driver into memory.  Further proceeding, we have the following:

            Connection conn = DriverManager.getConnection
("jdbc:microsoft:sqlserver://serverjag:1433","sa","");

The above statement will connect to the default SQL Server instance available at the server named “serverjag,” at the default port “1433,” with the user name “sa” and with a blank password.  Further proceeding, we have the following:

            PreparedStatement stmt = conn.prepareStatement("UPDATE Northwind..emp " +
                              "SET sal = ? " +
                              "WHERE empno = ?");

The above statement is a bit different from what we discussed in my previous article.  In my previous article, I worked with the “createStatement” method, which gives us a “Statement” object.

Now, we are working with the “prepareStatement” method, which gives us a “PreparedStatement” object.  A “PreparedStatement” is cached in memory for frequent execution.  But normally a “Statement” would never be cached in memory (and therefore you would see a slow performance, even when you issue the same type of statements very frequently).

In general, if you are issuing different DML statements, “Statement” would be helpful.  If you are issuing the same DML statement with a difference in values, “PreparedStatement” is better.  In the above statement, you can observe that the UPDATE statement is prepared with missing values (values with a question mark).  In fact, at this moment, the UPDATE statement would not be executed at all.  It is simply “prepared” in memory.

Further proceeding we have the following:

            stmt.setInt(1, 5000);
            stmt.setInt(2, 1002);
            stmt.executeUpdate( );

The first line replaces the first question mark in the UPDATE statement with 5000.  Similarly, the second line replaces the second question mark with 1002. Once you have provided the values for both missing quantities, you can execute the statement by issuing the “executeUpdate” command (with no parameters).

In the above code, I am not using the same “PreparedStatement” more than once (because it is a simple demonstration).  If you need to use the same “PreparedStatement” by applying new values (by replacing old values with new values), you need to issue the following statements (before applying the new values):

stmt.clearParameters( );
conn.commit( );

This is critical when dealing with the “PreparedStatement” multiple times, because you must clear the cache of existing values. 

Even though I provided only an UPDATE statement, you are free to work with any INSERT or DELETE with the same kind of method.