[웹프로그래밍] Maven/ JDBC

Maven

: 자바용 프로젝트 관리 도구

: 라이브러리 관리가 쉬움 (pom.xml)

Group ID: org.apache.maven.archetypes 

Artifact: maven-archetype-quickstart

pom.xml에 추가

-자바 컴파일러 버전을 1.8로 사용하기 위함

<plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.6.1</version>
            <configuration>
                <source>1.8</source>
                <target>1.8</target>
            </configuration>
</plugin>

 

---

JDBC

pom.xml에 추가

-sql 사용을 위한 드라이버

<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.22</version>
</dependency>

 

[boostcourse 웹프로그래밍 실습코드 직접 코딩하기]

package kr.or.connect.jdbcexam.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import kr.or.connect.jdbcexam.dto.Role;

public class RoleDao {
	private static String dburl = "jdbc:mysql://localhost:3306/connectdb?serverTimezone=Asia/Seoul&useSSL=false";
	private static String dbuser = "...";
	private static String dbpasswd = "...";

	// INSERT
	public int addRole(Role role) {

		int insertCount = 0; // n건 입력했습니다.

		// try catch finally 구문에서 사용하려고 미리 전역처리 해주는 것
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection(dburl, dbuser, dbpasswd);

			String sql = "INSERT INTO role(role_id, description) VALUES (?, ?);";

			ps = conn.prepareStatement(sql);
			ps.setInt(1, role.getRoleId());
			ps.setString(2, role.getDescription());

			insertCount = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return insertCount;
	}
	
	
	//DELETE
	public int delRole(Integer roleId) {
		int DeleteCount = 0;
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		String sql = "DELETE FROM role WHERE role_id = ?";
		
		try (
				Connection conn = DriverManager.getConnection(dburl, dbuser, dbpasswd);
				PreparedStatement ps = conn.prepareStatement(sql);
				){
			ps.setInt(1, roleId);
			DeleteCount = ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return DeleteCount;
	}
	
	//UPDATE
	public int updateRole(Role role) {
		int updateCount = 0;
		
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		String sql = "UPDATE role SET description = ? WHERE role_id = ?;";
		
		try (
				Connection conn = DriverManager.getConnection(dburl, dbuser, dbpasswd);
				PreparedStatement ps = conn.prepareStatement(sql);
				){
			ps.setString(1, role.getDescription());
			ps.setInt(2, role.getRoleId());
			
			updateCount = ps.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		return updateCount;
	}

	// SELECT
	public Role getRole(Integer roleId) {
		Role role = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection(dburl, dbuser, dbpasswd);
			String sql = "SELECT role_id, description FROM role WHERE role_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, roleId);
			rs = ps.executeQuery();

			if (rs.next()) {
				String desctiption = rs.getString(1);
				int id = rs.getInt("role_id");
				role = new Role(id, desctiption);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		return role;
	}

	// SELECT ALL
	public List<Role> getRoles() {
		List<Role> list = new ArrayList<Role>();

		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (Exception e) {
			e.printStackTrace();
		}

		String sql = "SELECT role_id, description FROM role;";

		try (Connection conn = DriverManager.getConnection(dburl, dbuser, dbpasswd);
				PreparedStatement ps = conn.prepareStatement(sql);
				ResultSet rs = ps.executeQuery();) {
			while (rs.next()) {
				String description = rs.getString(2);
				int id = rs.getInt(1);
				Role role = new Role(id, description);
				list.add(role);
			}

		} catch (Exception e) {
			e.printStackTrace();

		}

		return list;
	}
}

//SELECT 실행

package kr.or.connect.jdbcexam;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.jdbcexam.dto.Role;

public class JDBCExam1 {
	public static void main(String[] args) {
		RoleDao dao = new RoleDao();
		Role role = dao.getRole(100);
		System.out.println(role);
	}
}

//INSERT 실행

package kr.or.connect.jdbcexam;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.jdbcexam.dto.Role;

public class JDBCExam2 {
	public static void main(String[] args) {
		int roleId = 500;
		String description = "CTO";
		
		Role role = new Role(roleId, description);
		
		RoleDao dao = new RoleDao();
		int insertCount = dao.addRole(role);
		
		System.out.println(insertCount);
	}
}

//DELETE 실행

package kr.or.connect.jdbcexam;

import kr.or.connect.jdbcexam.dao.RoleDao;

public class JDBCExam4 {
	public static void main(String[] args) {
		int roleId = 500;
		
		RoleDao dao =  new RoleDao();
		
		int DeleteCount = dao.delRole(roleId);
		
		System.out.println(DeleteCount);
	}
}

//UPDATE 실행

package kr.or.connect.jdbcexam;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.jdbcexam.dto.Role;

public class JDBCExam5 {
	public static void main(String[] args) {
		
		int RoleId = 500;
		String description = "CEO";
		
		Role role = new Role(RoleId, description);
		
		RoleDao dao = new RoleDao();
		
		int updateCount = dao.updateRole(role);
		
		System.out.println(updateCount);
		
	}
}

//SELECT ALL 실행

package kr.or.connect.jdbcexam;

import java.util.List;

import kr.or.connect.jdbcexam.dao.RoleDao;
import kr.or.connect.jdbcexam.dto.Role;

public class JDBCExam3 {
	public static void main(String[] args) {
		RoleDao dao = new RoleDao();
		
		List<Role> list = dao.getRoles();
		
		//forEach 문
		for(Role role: list) {
			System.out.println(role);
		}
	}
}

---

[내용 출처 및 참고]

www.boostcourse.org/web316

웹 프로그래밍(풀스택)