Here we can post some general purpose codes

-------------------------------------------------------------------------------------------------------------------------------------
The following is a general purpose code which can be used for database connectivity(uses MICROSOFT SQL SERVER RC2)
import java.sql.*;
 
public class connectURL {
 
   public static void main(String[] args) {
 
      // Create a variable for the connection string.
      String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
         "databaseName=ICUDB;user=sqlserver;password=sql";
 
      // Declare the JDBC objects.
      Connection con = null;
      Statement stmt = null;
      ResultSet rs = null;
 
      try {
         // Establish the connection.
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         con = DriverManager.getConnection(connectionUrl);
 
         // Create and execute an SQL statement that returns some data.
         String SQL = "SELECT * from patient";
         stmt = con.createStatement();
         rs = stmt.executeQuery(SQL);
 
         // Iterate through the data in the result set and display it.
         while (rs.next()) {
            System.out.println(rs.getString(2) + " " + rs.getString(3));
         }
      }
 
      // Handle any errors that may have occurred.
      catch (Exception e) {
         e.printStackTrace();
      }
      finally {
         if (rs != null) try { rs.close(); } catch(Exception e) {}
         if (stmt != null) try { stmt.close(); } catch(Exception e) {}
         if (con != null) try { con.close(); } catch(Exception e) {}
      }
   }
}

Changing postgres password in Ubuntu

Selection_002.png






The following is a general purpose code which can be used for database connectivity(uses postgres)
import java.sql.*;
public class DbConnection1 // A class to perform all the database Operations
{
static final String DbDriver   = "org.postgresql.Driver";  // To set the driver
static final String strConnection    = "jdbc:postgresql://localhost:5432/ICUDb"; // Connection string
static final String strDbUsername = "postgres"; // Username of the database user
static final String strDbPassword = "sql"; // Password of the database user
static Connection   con; // Connection object to establish the connection to the database
static ResultSet    rs; // Resultset to store the information retrieved from the database
static Statement    st; // Statement object of the database connection
 
public DbConnection1 () // Constructor of the class
{
    makeConnection(); // To establish the database connection
}
 
public void makeConnection() // To establish the database connection, No return value and parameters
{
    try
    {
       Class.forName(DbDriver); // Setting the driver
       con = DriverManager.getConnection(strConnection, strDbUsername, strDbPassword); // Establishing the connection with the database
       st = con.createStatement(); // Assigning the statement to the connection
       System.out.println("Connection Success....");
    }
    catch (Exception e) // In case of any Exception
    {
        System.out.println(e); // Print the Exception
    }
}
 
public void insertIntoDatabase(String strInsertQuery) // A function which inserts the datavalues into the databases, No return value, Query as Parameter
{
    boolean IsResult = false; // To test whether the record is inserted
 
    try
    {
        IsResult = st.execute(strInsertQuery); // Execute the insert Query
        //DatabaseConnection.commit(); // Commit
    }
    catch (Exception e)
    {
        System.out.println(e); // Incase of any error print the error
    }
}
 
public void updataDatabase(String strUpdateQuery) // A function which updates the changes into the database, No return type, Query as parameter
{
    try
    {
        int row = st.executeUpdate(strUpdateQuery); // Returns No of rows affected
    }
    catch (Exception e)
    {
        System.out.println(e); // In case of errors, print the error
    }
}
 
public ResultSet selectFromDatabase(String strSelectQuery) // A function which retrieves Records from the database, Returns Recordset, Query as parameter
{
    try
    {
     rs = st.executeQuery(strSelectQuery); // Execute the select query
        return rs; // Returns the resultset
    }
    catch (Exception e)
    {
        System.out.println(e); // Print the exception
        return null; // Return NULL
    }
}
public void deleteFromDB(String strDeleteQuery) // A function Which is used to execute delete Queries, No Return type, Query as Argument
{
    boolean IsReturn = false; // To check Whether the Query is appropriately Executed
    try
    {
        IsReturn = st.execute(strDeleteQuery); // Executing the Query
    }
    catch (Exception e)
    {
        System.out.println(e); // Incase of Error, Print the error
    }
}
public void disConnect() {
    try {
       con.close();
    } catch (SQLException e) {e.printStackTrace();}
 
}
}
 
 
Please find a sample implementation code for the same
import java.sql.*;
public class Hospital {
 
    public static void main(String args[]){
        String insertQuery1,insertQuery2;
        String updateQuery;
        String selQuery;
        String delQuery;
        ResultSet rs1;
DbConnection1 dbc=new DbConnection1();
//dbc.makeConnection();
 
dbc.deleteFromDB("delete from patient");// deletes all data in patient table
//insert query(take care of primary key issue)
 
insertQuery1="insert into patient values(67,'JOAN')";
insertQuery2="insert into patient values(100,'JOHN')";
dbc.insertIntoDatabase(insertQuery1);
dbc.insertIntoDatabase(insertQuery2);
//delete query
delQuery="delete from patient where pname='JOAN'";
dbc.deleteFromDB(delQuery);
//update Query
updateQuery="update table patent set pid=200 where pname='JOHN' ";
//select Query(acquiring result set)
selQuery="select * from patient";
rs1=dbc.selectFromDatabase(selQuery);
 
try{
while(rs1.next())
{
System.out.println(rs1.getInt(1)+":"+rs1.getString(2));
}}catch(Exception e){e.printStackTrace();}
 
dbc.disConnect();
 
}
 
}


Posted By: Bichu

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Bean for Database connectivity and a corresponding jsp page

Bean

package com.icu.sample;
import java.sql.*;
import java.io.*;
import java.util.*;
import javax.naming.*;
import javax.sql.DataSource;
import java.sql.*;
 
public class DbBean1 {
  //String dbURL = "jdbc:db2:sample";
  //String dbDriver = "COM.ibm.db2.jdbc.app.DB2Driver";
       DataSource ds = null;
       Connection con = null;
       Statement stmt = null;
       InitialContext ic;
  //private Connection dbCon;
  public DbBean1(){
   connect();
   }
  public void connect() {
 
      try{
      ic = new InitialContext();
      ds = (DataSource) ic.lookup("java:jboss/datasources/sample");
 
      con = ds.getConnection();
      stmt = con.createStatement();
      //System.out.println("Success");
     }catch(Exception e){e.printStackTrace();}
 
 
      }
 
  public void close() throws SQLException{
  con.close();
   }
 
  public ResultSet execSQL(String sql) throws SQLException{
  Statement s = con.createStatement();
  ResultSet r = s.executeQuery(sql);
  return (r == null) ? null : r;
  }
 
 
  public int updateSQL(String sql) throws SQLException{
     Statement s = con.createStatement();
   int r = s.executeUpdate(sql);
   return (r == 0) ? 0 : r;
  }
}
 
JSP PAGE
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
 
<%@ page language="Java" import="java.sql.*" %>
<jsp:useBean id="db" scope="request" class="com.icu.sample.DbBean1" />
<jsp:setProperty name="db" property="*" />
<%
ResultSet rs;
try{
db.connect();
//inserting values to the course table
db.updateSQL("insert into course values('TESTING788','CS111','CSA111','SEM111')");
}catch(Exception e){e.printStackTrace();}
rs=db.execSQL("select * from course where stuid='TESTING788'");
 
//displaying values in a table using result set
while(rs.next())
{%>
 
<table border="1">
<tr><th>COURSE_ID</th><th>SECTION</th><th>SEMESTER</th></tr>
<tr><td><%=rs.getString("course_id")%></td><td><%=rs.getString("sec")%></td><td><%=rs.getString("sem")%></td></tr>
</table>
 
<%
}
db.close();
%>
</body>
</html>