JDBC案例

JDBC步骤

java.sql 包 专门用来操作数据库的一个包
DriverManger()驱动程序管理器
Connection数据库链接
Class.forname(“com.mysql.jdbc.Drive”);//为了获取与数据库的连接
Preparedstatment对象所代表的sql语句中的参数用?表示,调用Preparedstatment对象的setxxx()方法来设置这些参数
链接Connection主要用于执行对象的获取从而进一步执行Sql，在连接上下文中执行Sql语句并返回结果
1.加载驱动class.forname(“com.mysql.jdbc.Drive”);
2.创建链接Connection
3.SQL语句
4.Preparedstatment(预处理)
5.如果有?传参
6.执行 增删改(insert into/delete/update)executeUpdate 查(select)executeQuery

{
String sql="select * from cer_table";
Connection connection=getCon();
Preparedstatment statment=connection.Preparedstatment(sql);//Preparedstatment是Statment的子类,Preparment是为了提高效率
//实例化之后将Sql语句准备好
}

方法构造

在CarTable生成setget方法和tostring方法 再生成有参构造以及一个无参构造方法方便调用
在定义车牌号还有停车时间取车时间以及状态等需要用驼峰法来命名
set方法不需要返回值需要传参数并用int调用本类对应的方法
get方法不需要传参数需要带返回值

package Car;
public class CarTable {
int carId;//停车的数量 mysql中的语句在这里需要使用驼峰法命名
String carNumber;//车牌号
String carStart;//停车时间
String carEnd;//取车时间
String carState;//车的状态是正在停车还是已经取走
int carMoney;//停车结束后付款
public int getCarId() {
return carId;
}
public void setCarId(int carId) {
this.carId = carId;
}
public String getCarNumber() {
return carNumber;
}
public void setCarNumber(String carNumber) {
this.carNumber = carNumber;
}
public String getCarStart() {
return carStart;
}
public void setCarStart(String carStart) {
this.carStart = carStart;
}
public String getCarEnd() {
return carEnd;
}
public void setCarEnd(String carEnd) {
this.carEnd = carEnd;
}
public String getCarState() {
return carState;
}
public void setCarState(String carState) {
this.carState = carState;
}
public int getCarMoney() {
return carMoney;
}
public void setCarMoney(int carMoney) {
this.carMoney = carMoney;
}
@Override
public String toString() {
return "CarTable [carId=" + carId + ", carNumber=" + carNumber + ", carStart=" + carStart + ", carEnd=" + carEnd
+ ", carState=" + carState + ", carMoney=" + carMoney + "]";
}
public CarTable(String carNumber, String carStart, String carState) {
super();
this.carNumber = carNumber;
this.carStart = carStart;
this.carState = carState;
}
//方便调用生成空参构造
public CarTable() {
super();
}
}

JDBC

Connection立即释放Collection对象数据库和JDBC资源，而不是等待他们被自动释放
Preparedstatment继承的是Statment
Preparedstatment是预编译的,可以大大提升效率 Preparedstatment实例包含已编译的SQL语句,就是使语句"准备好"
executeUpdate用于执行insert update delete语句以及SQL DDL（数据定义语言）
executeQuery用于产生多个结果集的语句,例如select
int executeUpdate(insert/update/delete)
Resultset executeQuery(select)

package Car;
import java.nio.channels.NonWritableChannelException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Scanner;
import javax.naming.spi.DirStateFactory.Result;
public class CarJDBC {
public static Connection getCon() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
 //Connection立即释放Connection对象数据库和JDBC的资源，而不是等待他们被自动释放
    Connection connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bbbggg?characterEncoding=UTF-8", "root", "root123");
    return connection;
}
//插入停车场
public static void insert(CarTable carTable) throws Exception {
    String sql="insert into car_table(car_number,car_start,car_state) values(?,?,?)";
    Connection connection=getCon();
    PreparedStatement statement=connection.prepareStatement(sql);
    statement.setString(1, carTable.getCarNumber());
    statement.setString(2, carTable.getCarStart());
    statement.setString(3, carTable.getCarState());
    
    statement.executeUpdate();
    connection.close();
}
//插入方法
public static CarTable hasCar(String number) throws Exception  {
    String sql="select * from car_table where car_number=? and car_state=?";
    Connection connection=getCon();
    PreparedStatement statement=connection.prepareStatement(sql);
    statement.setString(1, number);
    statement.setString(2, "正在停车");    
    ResultSet set=statement.executeQuery();
    CarTable carTable=null;
    while(set.next()) {
        carTable=new CarTable();
        carTable.setCarId(set.getInt("car_id"));
        carTable.setCarNumber(set.getString("car_number"));
        carTable.setCarStart(set.getString("car_start"));
        
    }
    return carTable;
    
    //connection.close();
}
public static void pay(CarTable carTable) throws Exception {
    String sql="update car_table set car_end=?,car_state=?,car_money=? where car_id=?";
    Connection connection=getCon();
    PreparedStatement statement=connection.prepareStatement(sql);
    statement.setString(1, carTable.getCarEnd());
    statement.setString(2, carTable.getCarState());
    statement.setInt(3, carTable.getCarMoney());
    statement.setInt(4, carTable.getCarId());
    
    statement.executeUpdate();
    
}
public static List<CarTable> findAll() throws Exception {
    String sql="select * from car_table";
    Connection connection=getCon();
    PreparedStatement statement=connection.prepareStatement(sql);
    ResultSet set=statement.executeQuery();
    List<CarTable> list=new ArrayList<CarTable>();
    while(set.next()) {
        CarTable carTable=new CarTable();
        carTable.setCarNumber(set.getString("car_number"));
        carTable.setCarStart(set.getString("car_start"));
        carTable.setCarState(set.getString("car_state"));
        carTable.setCarMoney(set.getInt("car_money"));
        carTable.setCarEnd(set.getString("car_end"));
        carTable.setCarId(set.getInt("car_id"));    
        list.add(carTable);
    }
    return list;
}
public static List<CarTable> moneydemo() throws Exception {
    String sql="select * from car_table where car_money=0";
    Connection connection=getCon();
    PreparedStatement statement=connection.prepareStatement(sql);
    ResultSet set=statement.executeQuery();
    List<CarTable> list=new ArrayList<CarTable>();
    while(set.next()) {
        CarTable carTable=new CarTable();
        carTable.setCarNumber(set.getString("car_number"));
        carTable.setCarStart(set.getString("car_start"));
        carTable.setCarState(set.getString("car_state"));
        carTable.setCarMoney(set.getInt("car_money"));
        carTable.setCarEnd(set.getString("car_end"));
        carTable.setCarId(set.getInt("car_id"));    
        list.add(carTable);
    }
    return list;
}
public static List<CarTable> chepaihao(String num) throws Exception {
    String sql="select * from car_table where car_number=?";
    Connection connection=getCon();
    PreparedStatement statement=connection.prepareStatement(sql);
    statement.setString(1, num);
    ResultSet set=statement.executeQuery();
    List<CarTable> list=new ArrayList<CarTable>();
    while(set.next()) {
        CarTable carTable=new CarTable();
        carTable.setCarNumber(set.getString("car_number"));
        carTable.setCarStart(set.getString("car_start"));
        carTable.setCarState(set.getString("car_state"));
        carTable.setCarMoney(set.getInt("car_money"));
        carTable.setCarEnd(set.getString("car_end"));
        carTable.setCarId(set.getInt("car_id"));    
        list.add(carTable);
    }
    return list;
}
public static List<CarTable> datedemo(String date) throws Exception {
    String sql="select * from car_table where user_start=?";
    SimpleDateFormat simpleDateFormat=new SimpleDateFormat("MM-dd");
    
    Connection connection=getCon();
    PreparedStatement statement=connection.prepareStatement(sql);
    statement.setString(1, date);
    ResultSet set=statement.executeQuery();
    List<CarTable> list=new ArrayList<CarTable>();
    while(set.next()) {
        CarTable carTable=new CarTable();
        carTable.setCarNumber(set.getString("car_number"));
        carTable.setCarStart(set.getString("car_start"));
        carTable.setCarState(set.getString("car_state"));
        carTable.setCarMoney(set.getInt("car_money"));
        carTable.setCarEnd(set.getString("car_end"));
        carTable.setCarId(set.getInt("car_id"));    
        list.add(carTable);
    }
    return list;
}
public static List<CarTable> find(String start,String end) throws Exception {
    String sql="select * from car_table where car_start> ? and car_start< ? ";
    Connection connection=getCon();
    PreparedStatement statement=connection.prepareStatement(sql);
    statement.setString(1, start);
    statement.setString(2, end);
    ResultSet set=statement.executeQuery();
    List<CarTable> list=new ArrayList<CarTable>();
    while(set.next()) {
        CarTable carTable=new CarTable();
        carTable.setCarNumber(set.getString("car_number"));
        carTable.setCarStart(set.getString("car_start"));
        carTable.setCarState(set.getString("car_state"));
        carTable.setCarMoney(set.getInt("car_money"));
        carTable.setCarEnd(set.getString("car_end"));
        carTable.setCarId(set.getInt("car_id"));    
        list.add(carTable);
    }
    return list;
}

}

调用JDBC中的方法(停车场管理系统)

package Car;
import java.util.Date;
import java.util.List;
public class CarService {
public static String park(String number) throws Exception {
    /*
     * 获取当前时间
     * 转化成字符串
     * 封装到实体类
     * 调用jdbc的插入方法
     */
    //获取当前时间
    String time=CarUtils.getNow();
    CarTable carTable=new CarTable(number,time,"正在停车");
    
    //判断有没有
    CarTable car=CarJDBC.hasCar(number);
    if(car==null) {
        CarJDBC.insert(carTable);
        return "停车成功";
    }else {
        return "您已经停过车了";
    }
    
}

public static String pay(String number) throws Exception {
    CarTable car=CarJDBC.hasCar(number);//看之前有没有停过车
    if(car==null) {
        return "您没停车";
    }else {
        /*
         * 获得当前时间
         * 获取离开时间
         * 计算该收多少钱
         */
        String end=CarUtils.getNow();
        String start=car.getCarStart();
        Date enDate=CarUtils.s2d(end);
        Date startDate=CarUtils.s2d(start);
        
        long time=(enDate.getTime()-startDate.getTime())/1000/60;
        int money=(int)(time*10);
        car.setCarEnd(end);//离开时间
        car.setCarMoney(money);//收取的费用
        car.setCarState("已经离开");//停车状态
        
        CarJDBC.pay(car);
        return "共停车"+time+"分钟,消费"+money+"元，一路平安";
    }
    
}
public static boolean login(String name,String password) {
    if(name.equals("cxb521314")&&password.equals("5213149421")) {
        return true;
    }else {
        return false;
    }
}
public static List<CarTable> findAll() throws Exception{
    return CarJDBC.findAll();
}
public static List<CarTable> find(String start,String end) throws Exception{
    return CarJDBC.find(start,end);
}
public static List<CarTable> moneydemo() throws Exception {
    return CarJDBC.moneydemo();
}
public static List<CarTable> chepaihao(String num) throws Exception {
    return CarJDBC.chepaihao(num);
}
public static List<CarTable> datedemo(String date) throws Exception {
    return CarJDBC.chepaihao(date);
}
}

CarUtils(日期格式化)

package Car;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class CarUtils {
public static Date s2d(String time) throws ParseException {
    //HH是二十四小时制 hh是十二小时制
SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm");
return format.parse(time);
}
public static String getNow() {
    SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm");
    return format.format(new Date());
}

}

主方法Main

package Car;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class CarMain {
public static void main(String[] args) throws Exception {
    Scanner scanner=new Scanner(System.in);
    String input=null;
    String number=null;

    while(true) {
        System.out.println("1停车2取车3登录");
        //接收用户输入
        input=scanner.nextLine();
        switch (input) {
        case "1":
            System.out.println("请输入车牌号");
            number=scanner.nextLine();
            //调用服务层的停车方法车牌号传进去
            String msg= CarService.park(number);
            System.out.println(msg);

            break;
        case "2":
            System.out.println("请输入车牌号");
            number=scanner.nextLine();
            msg=CarService.pay(number);
            System.out.println(msg);
            break;
        case "3":
            System.out.println("输入用户名");
            String name=scanner.nextLine();
            System.out.println("请输入密码");
            String password=scanner.nextLine();
            boolean flag=CarService.login(name, password);
            if(flag) {
                //进入二级菜单
                while(true) {
                    System.out.println("1-查询所有记录\r\n"+
                            "2-查询所有未缴费车辆\r\n"+
                            "3-根据车牌号查询记录\r\n"+
                            "4-根据日期查询\r\n"+
                            "5-根据起止时间查询"                                                                
                            );
                    input=scanner.nextLine();
                    switch (input) {
                    case "1":
                        List<CarTable> list=CarService.findAll();
                        System.out.println(list);
                        break;
                    case "2":
                        List<CarTable> list2=CarService.moneydemo();
                        System.out.println(list2);
                        break;
                    case "3":
                        System.out.println("请输入车牌号");
                        String num=scanner.next();
                        List<CarTable> list4=CarService.chepaihao(num);
                        System.out.println(list4);
                        break;
                    case "4":
                        System.out.println("请输入日期");
                        String date=scanner.nextLine();
                        List<CarTable> list5=CarService.datedemo(date);
                        System.out.println(list5);
                        break;
                    case "5":
                        System.out.println("请输入开始时间");
                        String start=scanner.nextLine();
                        System.out.println("请输入结束时间");
                        String end=scanner.nextLine();
                        List<CarTable> list3=CarService.find(start, end);
                        System.out.println(list3);
                        break;
                    default:
                        System.out.println("您输入的数字不符合菜单栏要求请重新输入");
                        break;
                    }
                }


            }else {
                System.out.println("用户名或密码错误");
            }
            break;
        default:
            System.out.println("指令不存在");
            break;
        }
    }
}

}