JDBC的应用

1、在项目中添加jar文件

2、加载驱动类

3、建立与数据库的连接，获得连接对象

4、发送SQL

5、关闭数据库连接

//2.加载驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");
 
        //3.建立与数据库的连接，获得连接对象
        String url="jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai"; //其中schooldb为自己的数据库的名称
        String user="root";
        String password="root";
        Connection connection= DriverManager.getConnection(url,user,password);
 
        //4.发送sql
        Statement st =connection.createStatement();
        st.executeUpdate("insert into major(college)values('外国语学院')");
 
        //5.关闭数据库连接
        st.close();
        connection.close();

 从idea向数据库中添加，删除，修改数据

package Day1;
 
import java.sql.*;
 
public class Demo2 {
    public static void main(String[] args) {
        Demo2 demo2=new Demo2();
        try {
            //demo2.save("崔渭阳","男","123425", "渭南",4);
            //demo2.update(3);
            //demo2.delete(10);
            demo2.saves("朱京宝","男","123426", "安康",2);
            //demo2.updates(5,"123419", "延安");
            //demo2.deletes(12);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
 
    //添加1.0
    public void save(String name,String gender,String phone,String address,int majorid) throws ClassNotFoundException, SQLException {
        //加载驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");
        //建立与数据库的连接
        String url="jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
        String user="root";
        String password="root";
        Connection connection= DriverManager.getConnection(url,user,password);
        //发送sql
        Statement st=connection.createStatement();
        st.executeUpdate("INSERT INTO student(NAME,gender,phone,address,TIME,majorid)"+" VALUES('"+name+"','"+gender+"','"+phone+"','"+address+"',now(),"+majorid+")");
        //关闭数据库连接
        st.close();
        connection.close();
    }
 
    //修改
    public void update(int num) throws ClassNotFoundException, SQLException {
        //加载驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");
        //建立与数据库的连接
        String url="jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
        String user="root";
        String password="root";
        Connection connection= DriverManager.getConnection(url,user,password);
        //发送sql
        Statement st=connection.createStatement();
        st.executeUpdate("update student set height=1.89,address='汉中'"+" where num = +"+num+"");
        //关闭数据库连接
        st.close();
        connection.close();
    }
 
    //删除
    public void delete(int num) throws ClassNotFoundException, SQLException {
        //加载驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");
        //建立与数据库的连接
        String url="jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
        String user="root";
        String password="root";
        Connection connection= DriverManager.getConnection(url,user,password);
        //发送sql
        Statement st=connection.createStatement();
        st.executeUpdate("delete from student"+" where num = +"+num+"");
        //关闭数据库连接
        st.close();
        connection.close();
    }
 
    //添加2.0
    public void saves(String name,String gender,String phone,String address,int majorid) throws ClassNotFoundException, SQLException {
        //加载驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");
        //建立与数据库的连接
        String url="jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
        String user="root";
        String password="root";
        Connection connection= DriverManager.getConnection(url,user,password);
        //发送sql
        PreparedStatement ps=connection.prepareStatement("INSERT INTO student(NAME,gender,phone,address,TIME ,majorid)"+" VALUES(?,?,?,?,now(),?)");
        ps.setObject(1, name);
        ps.setObject(2, gender);
        ps.setObject(3, phone);
        ps.setObject(4, address);
        ps.setObject(5,majorid);
 
        //真正执行
        ps.executeUpdate();
        //关闭数据库连接
        ps.close();
        connection.close();
    }
 
    //修改2.0
    public void updates(int num,String phone,String address) throws ClassNotFoundException, SQLException {
        //加载驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");
        //建立与数据库的连接
        String url="jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
        String user="root";
        String password="root";
        Connection connection= DriverManager.getConnection(url,user,password);
        //发送sql
        PreparedStatement ps=connection.prepareStatement("update student set phone=?,address=? where num=?");
        ps.setObject(1, phone);
        ps.setObject(2, address);
        ps.setObject(3, num);
        //真正执行
        ps.executeUpdate();
        //关闭数据库连接
        ps.close();
        connection.close();
    }
 
    //删除2.0
    public void deletes(int num) throws ClassNotFoundException, SQLException {
        //加载驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");
        //建立与数据库的连接
        String url="jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
        String user="root";
        String password="root";
        Connection connection= DriverManager.getConnection(url,user,password);
        //发送sql
        PreparedStatement ps=connection.prepareStatement("delete from student where num=?");
        ps.setObject(1, num);
        //真正执行
        ps.executeUpdate();
        //关闭数据库连接
        ps.close();
        connection.close();
    }
}

PreparedStatement和Statement的区别：

相同点：都是向数据库发送sql

不同点：

Statement：将参数直接拼接到sql中，要拼接字符串，写起来麻烦，安全性差

PreparedStatement：先用？占位，再通过setObject()赋值，写法简单，安全性强

package Day1;
 
import java.sql.*;
import java.util.ArrayList;
 
public class Demo4 {
    public static void main(String[] args) {
        Demo4 demo4=new Demo4();
        try {
            //Student student=demo4.queryNum(1);
            //System.out.println(student);
          ArrayList<Student> students=demo4.queryGender("男");
            System.out.println(students);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
 
    //用学号查询
    public Student queryNum(int num) throws ClassNotFoundException, SQLException {
        //加载驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");
        //建立与数据库的连接
        String url="jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
        String user="root";
        String password="root";
        Connection connection= DriverManager.getConnection(url,user,password);
        //发送sql
        PreparedStatement ps=connection.prepareStatement("select num,name,gender,phone,address,time from student where num = ?");
        ps.setObject(1, num);
        //查询
        ResultSet rs=ps.executeQuery();//将查询的结果封装到一个ResultSet对象中，需要将ResultSet对象中的数据封装到对应的对象中
        Student student=null;
        //next() 如果结果有数据，返回true，否则false
        while(rs.next()){
            student=new Student();
            student.setNum(rs.getInt("num"));
            student.setName(rs.getNString("name"));
            student.setGender(rs.getNString("gender"));
            student.setPhone(rs.getNString("phone"));
            student.setAddress(rs.getNString("address"));
            student.setTime(rs.getTimestamp("time"));
        }
        //关闭与数据库的连接
        rs.close();
        ps.close();
        connection.close();
 
        return student;
    }
 
    
    //用性别来查询
    //集合方法
    public ArrayList<Student> queryGender(String gender) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //建立与数据库的连接
        String url="jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
        String user="root";
        String password="root";
        Connection connection= DriverManager.getConnection(url,user,password);
        //发送sql
        PreparedStatement ps=connection.prepareStatement("select num,name,gender,phone,address,time from student where gender = ?");
        ps.setObject(1, gender);
        //查询
        ResultSet rs=ps.executeQuery();
        ArrayList<Student> students=new ArrayList<>();
        while (rs.next()){
            //每查一组数据,创建一个对象，将对象存到集合中
            Student student=new Student();
            students.add(student);
            student.setNum(rs.getInt("num"));
            student.setName(rs.getNString("name"));
            student.setGender(rs.getNString("gender"));
            student.setPhone(rs.getNString("phone"));
            student.setAddress(rs.getNString("address"));
            student.setTime(rs.getTimestamp("time"));
        }
        //关闭与数据库的连接
        rs.close();
        ps.close();
        connection.close();
 
        return students;
    }
}