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

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

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

完成步骤:

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

　　1.建立数据库

　　2.建立车票表

　　3.建立java项目结构(model\dao\service\test)

　　4.创建model

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

　　6.创建service(接口实现)

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

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

27.项目标准结构创建

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

28\29\30\31.综合练习之代码实现

　　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;

     }

 }

Ticket

　　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();

 }

ITicketDao

　　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;

     }

 }

TicketDao

　　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);

     }

 }

TestTicketDao

　　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();

 }

ITicketService

　　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;

     }

 }

TicketService

　　7.项目完成后的结构

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