[Note] Cooperation between Java and DB (basic)

I wonder if I should keep what I did as a memorandum for myself. It ’s just the basics, but ... I would appreciate it if you could point out any mistakes or points that should be improved.

environment Java 8 MySQL ver 8.0.14

Basic code

Use the PreparedStatement class instead of Statement. To prevent sql injection.

package dbAccess;

import java.sql.*;
import java.util.Scanner;

public class DbAccess_pres_select {
	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
           /* DB_HOST:DB_PORT/DB_3 NAMEs are set individually
Since mysql 8 gives a driver timezone error, write as follows*/
			Connection con = DriverManager.getConnection(
				"jdbc:mysql://DB_HOST:DB_PORT/DB_NAME?zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=UTC",
				"hogeuser",
				"Password"
			);
			String line = new Scanner(System.in).nextLine();

			String sql = "SELECT * FROM employee WHERE nm_employee = ?";

			PreparedStatement stmt = con.prepareStatement(sql);

			//1 is?Shows the number and location of.
			stmt.setString(1, line);

			//executeQuery returns a ResultSet type. Search method
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("id");
				String name =rs.getString("name");
				String kana = rs.getString("kana");
				String email = rs.getString("mail");
				String pass = rs.getString("password");
				int idDep = rs.getInt("id_department");

				System.out.println(id + "\t" + name + "\t" + kana +"\t" + email + "\t" + pass + "\t" + idDep);
			}

			stmt.close();
			con.close();
		} catch(ClassNotFoundException e) {
			e.printStackTrace();
		} catch(SQLException e) {
			e.printStackTrace();
		}
	}
}

For DML (INSERT, UPDATE, DELETE) other than SELECT

ʻUse the executeUpdate ()` method. The return value is int type. Returns the updated number.

//abridgement
int i = ps.executeUpdate();
System.out.println("Number of updates:" + i);

Others

~~ To get and disconnect Statement and Connection, create a Util class or something and describe it as a static method in it. Used in Main class. ~~ => There is no problem if it is a stand-alone environment that is used by only one person, but when operating on the server side, use a connection pool to suppress the mass generation of Connections. (Try to make a connection every time even in a stand-alone environment.) nkojima Thank you for your comment!

DbUtil.java

import java.sql.*;

public class DbUtil {
Define 8 with private static final String
   "Hostname value or local server IP address";
   "database name";
   "user name";
   "password";
   "mysql"; //DB to use
   "com." + DBMS + ".cj.jdbc.Driver"; //driver
   "port number";
   "jdbc:" + DBMS + "://" + DB_HOST + ":" + DB_PORT + "/" + DB_NAME + "?zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=UTC"; //DB URL

	//Prohibition of instantiation
	private DbUtil() {
	}

	//Make Connection a single instance
　　　//If it is acquired from multiple applications, use the connection pool.
	private static Connection con;

	//Get a connection
	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		if (con == null || con.isClosed()) {
			Class.forName(DB_DRIVER);
			con = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
		}
		return con;
	}
	
        //Disconnect statement
	public static void closeStatement(PreparedStatement stmt) throws SQLException {
		if (stmt != null) {
			stmt.close();
		}
	}

	//Disconnect
	public static void closeConnection(Connection con) throws SQLException {
		if (con != null) {
			con.close();
		}
	}
}

DAO and DTO

For ** DAO ** class

--Constructor (Specify DB connection as an argument when creating a DAO instance)

public EmployeeDAO(Connection con) {
		this.con = con;
}

--Write the SELECT, ʻINSERT, ʻUPDATE, DELETE methods that return a list of DTO types

public List<EmployeeDTO> selectAll() throws SQLException, ClassNotFoundException {
	/*Declare and initialize List for return*/
        /*sql statement*/
		try {
			/*Execute sql statement and store in List*/
		} finally {
			/*close statement*/
		}
		return rtnList;
	}
public List<EmployeeDTO> empInsert(Arguments for the data you want to insert)throws exceptions{
//Other methods (Update and Delete)

For ** DTO ** classes

--Set column of DB table as field.

public class EmployeeDTO {
	/**Employee ID*/
	private int idEmployee;
	/**Employee name*/
	private String nmEmployee;
	/**Employee name Kana*/
	private String knEmployee;
	/**mail address*/
	private String mailAddress;
	/**password*/
	private String password;
	/**Job title ID*/
	private int idDepartment;

--Create each getter and setter as needed.

/*One case*/
public int getIdEmployee() {
	return idEmployee;
}
public void setIdEmployee(int idEmployee) {
	this.idEmployee = idEmployee;
}