Multipart Task (1/4) - Spring Data Access - Plain JDBC - Using Controller and Utility Classes #36
akash-coded
started this conversation in
Tasks
Replies: 15 comments
package com.jdbc.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcApplication {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
final String url = "jdbc:mysql://localhost:3306/jdbc_demo";
final String username = "root";
final String password = "password";
try (Connection connection = DriverManager.getConnection(url, username, password)) {
System.out.println("Connected to database server " + connection.getMetaData().getDatabaseProductName()
+ " version: " + connection.getMetaData().getDatabaseProductVersion());
// Create/Insert data in the DataBase Table - USER
System.out.println("Creating/Inserting data into the Table");
String query = "insert into USER values(?,?,?)";
try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
preparedStatement.setInt(1, 1001);
preparedStatement.setString(2, "Goutham");
preparedStatement.setString(3, "Gunji");
preparedStatement.execute();
System.out.println("A record is successfully inserted in DB");
preparedStatement.setInt(1, 1002);
preparedStatement.setString(2, "James");
preparedStatement.setString(3, "Andrew");
preparedStatement.execute();
System.out.println("A record is successfully inserted in DB");
}
// Read or Retrieve the data from DataBase - USER
try (Statement statement = connection.createStatement()) {
// read the full Table
query = "select * from USER";
ResultSet resultSet = statement.executeQuery(query);
System.out.println("Data in the Table :");
printResultset(resultSet);
// read the single data
query = """
Select *
from USER
where id = ?""";
try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
preparedStatement.setInt(1, 101);
System.out.println(" Read Data with specific ID : 101 ");
if (resultSet.next() == false) {
System.out.println("No Such Data exists");
} else {
resultSet = preparedStatement.executeQuery();
printResultset(resultSet);
}
}
}
// Update the Data in Database Table - USER
System.out.println("Updating the Table data :");
query = """
Update USER
set first_name = ?
where id = ?""";
try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
preparedStatement.setString(1, "Colin");
preparedStatement.setInt(2, 1002);
int rowsUpdated = preparedStatement.executeUpdate();
if (rowsUpdated > 0)
System.out.println("Rows Updated : " + rowsUpdated);
else
System.out.println("No data is updated");
}
// Delete the data from Table - USER
System.out.println("Deleting the date from Table");
query = """
delete from USER where id = ?""";
try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
preparedStatement.setInt(1, 1001);
int rowsDeleted = preparedStatement.executeUpdate();
if (rowsDeleted > 0)
System.out.println("Rows Updated : " + rowsDeleted);
else
System.out.println("No data is deleted");
}
}
}
private static void printResultset(ResultSet resultSet) throws SQLException {
// TODO Auto-generated method stub
while (resultSet.next()) {
int id = resultSet.getInt("id");
String firstName = resultSet.getString("first_name");
String lastName = resultSet.getString("last_name");
System.out.println("id : " + id + " firstName : " + firstName + " lastName : " + lastName);
}
}
} |
0 replies
BeanUser.javapackage com.jdbc.singleton.demo.beans;
public class User {
private int id;
private String firstName;
private String lastName;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public User(int id, String firstName, String lastName) {
super();
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
}
public User() {
super();
}
@Override
public String toString() {
return "User [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + "]";
}
}Database Connectionpackage com.jdbc.singleton.demo.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseConnection {
private Connection connection = null;
public DatabaseConnection(String url, String userName, String password) throws SQLException {
super();
this.connection = DriverManager.getConnection(url, userName, password);
}
public Connection getConnection() {
return connection;
}
}
CRUD Controllerpackage com.jdbc.singleton.demo.controller;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.jdbc.singleton.demo.beans.User;
import com.jdbc.singleton.demo.util.DatabaseConnection;
public class CrudController {
private String query;
private Connection connection;
public CrudController(DatabaseConnection dbConnection) {
super();
this.connection = dbConnection.getConnection();
}
public String create(User user) throws Exception {
System.out.println("Connected to database server " + connection.getMetaData().getDatabaseProductName()
+ " version: " + connection.getMetaData().getDatabaseProductVersion());
query = "insert into USER values(?,?,?)";
try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
preparedStatement.setInt(1, user.getId());
preparedStatement.setString(2, user.getFirstName());
preparedStatement.setString(3, user.getLastName());
if (!preparedStatement.execute()) {
return "Record inserted successfully";
} else {
return "Error in inserting the data";
}
}
}
public ResultSet readAll() throws Exception {
query = "select * from USER";
Statement statement = connection.createStatement();
return statement.executeQuery(query);
}
public String update(int id, String lastName) throws Exception {
connection = DatabaseConnection.getConnection();
query = """
update USER
set last_name = ?
where id=?""";
try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
preparedStatement.setInt(2, id);
preparedStatement.setString(1, lastName);
int i = preparedStatement.executeUpdate();
if (i > 0)
return "No.of Rows updated : " + i;
else
return "no specific Data exist to update";
}
}
public String delete(int id) throws Exception {
connection = DatabaseConnection.getConnection();
query = """
delete from
USER
where id = ?""";
try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
preparedStatement.setInt(1, id);
if (!preparedStatement.execute())
return "Record deleted successfully";
else
return "Unable to delete data or Data already deleted";
}
}
}ApplicationContext.xml<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<bean id="DBConnection"
class="com.jdbc.singleton.demo.util.DatabaseConnection"
scope="singleton" lazy-init="true">
<constructor-arg index="0"
value="jdbc:mysql://localhost:3306/jdbc_demo"></constructor-arg>
<constructor-arg index="1" value="root"></constructor-arg>
<constructor-arg index="2" value="password"></constructor-arg>
</bean>
<bean id="crudController"
class="com.jdbc.singleton.demo.controller.CrudController">
<constructor-arg index="0" ref="DBConnection"></constructor-arg>
</bean>
</beans>
Driver Classpackage com.jdbc.singleton.demo;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.jdbc.singleton.demo.beans.User;
import com.jdbc.singleton.demo.controller.CrudController;
public class Application {
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
AbstractApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
CrudController controller = context.getBean("crudController", CrudController.class);
// Performing the CRUD Operations
// 1. create data into the User Table
User user = new User(101, "Lakshya", "S");
System.out.println(controller.create(user));
// 2. Read the entire data in data base
ResultSet rs = controller.readAll();
displayResultset(rs);
// 3. Update the User Last name for specific Id
int id = 101;
String lastName = "Sen";
System.out.println(controller.update(id, lastName));
// 4. delete the data in table with specific id
int deleteId = 101;
System.out.println(controller.delete(deleteId));
}
private static void displayResultset(ResultSet resultSet) throws SQLException {
// TODO Auto-generated method stub
while (resultSet.next()) {
int id = resultSet.getInt("id");
String firstName = resultSet.getString("first_name");
String lastName = resultSet.getString("last_name");
User user = new User(id, firstName, lastName);
System.out.println(user);
}
}
} |
0 replies
Crud Controller
public class CrudController {
private static final String INSERT_USERS_SQL = "INSERT INTO USER" + " (id, name, age) VALUES " + " (?, ?, ?);";
private static final String SELECT_USERS_SQL = "SELECT id, name, age FROM USER WHERE id=?;";
private static final String UPDATE_USERS_SQL = "UPDATE USER SET name=? WHERE id=?;";
private static final String DELETE_USERS_SQL = "DELETE FROM USER WHERE id=?;";
PreparedStatement preparedStatement = null;
static Connection connection;
static {
try {
connection = DatabaseConnection.getConnection();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public void create() {
try {
preparedStatement = connection.prepareStatement(INSERT_USERS_SQL);
preparedStatement.setInt(1, 2);// id
preparedStatement.setString(2, "Poorani");// name
preparedStatement.setInt(3, 23);// age
System.out.println(preparedStatement);
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void read() {
try {
preparedStatement = connection.prepareStatement(SELECT_USERS_SQL);
preparedStatement.setInt(1, 1);// id
ResultSet rs = preparedStatement.executeQuery();
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
System.out.println("User Details:[" + id + " ," + name + " ," + age + "]");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}}
}
public void update() {
try {
preparedStatement = connection.prepareStatement(UPDATE_USERS_SQL);
preparedStatement.setString(1, "Nisha");// name
preparedStatement.setInt(2, 1);// id
System.out.println(preparedStatement);
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}}
}
public void delete() {
try {
preparedStatement = connection.prepareStatement(DELETE_USERS_SQL);
preparedStatement.setInt(1, 2);// id
System.out.println(preparedStatement);
preparedStatement.executeUpdate();
``
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}}
}
}
DataBase Connectionpublic class DatabaseConnection {
private static String url;
private static String username;
private static String password;
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public DatabaseConnection() {
super();
}
@Override
public String toString() {
return "DatabaseConnection [url=" + url + ", username=" + username + ", password=" + password + "]";
}
public DatabaseConnection(String url, String username, String password) {
super();
this.url = url;
this.username = username;
this.password = password;
}
public static Connection getConnection() throws `ClassNotFoundException{`
Class.forName("com.mysql.cj.jdbc.Driver");
try {
Connection connection= DriverManager.getConnection(url, username, password);
return connection;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}ApplicationContext<beans xmlns = "http://www.springframework.org/schema/beans"
xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
xmlns:context = "http://www.springframework.org/schema/context"
xsi:schemaLocation = "http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<bean id="databaseConnection" class="com.spring.main.util.DatabaseConnection">
<property name="url" value= "jdbc:mysql://localhost:3306/mydb"></property>
<property name="username" value= "root"></property>
<property name="password" value= "admin@123"></property>
</bean>
</beans>Applicationpublic class Application {
public static void main(String[] args) throws SQLException {
AbstractApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
context.start();
DatabaseConnection connection = context.getBean("databaseConnection", DatabaseConnection.class);
CrudController controller =new CrudController();
controller.create();
controller.read();
controller.update();
controller.delete();
context.close();
}
}
|
0 replies
|
//Visweshwaran M CrudControllerCrudController.javapublic class CrudController {
private Connection connection;
public CrudController(DatabaseConnection databaseConnection) {
this.connection = databaseConnection.getConnection();
}
public void read() throws SQLException {
try {
if (this.connection != null) {
try (Statement statement = connection.createStatement()) {
// Read (Retrieve) Operation using Statement
String sql = "SELECT * FROM worker order by JOINING_DATE desc limit 3";
ResultSet result = statement.executeQuery(sql);
while (result.next()) {
System.out.println("######################");
String id = result.getString("WORKER_ID");
String firstName = result.getString("FIRST_NAME");
String dept = result.getString("DEPARTMENT");
System.out.println(new StringJoiner("\t\t").add(id).add(firstName).add(dept)
.add(result.getString("JOINING_DATE")));
}
System.out.println("***** READ OPERATION COMPLETED ******");
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
public void create() {
try {
if (connection == null) {
return;
}
String insertSql = """
INSERT INTO Worker (
FIRST_NAME,
LAST_NAME,
SALARY,
JOINING_DATE,
DEPARTMENT)
VALUES (
'Steve', 'Rogers', '1000', NOW(), 'Experiment'
)""";
try (Statement statement = connection.createStatement()) {
System.out.println("ADDING NEW RECORD FOR STEVE ROGERS ... ");
statement.executeUpdate(insertSql);
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("***** CREATE OPERATION COMPLETED ******");
} catch (Exception e) {
e.printStackTrace();
}
}
public void update() {
try {
if (connection == null) {
return;
}
String updateSql1 = """
UPDATE Worker
SET
FIRST_NAME = ?
WHERE
LAST_NAME='Rogers'""";
try (PreparedStatement preparedStatement = connection.prepareStatement(updateSql1)) {
System.out.println("UPDATING NEW RECORD FOR STEVE --> 'STEVE PAUL' ... ");
preparedStatement.setString(1, "Steve Paul");
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("***** UPDATE OPERATION COMPLETED ******");
} catch (Exception e) {
e.printStackTrace();
}
}
public void delete() {
try {
if (connection == null) {
return;
}
// Delete Operation using Statement
String deleteSql = """
DELETE FROM Worker
WHERE
WORKER_ID > 10 limit 1""";
try (Statement statement = connection.createStatement()) {
int rowsDeleted = statement.executeUpdate(deleteSql);
System.out.println(rowsDeleted + " rows deleted.");
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("***** Delete OPERATION COMPLETED ******");
} catch (Exception e) {
e.printStackTrace();
}
}
}DatabaseConnection.javapublic class DatabaseConnection {
private Connection connection;
private final String url;
private final String username;
private final String password;
public DatabaseConnection(String url, String username, String password) throws SQLException {
super();
this.url = url;
this.username = username;
this.password = password;
this.connection = DriverManager.getConnection(url, username, password);
}
public Connection getConnection() {
return connection;
}
private void preInitFunc() {
System.out.println("Called DatabaseConnection.preInitFunc()");
if (this.connection != null) {
System.out.println("MySQL Connection establised successfully");
} else
System.out.println("MySQL Connection failed to connect");
}
}Driverpublic class Application {
public static void main(String[] args) {
System.out.println("Application.main() started");
try (AbstractApplicationContext context = new ClassPathXmlApplicationContext("applicationContect.xml");) {
CrudController crudController = context.getBean("crudController", CrudController.class);
crudController.create();
crudController.read();
crudController.update();
crudController.read();
crudController.delete();
} catch (Exception e) {
e.printStackTrace();
}
}
}Application Context ConfigurationapplicationContect.xml<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<bean id="databaseConnection"
class="com.spring_maven_jdbc.util.DatabaseConnection"
scope="singleton" init-method="preInitFunc">
<constructor-arg index="0"
value="jdbc:mysql://localhost:3306/org"></constructor-arg>
<constructor-arg index="1" value="root"></constructor-arg>
<constructor-arg index="2"
value="#######################"></constructor-arg>
</bean>
<bean id="crudController"
class="com.spring_maven_jdbc.controllers.CrudController">
<constructor-arg index="0" ref="databaseConnection"></constructor-arg>
</bean>
</beans>
pom.xml<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.spring</groupId>
<artifactId>spring-maven-jdbc-2</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-maven-jdbc-2</name>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.2.9.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.2.9.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
</dependencies>
</project>
Screenshots
|
0 replies
MainApplicationpackage com.jdbc.crud.demo;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.jdbc.crud.beans.User;
import com.jdbc.crud.controllers.CrudController;
public class Application {
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
CrudController controller = new CrudController();
User user = new User(1009, "Kalai", "Raj");
System.out.println(controller.create(user));
ResultSet rs = controller.readAll();
displayResultset(rs);
int id = 1;
String lastName = "Raj";
System.out.println(controller.update(id, lastName));
}
private static void displayResultset(ResultSet resultSet) throws SQLException {
// TODO Auto-generated method stub
while (resultSet.next()) {
int id = resultSet.getInt("id");
String firstName = resultSet.getString("first_name");
String lastName = resultSet.getString("last_name");
User user = new User(id, firstName, lastName);
System.out.println(user);
}
}
} |
0 replies
|
|
0 replies
Plain JDBC ConnectionEmployee Classpackage spring.jdbc.demo.beans;
public class Employee {
private int emp_id;
private String firstName;
private String lastName;
public int getId() {
return emp_id;
}
public void setId(int id) {
this.emp_id = id;
}
public String getFirstNmae() {
return firstName;
}
public void setFirstNmae(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public Employee() {
super();
}
public Employee(int id, String firstName, String lastName) {
super();
this.emp_id = id;
this.firstName = firstName;
this.lastName = lastName;
}
@Override
public String toString() {
return "Employee [id=" + emp_id + ", firstNmae=" + firstName + ", lastName=" + lastName + "]";
}
}Database Connection Classpackage spring.jdbc.demo.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class DatabaseConnection {
private static Connection con = null;
private String url;
private String userName;
private String password;
private Connection connection = null;
DatabaseConnection(String url, String username, String password) throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.cj.jdbc.Driver");
this.connection = DriverManager.getConnection(url, username, password);
}
private void printConnectionDetails() throws SQLException {
System.out.println("Connected to database server "
+ this.connection.getMetaData().getDatabaseProductName()
+ " version: "
+ this.connection.getMetaData().getDatabaseProductVersion()
+ "\n");
}
public Connection getConnection() {
return connection;
}
}CRUD Controller Classpackage spring.jdbc.demo.controller;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import spring.jdbc.demo.util.DatabaseConnection;
public class CRUDController {
private final Connection con;
public CRUDController(DatabaseConnection connection) throws SQLException {
con = connection.getConnection();
}
public void createEmployee() {
String INSERT_Query = "INSERT INTO employee_details" + " VALUES " + " (?, ?, ?);";
try (PreparedStatement preparedStatement = con.prepareStatement(INSERT_Query)) {
preparedStatement.setInt(1, 5);
preparedStatement.setString(2, "Akshay");
preparedStatement.setString(3, "Kumar");
System.out.println(preparedStatement);
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void readEmployees() {
String SELECT_Query = "SELECT * FROM employee_details WHERE emp_id=?;";
try (PreparedStatement preparedStatement = con.prepareStatement(SELECT_Query)) {
preparedStatement.setInt(1, 1);
ResultSet rs = (ResultSet) preparedStatement.executeQuery();
while (rs.next()) {
int id = rs.getInt("emp_id");
String first_name = rs.getString("first_name");
String last_name = rs.getString("last_name");
System.out.println("Employee Details:[" + id + " ," + first_name + " ," + last_name + "]");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void updateEmployeeDetails() {
String UPDATE_Query = "UPDATE employee_details SET first_name=? WHERE emp_id=?;";
try (PreparedStatement preparedStatement = con.prepareStatement(UPDATE_Query)) {
preparedStatement.setString(1, "Rithik");
preparedStatement.setInt(2, 3);
System.out.println(preparedStatement);
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void deleteEmployee() {
String DELETE_Query = "DELETE FROM employee_details WHERE emp_id=?;";
try (PreparedStatement preparedStatement = con.prepareStatement(DELETE_Query)) {
preparedStatement.setInt(1, 2);// id
System.out.println(preparedStatement);
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
}Application Context.xml<beans xmlns = "http://www.springframework.org/schema/beans"
xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
xmlns:context = "http://www.springframework.org/schema/context"
xsi:schemaLocation = "http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<bean id = "DBConnection" class = "spring.jdbc.demo.util.DatabaseConnection" scope="singleton" lazy-init="true" init-method="printConnectionDetails">
<constructor-arg index="0" value="jdbc:mysql://localhost:3306/employee"></constructor-arg>
<constructor-arg index="1" value="root"></constructor-arg>
<constructor-arg index="2" value="root"></constructor-arg>
</bean>
<bean id = "crudController" class = "spring.jdbc.demo.controller.CRUDController" scope = "prototype">
<constructor-arg index = "0" ref = "DBConnection"></constructor-arg>
</bean>
</beans>Main Application Classpackage spring.jdbc.demo;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import spring.jdbc.demo.controller.CRUDController;
public class Application {
public static void main(String[] args) {
AbstractApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
System.out.println("our Spring App");
CRUDController controller = context.getBean("crudController", CRUDController.class);
controller.createEmployee();
controller.readEmployees();
controller.updateEmployeeDetails();
controller.deleteEmployee();
}
}Screenshot
|
0 replies
Crud Controllerpublic class CrudController {
private final Connection connection;
public CrudController(DatabaseConnection connection) {
super();
this.connection = connection.getConnection();
}
public void create() {
String INSERT_USERS_SQL = """
INSERT INTO Worker
(WORKER_ID,
FIRST_NAME,
LAST_NAME,
SALARY,
JOINING_DATE,
DEPARTMENT)
VALUES (?, ?, ?,?,?,?);""";
try (PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
preparedStatement.setString(1, "10001");
preparedStatement.setString(2, "Karthika");
preparedStatement.setString(3, "Karikalan");
preparedStatement.setString(4, "100000");
preparedStatement.setString(5, "07/04/2022");
preparedStatement.setString(6, "Engineer");
preparedStatement.executeUpdate();
System.out.println("Record created Successfully");
} catch (SQLException e) {
e.printStackTrace();
}
}
public void read() {
String selectFormat = """
SELECT *
FROM
Worker
WHERE
WORKER_ID > ?""";
try (PreparedStatement preparedStatement = connection.prepareStatement(selectFormat)) {
preparedStatement.setString(1, "10001");
ResultSet rs = preparedStatement.executeQuery();
if (!rs.isBeforeFirst()) {
System.out.println("no rows found");
} else {
int columnCount = rs.getMetaData().getColumnCount();
System.out.println("Columns and Types::");
for (int i = 0; i < columnCount; i++) {
System.out.print(rs.getMetaData().getColumnName(i + 1) + ":"
+ rs.getMetaData().getColumnTypeName(i + 1) + "\t");
}
System.out.println("\nRecords::");
while (rs.next()) {
for (int i = 1; i < columnCount + 1; i++) {
System.out.print(rs.getObject(i) + "\t");
}
System.out.println();
}
}
} catch (SQLException e) {
System.out.println("Exception occurred in read() method of CrudController\n" + e);
}
}
public void update() {
String UPDATE_USERS_SQL = """
UPDATE
Worker
SET FIRST_NAME=?
WHERE
WORKER_ID=?
""";
try (PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_USERS_SQL)) {
preparedStatement.setString(1, "Thiru");// name
preparedStatement.setString(2, "10001");// id
preparedStatement.executeUpdate();
System.out.println("No of updated row:1");
} catch (SQLException e) {
e.printStackTrace();
}
}
public void delete() {
String DELETE_USERS_SQL = """
DELETE
FROM
Worker
WHERE
WORKER_ID=? """;
try (PreparedStatement preparedStatement = connection.prepareStatement(DELETE_USERS_SQL)) {
preparedStatement.setString(1, "10001");// id
preparedStatement.executeUpdate();
System.out.println("1 Row Deleted");
} catch (SQLException e) {
e.printStackTrace();
}
}
}DatabaseConnectionpublic class DatabaseConnection {
private Connection connection = null;
DatabaseConnection(String url, String username, String password) throws SQLException {
super();
this.connection = DriverManager.getConnection(url, username, password);
}
private void printConnectionDetails() throws SQLException {
System.out.println("Connected to database server "
+ this.connection.getMetaData().getDatabaseProductName()
+ " version: "
+ this.connection.getMetaData().getDatabaseProductVersion()
+ "\n");
}
public Connection getConnection() {
return connection;
}
}ApplicationContext<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<bean id="databaseConnection" class="com.spring.mysql.connector.demo.util.DatabaseConnection" scope="singleton" lazy-init="true" init-method="printConnectionDetails">
<constructor-arg index="0" value="jdbc:mysql://localhost:3306/mydb"></constructor-arg>
<constructor-arg index="1" value="root"></constructor-arg>
<constructor-arg index="2" value="admin@123"></constructor-arg>
</bean>
<bean id="crudController" class="com.spring.mysql.connector.demo.controller.CrudController" scope="prototype">
<constructor-arg index="0" ref="databaseConnection"></constructor-arg>
</bean>
</beans>Applicationpublic class MySqlConnectorApplication {
public static void main(String[] args) {
AbstractApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
context.start();
System.out.println("Spring JDBC app with MySQL Connector works!\n");
CrudController crudController = context.getBean("crudController", CrudController.class);
crudController.create();
crudController.read();
crudController.update();
crudController.delete();
context.close();
}
}
|
0 replies
CrudControllerimport java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import com.spring.maven.demo.model.Emp;
import com.spring.maven.demo.util.CurdRowMapper;
import com.spring.maven.demo.util.DatabaseConnection;
import com.spring.maven.demo.util.JdbcConnection;
public class CrudController {
private final Connection connection;
public CrudController(DatabaseConnection connection) throws Exception {
super();
this.connection = connection.getConnection();
}
public void read() {
// Read (Retrieve) Operation using PreparedStatement
String selectFormat = "select * from emp where eid>?";
try (PreparedStatement preparedStatement = connection.prepareStatement(selectFormat)) {
preparedStatement.setInt(1, 3);
ResultSet rs = preparedStatement.executeQuery();
if (!rs.isBeforeFirst()) {
System.out.println("no rows found");
} else {
int columnCount = rs.getMetaData().getColumnCount();
System.out.println("Columns and Types::");
for (int i = 0; i < columnCount; i++) {
System.out.print(rs.getMetaData().getColumnName(i + 1) + ":"
+ rs.getMetaData().getColumnTypeName(i + 1) + "\t");
}
System.out.println("\nRecords::");
while (rs.next()) {
for (int i = 1; i < columnCount + 1; i++) {
System.out.print(rs.getObject(i) + "\t");
}
System.out.println();
}
}
} catch (SQLException e) {
System.out.println("Exception occurred in read() method of CrudController\n" + e);
}
}
public void create( Emp emp) {
String sql="insert into Emp(eid,eName) values (?,?)";
try (PreparedStatement preparedStatement=connection.prepareStatement(sql)){
preparedStatement.setInt(1, emp.getEid());
preparedStatement.setString(2, emp.geteName());
preparedStatement.execute();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public int update(int eid,String ename) throws Exception {
String sql="update Emp set ename=? where eid=?";
try (PreparedStatement preparedStatement=connection.prepareStatement(sql)){
preparedStatement.setString(1, ename);
preparedStatement.setInt(2, eid);
return preparedStatement.executeUpdate();
}
}
public void delete(int eid) throws SQLException, Exception {
String sql="delete from Emp where eid=?";
try (PreparedStatement preparedStatement=connection.prepareStatement(sql)){
preparedStatement.setInt(1, eid);
preparedStatement.execute();
}
}
}###Main class import org.springframework.context.support.AbstractApplicationContext; import com.spring.foobar.beans.Bar; public class Application {
public static void main(String[] args) throws Exception {
AbstractApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
context.start();
System.out.println("Spring JDBC app with MySQL Connector works!\n");
CrudController crudController = context.getBean("crudController", CrudController.class);
Emp emp=new Emp();
emp.setEid(11122);
emp.seteName("krishna");
crudController.create(emp);
crudController.update(11, "a");
crudController.delete(11);
crudController.read();
context.close();
}
} |
0 replies
BeansDatabaseConnection.javapackage com.spring.mysql.connector;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseConnection
{
static Connection connection;
public static Connection connection() throws SQLException
{
try {
Class.forName("com.mysql.cj.jdbc.Driver");
// create the connection
String url="jdbc:mysql://localhost:3306/pratice";
String user="root";
String password="papun";
connection=DriverManager.getConnection(url,user,password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
}CurdController.javapackage com.spring.mysql.curd.controller;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import com.spring.mysql.connector.DatabaseConnection;
public class CurdController
{
String query;
PreparedStatement statement;
Scanner sc=new Scanner(System.in);
// TODO Auto-generated method stub
public void insert() throws SQLException
{
Connection connection=DatabaseConnection.connection();
query="insert into demo(name,address) values(?,?)";
System.out.println("For insert..");
System.out.println("Enter name:");
String name1=sc.next();
System.out.println("Enter address:");
String address1=sc.next();
statement=connection.prepareStatement(query);
statement.setString(1, name1);
statement.setString(2, address1);
statement.executeUpdate();
System.out.println("inserted");
}
public void update() throws SQLException
{
Connection connection=DatabaseConnection.connection();
System.out.println("Enter the id which you want to update:");
int id=sc.nextInt();
System.out.println("Enter a new name:");
String name=sc.next();
System.out.println("Enter a new address:");
String address=sc.next();
//update
System.out.println();
query="update demo set name=?,address=? where id=?";
statement=connection.prepareStatement(query);
statement.setString(1, name);
statement.setString(2, address);
statement.setInt(3, id);
statement.executeUpdate();
System.out.println("Done");
}
public void delete() throws SQLException
{
Connection connection=DatabaseConnection.connection();
//delete
System.out.println();
query="delete from demo where id=?";
System.out.println("Enter the id which you want to delete:");
int id=sc.nextInt();
statement=connection.prepareStatement(query);
statement.setInt(1, id);
statement.executeUpdate();
System.out.println("Deleted");
}
public void read() throws SQLException
{
Connection connection=DatabaseConnection.connection();
//read
System.out.println();
query="select *from demo"; statement=connection.prepareStatement(query);
ResultSet resultSet=statement.executeQuery();
while(resultSet.next())
{
System.out.println();
System.out.println("*****************************");
System.out.println("Name: "+resultSet.getString("name"));
System.out.println("Address: "+resultSet.getString("address"));
System.out.println("*****************************");
}
connection.close();
}
}MySqlConnectorApplication.javapackage com.spring.mysql.MySQLapplication;
import java.sql.SQLException;
import java.util.Scanner;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.spring.mysql.curd.controller.CurdController;
public class MySqlConnectorApplication
{
public static void main(String[] args) throws SQLException
{
AbstractApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");
context.start();
System.out.println("Spring JDBC app with mysql connector");
CurdController curdController=context.getBean("curdController",CurdController.class);
while(true)
{
System.out.println("press 1 for read \t press 2 for insert \t press 3 for delete \t press 4 for update \t press 5 for Exit");
System.out.println("Enter your choice: ");
Scanner scanner=new Scanner(System.in);
int key=scanner.nextInt();
switch (key) {
case 1:
curdController.read();
break;
case 2:
curdController.insert();
break;
case 3:
curdController.delete();
break;
case 4:
curdController.update();
break;
case 5:
System.exit(0);
break;
default:
System.out.println("Wrong option");
break;
}
}
}
}Driver// code Application Context Configuration<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<bean id="databaseConnection" class="com.spring.mysql.connector.DatabaseConnection" scope="singleton" lazy-init="true" init-method="printConnectionDetails"/>
<bean id="curdController" class="com.spring.mysql.curd.controller.CurdController" scope="prototype">
</bean>
</beans>pom.xml<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.spring.mysql.connector</groupId>
<artifactId>spring-with-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-with-jdbc</name>
<description>my sql connector app perfroming curd operation</description>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
</dependencies>
<properties>
<spring.version>5.2.9.RELEASE</spring.version>
</properties>
</project>Screenshots
|
0 replies
package com.spring.util.controllers;
import java.sql.*;
import com.spring.util.DatabaseConnection;
public class CrudController {
private final Connection connection;
public CrudController(DatabaseConnection connection) {
super();
this.connection = connection.getConnection();
}
public void delete() {
String deleteSql=("DELETE FROM Worker WHERE WORKER_ID = 8");
try (PreparedStatement preparedStatement = connection.prepareStatement(deleteSql)){
int rowsDeleted = preparedStatement.executeUpdate();
System.out.println(rowsDeleted + " rows deleted.");
System.out.println();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// public void create() {
// String insretSql=("INSERT INTO Worker (WORKER_ID,FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES (9, 'Tony', 'Stark', '50000000', NOW(), 'R&D')");
// try (PreparedStatement preparedStatement = connection.prepareStatement(insretSql)){
//
// int rowsInserted = preparedStatement.executeUpdate();
// System.out.println(rowsInserted + " rows inserted.");
// System.out.println();
//
// } catch (SQLException e) {
//
// e.printStackTrace();
// }
// }
public void read() {
// Read (Retrieve) Operation using PreparedStatement
String selectFormat = ("SELECT * FROM Worker WHERE WORKER_ID=?");
try (PreparedStatement preparedStatement = connection.prepareStatement(selectFormat))
{
preparedStatement.setInt(1, 3);
ResultSet rs = preparedStatement.executeQuery();
if (!rs.isBeforeFirst()) {
System.out.println("no rows found");
} else {
int columnCount = rs.getMetaData().getColumnCount();
System.out.println("Columns and Types::");
for (int i = 0; i < columnCount; i++) {
System.out.print(rs.getMetaData().getColumnName(i + 1) + ":"
+ rs.getMetaData().getColumnTypeName(i + 1) + "\t");
}
System.out.println("\nRecords::");
// System.out.println("WORKER_ID\tFIRST_NAME\tLAST_NAME\tSALARY\tJOINING_DATE\tDEPARTMENT");
while (rs.next()) {
for (int i = 1; i < columnCount + 1; i++) {
System.out.print(rs.getObject(i) + "\t");
}
System.out.println();
}
}
} catch (SQLException e) {
System.out.println("Exception occurred in read() method of CrudController\n" + e);
}
}
} |
0 replies
# Bean
# DatabaseConnector.java
package com.spring.jdbc.application.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseConnection {
private Connection connection = null;
public DatabaseConnection(String url, String username, String password) throws SQLException{
super();
this.connection = DriverManager.getConnection(url, username, password);
}
public void printConnectionDetails() throws SQLException {
System.out.println("Connected to database server:"
+this.connection.getMetaData().getDatabaseProductName()
+ "version: "
+this.connection.getMetaData().getDatabaseProductVersion());
}
public Connection getConnection() {
return connection;
}
}
# CrudController.java
package com.spring.jdbc.application.controllers;
import java.sql.*;
import com.spring.jdbc.application.util.DatabaseConnection;
public class CrudController {
private final Connection connection;
public CrudController(DatabaseConnection connection) {
super();
this.connection = connection.getConnection();
}
public void read() throws SQLException {
//Read operation using prepared statement
String selectFormat = """
SELECT *
FROM
studentdata
WHERE
roll_no = ?""";
PreparedStatement preparedStatement = connection.prepareStatement(selectFormat);
preparedStatement.setInt(1, 3);
ResultSet rs= preparedStatement.executeQuery();
if (!rs.isBeforeFirst()) {
System.out.println("No rows found");
}else {
int columnCount= rs.getMetaData().getColumnCount();
System.out.println("column and types::");
for (int i=0; i<columnCount; i++) {
System.out.println(rs.getMetaData().getColumnName(i+1) +":"
+rs.getMetaData().getColumnTypeName(i+1)+"\t");
}
System.out.println("\nRecords: ");
while (rs.next()) {
for (int i=1; i<=columnCount; i++) {
System.out.println(rs.getObject(i) +"\t");
}
System.out.println();
}
}
}
public void create() {
//create (insert) operation using prepared statement
String selectFormat = """
INSERT INTO
studentdata
VALUES(?,?,?)""";
try (PreparedStatement preparedStatement = connection.prepareStatement(selectFormat))
{
preparedStatement.setInt(1, 8);
preparedStatement.setString(2, "A");
preparedStatement.setString(3, "Supriya");
boolean rs= preparedStatement.execute();
System.out.println("Records inserted successfully ");
}
catch (SQLException e) {
System.out.println("Exception in create method of crud controller "+e);
e.printStackTrace();
}
}
public void update() {
//update (update/replace) operation using prepared statement
String selectFormat = """
UPDATE studentdata
SET
Student_name = 'Tony'
WHERE
roll_no=6 """;
try (PreparedStatement preparedStatement = connection.prepareStatement(selectFormat))
{
boolean rs= preparedStatement.execute();
System.out.println("Records updated successfully ");
}
catch (SQLException e) {
System.out.println("Exception in update method of crud controller "+e);
e.printStackTrace();
}
}
public void delete() {
//update (update/replace) operation using prepared statement
String selectFormat = """
DELETE FROM studentdata
WHERE
Student_name = 'Tony'
""";
try (PreparedStatement preparedStatement = connection.prepareStatement(selectFormat))
{
boolean rs= preparedStatement.execute();
System.out.println("Records DELETED successfully ");
}
catch (SQLException e) {
System.out.println("Exception in delete method of crud controller "+e);
e.printStackTrace();
}
}
}
# JdbcApplication.java
package com.spring.jdbc.application;
import java.sql.SQLException;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.spring.jdbc.application.controllers.*;
public class JdbcApplication {
public static void main(String[] args) throws SQLException {
AbstractApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
context.start();
CrudController crudController = context.getBean("crudController", CrudController.class);
crudController.read();
crudController.create();
crudController.update();
crudController.delete();
context.close();
}
}
# applicationContext.xml
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<bean id="databaseConnection" class="com.spring.jdbc.application.util.DatabaseConnection" scope="singleton" lazy-init="true" init-method="printConnectionDetails">
<constructor-arg index="0" value="jdbc:mysql://localhost:3306/newjavadb"></constructor-arg>
<constructor-arg index="1" value="root"></constructor-arg>
<constructor-arg index="2" value="kanika@17"></constructor-arg>
</bean>
<bean id="crudController" class="com.spring.jdbc.application.controllers.CrudController" scope="prototype">
<constructor-arg index="0" ref="databaseConnection"></constructor-arg>
</bean>
</beans>
|
0 replies
BeansDatabaseConnection.javaimport java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseConnection {
private Connection connection = null;
DatabaseConnection(String url, String username, String password) throws SQLException {
super();
this.connection = DriverManager.getConnection(url, username, password);
}
private void printConnectionDetails() throws SQLException {
System.out.println("Connected to database server "
+ this.connection.getMetaData().getDatabaseProductName()
+ " version: "
+ this.connection.getMetaData().getDatabaseProductVersion()
+ "\n");
}
public Connection getConnection() {
return connection;
}
}CrudController.javapackage com.spring.jdbc.controllers;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.spring.jdbc.util.DatabaseConnection;
public class CrudController {
private final Connection connection;
public CrudController(DatabaseConnection connection) {
super();
this.connection = connection.getConnection();
}
public void read() {
// Read (Retrieve) Operation using PreparedStatement
String selectFormat = """
SELECT *
FROM
routes """;
try (PreparedStatement preparedStatement = connection.prepareStatement(selectFormat)) {
//preparedStatement.setInt(1, 4);
ResultSet rs = preparedStatement.executeQuery();
if (!rs.isBeforeFirst()) {
System.out.println("no rows found");
} else {
System.out.println("Columns and Types::");
int columnCount = rs.getMetaData().getColumnCount();
for (int i = 0; i < columnCount; i++) {
System.out.print(rs.getMetaData().getColumnName(i + 1) + ":"
+ rs.getMetaData().getColumnTypeName(i + 1) + "\t");
}
System.out.println("\nRecords::");
while (rs.next()) {
for (int i = 1; i < columnCount + 1; i++) {
System.out.print(rs.getObject(i) + "\t");
}
System.out.println();
}
}
} catch (SQLException e) {
System.out.println("Exception occurred in read() method of CrudController\n" + e);
}
}
public void create() {
String query = "INSERT INTO routes(id, destination, dsnt, source, src) VALUES (?, ?, ?, ?, ?)";
try {
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setInt(1, 5);
pstmt.setString(2, "Mumbai");
pstmt.setString(3, "BOM");
pstmt.setString(4, "Hyderabad");
pstmt.setString(5, "HYD");
int count = pstmt.executeUpdate();
System.out.println(count + " row/s created");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void update() {
String sqlUpdate = "UPDATE routes "
+ "SET source = ? , src = ? "
+ "WHERE id = ?";
try {
PreparedStatement pstmt = connection.prepareStatement(sqlUpdate);
pstmt.setString(1, "Bangalore");
pstmt.setString(2, "BLR");
pstmt.setInt(3, 5);
int count = pstmt.executeUpdate();
System.out.println(count + " row/s updated");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void delete() {
String sqlDelete = "delete from routes where id=?";
try {
PreparedStatement pstmt = connection.prepareStatement(sqlDelete);
pstmt.setInt(1, 5);
int count = pstmt.executeUpdate();
System.out.println(count + " row/s deleted");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}Driverpackage com.spring.jdbc;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.spring.jdbc.controllers.CrudController;
public class MySqlConnectorApplication {
public static void main(String[] args) {
AbstractApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
context.start();
System.out.println("Spring JDBC app with MySQL Connector works!\n");
CrudController crudController = context.getBean("crudController", CrudController.class);
crudController.read();
System.out.println("-------------------------------------------------");
crudController.create();
System.out.println("-------------------------------------------------");
crudController.read();
System.out.println("-------------------------------------------------");
crudController.update();
System.out.println("-------------------------------------------------");
crudController.read();
System.out.println("-------------------------------------------------");
crudController.delete();
System.out.println("-------------------------------------------------");
crudController.read();
System.out.println("-------------------------------------------------");
context.close();
}
}Application Context Configuration<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<bean id="databaseConnection" class="com.spring.jdbc.util.DatabaseConnection" scope="singleton" lazy-init="true" init-method="printConnectionDetails">
<constructor-arg index="0" value="jdbc:mysql://localhost:3306/db"></constructor-arg>
<constructor-arg index="1" value="root"></constructor-arg>
<constructor-arg index="2" value="Sanju@123"></constructor-arg>
</bean>
<bean id="crudController" class="com.spring.jdbc.controllers.CrudController" scope="prototype">
<constructor-arg index="0" ref="databaseConnection"></constructor-arg>
</bean>
</beans>
|
0 replies
|
DataBaseConnection package com.spring.jdbcdemo.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DatabaseConnection {
private Connection connection ;
public DatabaseConnection(String url , String username ,String password) throws SQLException {
this.connection = DriverManager.getConnection(url, username, password);
}
@SuppressWarnings("unused")
private void getConnectionDetails() throws SQLException {
System.out.println("Connected to databse");
System.out.println(this.connection.getMetaData().getDatabaseProductName());
System.out.println(this.connection.getMetaData().getDatabaseProductVersion());
}
public Connection getConnection() {
return connection ;
}
}CrudController package com.spring.jdbcdemo.controllers;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.spring.jdbcdemo.util.DatabaseConnection;
public class CrudController {
private Connection connection ;
public CrudController(DatabaseConnection connection ) {
this.connection = connection.getConnection();
}
public void read() {
String sql = "Select * from HR.EMPLOYEES WHERE LAST_NAME LIKE ? AND FIRST_NAME LIKE ? ";
try {
PreparedStatement p_stmt = connection.prepareStatement(sql);
p_stmt.setString(1, "%r%");
p_stmt.setString(2, "%r%");
ResultSet rs = p_stmt.executeQuery();
if(rs.isBeforeFirst()) {
int col_count = rs.getMetaData().getColumnCount();
System.out.println(col_count);
}
while(rs.next()) {
int e_id = rs.getInt(1);
String e_name = rs.getString(2);
String e_lname = rs.getString(3);
System.out.println("E id : " + e_id + " E_name : " + e_name + " E_LName: " + e_lname);
}
}
catch (Exception e) {
e.printStackTrace();
}
}
public void update() {
String sql = "UPDATE HR.EMPLOYEES SET FIRST_NAME = ? , LAST_NAME = ? WHERE EMPLOYEE_ID = ? ";
try {
PreparedStatement p_stmt = connection.prepareStatement(sql);
p_stmt.setString(1, "Kevin");
p_stmt.setString(2, "De Bryune");
p_stmt.setInt(3, 104);
int updt_rows = p_stmt.executeUpdate();
if(updt_rows > 0 ) {
System.out.println(updt_rows + "Records updated successfully");
}
else {
System.out.println("Zero records got affected");
}
}
catch (Exception e) {
e.printStackTrace();
}
}
public void delete() {
String sql = "delete FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = ? ";
try {
PreparedStatement p_stmt = connection.prepareStatement(sql);
p_stmt.setInt(1, 307);
int updt_rows = p_stmt.executeUpdate();
if(updt_rows > 0 ) {
System.out.println(updt_rows + "Records DELETED successfully");
}
else {
System.out.println("Zero records got deleted");
}
}
catch (Exception e) {
e.printStackTrace();
}
}
}DriverClass package com.spring.jdbcdemo;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.spring.jdbcdemo.controllers.CrudController;
public class JdbcDemo {
public static void main(String[] args) {
AbstractApplicationContext context = new ClassPathXmlApplicationContext("application-context.xml");
context.start();
System.out.println("Context file configured");
CrudController control = context.getBean("crudcontroller" , CrudController.class);
control.read();
control.update();
control.delete();
context.close();
}
}ApplicationContext <beans xmlns = "http://www.springframework.org/schema/beans"
xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
xmlns:context = "http://www.springframework.org/schema/context"
xsi:schemaLocation = "http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<bean id = "databseconnection" class = "com.spring.jdbcdemo.util.DatabaseConnection" lazy-init="true" init-method="getConnectionDetails">
<constructor-arg index = "0" value="jdbc:oracle:thin:@localhost:1521:XE"/>
<constructor-arg index = "1" value="system"/>
<constructor-arg index = "2" value="manager"/>
</bean>
<bean id = "crudcontroller" class = "com.spring.jdbcdemo.controllers.CrudController" scope = "prototype">
<constructor-arg index = "0" ref="databseconnection"/>
</bean>
</beans>Output:
|
0 replies
BeansCarDao.java package com.spring.jdbcdemo.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import com.spring.jdbcdemo.connection.JDBCConnection;
import com.spring.main.beans.Car;
//Class for CRUD Operations interaction with database
public class CarDao {
//Method to insert a car record -> Create Operation
public boolean insertCar(Car car) {
Connection connection = JDBCConnection.getConnection();
String sql = "insert into Car values(?,?,?,?,?)";
try {
PreparedStatement psmt = connection.prepareStatement(sql);
psmt.setString(1, car.getCarNumber());
psmt.setString(2, car.getName());
psmt.setString(3, car.getFuel());
psmt.setString(4, car.getBrand());
psmt.setString(5, car.getType());
int rows = psmt.executeUpdate();
System.out.println(rows);
return rows==1;
}
catch(Exception ex) {
System.out.println(ex.getMessage());
return false;
}
finally {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//Method to retrieve one car record -> Read Operation from CRUD
public Car getCar(String carNumber) {
Connection connection = JDBCConnection.getConnection();
Car car = new Car();
try {
String sql = "SELECT * FROM Car where car_number=?";
PreparedStatement psmt = connection.prepareStatement(sql);
psmt.setString(1, carNumber);
ResultSet cars = psmt.executeQuery();
while(cars.next()) {
car.setCarNumber(cars.getString("car_number"));
car.setName(cars.getString("car_name"));
car.setFuel(cars.getString("fuel"));
car.setBrand(cars.getString("brand"));
car.setType(cars.getString("car_type"));
}
return car;
}
catch(Exception ex) {
System.out.println(ex.getMessage());
return null;
}
finally {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//Method to Update a Car record -> Update Operation
public boolean updateCar(Car car1, Car car2) {
Connection connection = JDBCConnection.getConnection();
try {
String sql = "UPDATE Car SET car_name=?,fuel=?,brand=?,car_type=? WHERE car_number=?";
PreparedStatement psmt = connection.prepareStatement(sql);
psmt.setString(1, car2.getName());
psmt.setString(2, car2.getFuel());
psmt.setString(3, car2.getBrand());
psmt.setString(4,car2.getType());
psmt.setString(5,car1.getCarNumber());
int rows = psmt.executeUpdate();
System.out.println(rows);
return rows==1;
}
catch(Exception ex) {
System.out.println(ex.getMessage());
return false;
}
finally {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//Method to Delete a Car Record
public boolean deleteCar(Car car) {
Connection connection = JDBCConnection.getConnection();
try {
String sql = "DELETE FROM Car WHERE car_number=? AND car_name=? AND fuel=? AND brand=? AND car_type=?";
PreparedStatement psmt = connection.prepareStatement(sql);
psmt.setString(1, car.getCarNumber());
psmt.setString(2, car.getName());
psmt.setString(3, car.getFuel());
psmt.setString(4, car.getBrand());
psmt.setString(5, car.getType());
int rows = psmt.executeUpdate();
System.out.println(rows +" rows deleted.");
return rows==1;
}
catch(Exception ex) {
System.out.println(ex.getMessage());
return false;
}
finally {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//Method toRetrieve All Car Records
public List<Car> getAllCars(){
Connection connection = JDBCConnection.getConnection();
try {
Statement s = connection.createStatement();
String sql = "SELECT * FROM Car";
List<Car> cars = (List<Car>)s.executeQuery(sql);
return cars;
}
catch(Exception ex) {
System.out.println(ex.getMessage());
return null;
}
finally {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}Car.java package com.spring.main.beans;
public class Car {
private String carNumber;
private String name;
private String fuel;
private String brand;
private String type;
public String getCarNumber() {
return carNumber;
}
public void setCarNumber(String carNumber) {
this.carNumber = carNumber;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getFuel() {
return fuel;
}
public void setFuel(String fuel) {
this.fuel = fuel;
}
public String getBrand() {
return brand;
}
public void setBrand(String brand) {
this.brand = brand;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
@Override
public String toString() {
return "Car [carNumber=" + carNumber + ", name=" + name + ", fuel=" + fuel + ", brand=" + brand + ", type="
+ type + "]";
}
}DriverJdbcSpringApplication.java package com.spring.main;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.spring.jdbcdemo.dao.CarDao;
import com.spring.main.beans.Car;
public class JdbcSpringApplication {
public static void main(String[] args) {
// TODO Auto-generated method stub
AbstractApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
//Create Operation
Car car1 = context.getBean("car1",Car.class);
Car car2 = context.getBean("car2",Car.class);
CarDao cd = context.getBean("carDao",CarDao.class);
boolean inserted1 = cd.insertCar(car1);
boolean inserted2 = cd.insertCar(car2);
if(inserted1 && inserted2) {
System.out.println("Row Insertion Successful");
}
else {
System.out.println("Row Insertion NOT Successful. Please Check the details and try again.");
}
//Read Operation
Car car = cd.getCar("AP04CP2334");
System.out.println(car);
//Update Operation
Car newCar = car1;
//Updating the car record by changing fuel and name
newCar.setFuel("Petrol");
newCar.setName("ALTO 800");
boolean updated = cd.updateCar(car1, newCar);
if(updated) {
System.out.println("Row successfully updated.");
}
else {
System.out.println("Row NOT Updated. Kindly check the details and try again.");
}
//Delete Operation
boolean deleted = cd.deleteCar(car1);
if(deleted) {
System.out.println("Row deleted Successfully.");
}
else {
System.out.println("Row NOT deleted. Please check the details and try again.");
}
//Closing Context
context.close();
}
}
Application Context ConfigurationapplicationContext.xml <beans xmlns = "http://www.springframework.org/schema/beans"
xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
xmlns:context = "http://www.springframework.org/schema/context"
xsi:schemaLocation = "http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<bean id="carDao" class="com.spring.jdbcdemo.dao.CarDao" />
<bean id="car1" class="com.spring.main.beans.Car">
<property name="carNumber" value="AP05CD5879"></property>
<property name="name" value="Alto"></property>
<property name="fuel" value="Diesel"></property>
<property name="brand" value="Maruthi"></property>
<property name="type" value="Hatchback"></property>
</bean>
<bean id="car2" class="com.spring.main.beans.Car">
<property name="carNumber" value="AP04CP2334"></property>
<property name="name" value="Etios"></property>
<property name="fuel" value="Petrol"></property>
<property name="brand" value="Toyota"></property>
<property name="type" value="Sedan"></property>
</bean>
</beans>Other FilesJDBCConnection.java package com.spring.jdbcdemo.connection;
import java.sql.Connection;
import java.sql.DriverManager;
public class JDBCConnection {
private static String url = "jdbc:mysql://localhost:3306/springjdbcdemo";
private static String username = "root";
private static String password = "Root@pd12";
public static Connection getConnection() {
Connection connection = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(url, username, password);
}
catch(Exception ex) {
System.out.println(ex.getMessage());
}
return connection;
}
}Pom.xml<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.spring</groupId>
<artifactId>spring-jdbc-demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-jdbc-demo</name>
<description>Spring JDBC Demo with Maven</description>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
</dependencies>
<properties>
<spring.version>5.3.20</spring.version>
</properties>
</project> |
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Uh oh!
There was an error while loading. Please reload this page.
Uh oh!
There was an error while loading. Please reload this page.
Connecting with the DB using JDBC driver
Create a Spring application that connects to the DB using the DB driver and performs all four CRUD operations using plain JDBC.
The CRUD operations in a relational DB and their corresponding DML statements are listed below:
INSERT INTOstatementSELECTqueryUPDATEorREPLACEstatementDELETE FROMstatementBonuses
Hints
pom.xml.DatabaseConnectionunder the<base-package>.utilsubpackage. This class will have the code to connect with the DB in a static factory method calledgetConnection(), preferably in a singleton manner. This static method will return an object ofConnectionwhich you can use in your application. You can even treat this class as a bean class and write a bean definition for it in theapplicationContext.xmlwhere you provide theurl,username, andpasswordfor the connection usingpropertytags.CrudControllerunder the<base-package>.controllerssubpackage. This class will have at least four handler methods, one for each of the CRUD operations, i.e.,create(),read(),update(), anddelete()methods. It will also have a data member of typeConnectionwhich will be initialized via all the constructors by calling the static methodgetConnection()of the utility classDatabaseConnection. You can even write an empty bean definition for this controller bean class.CrudControllerbean instance to perform the CRUD operations.All reactions