LoanPrpcessing.jaba

package lab4;

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

import com.mysql.jdbc.Connection;

public class LoanProcessing {

   //"jdbc:mysql://www.papademas.net:3306/411labs?autoReconnect=true&useSSL=false"

   private static String dbURL = "jdbc:mysql://localhost:3306/411labs?autoReconnect=true&useSSL=false";
   private static String username = "root";
   private static String password = "root";

   public static Connection getConnection() throws SQLException {

       Connection connection = null;

       connection = (Connection) DriverManager.getConnection(dbURL, username, password);
       if(connection != null)
           System.out.println("connected");

       return connection;

   }


}

Dao.Java

package lab4;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

public class Dao {

   public static void createTable() throws SQLException {

       Connection connection = LoanProcessing.getConnection();

       Statement stmt = (Statement) connection.createStatement();

   String sql = "CREATE TABLE IF NOT EXISTS fName_Lnam_tab " +
   "(id INTEGER not NULL, " +
   " income DOUBLE, " +
   " pep VARCHAR(255), " +
   " PRIMARY KEY ( id ))";

   stmt.executeUpdate(sql);
   System.out.println("Created table in given database...");

   }

   public static void insertRecords(BankRecords[] records) throws SQLException {

       int idCount = 1;

       for(BankRecords bankRecord : records) {

           String sql = "INSERT INTO fName_Lnam_tab (id, income, pep) VALUES (?, ?, ?)";

           PreparedStatement statement = (PreparedStatement) LoanProcessing.getConnection().prepareStatement(sql);
           statement.setInt(1, idCount);
           statement.setDouble(2, bankRecord.getIncome());
           statement.setString(3, bankRecord.getPep());

           int rowsInserted = statement.executeUpdate();
           if (rowsInserted > 0) {
           System.out.println("A new user was inserted successfully!");
           }

           idCount++;

       }



   }

   public static void retrieveRecords() throws SQLException {

       String sql = "select id,income, pep from fName_Lnam_tab order by pep desc";

       Statement statement = (Statement) LoanProcessing.getConnection().createStatement();
       ResultSet result = statement.executeQuery(sql);

       int count = 0;

       while (result.next()){
       int id = result.getInt("id");
       String income = result.getString("income");
       String pep = result.getString("pep");

       String output = "User #%d: %s - %s - %s - %s";
       System.out.println(String.format(output, ++count, id, income, pep));
       }

   }

}

0 Comments