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?
