Sometimes, you might find yourself needing to communicate with a database such as MySQL programmatically. Maybe you are creating a CRUD (Create, Read, Update, and Delete) application. Or maybe you don’t have a direct connection to the database.
In any case, knowing how to programmatically talk with MySQL will be beneficial to you as a software developer. Note: I am going to assume you are not a complete beginner to programming for the content of this post.
What Tools You Need
- A development environment for Java (e.g. Spring Tool or Eclipse)
- MySQL installed and running on your host machine
- JDBC driver for Java to interact with MySQL database
Connecting to MySQL
To connect to the MySQL server, you will need to dynamically load the JDBC (Java Database Connectivity) driver. Once the JDBC driver loads successfully, you can follow up by establishing a connection to the database with your login credentials. Note for this post, the login credentials are in the code. You wouldn’t do this in real-world usage. You would probably want to have the credentials encrypted in a config file.
private Connection databaseConnection = null;
private String url = "jdbc:mysql://localhost/?useSSL=false";
private String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
public void startJDBC() {
try {
Class.forName(JDBC_DRIVER);
establishDatabaseConnection();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private void establishDatabaseConnection() throws SQLException {
if (databaseConnection == null) {
databaseConnection = DriverManager.getConnection(url, "root", "brightdevelopers");
}
}
Creating and Selecting a Database
Once you connect to the MySQL server, you will want to select the database you want to use. If the database does not exist yet, then you would want to create it.
private String DATABASE_NAME = "products";
private String CREATE_PRODUCTS_DATABASE = "CREATE DATABASE " + DATABASE_NAME;
private String USE_DATABASE = "USE " + DATABASE_NAME;
private void createDatabase() throws SQLException {
if (databaseConnection == null) {
establishDatabaseConnection();
}
Statement sqlStatement = databaseConnection.createStatement();
if (!databaseExists()) {
sqlStatement.executeUpdate(CREATE_PRODUCTS_DATABASE);
}
}
private boolean databaseExists() throws SQLException {
if (databaseConnection == null) {
establishDatabaseConnection();
}
ResultSet allDatabasesSet = databaseConnection.getMetaData().getCatalogs();
while (allDatabasesSet.next()) {
String databaseName = allDatabasesSet.getString(1);
if (databaseName.equals(DATABASE_NAME)) {
return true;
}
}
return false;
}
private void selectProductDatabase() throws SQLException {
if (databaseConnection == null) {
establishDatabaseConnection();
}
Statement sqlStatement = databaseConnection.createStatement();
sqlStatement.executeUpdate(USE_DATABASE);
}
Creating a Table
Once you have chosen a database it is time for the tables within the database. If the tables you need to use already exists then all is fine and you can start querying and updating them. But what if some of them are missing? You’ll have to create each one of them. Here is a way for you to check if a table exists or not:
private static String tableName = "items";
private boolean tableExists() throws SQLException {
if (databaseConnection == null) {
establishDatabaseConnection();
}
ResultSet allTablesSet = databaseConnection.getMetaData().getTables(null, null, tableName, new String[] {"TABLE"});
while(allTablesSet.next()) {
String nameOfTable = allTablesSet.getString("TABLE_NAME");
if (nameOfTable.equals(tableName)) {
return true;
}
}
return false;
}
Primary Key
Depending on what data your table will hold you may want to have it contain only unique data. For example, in a table of products you want the product ID to only appear once in the table. In this case, when you create the table, you would want to identify the product ID as the Primary Key of the table. This means that there can only be one of that Primary Key in the table at any one time.
private String CREATE_TABLE_PRIMARY_KEY = "CREATE TABLE " + tableName + " (id INTEGER not NULL, name VARCHAR(255), PRIMARY KEY (id))";
private void createTableWithPrimaryKey() throws SQLException {
if (databaseConnection == null) {
establishDatabaseConnection();
}
Statement sqlStatement = databaseConnection.createStatement();
if (!tableExists()) {
sqlStatement.executeUpdate(CREATE_TABLE_PRIMARY_KEY);
}
}
To create a table where you do allow duplicates, simply remove the Primary Key constriction when creating the table.
private String CREATE_TABLE = "CREATE TABLE " + tableName + " (id INTEGER not NULL, name VARCHAR(255))";
private void createTable() throws SQLException {
if (databaseConnection == null) {
establishDatabaseConnection();
}
Statement sqlStatement = databaseConnection.createStatement();
if (!tableExists()) {
sqlStatement.executeUpdate(CREATE_TABLE);
}
}
Adding a Row into a Table
Now that you have the database and table ready to go, you probably want to start adding to them now. To add a row or tuple into a table, you can programmatically prepare a MySQL query for insertion. One thing to be careful of here is to not add a duplicate to the table if you are using Primary Key. That would result in an SQL Exception.
public void addItem(String name, int id) throws SQLException {
if (databaseConnection != null) {
establishDatabaseConnection();
selectProductDatabase();
}
if (databaseExists() && tableExists() && !containsItem(id)) {
String query = " insert into items (id, name)" + " values (?, ?)";
PreparedStatement preparedStatement = databaseConnection.prepareStatement(query);
preparedStatement.setInt(1, id);
preparedStatement.setString(2, name);
preparedStatement.execute();
}
}
public boolean containsItem(int itemId) throws SQLException {
if (databaseConnection != null) {
establishDatabaseConnection();
selectProductDatabase();
}
String query = "SELECT id FROM items WHERE id = " + itemId;
PreparedStatement idQuery = databaseConnection.prepareStatement(query);
ResultSet results = idQuery.executeQuery(query);
while (results.next()) {
int id = results.getInt(1);
if (id == itemId) {
return true;
}
}
return false;
}
I hope this post was helpful to you. If you found this post helpful, share it with others so they can benefit too.
To get in touch, you can follow me on Twitter, leave a comment, or send me an email at steven@brightdevelopers.com.