I'm having trouble doing an insert from my Java application to my MySQL database from my server. From a cycle for I do an insert, the first works without any problem, but then does nothing but insert, either the application fails or receive any error.
private Database _connect() {
try {
if (this._conn == null || this._conn.isValid(0)) {
try {
Class.forName("com.mysql.jdbc.Driver");
Properties connProps = new Properties();
connProps.put("user", Config.Config.DB_USER);
connProps.put("password", Config.Config.DB_PASS);
this._conn = DriverManager.
getConnection("jdbc:" + Config.Config.DB_DBMS + "://" + Config.Config.DB_HOST + ":"
+ Config.Config.DB_PORT + "/" + Config.Config.DB_NAME, Config.Config.DB_USER, Config.Config.DB_PASS);
timer = System.currentTimeMillis();
} catch (ClassNotFoundException e) {
System.out.println("Where is your MySQL JDBC Driver?");
e.printStackTrace();
} catch (Exception e) {
System.out.println("Could not connect to DB");
e.printStackTrace();
}
} else {
try {
long tmp = System.currentTimeMillis() - timer;
if (tmp > 1200000) { //3600000 one hour ; 1200000 twenty minutes
System.out.println("Forcing reconnection ("+tmp+" milliseconds passed since last connection)");
this.close();
this._connect();
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("Forcing reconnection");
this._conn = null;
this._connect();
}
}
} catch (Exception e) {
e.printStackTrace();
}
return this;
}
So insert:
public class File {
/*db preventive*/
private Statement sment = null;
private PreparedStatement psment = null;
private ResultSet rset = null;
public File(){
}
public boolean insert(Schema file) {
Connection conn = Database.connect().get();
String sql = "";
try {
sql = "";
psment = conn.prepareStatement("INSERT INTO files (evento_id, user_id, path, thumb, preview, width, height, md5, numero_corredor, created, modified) "
+ "VALUES (?,?,?,?,?,?,?,?,?,NOW(),NOW())");
psment.setInt(1, file.getEventId());
psment.setInt(2, file.getUserId());
psment.setString(3, file.getPath());
if (file.getPreview() == null)
psment.setNull(4, java.sql.Types.VARCHAR);
else
psment.setString(4, file.getPreview());
psment.setString(5, file.getThumb());
psment.setInt(6, file.getWidth());
psment.setInt(7, file.getHeight());
psment.setString(8, file.getMd5());
psment.setString(9, "sin_numero");
psment.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
//if there's error, better to have the sql insert at hand to do it manually
FileWriter fStream;
try {
java.io.File yourFile = new java.io.File("insert SQL.txt");
if (!yourFile.exists()) {
yourFile.createNewFile();
}
fStream = new FileWriter("insert SQL.txt", true);
sql = "INSERT INTO files (evento_id, user_id, path, preview, thumb, md5, width, height, created, modified) "
+ "VALUES ("+file.getEventId()+","+file.getUserId()+",'"+file.getPath()+"','"+file.getPreview()+"','"+file.getThumb()+"','"+file.getMd5()+"',"
+"',"+file.getWidth()+","+file.getHeight()+",NOW(),NOW());";
fStream.append(sql);
fStream.append(System.getProperty("line.separator"));
fStream.flush();
fStream.close();
} catch (Exception ex) {
}
return false;
} finally {
close();
}
return true;
}
/**
*
*/
private void close() {
try { if (rset != null) rset.close(); } catch (Exception e) {};
try { if (psment != null) psment.close(); } catch (Exception e) {};
try { if (sment != null) sment.close(); } catch (Exception e) {};
}
}