Tuesday, May 19, 2009

Database

First Normal Form: No Repeating Elements or Groups of Elements.
NF1 addresses two issues:
  1. A row of data cannot contain repeating groups of similar data (atomicity); and
  2. Each row of data must have a unique identifier (or Primary Key).


Second Normal Form: No Partial Dependencies on a Concatenated Key.
For each column ask:
  1. Can this column exist without one or the other part of the concatenated primary key?If the answer is "yes" — even once — then the table fails Second Normal Form.

Once the initial combined table is split, as the above question of each new sub-table that has been created till all columns are in tables that can be identified by a single non-concatenated primary key.

Third Normal Form: No Dependencies on Non-Key Attributes

Wednesday, June 18, 2008

J2EE Fundas

A WAR file is a convenient way to bundle up all pieces of a web application. All servlet containers know how to deploy a WAR file, they expand the bundle, look for the WEB-INF directory, and read the web.xml found there for further deployment instructions.

The EAR file provides the same type of functionality for a full-fledged J2EE application. JBoss expands the EAR, finds the required deployment descriptors, and proceeds from there.

Each component in the EAR is a specific piece of the J2EE puzzle.

Web module : A WAR file containing presentation tier components.
EJB module : An EJB JAR file containing the middle-tier components. (EJBs, MDBs, etc.)
Java module: A regular JAR file containing classes and libraries that are shared across the entire application. An application client JAR and a common JAR are two examples of Java modules.

Java JDBC

What is Java DataBase Connectivity?
The JDBC API defines a set of interfaces that encapsulate major database functionality. Database vendors provide drivers that implement the API for specific databses.


What is the latest version of JDBC ? What are the new features are added in that ?
JDBC 2.0 provides features like treating resultsets as javaBeans, pooling databse connections, and looking up database connection info from a naming service,storing java Objects in databases etc.


What are 4 drivers available in JDBC ?
1) JDBC-ODBC Bridge Driver -> Uses a bridge technology to connect a Java client to an ODBC database.
2) Native API Partly Java Drivers -> LIke Oracle drivers, they are native C++ Api's with a java wrapper.
3) Net Protocol All Java Drivers. -> Define a generic Netowrk protocol that interfaces with custom middleware like WebLogic
4) Native- Protocol All Java Drivers -> Written entirely in Java they can access a database directly over the Net without any additional software.


How do you connect to the Database ?
1. Loading the driver:
Class.forName("oracle.jdbc.driver.OracleDriver");
Class.forName automatically creates an instance of a driver and registers it with the DriverManager. When the driver is loaded it is available for making a connection with a DBMS.
2. Making the Connection :
The second step in establishing a connection is to have the appropriate driver connect to the DBMS.
If you are using the JDBC-ODBC Bridge driver, the JDBC URL will start with jdbc:odbc: . The rest of the URL is generally the data source name or database system. So, if the ODBC data source is called “Fred" the JDBC URL could be jdbc:odbc:Fred .

String url = "jdbc:oracle:thin:"+
userID+"/"+Pswd+
"@"+hostName+":"+oraclePort+":"+oraInstance;
Connection con = DriverManager.getConnection(url);


How many types of JDBC Statements are there?
1) Statement.
2) Prepared Statement.
3) Callable Statement.


What is a statement, how do you create one?
A Statement object sends SQL statement to the DBMS. A Statement object is created and then executed with the appropriate “execute” method with the SQL statement. For SELECT statements, the method used is “executeQuery” and for statements that create or modify tables, the method used is “executeUpdate”. An active connection is required to create a Statement object:


String sqlQuery =” create table temp (name VARCHAR (32), empid INTEGER, salary INTEGER)”;
Statement stmt = con.createStatement();
stmt.executeUpdate(sqlQuery);

“sqlQuery” is a DDL (data definition language) statement. Statements that create, alter, or drop tables are DDL statements and are executed with the method executeUpdate. executeUpdate is also used to execute SQL statements that update a table.


What is a a Prepared Statement, How do you create one?
If you want to execute a Statement object many times, a PreparedStatement object can be used to reduce execution time. Unlike a Statement object, a Prepared Statement is given an SQL statement when it is created. The advantage is that this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, the PreparedStatement object contains an SQL statement that has been precompiled.

PreparedStatement updateTemp= con.prepareStatement("update temp set salary = ? where name like ?");
updateTemp.clearParameters();
updateTemp.setInt(1,5000);
updateTemp.setSting(2,”Srujan”);
updateTemp.executeUpdate();


The return value of an executeUpdate method is an int with the number of rows updated. When the method executeUpdate is used to execute a DDL statement it returns 0.


What is a Stored Procedure?
A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.
Oracle PL/SQL Stored Procedure

CREATE OR REPLACE PROCEDURE show_temp
( id IN INTEGER
sal IN OUT INTEGER) IS
BEGIN
SELECT salary INTO sal FROM temp
where empid = id ;
sal := sal + sal * 0.12; // 12 % hike :)
UPDATE temp SET salary = sal WHERE empid = id;
END;


The procedure “show_temp” will be compiled and stored in the database as a database object that can be called.


What is a Callable Statement, How do you create one?
A CallableStatement object contains a call to a stored procedure. The first line of code below creates a call to the stored procedure “show_temp” using the connection con . The part that is enclosed in curly braces is the escape syntax for stored procedures. When the driver encounters "{call show_temp}”, it will translate this escape syntax into the native SQL used by the database to call the stored procedure named “show_temp” .

CallableStatement cs = con.prepareCall("{call show_temp(?,?)}");
cs.registerOutParameter(2, Types.Interger);
cs.setInt(1,14075);
cs.setInt(2,1000);
cs.executeQuery(); //ResultSet rs can be used as well.
out.println( cs.getInt(2));


If the procedure had contained one update or one DDL statement, the method executeUpdate would have been used. A stored procedure may contain more than one SQL statement, in which case it will produce more than one result set, more than one update count, or some combination of result sets and update counts. In this case, where there are multiple results, the method execute should be used to execute the CallableStatement .

The class CallableStatement is a subclass of PreparedStatement , so a CallableStatement object can take input parameters just as a PreparedStatement object can.


How do you create and use Result Sets.
JDBC returns results in a ResultSet object.

sqlQuery =”select name,empid from temp”;
ResultSet rs = stmt.executeQuery(sqlQuery);
while (rs.next()) {
String s = rs.getString("name");
int n = rs.getInt("empid");
System.out.println(s + " " + n);
}

The variable rs , which is an instance of ResultSet , contains the rows of results. To access the data we go to each row and retrieve the values according to their types. The method next moves what is called a cursor to the next row and makes that row (called the current row) the one upon which we can operate. Since the cursor is initially positioned just above the first row of a ResultSet object, the first call to the method next moves the cursor to the first row and makes it the current row. Successive invocations of the method next move the cursor down one row at a time from top to bottom. The JDBC 2.0 API, allows the cursor to move backwards, to specific positions, and to positions relative to the current row in addition to moving the cursor forward.

getXXX methods of the appropriate type are used to retrieve the value of each column. JDBC allows you to use either the column name or the column number as the argument to a getXXX method. Using the column number is slightly more efficient.

String s = rs.getString(1);
Int n = rs.getInt(2);

A ResultSet object is created with a cursor pointing above the first row. The first call to the next method positions the cursor on the first row of rs.




What is a transaction? How do you commit a transaction, how do you disable Auto-commit Mode?
A transaction is a set of SQL Statements that must behave atomically. When a connection is created, it is in auto-commit mode. The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode. If auto-commit is disabled, no SQL statements will be committed until the method “commit” is called explicitly.

con.setAutoCommit(false);
con.commit();
con.rollback(); To roll back a transaction if there was an Exception in the catch block.


What is Connection Pooling? How is it implemented?
Opening a connection to a database is a time-consuming process. For shortqueries, it can take much longer to open the connection than to perform theactual database retrieval.A connection pool class should be able to perform the following tasks:

1. Preallocate the connections.
Perform this task in the class constructor.
Assume thatmakeNewConnection simply calls the getConnectionmethod ofDriverManager.

availableConnections = new Vector(initialConnections);
busyConnections = new Vector();
for(int i=0; i < initialConnections; i++)
{
availableConnections.addElement(makeNewConnection());
}


2. Manage available connections.
If a connection is required and an idle connection is available,put it in the list of busy connections and then return it.

public synchronized Connection getConnection()throws SQLException
{
if (!availableConnections.isEmpty())
{
Connection existingConnection =(Connection)availableConnections.lastElement();
int lastIndex = availableConnections.size() -1;
availableConnections.removeElementAt(lastIndex);
if (existingConnection.isClosed())
{
notifyAll();
// Freed up a spot for anybody waiting.
return(getConnection()); // Repeat process.
}
else
{
busyConnections.addElement(existingConnection);
return(existingConnection);
}
}
}


3. Allocate new connections.If a connection is required, there is no idle connection available,and the connection limit has not been reached, then start a background thread to allocate a new connection.

if ((totalConnections() < maxConnections) &&!connectionPending)
{ // Pending = connecting in bg
makeBackgroundConnection();
}
try
{
wait(); // Give up lock and suspend self.
} catch(InterruptedException ie) {}
return(getConnection()); // Try again.


4. Wait for a connection to become available.
This situation occurs when there is no idle connection and you’ve reached the limit on the number of connections. This waiting should be accomplished without continual polling. The natural approach is to use the wait method, which gives up the thread synchronization lock and suspends the thread until notify or notifyAll is called. Since notifyAllcould stemfrom several possible sources, threads that wake up still need totest to see if they can proceed.

try {wait();}
catch(InterruptedException ie) {}
return(getConnection());

It may be that you don’t want to let clients wait and would rather throw an exception when no connections are available and theconnection limit has been reached. In such a case, do the following instead

throw new SQLException("Connection limit reached");


5. Close connections when required.Note that connections are closed when they are garbage col-lected, so you don’t always have to close them explicitly. But,you sometimes want more explicit control over the process.

public synchronized void closeAllConnections()
{
// The closeConnections method loops down Vector, calling
// close and ignoring any exceptions thrown.
closeConnections(availableConnections);
availableConnections = new Vector();
closeConnections(busyConnections);
busyConnections = new Vector();
}



If you are truncated using JDBC, How can you know ..that how much data is truncated ?

Java Garbage Collection

What do you know about the garbage collector ?
The JVM's heap stores all objects created by a Java program. Objects are created by Java's "new" operator, and memory for new objects is allocated on the heap at run time. Garbage collection is the process of automatically freeing objects that are no longer referenced by the program. The garbage collector runs finalizers on objects, since it is not generally possible to predict exactly when unreferenced objects will be garbage collected, it is not possible to predict when object finalizers will be run. Programmers should avoid writing code for which program correctness depends upon the timely finalization of objects.


How can you force garbage collection?
You can't force GC, but could request it by calling System.gc(). JVM does not guarantee that GC will be started immediately.

Dates in C#

String to DateTime // String to DateTime
String MyString; MyString = "1999-09-01 21:34 PM";
DateTime MyDateTime; MyDateTime = new DateTime();
MyDateTime = DateTime.ParseExact(MyString, "yyyy-MM-dd HH:mm tt", null);

DateTime to String //DateTime to String
MyDateTime = new DateTime(1999, 09, 01, 21, 34, 00);
String MyString; MyString = MyDateTime.ToString("yyyy-MM-dd HH:mm tt");

Format String For Dates
The following is the most communally used format characters:
d - Numeric day of the month without a leading zero.
dd - Numeric day of the month with a leading zero.
ddd - Abbreviated name of the day of the week.
dddd - Full name of the day of the week.
f,ff,fff,ffff,fffff,ffffff,fffffff - Fraction of a second. The more F's the higher the presision.
h - 12 Hour clock, no leading zero.
hh - 12 Hour clock with leading zero.
H - 24 Hour clock, no leading zero.
HH - 24 Hour clock with leading zero.
m - Minutes with no leading zero.
mm - Minutes with leading zero.
M - Numeric month with no leading zero.
MM - Numeric month with a leading zero.
MMM - Abbreviated name of month.
MMMM - Full month name.
s - Seconds with no leading zero.
ss - Seconds with leading zero.
t - AM/PM but only the first letter.
tt - AM/PM
y - Year with out century and leading zero.
yy - Year with out century, with leading zero.
yyyy - Year with century.
zz - Time zone off set with +/-.

SQL Server stuff

To select into a declared variable
SELECT @VariableName = (SELECT ColumnName FROM TabelName WHERE Condition)

C# antics

Alright here are some things abt C#

? Nullable value:
int? value , implies that value is an integer and is nullable.

?? Null Coalescing Operator
Takes 2 operands and returns the left operand if it is not null else returns the right operand

? nullableObject = null;
temp = nullableObject ?? new SomeObjectType();
this is equivalent to
temp = (nullableObject.HasValue )? nullableObject.Value : new SomeObjectType();
string filename = GetFileName() ?? "Untitled File";

@ Operator
string path = "C:\\Program Files\\MyPage" and
string path = @"C:\Program Files\MyPage"
are the same. "@" tells the compiler that the string should be treated as verbatim and so no escaping of characters is required.
Converting From Streams and Back
Here is a sample where I convert from a stream to a byte array:

strm = mqMsg.BodyStream;
BinaryReader br = new BinaryReader(strm);
byteArray= br.ReadBytes(Convert.ToInt32(strm.Length));

Here is a sample where I convert from a byte array to a stream:

MemoryStream memStream = new MemoryStream();
BinaryWriter binWriter = new BinaryWriter(memStream);
binWriter.Write(byteArray);