好的,我可以帮您解答这个问题。以下是一个基本的水电费管理系统的Java和SQL代码示例:
Java代码:
```
import java.sql.*;
public class WaterElectricitySystem {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/WaterElectricitySystem";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("连接数据库...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("实例化Statement对象...");
stmt = conn.createStatement();
String sql;
sql = "CREATE TABLE IF NOT EXISTS WaterElectricityRecord (\n" +
" RecordID INT(11) NOT NULL AUTO_INCREMENT,\n" +
" ResidentID INT(11) NOT NULL,\n" +
" RecordDate DATE NOT NULL,\n" +
" WaterAmount INT(11) NOT NULL,\n" +
" ElectricityAmount INT(11) NOT NULL,\n" +
" PRIMARY KEY (RecordID)\n" +
");";
stmt.executeUpdate(sql);
System.out.println("插入数据...");
sql = "INSERT INTO WaterElectricityRecord (ResidentID, RecordDate, WaterAmount, ElectricityAmount)\n" +
"VALUES (1, '2022-01-01', 20, 30);";
int rowsAffected = stmt.executeUpdate(sql);
System.out.println(String.format("已插入%s条记录", rowsAffected));
System.out.println("查询数据...");
sql = "SELECT RecordDate, WaterAmount, ElectricityAmount FROM WaterElectricityRecord WHERE ResidentID = 1";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
String recordDate = rs.getString("RecordDate");
int waterAmount = rs.getInt("WaterAmount");
int electricityAmount = rs.getInt("ElectricityAmount");
System.out.println(String.format("日期:%s,水费:%d元,电费:%d元", recordDate, waterAmount, electricityAmount));
}
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
se.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(stmt!=null) stmt.close();
}catch(SQLException se2){
}
try{
if(conn!=null) conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
}
}
```
SQL代码:
```
CREATE DATABASE WaterElectricitySystem;
USE WaterElectricitySystem;
CREATE TABLE IF NOT EXISTS Residents (
ResidentID INT(11) NOT NULL AUTO_INCREMENT,
Name VARCHAR(50) NOT NULL,
PRIMARY KEY (ResidentID)
);
CREATE TABLE IF NOT EXISTS WaterElectricityRecord (
RecordID INT(11) NOT NULL AUTO_INCREMENT,
ResidentID INT(11) NOT NULL,
RecordDate DATE NOT NULL,
WaterAmount INT(11) NOT NULL,
ElectricityAmount INT(11) NOT NULL,
PRIMARY KEY (RecordID),
FOREIGN KEY (ResidentID) REFERENCES Residents(ResidentID)
);
```
这个系统会跟踪每个住户的水电表读数,并将其存储在数据库中。如果您需要添加更多功能,比如账单计算,可以根据您的需求进行补充。