I have issues when inserting data into a mysql database with jdbc. when adding a new user to the database, I get a new connection, create a prepared statement and execute the query. However, no results are shown in the database.
For example, let's assume I manually add a new user in the database with MySql Query Browser.
- I add a new user --> name = Stefano, pin = 1010
- An auto-increment id is generated: id = 1.
Suppose I decide to add a new user programmaticaly:
- I call the method addUser(String username, int pin) --> addUser("pippo", 7636);
- No error occurs
- I open MySql Query Browser and none user is added.
Finally I add a new user manually: name = pluto , pin = 3434.
Now my table result in:
| id | name | pin |
|---|---|---|
| 1 | stefano | 1010 |
| 3 | pluto | 3434 |
id=2 is missing. So pippo has been added but I can't see it.
What's wrong?
Here my java code simplified:
package simpleexample;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class SimpleExample {
public static void addUser(String username, int pin) {
Connection conn = null;
PreparedStatement preparedStmt = null;
String insertSQL = "insert into users(name, pin) values (?, ?)";
try {
conn = DBConnectionPool.getConnection();
preparedStmt = conn.prepareStatement(insertSQL);
preparedStmt.setString(1, username);
preparedStmt.setInt(2, pin);
preparedStmt.execute();
} catch (SQLException ex) {
Logger.getLogger(SimpleExample.class.getName()).log(Level.SEVERE, null, ex);
} finally {
if (conn != null) {
DBConnectionPool.releaseConnection(conn);
}
}
}
public static void main(String[] args) {
addUser("pippo", 7636);
}
}
Here the class DBConnectionPool:
package simpleexample;
import java.util.*;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
/**
* This class simulates a db connection pool
*/
public class DBConnectionPool {
/*
* This code prepare the db connection pool. In particular, it creates the
* free connections queue and defines the db properties.
*/
static {
freeDbConnections = new ArrayList<Connection>();
try {
DBConnectionPool.loadDbProperties();
DBConnectionPool.loadDbDriver();
} catch (ClassNotFoundException e) {
System.out.println("DB DRIVER NOT FOUND!");
System.exit(1);
} catch (IOException e) {
System.out.println("DB CONNECTION POOL ERROR!");
System.exit(2);
}
}
/**
* The db properties (driver, url, login, and password)
*/
private static Properties dbProperties;
/**
* The free connection queue
*/
private static List<Connection> freeDbConnections;
/**
* Returns a free db connection accessing to the free db connection queue.
* If the queue is empty a new db connection will be created.
*
* @return A db connection
* @throws SQLException
*/
public static synchronized Connection getConnection() throws SQLException {
Connection connection;
if (!freeDbConnections.isEmpty()) {
// Extract a connection from the free db connection queue
connection = freeDbConnections.get(0);
DBConnectionPool.freeDbConnections.remove(0);
try {
// If the connection is not valid, a new connection will be
// analyzed
if (connection.isClosed()) {
connection = DBConnectionPool.getConnection();
}
} catch (SQLException e) {
connection = DBConnectionPool.getConnection();
}
} else // The free db connection queue is empty, so a new connection will
// be created
{
connection = DBConnectionPool.createDBConnection();
}
return connection;
}
/**
* Releases the connection represented by <code>pReleasedConnection</code>
* parameter
*
* @param pReleasedConnection The db connection to release
*/
public static synchronized void releaseConnection(
Connection pReleasedConnection) {
// Add the connection to the free db connection queue
DBConnectionPool.freeDbConnections.add(pReleasedConnection);
}
/**
* Creates a new db connection
*
* @return A db connection
* @throws SQLException
*/
private static Connection createDBConnection() throws SQLException {
Connection newConnection = null;
// Create a new db connection using the db properties
// newConnection = DriverManager.getConnection(
// "jdbc:mysql://localhost/resources", "root", "");
newConnection = DriverManager.getConnection(
DBConnectionPool.dbProperties.getProperty("url"),
DBConnectionPool.dbProperties.getProperty("username"),
DBConnectionPool.dbProperties.getProperty("password"));
newConnection.setAutoCommit(false);
return newConnection;
}
private static void loadDbDriver() throws ClassNotFoundException {
Class.forName(DBConnectionPool.dbProperties.getProperty("driver"));
}
/**
* Loads the db properties
*
* @throws IOException
*/
private static void loadDbProperties() throws IOException {
InputStream fileProperties = new FileInputStream("database.properties");
DBConnectionPool.dbProperties = new Properties();
DBConnectionPool.dbProperties.load(fileProperties);
}
}
Note: I have a file Database.properties in the project with
- driver=org.gjt.mm.mysql.Driver
- url=jdbc:mysql://localhost/name_db
- username=root
- password='password'
I already used the DBConnectionPool class in other projects and it's always worked fine. So I don't understand what's wrong. Maybe something about transactions?
