Java CRUD with MySQL

Example Java using MySQL

• CRUD
  • C - CREATE
  • R - READ
  • U - UPDATE
  • D - DELETE

Model

Model: Person;

public class Person {

private int id;
private String name;
private Date date;
    
//Getters and Setters
}

• See Getters and Setters;

MySQL

• MySQL
  • Database;
  • Tables;
  • Procedures;
  • Views;
• mysql-script: script.sql

Driver JDBC

Driver MySQL: mysql-connector 5.1.15

DAO

Imports

 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.Statement;

Variables

Connection cn;
private final String driver = "com.mysql.jdbc.Driver";
final String userDB = "root";
final String passwordDB = "";
final String databaseDB = "database_test";

private static final String ip = "localhost";
private static final String port = "3307";
private final String url = "jdbc:mysql://" + ip + ":" + port + "/";

ResultSet rs = null;
Statement st = null;
PreparedStatement ps = null;
CallableStatement stmt = null;

Constants

static final String PROCEDURE_INSERT_PERSON = "{ call stp_insert_person (?, ? ) }";
static final String PROCEDURE_UPDATE_PERSON = "{ call stp_update_person (?, ?, ? ) }";
static final String PROCEDURE_DELETE_PERSON = "{ call stp_delete_person (? ) }";

static final String VIEW_PERSON = "SELECT * FROM view_person";

static private final String COLUMN_ID = "id_person";
static private final String COLUMN_NAME = "name_person";
static private final String COLUMN_DATE = "date_person";

• See MySQL

Methods

⚠️ Need add throws declaration or surround with try/catch;

⚠️ It is recommended to use procedures for insert, update and delete and view/procedures for selects;

Method - Connect database

Class.forName(driver);

cn = DriverManager.getConnection(String url,String user, String password);

• See Method Connect
• See Variables
• See Database class

Method - Disconnect database

cn.close();

• See Method Disconnect
• See Database class

Procedure

How to call procedure.

Procedure implemented: PersonDAO

Check connection

if (conect()) {
// TODO 
}

• See Database class

Procedure implementation

CallableStatement cstmt = cn.prepareCall(PROCEDURE_DELETE_PERSON);
cstmt.setInt(1, id);

cstmt.execute();

• See Variables
• See Constants

View

How to select VIEW.

View implemented: PersonDAO;

List

List<Person> persons = new ArrayList<Person>();

View implementation

st = cn.createStatement();
rs = st.executeQuery(VIEW_PERSON);

while (rs.next()) {
persons.add(
    new Person(
        rs.getInt(COLUMN_ID),
        rs.getString(COLUMN_NAME), 
        rs.getDate(COLUMN_DATE)
        )
    );
}

• See Variables
• See Loop While
• See Constants
• See PersonDAO
  • See Integer Array

Exception

• SQLException;
• ClassNotFoundException;

Project

Download: Java MySQL - CRUD

Some links for more in depth learning

• JAVA
• JAVA PRINT;
• JAVA SWITCH CASE;
• JAVA IF/ELSE;
• JAVA ARITHMETIC;