Transcript JSP-database

JSP/Database Connectivity
Instructor: Dr. M. Anwar Hossain
Accessing a Database: Processing Steps
1.
2.
3.
4.
5.
6.
7.
Load a driver which is compatible with the database that is to be
processed.
Define and establish a connection to the database.
Associate an SQL statement with this connection.
Execute the SQL statement.
The SQL statement which has been executed will produce a table
which is stored in a ResultSet object. This object will contain a
reference to the rows of the table that has been formed by the
execution of the SQL statement.
Execute further SQL statements as above.
When the processing associated with the database is complete the
database is closed and the connection to the database is also closed.
Accessing a Database: Processing Steps Example
1. Class.forName("org.gjt.mm.mysql.Driver");
2. Connection conn = DriverManager.getConnection(
"jdbc:mysql://mysql0.ee.surrey.ac.uk:3306/webtech",
"webtech", "webtech");
3. Statement st = conn.createStatement();
4. ResultSet rs=st.executeQuery("SELECT * FROM images");
5. while(rs.next()){
anInteger = rs.getInteger(1);
aString = rs.getString(2);
}
7. st.close();
rs.close();
conn.close();
Define and Establish the Connection
Create a connection object
Connection conn = null;
Load the JDBC driver and connect to the database
Class.forName("driver...");
conn = DriverManager.getConnection("url..."
, [Username], [Password]);
NOTE: All functions of connecting and using a database should be enclosed
within a try-catch block.
NOTE: A database connection should always be closed after the code has finished
using the database.
Create a Statement Object and Execute
Create a Statement object and execute the SQL
Statement st = conn.createStatement();
// for selecting records
ResultSet rs = st.executeQuery("query...");
or
// for inserting, deleting or updating records
int numRows = st.executeUpdate("query...");
NOTE: Capturing exceptions is important and should not be ignored
Process the Results
Process the ResultSetMetaData
ResultSetMetaData rm = rs.getMetaData();
rm.getColumnCount(); // Number of columns
rm.getColumnName(1); // Name of column
rm.getColumnType(1); // Data type of column
Process the ResultSet
rs.next(); // move to next record, returns boolean
rs.getXxx();
rs.getString(1); // using column id
rs.getString("name"); // using column name
rs.getInteger(2);
Rs.getObject(3);
Example: Displaying results on a Table
First (based on previous 3 slides):
Define and Establish the Connection
Create a Statement Object and Execute the SQL
Get the ResultSetMetaData and ResultSet
Then, get number of columns and build a list of column names
int columns = rm.getColumnCount();
result = "<tr>";
for ( int i = 1; i <= columns; i++){
result += "<td>" + rm.getColumnLabel(i) + "</td>";
}
result += "</tr>";
Example: Displaying results on a Table
Then, get the actual data
while(rs.next()) {
result += "<tr>";
for ( int i = 1; i <= columns; i++) {
result += "<td>" +
rs.getObject(i).toString() + "</td>";
}
result += "</tr>";
}
Close the Statement, ResultSet, and Connection
st.close();
rs.close();
conn.close();
Eventually, you print the results on a JSP page
<table border="1">
<%=result%>
</table>