JDBC Programming Goal: Write at least two classes that will store and retrieve d

Question

JDBC Programming

Goal: Write at least two classes that will store and retrieve data of your choice. Deliverables: An Eclipse project that has two classes, which load and display your data. Details: Choose a data schema that contains at least five columns, one of which is a primary key. Create a .csv file with at least eight data lines. The first row optionally contains the column names. Write a Load class with a main method that loads the data from the .csv file into the database table. Download the SQLite3 database driver sqlite-jdbc-3.8.7.jar to the folder where you are keeping your Java projects.

Select Projects >> Properties >> Java Build Path >> Libraries Tab >> Add External Jars... Button. Browse for and

open sqlite-jdbc-3.7.2.jar;

click OK. Write a Display class that inputs a primary key,

then displays on the screen the corresponding data in a row of the table.

Revent Example

Explanation / Answer

you can use the below program to load the data from csv to database

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Date;
import org.apache.commons.lang.StringUtils;
public class CSVLoader {

   private static final
       String SQL_INSERT = "INSERT INTO ${table}(${keys}) VALUES(${values})";
   private static final String TABLE_REGEX = "\$\{table\}";
   private static final String KEYS_REGEX = "\$\{keys\}";
   private static final String VALUES_REGEX = "\$\{values\}";

   private Connection conn;
   private char sep;

   public CSVLoader(Connection conn) {
       this.conn = conn;
       this.sep = ',';
   }
  
   public void loadCSV(String csvFile, String tabName,
           boolean truncateBeforeLoad) throws Exception {

       CSVReader csvReader = null;
       if(null == this.conn) {
           throw new Exception("Not a valid conn.");
       }
       try {
          
           csvReader = new CSVReader(new FileReader(csvFile), this.sep);

       } catch (Exception e) {
           e.printStackTrace();
           throw new Exception("Error occured while executing file. "
                   + e.getMessage());
       }

       String[] header = csvReader.readNext();

       if (null == header) {
           throw new FileNotFoundException("check the CSV file format.no columns defined");
       }

       String quesmarks = StringUtils.repeat("?,", header.length);
       quesmarks = (String) quesmarks.subSequence(0, quesmarks
               .length() - 1);

       String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tabName);
       query = query
               .replaceFirst(KEYS_REGEX, StringUtils.join(header, ","));
       query = query.replaceFirst(VALUES_REGEX, quesmarks);

       System.out.println("Query: " + query);

       String[] nextLine;
       Connection con = null;
       PreparedStatement ps = null;
       try {
           con = this.conn;
           con.setAutoCommit(false);
           ps = con.prepareStatement(query);

           if(truncateBeforeLoad) {
               //delete data from table before loading csv
               con.createStatement().execute("DELETE FROM " + tabName);
           }

           final int batchSize = 1000;
           int count = 0;
           Date date = null;
           while ((nextLine = csvReader.readNext()) != null) {

               if (null != nextLine) {
                   int index = 1;
                   for (String string : nextLine) {
                       date = DateUtil.convertToDate(string);
                       if (null != date) {
                           ps.setDate(index++, new java.sql.Date(date
                                   .getTime()));
                       } else {
                           ps.setString(index++, string);
                       }
                   }
                   ps.addBatch();
               }
               if (++count % batchSize == 0) {
                   ps.executeBatch();
               }
           }// insert remaining records
           con.commit();
       } catch (Exception e) {
           con.rollback();
           e.printStackTrace();
           throw new Exception(error loading data from file to database."
                           + e.getMessage());
       } finally {
           if (null != ps)
               ps.close();
           if (null != con)
               con.close();

           csvReader.close();
       }
   }

   public char getsep() {
       return sep;
   }

   public void setsep(char sep) {
       this.sep = sep;
   }

}

****************************************************************************

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MainMethodClass{

   private static String JDBC_CONNECTION_URL = "jdbc:sqlite:kids.db";

  
   public static void main(String[] args) {
       try {

           CSVLoader loader = new CSVLoader(getCon());
          
           loader.loadCSV("C:\filenamecsv", "CUSTOMER", true);
          
       } catch (Exception e) {
           e.printStackTrace();
       }
   }

   private static Connection getCon() {
       Connection conn= null;
       try {
           Class.forName("jdbc:sqlite:kids.db");
           conn= DriverManager.getConnection(JDBC_CONNECTION_URL);

       } catch (ClassNotFoundException e) {
           e.printStackTrace();
       } catch (SQLException e) {
           e.printStackTrace();
       }

       return conn;
   }
}

*************************************************************************

Related Questions

Navigate

• Browse (All)
• Browse J
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.