All DBMS PROGRAM
EXP1
Title : Connection between client & data using two tier.
package javaapplication3;
import java.sql.*;
import java.util.Scanner;
public class JavaApplication3 {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/test";
// Database credentials
static final String USER = "root";
static final String PASS = "";
public static void main(String[] args) {
Scanner in = new Scanner(System.in);
int userChoice;
boolean quit = false;
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
System.out.println("Creating table in given database...");
stmt = conn.createStatement();
//STEP 4: Execute a query
do {
System.out.println("1. create table");
System.out.println("2.Insert");
System.out.println("3. update");
System.out.println("4 delete");
System.out.print("5. select");
userChoice = in.nextInt();
switch (userChoice) {
case 1:
String sql = "CREATE TABLE REGISTRATION3 " +
"(id INTEGER not NULL, " +
" first VARCHAR(255), " +
" last VARCHAR(255), " +
" age INTEGER, " +
" PRIMARY KEY ( id ))";
stmt.executeUpdate(sql);
System.out.println("Created table in given database...");
break;
case 2:
//
stmt = conn.createStatement();
sql = "INSERT INTO Registration3 " +
"VALUES (100, 'Zara', 'Ali', 18)";
stmt.executeUpdate(sql);
sql = "INSERT INTO Registration3 " +
"VALUES (101, 'Mahnaz', 'Fatma', 25)";
stmt.executeUpdate(sql);
sql = "INSERT INTO Registration3 " +
"VALUES (102, 'Zaid', 'Khan', 30)";
stmt.executeUpdate(sql);
sql = "INSERT INTO Registration3 " +
"VALUES(103, 'Sumit', 'Mittal', 28)";
stmt.executeUpdate(sql);
System.out.println("Inserted records into the table...");
break;
case 3:
// update
stmt = conn.createStatement();
String sql1 = "UPDATE Registration3 " +
"SET age = 30 WHERE id in (100, 101)";
stmt.executeUpdate(sql1);
// Now you can extract all the records
// to see the updated records
sql1 = "SELECT id, first, last, age FROM Registration3";
ResultSet rs = stmt.executeQuery(sql1);
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
rs.close();
break;
case 4:
//delete
sql = "DELETE FROM Registration3 " +
"WHERE id = 101";
stmt.executeUpdate(sql);
// Now you can extract all the records
// to see the remaining records
sql = "SELECT id, first, last, age FROM Registration3";
ResultSet rs1 = stmt.executeQuery(sql);
while(rs1.next()){
//Retrieve by column name
int id = rs1.getInt("id");
int age = rs1.getInt("age");
String first = rs1.getString("first");
String last = rs1.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
rs1.close();
break;
case 5:
//Select
sql = "SELECT id, first, last, age FROM Registration";
rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
rs.close();
break;
case 0:
quit = true;
break;
default:
System.out.println("Wrong choice.");
break;
}
System.out.println();
} while (!quit);
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
}//end JDBCExample
EXP2
Title : Three tier architecture using mysql to implement
joining in two table.
--%>
<%--
--%>
<%@page import = "java.sql.*" %>
<%@page import = "java.io.*" %>
<%@page import = "java.text.*" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<%
// JDBC driver name and database URL
String JDBC_DRIVER = "com.mysql.jdbc.Driver";
String DB_URL = "jdbc:mysql://localhost/test?";//java database connectivity
// Database credentials
String USER = "root";
String PASS = "";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("Hi");
//STEP 4: Execute a query
stmt = conn.createStatement();
String sql;
if(request.getParameter("J1")!= null)
{
System.out.println("Hi1");
sql = "select * from emp INNER JOIN dept on emp.deptno=dept.deptno";
rs = stmt.executeQuery(sql);
//STEP 5: Extrata result set
%>
<center>
<h1>INNER JOIN</h1>
<table title="INNER JOIN" cellpadding="1" cellspacing="1" border="true">
<tr>
<td> eno </td>
<td> ename </td>
<td> deptno </td>
<td> deptno </td>
<td> dname </td>
<td> loc </td>
</tr>
<%
while(rs.next()){
//Retrieve by column name
long emp_id = rs.getInt(1);
String E_name= rs.getString(2);
String E_dept = rs.getString(3);
int D_code = rs.getInt(4);
String D_name = rs.getString(5);
String L = rs.getString(6);
//Display values
%>
<tr>
<td> <%=emp_id%> </td>
<td> <%=E_name%> </td>
<td> <%=E_dept%> </td>
<td> <%=D_code%> </td>
<td> <%=D_name%> </td>
<td> <%=L%> </td>
</tr>
<%
}
%>
</table>
</center>
<%
}
else if(request.getParameter("J2") != null)
{
sql = "select * from emp LEFT JOIN dept on emp.deptno=dept.deptno";
rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
%>
<center>
<h1>LEFT JOIN</h1>
<table title="LEFT JOIN" cellpadding="1" cellspacing="1" border="true">
<tr>
<td> eno </td>
<td> ename </td>
<td> deptno </td>
<td> deptno </td>
<td> dname </td>
<td> loc </td>
</tr>
<%
while(rs.next()){
long emp_id = rs.getInt(1);
String E_name= rs.getString(2);
String E_dept = rs.getString(3);
int D_code = rs.getInt(4);
String D_name = rs.getString(5);
String L = rs.getString(6);
//Retrieve by column name
%>
<tr>
<td> <%=emp_id%> </td>
<td> <%=E_name%> </td>
<td> <%=E_dept%> </td>
<td> <%=D_code%> </td>
<td> <%=D_name%> </td>
<td> <%=L%> </td>
</tr>
<%
}
%>
</table>
</center>
<%
}
else
{
sql = "select * from emp RIGHT JOIN dept on emp.deptno=dept.deptno";
rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
%>
<center>
<h1>RIGHT JOIN</h1>
<table title="RIGHT JOIN" cellpadding="1" cellspacing="1" border="true">
<tr>
<td> eno </td>
<td> ename </td>
<td> deptno </td>
<td> deptno </td>
<td> dname </td>
<td> loc </td>
</tr>
<%
while(rs.next()){
//Retrieve by column name
long emp_id = rs.getInt(1);
String E_name= rs.getString(2);
String E_dept = rs.getString(3);
int D_code = rs.getInt(4);
String D_name = rs.getString(5);
String L = rs.getString(6);
//Display values
%>
<tr>
<td> <%=emp_id%> </td>
<td> <%=E_name%> </td>
<td> <%=E_dept%> </td>
<td> <%=D_code%> </td>
<td> <%=D_name%> </td>
<td> <%=L%> </td>
</tr>
<%
}
%>
</table>
</center>
<%
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close();
}
catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}
catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
%>
</body>
</html>
EXP3
Title : Implement sql DDL statement using two tier architecture.
package pract1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class abc {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://172.15.2.94/test";
// Database credentials
static final String USER = "comp4";
static final String PASS = "comp4";
public static void main(String[] args) {
Scanner in = new Scanner(System.in);
int userChoice;
boolean quit = false;
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
System.out.println("Creating table in given database...");
stmt = conn.createStatement();
//STEP 4: Execute a query
do {
System.out.println("1. create table");
System.out.println("2.Insert");
System.out.println("3.view");
System.out.println("4.index");
System.out.print("5. select");
userChoice = in.nextInt();
switch (userChoice) {
case 1:
String sql = "CREATE TABLE INFO " +
"(id INTEGER not NULL, " +
" name VARCHAR(20), " +
" dep VARCHAR(20), " +
"per integer,"+
"year integer,"+
" PRIMARY KEY ( id ))";
stmt.executeUpdate(sql);
System.out.println("Created table in given database...");
break;
case 2:
//
stmt = conn.createStatement();
sql = "INSERT INTO INFO " +
"VALUES (101, 'PAYAL', 'COMP', 75,2015)";
stmt.executeUpdate(sql);
sql = "INSERT INTO INFO " +
"VALUES (102, 'GAURI', 'E&Tc', 72,2015)";
stmt.executeUpdate(sql);
//sql = "INSERT INTO INFO" +
// "VALUES (103, 'SUJIT', 'MECH',69, 2015)";
//stmt.executeUpdate(sql);
sql = "INSERT INTO INFO " +
"VALUES(110, 'AKASH', 'MECH',80, 2015)";
stmt.executeUpdate(sql);
sql = "INSERT INTO INFO " +
"VALUES(111, 'NITA', 'COMP',81, 2014)";
stmt.executeUpdate(sql);
System.out.println("Inserted records into the table...");
break;
case 3:
// update
stmt = conn.createStatement();
String sql1 = "CREATE VIEW RD1 AS SELECT"+" name,per"+" FROM INFO ";
System.out.println("VIEW CREATED");
stmt.executeUpdate(sql1);
// Now you can extract all the records
// to see the updated records
sql1 = "SELECT * FROM RD1";
ResultSet rs = stmt.executeQuery(sql1);
while(rs.next()){
//Retrieve by column name
//int id = rs.getInt("id");
int per = rs.getInt("PER");
String name = rs.getString("NAME");
//String dep = rs.getString("DEP");
//int year= rs.getInt("year");
//Display values
//System.out.print("ID: " + id);
System.out.print(", per: " + per);
System.out.print(", name: " + name);
//System.out.println(",dep: " + dep);
// System.out.println (",year:"+ year);
}
rs.close();
break;
case 4:
//delete
sql = "CREATE INDEX ROLLNO1 ON INFO(id)";
System.out.println("INDEX CREATED");
stmt.executeUpdate(sql);
// Now you can extract all the records
// to see the remaining records
sql = "SELECT id, NAME, DEP, PER,YEAR FROM INFO";
ResultSet rs1 = stmt.executeQuery(sql);
while(rs1.next()){
//Retrieve by column name
int id = rs1.getInt("id");
int PER = rs1.getInt("PER");
String NAME = rs1.getString("NAME");
String DEP = rs1.getString("DEP");
int YEAR= rs1.getInt("YEAR");
//Display values
System.out.print("ID: " + id);
System.out.print(", PER: " + PER);
System.out.print(", NAME: " + NAME);
System.out.println(", DEP: " + DEP);
System.out.println(", YEAR: " + YEAR);
}
rs1.close();
break;
case 5:
//Select
sql = "SELECT id, NAME,DEP, PER,YEAR FROM INFO";
rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int PER = rs.getInt("PER");
String NAME = rs.getString("NAME");
String DEP = rs.getString("DEP");
int YEAR= rs.getInt("YEAR");
//Display values
System.out.print("ID: " + id);
System.out.print(", PER: " + PER);
System.out.print(", NAME: " + NAME);
System.out.println(", DEP: " + DEP);
System.out.println(", YEAR: " + YEAR);
}
rs.close();
break;
case 0:
quit = true;
break;
default:
System.out.println("Wrong choice.");
break;
}
System.out.println();
} while (!quit);
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
}
EXP4
Title : Implement Basic command of sql using three tier
architecture.
--%>
<%@page import = "java.sql.*" %>
<%@page import = "java.io.*" %>
<%@page import = "java.text.*" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<%
// JDBC driver name and database URL
String JDBC_DRIVER = "com.mysql.jdbc.Driver";
String DB_URL = "jdbc:mysql://localhost/test?";//java database connectivity
// Database credentials
String USER = "root";
String PASS = "";
Connection conn = null;
PreparedStatement ps=null;
Statement stmt = null;
ResultSet rs = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("Hi");
//STEP 4: Execute a query
stmt = conn.createStatement();
String sql;
if(request.getParameter("J1")!= null)
{
System.out.println("Hi1");
sql = "select * from student";
rs = stmt.executeQuery(sql);
//STEP 5: Extrata result set
%>
<center>
<h1>SHOW VALUES</h1>
<table title="SHOW VALUE" cellpadding="1" cellspacing="1" border="true">
<tr>
<td> Roll_no </td>
<td> name </td>
<td> mob_no </td>
</tr>
<%
while(rs.next()){
//Retrieve by column name
long R_no = rs.getInt(1);
String S_name= rs.getString(2);
int M_no = rs.getInt(3);
//Display values
%>
<tr>
<td> <%=R_no%> </td>
<td> <%=S_name%> </td>
<td> <%=M_no%> </td>
</tr>
<%
}
%>
</table>
</center>
<%
}
else if(request.getParameter("J2") != null)
{
String name=request.getParameter("n1");
String roll=request.getParameter("r1");
int r=Integer.parseInt(roll);
String mno=request.getParameter("m1");
int m=Integer.parseInt(mno);
sql="Insert Into student"+"(roll_no,name,mob_no)"+" values('"+r+"','"+name+"',"+m+")";
int rs1=stmt.executeUpdate(sql);
sql = "select * from student";
rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
%>
<center>
<h1>INSERT VALUE</h1>
<table title="Value Inserted" cellpadding="1" cellspacing="1" border="true">
<tr>
<td> Roll_no </td>
<td> name </td>
<td> mob_no </td>
</tr>
<%
while(rs.next()){
//Retrieve by column name
long R_no = rs.getInt(1);
String S_name= rs.getString(2);
int M_no = rs.getInt(3);
//Display values
%>
<tr>
<td> <%=R_no%> </td>
<td> <%=S_name%> </td>
<td> <%=M_no%> </td>
</tr>
<%
}
%>
</table>
</center>
<%
}
else if(request.getParameter("J3") != null)
{
String roll=request.getParameter("r2");
int rnm=Integer.parseInt(roll);
String namee=request.getParameter("n2");
String mnoo=request.getParameter("m2");
int mm=Integer.parseInt(mnoo);
ps=conn.prepareStatement("Update student set name=?,mob_no=? where roll_no=?");
ps.setString(1, namee);
ps.setInt(2, mm);
ps.setInt(3, rnm);
ps.executeUpdate();
sql = "select * from student";
rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
%>
<center>
<h1>UPDATE VALUE</h1>
<table title="Value Inserted" cellpadding="1" cellspacing="1" border="true">
<tr>
<td> Roll_no </td>
<td> name </td>
<td> mob_no </td>
</tr>
<%
while(rs.next()){
//Retrieve by column name
long R_no = rs.getInt(1);
String S_name= rs.getString(2);
int M_no = rs.getInt(3);
//Display values
%>
<tr>
<td> <%=R_no%> </td>
<td> <%=S_name%> </td>
<td> <%=M_no%> </td>
</tr>
<%
}
%>
</table>
</center>
<%
}
else
{
String roll3=request.getParameter("r3");
int rnm1=Integer.parseInt(roll3);
sql="Delete from student where roll_no="+rnm1+"";
int rs2=stmt.executeUpdate(sql);
sql = "select * from student";
rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
%>
<center>
<h1>DELETE VALUE</h1>
<table title="Value Deleted" cellpadding="1" cellspacing="1" border="true">
<tr>
<td> Roll_no </td>
<td> name </td>
<td> mob_no </td>
</tr>
<%
while(rs.next()){
//Retrieve by column name
long R_no = rs.getInt(1);
String S_name= rs.getString(2);
int M_no = rs.getInt(3);
//Display values
%>
<tr>
<td> <%=R_no%> </td>
<td> <%=S_name%> </td>
<td> <%=M_no%> </td>
</tr>
<%
}
%>
</table>
</center>
<%
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close();
}
catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}
catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
%>
</body>
</html>
EXP5
Title : CRUD opration of mongodb
import com.mongodb.*;
public class Conn {
public static void main( String args[] ){
try
{
// To connect to mongodb server
MongoClient mongoClient = new MongoClient( "localhost" , 27017 );
// Now connect to your databases
DB db = mongoClient.getDB( "local" );
System.out.println("Connected to database successfully");
//--------------create---------
DBCollection coll = db.createCollection("mycol",null);
System.out.println("Collection created successfully");
//---------------insert-------------
DBCollection col1 = db.getCollection("mycol");
System.out.println("Collection mycol selected successfully");
BasicDBObject doc1 = new BasicDBObject("title", "MongoDB").append("clg", "SKN").append("dept", "COMP");
BasicDBObject doc2 = new BasicDBObject("title", "MongoDB").append("clg", "SKN").append("dept", "ENTC");
BasicDBObject doc3 = new BasicDBObject("title", "MongoDB").append("clg", "SKNSIT").append("dept", "ELECTRICAL");
BasicDBObject doc4 = new BasicDBObject("title", "MongoDB").append("clg", "SKNSIT").append("dept", "IT");
col1.insert(doc1);
col1.insert(doc2);
col1.insert(doc3);
col1.insert(doc4);
System.out.println("Values Inserted....");
//-----------------Display----------------
DBCursor cursor = col1.find();
int i=1;
while (cursor.hasNext()) {
System.out.println("Inserted Document: "+i);
System.out.println(cursor.next());
i++;
}
//-----------------------Delete------------------------
DBObject myDoc = col1.findOne();
col1.remove(myDoc);
DBCursor cursor1 = col1.find();
int j=1;
while (cursor1.hasNext()) {
System.out.println("Inserted Document: "+j);
System.out.println(cursor1.next());
j++;
}
System.out.println("Document deleted successfully");
}
catch(Exception e)
{
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
}
}
}
EXP6
Title : MapRuduce in mongodb
[admi@172-15-1-104 ~]$ mongo
MongoDB shell version: 2.4.6
connecting to: test
> db.createCollection('bank')
{ "ok" : 1 }
> db.bank.insert({cutid:101,cutname:'payal',branchname:'sbi',bal:300000,location:'r.nagar'})
> db.bank.insert({cutid:102,cutname:'sam',branchname:'boi',bal:3000,location:'a.nagar'})
> db.bank.insert({cutid:103,cutname:'om',branchname:'axis',bal:50000,location:'a.bad'})
> db.bank.insert({cutid:104,cutname:'avni',branchname:'hdfc',bal:700,location:'pune'})
> db.bank.insert({cutid:104,cutname:'avni',branchname:'hdfc',bal:79000,location:'pune'})
> db.bank.insert({cutid:104,cutname:'avni',branchname:'hdfc',bal:00,location:'pune'})
> db.bank.insert({cutid:103,cutname:'om',branchname:'axis',bal:50000,location:'himachal'})
> db.bank.insert({cutid:103,cutname:'om',branchname:'axis',bal:50000,location:'pune'})
> db.bank.find().pretty()
{
"_id" : ObjectId("55de99c80f0881b9f4a95e8b"),
"cutid" : 101,
"cutname" : "payal",
"branchname" : "sbi",
"bal" : 300000,
"location" : "r.nagar"
}
{
"_id" : ObjectId("55de99fd0f0881b9f4a95e8c"),
"cutid" : 102,
"cutname" : "sam",
"branchname" : "boi",
"bal" : 3000,
"location" : "a.nagar"
}
{
"_id" : ObjectId("55de9a290f0881b9f4a95e8d"),
"cutid" : 103,
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "a.bad"
}
{
"_id" : ObjectId("55de9a660f0881b9f4a95e8e"),
"cutid" : 104,
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 700,
"location" : "pune"
}
{
"_id" : ObjectId("55de9a980f0881b9f4a95e8f"),
"cutid" : 104,
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 79000,
"location" : "pune"
}
{
"_id" : ObjectId("55de9aa40f0881b9f4a95e90"),
"cutid" : 104,
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 0,
"location" : "pune"
}
{
"_id" : ObjectId("55de9abf0f0881b9f4a95e91"),
"cutid" : 103,
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "himachal"
}
{
"_id" : ObjectId("55de9ace0f0881b9f4a95e92"),
"cutid" : 103,
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "pune"
}
> db.bank.mapReduce(function(){emit(this.branchname,this.bal)},function(key,values){return Array.sum(values)},{'out':'bankres'})
{
"result" : "bankres",
"timeMillis" : 85,
"counts" : {
"input" : 8,
"emit" : 8,
"reduce" : 2,
"output" : 4
},
"ok" : 1,
}
> db.bankres.find()
{ "_id" : "axis", "value" : 150000 }
{ "_id" : "boi", "value" : 3000 }
{ "_id" : "hdfc", "value" : 79700 }
{ "_id" : "sbi", "value" : 300000 }
> db.bank.insert({cutid:107,cutname:'hritik',branchname:'rsb',bal:5,location:'shreenagar'})
> db.bank.mapReduce(function(){emit(this.branchname,this.bal)},function(key,values){return Array.sum(values)},{'out':'bankres'})
{
"result" : "bankres",
"timeMillis" : 4,
"counts" : {
"input" : 9,
"emit" : 9,
"reduce" : 2,
"output" : 5
},
"ok" : 1,
}
> db.bankres.find()
{ "_id" : "axis", "value" : 150000 }
{ "_id" : "boi", "value" : 3000 }
{ "_id" : "hdfc", "value" : 79700 }
{ "_id" : "rsb", "value" : 5 }
{ "_id" : "sbi", "value" : 300000 }
> db.bank.insert({cutid:107,cutname:'hritik',branchname:'rsb',bal:5,location:'shreenagar'})
> db.bank.mapReduce(function(){emit(this.branchname,this.bal)},function(key,values){return Array.sum(values)},{'out':'bankres'})
{
"result" : "bankres",
"timeMillis" : 33,
"counts" : {
"input" : 10,
"emit" : 10,
"reduce" : 3,
"output" : 5
},
"ok" : 1,
}
> db.bankres.find()
{ "_id" : "axis", "value" : 150000 }
{ "_id" : "boi", "value" : 3000 }
{ "_id" : "hdfc", "value" : 79700 }
{ "_id" : "rsb", "value" : 10 }
{ "_id" : "sbi", "value" : 300000 }
> db.bank.mapReduce(function(){emit(this.branchname,this.bal)},function(key,values){return Array.avg(values)},{'out':'bankres'})
{
"result" : "bankres",
"timeMillis" : 5,
"counts" : {
"input" : 10,
"emit" : 10,
"reduce" : 3,
"output" : 5
},
"ok" : 1,
}
> db.bankres.find()
{ "_id" : "axis", "value" : 50000 }
{ "_id" : "boi", "value" : 3000 }
{ "_id" : "hdfc", "value" : 26566.666666666668 }
{ "_id" : "rsb", "value" : 5 }
{ "_id" : "sbi", "value" : 300000 }
> db.bank.insert({cutid:107,cutname:'hritik'})
> db.bank.mapReduce(function(){emit(this.branchname,this.bal)},function(key,values){return Array.sum(values)},{'out':'bankres'})
{
"result" : "bankres",
"timeMillis" : 150,
"counts" : {
"input" : 11,
"emit" : 11,
"reduce" : 3,
"output" : 6
},
"ok" : 1,
}
> db.bankres.find()
{ "_id" : null, "value" : null }
{ "_id" : "axis", "value" : 150000 }
{ "_id" : "boi", "value" : 3000 }
{ "_id" : "hdfc", "value" : 79700 }
{ "_id" : "rsb", "value" : 10 }
{ "_id" : "sbi", "value" : 300000 }
> db.bank.find({cutid:1,bal:0}).limit(2)
> db.bank.find().limit(2)
{ "_id" : ObjectId("55de99c80f0881b9f4a95e8b"), "cutid" : 101, "cutname" : "payal", "branchname" : "sbi", "bal" : 300000, "location" : "r.nagar" }
{ "_id" : ObjectId("55de99fd0f0881b9f4a95e8c"), "cutid" : 102, "cutname" : "sam", "branchname" : "boi", "bal" : 3000, "location" : "a.nagar" }
> db.bank.find().pretty().limit(2)
{
"_id" : ObjectId("55de99c80f0881b9f4a95e8b"),
"cutid" : 101,
"cutname" : "payal",
"branchname" : "sbi",
"bal" : 300000,
"location" : "r.nagar"
}
{
"_id" : ObjectId("55de99fd0f0881b9f4a95e8c"),
"cutid" : 102,
"cutname" : "sam",
"branchname" : "boi",
"bal" : 3000,
"location" : "a.nagar"
}
> db.bank.find({},{_id:0}).pretty().limit(2)
{
"cutid" : 101,
"cutname" : "payal",
"branchname" : "sbi",
"bal" : 300000,
"location" : "r.nagar"
}
{
"cutid" : 102,
"cutname" : "sam",
"branchname" : "boi",
"bal" : 3000,
"location" : "a.nagar"
}
> db.bank.find({},{_id:0,cutid:0}).pretty().limit(2)
{
"cutname" : "payal",
"branchname" : "sbi",
"bal" : 300000,
"location" : "r.nagar"
}
{
"cutname" : "sam",
"branchname" : "boi",
"bal" : 3000,
"location" : "a.nagar"
}
> db.bank.find({},{_id:0,cutid:0}).pretty().limit(9)
{
"cutname" : "payal",
"branchname" : "sbi",
"bal" : 300000,
"location" : "r.nagar"
}
{
"cutname" : "sam",
"branchname" : "boi",
"bal" : 3000,
"location" : "a.nagar"
}
{
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "a.bad"
}
{
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 700,
"location" : "pune"
}
{
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 79000,
"location" : "pune"
}
{
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 0,
"location" : "pune"
}
{
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "himachal"
}
{
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "pune"
}
{
"cutname" : "hritik",
"branchname" : "rsb",
"bal" : 5,
"location" : "shreenagar"
}
> db.bank.find({},{_id:0,cutid:0}).pretty().limit(9).skip(2)
{
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "a.bad"
}
{
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 700,
"location" : "pune"
}
{
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 79000,
"location" : "pune"
}
{
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 0,
"location" : "pune"
}
{
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "himachal"
}
{
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "pune"
}
{
"cutname" : "hritik",
"branchname" : "rsb",
"bal" : 5,
"location" : "shreenagar"
}
{
"cutname" : "hritik",
"branchname" : "rsb",
"bal" : 5,
"location" : "shreenagar"
}
{ "cutname" : "hritik" }
>
EXP7
Title : Implement Indeximg& Querying.
Indeximg-
>db.employee.ensureIndex({'cust_id':1,'amount':1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
>db.employee.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "mongodb.employee"
},
{
"v" : 1,
"key" : {
"cust_id" : 1,
"amount" : 1
},
"name" : "cust_id_1_amount_1",
"ns" : "mongodb.employee"
},
{
"v" : 1,
"key" : {
"cust_id" : 1,
"status" : 1
},
"name" : "cust_id_1_status_1",
"ns" : "mongodb.employee"
}
]
>db.employee.dropIndex({'cust_id':1,'amount':1})
{ "nIndexesWas" : 3, "ok" : 1 }
>db.employee.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "mongodb.employee"
},
{
"v" : 1,
"key" : {
"cust_id" : 1,
"status" : 1
},
"name" : "cust_id_1_status_1",
"ns" : "mongodb.employee"
}
]
Querying-
>db.student20.update({'name':'sachin'},{$set:{'name':'shashir'}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
>db.student20.find().pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit",
"addr" : "Loni"
}
{
"_id" :ObjectId("53c75c1cf4a21581aced6d2f"),
"roll_no" : 1,
"name" : "shashir",
"addr" : {
"At" : "Loni",
"Tal" : "Rahata",
"Dist" : "Ahemadnagar"
}
}
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "sachin",
"percent_marks" : 60,
"addr" : "Pune"
}
{
"_id" :ObjectId("53c75c9bf4a21581aced6d31"),
"name" : "Rahul",
"Address" : "Kolhar"
}
{ "_id" : 1, "roll_no" : 1 }
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 4, "roll_no" : 4 }
{ "_id" : 5, "roll_no" : 5 }
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
{ "_id" : 35, "roll_no" : 15, "name" : "xyz", "addr" : "Abad" }
>db.student20.update({'name':'sachin'},{$set:{'name':'shashir'}},{multi:true})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
>db.student20.find().pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit",
"addr" : "Loni"
}
{
"_id" :ObjectId("53c75c1cf4a21581aced6d2f"),
"roll_no" : 1,
"name" : "shashir",
"addr" : {
"At" : "Loni",
"Tal" : "Rahata",
"Dist" : "Ahemadnagar"
}
}
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "shashir",
"percent_marks" : 60,
"addr" : "Pune"
}
{
"_id" :ObjectId("53c75c9bf4a21581aced6d31"),
"name" : "Rahul",
"Address" : "Kolhar"
}
{ "_id" : 1, "roll_no" : 1 }
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 4, "roll_no" : 4 }
{ "_id" : 5, "roll_no" : 5 }
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
{ "_id" : 35, "roll_no" : 15, "name" : "xyz", "addr" : "Abad" }
>db.student20.find().limit(4)
{ "_id" : ObjectId("53c75bd3f4a21581aced6d2e"), "roll_no" : 1, "name" : "amit", "addr" : "Loni" }
{ "_id" : ObjectId("53c75c1cf4a21581aced6d2f"), "roll_no" : 1, "name" : "shashir", "addr" : { "At" : "Loni", "Tal" : "Rahata", "Dist" : "Ahemadnagar" } }
{ "_id" : ObjectId("53c75c51f4a21581aced6d30"), "roll_no" : 101, "name" : "shashir", "percent_marks" : 60, "addr" : "Pune" }
{ "_id" : ObjectId("53c75c9bf4a21581aced6d31"), "name" : "Rahul", "Address" : "Kolhar" }
>db.student20.find().skip(2).pretty()
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "shashir",
"percent_marks" : 60,
"addr" : "Pune"
}
{
"_id" :ObjectId("53c75c9bf4a21581aced6d31"),
"name" : "Rahul",
"Address" : "Kolhar"
}
{ "_id" : 1, "roll_no" : 1 }
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 4, "roll_no" : 4 }
{ "_id" : 5, "roll_no" : 5 }
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
{ "_id" : 35, "roll_no" : 15, "name" : "xyz", "addr" : "Abad" }
>db.student20.find({$and:[{'roll_no':{$gt:5}},{'roll_no':{$lt:110}}]}).pretty()
{>db.student20.find({},{'roll_no':1,'name':1}).pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit"
}
{ "_id" : ObjectId("53c75c9bf4a21581aced6d31"), "name" : "Rahul" }
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 4, "roll_no" : 4 }
{ "_id" : 5, "roll_no" : 5 }
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
{ "_id" : 35, "roll_no" : 15, "name" : "xyz" }
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "sachin",
"percent_marks" : 60,
"addr" : "Pune"
}
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
>db.student20.find({$or:[{'addr':'Loni'},{'addr':'Pune'}]}).pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit",
"addr" : "Loni"
}
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "sachin",
"percent_marks" : 60,
"addr" : "Pune"
}
>db.student20.find({'name':{$in:['sachin']}}).pretty()
{
"_id" :ObjectId("53c75c1cf4a21581aced6d2f"),
"roll_no" : 1,
"name" : "sachin",
"addr" : {
"At" : "Loni",
"Tal" : "Rahata",
"Dist" : "Ahemadnagar"
}
}
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "sachin",
"percent_marks" : 60,
"addr" : "Pune"
}
>db.student20.find({'name':{$nin:['sachin']}}).pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit",
"addr" : "Loni"
}
{
"_id" :ObjectId("53c75c9bf4a21581aced6d31"),
"name" : "Rahul",
"Address" : "Kolhar"
}
{ "_id" : 1, "roll_no" : 1 }
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 4, "roll_no" : 4 }
{ "_id" : 5, "roll_no" : 5 }
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
>db.student20.find({'name':'sachin'}).pretty()
{
"_id" :ObjectId("53c75c1cf4a21581aced6d2f"),
"roll_no" : 1,
"name" : "sachin",
"addr" : {
"At" : "Loni",
"Tal" : "Rahata",
"Dist" : "Ahemadnagar"
}
}
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "sachin",
"percent_marks" : 60,
"addr" : "Pune"
}
>db.student20.find({'roll_no':{$lt:50}}).pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit",
"addr" : "Loni"
}
{
"_id" :ObjectId("53c75c1cf4a21581aced6d2f"),
"roll_no" : 1,
"name" : "sachin",
"addr" : {
"At" : "Loni",
"Tal" : "Rahata",
"Dist" : "Ahemadnagar"
}
}
{ "_id" : 1, "roll_no" : 1 }
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 4, "roll_no" : 4 }
{ "_id" : 5, "roll_no" : 5 }
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
>db.student20.find({'roll_no':{$gt:50}}).pretty()
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "sachin",
"percent_marks" : 60,
"addr" : "Pune"
}
>db.student20.find({'roll_no':{$ne:50}}).pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit",
"addr" : "Loni"
}
{
"_id" :ObjectId("53c75c1cf4a21581aced6d2f"),
"roll_no" : 1,
"name" : "sachin",
"addr" : {
"At" : "Loni",
"Tal" : "Rahata",
"Dist" : "Ahemadnagar"
}
}
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "sachin",
"percent_marks" : 60,
"addr" : "Pune"
}
{
"_id" :ObjectId("53c75c9bf4a21581aced6d31"),
"name" : "Rahul",
"Address" : "Kolhar"
}
{ "_id" : 1, "roll_no" : 1 }
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 4, "roll_no" : 4 }
{ "_id" : 5, "roll_no" : 5 }
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
>db.student20.remove({'roll_no':{$gt:8}},true)
WriteResult({ "nRemoved" : 1 })
>db.student20.find().pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit",
"addr" : "Loni"
}
{
"_id" :ObjectId("53c75c9bf4a21581aced6d31"),
"name" : "Rahul",
"Address" : "Kolhar"
}
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 10, "roll_no" : 10 }
{ "_id" : 35, "roll_no" : 15, "name" : "xyz", "addr" : "Abad" }
>db.student20.remove({$and:[{'roll_no':{$gt:3}},{'roll_no':{$lt:8}}]})
WriteResult({ "nRemoved" : 3 })
>db.student20.find().pretty()
EXP8
Title : Database connectivity program in java with mongodb.
import com.mongodb.*;
public class Primary {
public static void main( String args[] ){
try
{ // To connect to mongodb server
MongoClient mongoClient = new MongoClient( "localhost" , 27018 );
// Now connect to your databases
DB db = mongoClient.getDB( "local" );
System.out.println("Connected to database successfully.");
}
catch(Exception e)
{ System.err.println( e.getClass().getName() + ": " + e.getMessage() );
}
}
}
Title : Connection between client & data using two tier.
package javaapplication3;
import java.sql.*;
import java.util.Scanner;
public class JavaApplication3 {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/test";
// Database credentials
static final String USER = "root";
static final String PASS = "";
public static void main(String[] args) {
Scanner in = new Scanner(System.in);
int userChoice;
boolean quit = false;
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
System.out.println("Creating table in given database...");
stmt = conn.createStatement();
//STEP 4: Execute a query
do {
System.out.println("1. create table");
System.out.println("2.Insert");
System.out.println("3. update");
System.out.println("4 delete");
System.out.print("5. select");
userChoice = in.nextInt();
switch (userChoice) {
case 1:
String sql = "CREATE TABLE REGISTRATION3 " +
"(id INTEGER not NULL, " +
" first VARCHAR(255), " +
" last VARCHAR(255), " +
" age INTEGER, " +
" PRIMARY KEY ( id ))";
stmt.executeUpdate(sql);
System.out.println("Created table in given database...");
break;
case 2:
//
stmt = conn.createStatement();
sql = "INSERT INTO Registration3 " +
"VALUES (100, 'Zara', 'Ali', 18)";
stmt.executeUpdate(sql);
sql = "INSERT INTO Registration3 " +
"VALUES (101, 'Mahnaz', 'Fatma', 25)";
stmt.executeUpdate(sql);
sql = "INSERT INTO Registration3 " +
"VALUES (102, 'Zaid', 'Khan', 30)";
stmt.executeUpdate(sql);
sql = "INSERT INTO Registration3 " +
"VALUES(103, 'Sumit', 'Mittal', 28)";
stmt.executeUpdate(sql);
System.out.println("Inserted records into the table...");
break;
case 3:
// update
stmt = conn.createStatement();
String sql1 = "UPDATE Registration3 " +
"SET age = 30 WHERE id in (100, 101)";
stmt.executeUpdate(sql1);
// Now you can extract all the records
// to see the updated records
sql1 = "SELECT id, first, last, age FROM Registration3";
ResultSet rs = stmt.executeQuery(sql1);
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
rs.close();
break;
case 4:
//delete
sql = "DELETE FROM Registration3 " +
"WHERE id = 101";
stmt.executeUpdate(sql);
// Now you can extract all the records
// to see the remaining records
sql = "SELECT id, first, last, age FROM Registration3";
ResultSet rs1 = stmt.executeQuery(sql);
while(rs1.next()){
//Retrieve by column name
int id = rs1.getInt("id");
int age = rs1.getInt("age");
String first = rs1.getString("first");
String last = rs1.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
rs1.close();
break;
case 5:
//Select
sql = "SELECT id, first, last, age FROM Registration";
rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
rs.close();
break;
case 0:
quit = true;
break;
default:
System.out.println("Wrong choice.");
break;
}
System.out.println();
} while (!quit);
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
}//end JDBCExample
EXP2
Title : Three tier architecture using mysql to implement
joining in two table.
--%>
<%--
--%>
<%@page import = "java.sql.*" %>
<%@page import = "java.io.*" %>
<%@page import = "java.text.*" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<%
// JDBC driver name and database URL
String JDBC_DRIVER = "com.mysql.jdbc.Driver";
String DB_URL = "jdbc:mysql://localhost/test?";//java database connectivity
// Database credentials
String USER = "root";
String PASS = "";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("Hi");
//STEP 4: Execute a query
stmt = conn.createStatement();
String sql;
if(request.getParameter("J1")!= null)
{
System.out.println("Hi1");
sql = "select * from emp INNER JOIN dept on emp.deptno=dept.deptno";
rs = stmt.executeQuery(sql);
//STEP 5: Extrata result set
%>
<center>
<h1>INNER JOIN</h1>
<table title="INNER JOIN" cellpadding="1" cellspacing="1" border="true">
<tr>
<td> eno </td>
<td> ename </td>
<td> deptno </td>
<td> deptno </td>
<td> dname </td>
<td> loc </td>
</tr>
<%
while(rs.next()){
//Retrieve by column name
long emp_id = rs.getInt(1);
String E_name= rs.getString(2);
String E_dept = rs.getString(3);
int D_code = rs.getInt(4);
String D_name = rs.getString(5);
String L = rs.getString(6);
//Display values
%>
<tr>
<td> <%=emp_id%> </td>
<td> <%=E_name%> </td>
<td> <%=E_dept%> </td>
<td> <%=D_code%> </td>
<td> <%=D_name%> </td>
<td> <%=L%> </td>
</tr>
<%
}
%>
</table>
</center>
<%
}
else if(request.getParameter("J2") != null)
{
sql = "select * from emp LEFT JOIN dept on emp.deptno=dept.deptno";
rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
%>
<center>
<h1>LEFT JOIN</h1>
<table title="LEFT JOIN" cellpadding="1" cellspacing="1" border="true">
<tr>
<td> eno </td>
<td> ename </td>
<td> deptno </td>
<td> deptno </td>
<td> dname </td>
<td> loc </td>
</tr>
<%
while(rs.next()){
long emp_id = rs.getInt(1);
String E_name= rs.getString(2);
String E_dept = rs.getString(3);
int D_code = rs.getInt(4);
String D_name = rs.getString(5);
String L = rs.getString(6);
//Retrieve by column name
%>
<tr>
<td> <%=emp_id%> </td>
<td> <%=E_name%> </td>
<td> <%=E_dept%> </td>
<td> <%=D_code%> </td>
<td> <%=D_name%> </td>
<td> <%=L%> </td>
</tr>
<%
}
%>
</table>
</center>
<%
}
else
{
sql = "select * from emp RIGHT JOIN dept on emp.deptno=dept.deptno";
rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
%>
<center>
<h1>RIGHT JOIN</h1>
<table title="RIGHT JOIN" cellpadding="1" cellspacing="1" border="true">
<tr>
<td> eno </td>
<td> ename </td>
<td> deptno </td>
<td> deptno </td>
<td> dname </td>
<td> loc </td>
</tr>
<%
while(rs.next()){
//Retrieve by column name
long emp_id = rs.getInt(1);
String E_name= rs.getString(2);
String E_dept = rs.getString(3);
int D_code = rs.getInt(4);
String D_name = rs.getString(5);
String L = rs.getString(6);
//Display values
%>
<tr>
<td> <%=emp_id%> </td>
<td> <%=E_name%> </td>
<td> <%=E_dept%> </td>
<td> <%=D_code%> </td>
<td> <%=D_name%> </td>
<td> <%=L%> </td>
</tr>
<%
}
%>
</table>
</center>
<%
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close();
}
catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}
catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
%>
</body>
</html>
EXP3
Title : Implement sql DDL statement using two tier architecture.
package pract1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class abc {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://172.15.2.94/test";
// Database credentials
static final String USER = "comp4";
static final String PASS = "comp4";
public static void main(String[] args) {
Scanner in = new Scanner(System.in);
int userChoice;
boolean quit = false;
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
System.out.println("Creating table in given database...");
stmt = conn.createStatement();
//STEP 4: Execute a query
do {
System.out.println("1. create table");
System.out.println("2.Insert");
System.out.println("3.view");
System.out.println("4.index");
System.out.print("5. select");
userChoice = in.nextInt();
switch (userChoice) {
case 1:
String sql = "CREATE TABLE INFO " +
"(id INTEGER not NULL, " +
" name VARCHAR(20), " +
" dep VARCHAR(20), " +
"per integer,"+
"year integer,"+
" PRIMARY KEY ( id ))";
stmt.executeUpdate(sql);
System.out.println("Created table in given database...");
break;
case 2:
//
stmt = conn.createStatement();
sql = "INSERT INTO INFO " +
"VALUES (101, 'PAYAL', 'COMP', 75,2015)";
stmt.executeUpdate(sql);
sql = "INSERT INTO INFO " +
"VALUES (102, 'GAURI', 'E&Tc', 72,2015)";
stmt.executeUpdate(sql);
//sql = "INSERT INTO INFO" +
// "VALUES (103, 'SUJIT', 'MECH',69, 2015)";
//stmt.executeUpdate(sql);
sql = "INSERT INTO INFO " +
"VALUES(110, 'AKASH', 'MECH',80, 2015)";
stmt.executeUpdate(sql);
sql = "INSERT INTO INFO " +
"VALUES(111, 'NITA', 'COMP',81, 2014)";
stmt.executeUpdate(sql);
System.out.println("Inserted records into the table...");
break;
case 3:
// update
stmt = conn.createStatement();
String sql1 = "CREATE VIEW RD1 AS SELECT"+" name,per"+" FROM INFO ";
System.out.println("VIEW CREATED");
stmt.executeUpdate(sql1);
// Now you can extract all the records
// to see the updated records
sql1 = "SELECT * FROM RD1";
ResultSet rs = stmt.executeQuery(sql1);
while(rs.next()){
//Retrieve by column name
//int id = rs.getInt("id");
int per = rs.getInt("PER");
String name = rs.getString("NAME");
//String dep = rs.getString("DEP");
//int year= rs.getInt("year");
//Display values
//System.out.print("ID: " + id);
System.out.print(", per: " + per);
System.out.print(", name: " + name);
//System.out.println(",dep: " + dep);
// System.out.println (",year:"+ year);
}
rs.close();
break;
case 4:
//delete
sql = "CREATE INDEX ROLLNO1 ON INFO(id)";
System.out.println("INDEX CREATED");
stmt.executeUpdate(sql);
// Now you can extract all the records
// to see the remaining records
sql = "SELECT id, NAME, DEP, PER,YEAR FROM INFO";
ResultSet rs1 = stmt.executeQuery(sql);
while(rs1.next()){
//Retrieve by column name
int id = rs1.getInt("id");
int PER = rs1.getInt("PER");
String NAME = rs1.getString("NAME");
String DEP = rs1.getString("DEP");
int YEAR= rs1.getInt("YEAR");
//Display values
System.out.print("ID: " + id);
System.out.print(", PER: " + PER);
System.out.print(", NAME: " + NAME);
System.out.println(", DEP: " + DEP);
System.out.println(", YEAR: " + YEAR);
}
rs1.close();
break;
case 5:
//Select
sql = "SELECT id, NAME,DEP, PER,YEAR FROM INFO";
rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int PER = rs.getInt("PER");
String NAME = rs.getString("NAME");
String DEP = rs.getString("DEP");
int YEAR= rs.getInt("YEAR");
//Display values
System.out.print("ID: " + id);
System.out.print(", PER: " + PER);
System.out.print(", NAME: " + NAME);
System.out.println(", DEP: " + DEP);
System.out.println(", YEAR: " + YEAR);
}
rs.close();
break;
case 0:
quit = true;
break;
default:
System.out.println("Wrong choice.");
break;
}
System.out.println();
} while (!quit);
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
}
EXP4
Title : Implement Basic command of sql using three tier
architecture.
--%>
<%@page import = "java.sql.*" %>
<%@page import = "java.io.*" %>
<%@page import = "java.text.*" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<%
// JDBC driver name and database URL
String JDBC_DRIVER = "com.mysql.jdbc.Driver";
String DB_URL = "jdbc:mysql://localhost/test?";//java database connectivity
// Database credentials
String USER = "root";
String PASS = "";
Connection conn = null;
PreparedStatement ps=null;
Statement stmt = null;
ResultSet rs = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("Hi");
//STEP 4: Execute a query
stmt = conn.createStatement();
String sql;
if(request.getParameter("J1")!= null)
{
System.out.println("Hi1");
sql = "select * from student";
rs = stmt.executeQuery(sql);
//STEP 5: Extrata result set
%>
<center>
<h1>SHOW VALUES</h1>
<table title="SHOW VALUE" cellpadding="1" cellspacing="1" border="true">
<tr>
<td> Roll_no </td>
<td> name </td>
<td> mob_no </td>
</tr>
<%
while(rs.next()){
//Retrieve by column name
long R_no = rs.getInt(1);
String S_name= rs.getString(2);
int M_no = rs.getInt(3);
//Display values
%>
<tr>
<td> <%=R_no%> </td>
<td> <%=S_name%> </td>
<td> <%=M_no%> </td>
</tr>
<%
}
%>
</table>
</center>
<%
}
else if(request.getParameter("J2") != null)
{
String name=request.getParameter("n1");
String roll=request.getParameter("r1");
int r=Integer.parseInt(roll);
String mno=request.getParameter("m1");
int m=Integer.parseInt(mno);
sql="Insert Into student"+"(roll_no,name,mob_no)"+" values('"+r+"','"+name+"',"+m+")";
int rs1=stmt.executeUpdate(sql);
sql = "select * from student";
rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
%>
<center>
<h1>INSERT VALUE</h1>
<table title="Value Inserted" cellpadding="1" cellspacing="1" border="true">
<tr>
<td> Roll_no </td>
<td> name </td>
<td> mob_no </td>
</tr>
<%
while(rs.next()){
//Retrieve by column name
long R_no = rs.getInt(1);
String S_name= rs.getString(2);
int M_no = rs.getInt(3);
//Display values
%>
<tr>
<td> <%=R_no%> </td>
<td> <%=S_name%> </td>
<td> <%=M_no%> </td>
</tr>
<%
}
%>
</table>
</center>
<%
}
else if(request.getParameter("J3") != null)
{
String roll=request.getParameter("r2");
int rnm=Integer.parseInt(roll);
String namee=request.getParameter("n2");
String mnoo=request.getParameter("m2");
int mm=Integer.parseInt(mnoo);
ps=conn.prepareStatement("Update student set name=?,mob_no=? where roll_no=?");
ps.setString(1, namee);
ps.setInt(2, mm);
ps.setInt(3, rnm);
ps.executeUpdate();
sql = "select * from student";
rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
%>
<center>
<h1>UPDATE VALUE</h1>
<table title="Value Inserted" cellpadding="1" cellspacing="1" border="true">
<tr>
<td> Roll_no </td>
<td> name </td>
<td> mob_no </td>
</tr>
<%
while(rs.next()){
//Retrieve by column name
long R_no = rs.getInt(1);
String S_name= rs.getString(2);
int M_no = rs.getInt(3);
//Display values
%>
<tr>
<td> <%=R_no%> </td>
<td> <%=S_name%> </td>
<td> <%=M_no%> </td>
</tr>
<%
}
%>
</table>
</center>
<%
}
else
{
String roll3=request.getParameter("r3");
int rnm1=Integer.parseInt(roll3);
sql="Delete from student where roll_no="+rnm1+"";
int rs2=stmt.executeUpdate(sql);
sql = "select * from student";
rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
%>
<center>
<h1>DELETE VALUE</h1>
<table title="Value Deleted" cellpadding="1" cellspacing="1" border="true">
<tr>
<td> Roll_no </td>
<td> name </td>
<td> mob_no </td>
</tr>
<%
while(rs.next()){
//Retrieve by column name
long R_no = rs.getInt(1);
String S_name= rs.getString(2);
int M_no = rs.getInt(3);
//Display values
%>
<tr>
<td> <%=R_no%> </td>
<td> <%=S_name%> </td>
<td> <%=M_no%> </td>
</tr>
<%
}
%>
</table>
</center>
<%
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close();
}
catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}
catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
%>
</body>
</html>
EXP5
Title : CRUD opration of mongodb
import com.mongodb.*;
public class Conn {
public static void main( String args[] ){
try
{
// To connect to mongodb server
MongoClient mongoClient = new MongoClient( "localhost" , 27017 );
// Now connect to your databases
DB db = mongoClient.getDB( "local" );
System.out.println("Connected to database successfully");
//--------------create---------
DBCollection coll = db.createCollection("mycol",null);
System.out.println("Collection created successfully");
//---------------insert-------------
DBCollection col1 = db.getCollection("mycol");
System.out.println("Collection mycol selected successfully");
BasicDBObject doc1 = new BasicDBObject("title", "MongoDB").append("clg", "SKN").append("dept", "COMP");
BasicDBObject doc2 = new BasicDBObject("title", "MongoDB").append("clg", "SKN").append("dept", "ENTC");
BasicDBObject doc3 = new BasicDBObject("title", "MongoDB").append("clg", "SKNSIT").append("dept", "ELECTRICAL");
BasicDBObject doc4 = new BasicDBObject("title", "MongoDB").append("clg", "SKNSIT").append("dept", "IT");
col1.insert(doc1);
col1.insert(doc2);
col1.insert(doc3);
col1.insert(doc4);
System.out.println("Values Inserted....");
//-----------------Display----------------
DBCursor cursor = col1.find();
int i=1;
while (cursor.hasNext()) {
System.out.println("Inserted Document: "+i);
System.out.println(cursor.next());
i++;
}
//-----------------------Delete------------------------
DBObject myDoc = col1.findOne();
col1.remove(myDoc);
DBCursor cursor1 = col1.find();
int j=1;
while (cursor1.hasNext()) {
System.out.println("Inserted Document: "+j);
System.out.println(cursor1.next());
j++;
}
System.out.println("Document deleted successfully");
}
catch(Exception e)
{
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
}
}
}
EXP6
Title : MapRuduce in mongodb
[admi@172-15-1-104 ~]$ mongo
MongoDB shell version: 2.4.6
connecting to: test
> db.createCollection('bank')
{ "ok" : 1 }
> db.bank.insert({cutid:101,cutname:'payal',branchname:'sbi',bal:300000,location:'r.nagar'})
> db.bank.insert({cutid:102,cutname:'sam',branchname:'boi',bal:3000,location:'a.nagar'})
> db.bank.insert({cutid:103,cutname:'om',branchname:'axis',bal:50000,location:'a.bad'})
> db.bank.insert({cutid:104,cutname:'avni',branchname:'hdfc',bal:700,location:'pune'})
> db.bank.insert({cutid:104,cutname:'avni',branchname:'hdfc',bal:79000,location:'pune'})
> db.bank.insert({cutid:104,cutname:'avni',branchname:'hdfc',bal:00,location:'pune'})
> db.bank.insert({cutid:103,cutname:'om',branchname:'axis',bal:50000,location:'himachal'})
> db.bank.insert({cutid:103,cutname:'om',branchname:'axis',bal:50000,location:'pune'})
> db.bank.find().pretty()
{
"_id" : ObjectId("55de99c80f0881b9f4a95e8b"),
"cutid" : 101,
"cutname" : "payal",
"branchname" : "sbi",
"bal" : 300000,
"location" : "r.nagar"
}
{
"_id" : ObjectId("55de99fd0f0881b9f4a95e8c"),
"cutid" : 102,
"cutname" : "sam",
"branchname" : "boi",
"bal" : 3000,
"location" : "a.nagar"
}
{
"_id" : ObjectId("55de9a290f0881b9f4a95e8d"),
"cutid" : 103,
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "a.bad"
}
{
"_id" : ObjectId("55de9a660f0881b9f4a95e8e"),
"cutid" : 104,
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 700,
"location" : "pune"
}
{
"_id" : ObjectId("55de9a980f0881b9f4a95e8f"),
"cutid" : 104,
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 79000,
"location" : "pune"
}
{
"_id" : ObjectId("55de9aa40f0881b9f4a95e90"),
"cutid" : 104,
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 0,
"location" : "pune"
}
{
"_id" : ObjectId("55de9abf0f0881b9f4a95e91"),
"cutid" : 103,
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "himachal"
}
{
"_id" : ObjectId("55de9ace0f0881b9f4a95e92"),
"cutid" : 103,
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "pune"
}
> db.bank.mapReduce(function(){emit(this.branchname,this.bal)},function(key,values){return Array.sum(values)},{'out':'bankres'})
{
"result" : "bankres",
"timeMillis" : 85,
"counts" : {
"input" : 8,
"emit" : 8,
"reduce" : 2,
"output" : 4
},
"ok" : 1,
}
> db.bankres.find()
{ "_id" : "axis", "value" : 150000 }
{ "_id" : "boi", "value" : 3000 }
{ "_id" : "hdfc", "value" : 79700 }
{ "_id" : "sbi", "value" : 300000 }
> db.bank.insert({cutid:107,cutname:'hritik',branchname:'rsb',bal:5,location:'shreenagar'})
> db.bank.mapReduce(function(){emit(this.branchname,this.bal)},function(key,values){return Array.sum(values)},{'out':'bankres'})
{
"result" : "bankres",
"timeMillis" : 4,
"counts" : {
"input" : 9,
"emit" : 9,
"reduce" : 2,
"output" : 5
},
"ok" : 1,
}
> db.bankres.find()
{ "_id" : "axis", "value" : 150000 }
{ "_id" : "boi", "value" : 3000 }
{ "_id" : "hdfc", "value" : 79700 }
{ "_id" : "rsb", "value" : 5 }
{ "_id" : "sbi", "value" : 300000 }
> db.bank.insert({cutid:107,cutname:'hritik',branchname:'rsb',bal:5,location:'shreenagar'})
> db.bank.mapReduce(function(){emit(this.branchname,this.bal)},function(key,values){return Array.sum(values)},{'out':'bankres'})
{
"result" : "bankres",
"timeMillis" : 33,
"counts" : {
"input" : 10,
"emit" : 10,
"reduce" : 3,
"output" : 5
},
"ok" : 1,
}
> db.bankres.find()
{ "_id" : "axis", "value" : 150000 }
{ "_id" : "boi", "value" : 3000 }
{ "_id" : "hdfc", "value" : 79700 }
{ "_id" : "rsb", "value" : 10 }
{ "_id" : "sbi", "value" : 300000 }
> db.bank.mapReduce(function(){emit(this.branchname,this.bal)},function(key,values){return Array.avg(values)},{'out':'bankres'})
{
"result" : "bankres",
"timeMillis" : 5,
"counts" : {
"input" : 10,
"emit" : 10,
"reduce" : 3,
"output" : 5
},
"ok" : 1,
}
> db.bankres.find()
{ "_id" : "axis", "value" : 50000 }
{ "_id" : "boi", "value" : 3000 }
{ "_id" : "hdfc", "value" : 26566.666666666668 }
{ "_id" : "rsb", "value" : 5 }
{ "_id" : "sbi", "value" : 300000 }
> db.bank.insert({cutid:107,cutname:'hritik'})
> db.bank.mapReduce(function(){emit(this.branchname,this.bal)},function(key,values){return Array.sum(values)},{'out':'bankres'})
{
"result" : "bankres",
"timeMillis" : 150,
"counts" : {
"input" : 11,
"emit" : 11,
"reduce" : 3,
"output" : 6
},
"ok" : 1,
}
> db.bankres.find()
{ "_id" : null, "value" : null }
{ "_id" : "axis", "value" : 150000 }
{ "_id" : "boi", "value" : 3000 }
{ "_id" : "hdfc", "value" : 79700 }
{ "_id" : "rsb", "value" : 10 }
{ "_id" : "sbi", "value" : 300000 }
> db.bank.find({cutid:1,bal:0}).limit(2)
> db.bank.find().limit(2)
{ "_id" : ObjectId("55de99c80f0881b9f4a95e8b"), "cutid" : 101, "cutname" : "payal", "branchname" : "sbi", "bal" : 300000, "location" : "r.nagar" }
{ "_id" : ObjectId("55de99fd0f0881b9f4a95e8c"), "cutid" : 102, "cutname" : "sam", "branchname" : "boi", "bal" : 3000, "location" : "a.nagar" }
> db.bank.find().pretty().limit(2)
{
"_id" : ObjectId("55de99c80f0881b9f4a95e8b"),
"cutid" : 101,
"cutname" : "payal",
"branchname" : "sbi",
"bal" : 300000,
"location" : "r.nagar"
}
{
"_id" : ObjectId("55de99fd0f0881b9f4a95e8c"),
"cutid" : 102,
"cutname" : "sam",
"branchname" : "boi",
"bal" : 3000,
"location" : "a.nagar"
}
> db.bank.find({},{_id:0}).pretty().limit(2)
{
"cutid" : 101,
"cutname" : "payal",
"branchname" : "sbi",
"bal" : 300000,
"location" : "r.nagar"
}
{
"cutid" : 102,
"cutname" : "sam",
"branchname" : "boi",
"bal" : 3000,
"location" : "a.nagar"
}
> db.bank.find({},{_id:0,cutid:0}).pretty().limit(2)
{
"cutname" : "payal",
"branchname" : "sbi",
"bal" : 300000,
"location" : "r.nagar"
}
{
"cutname" : "sam",
"branchname" : "boi",
"bal" : 3000,
"location" : "a.nagar"
}
> db.bank.find({},{_id:0,cutid:0}).pretty().limit(9)
{
"cutname" : "payal",
"branchname" : "sbi",
"bal" : 300000,
"location" : "r.nagar"
}
{
"cutname" : "sam",
"branchname" : "boi",
"bal" : 3000,
"location" : "a.nagar"
}
{
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "a.bad"
}
{
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 700,
"location" : "pune"
}
{
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 79000,
"location" : "pune"
}
{
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 0,
"location" : "pune"
}
{
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "himachal"
}
{
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "pune"
}
{
"cutname" : "hritik",
"branchname" : "rsb",
"bal" : 5,
"location" : "shreenagar"
}
> db.bank.find({},{_id:0,cutid:0}).pretty().limit(9).skip(2)
{
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "a.bad"
}
{
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 700,
"location" : "pune"
}
{
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 79000,
"location" : "pune"
}
{
"cutname" : "avni",
"branchname" : "hdfc",
"bal" : 0,
"location" : "pune"
}
{
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "himachal"
}
{
"cutname" : "om",
"branchname" : "axis",
"bal" : 50000,
"location" : "pune"
}
{
"cutname" : "hritik",
"branchname" : "rsb",
"bal" : 5,
"location" : "shreenagar"
}
{
"cutname" : "hritik",
"branchname" : "rsb",
"bal" : 5,
"location" : "shreenagar"
}
{ "cutname" : "hritik" }
>
EXP7
Title : Implement Indeximg& Querying.
Indeximg-
>db.employee.ensureIndex({'cust_id':1,'amount':1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
>db.employee.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "mongodb.employee"
},
{
"v" : 1,
"key" : {
"cust_id" : 1,
"amount" : 1
},
"name" : "cust_id_1_amount_1",
"ns" : "mongodb.employee"
},
{
"v" : 1,
"key" : {
"cust_id" : 1,
"status" : 1
},
"name" : "cust_id_1_status_1",
"ns" : "mongodb.employee"
}
]
>db.employee.dropIndex({'cust_id':1,'amount':1})
{ "nIndexesWas" : 3, "ok" : 1 }
>db.employee.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "mongodb.employee"
},
{
"v" : 1,
"key" : {
"cust_id" : 1,
"status" : 1
},
"name" : "cust_id_1_status_1",
"ns" : "mongodb.employee"
}
]
Querying-
>db.student20.update({'name':'sachin'},{$set:{'name':'shashir'}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
>db.student20.find().pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit",
"addr" : "Loni"
}
{
"_id" :ObjectId("53c75c1cf4a21581aced6d2f"),
"roll_no" : 1,
"name" : "shashir",
"addr" : {
"At" : "Loni",
"Tal" : "Rahata",
"Dist" : "Ahemadnagar"
}
}
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "sachin",
"percent_marks" : 60,
"addr" : "Pune"
}
{
"_id" :ObjectId("53c75c9bf4a21581aced6d31"),
"name" : "Rahul",
"Address" : "Kolhar"
}
{ "_id" : 1, "roll_no" : 1 }
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 4, "roll_no" : 4 }
{ "_id" : 5, "roll_no" : 5 }
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
{ "_id" : 35, "roll_no" : 15, "name" : "xyz", "addr" : "Abad" }
>db.student20.update({'name':'sachin'},{$set:{'name':'shashir'}},{multi:true})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
>db.student20.find().pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit",
"addr" : "Loni"
}
{
"_id" :ObjectId("53c75c1cf4a21581aced6d2f"),
"roll_no" : 1,
"name" : "shashir",
"addr" : {
"At" : "Loni",
"Tal" : "Rahata",
"Dist" : "Ahemadnagar"
}
}
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "shashir",
"percent_marks" : 60,
"addr" : "Pune"
}
{
"_id" :ObjectId("53c75c9bf4a21581aced6d31"),
"name" : "Rahul",
"Address" : "Kolhar"
}
{ "_id" : 1, "roll_no" : 1 }
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 4, "roll_no" : 4 }
{ "_id" : 5, "roll_no" : 5 }
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
{ "_id" : 35, "roll_no" : 15, "name" : "xyz", "addr" : "Abad" }
>db.student20.find().limit(4)
{ "_id" : ObjectId("53c75bd3f4a21581aced6d2e"), "roll_no" : 1, "name" : "amit", "addr" : "Loni" }
{ "_id" : ObjectId("53c75c1cf4a21581aced6d2f"), "roll_no" : 1, "name" : "shashir", "addr" : { "At" : "Loni", "Tal" : "Rahata", "Dist" : "Ahemadnagar" } }
{ "_id" : ObjectId("53c75c51f4a21581aced6d30"), "roll_no" : 101, "name" : "shashir", "percent_marks" : 60, "addr" : "Pune" }
{ "_id" : ObjectId("53c75c9bf4a21581aced6d31"), "name" : "Rahul", "Address" : "Kolhar" }
>db.student20.find().skip(2).pretty()
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "shashir",
"percent_marks" : 60,
"addr" : "Pune"
}
{
"_id" :ObjectId("53c75c9bf4a21581aced6d31"),
"name" : "Rahul",
"Address" : "Kolhar"
}
{ "_id" : 1, "roll_no" : 1 }
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 4, "roll_no" : 4 }
{ "_id" : 5, "roll_no" : 5 }
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
{ "_id" : 35, "roll_no" : 15, "name" : "xyz", "addr" : "Abad" }
>db.student20.find({$and:[{'roll_no':{$gt:5}},{'roll_no':{$lt:110}}]}).pretty()
{>db.student20.find({},{'roll_no':1,'name':1}).pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit"
}
{ "_id" : ObjectId("53c75c9bf4a21581aced6d31"), "name" : "Rahul" }
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 4, "roll_no" : 4 }
{ "_id" : 5, "roll_no" : 5 }
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
{ "_id" : 35, "roll_no" : 15, "name" : "xyz" }
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "sachin",
"percent_marks" : 60,
"addr" : "Pune"
}
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
>db.student20.find({$or:[{'addr':'Loni'},{'addr':'Pune'}]}).pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit",
"addr" : "Loni"
}
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "sachin",
"percent_marks" : 60,
"addr" : "Pune"
}
>db.student20.find({'name':{$in:['sachin']}}).pretty()
{
"_id" :ObjectId("53c75c1cf4a21581aced6d2f"),
"roll_no" : 1,
"name" : "sachin",
"addr" : {
"At" : "Loni",
"Tal" : "Rahata",
"Dist" : "Ahemadnagar"
}
}
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "sachin",
"percent_marks" : 60,
"addr" : "Pune"
}
>db.student20.find({'name':{$nin:['sachin']}}).pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit",
"addr" : "Loni"
}
{
"_id" :ObjectId("53c75c9bf4a21581aced6d31"),
"name" : "Rahul",
"Address" : "Kolhar"
}
{ "_id" : 1, "roll_no" : 1 }
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 4, "roll_no" : 4 }
{ "_id" : 5, "roll_no" : 5 }
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
>db.student20.find({'name':'sachin'}).pretty()
{
"_id" :ObjectId("53c75c1cf4a21581aced6d2f"),
"roll_no" : 1,
"name" : "sachin",
"addr" : {
"At" : "Loni",
"Tal" : "Rahata",
"Dist" : "Ahemadnagar"
}
}
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "sachin",
"percent_marks" : 60,
"addr" : "Pune"
}
>db.student20.find({'roll_no':{$lt:50}}).pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit",
"addr" : "Loni"
}
{
"_id" :ObjectId("53c75c1cf4a21581aced6d2f"),
"roll_no" : 1,
"name" : "sachin",
"addr" : {
"At" : "Loni",
"Tal" : "Rahata",
"Dist" : "Ahemadnagar"
}
}
{ "_id" : 1, "roll_no" : 1 }
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 4, "roll_no" : 4 }
{ "_id" : 5, "roll_no" : 5 }
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
>db.student20.find({'roll_no':{$gt:50}}).pretty()
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "sachin",
"percent_marks" : 60,
"addr" : "Pune"
}
>db.student20.find({'roll_no':{$ne:50}}).pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit",
"addr" : "Loni"
}
{
"_id" :ObjectId("53c75c1cf4a21581aced6d2f"),
"roll_no" : 1,
"name" : "sachin",
"addr" : {
"At" : "Loni",
"Tal" : "Rahata",
"Dist" : "Ahemadnagar"
}
}
{
"_id" :ObjectId("53c75c51f4a21581aced6d30"),
"roll_no" : 101,
"name" : "sachin",
"percent_marks" : 60,
"addr" : "Pune"
}
{
"_id" :ObjectId("53c75c9bf4a21581aced6d31"),
"name" : "Rahul",
"Address" : "Kolhar"
}
{ "_id" : 1, "roll_no" : 1 }
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 4, "roll_no" : 4 }
{ "_id" : 5, "roll_no" : 5 }
{ "_id" : 6, "roll_no" : 6 }
{ "_id" : 7, "roll_no" : 7 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 9, "roll_no" : 9 }
{ "_id" : 10, "roll_no" : 10 }
>db.student20.remove({'roll_no':{$gt:8}},true)
WriteResult({ "nRemoved" : 1 })
>db.student20.find().pretty()
{
"_id" :ObjectId("53c75bd3f4a21581aced6d2e"),
"roll_no" : 1,
"name" : "amit",
"addr" : "Loni"
}
{
"_id" :ObjectId("53c75c9bf4a21581aced6d31"),
"name" : "Rahul",
"Address" : "Kolhar"
}
{ "_id" : 2, "roll_no" : 2 }
{ "_id" : 3, "roll_no" : 3 }
{ "_id" : 8, "roll_no" : 8 }
{ "_id" : 10, "roll_no" : 10 }
{ "_id" : 35, "roll_no" : 15, "name" : "xyz", "addr" : "Abad" }
>db.student20.remove({$and:[{'roll_no':{$gt:3}},{'roll_no':{$lt:8}}]})
WriteResult({ "nRemoved" : 3 })
>db.student20.find().pretty()
EXP8
Title : Database connectivity program in java with mongodb.
import com.mongodb.*;
public class Primary {
public static void main( String args[] ){
try
{ // To connect to mongodb server
MongoClient mongoClient = new MongoClient( "localhost" , 27018 );
// Now connect to your databases
DB db = mongoClient.getDB( "local" );
System.out.println("Connected to database successfully.");
}
catch(Exception e)
{ System.err.println( e.getClass().getName() + ": " + e.getMessage() );
}
}
}
Comments
Post a Comment