admin « CodeThatAint

CREATE TABLE ipaddress(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                       date VARCHAR(255),
                       ip VARCHAR(255));

INSERT INTO ipaddress(date, ip)
               VALUES('1-1-2012', '195.165.1.2'),
                     ('1-1-2012', '195.165.1.3'),
                     ('12-2-2012', '195.165.1.8');

Total number of Visits should be grouped based on IP Address

SELECT COUNT(ip), MONTHNAME(STR_TO_DATE(date, '%c-%e-%Y')) as period
  FROM ipaddress
 GROUP BY period;

package com.javadb2.mugil.employee;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.javadb2.mugil.db.display;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

public class update
{	 
	public static void main(String args[])
	{
	  bean objBean = new bean();
	  objBean.setEmpId(Integer.parseInt(getInput("Enter Employee Id :")));
	  objBean.setEmpName((String)getInput("Enter Employee Name:"));
	  objBean.setEmpSalary(Integer.parseInt(getInput("Salary")));
	  updateValues(objBean);
	}
	
	public static void updateValues(bean objBean)
	{	
		System.out.println(objBean.getEmpName());
		System.out.println(objBean.getEmpSalary());
		
		String strSQL = "UPDATE employeelist" +
				"   SET empname = ? ," +
				"       salary  = ?" +
				" WHERE empid = ?";
		
		try 
		{
			Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "pass");
			PreparedStatement stmt = (PreparedStatement) conn.prepareStatement(strSQL);
			
			stmt.setString(1, objBean.getEmpName());
			stmt.setDouble(2, objBean.getEmpSalary());
			stmt.setInt(3, objBean.getEmpId());
			
			int affected = stmt.executeUpdate();
						
			if(affected == 1)
			{
 			  System.out.println("Updated Successfully");
			}
			else
			{
			  System.err.println("Unable to Update Employee");
			}
		} 
		catch (SQLException e) 
		{
		  e.printStackTrace();
		}
	}

	public static String getInput(String prompt)
	{
		BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in));
		
		System.out.print(prompt);
		System.out.flush();
		
		try 
		{
			return stdin.readLine();
		} 
		catch (Exception e) 
		{
			// TODO: handle exception
			return e.getMessage();
		}
	}
}

Database Table

 CREATE TABLE employeeList(empid int not null primary key auto_increment,
                           empname varchar(255),
                           salary  double);

Bean Class

package com.javadb2.mugil.employee;

public class bean
{
  private int  empId;
  private String  empName;
  private double  empSalary;
	
  public int getEmpId() 
  {
    return empId;
  }
	
  public void setEmpId(int empId) 
  {
    this.empId = empId;
  }
	
  public String getEmpName() 
  {
    return empName;
  }
  
  public void setEmpName(String empName) 
  {
    this.empName = empName;
  }
  
  public double getEmpSalary() 
  {
    return empSalary;
  }

  public void setEmpSalary(double empSalary) 
  {
    this.empSalary = empSalary;
  }
}

Crud Operation with Bean Class

 package com.javadb2.mugil.employee;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.javadb2.mugil.db.display;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

public class insert
{	
  public static void main(String[] args) 
  {
    bean objBean = new bean();
		
    objBean.setEmpName(display.getInput("Enter the Employee Name"));
    objBean.setEmpSalary(Integer.parseInt(display.getInput("Enter Salary")));
		
    boolean result = getInsertedKey(objBean);
		
    if(result)
    {
      System.out.println("The Last Inserted Primary Key is "+objBean.getEmpId());			 
    }
   }
	
   public static boolean getInsertedKey(bean objBean)
   {	
     ResultSet keys = null;
		
     String strSQL = "INSERT INTO employeeList(empname, salary)VALUES(?,?)";		
		
     try 
     {
	Connection conn 	= getConnection();
	PreparedStatement stmt 	= (PreparedStatement) conn.prepareStatement(strSQL, Statement.RETURN_GENERATED_KEYS);
			
	stmt.setString(1, objBean.getEmpName());
	stmt.setDouble(2, objBean.getEmpSalary());
			
	int affected = stmt.executeUpdate();
			
	if(affected == 1)
	{
  	   keys = stmt.getGeneratedKeys();
	   keys.next();
	   int newkey = keys.getInt(1);
	   objBean.setEmpId(newkey);
	}
	else
	{
	   System.err.println("Unable to Update Values");
	   return false;
	}
      }
      catch (SQLException e) 
      {
	 processException(e);
	 System.err.println("Error in Establishing Connection");
      }
		
      return true;
  }
	
  public static Connection getConnection() throws SQLException
  {
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "pass");
    return conn;
  }
	
  public static void processException(SQLException e)
  {
     System.err.println("Error Message "+e.getMessage());
     System.err.println("Error Code"+e.getErrorCode());
     System.err.println("SQL State"+e.getSQLState());
  }
}

 public class JAVADB2
 {
   public static void main(String args[]) throws SQLException 
   {	
     try	
     {
	Connection conn = null;		
	ResultSet  rs 	= null;
	Statement  stmt	= null;
			
	String strSQL = "SELECT * FROM employee";
	conn = incDB.getConnection();
	stmt = (Statement)conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.FETCH_FORWARD);			
			
 	rs = stmt.executeQuery(strSQL);			
	display.getRecords(rs);
      } 
      catch (SQLException e) 
      {
	incDB.getException(e);
      }
    }	
}
Utility Methods
 public class incDB
{	
	private static String USER_NAME = "root";
	private static String USER_PASS = "pass";
	private static String CONN_STRING = "jdbc:mysql://localhost/test";
	
	public static Connection getConnection() throws SQLException
	{
		return DriverManager.getConnection(CONN_STRING, USER_NAME, USER_PASS);
	}
	
	public static void getException(SQLException e)
	{
		System.out.println("Message :"+e.getMessage());
		System.out.println("Error Code :"+e.getErrorCode());
		System.out.println("State : "+e.getSQLState());
	}
}

public class incDB
{	
	private static String USER_NAME = "root";
	private static String USER_PASS = "";
	private static String CONN_STRING = "jdbc:mysql://localhost/test";
	
	public static Connection getConnection() throws SQLException
	{
		return DriverManager.getConnection(CONN_STRING, USER_NAME, USER_PASS);
	}
	
	public static void getException(SQLException e)
	{
		System.out.println("Message :"+e.getMessage());
		System.out.println("Error Code :"+e.getErrorCode());
		System.out.println("State : "+e.getSQLState());
	}
}

Stored Procedure

  DROP PROCEDURE IF EXISTS getEmpDetail;
  CREATE PROCEDURE getEmpDetail(IN Salary INT)
  BEGIN   
   SELECT empid, empname, sal
     FROM employee
    WHERE sal < Salary;
  END;

Java Code

public static void main(String args[]) throws SQLException 
{	
  try	
  {
    Connection conn = null;		
    ResultSet  rs   = null;
			
    Integer salary = Integer.parseInt(display.getInput("Enter the Salary Amount"));
			
    String strSQL = "{CALL getEmpDetail(?)}";
    conn = incDB.getConnection();
    PreparedStatement stmt = (PreparedStatement) conn.prepareStatement(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.FETCH_FORWARD);			
    
    stmt.setInt(1, salary);
    rs = stmt.executeQuery();
		
    display.getRecords(rs);
  } 
  catch (SQLException e) 
  {
    incDB.getException(e);
  }
}

Utility Function

 public class incDB
{	
	private static String USER_NAME = "root";
	private static String USER_PASS = "pass";
	private static String CONN_STRING = "jdbc:mysql://localhost/test";
	
	public static Connection getConnection() throws SQLException
	{
		return DriverManager.getConnection(CONN_STRING, USER_NAME, USER_PASS);
	}
	
	public static void getException(SQLException e)
	{
		System.out.println("Message :"+e.getMessage());
		System.out.println("Error Code :"+e.getErrorCode());
		System.out.println("State : "+e.getSQLState());
	}
}

public static void getRecords(ResultSet rs) throws SQLException 
{	
  while(rs.next())
  {
    StringBuffer buffer =  new StringBuffer();
    buffer.append("Emp Id :"+ rs.getString("empid")+ " Emp Name :"+rs.getString("empname"));
    System.out.println(buffer.toString());
  }
		
  if(rs.isBeforeFirst())
    System.out.println("Finished Printing");
 }
	
 public static String getInput(String prompt)
 {
   BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in));
   System.out.print(prompt);
   System.out.flush();
	
   try 
   {
     return stdin.readLine();
   } 
   catch (Exception e) 
   {
     return "Error :"+e.getMessage();
   }
  }

Stored Procedure

 DROP PROCEDURE IF EXISTS getEmpDetails;
CREATE PROCEDURE getEmpDetails(IN Salary INT, OUT total INT)
BEGIN   
   SELECT count(*) into total
    FROM employee
   WHERE sal < Salary;
  SELECT empid, empname, sal
    FROM employee
   WHERE sal < Salary;
END;

Java Code

public static void main(String args[]) throws SQLException 
{	
  try	
  {
     Connection conn = null;		
     ResultSet  rs   = null;
			
     Integer salary = Integer.parseInt(display.getInput("Enter the Salary Amount"));			
     String strSQL  = "{CALL getEmpDetails(?, ?)}";
     conn           = incDB.getConnection();
     java.sql.CallableStatement stmt = conn.prepareCall(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.FETCH_FORWARD);			
     stmt.setInt(1, salary);
     stmt.registerOutParameter("total", Types.INTEGER);
			
     rs        = stmt.executeQuery();
     int nRows = stmt.getInt("total");
			
     System.out.println("Total no of Records :"+nRows);
     display.getRecords(rs);
  } 
  catch (SQLException e) 
  {
     incDB.getException(e);
  }
}

Utility Functions

public static void getRecords(ResultSet rs) throws SQLException 
{	
   while(rs.next())
   {
     StringBuffer buffer =  new StringBuffer();
     buffer.append("Emp Id :"+ rs.getString("empid")+ " Emp Name :"+rs.getString("empname"));
     System.out.println(buffer.toString());
   }
		
    if(rs.isBeforeFirst())
      System.out.println("Finished Printing");		
}

The first Location from second table should be added to the first table Location.

CREATE TABLE projectlist(ProjectId   INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                         ProjectName VARCHAR(50),
                         Location    VARCHAR(50));
                             
CREATE TABLE LocationList(LocaId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                          ProjectId INT,
                          Location    VARCHAR(50));

INSERT INTO projectlist(ProjectName)
                 VALUES('Project A'),
                       ('Project B'),
                       ('Project C'),
                       ('Project D'),
                       ('Project E'),
                       ('Project F'),
                       ('Project G'),
                       ('Project H');
                           
INSERT INTO LocationList(ProjectId, Location)
                  VALUES(1, 'Location A'),
                        (1, 'Location C'),                      
                        (3, 'Location B'),
                        (4, 'Location C'),
                        (5, 'Location D'),
                        (2, 'Location C'),
                        (2, 'Location B'),
                        (2, 'Location A'),
                        (6, 'Location A'),
                        (6, 'Location B'),
                        (7, 'Location B'),
                        (8, 'Location D'),
                        (8, 'Location A');

Query 2

UPDATE projectlist JOIN (SELECT LocationList.ProjectID,
                                LocationList.Location
                           FROM LocationList
                           JOIN (SELECT LocationList.ProjectID,
                                       min(LocaId) minLocaId
                                  FROM LocationList
                                 GROUP BY LocationList.ProjectID) l1
                             ON LocationList.LocaId = l1.minLocaID) l2 ON projectList.ProjectID = l2.ProjectID
   SET ProjectList.Location = l2.Location;

        url = "/property/realestate_statistics/";	
	
	lngCityId = $('#cboCityName').val();
	
        $.post(
	  url,
	  {
	   "act" 		: "GetCommonLocation",
	   "CityId" 		: lngCityId
	  },
	  function(responseText){		
	   $('#cboLocation').html(responseText);		
	   $('#cboPropertyType').html('');
  	   $('#cboPropertyType').append( new Option('Property Type',''));      			
	   $('#cboPropertyType').val('Property Type');
	  }, 
	  "html"
	);

I have a response text coming after changing city and this will be populated in second drop down
cboLocation. Now the third drop down should be repopulated based on property type at city by taking common location selected in to consideration. for that you should reset property type apart from resetting cboLocation.

To do so

        $('#cboPropertyType').html('');
  	$('#cboPropertyType').append( new Option('Property Type',''));      			
	$('#cboPropertyType').val('Property Type');

$(‘#cboPropertyType’).html(”) – To empty the Content of Drop down
$(‘#cboPropertyType’).append( new Option(‘Property Type’,”)) – It should Contain only Property Type in Option so i am Appending that
$(‘#cboPropertyType’).val(‘Property Type’) – It should point to only one option that exixts

 $strLocation = 'http://www.google.com';
 $strLink = "HI there";
 (or)
 $strLink = 'HI there';

 print $strLink;

Using ' is html special char which remains undetected during string operations and will appear only in browser.

Using simple escapes the string

Method 1

function open_in_new_tab(url )
{
  window.open(url, '_blank');
  window.focus();
}

Method 2

 $('a').click(function() {
  $(this).attr('target', '_blank');
 });

Method 3

 $(document).ready(function() {
   $('#NewTab').click(function() {
        $(this).target = "_blank";
        window.open($(this).prop('href'));
        return false;
   });
});

M	T	W	T	F	S	S
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

M	T	W	T	F	S	S
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

CodeThatAint

Author Archives: admin

Query for website site counter based on month

Updatating Database through bean

How to create Java bean and use for CRUD Operation

Executing a Select statement in Java

Stored procedure which returns records from select statement

How to Get values from MySQL Stored Procedure in Java that returns more than one resultset

Query2

Jquery Dropdown Population after ajax Request

How to escape single quotes in javascript

how to open link in new tab using javascript