STEPS TO CONNECT WITH DATABASE | Java J2EE Tutorial pdf

STEPS TO CONNECT WITH DATABASE

There are seven standard steps in querying databases:
1. Load the JDBC driver.
2. Establish the connection.
3. Create a statement object.
4. Execute a query or update.
5. Process the results.
6. Close the connection.
1. Load the JDBC driver
Loading the driver or drivers we want to use is very simple and involves just one line of code. If,
for example, we want to use the JDBC–ODBC Bridge driver, the following code will load it:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
2. Establish the connection
The second step in establishing a connection is to have the appropriate driver connect to the DBMS. The following line of code illustrates the general idea:
Connection con = DriverManager.getConnection(url, "myLogin", "myPassword");
This step is also simple, the hardest part being what to supply for url. If we are using the JDBC–ODBC Bridge driver, the JDBC URL will start with jdbc:odbc:. The rest of the URL is generally our data source name or database system. So, if we are using ODBC to access an ODBC data source called "Fred", for example, our JDBC URL could be jdbc:odbc:Fred. In place of "myLogin" we put the name we use to log in to the DBMS; in place of "myPassword" we put our password for the DBMS. So if we log in to our DBMS with a login name of "Fernanda" and a password of "J8", just these two lines of code will establish a connection:
String url = "jdbc:odbc:Fred";
Connection con = DriverManager.getConnection(url, "Fernanda", "J8");
3. Create a statement object
A Statement object is used to send queries and commands to the database and is created from the Connection as follows:
Statement statement = con.createStatement();
4. Execute a query or update
Once we have a Statement object, we can use it to send SQL queries by using the executeQuery method, which returns an object of type Result-Set. Here is an example:
String query = "SELECT col1, col2, col3 FROM sometable";
ResultSet resultSet = statement.executeQuery(query);
To modify the database, use executeUpdate instead of executeQuery, and supply a string that uses UPDATE, INSERT, or DELETE. Other useful method in the Statement class include execute (execute an arbitrary command).
5. Process the results
The simplest way to handle the results is to process them one row at a time, using the ResultSet’s next method to move through the table a row at a time. Within a row, ResultSet provides various getXxx methods that take a column index or column name as an argument and return the result as a variety of different Java types. For instance, use getInt if the value should be an integer, getString for a String, and so on for most other data types. If we just want to display the results, we can use getString regardless of the actual column type. However, if we use the version that takes a column index, note that columns are indexed starting at 1 (following the SQL convention), not at 0 as with arrays, vectors, and most other data structures in the Java
programming language.
Here is an example that prints the values of the first three columns in all rows of a ResultSet.
while(resultSet.next()) {
System.out.println(results.getString(1) + " " + results.getString(2) + " " + results.getString(3));
}
6. Close the connection
To close the connection explicitly, we would do:
con.close();
Complete program :--
import java.sql.*;
public class jdbc_type1 {
public static void main(String a[]) throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:TEST");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("select * from test");
while(rs.next()){
System.out.println(rs.getString(1));
}
rs.close();
st.close();
con.close();
}
}