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);

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)
        )
    );
}