What we will discuss today is this structure. It looks very complicated, but let's say it is more structured! Please look through and refer to this figure later.
There are three ways to connect to the database: JDBC(Java DataBase Connectivity), DBCP(DataBase Connection Pool), and ORM(Object Relat.ional Mapping) Frameworks like iBatis, MyBatis, , etce, JPA, etc.
In Model2, we connected the database with the DBCP method, and in this post, we will be using the ORM Frameworks, specifically MyBatis.
We need to set up the plug-in in Eclipse.
Enter Eclipse Marketplace.
Search ORM and install MyBatipse.
Restart the Eclipse and create a new file
In this file, you can deal with all of the SQLs. So, with the MyBatis, you can extract the SQL statements from the DAO Cthe lass to member.xml file.
Now, we will import the MyBatis file. It is different from importing Dynamic Web Projects.
You must select the Existing Projects into Workspace wizard to import the folder.
db.properties and mybatis-config.xml are connected, and you will decide the URL, username, password, etc.
In the mybatis-config.xml, there are three main tags to set : <typeAliases>, <properties>, and <mappers>.
<typeAliases> tag is for the alias of the project. It will be used as the parameterType in the mapper file.
<properties> tag is to call the values to connect the database.
In the Mapper.xml, we use <resultMap> tag if the column and property names don't coch other.
<mappers> tag is to call the mapper files.
Before the exercise, refer to this figure to understand how to access a database with MyBatis.
Now, we will discuss connecting MyBatis and JSP Model 1. I will demonstrate basic login, sign up, delete and update forms with, MyBatis and I will show you partly to clarify the difference en with MyBatis and without it.
Import the folder first, and add dependencies. If you want to know how, please refer to my last post :
2022.09.23 - [Maven] - Maven) Configuration and Basics
DTO Class
Since the column and property names are the same, we don't need to write the resultMap.
package model;
public class Member {
private String id;
private String password;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
member.xml(The DAO Class calls the id values in this mapper file.)
The mapper file starts with the <mapper> tag and closes with </mapper>.
The namespace is an area of logical names. It is used in C++ too.
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="memberns">
<insert id="insert" parameterType="member">
insert into member22 values (#{id}, #{password})
</insert>
<select id="list" resultType="member">
select * from member22
</select>
<select id="select" parameterType="String" resultType="member">
select * from member22 where id = #{id}
</select>
<update id="update" parameterType="member">
update member22 set password = #{password} where id = #{id}
</update>
<delete id="delete" parameterType="String">
delete from member22 where id = #{id}
</delete>
</mapper>MemberDAO
As we extracted the SQLs in the mapper file, we don't need to includIfSQLs.
if you set the openSession value ,as "true" it will execute the commit automatically. Otherwise, you need to execute the commit whenever you need it, like this: session.commit();
We call mybatis-cofig.xml in the DAO Class, and the mybatis-cofig.xml calls the SQLSession and executes them.
insert(), update(), and delete() methods return the result with int datatypes.
Select is a bit different, selectOne() method returns the Object datatype as a result, and selectList() returns the List datatype.
Here is the complete code of the DAO Class.
package dao;
import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.Member;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MemberDao {
private SqlSession getSession() {
SqlSession session=null;
Reader reader=null;
try {
reader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(reader);
session = sf.openSession(true); // auto commit
} catch (IOException e) {
System.out.println(e.getMessage());
}
return session;
}
public int insert(Member member) {
int result = 0;
SqlSession session=null;
try {
session = getSession();
result = session.insert("insert", member);
} catch (Exception e) {
System.out.println(e.getMessage());
}
return result;
}
public int chk(Member member) {
int result = 0;
SqlSession session=null;
try { session = getSession();
Member mem = (Member) session.selectOne("select", member.getId());
if (mem.getId().equals(member.getId())) {
result = -1;
if (mem.getPassword().equals(member.getPassword())) {
result = 1;
}
}
} catch (Exception e) {
System.out.println(e.getMessage());
}
return result;
}
public Member select(String id) throws SQLException {
Member mem = null;
SqlSession session=null;
try { session = getSession();
mem = (Member) session.selectOne("select", id);
} catch (Exception e) {
System.out.println(e.getMessage());
}
return mem;
}
public List<Member> list() {
List<Member> list = new ArrayList<Member>();
SqlSession session=null;
try { session = getSession();
list = session.selectList("list");
} catch (Exception e) {
System.out.println(e.getMessage());
}
return list;
}
public int delete(String id) {
int result = 0;
SqlSession session=null;
try { session = getSession();
result = session.delete("delete", id);
} catch (Exception e) {
System.out.println(e.getMessage());
}
return result;
}
public int update(Member mem) {
int result = 0;
SqlSession session=null;
try { session = getSession();
result = session.update("update", mem);
} catch (Exception e) {
System.out.println(e.getMessage());
}
return result;
}
}mybatis-cofig.xml
Here, setting up the alias makes the project e,fficient and we set the alias as "member".
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="db.properties" />
<typeAliases>
<typeAlias type="model.Member" alias="member"></typeAlias>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driverClassName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="member.xml" />
</mappers>
</configuration>By using MyBatis, you will see that the codes are written in much more productive way.
The following is Model1 DAO Class without MyBatis. As you can see, the codes are way short but efficient.
// DAO(Data Access Object)
package member;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class MemberDAO {
// Singleton
private static MemberDAO instance = new MemberDAO();
public static MemberDAO getInstance() {
return instance;
}
private Connection getConnection() throws Exception{
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/orcl");
return ds.getConnection();
}
public int memberAuth(String id) {
int result = 0;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = getConnection();
String sql = "select * from member where id=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
rs = pstmt.executeQuery();
if(rs.next()) {
result = 1;
}else {
result = -1;
}
}catch(Exception e) {
e.printStackTrace();
}finally {
if(rs != null) try { rs.close();}catch(Exception e) {}
if(pstmt != null) try { pstmt.close();}catch(Exception e) {}
if(con != null) try { con.close();}catch(Exception e) {}
}
return result;
}
public int insert(MemberDTO member) {
int result = 0;
Connection con = null;
PreparedStatement pstmt = null;
try {
con = getConnection();
String sql="insert into member ";
sql+=" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, member.getId());
pstmt.setString(2, member.getPasswd());
pstmt.setString(3, member.getName());
pstmt.setString(4, member.getJumin1());
pstmt.setString(5, member.getJumin2());
pstmt.setString(6, member.getMailid());
pstmt.setString(7, member.getDomain());
pstmt.setString(8, member.getTel1());
pstmt.setString(9, member.getTel2());
pstmt.setString(10, member.getTel3());
pstmt.setString(11, member.getPhone1());
pstmt.setString(12, member.getPhone2());
pstmt.setString(13, member.getPhone3());
pstmt.setString(14, member.getPost());
pstmt.setString(15, member.getAddress());
pstmt.setString(16, member.getGender());
pstmt.setString(17, member.getHobby());
pstmt.setString(18, member.getIntro());
result = pstmt.executeUpdate();
}catch(Exception e) {
e.printStackTrace();
}finally {
if(pstmt != null) try { pstmt.close();}catch(Exception e) {}
if(con != null) try { con.close();}catch(Exception e) {}
}
return result;
}
// Log in
public int memberCheck(MemberDTO member) {
int result = 0;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = getConnection();
String sql="select * from member where id=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, member.getId());
rs = pstmt.executeQuery();
if(rs.next()) {
if(rs.getString("passwd").equals(member.getPasswd())) {
result = 1;
}else {
result = -1;
}
}else {
result = -2;
}
}catch(Exception e) {
e.printStackTrace();
}finally {
if(rs != null) try { rs.close();}catch(Exception e) {}
if(pstmt != null) try { pstmt.close();}catch(Exception e) {}
if(con != null) try { con.close();}catch(Exception e) {}
}
return result;
}
public MemberDTO getMember(String id) {
MemberDTO member = new MemberDTO();
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = getConnection();
String sql="select * from member where id=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
rs = pstmt.executeQuery();
if(rs.next()) {
member.setId(rs.getString("id"));
member.setPasswd(rs.getString("passwd"));
member.setName(rs.getString("name"));
member.setJumin1(rs.getString("jumin1"));
member.setJumin2(rs.getString("jumin2"));
member.setMailid(rs.getString("mailid"));
member.setDomain(rs.getString("domain"));
member.setTel1(rs.getString("tel1"));
member.setTel2(rs.getString("tel2"));
member.setTel3(rs.getString("tel3"));
member.setPhone1(rs.getString("phone1"));
member.setPhone2(rs.getString("phone2"));
member.setPhone3(rs.getString("phone3"));
member.setPost(rs.getString("post"));
member.setAddress(rs.getString("address"));
member.setGender(rs.getString("gender"));
member.setHobby(rs.getString("hobby"));
member.setIntro(rs.getString("intro"));
member.setRegister(rs.getTimestamp("register"));
}
}catch(Exception e) {
e.printStackTrace();
}finally {
if(rs != null) try { rs.close();}catch(Exception e) {}
if(pstmt != null) try { pstmt.close();}catch(Exception e) {}
if(con != null) try { con.close();}catch(Exception e) {}
}
return member;
}
public int update(MemberDTO member) {
int result = 0;
Connection con = null;
PreparedStatement pstmt = null;
try {
con = getConnection();
String sql="update member set name=?,jumin1=?,jumin2=?,mailid=?,";
sql+="domain=?,tel1=?,tel2=?,tel3=?,phone1=?,phone2=?,phone3=?,";
sql+="post=?,address=?,gender=?,hobby=?,intro=? where id=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, member.getName());
pstmt.setString(2, member.getJumin1());
pstmt.setString(3, member.getJumin2());
pstmt.setString(4, member.getMailid());
pstmt.setString(5, member.getDomain());
pstmt.setString(6, member.getTel1());
pstmt.setString(7, member.getTel2());
pstmt.setString(8, member.getTel3());
pstmt.setString(9, member.getPhone1());
pstmt.setString(10, member.getPhone2());
pstmt.setString(11, member.getPhone3());
pstmt.setString(12, member.getPost());
pstmt.setString(13, member.getAddress());
pstmt.setString(14, member.getGender());
pstmt.setString(15, member.getHobby());
pstmt.setString(16, member.getIntro());
pstmt.setString(17, member.getId());
result = pstmt.executeUpdate();
}catch(Exception e) {
e.printStackTrace();
}finally {
if(pstmt != null) try { pstmt.close();}catch(Exception e) {}
if(con != null) try { con.close();}catch(Exception e) {}
}
return result;
}
public int delete(String id) {
int result = 0;
Connection con = null;
PreparedStatement pstmt = null;
try {
con = getConnection();
String sql="delete from member where id=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
result = pstmt.executeUpdate();
}catch(Exception e) {
e.printStackTrace();
}finally {
if(pstmt != null) try { pstmt.close();}catch(Exception e) {}
if(con != null) try { con.close();}catch(Exception e) {}
}
return result;
}
}In the next post, we will discuss how it can be more economic by using MyBatis in Model2.