WIP: Integrating SQL databases into GUI's with Java

So, here's the base of some code that I'm writing the uses Java
to access a SQL database with a GUI layed over it. I happen to think this
is pretty neat:
import java.sql.*;

import javax.swing.JFrame;

public class mysqlTemplate{

public static void main(String args[]){

try {

String url = "jdbc:mysql://localhost:3306/yourdatabase";
String username = "yourusernameforsql";
String password = "yourpasswordforsql";
Connection connection = null;

System.out.println("Connecting database...");
/*This is the connection url for MySQL database.
*Each driver has a different syntax for the url.
*In our case, we provide a host, a port and a database name.
**/
connection = DriverManager.getConnection(url, username, password);
System.out.println("Database connected!");
/*
* The createStatement() method of the connection object creates
* a Statement object for sending SQL statements to the database
* The createStatement() method of the connection object executes the given SQL statement,
* which returns a single ResultSet object.
* */
Statement stmt = connection.createStatement();
try{
String table =
"CREATE TABLE Employee(Emp_Id INT(10), Emp_Name VARCHAR(20) NOT NULL, Emp_Hire_Date DATE NOT NULL, Emp_Birth_Date DATE NOT NULL, Emp_Sex VARCHAR(5) NOT NULL, Emp_Job_Status VARCHAR(5) NOT NULL, Emp_Pay_Type VARCHAR(5) NOT NULL, Emp_Annual_Salary DOUBLE NOT NULL, Emp_Years_Of_Service INT(10) NOT NULL, PRIMARY KEY(Emp_Id))";
stmt.executeUpdate(table);
System.out.println("Table creation process successfully!");
}
catch(SQLException s){
System.out.println("Table already exists!");
}

//BEFORE CONNECTION CLOSE!!!!!
connection.close();
}
catch (Exception e){
e.printStackTrace();
}
}

public static void Insert(int id, String lastName, String HireDate, String birthDate,
String sex, String jobStatus, String payType, double salary, int years) {
try {

String url = "jdbc:mysql://localhost:3306/test";

String username = "root";
String password = "aramiss";
Connection connection = null;

System.out.println("Connecting database...");

connection = DriverManager.getConnection(url, username, password);
System.out.println("Database connected!");

Statement stmt = connection.createStatement();
try {
String insert = "INSERT INTO employee VALUES(\'" + id
+ "\', \'" + lastName + "\', \'" + HireDate + "\', \'"
+ birthDate + "\', \'" + sex + "\', \'" + jobStatus
+ "\', \'" + payType + "\', \'" + salary + "\', \'"
+ years + "\');";
stmt.executeUpdate(insert);
System.out.println("New Employee Added");
} catch (SQLException s) {
System.out.println("Code Fail!");
}

connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}

public static void Delete(int id) {
try {
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "aramiss";
Connection connection = null;

System.out.println("Connecting database...");

connection = DriverManager.getConnection(url, username, password);
System.out.println("Database connected!");

Statement stmt = connection.createStatement();
try {
String delete = "DELETE FROM employee WHERE Emp_ID = "
+ id + ";";
stmt.executeUpdate(delete);
System.out.println("Employee deleted!");
} catch (SQLException s) {
System.out.println("Delete failed");
}

connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}

public static String[][] Search(int id) {
String[][] Data = null;
try {
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "aramiss";
Connection connection = null;

System.out.println("Connecting database...");

connection = DriverManager.getConnection(url, username, password);
System.out.println("Database connected!");

Statement stmt = connection.createStatement();
try {
ResultSet count = stmt
.executeQuery("SELECT count(*) FROM employee WHERE Emp_ID = \'"
+ id + "\';");
count.next();
int size = count.getInt(1);
Data = new String[size][9];

ResultSet execute = stmt
.executeQuery("SELECT * FROM employee WHERE Emp_ID = \'"
+ id + "\';");
for (int i = 0; execute.next(); i++) {
for (int j = 0; j < 9; j++) { Data[i][j] = execute.getString(j + 1); } } System.out.println("Search complete"); } catch (SQLException s) { System.out.println("Search failed: Employee(s) with id = " + id + " not found"); } connection.close(); } catch (Exception e) { e.printStackTrace(); } return Data; } public static String[][] Search1() { String[][] Data = null; try { String url = "jdbc:mysql://localhost:3306/test"; String username = "root"; String password = "aramiss"; Connection connection = null; System.out.println("Connecting database..."); connection = DriverManager.getConnection(url, username, password); System.out.println("Database connected!"); Statement stmt = connection.createStatement(); try { ResultSet count = stmt .executeQuery("SELECT count(*) FROM employee WHERE Emp_Birth_Date < '1972-12-03' AND Emp_Job_Status = 'FT' AND Emp_Pay_Type = 'S' AND Emp_Years_Of_Service > '3';");
count.next();
int size = count.getInt(1);
Data = new String[size][9];

ResultSet execute = stmt
.executeQuery("SELECT * FROM employee WHERE Emp_Birth_Date < '1972-12-03' AND Emp_Job_Status = 'FT' AND Emp_Pay_Type = 'S' AND Emp_Years_Of_Service > '3';");
for (int i = 0; execute.next(); i++) {
for (int j = 0; j < 9; j++) { Data[i][j] = execute.getString(j + 1); } } System.out.println("Search complete"); } catch (SQLException s) { System.out.println("Search failed: Employee(s) not found"); } connection.close(); } catch (Exception e) { e.printStackTrace(); } return Data; } public static String[][] Search2() { String[][] Data = null; try { String url = "jdbc:mysql://localhost:3306/test"; String username = "root"; String password = "aramiss"; Connection connection = null; System.out.println("Connecting database..."); connection = DriverManager.getConnection(url, username, password); System.out.println("Database connected!"); Statement stmt = connection.createStatement(); try { ResultSet count = stmt .executeQuery("SELECT count(*) FROM employee WHERE Emp_Sex = 'F' AND Emp_Annual_Salary > '40000.00';");
count.next();
int size = count.getInt(1);
Data = new String[size][9];

ResultSet execute = stmt
.executeQuery("SELECT * FROM employee WHERE Emp_Sex = 'F' AND Emp_Annual_Salary > '40000.00';");
for (int i = 0; execute.next(); i++) {
for (int j = 0; j < 9; j++) { Data[i][j] = execute.getString(j + 1); } } System.out.println("Search complete"); } catch (SQLException s) { System.out.println("Search failed: Employee(s) not found"); } connection.close(); } catch (Exception e) { e.printStackTrace(); } return Data; } public static String[][] Search3() { String[][] Data = null; try { String url = "jdbc:mysql://localhost:3306/test"; String username = "root"; String password = "aramiss"; Connection connection = null; System.out.println("Connecting database..."); connection = DriverManager.getConnection(url, username, password); System.out.println("Database connected!"); Statement stmt = connection.createStatement(); try { ResultSet count = stmt .executeQuery("SELECT count(*) FROM employee WHERE Emp_Name IN (SELECT Emp_Name FROM employee GROUP BY Emp_Name HAVING COUNT(Emp_Name) > 1) AND Emp_Annual_Salary > '100000';");
count.next();
int size = count.getInt(1);
Data = new String[size][9];

ResultSet execute = stmt
.executeQuery("SELECT * FROM employee WHERE Emp_Name IN (SELECT Emp_Name FROM employee GROUP BY Emp_Name HAVING COUNT(Emp_Name) > 1) AND Emp_Annual_Salary > '100000';");
for (int i = 0; execute.next(); i++) {
for (int j = 0; j < 9; j++) { Data[i][j] = execute.getString(j + 1); } } System.out.println("Search complete"); } catch (SQLException s) { System.out.println("Search failed: Employee(s) not found"); } connection.close(); } catch (Exception e) { e.printStackTrace(); } return Data; } public static String[][] Display() { String Data[][] = null; try { String url = "jdbc:mysql://localhost:3306/test"; String username = "root"; String password = "aramiss"; Connection connection = null; System.out.println("Connecting database..."); connection = DriverManager.getConnection(url, username, password); System.out.println("Database connected!"); Statement stmt = connection.createStatement(); try { ResultSet count = stmt .executeQuery("SELECT count(*) FROM employee;"); count.next(); int size = count.getInt(1); Data = new String[size][9]; ResultSet execute = stmt .executeQuery("SELECT * FROM employee ORDER BY Emp_ID;"); for (int i = 0; execute.next(); i++) { for (int j = 0; j < 9; j++) { Data[i][j] = execute.getString(j + 1); } } System.out.println("Code Executed Successfully!!"); } catch (SQLException s) { System.out.println("ERROR!!!"); } connection.close(); } catch (Exception e) { e.printStackTrace(); } return Data; } public static void UpdateId(int id, int id2) { try { String url = "jdbc:mysql://localhost:3306/test"; String username = "root"; String password = "aramiss"; Connection connection = null; System.out.println("Connecting database..."); connection = DriverManager.getConnection(url, username, password); System.out.println("Database connected!"); Statement stmt = connection.createStatement(); try { String updateid = "UPDATE employee SET Emp_ID = \'" + id2 + "\' WHERE Emp_ID = \'" + id + "\';"; stmt.executeUpdate(updateid); System.out.println("Employee updated"); } catch (SQLException s) { System.out .println("Delete failed: Employee id is incorrect/ not found"); } connection.close(); } catch (Exception e) { e.printStackTrace(); } } public static void UpdateLast(int id, String last) { try { String url = "jdbc:mysql://localhost:3306/test"; String username = "root"; String password = "aramiss"; Connection connection = null; System.out.println("Connecting database..."); connection = DriverManager.getConnection(url, username, password); System.out.println("Database connected!"); Statement stmt = connection.createStatement(); try { String updatelast = "UPDATE employee SET Emp_Name = \'" + last + "\' WHERE Emp_ID = \'" + id + "\';"; stmt.executeUpdate(updatelast); System.out.println("Employee updated"); } catch (SQLException s) { System.out .println("Delete failed: Employee id is incorrect/ not found"); } connection.close(); } catch (Exception e) { e.printStackTrace(); } } public static void UpdateHire(int id, String hire) { try { String url = "jdbc:mysql://localhost:3306/tet"; String username = "root"; String password = "aramiss"; Connection connection = null; System.out.println("Connecting database..."); connection = DriverManager.getConnection(url, username, password); System.out.println("Database connected!"); Statement stmt = connection.createStatement(); try { String updatehire = "UPDATE employee SET Emp_Hire_Date = \'" + hire + "\' WHERE Emp_ID = \'" + id + "\';"; stmt.executeUpdate(updatehire); System.out.println("Employee updated"); } catch (SQLException s) { System.out .println("Delete failed: Employee id is incorrect/ not found"); } connection.close(); } catch (Exception e) { e.printStackTrace(); } } public static void UpdateBirth(int id, String birth) { try { String url = "jdbc:mysql://localhost:3306/test"; String username = "root"; String password = "aramiss"; Connection connection = null; System.out.println("Connecting database..."); connection = DriverManager.getConnection(url, username, password); System.out.println("Database connected!"); Statement stmt = connection.createStatement(); try { String updatebirth = "UPDATE employee SET Emp_Birth_Date = \'" + birth + "\' WHERE Emp_ID = \'" + id + "\';"; stmt.executeUpdate(updatebirth); System.out.println("Employee updated"); } catch (SQLException s) { System.out .println("Delete failed: Employee id is incorrect/ not found"); } connection.close(); } catch (Exception e) { e.printStackTrace(); } } public static void UpdateSex(int id, String sex) { try { String url = "jdbc:mysql://localhost:3306/tet"; String username = "root"; String password = "aramiss"; Connection connection = null; System.out.println("Connecting database..."); connection = DriverManager.getConnection(url, username, password); System.out.println("Database connected!"); Statement stmt = connection.createStatement(); try { String updatesex = "UPDATE employee SET Emp_Sex = \'\"" + sex + "\"\' WHERE Emp_ID = \'" + id + "\';"; stmt.executeUpdate(updatesex); System.out.println("Employee updated"); } catch (SQLException s) { System.out .println("Delete failed: Employee id is incorrect/ not found"); } connection.close(); } catch (Exception e) { e.printStackTrace(); } } public static void UpdateStatus(int id, String status) { try { String url = "jdbc:mysql://localhost:3306/test"; String username = "root"; String password = "aramiss"; Connection connection = null; System.out.println("Connecting database..."); connection = DriverManager.getConnection(url, username, password); System.out.println("Database connected!"); Statement stmt = connection.createStatement(); try { String updatestatus = "UPDATE employee SET Emp_Job_Status = \'\"" + status + "\"\' WHERE Emp_ID = \'" + id + "\';"; stmt.executeUpdate(updatestatus); System.out.println("Employee updated"); } catch (SQLException s) { System.out .println("Delete failed: Employee id is incorrect/ not found"); } connection.close(); } catch (Exception e) { e.printStackTrace(); } } public static void UpdatePay(int id, String pay) { try { String url = "jdbc:mysql://localhost:3306/test"; String username = "root"; String password = "aramiss"; Connection connection = null; System.out.println("Connecting database..."); connection = DriverManager.getConnection(url, username, password); System.out.println("Database connected!"); Statement stmt = connection.createStatement(); try { String updatepay = "UPDATE employee SET Emp_Pay_Type = \'\"" + pay + "\"\' WHERE Emp_ID = \'" + id + "\';"; stmt.executeUpdate(updatepay); System.out.println("Employee updated"); } catch (SQLException s) { System.out .println("Delete failed: Employee id is incorrect/ not found"); } connection.close(); } catch (Exception e) { e.printStackTrace(); } } public static void UpdateSalary(int id, double salary) { try { String url = "jdbc:mysql://localhost:3306/test"; String username = "root"; String password = "aramiss"; Connection connection = null; System.out.println("Connecting database..."); connection = DriverManager.getConnection(url, username, password); System.out.println("Database connected!"); Statement stmt = connection.createStatement(); try { String updatesalary = "UPDATE employee SET Emp_Annual_Salary = \'" + salary + "\' WHERE Emp_ID = \'" + id + "\';"; stmt.executeUpdate(updatesalary); System.out.println("Employee updated"); } catch (SQLException s) { System.out .println("Delete failed: Employee id is incorrect/ not found"); } connection.close(); } catch (Exception e) { e.printStackTrace(); } } public static void UpdateYears(int id, int years) { try { String url = "jdbc:mysql://localhost:3306/test"; String username = "root"; String password = "aramiss"; Connection connection = null; System.out.println("Connecting database..."); connection = DriverManager.getConnection(url, username, password); System.out.println("Database connected!"); Statement stmt = connection.createStatement(); try { String updateyears = "UPDATE employee SET Emp_Years_Of_Service = \'" + years + "\' WHERE Emp_ID = \'" + id + "\';"; stmt.executeUpdate(updateyears); System.out.println("Employee updated"); } catch (SQLException s) { System.out .println("Delete failed: Employee id is incorrect/ not found"); } connection.close(); } catch (Exception e) { e.printStackTrace(); } } }

WIP: ROR Projects
This link will take you to a basic layout for an application written in
Ruby on the Rails. It was developed by a rad pair of girls in San Fransisco who created a group called RailsBridge. My applications are stored in a development environment on Heroku and then push to commit on github (a link is included in the upper right of this webpage).

Leave a Reply

  • (will not be published)