Inserting records into a MySQL table using Java

Question

I created a database with one table in MySQL   CREATE DATABASE iac enrollment system   USE iac enrollment system   CREATE TABLE course      course code CHAR 7       course desc VARCHAR 255  NOT NULL      course chair VARCHAR 255       PRIMARY KEY course code       I tried to insert a record using Java      STEP 1  Import required packages import java sql    import java util     public class SQLInsert      JDBC driver name and database URL static final String JDBC DRIVER    com mysql jdbc Driver     static final String DB URL    jdbc mysql   localhost 3306 iac enrollment system       Database credentials static final String USER    root   static final String PASS    1234    public static void main String   args        Connection conn   null      Statement stmt   null      Scanner scn   new Scanner System in       String course code   null  course desc   null  course chair   null       try              STEP 2  Register JDBC driver         Class forName  com mysql jdbc Driver                STEP 3  Open a connection         System out print   nConnecting to database               conn   DriverManager getConnection DB URL  USER  PASS           System out println   SUCCESS  n                STEP 4  Ask for user input         System out print  Enter course code              course code   scn nextLine             System out print  Enter course description              course desc   scn nextLine             System out print  Enter course chair              course chair   scn nextLine                STEP 5  Excute query         System out print   nInserting records into table               stmt   conn createStatement             String sql    INSERT INTO course                  VALUES  course code  course desc  course chair            stmt executeUpdate sql            System out println   SUCCESS  n           catch SQLException se            se printStackTrace          catch Exception e            e printStackTrace          finally           try               if stmt    null                  conn close              catch SQLException se                      try               if conn    null                  conn close              catch SQLException se                se printStackTrace                        System out println  Thank you for your patronage             The output appears to return successfully    But when I select from MySQL  the inserted record is blank    Why is it inserting a blank record

User · Answer

this can also be done like this if you don't want to use prepared statements.

String sql = "INSERT INTO course(course_code,course_desc,course_chair)"+"VALUES('"+course_code+"','"+course_desc+"','"+course_chair+"');"

Why it didnt insert value is because you were not providing values, but you were providing names of variables that you have used.

User · Answer

There is a mistake in your insert statement chage it to below and try    String sql    insert into table name values       Col1          Col2           Col3

User · Answer

This should work for any table  instead of hard-coding the columns    x000D   x000D    Source details x000D      String sourceUrl    jdbc oracle thin    server 1521 db   x000D      String sourceUserName    src   x000D      String sourcePassword          x000D   x000D         Destination details x000D      String destinationUserName    dest   x000D      String destinationPassword          x000D      String destinationUrl    jdbc mysql   server 3306 db   x000D   x000D      Connection srcConnection   getSourceConnection sourceUrl  sourceUserName  sourcePassword   x000D      Connection destConnection   getDestinationConnection destinationUrl  destinationUserName  destinationPassword   x000D   x000D      PreparedStatement sourceStatement   srcConnection prepareStatement  SELECT    FROM src table     x000D      ResultSet rs   sourceStatement executeQuery    x000D      rs setFetchSize 1000      not needed x000D   x000D   x000D      ResultSetMetaData meta   rs getMetaData    x000D   x000D   x000D   x000D      List lt String gt  columns   new ArrayList lt  gt     x000D      for  int i   1  i  lt   meta getColumnCount    i    x000D          columns add meta getColumnName i    x000D   x000D      try  PreparedStatement destStatement   destConnection prepareStatement  x000D               INSERT INTO dest table    x000D                        columns stream   collect Collectors joining        x000D                           VALUES    x000D                        columns stream   map c - gt       collect Collectors joining        x000D                            x000D                x000D        x000D        x000D          int count   0  x000D          while  rs next      x000D              for  int i   1  i  lt   meta getColumnCount    i      x000D                  destStatement setObject i  rs getObject i    x000D                x000D               x000D              destStatement addBatch    x000D              count    x000D            x000D          destStatement executeBatch       you will see all the rows in dest once this statement is executed x000D          System out println  done     count   x000D   x000D        x000D   x000D   x000D

User · Answer

no that cannot work not with real data    String sql    INSERT INTO course              VALUES  course code  course desc  course chair        stmt executeUpdate sql     change it to   String sql    INSERT INTO course  course code  course desc  course chair              VALUES               Create a  PreparedStatment with that sql and insert the values with index   PreparedStatement preparedStatement   conn prepareStatement sql   preparedStatement setString 1   Test    preparedStatement setString 2   Test2    preparedStatement setString 3   Test3    preparedStatement executeUpdate

[java] Inserting records into a MySQL table using Java

Examples related to java

Examples related to mysql

Examples related to sql

Examples related to jdbc

Examples related to insert-into