[ DTO / DAO / MAPPER] product 테이블에 대한 자바 코드

환경 : DBeaver, Eclipse, Spring Tool Suite4

 

 

 

ProductDto.java

import java.util.Date;

public class ProductDto {
    private int product_id;
    private String product_name;
    private String product_category;
    private Date product_release_date;
    private double product_price;

    // Constructors, getters, and setters 추가

    @Override
    public String toString() {
        return "ProductDto [product_id=" + product_id + ", product_name=" + product_name + ", product_category="
                + product_category + ", product_release_date=" + product_release_date + ", product_price="
                + product_price + "]";
    }
}

 

ProductMapper.java

@Service
public class ProductMapper implements RowMapper<ProductDto> {

    @Override
    public ProductDto mapRow(ResultSet rs, int idx) throws SQLException {
        ProductDto dto = new ProductDto();
        dto.setProductId(rs.getInt("product_id"));
        dto.setProductName(rs.getString("product_name"));
        dto.setProductCategory(rs.getString("product_category"));
        dto.setProductReleaseDate(rs.getDate("product_release_date"));
        dto.setProductPrice(rs.getDouble("product_price"));
        return dto;
    }
}

 

 

ProductDao.java

@Repository
public class ProductDao {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private ProductMapper mapper;

    public void insert(ProductDto dto) {
        String sql = "insert into product("
                + "product_id, product_name, product_category, product_release_date, product_price"
                + ") values(product_seq.nextval, ?, ?, ?, ?)";
        Object[] data = {
            dto.getProductName(), dto.getProductCategory(), 
            dto.getProductReleaseDate(), dto.getProductPrice()
        };
        jdbcTemplate.update(sql, data);
    }

    public boolean update(ProductDto dto) {
        String sql = "update product "
                + "set product_name=?, product_category=?, product_release_date=?, product_price=? "
                + "where product_id=?";
        Object[] data = {
            dto.getProductName(), dto.getProductCategory(), 
            dto.getProductReleaseDate(), dto.getProductPrice(), dto.getProductId()
        };
        return jdbcTemplate.update(sql, data) > 0;
    }

    public boolean delete(int productId) {
        String sql = "delete from product where product_id=?";
        Object[] data = { productId };
        return jdbcTemplate.update(sql, data) > 0;
    }

    public List<ProductDto> selectList() {
        String sql = "select * from product order by product_id asc";
        return jdbcTemplate.query(sql, mapper);
    }

    public List<ProductDto> selectList(String column, String keyword) {
        String sql = "select * from product where instr(" + column + ",?) > 0 "
                + "order by " + column + " asc, product_id asc";
        Object[] data = { keyword };
        return jdbcTemplate.query(sql, mapper, data);
    }

    public ProductDto selectOne(int productId) {
        String sql = "select * from product where product_id=?";
        Object[] data = { productId };
        List<ProductDto> list = jdbcTemplate.query(sql, mapper, data);
        return list.isEmpty() ? null : list.get(0);
    }
}

---

< 추가설정 >

[ Spring ]

 - pom.xml  (의존성 추가)

<?xml version="1.0" encoding="UTF-8"?>
<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>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.2.1</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.kh</groupId>
	<artifactId>spring10integrated</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>war</packaging>
	<name>spring10integrated</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>17</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>com.oracle.database.jdbc</groupId>
			<artifactId>ojdbc11</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-tomcat</artifactId>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		
		<!--		프로젝트 화면(JSP)를 추가하기 위한 의존성-->
		<dependency>
			<groupId>org.apache.tomcat.embed</groupId>
			<artifactId>tomcat-embed-jasper</artifactId>
		</dependency>
		<dependency>
			<groupId>jakarta.servlet</groupId>
			<artifactId>jakarta.servlet-api</artifactId>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>jakarta.servlet.jsp.jstl</groupId>
			<artifactId>jakarta.servlet.jsp.jstl-api</artifactId>
		</dependency>
		<dependency>
			<groupId>org.glassfish.web</groupId>
			<artifactId>jakarta.servlet.jsp.jstl</artifactId>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

 

- application.properties (데이터베이스 연동)

# setting for spring boot

# database setting
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.username=db아이디
spring.datasource.password=db비밀번호

---

[ Eclipse / java ]

 - JdbcHelper.java

package jdbc.util;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class JdbcHelper {
	public static JdbcTemplate getJdbcTemplate() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setUsername("db아이디");
		dataSource.setPassword("db비밀번호");
		dataSource.setDriverClassName("oracle.jdbc.OracleDriver");
		dataSource.setUrl("jdbc:oracle:thin:@localhost:1521:xe");
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate();
		jdbcTemplate.setDataSource(dataSource);
		
		return jdbcTemplate;
	}
}

 

 

 

 

 

개인 공부 기록용입니다:)