I am learning Spring JDBC inserting to database but I could not insert my 2 aggregated classes together. (Employee which has Address class). How can I add Address class' variables to Employee table? Code below, thanks.
Employee.java
package model;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
public class Employee {
private int id;
private String name;
private String surname;
private int age;
private String gender;
private String contact;
public Employee(int id, String name, String surname, int age, String gender, String contact, Address address) {
super();
this.id = id;
this.name = name;
this.surname = surname;
this.age = age;
this.gender = gender;
this.contact = contact;
this.address = address;
}
private Address address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSurname() {
return surname;
}
public void setSurname(String surname) {
this.surname = surname;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getContact() {
return contact;
}
public void setContact(String contact) {
this.contact = contact;
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
@Override
public String toString() {
return "Employee [id=" + id + ", name=" + name + ", surname=" + surname + ", age=" + age + ", gender=" + gender
+ ", contact=" + contact + ", address=" + address + "]";
}
}
Address.java
package model;
public class Address {
private String address;
private String city;
private String country;
public Address(String address, String city, String country) {
super();
this.address = address;
this.city = city;
this.country = country;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
@Override
public String toString() {
return "[" + address + ", city=" + city + ", country=" + country + "]";
}
}
EmployeeDAO.java
package dao;
import java.sql.SQLException;
import model.Employee;
public interface EmployeeDAO {
public void saveEmployee(Employee employee) throws Exception;
}
EmployeeDAOImpl.java
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.stereotype.Repository;
import model.Employee;
public class EmployeeDAOImpl implements EmployeeDAO {
private final static String INSERT_EMPLOYEE = "insert into employee (id, name, surname, age, gender, contact) values (?,?,?,?,?,?)";
private DataSource dataSource;
public DataSource getDataSource() {
return dataSource;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
@Override
public void saveEmployee(Employee employee) throws SQLException {
try {
Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(INSERT_EMPLOYEE);
preparedStatement.setInt(1, employee.getId());
preparedStatement.setString(2, employee.getName());
preparedStatement.setString(3, employee.getSurname());
preparedStatement.setInt(4, employee.getAge());
preparedStatement.setString(5, employee.getGender());
preparedStatement.setString(6, employee.getContact());
//index 7,8 and 9 are address, city and country...
preparedStatement.executeUpdate();
preparedStatement.close();
System.out.println("Employee is inserted..." + employee);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
xml file
<?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.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd ">
<bean
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location">
<value>myconfiguration/jdbc.properties</value>
</property>
</bean>
<bean id="dataSourceId"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName"
value="${jdbc.driverClassName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>
<bean id="employeeDAOImplId" class="dao.EmployeeDAOImpl">
<property name="dataSource" ref="dataSourceId" />
</bean>
</beans>
MainTest.java
package test;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import dao.EmployeeDAO;
import model.Employee;
public class MainTest {
public static void main(String[] args) throws Exception {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("EmployeeJDBC.xml");
EmployeeDAO dao = context.getBean(EmployeeDAO.class);
Employee employee = new Employee(1, "John", "Doe", 24, "M", "+15555555555", null); // here null, i couldn't fix.
// Address address = new Address("RandAddress", "RandCity", "RandCountry");
dao.saveEmployee(employee);
}
}
By your design, you should be able just to add appropriate columns to the insert query, and update parameters:
// EmployeeDAOImpl
private final static String INSERT_EMPLOYEE = "insert into employee (id, name, surname, age, gender, contact, address, city, country) values (?,?,?,?,?,?,?,?,?)";
// ...
@Override
public void saveEmployee(Employee employee) throws SQLException {
try {
Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(INSERT_EMPLOYEE);
preparedStatement.setInt(1, employee.getId());
preparedStatement.setString(2, employee.getName());
preparedStatement.setString(3, employee.getSurname());
preparedStatement.setInt(4, employee.getAge());
preparedStatement.setString(5, employee.getGender());
preparedStatement.setString(6, employee.getContact());
//index 7,8 and 9 are address, city and country...
preparedStatement.setString(7, employee.getAddress().getAddress());
preparedStatement.setString(8, employee.getAddress().getCity());
preparedStatement.setString(9, employee.getAddress().getCountry());
preparedStatement.executeUpdate();
preparedStatement.close();
System.out.println("Employee is inserted..." + employee);
} catch (SQLException e) {
e.printStackTrace();
}
}
And set the fields in MainTest:
public class MainTest {
public static void main(String[] args) throws Exception {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("EmployeeJDBC.xml");
EmployeeDAO dao = context.getBean(EmployeeDAO.class);
Address address = new Address("RandAddress", "RandCity", "RandCountry");
Employee employee = new Employee(1, "John", "Doe", 24, "M", "+15555555555", address);
dao.saveEmployee(employee);
}
}