[JSP] sqlite3 DB를 이용하여 python 파일 값 java로 받아오기

#정리

#sqlite-jdbc 라이브러리 설치
 - pom.xml파일<dependencies>에 dependency 추가 후 저장

 

#DB 연결

 - 드라이버 등록 : Class.forName("org.sqlite.JDBC");

 - DB 커넥션 : conn = DriverManager.getConnection("jdbc:sqlite:"+db_file_path); 
 - DB 통신 : pstmt = conn.prepareStatement(쿼리문); / rs = pstmt.executeQuery();

 - Statement 종료(예외 처리 및 메모리 누수 방지)

 

 

marketInfo.db

0.01MB

--전체 예문 보기--

package zemi.com.stock;

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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import zemi.com.stock.model.StockVO;

public class sqlite2 {
	private static final Logger logger = LoggerFactory.getLogger(sqlite2.class);
	
	public static Connection conn = null;
	public static PreparedStatement pstmt = null;
	public static ResultSet rs = null;
	
	public static void getConn() {
		try {
			Class.forName("org.sqlite.JDBC");
			logger.info("conn successed....");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	
	public static void selectAll() {
		logger.info("selectAll()..");
		List<StockVO> vos = new ArrayList<StockVO>();
		
		try {
			String db_file_path = "C:\\kosta_iot\\python\\zemiFinal\\marketInfo.db";
			conn = DriverManager.getConnection("jdbc:sqlite:"+db_file_path);
			logger.info("driver successed...");
			
			pstmt = conn.prepareStatement("select * from marketinfo");
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				StockVO vo = new StockVO();
				
				String stockcode = rs.getString("stockCode");
				String stockname = rs.getString("stockName");
				
				vo.setStockcode(stockcode);
				vo.setStockname(stockname);
				vos.add(vo);
			}
			

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
	}//end finally
		
	System.out.println(vos);
	}//end selectAll 
		

	public static void main(String[] args) {
		getConn();
		selectAll();
	}
	
}

 

#sqlite-jdbc 라이브러리 설치

 - pom.xml파일<dependencies>에 dependency 추가 후 저장

<!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->
<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.36.0.3</version>
</dependency>

 

#DB 연결

 - 드라이버 등록 : Class.forName("org.sqlite.JDBC");

	public static void getConn() {
		try {
			Class.forName("org.sqlite.JDBC");
			logger.info("conn successed....");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

 

 - DB 커넥션 : conn = DriverManager.getConnection("jdbc:sqlite:"+db_file_path);

try {
			String db_file_path = "C:\\kosta_iot\\python\\zemiFinal\\marketInfo.db";
			conn = DriverManager.getConnection("jdbc:sqlite:"+db_file_path);
			logger.info("driver successed...");
						

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

 - DB 통신 : pstmt = conn.prepareStatement(쿼리문); / rs = pstmt.executeQuery();

			pstmt = conn.prepareStatement("select * from marketinfo");
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				StockVO vo = new StockVO();
				
				String stockcode = rs.getString("stockCode");
				String stockname = rs.getString("stockName");
				
				vo.setStockcode(stockcode);
				vo.setStockname(stockname);
				vos.add(vo);
			}

 

 

 - Statement 종료(예외 처리 및 메모리 누수 방지)

finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
	}//end finally

 

 

# 실행 결과