Setting the database:
Create a MySQL database on AWS RDS.
Select My SQL Client products.
Make the RDS instance publicly available.
Change the Security group as per your configuration.
Use My SQL endpoint is a Host.
--------------------------------------------------------------------------------------------------------------
Database and table creation script. (Execute it from MySQL workbench)
CREATE DATABASE IF NOT EXISTS students;
CREATE TABLE students.credential (
UserId INT NOT NULL AUTO_INCREMENT primary key,
UserName VARCHAR(255) not null,
password VARCHAR(512) not null);
Insert into students.credential values (1, 'ABC', 'ABC');
Insert into students.credential values (2, 'XYZ', 'XYZ');
Download My SQL workbench. Link: https://dev.mysql.com/downloads/workbench/
Download JDBC driver: https://downloads.mysql.com/archives/c-j/
Find out the driver class name from the https://dev.mysql.com/doc/connector-j/8.0/en/. The link will be available in the read me.
Write the following Java Program (SimpleLogin.java):
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class SimpleLogin {
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
Scanner sc=new Scanner(System.in);
System.out.println("Input User Name and Press Enter: ");
String userName=sc.nextLine();
System.out.println("Input Password and Press Enter: ");
String password=sc.nextLine();
sc.close();
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://mydb.ccli3fu3j2su.us-east-1.rds.amazonaws.com/students?" +
"user=admin&password=abcd1234");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select count(*) from credential where
UserName = '"+userName+"' and password='"+password+"'");
rs.next();
if(rs.getInt("count(*)")>0)
{
System.out.println("Login Success!!!");
} else {
System.out.println("Invalid creadentials!!!");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Following is the Login Application with a salted password.
We need to add the entries in the database as follows to make this program work.
First, we need to alter the table as we are going to store 512 bit in it as follows.
alter table students.credential modify username varchar(512);
alter table students.credential modify password varchar(512);
Insert into Login.credential values (3, 'PQR', SHA2('PQR',512));
Insert into Login.credential values (4, 'UVW', SHA2('UVW',512));
Change the login application we have created earlier as follows:
import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.*;
import java.util.Scanner;
public class LoginAWS {
public static void main(String[] args) throws SQLException {
Connection myConn = null;
Statement myStmt = null;
ResultSet myRs = null;
try {
// 1. Get a connection to database
Scanner sc=new Scanner(System.in);
System.out.println("Input User Name and Press Enter: ");
String userName=sc.nextLine();
System.out.println("Input Password and Press Enter: ");
String password=sc.nextLine();
sc.close();
myConn = DriverManager.getConnection("jdbc:mysql://mydb.ccli3fu3j2su.us-east-1.rds.amazonaws.com/students",
"admin" , "abcd1234");
System.out.println("Database connection successful!\n");
// 2. Create a statement
myStmt = myConn.createStatement();
// 3. Execute SQL query
myRs = myStmt.executeQuery("select count(*) from credential where
UserName = '"+userName+"' and password='"+getSHA(password)+"'");
myRs.next();
if(myRs.getInt("count(*)")>0)
{
System.out.println("Login Success!!!");
} else {
System.out.println("Invalid creadentials!!!");
}
}
catch (Exception exc) {
exc.printStackTrace();
}
finally {
if (myRs != null) {
myRs.close();
}
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
}
public static String getSHA(String input)
{
try {
// Static getInstance method is called with hashing SHA
MessageDigest md = MessageDigest.getInstance("SHA-512");
// digest() method called
// to calculate message digest of an input
// and return array of byte
byte[] messageDigest = md.digest(input.getBytes());
// Convert byte array into signum representation
BigInteger no = new BigInteger(1, messageDigest);
// Convert message digest into hex value
String hashtext = no.toString(16);
while (hashtext.length() < 32) {
hashtext = "0" + hashtext;
}
return hashtext;
}
// For specifying wrong message digest algorithms
catch (NoSuchAlgorithmException e) {
System.out.println("Exception thrown"
+ " for incorrect algorithm: " + e);
return null;
}
}
}
Simple Login Application using the prepared statement:
import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class LoginPreparedStmt {
public static void main(String[] args) throws SQLException {
Connection myConn = null;
PreparedStatement myStmt = null;
ResultSet myRs = null;
try {
// 1. Get a connection to database
Scanner sc=new Scanner(System.in);
System.out.println("Input User Name and Press Enter: ");
String userName=sc.nextLine();
System.out.println("Input Password and Press Enter: ");
String password=sc.nextLine();
sc.close();
myConn =
DriverManager.getConnection("jdbc:mysql://mydb.ccli3fu3j2su.us-east-1.rds.amazonaws.com/students?user=admin&password=abcd1234");
System.out.println("Database connection successful!\n");
// 2. Create a statement
myStmt =
myConn.prepareStatement("select count(*) from credential where UserName = ? and password=?");
myStmt.setString(1, userName);
myStmt.setString(2, getSHA(password));
// 3. Execute SQL query
myRs = myStmt.executeQuery();
myRs.next();
// 4. Process the result set
if(myRs.getInt("count(*)")>0)
{
System.out.println("Login Success!!!");
} else {
System.out.println("Invalid creadentials!!!");
}
}
catch (Exception exc) {
exc.printStackTrace();
}
finally {
if (myRs != null) {
myRs.close();
}
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
}
public static String getSHA(String input)
{
try {
// Static getInstance method is called with hashing SHA
MessageDigest md = MessageDigest.getInstance("SHA-512");
// digest() method called
// to calculate message digest of an input
// and return array of byte
byte[] messageDigest = md.digest(input.getBytes());
// Convert byte array into signum representation
BigInteger no = new BigInteger(1, messageDigest);
// Convert message digest into hex value
String hashtext = no.toString(16);
while (hashtext.length() < 32) {
hashtext = "0" + hashtext;
}
return hashtext;
}
// For specifying wrong message digest algorithms
catch (NoSuchAlgorithmException e) {
System.out.println("Exception thrown"
+ " for incorrect algorithm: " + e);
return null;
}
}
}
Connecting to the database using Property file:
Create a file called demo.properties as follows:
user=admin
password=abcd123
dburl=jdbc:mysql://mydb.ccli3fu3j2su.us-east-1.rds.amazonaws.com/students
Run the following program to check working of it
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import java.util.Properties;
public class PropertyFileDemo {
public static void main(String[] args) throws Exception {
Connection myConn = null;
Statement myStmt = null;
ResultSet myRs = null;
try {
// 1. Load the properties file
Properties props = new Properties();
props.load(new FileInputStream("demo.properties"));
// 2. Read the props
String theUser = props.getProperty("user");
String thePassword = props.getProperty("password");
String theDburl = props.getProperty("dburl");
System.out.println("Connecting to database...");
System.out.println("Database URL: " + theDburl);
System.out.println("User: " + theUser);
// 3. Get a connection to database
myConn = DriverManager.getConnection(theDburl, theUser, thePassword);
} catch (Exception e) {
} finally {
if (myConn != null)
System.out.println("\nConnection successful!\n");
else
System.out.println("\nWrong Credential!\n");
close(myConn, myStmt, myRs);
}
}
private static void close(Connection myConn, Statement myStmt, ResultSet myRs) throws SQLException {
if (myRs != null) {
myRs.close();
}
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
}
Creating a table and performing DML using java program
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DMLSimple {
public static void main(String[] args) throws SQLException {
Connection myConn = null;
Statement myStmt = null;
ResultSet myRs = null;
String dbUrl = "jdbc:mysql://mydb.ccli3fu3j2su.us-east-1.rds.amazonaws.com/demo";
String user = "admin";
String pass = "abcd1234";
try {
// 1. Get a connection to database
myConn = DriverManager.getConnection(dbUrl, user, pass);
// 2. Create a statement
myStmt = myConn.createStatement();
// 2.1 Create a table
boolean dbNotCreated = myStmt.execute("create database if not exists demo");
if(!dbNotCreated) {
System.out.println("DB created successfully");
myStmt.execute("use demo");
} else {
System.out.println("DB not created");
}
myStmt.execute("drop table if exists students");
boolean tableNotCreated = myStmt.execute("CREATE TABLE `students` (\r\n" +
" `id` int(11) NOT NULL AUTO_INCREMENT,\r\n" +
" `last_name` varchar(64) DEFAULT NULL,\r\n" +
" `first_name` varchar(64) DEFAULT NULL,\r\n" +
" `email` varchar(64) DEFAULT NULL,\r\n" +
" `language` varchar(64) DEFAULT NULL,\r\n" +
" `age` DECIMAL(10,2) DEFAULT NULL,\r\n" +
" PRIMARY KEY (`id`)\r\n" +
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;");
if(!tableNotCreated) {
System.out.println("Table created successfully");
} else {
System.out.println("Table not created");
}
// 3. Insert a new employee
System.out.println("Inserting a new employee to database\n");
int rowsAffected = myStmt
.executeUpdate("insert into students "
+ "(last_name, first_name, email, language, age) "
+ "values "
+ "('patel', 'sagar', 'sagar.patel@cryptogurus.in', 'All', 13)");
if (rowsAffected>0) {
System.out.println("Record added successfully");
}
rowsAffected = myStmt
.executeUpdate("insert into students "
+ "(last_name, first_name, email, language, age) "
+ "values "
+ "('shah', 'parth', 'parth.shah@cryptogurus.in', 'java', 15)");
if (rowsAffected>0) {
System.out.println("Record added successfully");
}
// 4. Verify this by getting a list of students
myRs = myStmt
.executeQuery("select * from students order by last_name");
// 5. Process the result set
while (myRs.next()) {
System.out.println(myRs.getString("last_name") + ", "
+ myRs.getString("first_name"));
}
System.out.println("BEFORE THE UPDATE...");
myRs = myStmt
.executeQuery("select * from students where first_name= 'parth' and last_name='shah' order by last_name ");
while (myRs.next()) {
System.out.println(myRs.getString("last_name") + ", "
+ myRs.getString("first_name") + ", "
+ myRs.getString("email"));
}
rowsAffected = myStmt.executeUpdate("update students "
+ "set email='shah.parth@charusat.ac.in' "
+ "where last_name='shah' and first_name='parth'");
// UPDATE the employee
System.out.println("\nEXECUTING THE UPDATE FOR: parth shah\n");
myRs = myStmt
.executeQuery("select * from students where first_name= 'parth' and last_name='shah' order by last_name");
while (myRs.next()) {
System.out.println(myRs.getString("last_name") + ", "
+ myRs.getString("first_name") + ", "
+ myRs.getString("email"));
}
System.out.println("BEFORE THE DELETE...");
myRs = myStmt
.executeQuery("select * from students where first_name= 'parth' and last_name='shah' order by last_name ");
while (myRs.next()) {
System.out.println(myRs.getString("last_name") + ", "
+ myRs.getString("first_name") + ", "
+ myRs.getString("email"));
}
// DELETE the employee
System.out.println("\nDELETING THE EMPLOYEE: parth shah\n");
rowsAffected = myStmt.executeUpdate(
"delete from students " +
"where last_name='shah' and first_name='parth'");
// Call helper method to display the employee's information
System.out.println("AFTER THE DELETE...");
myRs = myStmt
.executeQuery("select * from students where first_name= 'parth' and last_name='shah' order by last_name ");
while (myRs.next()) {
System.out.println(myRs.getString("last_name") + ", "
+ myRs.getString("first_name") + ", "
+ myRs.getString("email"));
}
} catch (Exception exc) {
exc.printStackTrace();
} finally {
if (myRs != null) {
myRs.close();
}
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
}
}
Using a prepared statement to perform DML operations:
import java.sql.*;
public class PrepareStmtExample {
public static void main(String[] args) throws SQLException {
Connection myConn = null;
PreparedStatement myStmt = null;
ResultSet myRs = null;
try {
// 1. Get a connection to database
myConn = DriverManager.getConnection(
"jdbc:mysql://mydb.ccli3fu3j2su.us-east-1.rds.amazonaws.com/demo", "admin", "abcd1234");
// 2. Prepare statement
myStmt = myConn
.prepareStatement("select * from students where age > ? and language=?");
// 3. Set the parameters
myStmt.setDouble(1, 12);
myStmt.setString(2, "All");
// 4. Execute SQL query
myRs = myStmt.executeQuery();
// 5. Display the result set
display(myRs);
//
// Reuse the prepared statement: salary > 25000, department = HR
//
System.out
.println("\n\nReuse the prepared statement: age > 12, department = All");
// 6. Set the parameters
myStmt.setDouble(1, 14);
myStmt.setString(2, "java");
// 7. Execute SQL query
myRs = myStmt.executeQuery();
// 8. Display the result set
display(myRs);
} catch (Exception exc) {
exc.printStackTrace();
} finally {
if (myRs != null) {
myRs.close();
}
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
}
private static void display(ResultSet myRs) throws SQLException {
while (myRs.next()) {
String lastName = myRs.getString("last_name");
String firstName = myRs.getString("first_name");
double age = myRs.getDouble("age");
String language = myRs.getString("language");
System.out.println(lastName + " " + firstName + " " + age + " "
+ language);
}
}
}
Lets recreate the database to perform remain excercise
create database if not exists demo;
use demo;
drop table if exists students;
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`last_name` varchar(64) DEFAULT NULL,
`first_name` varchar(64) DEFAULT NULL,
`email` varchar(64) DEFAULT NULL,
`language` varchar(64) DEFAULT NULL,
`age` DECIMAL(10,2) DEFAULT NULL,
`resume` MEDIUMBLOB,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
INSERT INTO `students` (`id`,`last_name`,`first_name`,`email`, `language`, `age`)
VALUES (1,'patel','sagar','patel.sagar@cryptogurus.in', 'All', 12);
INSERT INTO `students` (`id`,`last_name`,`first_name`,`email`, `language`, `age`)
VALUES (2,'shah','parth','shah.parth@cryptogurus.in', 'java', 14);
The following program will write a BLOB to the database
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class WriteBlobDemo {
public static void main(String[] args) throws Exception {
Connection myConn = null;
PreparedStatement myStmt = null;
FileInputStream input = null;
try {
// 1. Load the properties file
Properties props = new Properties();
props.load(new FileInputStream("demo.properties"));
// props.load(new FileInputStream("demo.properties"));
// 2. Read the props
String theUser = props.getProperty("user");
String thePassword = props.getProperty("password");
String theDburl = props.getProperty("dburl");
// 3. Get a connection to database
myConn = DriverManager.getConnection(theDburl, theUser, thePassword);
// 4. Prepare statement
String sql = "update students set resume=? where email='patel.sagar@cryptogurus.in'";
myStmt = myConn.prepareStatement(sql);
// 5. Set parameter for resume file name
File theFile = new File("sample_resume.pdf");
input = new FileInputStream(theFile);
myStmt.setBinaryStream(1, input);
System.out.println("Reading input file: " + theFile.getAbsolutePath());
// 6. Execute statement
System.out.println("\nStoring resume in database: " + theFile);
System.out.println(sql);
int rowAffected = myStmt.executeUpdate();
if(rowAffected >1) {
System.out.println("\nCompleted successfully!");
}
} catch (Exception exc) {
exc.printStackTrace();
} finally {
if (input != null) {
input.close();
}
close(myConn, myStmt);
}
}
private static void close(Connection myConn, Statement myStmt)
throws SQLException {
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
}
The following program will read a BLOB from the database
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class ReadBlobDemo {
public static void main(String[] args) throws Exception {
Connection myConn = null;
Statement myStmt = null;
ResultSet myRs = null;
InputStream input = null;
FileOutputStream output = null;
try {
// 1. Read the props
Properties props = new Properties();
props.load(new FileInputStream("demo.properties"));
String theUser = props.getProperty("user");
String thePassword = props.getProperty("password");
String theDburl = props.getProperty("dburl");
myConn = DriverManager.getConnection(theDburl, theUser, thePassword);
// 2. Execute statement
myStmt = myConn.createStatement();
String sql = "select resume from students where email='patel.sagar@cryptogurus.in'";
myRs = myStmt.executeQuery(sql);
// 3. Set up a handle to the file
File theFile = new File("resume_from_db.pdf");
output = new FileOutputStream(theFile);
if (myRs.next()) {
input = myRs.getBinaryStream("resume");
System.out.println("Reading resume from database...");
System.out.println(sql);
byte[] buffer = new byte[1024];
while (input.read(buffer) > 0) {
output.write(buffer);
}
System.out.println("\nSaved to file: " + theFile.getAbsolutePath());
System.out.println("\nCompleted successfully!");
}
} catch (Exception exc) {
exc.printStackTrace();
} finally {
if (input != null) {
input.close();
}
if (output != null) {
output.close();
}
close(myConn, myStmt);
}
}
private static void close(Connection myConn, Statement myStmt)
throws SQLException {
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
}
Create a stored procedure as follows
CREATE PROCEDURE `greet_the_department`(INOUT language VARCHAR(64))
BEGIN
SET department = concat('Hello to the awesome ', language, ' team!');
END
Java program to call a stored procedure
import java.sql.*;
public class ProcGreetTheStudent {
public static void main(String[] args) throws Exception {
Connection myConn = null;
CallableStatement myStmt = null;
try {
// Get a connection to database
myConn = DriverManager.getConnection(
"jdbc:mysql://mydb.ccli3fu3j2su.us-east-1.rds.amazonaws.com/students", "admin", "abcd1234");
String theLanguage = "All";
// Prepare the stored procedure call
myStmt = myConn
.prepareCall("{call greet_the_department(?)}");
// Set the parameters
myStmt.registerOutParameter(1, Types.VARCHAR);
myStmt.setString(1, theLanguage);
// Call stored procedure
System.out.println("Calling stored procedure. greet_the_department('" + theLanguage + "')");
myStmt.execute();
System.out.println("Finished calling stored procedure");
// Get the value of the INOUT parameter
String theResult = myStmt.getString(1);
System.out.println("\nThe result = " + theResult);
} catch (Exception exc) {
exc.printStackTrace();
} finally {
close(myConn, myStmt);
}
}
private static void close(Connection myConn, Statement myStmt) throws SQLException {
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
}
Create another procedure as follows
CREATE PROCEDURE `increase_age_of_studnets`(IN the_student VARCHAR(64), IN increase_age DECIMAL(10,2), OUT rows_affected INT)
BEGIN
UPDATE students SET age= age+ 1 where last_name=the_student;
SELECT COUNT(*) INTO rows_affected FROM students where last_name=the_student;
END
Following program will call the above created stored procedure
import java.sql.*;
public class ProcIncreaseSalariesForDepartment {
public static void main(String[] args) throws Exception {
Connection myConn = null;
CallableStatement myStmt = null;
try {
// Get a connection to database
myConn = DriverManager.getConnection(
"jdbc:mysql://mydb.ccli3fu3j2su.us-east-1.rds.amazonaws.com/students", "admin", "abcd1234");
String last_name = "patel";
int theIncreaseYear = 1;
// Show age BEFORE
System.out.println("Age BEFORE\n");
showAge(myConn, last_name);
// Prepare the stored procedure call
myStmt = myConn
.prepareCall("{call increase_age_of_studnets(?, ?,?)}");
// Set the parameters
myStmt.registerOutParameter(3, Types.INTEGER);
myStmt.setString(1, last_name);
myStmt.setDouble(2, theIncreaseYear);
// Call stored procedure
System.out.println("\n\nCalling stored procedure. increase_age_of_students('" + last_name + "', " + theIncreaseYear + ")");
myStmt.execute();
System.out.println("Finished calling stored procedure");
// Show salaries AFTER
System.out.println("\n\nAge AFTER\n");
showAge(myConn, last_name);
} catch (Exception exc) {
exc.printStackTrace();
} finally {
close(myConn, myStmt, null);
}
}
private static void showAge(Connection myConn, String last_name) throws SQLException {
PreparedStatement myStmt = null;
ResultSet myRs = null;
try {
// Prepare statement
myStmt = myConn
.prepareStatement("select * from students where last_name=?");
myStmt.setString(1, last_name);
// Execute SQL query
myRs = myStmt.executeQuery();
// Process result set
while (myRs.next()) {
String lastName = myRs.getString("last_name");
String firstName = myRs.getString("first_name");
double age = myRs.getDouble("age");
String language = myRs.getString("language");
System.out.println(lastName +" "+ firstName+" "+ language+" "+ age);
}
} catch (Exception exc) {
exc.printStackTrace();
} finally {
close(myStmt, myRs);
}
}
private static void close(Connection myConn, Statement myStmt,
ResultSet myRs) throws SQLException {
if (myRs != null) {
myRs.close();
}
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
private static void close(Statement myStmt, ResultSet myRs)
throws SQLException {
close(null, myStmt, myRs);
}
}
Following program will get metadata of Database
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SchemaInfo {
public static void main(String[] args) throws SQLException {
String catalog = null;
String schemaPattern = null;
String tableNamePattern = null;
String columnNamePattern = null;
String[] types = null;
Connection myConn = null;
ResultSet myRs = null;
try {
// 1. Get a connection to database
myConn = DriverManager.getConnection(
"jdbc:mysql://mydb.ccli3fu3j2su.us-east-1.rds.amazonaws.com/demo", "admin", "abcd1234");
// 2. Get metadata
DatabaseMetaData databaseMetaData = myConn.getMetaData();
// 3. Get list of tables
System.out.println("List of Tables");
System.out.println("--------------");
myRs = databaseMetaData.getTables(catalog, schemaPattern, tableNamePattern,
types);
while (myRs.next()) {
System.out.println(myRs.getString("TABLE_NAME"));
}
// 4. Get list of columns
System.out.println("\n\nList of Columns");
System.out.println("--------------");
myRs = databaseMetaData.getColumns(catalog, schemaPattern, "students", columnNamePattern);
while (myRs.next()) {
System.out.println(myRs.getString("COLUMN_NAME"));
}
} catch (Exception exc) {
exc.printStackTrace();
} finally {
close(myConn, myRs);
}
}
private static void close(Connection myConn, ResultSet myRs)
throws SQLException {
if (myRs != null) {
myRs.close();
}
if (myConn != null) {
myConn.close();
}
}
}
The following program will get metadata of table (ResultSet)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class ResultSetDemo {
public static void main(String[] args) throws SQLException {
Connection myConn = null;
Statement myStmt = null;
ResultSet myRs = null;
try {
// 1. Get a connection to database
myConn = DriverManager.getConnection(
"jdbc:mysql://mydb.ccli3fu3j2su.us-east-1.rds.amazonaws.com/students", "admin", "abcd1234");
// 2. Run query
myStmt = myConn.createStatement();
myRs = myStmt.executeQuery("select id, last_name, first_name, age from students");
// 3. Get result set metadata
ResultSetMetaData rsMetaData = myRs.getMetaData();
// 4. Display info
int columnCount = rsMetaData.getColumnCount();
System.out.println("Column count: " + columnCount + "\n");
for (int column=1; column <= columnCount; column++) {
System.out.println("Column name: " + rsMetaData.getColumnName(column));
System.out.println("Column type name: " + rsMetaData.getColumnTypeName(column));
System.out.println("Is Nullable: " + rsMetaData.isNullable(column));
System.out.println("Is Auto Increment: " + rsMetaData.isAutoIncrement(column) + "\n");
}
} catch (Exception exc) {
exc.printStackTrace();
} finally {
close(myConn, myStmt, myRs);
}
}
private static void close(Connection myConn, Statement myStmt, ResultSet myRs)
throws SQLException {
if (myRs != null) {
myRs.close();
}
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
}
Let's create the program of transaction
import java.sql.*;
import java.util.Scanner;
public class TransactionDemo {
public static void main(String[] args) throws SQLException {
Connection myConn = null;
Statement myStmt = null;
try {
// 1. Get a connection to database
myConn = DriverManager.getConnection(
"jdbc:mysql://mydb.ccli3fu3j2su.us-east-1.rds.amazonaws.com/students", "admin", "abcd1234");
// Turn off auto commit
myConn.setAutoCommit(false);
// Show salaries BEFORE
System.out.println("Salaries BEFORE\n");
showSalaries(myConn, "All");
showSalaries(myConn, "java");
// Transaction Step 1: Delete all HR employees
myStmt = myConn.createStatement();
myStmt.executeUpdate("delete from students where language='java'");
// Transaction Step 2: Set salaries to 300000 for all Engineering
// employees
myStmt.executeUpdate("update students set age=16 where last_name='shah'");
System.out.println("\n>> Transaction steps are ready.\n");
// Ask user if it is okay to save
boolean ok = askUserIfOkToSave();
if (ok) {
// store in database
myConn.commit();
System.out.println("\n>> Transaction COMMITTED.\n");
} else {
// discard
myConn.rollback();
System.out.println("\n>> Transaction ROLLED BACK.\n");
}
// Show salaries AFTER
System.out.println("Age AFTER\n");
showSalaries(myConn, "All");
showSalaries(myConn, "java");
} catch (Exception exc) {
exc.printStackTrace();
} finally {
close(myConn, myStmt, null);
}
}
/**
* Prompts the user. Return true if they enter "yes", false otherwise
*
* @return
*/
private static boolean askUserIfOkToSave() {
Scanner scanner = new Scanner(System.in);
System.out.println("Is it okay to save? yes/no: ");
String input = scanner.nextLine();
scanner.close();
return input.equalsIgnoreCase("yes");
}
private static void showSalaries(Connection myConn, String theLanguage)
throws SQLException {
PreparedStatement myStmt = null;
ResultSet myRs = null;
System.out.println("Show Salaries for Department: " + theLanguage);
try {
// Prepare statement
myStmt = myConn
.prepareStatement("select * from students where language=?");
myStmt.setString(1, theLanguage);
// Execute SQL query
myRs = myStmt.executeQuery();
// Process result set
while (myRs.next()) {
String lastName = myRs.getString("last_name");
String firstName = myRs.getString("first_name");
double age = myRs.getDouble("age");
String language = myRs.getString("language");
System.out.printf("%s, %s, %s, %.2f\n", lastName, firstName,
language, age);
}
System.out.println();
} catch (Exception exc) {
exc.printStackTrace();
} finally {
close(myStmt, myRs);
}
}
private static void close(Connection myConn, Statement myStmt,
ResultSet myRs) throws SQLException {
if (myRs != null) {
myRs.close();
}
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
private static void close(Statement myStmt, ResultSet myRs)
throws SQLException {
close(null, myStmt, myRs);
}
}
Perform the following assignment to assess your learning
Write a java program to create a schema to store end semester results of the students. Filename should be SchemaCreation.java.
1. Import following packages in your program.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
2. Declare the following variables to interact with the database.
Connection myConn = null;
Statement myStmt = null;
ResultSet myRs = null;
3. Use the following parameters to connect with the database. The database should be launched at AWS.
Use the following statement to get a connection from the database.
myConn = DriverManager.getConnection(url, username , password);
Your program should display "Database connection successful!"
4. Use the following script to create a table
"CREATE TABLE Demo.table_name (
SemId INT NOT NULL AUTO_INCREMENT primary key,
SGPA DECIMAL(10,2) not null,
CGPA DECIMAL(10,2) DEFAULT NULL,
class varchar(10) not null)"
where table_name must be your ID no.
boolean tableCreated = myStmt.execute(Script for creating a table);
After creating a table your program should display the message "Table is created."
Write a program to store student's end semester results in the table created earlier. Save the file with the name InsertRecord.java
Set auto-commit off before inserting the data using setAutoCommit method of Connection.
Input your end semester results in the table. You must have to use a prepared Statement to input the academic information.
Write a program to add the column in the table created earlier. Save the file with the name AlterTable.java
Use the following query to alter the table definition.
ALTER TABLE `table_name` ADD COLUMN `marksheet` `blob`;
Update the image of mark sheets of each semester in the mark sheet column of the table.
Write a program to display SGPA of the students for a given semester. Save the file with the name DisplaySGPA.java
1. Write a pl/sql block to find the sgpa of a semester. Display the result through your program. The procedure should have one IN (semester) and one OUT (SGPA) parameter.
Write a program to display information related databases and tables. Save the file with the name Metadata.java
Your program should print database vendor name and total no. of rows in your table through metadata interface. (Use the aggregate function like count to get no. of rows)
