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).
- LaTex Tutorial
- Creating Applications to undo location based marketing with John Hurley
- Portfolio Website for Juliana Parr
Past projects