JDBC Operation using
Scala
Here, we will discuss about how to do basic
database operations (CRUD - Create, Retrieve, Update and Delete) using JDBC
(Java Database Connectivity) API. These CRUD operations are equivalent to the
INSERT, SELECT, UPDATE and DELETE statements in MySQL language
If you face any configuration issue/s while performing Jdbc connectivity please follow Jdbc Connecivity using Scala link.
***No need
to load MySQL driver class explicitly
The Connector/J library comes with
a JDBC driver class: com.mysql.jdbc.Driver. Before Java 6, we have to
load the driver explicitly by this statement: Class.forName("com.mysql.jdbc.Driver");
However that statement is no
longer needed, thanks to new update in JDBC 4.0 comes from Java 6. As long as
you put the mysql-connector-java-5.1.21-bin.jar file into your
program’s classpath, the JDBC’s driver manager can find and load the driver.
Creating a sample MySQL database
Let’s create a MySQL database called SampleDB with one table name as
Sample.
Execute the
following SQL script inside MySQL Workbench:
create database SampleDB; //Creating database SampleDB
use SampleDB; //Use or perform operation on SampleDB
create table Sample( //Create table Sample with columns
id
INTEGER not NULL,
first
VARCHAR(255) not null,
last
VARCHAR(255),
age
INTEGER,
PRIMARY
KEY ( id )
)
Now create table using scala :
//Code
package TScala_JDBC
import java.sql.{Connection, DriverManager}
object TScala_Jdbc_Val {
def main(args:Array[String]): Unit ={
// connect to the database named "mysql" on the
localhost
val driver = "com.mysql.jdbc.Driver"
//driver for connecting mysql db
val url = "jdbc:mysql://url_of_mysql_db"
//url for accessing database
val username = "username"
//username
val password = "******"
//password
// there's probably a better way to do this
var connection:Connection = null
//initialize connection with null value
try{
// make the connection
Class.forName(driver)
connection = DriverManager.getConnection(url,
username, password)
// create the statement, and run the select query
val statement = connection.createStatement()
statement.executeUpdate("create table Sample(id
INTEGER NOT NULL, first VARCHAR(255) not null, last VARCHAR(255), age INTEGER,
PRIMARY KEY ( id ))")
connection.commit()
}
catch{
case e => e.printStackTrace
}
}
}
Executing SELECT statement
Execute the following SQL script inside MySQL Workbench:
use SampleDB
SELECT * FROM Sample;
Now Read table using scala :
package TScala_JDBC
import java.sql.{Connection, DriverManager}
object TScala_Jdbc_Val {
def main(args:Array[String]): Unit ={
// connect to the database named "mysql" on the
localhost
val driver = "com.mysql.jdbc.Driver"
//driver for connecting
mysql db
val url = "jdbc:mysql:://url_address_db" //url
of db
val username = "username"
//username
val password = "*******"
//password
// there's probably a better way to do this
var connection:Connection = null
//initialize connection with null value
try{
// make the connection
Class.forName(driver)
connection = DriverManager.getConnection(url,
username, password)
// create the statement, and run the select query
val statement = connection.createStatement()
//store result in a resultset
val resultSet = statement.executeQuery("SELECT
* From Sample")
while ( resultSet.next() ) {
val id = resultSet.getInt("id")
val fname =
resultSet.getString("first")
val lname =
resultSet.getString("last")
val age = resultSet.getInt("age")
println( + id + ", " + name +
"," + age)
}
connection.commit()
}
catch{
case e => e.printStackTrace
}
}
}
Executing INSERT
statement
Execute the following SQL script inside MySQL Workbench:
use SampleDB;
insert into Sample(id, first, last, age) values (1,'U','Veri',37),
(2,'P','Meri',27)
Now insert row values
in table using scala :
//Code
package TScala_JDBC
import java.sql.{Connection, DriverManager}
object TScala_Jdbc_Val {
def main(args:Array[String]): Unit ={
// connect to the database named "mysql" on the
localhost
val driver = "com.mysql.jdbc.Driver"
//driver for connecting
mysql db
val url = "jdbc:mysql://url_address_db" //url of
db
val username = "username"
//username
val password = "*******"
//password
// there's probably a better way to do this
var connection:Connection = null
//initialize connection with null value
try{
// make the connection
Class.forName(driver)
connection = DriverManager.getConnection(url,
username, password)
// create the statement, and run the select query
val statement = connection.createStatement()
//store result in a resultset
statement.executeUpdate("insert into
Sample(id, first, last, age) values (1,'U','Veri',37), (2,'P','Meri',27)")
connection.commit()
}
catch{
case e => e.printStackTrace
}
}
}
Executing Delete
statement
Execute the following SQL script inside MySQL Workbench:
use SampleDB;
Delete from Sample where id = 1
Now delete row from
table using scala :
//Code
package TScala_JDBC
import java.sql.{Connection, DriverManager}
object TScala_Jdbc_Val {
def main(args:Array[String]): Unit ={
// connect to the
database named "mysql" on the localhost
val driver = "com.mysql.jdbc.Driver"
//driver for connecting
mysql db
val url = "jdbc:mysql://url_address_db" //url of
db
val username = "username"
//username
val password = "*******"
//password
// there's probably a better way to do this
var connection:Connection = null
//initialize connection with null value
try{
// make the connection
Class.forName(driver)
connection = DriverManager.getConnection(url,
username, password)
// create the statement, and run the select query
val statement = connection.createStatement()
//store result in a resultset
statement.executeUpdate("Delete from Sample
where id = 1")
connection.commit()
}
catch{
case e
=> e.printStackTrace
}
}
}
Executing Update
statement
Execute the following SQL script inside MySQL Workbench:
use SampleDB;
Update Sample set first='Meri' where id=2
Now update row from
table using scala :
package TScala_JDBC
import java.sql.{Connection, DriverManager}
object TScala_Jdbc_Val {
def main(args:Array[String]): Unit ={
// connect to the database named "mysql" on the
localhost
val driver = "com.mysql.jdbc.Driver"
//driver for connecting
mysql db
val url = "jdbc:mysql://path_of_db" //url of db
val username = "umeshpm544"
//username
val password = "umeshpm544"
//password
// there's probably a better way to do this
var connection:Connection = null
//initialize connection with null value
try{
// make the connection
Class.forName(driver)
connection = DriverManager.getConnection(url,
username, password)
// create the statement, and run the select query
val statement = connection.createStatement()
statement.executeUpdate("Update Sample set
first='Meri' where id=2")
connection.commit()
}
catch{
case e => e.printStackTrace
}
}
}
No comments:
Post a Comment