JDBC部分内容

JDBC概述

java Database cnnection -->java数据库连接

jdbc是java制定的一套用于如何连接数据的API,可以支持连接多种数据库

java只定义了一套标准(抽象类/接口)

具体链接数据的实现,数据库开发商来实现,实现必须遵守java制定的规范

JDBC搭建

1.将mysql实现的jar文件 导入项目中

mysql-connector-java-8.0.16.jar

2.加载驱动

1.new Driver ()

2.Class.forName("com.mysql.cj.jdbc.Driver");//动态加载Driver

3.建立与数据库的连接

Connection接口 DriverManager.getConnection()中存放

URL:jdbc:mysql://ip(127.0.0.1):端口(3306)/数据库

名?serverTimezone=Asia/Shanghai

USER:用户名(root)

PASS:密码(root)

4.向数据库发送sql

两种方法:

1.Statement

①.直接将变量拼接到sql中,书写不方便

Statement st = connection.createStatement();                        st.executeUpdate("insert into student(num,name,gender,birthday,height,phone,address,reg_time)"+                                    "value("+num+",'"+name+"','"+gender+"','"+birthday+"',"+height+",'"+phone+"','"+address+"',now())");

②.直接将变量拼接到sql中,不安全别人可以在参数中添加一些永 远成立的条件,也就是恶意攻击(sql注入).

2.PerpareStatement

①.不需要将变量拼接到sql中,书写方便,通过特定的set方法设置值

②.set方法会检测输入进来的值,防止sql注入.

我的是jdk1.8.0_261在输入("1 or 1=1")时就会报错 Data truncation: Truncated incorrect DOUBLE value: '1 or 1=1'

5.关闭与数据库的连接

JDBC增删改查

为student表添加一行数据

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Dome2.operStudent(20,"王某某","女","2002-02-02",1.65,"18012378212","陕西西安");    
    }    
    public static void operStudent(int num,String name,String gender,String birthday,double height,String phone,String address) throws ClassNotFoundException, SQLException {                        //new Driver();加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");//动态加载Driver
        String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";                        
        String uname = "root";                       
        String password = "****";//此处为mysql密码        
        Connection connection = DriverManager.getConnection(url,uname,password);
        //向数据库发送sql
        Statement st = connection.createStatement();                    st.executeUpdate("insert into student(num,name,gender,birthday,height,phone,address,reg_time)"+                                            "value("+num+",'"+name+"','"+gender+"','"+birthday+"',"+height+",'"+phone+"','"+address+"',now())");
        //关闭连接
        st.close();                    
        connection.close();
    }
}

下面是用preparedStatement接口实现添加一行,"?"是一个占位符在相应的位置上就可以输入数据

Connection connection = DriverManager.getConnection(url,uname,password);
//从此处开始不一样
//将sql与sql值预编译到PreparedStatement对象中,并没有执行
PreparedStatement ps = connection.prepareStatement("insert into student(num,name,gender,birthday,height,phone,address,reg_time)"+                                                        "value(?,?,?,?,?,?,?,?)");
ps.setObject(1,num);
ps.setObject(2,name);
ps.setObject(3,gender);
ps.setObject(4,birthday);
ps.setObject(5,height);
ps.setObject(6,phone);
ps.setObject(7,address);
ps.setObject(8,new java.util.Date());
ps.executeUpdate();
ps.close();
connection.close();

删除比较简单就不多说了,delect所在行就可以了.

但要强调一点最好是使用prepareStatement比较安全

前文部分说过

st.executeUpdate("delete from major where id = "+id);

查询分了单行查询和全部查询,我们需要创建一个student类,来接收我们查询到的数据

上代码:

单行查询

public static void main(String[] args) throws SQLException, ClassNotFoundException {
    Student student = findStudentByID(14);
    System.out.println(student);
}
public static Student findStudentByID(Integer num) throws ClassNotFoundException, SQLException {
    //new Driver();
    Class.forName("com.mysql.cj.jdbc.Driver");//动态加载Driver
    String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
    String uname = "root";
    String password = "****";
    Connection connection = DriverManager.getConnection(url,uname,password);
    PreparedStatement ps = connection.prepareStatement(""+"select num,name,gender,birthday,phone,height,address from student where num = ?");
    ps.setObject(1,num);
    ResultSet rs = ps.executeQuery();//执行一个查询操作,将查询结果封装到ResultSet中
    Student student = new Student();//创建学生对象,接收数据库查询的数据
    while (rs.next()){
        student.setNum(rs.getInt("num"));
        student.setName(rs.getString("name"));
        student.setGender(rs.getString("gender"));
        student.setBirthday(rs.getString("birthday"));
        student.setHeight(rs.getDouble("height"));
        student.setPhone(rs.getString("phone"));
        student.setAddress(rs.getString("address"));
    }
    return student;
}
}

多行查询,也没多大区别就是创建了一个集合,将查询到的数据存放在集合中

public static void main(String[] args) throws SQLException, ClassNotFoundException {
    ArrayList<Student> students = findStudents();
    System.out.println(students);
    System.out.println(students.size());
}
public static ArrayList<Student> findStudents() throws ClassNotFoundException, SQLException {
    //new Driver();
    Class.forName("com.mysql.cj.jdbc.Driver");//动态加载Driver
    String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";
    String uname = "root";
    String password = "hzhz";
    Connection connection = DriverManager.getConnection(url,uname,password);
    //将sql与sql值预编译到PreparedStatement对象中,并没有执行
    PreparedStatement ps = connection.prepareStatement(""+                "select num,name,gender,birthday,phone,height,address from student");                ResultSet rs = ps.executeQuery();
    ArrayList<Student> students = new ArrayList<>();
    while (rs.next()){
        Student student = new Student();//此处每循环一次创建一个学生对象,直至取出数据库中的所有对象
        student.setNum(rs.getInt("num"));
        student.setName(rs.getString("name"));
        student.setGender(rs.getString("gender"));
        student.setBirthday(rs.getString("birthday"));
        student.setHeight(rs.getDouble("height"));
        student.setPhone(rs.getString("phone"));
        student.setAddress(rs.getString("address"));
        students.add(student);
    }
    return students;
}
}