java - 我正在我的 java 类中处理数据库,我需要帮助弄清楚如何更新数据库中的特定值/列
问题描述
这是我的第一堂课。这是我建立我的表并插入一些值的地方。第二课我只显示firstName
, lastName
, 和salary
但是我还需要再显示一次薪水,但是这次增加了10%,或者salary += salary*.1
说清楚
public class CreateEmployeeDB
{
public static void main(String[] args)
{
final String DB_URL = "jdbc:derby:EmployeeDB;create=true";
try
{
Connection conn =
DriverManager.getConnection(DB_URL);
dropTables(conn);
buildEmployees(conn);
conn.close();
}
catch (Exception ex)
{
System.out.println("ERROR: " + ex.getMessage());
}
}
public static void dropTables(Connection conn)
{
try
{
// Get a Statement object.
Statement stmt = conn.createStatement();
try
{
stmt.execute("DROP TABLE Employees");
}
catch(SQLException ex)
{
// No need to report an error.
// The table simply did not exist.
}
}
catch(SQLException ex)
{
System.out.println("ERROR: " + ex.getMessage());
ex.printStackTrace();
}
}
public static void buildEmployees(Connection conn)
{
try
{
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE Employees (" +
"socialSecurityNumber VARCHAR(30) NOT NULL PRIMARY KEY, " +
"firstName VARCHAR(30), " +
"lastName VARCHAR(30), " +
"weeklySalary DOUBLE, " +
"birthday DATE, " +
"employeeType VARCHAR(30), " +
"departmentName VARCHAR(30) " +
")");
stmt.execute("INSERT INTO EMPLOYEES VALUES( " +
"'11-1111', " +
"'John', " +
"'Smith', " +
"1000.50, " +
"'1945-01-02', " +
"'salariedEmployee', " +
"'R&D')");
stmt.execute("INSERT INTO EMPLOYEES VALUES( " +
"'22-2222', " +
"'Sue', " +
"'Jones', " +
"865.00, " +
"'1961-02-03', " +
"'commissionEmployee', " +
"'SALES')");
stmt.execute("INSERT INTO EMPLOYEES VALUES( " +
"'33-3333', " +
"'Bob', " +
"'Lowis', " +
"950.25, " +
"'1958-10-05', " +
"'basePlusEmployee', " +
"'SALES')");
stmt.execute("INSERT INTO EMPLOYEES VALUES( " +
"'44-4444', " +
"'Karen', " +
"'Price', " +
"1100.15, " +
"'1972-05-25', " +
"'salariedEmployee', " +
"'HR')");
}
catch (SQLException ex)
{
System.out.println("ERROR: " + ex.getMessage());
}
}
}
这是我的第二节课。我如何在这里更新工资?我假设我需要做点什么SELECT
?但我无法完全弄清楚。
public class UpdateSalary
{
public static void main(String[] args)
{
final String DB_URL = "jdbc:derby:EmployeeDB";
try
{
Connection conn = DriverManager.getConnection(DB_URL);
showSalary(conn);
updateSalary(conn);
}
catch(Exception ex)
{
System.out.println("ERROR: " + ex.getMessage());
}
}
public static void showSalary(Connection conn)
{
final String DB_URL = "jdbc:derby:Personnel";
try
{
Statement stmt = conn.createStatement();
String sqlStatement = "SELECT * FROM Employees";
ResultSet result = stmt.executeQuery(sqlStatement);
System.out.println("\n\t\t\t\t Employees Report - Current Salary ");
System.out.println("-------------------------------");
System.out.println("\t\t\t First \t\t\tLast Salary");
while (result.next())
{
System.out.printf("%30s %30s $%5.2f\n",
result.getString("firstName"),
result.getString("lastName"),
result.getDouble("weeklySalary")
);
}
//conn.close();
}
catch(Exception ex)
{
System.out.println("ERROR: " + ex.getMessage());
}
}
public static void updateSalary(Connection conn)
{
final String DB_URL = "jdbc:derby:Personnel";
try
{
Statement stmt = conn.createStatement();
String sqlStatement = "SELECT weeklySalary FROM Employees ";
ResultSet result = stmt.executeQuery(sqlStatement);
System.out.println("\n\t\t\t\t Employees Report - Updated Salary ");
System.out.println("----------------------------------");
System.out.println("\t\t\t First \t\t\tLast Salary");
while (result.next())
{
System.out.printf("%30s %30s $%5.2f\n",
result.getString("firstName"),
result.getString("lastName"),
result.getDouble("weeklySalary")
);
}
conn.close();
}
catch(Exception ex)
{
System.out.println("ERROR: " + ex.getMessage());
}
}
}
对于您在输出中得到的混乱代码和奇怪的格式,我深表歉意。如果有人有提示,我将不胜感激。感谢您的时间。
解决方案
您可以SELECT
通过进行相关更改并直接显示结果来实现。虽然这不会影响数据库的每周工资值。
SELECT firstName,lastName,weeklySalary*1.1 as weeklySalary FROM Employees
您可以使用更新后的工资值创建 SQL 语句UPDATE
,然后使用executeUpdate()
. 这将更新数据库的每周工资值。然后,简单地显示结果。
根据updateSalary(Connection conn)
更换。
String updateSqlStatement = "UPDATE Employees SET weeklySalary = weeklySalary*1.1";
stmt.executeUpdate(updateSqlStatement);
String sqlStatement = "SELECT * FROM Employees";
ResultSet result = stmt.executeQuery(sqlStatement);
推荐阅读
- php - 如何使用带有多个 OR 值的嵌套 if 语句的 switch 语句
- java - 将 pdfReader 转换为 byte[] - Itext Java
- ubuntu-18.04 - live migrate KVM VM 在 Ubuntu 中因 TCP 连接而失败
- android - FusedLocationProviderClient 不返回位置
- javascript - 点击后不显示粗体
- git - 如何查看我之前在哪个分支
- java - 如何在 JAVA 中将带有逗号分隔值的字符串作为函数参数传递
- php - php 类型转换和数组引用
- javascript - 为什么我无法从输入框中获取输入?
- python-3.x - 更改 Pandas 中的栏项目名称