Reputation: 21
I'm passing my method InsertQuery
variables from another method which are entered by the user via Scanner
.
How do I fill in the iName
, iType
etc. into my iQuery
so that I can insert them into my DB?
public void InsertQuery (String iName, String iType, int health_Problem, Date date2, String aRemind, String docName, String docType, String docAdress)
{
final String url = "jdbc:mysql://localhost/ehealthdb?serverTimezone=UTC";
final String DBUSER = "root";
final String DBPSWD = "root";
try {
Connection con = DriverManager.getConnection(url,DBUSER,DBPSWD);
Statement stmt = con.createStatement();
String iQuery = "INSERT into appointment"
+ "(ID, PatientID, Insurance_Name, Insurance_Type, Health_Problem, Appointment_Date, Appointment_Remind, Doctor_Name,Doctor_Type,Doctor_Adress)"
+ "values ('1','1',,'Gesetzlich','5','15.01.2020','1 Week','Musterarzt','Hausarzt','Musterstraße')";
stmt.executeUpdate(iQuery);
} catch (Exception e) {
System.out.println("Something went wrong @InsertQuery");
}
}
Upvotes: 2
Views: 115
Reputation: 312404
The easiest approach would probably be to use a PreparedStatement
:
public void insertQuery
(String iName, String iType, int healthProblem, Date date2, String aRemind, String docName, String docType, String docAddress)
throws SQLException {
final String url = "jdbc:mysql://localhost/ehealthdb?serverTimezone=UTC";
final String DBUSER = "root";
final String DBPSWD = "root";
try (Connection con = DriverManager.getConnection(url,DBUSER,DBPSWD);
PreparedStatement stmt = con.prepareStatement(
"INSERT into appointment" +
"(ID, PatientID, Insurance_Name, Insurance_Type, Health_Problem, Appointment_Date, Appointment_Remind, Doctor_Name, Doctor_Type, Doctor_Adress) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
stmt.setString(1, iName);
stmt.setString(2, iType);
stmt.setInt(3, healthProblem);
stmt.setTimestamp(4, new Timestamp(date2.getTime()));
stmt.setString(5, aRemind);
stmt.setString(6, docName);
stmt.setString(7, docType);
stmt.setString(8, docAddress);
stmt.executeUpdate();
}
}
Upvotes: 5
Reputation: 86
I used this way and it is working greatly
public void InsertQuery (String iName, String iType, int health_Problem, Date date2, String aRemind, String docName, String docType, String docAdress)
{
final String url = "jdbc:mysql://localhost/ehealthdb?serverTimezone=UTC";
final String DBUSER = "root";
final String DBPSWD = "root";
try {
Connection con = DriverManager.getConnection(url,DBUSER,DBPSWD);
Statement stmt = con.createStatement();
String iQuery = "INSERT into appointment"
+ "(ID, PatientID, Insurance_Name, Insurance_Type, Health_Problem, Appointment_Date, Appointment_Remind, Doctor_Name,Doctor_Type,Doctor_Adress)"
+ "values ('1','1',,'"+iName+"','5','15.01.2020','1 Week','Musterarzt','Hausarzt','Musterstraße')";
stmt.executeUpdate(iQuery);
} catch (Exception e) {
System.out.println("Something went wrong @InsertQuery");
}
}
Upvotes: -1
Reputation: 79620
The recommended approach is to use PreparedStatement
which solves the following two important problems apart from many other benefits:
Typical usage is as shown below:
String query = "INSERT INTO appointment(ID, PatientID, Insurance_Name, Insurance_Type, Health_Problem) VALUES (?, ?, ?, ?, ?)";
try (PreparedStatement pstmt = con.prepareStatement(query)) {
//...
pstmt.setString(1, id);
pstmt.setString(2, patientId);
pstmt.setString(3, insuranceName);
//...
pstmt.executeUpdate();
} catch(SQLException e) {
e.printStackTrace();
}
Note that for each ?
, you will have to use pstmt.setXXX
. Another thing you need to understand is that in the method call, pstmt.setString(1, Id)
, 1
refers to the first ?
and not the first column in your table.
Some other important points:
Insurance_Name
should be named as insuranceName
.Upvotes: 2
Reputation: 103913
Don't use a statement, use a PreparedStatement
. Otherwise, you get hacked.
More generally, JDBC is a tricky beast and not a particularly nice API. For fairly good reasons - it is designed to be the lowest common denominator, and it is more focused on exposing all the bells and whistles of all databases in existence, than in giving you, programmer who wants to interact with a database, a nice experience.
Your exception handling is also wrong. If you catch an exception, either handle it, or make sure you throw something. Logging it, (or worse, printing it) definitely does not count. Add throws
to your method signature. If that's not possible (and it usually is possible, try that first), throw new RuntimeException("Uncaught", e)
is what you want. not e.printStackTrace()
, or even worse, what you did: You just tossed out all relevant information. Don't do that.
Upvotes: 2