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();
}
}
}
Category Archives: Java
How to create Java bean and use for CRUD Operation
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());
}
}
Executing a Select statement in Java
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 which returns records from select statement
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();
}
}
How to Get values from MySQL Stored Procedure in Java that returns more than one resultset
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");
}
Counting Array Arguments
package com.apryll.package2;
public class sample2
{
public static void main(String args[])
{
sample2 objsample2 = new sample2();
objsample2.argCount(1, 2, 3, 4);
}
public void argCount(int num1, int num2, int... args)
{
System.out.println(num1);
System.out.println(num2);
System.out.println(args.length);
}
}
OP
1
2
2
Please Note the Last 2 which accounts for the array argument args as num1 and num2 does not account for array arguments
If the Code is like below then it would have been a error
public void argCount(int num1, int num2, int... args)
Creation of Static Array
static final int[] a = { 100,200 };
static final int[] a;
static { a=new int[2]; a[0]=100; a[1]=200; }
Looping through Arguments in Function
How to Loop Through Arguments Passed to function
package com.apryll.package2;
public class sample2
{
public static void main(String args[])
{
sample2 objsample2 = new sample2();
objsample2.argCount(1, 2, 3, 4);
}
public void argCount(int... args)
{
System.out.println(args.length);
}
}
Importing Classes and Methods
Sample1.java
package com.apryll.package1;
//import static com.apryll.package2.sample2.Funct3;
public class Sample1
{
public static void main(String[] args)
{
Sample1 objSample1 = new Sample1();
//sample2 objSample2 = new sample2();
System.out.println(com.apryll.package2.sample2.City);
objSample1.Funct2();
//objSample2.Funct4();
//Sample1.Funct1();
}
static void Funct1()
{
System.out.println("I am Static Function 1");
}
void Funct2()
{
System.out.println(Funct3());
System.out.println(City);
}
}
Sample2.java
package com.apryll.package2;
public class sample2
{
public static int City = 555;
public static String Funct3()
{
return "Hi there";
}
public void Funct4()
{
System.out.println("Hello There");
}
}
1
package com.apryll.package1;
import com.apryll.package2.sample2.*;
public class Sample1
{
public static void main(String[] args)
{
sample2 objSample2 = new sample2();
}
}
The Above Code Does not Work when creating a object for sample2 since in the import statements is sample2.* is given.It should be
import com.apryll.package2.sample2; . . . .
2
While Importing class methods and variables the static will not get imported unless you specify static
import in the import statement
You can Import the Static Methods and Variables in class as below
To Import static methods and Variables
import static com.apryll.package2.sample2.*;
To Import static methods and Variables
import static com.apryll.package2.sample2.*;
To Import static Variable City
import static com.apryll.package2.sample2.City;
To Import static Function getCityName
import static com.apryll.package2.sample2.getCityName;
3
You can directly access static method and Variable declared in some other package in static void main() method as follows
System.out.println(com.apryll.package2.sample2.City); System.out.println(com.apryll.package2.sample2.Funct3());
Static Methods and Variables in Java
How to call Static Method in Another Package
Sample4.java
package com.scjp.test;
public class Sample4
{
public static void CallMe()
{
System.out.println("I am Static in Another Package");
}
public static void main(String[] args)
{
}
}
Sample6.java
import com.scjp.test.*;
public class Sample6
{
public static void main(String[] args)
{
Sample4.CallMe();
}
}