参考资料
练习题 icon lost
交流讨论
笔记
img lost
资料来源 jdbc介绍
视频作者 weixin_46938490

一、JDBC概述

1、JDBC(Java DataBase Connectivity)java 数据库连接
2、是一种用于执行SQL语句的java API ,可以为多种关系型数据库提供统一访问,它由一组java语言编写的类和接口组成。
JDBC API:
供程序员调用的接口与类,集成在java.sql 包中
DriverManager类作用:管理各种不同的jdbc驱动;
Connection 接口 与特定数据库的连接
Statement 接口 执行sql
PreparedStatement 接口 执行sql
ResultSet 接口 接收查询结果

二、JDBC搭建

1、在工程目录下新建lib目录,将需要的jar包复制到该目录下
2、将jar包引入工程
在这里插入图片描述
在这里插入图片描述
3、编写程序
(1)注册JDBC驱动程序
这需要初始化驱动程序,这样就可以打开与数据库的通信信道。
Class.forName(“com.mysql.cj.jdbc.Driver”);//反射实现
或者
DriverManager.registerDriver(new Driver());
(2)建立与数据库连接
这需要使用DriverManger。getConnection()方法来创建一个Connection对象,他代表一个物理连接的数据库。
Connection conn=
DriverManger。getConnection(URL,USER,PASS);
URL:jdbc:mysql: //ip(127.0.0.1):端口(3306) /数据库名?characterEncoding=utf8&useSSL=false&serverTimezone=UTC
USER:用户名(root)
PASS:密码
(3)获得Satement执行sql语句
Statement st = connection.createStatement();

package com.java.ff.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcDemo2 {
    public static void main(String[] args) {
        JdbcDemo2 jd2 = new JdbcDemo2();
        try {
            jd2.savestudent("王萍", "男");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
    public void savestudent(String name, String sex) throws ClassNotFoundException, SQLException {
        Connection connection=null;
        Statement st=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String  url= "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
            connection=DriverManager.getConnection(url,"root","123");
            st=connection.createStatement();
         int row=   st.executeUpdate("insert into t_teacher(name,sex)" +
                    "values ('"+name+"','"+sex+"')");
            System.out.println(row);
        }finally {
            if(st!=null){
                st.close();
            }
            if(connection!=null){
                connection.close();
            }
        }
        }

    }


(4) 获得PrepareStatement执行sql语句

package com.java.ff.jdbc;

import java.sql.*;

public class JdbcDemo5 {
    public static void main(String[] args) {
        JdbcDemo5 jd2 = new JdbcDemo5();
        try {
            jd2.savestudent("王萍", "男");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
    public void savestudent(String name, String sex) throws ClassNotFoundException, SQLException {
        Connection connection=null;
      PreparedStatement ps=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String  url= "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
            connection=DriverManager.getConnection(url,"root","123");
            //st=connection.createStatement();
           ps=connection.prepareStatement("insert into t_teacher(name,sex) values (?,?)");
           ps.setString(1,name);
           ps.setString(2,sex);
           ps.executeUpdate();
            System.out.println(ps);
        }finally {
            if(ps!=null){
                ps.close();
            }
            if(connection!=null){
                connection.close();
            }
        }
        }

    }


(5)关闭与数据库的链接通道
每次操作完成后关闭所有与数据库交互的通道
st.close();
rs.close();
conn.close();
ps.close();

三、 PreparedStatement和Statement

1、代码的可读性和可维护性
2、提高了安全性

package com.java.ff.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JdbcDemo7 {
    public static void main(String[] args) {
        JdbcDemo7 jd2 = new JdbcDemo7();
        try {
            jd2.deletestudent("17 or 1=1");  //安全
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
    public void deletestudent(String num) throws ClassNotFoundException, SQLException {
        Connection connection=null;
       PreparedStatement ps =null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String  url= "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
            connection=DriverManager.getConnection(url,"root","123");
            ps=connection.prepareStatement("delete from t_teacher where num = ? ");
            ps.setString(1,num);
            ps.executeUpdate();
        }finally {
            if(ps!=null){
                ps.close();
            }
            if(connection!=null){
                connection.close();
            }
        }
        }

    }


package com.java.ff.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcDemo8 {
    public static void main(String[] args) {
        JdbcDemo8 jd2 = new JdbcDemo8();
        try {
            jd2.deletestudent("12 or 1=1");  //不安全
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
    public void deletestudent(String id) throws ClassNotFoundException, SQLException {
        Connection connection=null;
        Statement st=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String  url= "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
            connection=DriverManager.getConnection(url,"root","123");
            st=connection.createStatement();

          // int row =st.executeUpdate("delete  from grade where id ="+id);

           int row=   st.executeUpdate("delete  from graded where id ="+id);

           // int row=   st.executeUpdate("delete from graded where id= "+id);

            System.out.println(row);
        }finally {
            if(st!=null){
                st.close();
            }
            if(connection!=null){
                connection.close();
            }
        }
        }

    }


四、结果集处理

1、PreparedStatement 和Statement 中的executeQuery()方法中会返回一个ResultSet对象,查询结果就封装在此对象中。
2、使用ResultSet中的next()方法获得下一行数据
3、使用getXXX(String name)方法获得值

package com.java.ff.jdbc;

import java.sql.*;

public class JdbcDemo9 {

    public static void main(String[] args) {
        JdbcDemo9 jd2 = new JdbcDemo9();
        try {
          Student student = jd2.findstudent(16);
            System.out.println(student);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
    public Student findstudent(int num) throws ClassNotFoundException, SQLException {
        Connection connection=null;
       PreparedStatement ps =null;
        Student student =new Student();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String  url= "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
            connection=DriverManager.getConnection(url,"root","123");
            ps=connection.prepareStatement("SELECT NAME,num,sex,score,height,operttime\n" +
                    "FROM t_teacher\n" +
                    "WHERE num=?");
            ps.setInt(1,num);
        ResultSet res=ps.executeQuery();
            while (res.next()){
                student.setName(res.getString("name"));
                student.setNum(res.getString("num"));
                student.setSex(res.getString("sex"));
                student.setScore(res.getString("score"));
                student.setHeight(res.getString("height"));
                student.setOperttime(res.getString("operttime"));

            }

        }finally {
            if(ps!=null){
                ps.close();
            }
            if(connection!=null){
                connection.close();
            }
        }
        return student ;
        }

    }


package com.java.ff.jdbc;

import java.sql.*;
import java.util.ArrayList;

public class JdbcDemo10 {

    public static void main(String[] args) {
        JdbcDemo10 jd2 = new JdbcDemo10();
        try {

            ArrayList<Student> list =jd2.findstudent("122");
            System.out.println(list );
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public ArrayList<Student> findstudent(String weight) throws ClassNotFoundException, SQLException {
        Connection connection=null;
       PreparedStatement ps =null;
        ArrayList<Student> list =new ArrayList<>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String  url= "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
            connection=DriverManager.getConnection(url,"root","123");
            ps=connection.prepareStatement("SELECT NAME,num,sex,score,height,operttime\n" +
                    "FROM t_teacher\n" +
                    "WHERE weight=?");
            ps.setString(1,weight);
        ResultSet res=ps.executeQuery();
            while (res.next()){
                Student student =new Student();
                student.setName(res.getString("name"));
                student.setNum(res.getString("num"));
                student.setSex(res.getString("sex"));
                student.setScore(res.getString("score"));
                student.setHeight(res.getString("height"));
                student.setOperttime(res.getString("operttime"));
            list.add(student);
            }

        }finally {
            if(ps!=null){
                ps.close();
            }
            if(connection!=null){
                connection.close();
            }
        }
    return list;
        }

    }


资料来源 JDBC概述
博客作者 qq_48019875
前往答题
我的笔记