I am a very beginner to Java and for one of my assignments. I have to do database operations. I have this Java code for inserting data to a database and it works fine.
public class JavaDBWork
{
public static void main(String[] args)
{
try
{
// create a mysql database connection
String myDriver = "org.gjt.mm.mysql.Driver";
String myUrl = "jdbc:mysql://localhost/test";
Class.forName(myDriver);
Connection conn = DriverManager.getConnection(myUrl, "root", "");
// create a sql date object so we can use it in our INSERT statement
Calendar calendar = Calendar.getInstance();
java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());
// the mysql insert statement
String query = " insert into users (first_name, last_name, date_created, is_admin, num_points)"
+ " values (?, ?, ?, ?, ?)";
//Other Code
}
My question is every time the code runs, my code has to create a database connection and prepared statement objects and as I understand it is a costly operation.
Is there any way that I can keep a single connection to database and use that to perform my database work?
You can use the design pattern Singleton Connection like this :
1- create a class SingletonConnection.java look like that :
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.EmptyStackException;
public class SingletonConnection {
private static Connection connection = null ;
static
{
try{
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection
("jdbc:mysql://localhost:3306/gestion_comptes","root","");
}catch(Exception ex)
{
}
}
public static Connection getConnection() throws Exception {
return connection;
}
}
And in your other class call it like that :
public class DaoImpl{
public Connection connection = SingletonConnection.getConnection();
public DaoImpl() throws Exception
{
if(connection==null)
throw new Exception("impossible de se connecter à la base de données");
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Calendar;
public class JavaDBWork {
private final String DATBASE = "myDatabasename";
private final String URL = "jdbc:mysql://localhost:3306/" + DATBASE + "?useSSL=false";
private final String USERNAME = "myUsername";
private final String PASSWORD = "myPassword";
private Connection databaseConnection;
// Main Method to init
public static void main(String[] args) {
// build an javaDBWork with init connection one time
JavaDBWork javaDBWork = new JavaDBWork();
// call methods in javaDBWork you like
javaDBWork.doSomething();
}
public JavaDBWork() {
// create a mysql database connection at init
this.databaseConnection = databaseConnection();
}
private Connection databaseConnection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (Exception exception) {
exception.printStackTrace();
}
return connection;
}
public void doSomething() {
try {
// create a sql date object so we can use it in our INSERT statement
Calendar calendar = Calendar.getInstance();
java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());
// the mysql insert statement
String sql = " insert into users (first_name, last_name, date_created, is_admin, num_points)" + " values (?, ?, ?, ?, ?)";
PreparedStatement preparedStatement = null;
preparedStatement = databaseConnection.prepareStatement(sql);
preparedStatement.setString(1, "Lucky");
preparedStatement.setString(2, "Luke");
...
preparedStatement.executeUpdate();
} catch (Exception e) {
// TODO: handle exception
}
}
You can do something like this.
Init the database connection one time and hold the connection in a object which you call when you need.
EDIT:
Example when you need connection in another class:
public class MyMainClass {
private JavaDBWork javaDBWork;
// Main Method to init
public static void main(String[] args) {
new MyMainClass();
}
public MyMainClass() {
// build an javaDBWork with init connection one time
javaDBWork = new JavaDBWork();
// call methods in javaDBWork you like
javaDBWork.doSomething();
}
}
public class JavaDBWork {
private final String DATBASE = "myDatabasename";
private final String URL = "jdbc:mysql://localhost:3306/" + DATBASE + "?useSSL=false";
private final String USERNAME = "myUsername";
private final String PASSWORD = "myPassword";
private Connection databaseConnection;
public JavaDBWork() {
// create a mysql database connection at init
this.databaseConnection = databaseConnection();
}
private Connection databaseConnection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (Exception exception) {
exception.printStackTrace();
}
return connection;
}
public void doSomething() {
try {
// create a sql date object so we can use it in our INSERT statement
Calendar calendar = Calendar.getInstance();
java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());
// the mysql insert statement
String sql = " insert into users (first_name, last_name, date_created, is_admin, num_points)" + " values (?, ?, ?, ?, ?)";
PreparedStatement preparedStatement = null;
preparedStatement = databaseConnection.prepareStatement(sql);
preparedStatement.setString(1, "Lucky");
preparedStatement.setString(2, "Luke");
preparedStatement.executeUpdate();
} catch (Exception e) {
// TODO: handle exception
}
}
}
