스프링 04. Sqlite 데이터베이스와 DAO 생성

Posted on: 2020년 06월 21일 2020년 06월 21일
Categories: Spring Framework, 강의자료

참조 : https://github.com/kwj1270

JDBCUtil 클래스 생성 : DB connect()와 close()를 모아 놓은 클래스

폴더 생성 : /bookapp에서 마우스 오른쪽 버튼 클릭 -> New -> Folder
클래스 생성 : util 폴더를 마우스 오른쪽 버튼 클릭 -> New -> Other -> class로 검색

JDBCUtil.java : static method로 만들었음

package krco.moak.bookapp.dao.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JDBCUtil {
	public static Connection getConn() {
		Connection conn = null;

		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + "mybook.db");
			// eclipse.exe가 있는 폴더에 mybook.db가 있어야 함
			//conn = DriverManager.getConnection("jdbc:sqlite:" + System.getProperty("user.dir") + "mybook.db");
			// System.getProperty("user.dir")는 eclipse.exe가 있는 폴더를 가리킴
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;
	}

	public static void close(PreparedStatement ps, Connection conn) {
		if(ps != null) {
			try {
				if(!ps.isClosed()) {
					ps.close();
				}
			}
			catch (Exception e) {
				e.printStackTrace();
				// TODO: handle exception
			}
			finally {
				ps = null;
			}
		}

		if(conn != null) {
			try {
				if(!conn.isClosed()) {
					conn.close();
				}
			}
			catch (Exception e) {
				e.printStackTrace();
				// TODO: handle exception
			}
			finally {
				conn = null;
			}
		}
	}

	public static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
		if(rs != null) {
			try {
				if(!rs.isClosed()) {
					rs.close();
				}
			}
			catch (Exception e) {
				e.printStackTrace();
				// TODO: handle exception
			}
			finally {
				rs = null;
			}
		}

		close(ps, conn);
	}
}

package krco.moak.bookapp.dao.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

public class JDBCUtil {

public static Connection getConn() {

Connection conn = null;

try {

Class.forName("org.sqlite.JDBC");

conn = DriverManager.getConnection("jdbc:sqlite:" + "mybook.db");

// eclipse.exe가 있는 폴더에 mybook.db가 있어야 함

//conn = DriverManager.getConnection("jdbc:sqlite:" + System.getProperty("user.dir") + "mybook.db");

// System.getProperty("user.dir")는 eclipse.exe가 있는 폴더를 가리킴

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return conn;

}

public static void close(PreparedStatement ps, Connection conn) {

if(ps != null) {

try {

if(!ps.isClosed()) {

ps.close();

}

catch (Exception e) {

e.printStackTrace();

// TODO: handle exception

}

finally {

ps = null;

}

if(conn != null) {

try {

if(!conn.isClosed()) {

conn.close();

}

catch (Exception e) {

e.printStackTrace();

// TODO: handle exception

}

finally {

conn = null;

}

public static void close(ResultSet rs, PreparedStatement ps, Connection conn) {

if(rs != null) {

try {

if(!rs.isClosed()) {

rs.close();

}

catch (Exception e) {

e.printStackTrace();

// TODO: handle exception

}

finally {

rs = null;

}

close(ps, conn);

}

BookDAOJDBC 클래스 생성

/bookapp/dao 위치에 book 폴더 생성

BookDAOJDBC 클래스 생성

package krco.moak.bookapp.dao.book;

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

import org.springframework.stereotype.Repository;

import krco.moak.bookapp.dao.util.JDBCUtil;
import krco.moak.bookapp.dto.book.Book;

@Repository
public class BookDAOJDBC {

	private final static String BOOK_INSERT = "INSERT INTO book(no, title, pric, publ) "
			+ " VALUES((SELECT ISNULL(max(no),0)+1 FROM book),?,?,?);"; // AUTOINCREMENT 기능 직접 추가
	// Oracle, H2에서 NVL(인자1, 인자2) 인자1이 null일 경우 인자2를 사용하겠다는 뜻
	// MySql, Sqlite에서는 ISNULL()
	private final static String BOOK_UPDATE = "UPDATE book SET title=?, pric=?, publ=? WHERE no=?;";
	private final static String BOOK_DELETE = "DELETE FROM book WHERE no=?;";
	private final static String BOOK_GET = "SELECT * FROM book WHERE no=?;";
	private final static String BOOK_LIST = "SELECT * FROM book ORDER BY no DESC;";

	public int insert(Book vo) {
		int r = -1;
		Connection conn = null;
		PreparedStatement ps = null;

		conn = JDBCUtil.getConn();
		try {
			ps = conn.prepareStatement(BOOK_INSERT);
			ps.setString(1, vo.getTitle());
			ps.setInt(2, vo.getPric());
			ps.setString(3, vo.getPubl());

			r = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			JDBCUtil.close(ps, conn);
		}

		return r;
	}

	public int update(Book vo) {
		int r = -1;
		Connection conn = null;
		PreparedStatement ps = null;

		conn = JDBCUtil.getConn();

		try {
			ps = conn.prepareStatement(BOOK_UPDATE);
			ps.setString(1, vo.getTitle());
			ps.setInt(2, vo.getPric());
			ps.setString(3, vo.getPubl());
			ps.setInt(4, vo.getNo());

			r = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			JDBCUtil.close(ps, conn);
		}

		return r;
	}

	public int delete(Book vo) {
		int r = -1;
		Connection conn = null;
		PreparedStatement ps = null;

		conn = JDBCUtil.getConn();

		try {
			ps = conn.prepareStatement(BOOK_DELETE);
			ps.setInt(1, vo.getNo());

			r = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			JDBCUtil.close(ps, conn);
		}

		return r;
	}

	public Book getOne(Book vo) {
		Book book = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		conn = JDBCUtil.getConn();
		try {
			ps = conn.prepareStatement(BOOK_GET);
			ps.setInt(1, vo.getNo());

			rs = ps.executeQuery();
			if(rs.next()) {
				book = new Book();
				book.setNo(rs.getInt("no"));
				book.setTitle(rs.getString("title"));
				book.setPric(rs.getInt("pric"));
				book.setPubl(rs.getString("publ"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			JDBCUtil.close(rs, ps, conn);
		}

		return book;
	}

	public List<Book> getAll(){
		List<Book> arList = new ArrayList<Book>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		conn = JDBCUtil.getConn();
		try {
			ps = conn.prepareStatement(BOOK_LIST);
			rs = ps.executeQuery();
			while(rs.next()) {
				Book book = new Book();
				book.setNo(rs.getInt("no"));
				book.setTitle(rs.getString("title"));
				book.setPric(rs.getInt("pric"));
				book.setPubl(rs.getString("publ"));
				arList.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			JDBCUtil.close(rs, ps, conn);
		}

		return arList;
	}
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

package krco.moak.bookapp.dao.book;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

import java.util.List;

import org.springframework.stereotype.Repository;

import krco.moak.bookapp.dao.util.JDBCUtil;

import krco.moak.bookapp.dto.book.Book;

@Repository

public class BookDAOJDBC {

private final static String BOOK_INSERT = "INSERT INTO book(no, title, pric, publ) "

+ " VALUES((SELECT ISNULL(max(no),0)+1 FROM book),?,?,?);"; // AUTOINCREMENT 기능 직접 추가

// Oracle, H2에서 NVL(인자1, 인자2) 인자1이 null일 경우 인자2를 사용하겠다는 뜻

// MySql, Sqlite에서는 ISNULL()

private final static String BOOK_UPDATE = "UPDATE book SET title=?, pric=?, publ=? WHERE no=?;";

private final static String BOOK_DELETE = "DELETE FROM book WHERE no=?;";

private final static String BOOK_GET = "SELECT * FROM book WHERE no=?;";

private final static String BOOK_LIST = "SELECT * FROM book ORDER BY no DESC;";

public int insert(Book vo) {

int r = -1;

Connection conn = null;

PreparedStatement ps = null;

conn = JDBCUtil.getConn();

try {

ps = conn.prepareStatement(BOOK_INSERT);

ps.setString(1, vo.getTitle());

ps.setInt(2, vo.getPric());

ps.setString(3, vo.getPubl());

r = ps.executeUpdate();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally {

JDBCUtil.close(ps, conn);

}

return r;

}

public int update(Book vo) {

int r = -1;

Connection conn = null;

PreparedStatement ps = null;

conn = JDBCUtil.getConn();

try {

ps = conn.prepareStatement(BOOK_UPDATE);

ps.setString(1, vo.getTitle());

ps.setInt(2, vo.getPric());

ps.setString(3, vo.getPubl());

ps.setInt(4, vo.getNo());

r = ps.executeUpdate();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally {

JDBCUtil.close(ps, conn);

}

return r;

}

public int delete(Book vo) {

int r = -1;

Connection conn = null;

PreparedStatement ps = null;

conn = JDBCUtil.getConn();

try {

ps = conn.prepareStatement(BOOK_DELETE);

ps.setInt(1, vo.getNo());

r = ps.executeUpdate();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally {

JDBCUtil.close(ps, conn);

}

return r;

}

public Book getOne(Book vo) {

Book book = null;

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

conn = JDBCUtil.getConn();

try {

ps = conn.prepareStatement(BOOK_GET);

ps.setInt(1, vo.getNo());

rs = ps.executeQuery();

if(rs.next()) {

book = new Book();

book.setNo(rs.getInt("no"));

book.setTitle(rs.getString("title"));

book.setPric(rs.getInt("pric"));

book.setPubl(rs.getString("publ"));

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally {

JDBCUtil.close(rs, ps, conn);

}

return book;

}

public List<Book> getAll(){

List<Book> arList = new ArrayList<Book>();

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

conn = JDBCUtil.getConn();

try {

ps = conn.prepareStatement(BOOK_LIST);

rs = ps.executeQuery();

while(rs.next()) {

Book book = new Book();

book.setNo(rs.getInt("no"));

book.setTitle(rs.getString("title"));

book.setPric(rs.getInt("pric"));

book.setPubl(rs.getString("publ"));

arList.add(book);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally {

JDBCUtil.close(rs, ps, conn);

}

return arList;

}

DAO 교체를 위한 인터페이스 추출 : 먼저 인터페이스를 만들고 이를 상속받아 DAO를 만들어야 하지만 그 반대 순서로 만들어 봄

BookDAOJDBC 소스 코드에서 마우스 오른쪽 버튼 클릭 후

BookDAO.java 파일이 생성됨

package krco.moak.bookapp.dao.book;

import java.util.List;

import krco.moak.bookapp.dto.book.Book;

public interface BookDAO {

	int insert(Book vo);

	int update(Book vo);

	int delete(Book vo);

	Book getOne(Book vo);

	List<Book> getAll();

}

package krco.moak.bookapp.dao.book;

import java.util.List;

import krco.moak.bookapp.dto.book.Book;

public interface BookDAO {

int insert(Book vo);

int update(Book vo);

int delete(Book vo);

Book getOne(Book vo);

List<Book> getAll();

}

BookDAOJDBC.java는 implements BookDAO가 추가되고 모든 메소드에는 @Override가 추가됨

package krco.moak.bookapp.dao.book;

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

import org.springframework.stereotype.Repository;

import krco.moak.bookapp.dao.util.JDBCUtil;
import krco.moak.bookapp.dto.book.Book;

@Repository
public class BookDAOJDBC implements BookDAO {

	private final Static String BOOK_INSERT = "INSERT INTO tbook(no, title, pric, publ) "
			+ " VALUES((SELECT ISNULL(max(no),0)+1 FROM book),?,?,?);"; // AUTOINCREMENT 기능 직접 추가
	// Oracle, H2에서 NVL(인자1, 인자2) 인자1이 null일 경우 인자2를 사용하겠다는 뜻
	// MySql, Sqlite에서는 ISNULL()
	private final Static String BOOK_UPDATE = "UPDATE book SET title=?, pric=?, publ=? WHERE no=?;";
	private final Static String BOOK_DELETE = "DELETE FROM book WHERE no=?;";
	private final Static String BOOK_GET = "SELECT * FROM book WHERE no=?;";
	private final Static String BOOK_LIST = "SELECT * FROM book ORDER BY no DESC;";

	@Override
	public int insert(Book vo) {
		int r = -1;
		Connection conn = null;
		PreparedStatement ps = null;

		conn = JDBCUtil.getConn();
		try {
			ps = conn.prepareStatement(BOOK_INSERT);
			ps.setString(1, vo.getTitle());
			ps.setInt(2, vo.getPric());
			ps.setString(3, vo.getPubl());

			r = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			JDBCUtil.close(ps, conn);
		}

		return r;
	}

	@Override
	public int update(Book vo) {
		int r = -1;
		Connection conn = null;
		PreparedStatement ps = null;

		conn = JDBCUtil.getConn();

		try {
			ps = conn.prepareStatement(BOOK_UPDATE);
			ps.setString(1, vo.getTitle());
			ps.setInt(2, vo.getPric());
			ps.setString(3, vo.getPubl());
			ps.setInt(4, vo.getNo());

			r = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			JDBCUtil.close(ps, conn);
		}

		return r;
	}

	@Override
	public int delete(Book vo) {
		int r = -1;
		Connection conn = null;
		PreparedStatement ps = null;

		conn = JDBCUtil.getConn();

		try {
			ps = conn.prepareStatement(BOOK_DELETE);
			ps.setInt(1, vo.getNo());

			r = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			JDBCUtil.close(ps, conn);
		}

		return r;
	}

	@Override
	public Book getOne(Book vo) {
		Book book = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		conn = JDBCUtil.getConn();
		try {
			ps = conn.prepareStatement(BOOK_GET);
			ps.setInt(1, vo.getNo());

			rs = ps.executeQuery();
			if(rs.next()) {
				book = new Book();
				book.setNo(rs.getInt("no"));
				book.setTitle(rs.getString("title"));
				book.setPric(rs.getInt("pric"));
				book.setPubl(rs.getString("publ"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			JDBCUtil.close(rs, ps, conn);
		}

		return book;
	}

	@Override
	public List<Book> getAll(){
		List<Book> arList = new ArrayList<Book>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		conn = JDBCUtil.getConn();
		try {
			ps = conn.prepareStatement(BOOK_LIST);
			rs = ps.executeQuery();
			while(rs.next()) {
				Book book = new Book();
				book.setNo(rs.getInt("no"));
				book.setTitle(rs.getString("title"));
				book.setPric(rs.getInt("pric"));
				book.setPubl(rs.getString("publ"));
				arList.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			JDBCUtil.close(rs, ps, conn);
		}

		return arList;
	}

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

package krco.moak.bookapp.dao.book;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.ArrayList;

import java.util.List;

import org.springframework.stereotype.Repository;

import krco.moak.bookapp.dao.util.JDBCUtil;

import krco.moak.bookapp.dto.book.Book;

@Repository

public class BookDAOJDBC implements BookDAO {

private final Static String BOOK_INSERT = "INSERT INTO tbook(no, title, pric, publ) "

+ " VALUES((SELECT ISNULL(max(no),0)+1 FROM book),?,?,?);"; // AUTOINCREMENT 기능 직접 추가

// Oracle, H2에서 NVL(인자1, 인자2) 인자1이 null일 경우 인자2를 사용하겠다는 뜻

// MySql, Sqlite에서는 ISNULL()

private final Static String BOOK_UPDATE = "UPDATE book SET title=?, pric=?, publ=? WHERE no=?;";

private final Static String BOOK_DELETE = "DELETE FROM book WHERE no=?;";

private final Static String BOOK_GET = "SELECT * FROM book WHERE no=?;";

private final Static String BOOK_LIST = "SELECT * FROM book ORDER BY no DESC;";

@Override

public int insert(Book vo) {

int r = -1;

Connection conn = null;

PreparedStatement ps = null;

conn = JDBCUtil.getConn();

try {

ps = conn.prepareStatement(BOOK_INSERT);

ps.setString(1, vo.getTitle());

ps.setInt(2, vo.getPric());

ps.setString(3, vo.getPubl());

r = ps.executeUpdate();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally {

JDBCUtil.close(ps, conn);

}

return r;

}

@Override

public int update(Book vo) {

int r = -1;

Connection conn = null;

PreparedStatement ps = null;

conn = JDBCUtil.getConn();

try {

ps = conn.prepareStatement(BOOK_UPDATE);

ps.setString(1, vo.getTitle());

ps.setInt(2, vo.getPric());

ps.setString(3, vo.getPubl());

ps.setInt(4, vo.getNo());

r = ps.executeUpdate();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally {

JDBCUtil.close(ps, conn);

}

return r;

}

@Override

public int delete(Book vo) {

int r = -1;

Connection conn = null;

PreparedStatement ps = null;

conn = JDBCUtil.getConn();

try {

ps = conn.prepareStatement(BOOK_DELETE);

ps.setInt(1, vo.getNo());

r = ps.executeUpdate();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally {

JDBCUtil.close(ps, conn);

}

return r;

}

@Override

public Book getOne(Book vo) {

Book book = null;

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

conn = JDBCUtil.getConn();

try {

ps = conn.prepareStatement(BOOK_GET);

ps.setInt(1, vo.getNo());

rs = ps.executeQuery();

if(rs.next()) {

book = new Book();

book.setNo(rs.getInt("no"));

book.setTitle(rs.getString("title"));

book.setPric(rs.getInt("pric"));

book.setPubl(rs.getString("publ"));

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally {

JDBCUtil.close(rs, ps, conn);

}

return book;

}

@Override

public List<Book> getAll(){

List<Book> arList = new ArrayList<Book>();

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

conn = JDBCUtil.getConn();

try {

ps = conn.prepareStatement(BOOK_LIST);

rs = ps.executeQuery();

while(rs.next()) {

Book book = new Book();

book.setNo(rs.getInt("no"));

book.setTitle(rs.getString("title"));

book.setPric(rs.getInt("pric"));

book.setPubl(rs.getString("publ"));

arList.add(book);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally {

JDBCUtil.close(rs, ps, conn);

}

return arList;

}

whan0623

516