JAVA AND DATABASES
In this article we will see how to use the popular DBMS (Database Management System) MySQL Server in Java. We will start with the installation of MySQL, I am working with a MacOS Ventura and the procedure I will show you applies to this Operating System. For Windows and Linux refer to the MySQL documentation. Go with your browser to https://dev.mysql.com/downloads/mysql/ and select your operating system and the right architecture (in my case macOS version of MySQL 8.0.34 ARM 64 architecture) and download the file.
Now with a video I will walk you through the installation procedure on macOS.
MySQL Server remains listening on port 3306 by default, in my case the instance startup fails because on port 3306 I have another instance of MySQL on Container Docker. I need to change the default port, setting, for example, 3307. In the video I show you how to do it on macOS. If you do not have this requirement, go ahead and leave the default port 3306.
Once you have installed MySQL you need to install MySQL Workbench 8.0.34 by going to the following URL: https://dev.mysql.com/downloads/workbench/ I have already downloaded and installed the program, the procedure is very simple. Now with a video I show you how to configure the connection to MySQL. Of course, you have to make sure that the MySQL instance is active.
Once you have configured the connection open the project downloaded from GITHUB, go to the following package it.corso.java.database, here you will find a file with .sql extension to copy and paste into MySQL Workbench to create database and table. You will also find example code in this package. Be sure to enter your MySQL instance password and port in the code. Before you can run that code, you must install the JDBC Connector which you find already available in the GITHUB code. In the next video I show you how to configure the JDBC driver in Visual Studio Code.
One final observation about the editor. At the beginning of this course I recommended Visual Studio Code because it is a full-featured, feature-rich, free editor. However if you intend to develop in Java my advice is to choose a professional editor such as IntelliJ IDEA, it is a paid editor, but I assure you it is 20€ or so monthly well spent.
package it.corso.java.database;
import com.mysql.cj.jdbc.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Clienti {
private Connection connection;
public static void main(String[] args) {
try
{
Clienti clienti = new Clienti();
clienti.Insert("Marco","Albasini","malbasini@gmail.com","999999999");
clienti.Insert("Mario","Rossi","mario.rossi@gmail.com","2222222222");
clienti.Insert("Luca","Verdi","luca.verdi@gmail.com","1223445678");
clienti.Update("12345678",1);
clienti.Select("Rossi");
clienti.Delete(3);
}
catch(SQLException e){
String s = e.getMessage();
System.out.println(s);
}
}
private void Insert(String nome, String cognome, String email, String telefono) throws SQLException {
String sql = "INSERT INTO clienti(nome, cognome, email, telefono) "+
"VALUES('"+nome+"', '"+cognome+"', '"+email+"', '"+telefono+"')";
PreparedStatement ps = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
rs.next();
System.out.println("l'id generato è: " + rs.getInt(1));
}
private void Select(String cognome) throws SQLException {
String sql = "SELECT * FROM clienti WHERE cognome = ?";
PreparedStatement ps = getConnection().prepareStatement(sql);
ps.setString(1, cognome);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
System.out.println("id = " + rs.getInt(1));
System.out.println("nome = " + rs.getString(2));
System.out.println("cognome = " + rs.getString(3));
System.out.println("email = " + rs.getString(4));
System.out.println("telefono = " + rs.getString(5));
System.out.println("------------------------------------------------");
}
}
private void Delete(int id) throws SQLException {
String sql = "DELETE FROM clienti WHERE id = ?";
PreparedStatement ps = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setInt(1, id);
ps.executeUpdate();
connection.close();
}
private void Update(String telefono, int id) throws SQLException {
String sql = "UPDATE clienti SET telefono = ? WHERE id = ?";
PreparedStatement ps = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, telefono);
ps.setInt(2, id);
ps.executeUpdate();
}
private Connection getConnection() throws SQLException {
if(connection == null) {
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setServerName("127.0.0.1");
dataSource.setPortNumber(3307);
dataSource.setUser("root");//root
dataSource.setPassword("**************");//pwd di MySQL
dataSource.setDatabaseName("CorsoJava");
connection = dataSource.getConnection();
}
return connection;
}
}
LINKS TO PREVIOUS POSTS
LINK TO CODE ON GITHUB
EXECUTION OF THE EXAMPLE CODE
For this example, put the main of the file Clienti.java running, which is located in the package it.corso.java.database.
Leave A Comment