Communication Between JSP and SQL

For the communication between JSP and SQL to take place java plays an important role. You can write the java code in JSP itself. It makes the code more less complicated. You don’t need to pass parameters from one file to another.
The java code in JSP is enclosed in ‘<%’ ‘%>’ tags. It helps differentiate the HTML or XML code from java code in JSP file.

So the first step towards setting a connection between JSP and SQL is that you have to load com.mysql.jdbc.Driver into memory.
For that you need to write –

    Class.forName("com.mysql.jdbc.Driver");

A call to Class.forName(“com.mysql.jdbc.Driver”) causes the class named com.mysql.jdbc.Driver to be dynamically loaded (at runtime). A call to forName(“com.mysql.jdbc.Driver”) causes the class named com.mysql.jdbc.Driver to be initialized.

Then the second step is to establish a connection and access the SQL.
For this, you write the following code –

    String connectionUrl = "jdbc:mysql://localhost:8080/database_name";
    String dbUser = "username";
    String dbPwd = "password";
    
    Connection conn=DriverManager.getConnection(connectionUrl, dbUser, dbPwd);

After this, you create an SQL statement. A Statement is an interface that represents a SQL statement. This is shown as below

    Statement statement = conn.createStatement();

Now you are ready to execute the required query and get the required data from SQL to JSP.
To do so, call an execute method from Statement

    ResultSet resultset = statement.executeQuery("select *  from table_name"); 

You have got the desired result in the resultset.
Now to see the result in the resultset write the following code

    while (rs.next()) {
       String column1 = rs.getString("column_name1");
       String column2 = rs.getString("column_name2");
       int column3 = rs.getInt("column_name3");
       System.out.println(column1 + "\t" + column2 + "\t" + column3);
    }	

In order to perform operations like insert, delete you need to write the following code –

    statement.executeUpdate("insert into table_name (column_name1,column_name2,column_name3) values ("value1","value2","value3");

After performing the operations the final step is to close the statement and the connection

    if(statement !=null)
    {
	statement.close();
    }

    if (conn != null)
    {
	conn.close();
    }

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download

Thanks,
Prayag Mane