JDBC-JPA

Updated: December 20, 2020

What is JDBC?

JDBC(Java Database Connectivity) is a Java API that allows Java to access databases.

DB Connection Example

public class DBUtil {

	private static Properties dbInfo = new Properties();
	private static Properties sqlAll = new Properties();

	static {
		try {
			dbInfo.load(new FileInputStream("dbinfo.properties"));
			sqlAll.load(new FileInputStream("allsql.properties"));
			Class.forName(dbInfo.getProperty("driver"));
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	// URL, ID, PW
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(dbInfo.getProperty("url"), dbInfo.getProperty("id"),
				dbInfo.getProperty("pw"));
	}

	public static Properties getSqlAll() {
		return sqlAll;
	}

	public static void close(Connection con, Statement stmt) {
		try {
			if (stmt != null) {
				stmt.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void close(Connection con, Statement stmt, ResultSet rset) {
		try {
			if (rset != null) {
				rset.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

Properties example

#allsql
# dept table crud
deptAll=select * from dept
dept.getDept=select * form dept where deptno=?
dept.insert=insert into dept values(?, ?, ?)
dept.update=update dept set loc=? where deptno=?
dept.delete=delete from dept where deptno=?

#dbinfo
# oracle DB info
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:xe
id=SCOTT
pw=?

CRUD example

public class DeptDAO {

	static Properties sqlAll = DBUtil.getSqlAll();

	public static ArrayList<DeptDTO> deptAll() throws SQLException {
		Connection con = null;
		Statement stmt = null;
		ResultSet rset = null;

		ArrayList<DeptDTO> datas = null;

		try {
			con = DBUtil.getConnection();
			stmt = con.createStatement();
			rset = stmt.executeQuery(sqlAll.getProperty("deptAll"));

			datas = new ArrayList<DeptDTO>();
			while (rset.next()) {
				datas.add(new DeptDTO(rset.getInt("deptno"), rset.getString("dname"), rset.getString("loc")));
			}
		} finally {
			DBUtil.close(con, stmt, rset);
		}
		return datas;
	}

	public static DeptDTO getDept(int deptno) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;

		try {
			con = DBUtil.getConnection();
			pstmt = con.prepareStatement(sqlAll.getProperty("deptAll"));

			pstmt.setInt(1, deptno);

			rset = pstmt.executeQuery();

			if (rset.next()) {
				return new DeptDTO(rset.getInt("deptno"), rset.getString("dname"), rset.getString("loc"));
			}
		} finally {
			DBUtil.close(con, pstmt, rset);
		}
		return null;
	}

	public static boolean insert(DeptDTO newDept) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;

		try {
			con = DBUtil.getConnection();
			pstmt = con.prepareStatement(sqlAll.getProperty("dept.insert"));

			pstmt.setInt(1, newDept.getDeptno());
			pstmt.setString(2, newDept.getDname());
			pstmt.setString(3, newDept.getLoc());

			if (pstmt.executeUpdate() != 0) {
				return true;
			}
		} finally {
			DBUtil.close(con, pstmt);
		}
		return false;
	}

	public static boolean update(int newDeptno, String newLoc) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;

		try {
			con = DBUtil.getConnection();
			pstmt = con.prepareStatement(sqlAll.getProperty("dept.update"));

			pstmt.setString(1, newLoc);
			pstmt.setInt(2, newDeptno);

			if (pstmt.executeUpdate() != 0) {
				return true;
			}
		} finally {
			DBUtil.close(con, pstmt);
		}
		return false;
	}

	static boolean delete(int DeleteDeptno) {
		Connection con = null;
		PreparedStatement pstmt = null;

		try {
			con = DBUtil.getConnection();
			pstmt = con.prepareStatement(sqlAll.getProperty("dept.delete"));

			pstmt.setInt(1, DeleteDeptno);

			if (pstmt.executeUpdate() != 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(con, pstmt);
		}
		return false;
	}

}

What is JPA?

Jakarta Persistence (JPA; formerly Java Persistence API) is a Jakarta EE application programming interface specification that describes the management of relational data in enterprise Java applications.

Entity class example

@NoArgsConstructor
@AllArgsConstructor
@Setter
@Getter
@ToString(exclude = "employees")

@Entity
public class Dept {

	@Id
	private int deptno;

	@Column(length = 20)
	private String dname;

	@Column(length = 20)
	private String loc;

	@OneToMany(mappedBy = "deptno")
	private List<Employee> employees;
}

@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
@ToString

@NamedQuery(query = "select e from Employee e where e.eid=:eid and e.ename=:ename", name = "Employee.findByEmpnoAndEname")
@Entity
public class Employee {

	@Id
	@Column(name = "empno")
	private int eid;

	@Column(nullable = false, length = 20, name = "ename")
	private String ename;

	@Column(nullable = false, name = "sal")
	private int salary;

	@ManyToOne
	@JoinColumn(name = "deptno")
	private Dept deptno;
}

DBUtil example

public class PublicCommon {

	private static EntityManagerFactory emf;
	
	static {
		emf = Persistence.createEntityManagerFactory("oracleDBUse");
	}
	
	public static EntityManager getEntityManager() {
		return emf.createEntityManager();
	}
	
	public static void close() {
		emf.close();		
	}
}

persistence.xml example

<?xml version="1.0" encoding="UTF-8"?>

-<persistence xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/persistence" version="2.1">

-<persistence-unit name="oracleDBUse">

<class>model.domain.Dept</class>

<class>model.domain.Employee</class>

-<properties>

<property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver"/>

<property name="javax.persistence.jdbc.url" value="jdbc:oracle:thin:@127.0.0.1:1521:xe"/>

<property name="javax.persistence.jdbc.user" value="SCOTT"/>

<property name="javax.persistence.jdbc.password" value="TIGER"/>

<property name="hibernate.dialect" value="org.hibernate.dialect.OracleDialect"/>

<property name="hibernate.show_sql" value="true"/>

<!-- <property name="hibernate.format_sql" value="true" /><property name="hibernate.use_sql_comments" value="true" /> -->

<property name="hibernate.hbm2ddl.auto" value="none"/>

</properties>

</persistence-unit>

</persistence>

CRUD example

public class RunCRUD {

	@Test
	public void createEmployee() {
		EntityManager entitymanager = PublicCommon.getEntityManager();
		entitymanager.getTransaction().begin();

		try {
			Dept dept10 = entitymanager.find(Dept.class, 10);
			Employee employee = new Employee(4000, "king", 400000, dept10);
			entitymanager.persist(employee);
			entitymanager.getTransaction().commit();

		} catch (Exception e) {
			entitymanager.getTransaction().rollback();

		} finally {
			entitymanager.close();

		}
	}

	public static void updateEmployee() {
		EntityManager entitymanager = PublicCommon.getEntityManager();
		entitymanager.getTransaction().begin();

		try {
			Employee employee = entitymanager.find(Employee.class, 4000);

			// before update
			System.out.println("update before : " + employee);
			employee.setSalary(2000);
			entitymanager.getTransaction().commit();

			// after update
			System.out.println("update after : " + employee);

		} catch (Exception e) {
			entitymanager.getTransaction().rollback();

		} finally {
			entitymanager.close();

		}
	}

	// select
	public static void findElement() {
		EntityManager entitymanager = PublicCommon.getEntityManager();

		try {
			Employee employee = entitymanager.find(Employee.class, 4000);
			if (employee != null) {
				System.out.println("employee ID = " + employee.getEid());
				System.out.println("employee NAME = " + employee.getEname());
				System.out.println("employee SALARY = " + employee.getSalary());
				System.out.println("employee DESIGNATION = " + employee.getDeptno());
			} else {
				System.out.println("Fail");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			entitymanager.close();
		}

	}

	public static void findAllEmployees() {
		EntityManager entitymanager = PublicCommon.getEntityManager();
		try {
			String jpql = "select e from Employee e";
			List list = entitymanager.createQuery(jpql).getResultList();
			list.forEach(v -> System.out.println(v));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			entitymanager.close();
		}
	}

	// delete
	public static void deleteElement() {
		EntityManager entitymanager = PublicCommon.getEntityManager();
		entitymanager.getTransaction().begin();

		try {
			Employee employee = entitymanager.find(Employee.class, 4000);
			entitymanager.remove(employee);
			entitymanager.getTransaction().commit();

		} catch (Exception e) {
			entitymanager.getTransaction().rollback();

		} finally {
			entitymanager.close();

		}
	}

	// dept crud
	public static void createDept() {
		EntityManager entitymanager = PublicCommon.getEntityManager();
		entitymanager.getTransaction().begin();

		try {
			Dept dept = new Dept(50, "carking", "suwon", new ArrayList());
			entitymanager.persist(dept);
			entitymanager.getTransaction().commit();

		} catch (Exception e) {
			entitymanager.getTransaction().rollback();

		} finally {
			entitymanager.close();

		}
	}

	public static void updateDept() {
		EntityManager entitymanager = PublicCommon.getEntityManager();
		entitymanager.getTransaction().begin();

		try {
			Dept dept = entitymanager.find(Dept.class, 50);

			// before update
			System.out.println("update before : " + dept);
			dept.setDname("Good");
			entitymanager.getTransaction().commit();

			// after update
			System.out.println("update after : " + dept);

		} catch (Exception e) {
			entitymanager.getTransaction().rollback();

		} finally {
			entitymanager.close();

		}
	}

	// select
	public static void findDept() {
		EntityManager entitymanager = PublicCommon.getEntityManager();

		try {
			Dept dept = entitymanager.find(Dept.class, 50);
			if (dept != null) {
				System.out.println("dept NO = " + dept.getDeptno());
				System.out.println("dept DNAME = " + dept.getDname());
				System.out.println("dept LOC = " + dept.getLoc());
			} else {
				System.out.println("Fail");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			entitymanager.close();
		}

		entitymanager.close();
	}

	public static void findAllDepts() {
		EntityManager entitymanager = PublicCommon.getEntityManager();
		try {
			String jpql = "select * from Dept";
			List list = entitymanager.createNativeQuery(jpql).getResultList();
			list.forEach(v -> System.out.println(v));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			entitymanager.close();
		}
	}

	// join
	public static void joinDeptEmployee() {
		EntityManager entitymanager = PublicCommon.getEntityManager();
		try {
			String jpql = "select e from Employee e INNER JOIN e.deptno";
			List list = entitymanager.createQuery(jpql).getResultList();
			list.forEach(v -> System.out.println(v));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			entitymanager.close();
		}
	}

	// named query
	public static void namedQueryEidEname() {
		EntityManager em = PublicCommon.getEntityManager();
		try {
			Employee e = (Employee) em.createNamedQuery("Employee.findByEmpnoAndEname").setParameter("eid", 7369)
					.setParameter("ename", "SMITH").getSingleResult();
			System.out.println(e);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			em.close();
		}
	}
}

Cha Wanghyeon

JDBC-JPA

What is JDBC?

DB Connection Example

Properties example

CRUD example

What is JPA?

Entity class example

DBUtil example

persistence.xml example

CRUD example

You May Also Enjoy

백준 단계별로 풀어보기 - 기본 수학 2 (Python)

백준 단계별로 풀어보기 - 기본 수학 1 (Python)

백준 단계별로 풀어보기 - 문자열 (Python)

백준 단계별로 풀어보기 - 함수 (Python)