JDBC CRUD application[java]

By: Unknown on 1:52 AM

Lets create a small CRUD application in JDBC using core java.Our app consists of a menu which holds the following options

• Create
• Retrieve
• Update
• Delete

Lets create a table called jobs in oracle  first.

create table jobs(id varchar(30),title varchar(30),min_sal varchar(30),max_sal varchar(30));

Sourcefile

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class CRUDapplication {
    public static void main(String... a) {
        Connection con;
        ResultSet rs;
        PreparedStatement ps;
        String ex;
        Scanner s = new Scanner(System.in);
        while (true) {
            try {
                System.out.println("***CRUD APPLICATION***");
                int opt = 0;
                int result;
                String jid, jtit;

                int minsal, maxsal;

                System.out.println("1.Create");
                System.out.println("2.Retrieve");
                System.out.println("3.Update");
                System.out.println("4.Delete");
                System.out.println("Enter Your Choice");

                opt = s.nextInt();
                switch (opt) {

                    case 1:
                        System.out.println("Enter JOB ID: ");
                        jid = s.next();
                        System.out.println("Enter JOB TITLE: ");
                        jtit = s.next();
                        System.out.println("Enter MIN SALARY: ");
                        minsal = s.nextInt();
                        System.out.println("Enter MAX SALARY: ");
                        maxsal = s.nextInt();
                        try {
                            Class.forName("oracle.jdbc.driver.OracleDriver");
                            con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "oracle");
                            ps = con.prepareStatement("insert into jobs values(?,?,?,?)");
                            ps.setString(1, jid);
                            ps.setString(2, jtit);
                            ps.setInt(3, minsal);
                            ps.setInt(4, maxsal);
                            result = ps.executeUpdate();
                            if (result == 1) {
                                System.out.println("Inserted Successfully");
                            }
                        } catch (Exception e) {
                            System.out.println("Error---> " + e);
                        }
                        break;
                    case 2:
                        try {
                            Class.forName("oracle.jdbc.driver.OracleDriver");
                            con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "oracle");
                            ps = con.prepareStatement("select * from jobs");
                            rs = ps.executeQuery();
                            while (rs.next()) {
                                System.out.println("JOB ID: " + rs.getString(1));
                                System.out.println("JOB TITLE: " + rs.getString(2));
                                System.out.println("MIN SALARY: " + rs.getInt(3));
                                System.out.println("MAX SALARY: " + rs.getInt(4));
                            }
                        } catch (Exception e) {
                            System.out.println("Error---> " + e);
                        }
                        break;
                    case 3:
                        System.out.println("Enter JOB ID to be updated: ");
                        jid = s.next();
                        System.out.println("Enter new JOB TITLE: ");
                        jtit = s.next();
                        System.out.println("Enter new MIN SALARY: ");
                        minsal = s.nextInt();
                        System.out.println("Enter new MAX SALARY: ");
                        maxsal = s.nextInt();
                        try {
                            Class.forName("oracle.jdbc.driver.OracleDriver");
                            con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "oracle");
                            ps = con.prepareStatement("update jobs set title=?,min_sal=?,max_sal=? where id=?");
                            ps.setString(1, jtit);
                            ps.setInt(2, minsal);
                            ps.setInt(3, maxsal);
                            ps.setString(4, jid);
                            result = ps.executeUpdate();
                            if (result == 1) {
                                System.out.println("Updated Successfully");
                            } else {
                                System.out.println("Invalid id");
                            }
                        } catch (Exception e) {
                            System.out.println("Error---> " + e);
                        }
                        break;

                    case 4:
                        System.out.println("Enter JOB ID tobe deleted: ");
                        jid = s.next();
                        try {
                            Class.forName("oracle.jdbc.driver.OracleDriver");
                            con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "oracle");
                            ps = con.prepareStatement("delete from jobs where id=?");
                            ps.setString(1, jid);
                            result = ps.executeUpdate();
                            if (result == 1) {
                                System.out.println("Deleted Successfully");
                            } else {
                                System.out.println("Invalid id");
                            }
                        } catch (Exception e) {
                            System.out.println("Error---> " + e);
                        }
                        break;

                }

            } catch (Exception e) {
                System.out.println("Error--> " + e);
            }
            System.out.println("Enter n to exit s to continue");
            ex = s.next();
            if (ex.equals("n")) {
                break;
            } else if (ex.equals("s")) {
                continue;
            }
        }
    }
}

SCREENSHOTS

Create

Retrieve

Update

Delete

Read More

How to Run JDBC program through cmd

By: Unknown on 12:33 AM

Okay fine welcome back upto now we have seen how to write a JDBC program. Do you remember the diagram of JDBC API there is a driver in between JDBC API and Database. That driver comes into picture while getting our program executed.Okay lets see how to do
>>Find the driver i.e ojdbc14.jar file in the path  C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib
copy the jar file and paste it in your current directory where your source files(.java) are present.

>>To compile : Open cmd and type javac -cp ojdbc14.jar Insert.java

>>To run  : java -cp ojdbc14.jar; Insert

Note: If we donot copy jar file ojdbc14 to the current directory we have to say the path of jar file while compiling and running to JVM so that all the classes present int the jar file are loaded and get executed. If we donot mention the classpath correctly famous classNotFoundException may arise.

Read More

Basic DB operations JDBC

By: Unknown on 6:07 AM

Lets see how basic DB operations can be done like

• insertion
• retrieval
• updation
• deletion

Lets create a table first in the oracle : "create table users(nm varchar(20),pw varchar(2),em varchar(20));"

Insert.java

import java.sql.*;
public class Insert {

    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement ps = null;
        int rows_affected = 0;
        String classnm = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:XE";
        String username = "system";
        String password = "oracle";
        String sql = "insert into users values(?,?,?)";

        try {
            Class.forName(classnm);
            con = DriverManager.getConnection(url, username, password);
            ps = con.prepareStatement(sql);
            ps.setString(1, "abc");
            ps.setString(2, "xyz");
            ps.setString(3, "abc@xyz.com");
            rows_affected = ps.executeUpdate();
            ps.close();            
            con.close();
         }
            catch (Exception e) {
            System.out.println("Error--> " + e);
        }
            if (rows_affected == 1) {
                System.out.println("Inserted Sucessfully");
            } else {
                System.out.println("Try again");
            }
        }

    }

Retrieve.java

import java.sql.*;
public class Retrieve {

    public static void main(String[] args) {

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int rows_affected = 0;
        String classnm = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:XE";
        String username = "system";
        String password = "oracle";
        String sql = "select * from users";

        try {

            Class.forName(classnm);
            con = DriverManager.getConnection(url, username, password);
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
           //loops until rs has records
            while (rs.next()) {

                System.out.println("Name :" + rs.getString("nm"));
                System.out.println("Password :" + rs.getString("pw"));
                System.out.println("Email :" + rs.getString("em"));
            }
            ps.close();
            rs.close();
            con.close();

        } catch (Exception e) {

            System.out.println("Error--> " + e);

        }
    }

}

Update.java

import java.sql.*;
public class Update {
    public static void main(String[] args) {

        Connection con = null;
        PreparedStatement ps = null;
        int rows_affected = 0;
        String classnm = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:XE";
        String username = "system";
        String password = "oracle";
        String sql = "update users set pw=? where em=?";

        try {

            Class.forName(classnm);
            con = DriverManager.getConnection(url, username, password);
            ps = con.prepareStatement(sql);
            ps.setString(1, "def");
            ps.setString(2, "abc@xyz.com");
            rows_affected = ps.executeUpdate();
            ps.close();
            con.close();

        } catch (Exception e) {
            System.out.println("Error---> " + e);

        }

        if (rows_affected == 1) {
            System.out.println("Updated Succesfully");

        } else {
            System.out.println("Try again");
        }
    }
}

Delete.java

import java.sql.*;

public class Delete {

    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement ps = null;
        int rows_affected = 0;
        String classnm = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:XE";
        String username = "system";
        String password = "oracle";
        String sql = "delete from users where em=?";

        try {
            Class.forName(classnm);
            con = DriverManager.getConnection(url, username, password);
            ps = con.prepareStatement(sql);
            ps.setString(1, "abc@xyz.com");
            rows_affected = ps.executeUpdate();
            ps.close();
            con.close();
        } catch (Exception e) {
            System.out.println("Error---> " + e);
        }
        if (rows_affected == 1) {
            System.out.println("Deleted Succesfully");
        } else {
            System.out.println("Try again");
        }

    }

}

Note: The values can be given dynamically by using Scanner class or graphically using Swings but i have shown static way of doing that just to demonstrate how actually the stuff goes. I will cover them in next posts.

Read More

Phonebook Application in PHP

By: Unknown on 9:26 PM

Lets create a simple phone book application in php which covers all the basic database operations like insert,retrieve,update,delete. Our app takes 4 fields from user: Contact name,number,email,city.Our main aim here is to see all the 4 basic operations in work. Thats the reason why iam building this application. The application which include these four can also be called as CRUD application(C-Create, R-Retrieve,U-Update,D-Delete).

our app consists of following files

• index.php
• insert.php
• retrieve.php
• update.php
• delete.php

index.php

<!DOCTYPE html>

<html>
    <head>
        <meta charset="UTF-8">
        <title>PhoneBook</title>
    </head>
    <body style="font-size: 15pt">
        <h1 align='center'>PhoneBook Application</h1>
        <table align='center' border='1'>
            <form method="post" action="insert.php">
                <tr><td>Name</td><td><input type="text" name="t1"/></td></tr>
                <tr><td>Number</td><td><input type="text" name="t2"/></td></tr>
                <tr><td>Email</td><td><input type="email" name="t3"/></td></tr>
                <tr><td>City</td><td><input type="text" name="t4"/></td></tr>
                <tr><td colspan="2"><input type="submit" value="Save"/>
                <input type="submit" value="View" formaction="retrieve.php"/>
                <input type="submit" value="Update" formaction="update.php"/>  
                <input type="submit" value="Delete" formaction="delete.php"/></td></tr>
            </form>              
        </table>
        <label>Note:</label>
        <ul>
            <li>
                Insert all fields to save contact.
            </li>
            <li>
                Click view to see saved contacts.
            </li>
            <li>
                Insert number field to delete contact.
            </li>
            <li>
                Insert name,number,email fields to update contact number.
            </li>
        </ul>
    </body>
</html>

insert.php

<?php

$name = $_POST["t1"];
$number = $_POST["t2"];
$email = $_POST["t3"];
$city = $_POST["t4"];

$server = "localhost";
$user = "root";
$password = "mysql";
$database = "phone";

$con = mysql_connect($server, $user, $password);
mysql_select_db($database, $con);
$query = "insert into contacts values('$name','$number','$email','$city')";
mysql_query($query);
$rows_affected = mysql_affected_rows();
if ($rows_affected > 0) {
    echo "<h1 align='center'>Contact saved!</h1>"
    . "</br><div align='center'><a href='index.php'>[back]</a></div>";
} else if ($rows_affected == 0) {
    echo "<h1 align='center'>Details provided are invalid</h1>"
    . "</br><div align='center'><a href='index.php'>[back]</a></div>";
} else {
    echo "<h1 align='center'>Error--->" . mysql_error() . "</h1>"
    . "</br><div align='center'><a href='index.php'>[back]</a></div>";
}
mysql_close();
?>

retrieve.php

<?php

error_reporting(0);
$server = "localhost";
$user = "root";
$password = "mysql";
$database = "phone";
$con = mysql_connect($server, $user, $password);
mysql_select_db($database, $con);
$query = "select * from contacts";
$result = mysql_query($query);
echo "<table align='center' border='1'><thead><tr><td>Name</td><td>Number</td><td>Email</td><td>City</td></tr></thead>";
echo "<tbody>";
while ($row = mysql_fetch_array($result)) {
    echo "<tr><td>" . $row["nm"] . "</td>";
    echo "<td>" . $row["num"] . "</td>";
    echo "<td>" . $row["em"] . "</td>";
    echo "<td>" . $row["ci"] . "</td></tr>";
}
echo "</tbody></table>"
 . "</br><div align='center'><a href='index.php'>[back]</a></div>";
mysql_close();
?>

update.php

<?php

$name = $_POST["t1"];
$num = $_POST["t2"];
$email = $_POST["t3"];

$server = "localhost";
$user = "root";
$password = "mysql";
$database = "phone";

$con = mysql_connect($server, $user, $password);
mysql_select_db($database, $con);
$query = "update contacts set num='$num' where nm='$name' and em='$email'";
mysql_query($query);
$rows_affected = mysql_affected_rows();
if ($rows_affected > 0) {
    echo "<h1 align='center'>Contact updated!</h1>"
    . "</br><div align='center'><a href='index.php'>[back]</a></div>";
} else if ($rows_affected == 0) {
    echo "<h1 align='center'>Details provided are invalid</h1>"
    . "</br><div align='center'><a href='index.php'>[back]</a></div>";
} else {
    echo "<h1 align='center'>Error--->" . mysql_error() . "</h1>"
    . "</br><div align='center'><a href='index.php'>[back]</a></div>";
}
mysql_close();
?>

delete.php

<?php

$num = $_POST["t2"];

$server = "localhost";
$user = "root";
$password = "mysql";
$database = "phone";

$con = mysql_connect($server, $user, $password);
mysql_select_db($database, $con);
$query = "delete from contacts where num='$num'";
mysql_query($query);
$rows_affected = mysql_affected_rows();
if ($rows_affected > 0) {
    echo "<h1 align='center'>Contact deleted!</h1>"
    . "</br><div align='center'><a href='index.php'>[back]</a></div>";
} else if ($rows_affected == 0) {
    echo "<h1 align='center'>Details provided are invalid</h1>"
    . "</br><div align='center'><a href='index.php'>[back]</a></div>";
} else {
    echo "<h1 align='center'>Error--->" . mysql_error() . "</h1>"
    . "</br><div align='center'><a href='index.php'>[back]</a></div>";
}
mysql_close();
?>

SCREENSHOTS

Insert

Retrieve

Update

Delete

Read More

Simple Registration Form in php

By: Unknown on 11:33 PM

As of now we know how to design, how to code and how to insert data into database. So lets implement them by creating a registration form and inserting those details into mysql database.

1.Registraion form.html[UI]

Take 4 textfields as of now and a submit button to insert them

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Registration form</title>
    </head>
    <body>
        <h1 align='center'>Registration Form</h1>
        <table align='center' border='1'>
            <form method="post" action="insert.php">
                <tr><td>Username</td><td><input type="text" name="t1" required="required"/></td></tr>
                <tr><td>Password</td><td><input type="password" name="t2" required="required"/></td></tr>
                <tr><td>Email id</td><td><input type="email" name="t3" required="required"/></td></tr>
                <tr><td>Mobile</td><td><input type="text" name="t4" required="required"/></td></tr>
                <tr><td colspan="2" align="right"><input type="submit" value="Register"/></td></tr>
            </form>               
        </table>
    </body>
</html>

I have used some html5 input type attributes like required,email don't worry about them just focus on the structure.

• required="required" will make sure that textfield is not left empty before submitting the form.
• type="email" checks wether a valid email is entered or not.

Now our form is ready lets code insert.php through which entered values will hit the database.

But before that we have to create a database and table in mysql remeber that
database:  create database demo;
table: create table users(un varchar(20),pw varchar(20),em varchar(50),mb varchar(20));

2.insert.php

In this script we have to make sure that the values from form are inserted into the database. For that first we have to read values from the form using $_POST["element name"] in our case t1,t2,.. and thereafter we have to follow the steps we have seen earlier. 

<?php
$username = $_POST["t1"];
$password = $_POST["t2"];
$email = $_POST["t3"];
$mobile = $_POST["t4"];

$server = "localhost";
$user = "root";
$password = "mysql";
$database = "demo";

$con = mysql_connect($server, $user, $password);
mysql_select_db($database, $con);
$query = "insert into users values('$username','$password','$email','$mobile')";
$result = mysql_query($query);
if ($result) {
    echo "<h1 align='center'>Details posted!</h1>";
} else {
    echo "<h1 align='center'>Error---></h1>" . mysql_error();
}
mysql_close();
?>

Now open mysql and pass a query "select * from users;" so that you can see the inserted data.

SCREENSHOTS:

Read More

How to run a PHP project

By: Unknown on 10:22 PM

To run a php project first we need to have a server in our localsystem(since we are testing our project localhost will act as both client and server).Download xampp server from here. After downloading install it. Now follow the series of steps to run the project

• Open xampp control panel and start the Apache.

• Copy your project folder and place it under C:\xampp\htdocs.

• Open browser and type "localhost/projectfoldername/or filename.php".

• You can give like this "localhost/projectfoldername" if your project contains index.php otherwise mention the file name like this "localhost/projectfoldername/filename.php".
• Filename need not to be php it can be anything like html too.

Read More

Why Programming

By: Unknown on 7:44 PM

Programming is essential in my view, its easy to learn and implement. As we are in advanced era we should learn how to code. Many organisations are promoting the importance of coding and one such is code.org.  Okay Whats the real use of programming? For example you lost your calculator and you have to calculate  hell a lot of expression, you cant do it manually, so if you know how to program a computer you can easily code it in any language and computer will do the rest and also you can use it any number of times.In this way programming provide solution to many real-world problems that is the real power of programming.

Things you can do with programming.

• You can build apps for your own Desktop-PC.
• You can build apps for your own Mobile.
• You can create your own Web-Apps.

Isnt that interesting building apps on your own,of your interest. Sounds really good huh so explore the real power of programming now.

Cheers and All the Best

-Ravi Chandra Durvasula

Read More