package com.imooc.jdbc.demo1;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.junit.Test;

import com.imooc.jdbc.utils.JDBCUtils;

public class JDBCTest6 {

    //首先将表格中的三条数据添加到数据库
    @Test
    public void insert(){
        Connection con=null;
        PreparedStatement pStm=null;
        try {
            con=JDBCUtils.getConnection();
            String sql="INSERT course(name,category,desp,createTime) VALUES(?,?,?,?),(?,?,?,?),(?,?,?,?);";
            pStm=con.prepareStatement(sql);
            pStm.setString(1, "Java零基础");
            pStm.setString(2, "Java");
            pStm.setString(3, "Java基础语法");
            pStm.setDate(4, new Date(System.currentTimeMillis()));
            pStm.setString(5, "Java Web");
            pStm.setString(6, "Java");
            pStm.setString(7, "Jsp和Servlet");
            pStm.setDate(8, new Date(System.currentTimeMillis()));
            pStm.setString(9, "前端小白");
            pStm.setString(10, "前端");
            pStm.setString(11, "HTML/CSS/JS");
            pStm.setDate(12, new Date(System.currentTimeMillis()));
            int i=pStm.executeUpdate();
            if(i>0) {
                System.out.println(i+" lines affected!");
            }
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(pStm, con);
        }
    }

    //显示所有数据
    @Test
    public void displayAll() {
        Connection con=null;
        PreparedStatement pStm=null;
        ResultSet rs=null;
        try {
            con=JDBCUtils.getConnection();
            String sql="Select * FROM course;";
            pStm=con.prepareStatement(sql);
            rs=pStm.executeQuery();
            while(rs.next()) {
                int id=rs.getInt("id");
                String name=rs.getString("name");
                String category=rs.getString("category");
                String desp=rs.getString("desp");
                Date createTime=rs.getDate("createTime");
                System.out.println(id+"    "+name+"    "+category+"    "+desp+"    "+createTime);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(pStm, con, rs);
        }
    }

    //查询课程名称为Java零基础的数据并显示
    @Test
    public void display() {
        Connection con=null;
        PreparedStatement pStm=null;
        ResultSet rs=null;
        try {
            con=JDBCUtils.getConnection();
            String sql="Select * FROM course WHERE name=?;";
            pStm=con.prepareStatement(sql);
            pStm.setString(1, "Java零基础");
            rs=pStm.executeQuery();
            if(rs.next()) {
                int id=rs.getInt("id");
                String name=rs.getString("name");
                String category=rs.getString("category");
                String desp=rs.getString("desp");
                Date createTime=rs.getDate("createTime");
                System.out.println(id+"    "+name+"    "+category+"    "+desp+"    "+createTime);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(pStm, con, rs);
        }
    }

    //将Java零基础的课程描述改为Java语法
    @Test
    public void update(){
        Connection con=null;
        PreparedStatement pStm=null;
        try {
            con=JDBCUtils.getConnection();
            String sql="UPDATE course SET desp=? WHERE name=?";
            pStm=con.prepareStatement(sql);
            pStm.setString(1, "Java语法");
            pStm.setString(2, "Java零基础");
            int i=pStm.executeUpdate();
            if(i>0) {
                System.out.println(i+" lines affected!");
            }
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(pStm, con);
        }
    }

    //删除Java Web这门课程
    @Test
    public void delete(){
        Connection con=null;
        PreparedStatement pStm=null;
        try {
            con=JDBCUtils.getConnection();
            String sql="DELETE FROM course WHERE name=?";
            pStm=con.prepareStatement(sql);
            pStm.setString(1, "Java Web");
            int i=pStm.executeUpdate();
            if(i>0) {
                System.out.println(i+" lines affected!");
            }
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.release(pStm, con);
        }
    }

    //按创建时间倒序排序显示所有数据
    @Test
    public void displayAllDesc() {
        Connection con=null;
        PreparedStatement pStm=null;
        ResultSet rs=null;
        try {
            con=JDBCUtils.getConnection();
            String sql="Select * FROM course ORDER BY createTime DESC;";
            pStm=con.prepareStatement(sql);
            rs=pStm.executeQuery();
            while(rs.next()) {
                int id=rs.getInt("id");
                String name=rs.getString("name");
                String category=rs.getString("category");
                String desp=rs.getString("desp");
                Date createTime=rs.getDate("createTime");
                System.out.println(id+"    "+name+"    "+category+"    "+desp+"    "+createTime);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.release(pStm, con, rs);
        }
    }
}

-- 课程信息表
CREATE TABLE IF NOT EXISTS course(
  id INT AUTO_INCREMENT KEY COMMENT "课程编号",
  name VARCHAR(20) NOT NULL COMMENT "课程名称",
  category VARCHAR(20) NOT NULL COMMENT "所属方向",
  desp VARCHAR(30) NOT NULL COMMENT "课程描述",
  createTime DATETIME NOT NULL COMMENT "创建时间" 
);

-- 课程信息表内容
INSERT course(name,category,desp,createTime) VALUES("Java零基础","Java","Java基础语法",now()),
("Java Web","Java","Jsp和Servlet",now()),
("前端小白","前端","HTML/CSS/JS",now());

老师，在java中书写sql语句时要;结尾吗比如String sql="SELECT * FROM course;"，我试过有没有;sql都能正常执行，平时是写上;比较好吗？

在sql语句用now()导入的数据带时间的，但是我在Java中使用new Date(System.currentTimeMillis())存入数据库的时间只精确带到天没有时间，这是正常现象吗?

对于这样的当前时间存入要求，把now()直接写到sql字符串里是不是更好一些？