Приложение г Листинг программного модуля (обязательное)

//реализация методов просмотра

public List View() {

try {

stmt = null;

ResultSet rs = null;

list = new ArrayList();

Class.forName("com.mysql.jdbc.Driver");

Properties ConnectionProps = new Properties();

ConnectionProps.setProperty("useUnicode", "TRUE");

ConnectionProps.setProperty("characterEncoding", "cp866");

String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";

Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);

stmt = con.createStatement();

rs = stmt.executeQuery("SELECT `personal`.`FIO`, `personal`.`age`, `personal`.`sex`,"

+ " `personal`.`skill`, `speciality`.`spec`, `speciality`.`otdel`,"

+ " `speciality`.`boss`, `statement`.`state`,`personal`.`zarpl` FROM `personal` INNER JOIN `speciality`"

+ " ON speciality.`ID_spec` =personal.`ID_spec`"

+ "INNER JOIN `statement` ON `statement`.`ID_state` =personal.`ID_state`;");

// rs.next();

while (rs.next()) {

list.add(rs.getString("FIO"));

list.add(rs.getString("age"));

list.add(rs.getString("sex"));

list.add(rs.getString("skill"));

list.add(rs.getString("spec"));

list.add(rs.getString("otdel"));

list.add(rs.getString("boss"));

list.add(rs.getString("state"));

list.add(rs.getString("zarpl"));

}

//int rowsEffected = stmt.executeUpdate("DELETE FROM mvalues WHERE `IDМатериальнойЦенности` = '"+delName+"' ");

//int rowsEffected = stmt.executeUpdate("INSERT INTO mvalues VALUES "

// + "('"+Integer.parseInt(s1)+"','"+s2+"','"+s3

// +"','"+Integer.parseInt(s5)+"','"+s4+"'"//**c2-c3

// + ",'"+Integer.parseInt(s6)+"','"+Integer.parseInt(s7)+"')");

// rs = stmt.executeQuery("SELECT * FROM `mvalues` INNER JOIN `building` "

// + "ON `mvalues`.`mesto` = `building`.`IDbuilding` INNER JOIN `matotvetstvenniy`"

// + "ON `mvalues`.`otv` = `matotvetstvenniy`.`IDsotr`;");

//

} catch (SQLException e) {

System.out.println("SQL Exception: "+ e.toString());

} catch (ClassNotFoundException cE) {

System.out.println("Class Not Found Exception: "+ cE.toString());

}

return list;

}

@Override

public List View1() {

try {

stmt = null;

ResultSet rs = null;

list = new ArrayList();

Class.forName("com.mysql.jdbc.Driver");

Properties ConnectionProps = new Properties();

ConnectionProps.setProperty("useUnicode", "TRUE");

ConnectionProps.setProperty("characterEncoding", "cp866");

String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";

Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);

stmt = con.createStatement();

rs = stmt.executeQuery("SELECT `personal`.`FIO`, `personal`.`age`, `personal`.`sex`,"

+ " `personal`.`skill`, `speciality`.`spec`, `speciality`.`otdel`,"

+ " `speciality`.`boss`, `statement`.`state`,`personal`.`zarpl` FROM `personal` INNER JOIN `speciality`"

+ " ON speciality.`ID_spec` =personal.`ID_spec`"

+ "INNER JOIN `statement` ON `statement`.`ID_state` =personal.`ID_state` WHERE `statement`.`ID_state`='1';");

// rs.next();

while (rs.next()) {

list.add(rs.getString("FIO"));

list.add(rs.getString("age"));

list.add(rs.getString("sex"));

list.add(rs.getString("skill"));

list.add(rs.getString("spec"));

list.add(rs.getString("otdel"));

list.add(rs.getString("boss"));

list.add(rs.getString("state"));

list.add(rs.getString("zarpl"));

}

} catch (SQLException e) {

System.out.println("SQL Exception: "+ e.toString());

} catch (ClassNotFoundException cE) {

System.out.println("Class Not Found Exception: "+ cE.toString());

}

return list;

}

@Override

public List View2() {

try {

stmt = null;

ResultSet rs = null;

list = new ArrayList();

Class.forName("com.mysql.jdbc.Driver");

Properties ConnectionProps = new Properties();

ConnectionProps.setProperty("useUnicode", "TRUE");

ConnectionProps.setProperty("characterEncoding", "cp866");

String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";

Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);

stmt = con.createStatement();

rs = stmt.executeQuery("SELECT `personal`.`FIO`, `personal`.`age`, `personal`.`sex`,"

+ " `personal`.`skill`, `speciality`.`spec`, `speciality`.`otdel`,"

+ " `speciality`.`boss`, `statement`.`state`,`personal`.`zarpl` FROM `personal` INNER JOIN `speciality`"

+ " ON speciality.`ID_spec` =personal.`ID_spec`"

+ "INNER JOIN `statement` ON `statement`.`ID_state` =personal.`ID_state` WHERE `statement`.`ID_state`='2';");

//rs.next();

while (rs.next()) {

list.add(rs.getString("FIO"));

list.add(rs.getString("age"));

list.add(rs.getString("sex"));

list.add(rs.getString("skill"));

list.add(rs.getString("spec"));

list.add(rs.getString("otdel"));

list.add(rs.getString("boss"));

list.add(rs.getString("state"));

list.add(rs.getString("zarpl"));

}

} catch (SQLException e) {

System.out.println("SQL Exception: "+ e.toString());

} catch (ClassNotFoundException cE) {

System.out.println("Class Not Found Exception: "+ cE.toString());

}

return list;

}

@Override

public int Remove(String fio) {

try {

stmt = null;

ResultSet rs = null;

list = new ArrayList();

Class.forName("com.mysql.jdbc.Driver");

Properties ConnectionProps = new Properties();

ConnectionProps.setProperty("useUnicode", "TRUE");

ConnectionProps.setProperty("characterEncoding", "cp866");

String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";

Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);

stmt = con.createStatement();

int rowsEffected = stmt.executeUpdate("DELETE FROM personal WHERE `FIO` = '"+fio+"' ");

minus++;

if(rowsEffected!=0)return 1;

else return 0;

} catch (SQLException e) {

System.out.println("SQL Exception: "+ e.toString());

return 0;

} catch (ClassNotFoundException cE) {

System.out.println("Class Not Found Exception: "+ cE.toString());

return 0;

}

}

@Override

public List View3() {

try {

stmt = null;

ResultSet rs = null;

list = new ArrayList();

Class.forName("com.mysql.jdbc.Driver");

Properties ConnectionProps = new Properties();

ConnectionProps.setProperty("useUnicode", "TRUE");

ConnectionProps.setProperty("characterEncoding", "cp866");

String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";

Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);

stmt = con.createStatement();

rs = stmt.executeQuery("SELECT `personal`.`FIO`, `personal`.`age`, `personal`.`sex`,"

+ " `personal`.`skill`, `speciality`.`spec`, `speciality`.`otdel`,"

+ " `speciality`.`boss`, `statement`.`state`,`personal`.`zarpl` FROM `personal` INNER JOIN `speciality`"

+ " ON speciality.`ID_spec` =personal.`ID_spec`"

+ "INNER JOIN `statement` ON `statement`.`ID_state` =personal.`ID_state` WHERE `statement`.`ID_state`='3';");

//rs.next();

while (rs.next()) {

list.add(rs.getString("FIO"));

list.add(rs.getString("age"));

list.add(rs.getString("sex"));

list.add(rs.getString("skill"));

list.add(rs.getString("spec"));

list.add(rs.getString("otdel"));

list.add(rs.getString("boss"));

list.add(rs.getString("state"));

list.add(rs.getString("zarpl"));

}

} catch (SQLException e) {

System.out.println("SQL Exception: "+ e.toString());

} catch (ClassNotFoundException cE) {

System.out.println("Class Not Found Exception: "+ cE.toString());

}

return list;

}

//реализация методов Add() – добавление, Remove() – удаление, Edit() – изменение

public int Add(String id, String fio, String age, String sex, String id_spec, String skill, String id_state, String zp) {

try {

stmt = null;

ResultSet rs = null;

list = new ArrayList();

Class.forName("com.mysql.jdbc.Driver");

Properties ConnectionProps = new Properties();

ConnectionProps.setProperty("useUnicode", "TRUE");

ConnectionProps.setProperty("characterEncoding", "cp866");

String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";

Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);

stmt = con.createStatement();

int rowsEffected = stmt.executeUpdate("INSERT INTO personal VALUES "

+ "('"+Integer.parseInt(id)+"','"+fio+"','"+Integer.parseInt(age)

+"','"+sex+"','"+Integer.parseInt(id_spec)+"'"

+ ",'"+Integer.parseInt(skill)+"','"+Integer.parseInt(id_state)+"','"+Integer.parseInt(zp)+"')");

plus++;

if(rowsEffected!=0) {return 1;}

else return 0;

} catch (SQLException e) {

System.out.println("SQL Exception: "+ e.toString());

return 0;

} catch (ClassNotFoundException cE) {

System.out.println("Class Not Found Exception: "+ cE.toString());

return 0;

}

catch (NumberFormatException e8) {

System.out.println("NumberFormatException: "+ e8.toString());

return 0;

}

public int Remove(String fio) {

try {

stmt = null;

ResultSet rs = null;

list = new ArrayList();

Class.forName("com.mysql.jdbc.Driver");

Properties ConnectionProps = new Properties();

ConnectionProps.setProperty("useUnicode", "TRUE");

ConnectionProps.setProperty("characterEncoding", "cp866");

String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";

Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);

stmt = con.createStatement();

int rowsEffected = stmt.executeUpdate("DELETE FROM personal WHERE `FIO` = '"+fio+"' ");

minus++;

if(rowsEffected!=0)return 1;

else return 0;

} catch (SQLException e) {

System.out.println("SQL Exception: "+ e.toString());

return 0;

} catch (ClassNotFoundException cE) {

System.out.println("Class Not Found Exception: "+ cE.toString());

return 0;

}

}

public int Edit(String id, String name, String val) {

try {

stmt = null;

ResultSet rs = null;

list = new ArrayList();

Class.forName("com.mysql.jdbc.Driver");

Properties ConnectionProps = new Properties();

ConnectionProps.setProperty("useUnicode", "TRUE");

ConnectionProps.setProperty("characterEncoding", "cp866");

String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";

Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);

stmt = con.createStatement();

int rowsEffected = stmt.executeUpdate("UPDATE personal SET "+name+"= '"+val+"' WHERE id='"+id+"'");

if(rowsEffected!=0) {return 1;}

else return 0;

} catch (SQLException e) {

System.out.println("SQL Exception: "+ e.toString());

return 0;

} catch (ClassNotFoundException cE) {

System.out.println("Class Not Found Exception: "+ cE.toString());

return 0;

}

catch (NumberFormatException e8) {

System.out.println("NumberFormatException: "+ e8.toString());

return 0;

}

}

// Реализация статистики

public double Stat(double i) {

if(i==1){

return plus;

}

if (i==2)

{return minus;}

if (i==3)

{return (plus+1)/(minus+1);}

else {

return 11;}

}

@Override

public List BestZp(String zp) {

try {

stmt = null;

ResultSet rs = null;

list = new ArrayList();

Class.forName("com.mysql.jdbc.Driver");

Properties ConnectionProps = new Properties();

ConnectionProps.setProperty("useUnicode", "TRUE");

ConnectionProps.setProperty("characterEncoding", "cp866");

String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";

Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);

stmt = con.createStatement();

rs = stmt.executeQuery("SELECT `personal`.`FIO`, `personal`.`age`, `personal`.`sex`,"

+ " `personal`.`skill`, `speciality`.`spec`, `speciality`.`otdel`,"

+ " `speciality`.`boss`, `statement`.`state`,`personal`.`zarpl` FROM `personal` INNER JOIN `speciality`"

+ " ON speciality.`ID_spec` =personal.`ID_spec`"

+ "INNER JOIN `statement` ON `statement`.`ID_state` =personal.`ID_state` "

+ "WHERE `personal`.`zarpl`>='"+zp+"';");

// rs.next();

while (rs.next()) {

list.add(rs.getString("FIO"));

list.add(rs.getString("age"));

list.add(rs.getString("sex"));

list.add(rs.getString("skill"));

list.add(rs.getString("spec"));

list.add(rs.getString("otdel"));

list.add(rs.getString("boss"));

list.add(rs.getString("state"));

list.add(rs.getString("zarpl"));

}

} catch (SQLException e) {

System.out.println("SQL Exception: "+ e.toString());

} catch (ClassNotFoundException cE) {

System.out.println("Class Not Found Exception: "+ cE.toString());

}

return list;

}

@Override

public List Skill(String skil) {

try {

stmt = null;

ResultSet rs = null;

list = new ArrayList();

Class.forName("com.mysql.jdbc.Driver");

Properties ConnectionProps = new Properties();

ConnectionProps.setProperty("useUnicode", "TRUE");

ConnectionProps.setProperty("characterEncoding", "cp866");

String connectionUrl = "jdbc:mysql://localhost/workers?"+"user=root&password=root";

Connection con = DriverManager.getConnection(connectionUrl,ConnectionProps);

stmt = con.createStatement();

rs = stmt.executeQuery("SELECT `personal`.`FIO`, `personal`.`age`, `personal`.`sex`,"

+ " `personal`.`skill`, `speciality`.`spec`, `speciality`.`otdel`,"

+ " `speciality`.`boss`, `statement`.`state`,`personal`.`zarpl` FROM `personal` INNER JOIN `speciality`"

+ " ON speciality.`ID_spec` =personal.`ID_spec`"

+ "INNER JOIN `statement` ON `statement`.`ID_state` =personal.`ID_state` "

+ "WHERE `personal`.`skill`>'"+skil+"';");

// rs.next();

while (rs.next()) {

list.add(rs.getString("FIO"));

list.add(rs.getString("age"));

list.add(rs.getString("sex"));

list.add(rs.getString("skill"));

list.add(rs.getString("spec"));

list.add(rs.getString("otdel"));

list.add(rs.getString("boss"));

list.add(rs.getString("state"));

list.add(rs.getString("zarpl"));

}

} catch (SQLException e) {

System.out.println("SQL Exception: "+ e.toString());

} catch (ClassNotFoundException cE) {

System.out.println("Class Not Found Exception: "+ cE.toString());

}

return list;

}

}

// файлы оформления CSS

Design.css

body {

font-style: italic;

background-image: url("hd2.jpg")

}

h1{

color:whitesmoke;

text-decoration: blink;

}

Tables.css

body {

font-style: italic;

background-image: url("koala.jpg")

}

h1{

color:whitesmoke;

text-decoration: blink;

}

a:hover {

text-decoration: blink;

color:red;

}

table {

border: 4px double #333;

border-collapse: separate;

width: 100%;

border-spacing: 7px 11px;

}

td {

background: #fc0;

padding: 5px;

border: 1px solid #a52a2a;

}

Stat.css

body {

font-style: italic;

background-image: url("money.jpg")

}

h1{

color:gray;

text-decoration: blink;

}

a:hover {

text-decoration: blink;

color:red;

}

table {

border: 4px double #333;

border-collapse: separate;

width: 100%;

border-spacing: 7px 11px;

}

td {

background: #fc0;

padding: 5px;

border: 1px solid #a52a2a;

}

48