[Java] I installed JDBC and tried to connect with servlet + MySQL. (There is a version using DAO / Bean)

◆ Table of contents

◇ Directory structure ◇ [Image] Output contents & DB contents

1. JDBC driver installation
2. Create context.xml
3. Execution (multiple patterns)
4. [Execution] servlet
5. [Execution] DAO ~ Do not use Bean ~
6. [Execute] DAO ~ Use Bean ~

◇ Directory structure

:file_folder: book 　┣━ :file_folder: src 　┃　　┣━ :file_folder:　sample 　┃　　┃　　┣━ :page_facing_up: All.java 　┃　　┃　　┣━ :page_facing_up: ProductList.java 　┃　　┃　　┗━ :page_facing_up: ProductList2.java 　┃　　┃ 　┃　　┣━ :file_folder: dao 　┃　　┃　　┣━ :page_facing_up: DAO.java 　┃　　┃　　┗━ :page_facing_up: ProductDAO.java 　┃　　┃ 　┃　　┗━ :file_folder: bean 　┃　　　　　┗━ :page_facing_up: Product.java 　┃ 　┗━ :file_folder: WebContent 　　　　┣━ :file_folder: META-INF 　　　　┃　　┗━ :page_facing_up: context.xml 　　　　┃ 　　　　┗━ :file_folder: WEB-INF 　　　　　　　┗━ :file_folder: lib 　　　　　　　　　　┗━ :page_facing_up: mysql-connector-java-5.1.48-bin.jar

◇ [Image] Output contents & DB contents

・ Display image (left) ・ DB contents (right)

1. JDBC driver installation

1. [Download] mysql-connector-java-8.0.18.zip from the official website (https://dev.mysql.com/downloads/connector/j/)
2. Unzip and extract mysql-connector-java-5.1.48-bin.jar
3. Put it in / WebContent / WEB-INF / lib / mysql-connector-java-5.1.48-bin.jar

2. Create context.xml

Create in / WebContent / META-INF / context.xml

context.xml

<?xml version="1.0" encoding="UTF-8" ?>
<Context>
	<Resource name = "jdbc/book"
              auth = "Container"
              type = "javax.sql.DataSource"
              driverClassName = "com.mysql.jdbc.Driver"
              url      = "jdbc:mysql://localhost/book"
              username = "username"
              password = "password">
	</Resource>
</Context>

3. Execution (multiple patterns)

1. [Execution] servlet
2. [Execution] DAO ~ Do not use Bean ~
3. [Execute] DAO ~ Use Bean ~

1. [Execution] servlet

Create in /src/sample/All.java

All.java

package sample;

import java.io.*;
import java.sql.*;

import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;
import javax.sql.*;

/**
 * Servlet implementation class All
 */
@WebServlet("/All")
public class All extends HttpServlet {


	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		PrintWriter out = response.getWriter();

		try {
			//Get connection
			InitialContext ic = new InitialContext();
			DataSource ds = (DataSource) ic.lookup("java:/comp/env/jdbc/book");
			Connection con = ds.getConnection();

			//SQL statement transmission
			PreparedStatement st = con.prepareStatement("select * from product");
			//Execution & result reception
			ResultSet rs = st.executeQuery();

			//Data display
			while (rs.next()) {
				out.println(
					rs.getInt("id")      + ":" + 
					rs.getString("name") + ":" + 
					rs.getInt("price")
				);
			}

			//Database disconnection
			st.close();
			con.close();

		} catch (Exception e) {
			//Connection / SQL statement error
			e.printStackTrace(out);

		} // try
	}

}

2. [Execution] DAO ~ Do not use Bean ~

Create DAO (Data Access Object)! !! !! !! (^ ω ^) Create a library of the contents of the above 1. [Execute] servlet

1. Create DAO.java (get connection)
2. Create ProductList.java (DAO acquisition & processing & display)

1) Create DAO.java (get connection)

DAO.java

package dao;

import java.sql.Connection;

import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DAO {
	static DataSource ds;

	public Connection getConnection() throws Exception {
		
		//Initial context construction
		InitialContext ic = new InitialContext();
		
		//Get DB connection destination information (context).Contents of xml)
		ds = (DataSource) ic.lookup("java:/comp/env/jdbc/book");

		return ds.getConnection();
	}
}

2) Create ProductList.java (DAO acquisition & processing & display)

ProductList.java

package sample;

import dao.DAO;

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;

@WebServlet("/sample/productList")
public class ProductList extends HttpServlet {

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		PrintWriter out = response.getWriter();

		try {
			//DB connection
			DAO dao = new DAO();
			Connection con = dao.getConnection();

			//SQL creation
			PreparedStatement st = con.prepareStatement("SELECT * FROM product");

			//SQL execution
			ResultSet rs = st.executeQuery();

			//Set data
			while (rs.next()) {
				out.println(
	                    rs.getInt("id")      + ":" + 
	                    rs.getString("name") + ":" + 
	                    rs.getInt("price")   + "<br>"
	                );
			}

			//Disconnect
			st.close();
			con.close();

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

	}
}

3. [Execute] DAO ~ Use Bean ~

1. Create DAO.java (get connection)
2. Create Product.java (Bean)
3. Create ProductDAO.java (DAO acquisition & processing)
4. Create ProductList2.java (Get & display ProductDAO)

1) Create DAO.java (get connection)

Above, 2) [Execution] Because it is the same as DAO ~ Do not use Bean ~ !! !! !! !! !! Omitted! !! !! !! !!

2) Create Product.java (Bean)

Product.java

package bean;

public class Product implements java.io.Serializable {
	private int id;
	private String name;
	private int price;

	public int getId() {
		return id;
	}

	public String getName() {
		return name;
	}

	public int getPrice() {
		return price;
	}

	public void setId(int id) {
		this.id = id;
	}

	public void setName(String name) {
		this.name = name;
	}

	public void setPrice(int price) {
		this.price = price;
	}
}

3) Create ProductDAO.java (DAO acquisition & processing)

ProductDAO.java

package dao;

import java.sql.*;
import java.util.*;
import bean.Product;

public class ProductDAO extends DAO {
	public List<Product> listAll() throws Exception {

		List<Product> list = new ArrayList<>();

		//DB connection
		Connection con = getConnection();

		//SQL creation
		PreparedStatement st = con.prepareStatement("SELECT * FROM product");

		//SQL execution
		ResultSet rs = st.executeQuery();

		//Set data
		while (rs.next()) {
			Product p = new Product();
			p.setId(rs.getInt("id"));
			p.setName(rs.getString("name"));
			p.setPrice(rs.getInt("price"));

			list.add(p);
		}

		//Disconnect
		st.close();
		con.close();

		return list;
	}
}

4) Create ProductList2.java (Get & display ProductDAO)

ProductList2.java

package sample;

import java.io.*;
import java.sql.*;
import java.util.List;
import javax.servlet.*;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;
import javax.sql.*;
import bean.Product;
import dao.ProductDAO;

@WebServlet("/sample/productList2")
public class Search2 extends HttpServlet {

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		PrintWriter out = response.getWriter();

		try {
			ProductDAO dao = new ProductDAO();

			List<Product> list = dao.listAll();

			for (Product p : list) {
				out.println(
						p.getId()    + ":" + 
						p.getName()  + ":" + 
						p.getPrice() + "<br>"
				);
			}

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

	}

}

Reference site

• It was really helpful! !! !! How to connect to MySQL with Java https://garafu.blogspot.com/2016/05/how-to-connect-mysql.html#con-svlt1