用java和sql写一个海洋景点导游系统

好的，下面是一个简单的海洋景点导游系统的Java和SQL代码：

1. 首先，我们需要创建一个数据库并创建两个表格，一个是景点表，一个是用户表。这里我们使用MySQL数据库，创建两个表格的SQL语句如下：

CREATE TABLE `spots` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `location` varchar(50) NOT NULL,
  `price` double NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
);

2. 接下来，我们需要使用Java连接到MySQL数据库，并编写Java代码对数据库进行操作。首先，我们需要创建一个类来表示景点：

public class Spot {
    private int id;
    private String name;
    private String description;
    private String location;
    private double price;
    
    // 构造函数
    public Spot(int id, String name, String description, String location, double price) {
        this.id = id;
        this.name = name;
        this.description = description;
        this.location = location;
        this.price = price;
    }
    
    // Getter和Setter方法
    public int getId() {
        return id;
    }
    
    public void setId(int id) {
        this.id = id;
    }
    
    public String getName() {
        return name;
    }
    
    public void setName(String name) {
        this.name = name;
    }
    
    public String getDescription() {
        return description;
    }
    
    public void setDescription(String description) {
        this.description = description;
    }
    
    public String getLocation() {
        return location;
    }
    
    public void setLocation(String location) {
        this.location = location;
    }
    
    public double getPrice() {
        return price;
    }
    
    public void setPrice(double price) {
        this.price = price;
    }
}

3. 接下来，我们需要创建一个类来表示用户：

public class User {
    private int id;
    private String name;
    private String email;
    private String password;
    
    // 构造函数
    public User(int id, String name, String email, String password) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.password = password;
    }
    
    // Getter和Setter方法
    public int getId() {
        return id;
    }
    
    public void setId(int id) {
        this.id = id;
    }
    
    public String getName() {
        return name;
    }
    
    public void setName(String name) {
        this.name = name;
    }
    
    public String getEmail() {
        return email;
    }
    
    public void setEmail(String email) {
        this.email = email;
    }
    
    public String getPassword() {
        return password;
    }
    
    public void setPassword(String password) {
        this.password = password;
    }
}

4. 然后，我们需要创建一个类来管理数据库连接和操作：

import java.sql.*;

public class DatabaseManager {
    private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/ocean_tourism";
    private static final String DATABASE_USER = "root";
    private static final String DATABASE_PASSWORD = "password";
    
    // 获取数据库连接
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DATABASE_URL, DATABASE_USER, DATABASE_PASSWORD);
    }
    
    // 查询所有景点
    public Spot[] getAllSpots() throws SQLException {
        Connection connection = getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT * FROM spots");
        Spot[] spots = new Spot[resultSet.getMetaData().getColumnCount()];
        int i = 0;
        while (resultSet.next()) {
            spots[i] = new Spot(
                resultSet.getInt("id"),
                resultSet.getString("name"),
                resultSet.getString("description"),
                resultSet.getString("location"),
                resultSet.getDouble("price")
            );
            i++;
        }
        connection.close();
        return spots;
    }
    
    // 根据ID查询景点
    public Spot getSpotById(int id) throws SQLException {
        Connection connection = getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT * FROM spots WHERE id=?");
        statement.setInt(1, id);
        ResultSet resultSet = statement.executeQuery();
        Spot spot = null;
        if (resultSet.next()) {
            spot = new Spot(
                resultSet.getInt("id"),
                resultSet.getString("name"),
                resultSet.getString("description"),
                resultSet.getString("location"),
                resultSet.getDouble("price")
            );
        }
        connection.close();
        return spot;
    }
    
    // 查询所有用户
    public User[] getAllUsers() throws SQLException {
        Connection connection = getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT * FROM users");
        User[] users = new User[resultSet.getMetaData().getColumnCount()];
        int i = 0;
        while (resultSet.next()) {
            users[i] = new User(
                resultSet.getInt("id"),
                resultSet.getString("name"),
                resultSet.getString("email"),
                resultSet.getString("password")
            );
            i++;
        }
        connection.close();
        return users;
    }
    
    // 根据ID查询用户
    public User getUserById(int id) throws SQLException {
        Connection connection = getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT * FROM users WHERE id=?");
        statement.setInt(1, id);
        ResultSet resultSet = statement.executeQuery();
        User user = null;
        if (resultSet.next()) {
            user = new User(
                resultSet.getInt("id"),
                resultSet.getString("name"),
                resultSet.getString("email"),
                resultSet.getString("password")
            );
        }
        connection.close();
        return user;
    }
    
    // 根据Email查询用户
    public User getUserByEmail(String email) throws SQLException {
        Connection connection = getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT * FROM users WHERE email=?");
        statement.setString(1, email);
        ResultSet resultSet = statement.executeQuery();
        User user = null;
        if (resultSet.next()) {
            user = new User(
                resultSet.getInt("id"),
                resultSet.getString("name"),
                resultSet.getString("email"),
                resultSet.getString("password")
            );
        }
        connection.close();
        return user;
    }
    
    // 添加用户
    public void addUser(User user) throws SQLException {
        Connection connection = getConnection();
        PreparedStatement statement = connection.prepareStatement("INSERT INTO users (name, email, password) VALUES (?, ?, ?)");
        statement.setString(1, user.getName());
        statement.setString(2, user.getEmail());
        statement.setString(3, user.getPassword());
        statement.executeUpdate();
        connection.close();
    }
    
    // 更新用户
    public void updateUser(User user) throws SQLException {
        Connection connection = getConnection();
        PreparedStatement statement = connection.prepareStatement("UPDATE users SET name=?, email=?, password=? WHERE id=?");
        statement.setString(1, user.getName());
        statement.setString(2, user.getEmail());
        statement.setString(3, user.getPassword());
        statement.setInt(4, user.getId());
        statement.executeUpdate();
        connection.close();
    }
    
    // 删除用户
    public void deleteUser(int id) throws SQLException {
        Connection connection = getConnection();
        PreparedStatement statement = connection.prepareStatement("DELETE FROM users WHERE id=?");
        statement.setInt(1, id);
        statement.executeUpdate();
        connection.close();
    }
}

5. 最后，我们可以在主函数中使用这些类来实现海洋景点导游系统：

import java.sql.SQLException;
import java.util.Scanner;

public class OceanTourismGuide {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        DatabaseManager databaseManager = new DatabaseManager();
        while (true) {
            System.out.println("请选择操作：");
            System.out.println("1. 查询所有景点");
            System.out.println("2. 查询景点详情");
            System.out.println("3. 登录");
            System.out.println("4. 注册");
            System.out.println("0. 退出");
            int choice = scanner.nextInt();
            switch (choice) {
                case 1:
                    try {
                        Spot[] spots = databaseManager.getAllSpots();
                        System.out.println("所有景点如下：");
                        for (Spot spot : spots) {
                            System.out.println(spot.getId() + ". " + spot.getName() + " - " + spot.getLocation() + " - " + spot.getPrice() + "元");
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    break;
                case 2:
                    System.out.println("请输入景点ID：");
                    int spotId = scanner.nextInt();
                    try {
                        Spot spot = databaseManager.getSpotById(spotId);
                        if (spot != null) {
                            System.out.println(spot.getName() + " - " + spot.getDescription() + " - " + spot.getLocation() + " - " + spot.getPrice() + "元");
                        } else {
                            System.out.println("未找到对应景点");
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    break;
                case 3:
                    System.out.println("请输入Email：");
                    String email = scanner.next();
                    System.out.println("请输入密码：");
                    String password = scanner.next();
                    try {
                        User user = databaseManager.getUserByEmail(email);
                        if (user != null && user.getPassword().equals(password)) {
                            System.out.println("登录成功，欢迎您，" + user.getName() + "！");
                        } else {
                            System.out.println("登录失败，用户名或密码错误！");
                        }
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    break;
                case 4:
                    System.out.println("请输入姓名：");
                    String name = scanner.next();
                    System.out.println("请输入Email：");
                    email = scanner.next();
                    System.out.println("请输入密码：");
                    password = scanner.next();
                    User user = new User(0, name, email, password);
                    try {
                        databaseManager.addUser(user);
                        System.out.println("注册成功！");
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    break;
                case 0:
                    System.exit(0);
                    break;
                default:
                    System.out.println("无效的操作！");
                    break;
            }
        }
    }
}

以上是一个简单的海洋景点导游系统的Java和SQL代码，仅供参考。