03_java基础（九）之综合练习与考核评估

25.综合练习之车站业务分析

　　

      完成步骤:

　　需求: 以车站业务对车票做增删改查操作

　　1.建立数据库

　　2.建立车票表

　　3.建立java项目结构(modeldaoservice est)

　　4.创建model

　　5.创建dao(接口和实现)并实现测试

　　6.创建service(接口实现)

26.综合练习之数据库与表建立

　　

27.项目标准结构创建

　　

28293031.综合练习之代码实现

　　1.model

package com.day03.station.model;

/**
 * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
 * 疑问咨询wx:851298348
 */
public class Ticket {
    private Integer id;
    private String startStation;
    private String stopStation;
    private String startTime;
    private Integer ticketPrice;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getStartStation() {
        return startStation;
    }

    public void setStartStation(String startStation) {
        this.startStation = startStation;
    }

    public String getStopStation() {
        return stopStation;
    }

    public void setStopStation(String stopStation) {
        this.stopStation = stopStation;
    }

    public String getStartTime() {
        return startTime;
    }

    public void setStartTime(String startTime) {
        this.startTime = startTime;
    }

    public Integer getTicketPrice() {
        return ticketPrice;
    }

    public void setTicketPrice(Integer ticketPrice) {
        this.ticketPrice = ticketPrice;
    }
}

　　2.dao接口

package com.day03.station.dao;

import com.day03.station.model.Ticket;

import java.util.List;

/**
 * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
 * 疑问咨询wx:851298348
 */
public interface ITicketDao {
    //增加
    public void save(Ticket ticket);
    //删除  根据id删除
    public void delete(Integer id);

    //修改
    public void update(Ticket ticket);
    //查找   查单个  根据id查询
    public Ticket queryById(Integer id);
    //查找多个  （集合装）List
    public List<Ticket> queryAll();
}

　　3.dao实现

package com.day03.station.dao.impl;

import com.day03.station.dao.ITicketDao;
import com.day03.station.model.Ticket;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
 * 疑问咨询wx:851298348
 */
public class TicketDao implements ITicketDao {
    @Override
    public void save(Ticket ticket) {

        try {

            //1.加载
            Class.forName("com.mysql.jdbc.Driver");
            //2.链接
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/station_data", "root", "admin");
            //3.创建编译语句
            String sql="INSERT INTO ticket (start_station,stop_station,start_time,ticket_price) VALUES (?,?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
                //设置参数
            preparedStatement.setString(1,ticket.getStartStation());
            preparedStatement.setString(2,ticket.getStopStation());
            preparedStatement.setString(3,ticket.getStartTime());
            preparedStatement.setInt(4,ticket.getTicketPrice());
            //4.执行
            preparedStatement.executeUpdate();
            //5.释放资源
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Override
    public void delete(Integer id) {
        try {

            //1.加载
            Class.forName("com.mysql.jdbc.Driver");
            //2.链接
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/station_data", "root", "admin");
            //3.创建编译语句
            String sql="DELETE FROM ticket WHERE id=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //设置参数
            preparedStatement.setInt(1,id);
            //4.执行
            preparedStatement.executeUpdate();
            //5.释放资源
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public void update(Ticket ticket) {
        try {

            //1.加载
            Class.forName("com.mysql.jdbc.Driver");
            //2.链接
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/station_data", "root", "admin");
            //3.创建编译语句
            String sql="UPDATE ticket SET ticket_price=? WHERE id=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //设置参数
            preparedStatement.setInt(1,ticket.getTicketPrice());
            preparedStatement.setInt(2,ticket.getId());

            //4.执行
            preparedStatement.executeUpdate();
            //5.释放资源
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Ticket queryById(Integer id) {
        //准备一个对象,装获取到的结果数据
        Ticket ticket = new Ticket();
        try {

            //1.加载
            Class.forName("com.mysql.jdbc.Driver");
            //2.链接
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/station_data", "root", "admin");
            //3.创建编译语句
            String sql="SELECT id,start_station,stop_station,start_time,ticket_price FROM ticket  WHERE id=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //设置参数
            preparedStatement.setInt(1,id);
            //4.执行
            ResultSet resultSet = preparedStatement.executeQuery();
             //解析结果集
            while (resultSet.next()){
                //取id
                int id1 = resultSet.getInt("id");
                //取开始车站
                String startStation = resultSet.getString("start_station");
                //取到达车站
                String stopStation = resultSet.getString("stop_station");
                //取发车时间
                String startTime = resultSet.getString("start_time");
                //取车票价格
                int ticketPrice = resultSet.getInt("ticket_price");
                  //封装到对象里面去
                ticket.setId(id1);
                ticket.setStartStation(startStation);
                ticket.setStopStation(stopStation);
                ticket.setStartTime(startTime);
                ticket.setTicketPrice(ticketPrice);

            }

            //5.释放资源
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return ticket;
    }

    @Override
    public List<Ticket> queryAll() {
        //准备一个对象,装获取到的结果数据
        //Ticket ticket = new Ticket();
        //准备一个可以装多个对象的对象来装多个车票对象==>集合对象List
        List<Ticket> list=new ArrayList<>();
        //list.add(); 添加
        //list.get(); 取
        try {

            //1.加载
            Class.forName("com.mysql.jdbc.Driver");
            //2.链接
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/station_data", "root", "admin");
            //3.创建编译语句
            String sql="SELECT id,start_station,stop_station,start_time,ticket_price FROM ticket";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            //设置参数

            //4.执行
            ResultSet resultSet = preparedStatement.executeQuery();
            //解析结果集
            while (resultSet.next()){
                //取id
                int id1 = resultSet.getInt("id");
                //取开始车站
                String startStation = resultSet.getString("start_station");
                //取到达车站
                String stopStation = resultSet.getString("stop_station");
                //取发车时间
                String startTime = resultSet.getString("start_time");
                //取车票价格
                int ticketPrice = resultSet.getInt("ticket_price");
                //封装到对象里面去
                Ticket ticket = new Ticket();
                ticket.setId(id1);
                ticket.setStartStation(startStation);
                ticket.setStopStation(stopStation);
                ticket.setStartTime(startTime);
                ticket.setTicketPrice(ticketPrice);

                //将车票对象装入集合对象里面
                list.add(ticket);

            }

            //5.释放资源
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}

　　4.dao测试

package com.day03.station.testDao;

import com.day03.station.dao.impl.TicketDao;
import com.day03.station.model.Ticket;
import org.junit.Test;

import java.util.List;

/**
 * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
 * 疑问咨询wx:851298348
 */
public class TestTicketDao {
    TicketDao ticketDao = new TicketDao();
    //测试车票保存
    @Test
    public void testSave(){
        //1.创建dao对象
        //TicketDao ticketDao = new TicketDao();
        //2.调用dao方法
        Ticket ticket = new Ticket();
            //赋值
        ticket.setStartStation("南充");
        ticket.setStopStation("成都");
        ticket.setStartTime("2018-05-20 13:14:00");
        ticket.setTicketPrice(99);
        ticketDao.save(ticket);
    }
    @Test
    public void testDelete(){
        ticketDao.delete(5);
    }
    @Test
    public void testUpdate(){
        Ticket ticket = new Ticket();
           ticket.setTicketPrice(120);
            ticket.setId(2);
        ticketDao.update(ticket);
    }
    @Test
    public void testQueryById(){
        Ticket ticket = ticketDao.queryById(2);
        System.out.println("ticket="+ticket);
    }
    @Test
    public void testQuery(){
        List<Ticket> tickets = ticketDao.queryAll();
        System.out.println("tickets="+tickets);

    }

}

　　5.service接口

package com.day03.station.service;

import com.day03.station.model.Ticket;

import java.util.List;

/**
 * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
 * 疑问咨询wx:851298348
 */
public interface ITicketService {
    //增加
    public void save(Ticket ticket);
    //删除  根据id删除
    public void delete(Integer id);

    //修改
    public void update(Ticket ticket);
    //查找   查单个  根据id查询
    public Ticket queryById(Integer id);
    //查找多个  （集合装）List
    public List<Ticket> queryAll();
}

　　6.service实现

package com.day03.station.service.impl;

import com.day03.station.dao.impl.TicketDao;
import com.day03.station.model.Ticket;
import com.day03.station.service.ITicketService;

import java.util.List;

/**
 * 课程笔记:http://www.cnblogs.com/newAndHui/category/1153640.html
 * 疑问咨询wx:851298348
 */
public class TicketService implements ITicketService {
    TicketDao ticketDao = new TicketDao();

    @Override
    public void save(Ticket ticket) {
        //1.创建dao对象
        //TicketDao ticketDao = new TicketDao();
        //2.调用dao方法
        ticketDao.save(ticket);
    }

    @Override
    public void delete(Integer id) {
        ticketDao.delete(id);
    }

    @Override
    public void update(Ticket ticket) {
     ticketDao.update(ticket);
    }

    @Override
    public Ticket queryById(Integer id) {
        Ticket ticket = ticketDao.queryById(id);

        return ticket;
    }

    @Override
    public List<Ticket> queryAll() {
        List<Ticket> tickets = ticketDao.queryAll();

        return tickets;
    }
}

 　　7.项目完成后的结构