JDBC执行SQL语句（statement对象）

※ 执行SQL语句（java中默认执行commit）

1.execute()
返回的结果boolean，boolean表示是否有结果集返回（除select外为false），有为true，其他情况都为false
2.executeUpdate()
返回的结果int，int表是对数据库影响的行计数
3.executeQuery() 返回的结果resultSet，一般情况存放的是select查询的结果集

注意：3种方式都可以执行各种sql语句，看你需要什么样的结果

※ 类和表有对应关系

一个java类对应数据库中的一张表
一个java对象对应数据库中的一行数据

注意：数据库表中的列名（不包含维护关系的列）就是类中的属性

/*
*Teacher数据类
*/
package com.briup.jdbc;

import java.sql.Date;

public class Teacher {
    private long id;
    private String name;
    private int age;
    private Date birth;

    public Teacher(String name, int age, Date birth) {
        super();
        this.name = name;
        this.age = age;
        this.birth = birth;
    }
    public Teacher() {
        super();
    }
    public Teacher(long id, String name, int age, Date birth) {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
        this.birth = birth;
    }
    @Override
    public String toString() {
        return "Teacher [id=" + id + ", name=" + name + ", age=" + age + ", birth=" + birth + "]";
    }
    public long getId() {
        return id;
    }
    public void setId(long id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public Date getBirth() {
        return birth;
    }
    public void setBirth(Date birth) {
        this.birth = birth;
    }

}



/*
*执行SQL语句遵循六步规则
*/
package com.briup.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class DMLSJdbc {
    private String driver="oracle.jdbc.driver.OracleDriver";
    private String url="jdbc:oracle:thin:@192.168.43.216:1521:XE";
    private String user="jd1812";
    private String password="briup";
    public static void main(String[] args) {
//      new DMLSJdbc().createTable();
        //new DMLSJdbc().insert();
        List<Teacher> list=new ArrayList<>();
        for(int i=0;i<100;i++){
            list.add(new Teacher("lisi"+i, i, new Date(System.currentTimeMillis())));
        }
        new DMLSJdbc().insert(list);
    }
    //DDL (alter create truncate drop rename)
    public void createTable(){
        Connection conn=null;
        Statement sts=null;
            //第一步：注册驱动
        try {
            Class.forName(driver);
            //第二步获取连接
            conn=DriverManager.getConnection(url, user, password);
            //第三步创建Statement对象
            sts=conn.createStatement();
            String sql="create table s_tea("
                    + "id number primary key,"
                    + "name varchar2(10) not null,"
                    + "age number,"
                    + "birth date"
                    + ")";
            //第四步执行sql语句
//          boolean b=sts.execute(sql);
//          System.out.println(b);
//          int n=sts.executeUpdate(sql);
//          System.out.println(n);
            sts.executeQuery(sql);
            //第五步省略，没有结果
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            //关闭资源
            try {
                if(sts!=null) sts.close();
                if(conn!=null) conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    public void insert(){
        Connection conn=null;
        Statement sts=null;
        //第一步：注册驱动
        try {
            Class.forName(driver);
            //第二步：获取连接
            conn=DriverManager.getConnection(url, user, password);
            //第三步：构建Statement对象
            sts=conn.createStatement();
//          String sql="insert into s_tea values(2,'jake',33,'09-9月-18')";
            String sql="insert into s_tea values(4,'rose',23,to_date('21-9月-18','dd-Mon-yy'))";
            //第四步：执行sql语句
//          boolean b=sts.execute(sql);
//          System.out.println(b);
//          int n=sts.executeUpdate(sql);
//          System.out.println(n);
            sts.executeQuery(sql);
            //第五步：没有结果，省略；
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            //第六步：关闭资源
            try {
                if(sts!=null)sts.close();
                if(conn!=null)conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }
    public void insert(List<Teacher> list){
        Connection conn=null;
        Statement sts=null;
        try {
            Class.forName(driver);
            //连接对象不能无限创建
            conn=DriverManager.getConnection(url, user, password);
            sts=conn.createStatement();
            SimpleDateFormat sdf=new SimpleDateFormat("dd-MM-yy");
            for(int i=0;i<list.size();i++){
                Teacher tea=list.get(i);
                String d=sdf.format(tea.getBirth());//09-09-18
            //在写SQL语句时，可以先写一组符合的数据，然后在改成java代码中数据，不易出错
                String sql="insert into s_tea values(s_t.nextval,'"+tea.getName()+"',"+tea.getAge()+",to_date('"+d+"','dd-mm-yy'))";
                sts.executeQuery(sql);
            }

        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            //第六步：关闭资源
            try {
                if(sts!=null)sts.close();
                if(conn!=null)conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }
    public List<Teacher> findAllTeacher(){
        Connection conn=null;
        Statement sts=null;
        ResultSet rs=null;
        List<Teacher> list=new ArrayList<>();
        //第一步：注册驱动
        try {
            Class.forName(driver);
            //第二步：获取连接
            conn=
                    DriverManager.getConnection(url, user, password);
            //第三步：创建Statement对象
            sts=conn.createStatement();
            //*->id name age birth
            String sql="select * from s_tea";
            //第四步：执行sql语句
//          boolean b=sts.execute(sql);
//          System.out.println(b);
//          int n=sts.executeUpdate(sql);
//          System.out.println(n);
            rs=sts.executeQuery(sql);
            /*
             * id       name    age birth
             * id       name    age birth
             * id       name    age birth
             * id       name    age birth
             * id       name    age birth
             */
            while(rs.next()){
                //1.取值基于列在select后面的位置取
                //看java中你需要的类型
//              long id=rs.getLong(1);
//              String name=rs.getString(2);
//              int age =rs.getInt(3);
//              //Date da=rs.getDate(4);
//              java.util.Date da=rs.getDate(4);
//              System.out.println(id+"-"+name+"-"+age+"-"+da);
                //2.取值基于列的名字取值（如果有别名，以别名为准）
                long id=rs.getLong("id");
                String name=rs.getString("name");
                int age=rs.getInt("age");
                Date d=rs.getDate("birth");
                System.out.println(id+"-"+name+"-"+age+"-"+d);
                Teacher t=new Teacher(id, name, age, d);
                list.add(t);
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            //第六步：关闭资源
            try {
                if(rs!=null)rs.close();
                if(sts!=null)sts.close();
                if(conn!=null)conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return list;
    }
    public Teacher findTeacherById(long id){
        Connection conn=null;
        Statement sts=null;
        ResultSet rs=null;
        Teacher tea=null;
        //第一步：注册驱动
        try {
            Class.forName(driver);
            //第二步：获取连接
            conn=
                    DriverManager.getConnection(url, user, password);
            //第三步：创建Statement对象
            sts=conn.createStatement();
            //*->id name age birth
            //String sql="select * from s_tea where id="+id+"";
            String sql="select * from s_tea where id="+id;
            //第四步：执行sql语句
//          boolean b=sts.execute(sql);
//          System.out.println(b);
//          int n=sts.executeUpdate(sql);
//          System.out.println(n);
            rs=sts.executeQuery(sql);
            /*
             * id       name    age birth
             * id       name    age birth
             * id       name    age birth
             * id       name    age birth
             * id       name    age birth
             */
            while(rs.next()){
                //取值基于列在select后面的位置取
                //看java中你需要的类型
//              long id=rs.getLong(1);
//              String name=rs.getString(2);
//              int age =rs.getInt(3);
//              //Date da=rs.getDate(4);
//              java.util.Date da=rs.getDate(4);
//              System.out.println(id+"-"+name+"-"+age+"-"+da);
                //取值基于列的名字取值（如果有别名，以别名为准）
                long ids=rs.getLong("id");
                String name=rs.getString("name");
                int age=rs.getInt("age");
                Date d=rs.getDate("birth");
                System.out.println(ids+"-"+name+"-"+age+"-"+d);
                tea=new Teacher(ids, name, age, d);
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            //第六步：关闭资源
            try {
                if(rs!=null)rs.close();
                if(sts!=null)sts.close();
                if(conn!=null)conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return tea;
    }
    public void deleteTeacher(long id){
        Connection conn=null;
        Statement sts=null;
        //第一步：注册驱动
        try {
            Class.forName(driver);
            //第二步：获取连接
            conn=
                    DriverManager.getConnection(url, user, password);
            //第三步：创建Statement对象
            sts=conn.createStatement();
            String sql="delete from s_tea where id="+id;
            //第四步：执行sql语句
            sts.execute(sql);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            //第六步：关闭资源
            try {
                if(sts!=null)sts.close();
                if(conn!=null)conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    public void updateTeacher(Teacher tea){
        Connection conn=null;
        Statement sts=null;
        //第一步：注册驱动
        try {
            Class.forName(driver);
            //第二步：获取连接
            conn=
                    DriverManager.getConnection(url, user, password);
            //第三步：创建Statement对象
            sts=conn.createStatement();
            SimpleDateFormat sdf=new SimpleDateFormat("dd-MM-yy");
            String da=sdf.format(tea.getBirth());
            String sql="update s_tea set name='"+tea.getName()+"',"
                        + "age="+tea.getAge()+","
                        + "birth=to_date('"+da+"','dd-mm-yy') "
                        + " where id="+tea.getId();
            //第四步：执行sql语句
            sts.execute(sql);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            //第六步：关闭资源
            try {
                if(sts!=null)sts.close();
                if(conn!=null)conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }

}