Java Blog: JDBC

Oracle:

Connection.java

//package JDBC;

package CRUD_Oracle;

import java.sql.DriverManager;

import java.sql.SQLException;

public class Connection {

public static void main(String[] args) throws ClassNotFoundException,

SQLException {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

java.sql.Connection con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:xe", "raghu", "raghu");

System.out.println("Connection Established Successfully");

} catch (Exception e) {

System.err.println(e);

}

o/p:-

Connection Established Successfully

Table creation:-

CREATE table "STUDENTS" (

"ID" NUMBER(20),

"NAME" VARCHAR2(20),

"SALARY" NUMBER(20)

)

Table created.

Clear Table Data:-

truncate table "STUDENTS"

Table Data Deleted.

InsertRecord.java

//package JDBC;

package CRUD_Oracle;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

public class InsertRecord {

public static void main(String[] args) {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "raghu", "raghu");

Statement stmt = con.createStatement();

stmt.executeUpdate("insert into students values(4,'ddd',99)");

System.out.println("One Record Inserted Successfully");

} catch (Exception e) {

System.err.println(e);

}

o/p:-

One Record Inserted Successfully

CREATE table "STUDENTSS" (

"RNO" NUMBER(20),

"NAME" VARCHAR2(20),

"MARKS" NUMBER(20)

)

Table created.

InsertingRecords.java

//package JDBC;

package CRUD_Oracle;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class InsertingRecords {

public static void main(String[] args) {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "raghu", "raghu");

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery("select rno,name,marks from STUDENTSS");

rs.moveToInsertRow();

rs.updateInt(1, 11);

rs.updateString(2, "iii");

rs.updateInt(3, 555);

System.out.println("One record Inserted Successfully");

} catch (Exception e) {

System.err.println(e);

}

o/p:-

One record Inserted Successfully

DeleteRecord.java

//package JDBC;

package CRUD_Oracle;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class DeleteRecord {

public static void main(String[] args) {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "manager");

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery("select rno,name,marks from students");

rs.absolute(1);

rs.deleteRow();

System.out.println("One Record Deleted Successfully");

} catch (Exception e) {

System.err.println(e);

}

o/p:-

One Record Deleted Successfully

UpdeRecord.java

//package JDBC;

package CRUD_Oracle;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class UpdeRecord {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "manager");

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery("select rno,name,marks from students");

rs.absolute(1);//row no

rs.updateInt(1, 5);

rs.updateString(2, "BHASKAR");

rs.updateInt(3, 93);

rs.updateRow();

System.out.println("One Record Updated Successfully");

} catch (Exception e) {

System.err.println(e);

}

o/p:-

One Record Updated Successfully

RetrieveData.java

//package JDBC;

package CRUD_Oracle;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.Statement;

public class RetrieveData {

public static void main(String[] args) {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "manager");

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery("select * from students");

ResultSetMetaData rm = rs.getMetaData();

int n = rm.getColumnCount();

for (int i = 1; i <= n; i++) {

System.out.println(rm.getColumnName(i) + "\t");

}

System.out.println();

while (rs.next()) {

System.out.println(rs.getInt(1) + "\t");

System.out.println(rs.getString(2) + "\t");

System.out.println(rs.getInt(3) + "\t");

System.out.println();

}

} catch (Exception e) {

System.err.println(e);

}

o/p:-

shows the content

BatchProcessing.java

package CRUD_Oracle;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

public class BatchProcessing {

public static void main(String[] args) {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:xe", "system", "manager");

Statement stmt = con.createStatement();

stmt.addBatch("insert into students values(6,'Ravi',77)");

stmt.addBatch("update students set marks=40 where rno=4");

stmt.addBatch("delete from students where rno=2");

stmt.executeBatch();

System.out.println("Batch Executed Successfully");

} catch (Exception e) {

System.err.println(e);

}

o/p:-

Done All Batch Processing

Table.java

//package JDBC;

package CRUD_Oracle;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

public class Table {

public static void main(String[] args) {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "manager");

Statement stmt = con.createStatement();

stmt.execute("create table students(rno number(3),name varchar2(10),marks number(3))");

System.out.println("Table Cre1ated Successfully");

} catch (Exception e) {

System.err.println(e);

}

o/p:-

Table created

SavePoint.java

//package JDBC;

package CRUD_Oracle;

public class SavePoint {

public static void main(String[] args) {

}

o/p:-

Code First

WithoutSavePoint.java

//package JDBC;

package CRUD_Oracle;

public class WithoutSavePoint {

public static void main(String[] args) {

}

o/p:-

Code First

Mysql:

DBConnect3.java

package CRUD_mysql;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class DBConnect3 {

public static void main(String[] args) throws ClassNotFoundException {

String databaseURL = "";

String user = "";

String password = "";

Connection conn = null;

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection(

"jdbc:mysql://localhost:3306/sampledb", "root", "");

if (conn != null) {

System.out.println("Connected to the database");

}

} catch (SQLException ex) {

System.out

.println("An error occurred. Maybe user/password is invalid");

ex.printStackTrace();

} finally {

if (conn != null) {

try {

conn.close();

} catch (SQLException ex) {

ex.printStackTrace();

}

o/p:-

JdbcInsertDemo.java

package CRUD_mysql;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

/**

* A sample program that demonstrates how to execute SQL INSERT statement using

* JDBC.

* @author www.codejava.net

public class JdbcInsertDemo {

public static void main(String[] args) {

String dbURL = "jdbc:mysql://localhost:3306/sampledb";

String username = "root";

String password = "";

try (Connection conn = DriverManager.getConnection(dbURL, username,

password)) {

String sql = "INSERT INTO Users (username, password, fullname, email) VALUES (?, ?, ?, ?)";

PreparedStatement statement = conn.prepareStatement(sql);

statement.setString(1, "bill");

statement.setString(2, "secretpass");

statement.setString(3, "Bill Gates");

statement.setString(4, "bill.gates@microsoft.com");

int rowsInserted = statement.executeUpdate();

if (rowsInserted > 0) {

System.out.println("A new user was inserted successfully!");

}

} catch (SQLException ex) {

ex.printStackTrace();

}

o/p:-

JdbcDeleteDemo.java

package CRUD_mysql;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

/**

* A sample program that demonstrates how to execute SQL DELETE statement using

* JDBC.

* @author www.codejava.net

public class JdbcDeleteDemo {

public static void main(String[] args) {

String dbURL = "jdbc:mysql://localhost:3306/sampledb";

String username = "root";

String password = "";

try (Connection conn = DriverManager.getConnection(dbURL, username,

password)) {

String sql = "DELETE FROM Users WHERE username=?";

PreparedStatement statement = conn.prepareStatement(sql);

statement.setString(1, "bill");

int rowsDeleted = statement.executeUpdate();

if (rowsDeleted > 0) {

System.out.println("A user was deleted successfully!");

}

} catch (SQLException ex) {

ex.printStackTrace();

}

o/p:-

JdbcUpdateDemo.java

package CRUD_mysql;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

/**

* A sample program that demonstrates how to execute SQL UPDATE statement

* using JDBC.

* @author www.codejava.net

public class JdbcUpdateDemo {

public static void main(String[] args) {

String dbURL = "jdbc:mysql://localhost:3306/sampledb";

String username = "root";

String password = "";

try (Connection conn = DriverManager.getConnection(dbURL, username, password)) {

String sql = "UPDATE Users SET password=?, fullname=?, email=? WHERE username=?";

PreparedStatement statement = conn.prepareStatement(sql);

statement.setString(1, "123456789");

statement.setString(2, "William Henry Bill Gates");

statement.setString(3, "bill@gatesfoundation.org");

statement.setString(4, "bill");

int rowsUpdated = statement.executeUpdate();

if (rowsUpdated > 0) {

System.out.println("An existing user was updated successfully!");

}

} catch (SQLException ex) {

ex.printStackTrace();

}

o/p:-

JdbcSelectDemo.java

package CRUD_mysql;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

/**

* A sample program that demonstrates how to execute SQL SELECT statement

* using JDBC.

* @author www.codejava.net

public class JdbcSelectDemo {

public static void main(String[] args) {

String dbURL = "jdbc:mysql://localhost:3306/sampledb";

String username = "root";

String password = "";

try (Connection conn = DriverManager.getConnection(dbURL, username, password)) {

String sql = "SELECT * FROM Users";

Statement statement = conn.createStatement();

ResultSet result = statement.executeQuery(sql);

int count = 0;

while (result.next()){

String name = result.getString(2);

String pass = result.getString(3);

String fullname = result.getString("fullname");

String email = result.getString("email");

String output = "User #%d: %s - %s - %s - %s";

System.out.println(String.format(output, ++count, name, pass, fullname, email));

}

} catch (SQLException ex) {

ex.printStackTrace();

}

o/p:-

Java Blog

Pages

JDBC

No comments:

Post a Comment

Report Abuse