[JAVA] [Practice! 】 Execution of SQL statement

1. Prior knowledge

-[Latest] How to build Java environment on Ubuntu

-[Even beginners can do it! ] How to create a Java environment on Windows 10 (JDK14.0.1)

-[Easy-to-understand explanation! ] How to use Java instance

-[Even beginners can do it! ] How to install Eclipse on Windows 10 (Java environment construction)

-[Even beginners can do it! ] How to install MySQL on Windows 10 (MySQL Server 8.0.20)

-[Practice! ] Java database linkage (Connector / J 8.0.20)

As prior knowledge, the contents of the above link are required.

2. Basic writing

Update SQL

public class class name{
	public static void main(String[] args) {
		try {
			/*connection*/
			Connection con = DriverManager.getConnection(Database URL,Database username,Database password);
			/*statement*/
			PreparedStatement pstmt = con.prepareStatement(SQL statement);

			//Assigning a value to an SQL statement
			pstmt.setString(What number?Or,value);

			/*Execution of SQL statement*/
			pstmt.executeUpdate(); 

		} catch (SQLException e) {
			//Processing when SQL error occurs
		}
	}
}

Reference SQL

public class class name{
	public static void main(String[] args) {
		try {
			/*connection*/
			Connection con = DriverManager.getConnection(Database URL,Database username,Database password);
			/*statement*/
			PreparedStatement pstmt = con.prepareStatement(SQL statement);

			//Assigning a value to an SQL statement
			pstmt.setString(What number?Or,value);

			//Execution of SQL statement
			ResultSet res = pstmt.executeQuery();

			//Operation of ResultSet
			while (res.next()) {
				//Display of execution results
			}

		} catch (SQLException e) {
			//Processing when SQL error occurs
		}
	}
}

--The execution of basic SQL statements is described as above.

3. Advance preparation

• [Practice! ] Java database linkage (Connector / J 8.0.20) is assumed to have been set to database settings.

1. After starting Eclipse, select [New] → [Java Project].
2. Enter TestDB for the project name, select JavaSE-1.8 to use the execution environment JRE, and click the Finish button.
3. Select [New (N)] → [Class].
4. Enter db in the package and Test1 in the name, check public static void main (String [] args), and click the Finish button.
5. Right-click on the project and select [Build Path (B)] → [Add External Archive (V)].
6. Select mysql-connector-java-8.0.20.jar in C: \ Program Files \ MySQL \ mysql-connector-java-8.0.20.

4. Description example

--Reference (SELECT statement)

Test1.java

package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test1 {
	public static void main(String[] args) {
		/*Database URL*/
		String url = "jdbc:mysql://localhost/test?characterEncoding=UTF-8&serverTimezone=JST&useSSL=false";

		/*Database user and password*/
		String user = "test";
		String password = "test";

		/*SQL statement*/
		String sql = "SELECT * FROM test1";

		try {
			/*connection*/
			Connection con = DriverManager.getConnection(url, user, password);
			/*statement*/
			PreparedStatement pstmt = con.prepareStatement(sql);

			//Execution of SQL statement
			ResultSet res = pstmt.executeQuery();

			//Operation of ResultSet
			while (res.next()) {
				//Display of execution results
				System.out.print("ID：" + res.getInt("id") + " ");
				System.out.print("name:" + res.getString("name") + "\n");
			}
		} catch (SQLException e) {
			//Processing when SQL error occurs
			e.printStackTrace();
		}
	}
}

--Registration (INSERT statement)

Test1.java

package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test1 {
	public static void main(String[] args) {
		/*Database URL*/
		String url = "jdbc:mysql://localhost/test?characterEncoding=UTF-8&serverTimezone=JST&useSSL=false";

		/*Database user and password*/
		String user = "test";
		String password = "test";

		/*SQL statement*/
		String sql = "INSERT INTO test1 (name) VALUE (?)";

		try {
			/*connection*/
			Connection con = DriverManager.getConnection(url, user, password);
			/*statement*/
			PreparedStatement pstmt = con.prepareStatement(sql);

			//Set value in placeholder
			pstmt.setString(1,"test5");

			//SQL statement execution (update)
			pstmt.executeUpdate();

			//SQL statement update
			sql = "SELECT * FROM test1";

			/*statement*/
			pstmt = con.prepareStatement(sql);

			//Execution of SQL statement (see)
			ResultSet res = pstmt.executeQuery();

			//Operation of ResultSet
			while (res.next()) {
				//Display of execution results
				System.out.print("ID：" + res.getInt("id") + " ");
				System.out.print("name:" + res.getString("name") + "\n");
			}
		} catch (SQLException e) {
			//Processing when SQL error occurs
			e.printStackTrace();
		}
	}
}

--Change (UPDATE statement)

Test1.java

package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test1 {
	public static void main(String[] args) {
		/*Database URL*/
		String url = "jdbc:mysql://localhost/test?characterEncoding=UTF-8&serverTimezone=JST&useSSL=false";

		/*Database user and password*/
		String user = "test";
		String password = "test";

		/*SQL statement*/
		String sql = "UPDATE test1 SET name = ? WHERE id = ?";

		try {
			/*connection*/
			Connection con = DriverManager.getConnection(url, user, password);
			/*statement*/
			PreparedStatement pstmt = con.prepareStatement(sql);

			//Set value in placeholder
			pstmt.setString(1,"testX");
			pstmt.setInt(2,2);

			//SQL statement execution (update)
			pstmt.executeUpdate();

			//SQL statement update
			sql = "SELECT * FROM test1";

			/*statement*/
			pstmt = con.prepareStatement(sql);

			//Execution of SQL statement (see)
			ResultSet res = pstmt.executeQuery();

			//Operation of ResultSet
			while (res.next()) {
				//Display of execution results
				System.out.print("ID：" + res.getInt("id") + " ");
				System.out.print("name:" + res.getString("name") + "\n");
			}
		} catch (SQLException e) {
			//Processing when SQL error occurs
			e.printStackTrace();
		}
	}
}

--Delete (DELETE statement)

Test1.java

package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test1 {
	public static void main(String[] args) {
		/*Database URL*/
		String url = "jdbc:mysql://localhost/test?characterEncoding=UTF-8&serverTimezone=JST&useSSL=false";

		/*Database user and password*/
		String user = "test";
		String password = "test";

		/*SQL statement*/
		String sql = "DELETE FROM test1 WHERE id = ?";

		try {
			/*connection*/
			Connection con = DriverManager.getConnection(url, user, password);
			/*statement*/
			PreparedStatement pstmt = con.prepareStatement(sql);

			//Set value in placeholder
			pstmt.setInt(1,3);

			//Execution (deletion) of SQL statement
			pstmt.executeUpdate();

			//SQL statement update
			sql = "SELECT * FROM test1";

			/*statement*/
			pstmt = con.prepareStatement(sql);

			//Execution of SQL statement (see)
			ResultSet res = pstmt.executeQuery();

			//Operation of ResultSet
			while (res.next()) {
				//Display of execution results
				System.out.print("ID：" + res.getInt("id") + " ");
				System.out.print("name:" + res.getString("name") + "\n");
			}
		} catch (SQLException e) {
			//Processing when SQL error occurs
			e.printStackTrace();
		}
	}
}

Copy the above sentence, specify S-JIS as the character code, save the file name as Test1.java, and execute it. ↓ ↓

--Reference (SELECT statement)

--Registration (INSERT statement)

--Change (UPDATE statement)

--Delete (DELETE statement)

5. Related

-[Useful to remember !!!] Easy creation of constructor and getter / setter in Eclipse -[Useful to remember !!!] Easy creation of inherited class in Eclipse -[Useful to remember !!!] Change MySQL character code -[Even beginners can do it! ] How to write Javadoc -[Easy-to-understand explanation! ] How to use Java overload -[Easy-to-understand explanation! ] How to use Java encapsulation -[Easy-to-understand explanation! ] How to use Java inheritance [Override explanation] -[Easy-to-understand explanation! ] Type conversion of reference type in Java -[Easy-to-understand explanation! ] How to use Java polymorphism -[Easy-to-understand explanation! ] How to use ArrayList [Java] -[Practice! ] Introduction of JFrame (explanation up to screen creation)