CODEWITHSUNDEEP
javadatabasejdbcsql-insert
P3ngu62
P3ngu62

Reputation: 21

JDBC inserting variables to database

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

Answers (4)

Mureinik
Mureinik

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

Tawfik Yasser
Tawfik Yasser

Reputation: 86

I used this way and it is working greatly

  • for iName
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

Arvind Kumar Avinash
Arvind Kumar Avinash

Reputation: 79620

The recommended approach is to use PreparedStatement which solves the following two important problems apart from many other benefits:

  1. It helps you protect your application from SQL Injection.
  2. You will not have to enclose the text values within single quotes yourself.

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:

  1. I have used try-with-resources statement which is an easier and recommended way to close the resources after the program is finished with it. Learn more about it from Oracle's tutorial on it.
  2. Always follow Java naming conventions e.g. Insurance_Name should be named as insuranceName.

Upvotes: 2

rzwitserloot
rzwitserloot

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.

Try JDBC or JOOQ.

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

Related Questions