JDBC

一、JDBC是什么

1、JDBC的概念

2、JDBC访问数据库步骤

3、新建JDBC项目

先建一个web工程

导入jdbc的依赖jar包

修复导入之后的问题

4、JDBC代码示例

####################初始代码##############################################
public class TestJDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
    //1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");
    //2、获取连接、数据库地址url、用户名、密码
        String url="jdbc:mysql://192.168.186.100/test";
        Connection conn = DriverManager.getConnection(url,"root","ok");
     //3、执行增删改查等操作
        Statement stat = conn.createStatement();
        String sql="select * from grade";
        ResultSet rs = stat.executeQuery(sql);
        while(rs.next()) {
            int gradeId = rs.getInt(1);
            String gradeName=rs.getString(2);
            System.out.println(gradeId+"\t"+gradeName);
        }
      //4、关闭释放资源
        rs.close();
        stat.close();
        conn.close();
    }
}
############################运行结果########################################
1	大一
3	大三
2	大二
4	大四
5	研一

#########################################提取方法后的jdbc写法#######################
public class TestJDBC {
    private static Connection conn;
    private static Statement stat;
    private static ResultSet rs;
    public static void getConn(){
        //1、加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //2、获取连接、数据库地址url，用户名、密码
        String url="jdbc:mysql://192.168.186.100:3306/test";
        try {
            conn = DriverManager.getConnection(url, "root", "ok");
            if(conn!=null)
                stat=conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void query(String sql,String param){
        try {
            if(stat!=null) {
                rs = stat.executeQuery(sql+param);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        finally {
//            try {
//                stat.close();
//                conn.close();
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
    }
    public static boolean update(String sql){
        int num = -1;
        try {
            if(stat!=null)
                num = stat.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num>0;
    }
    public static void close(){
        if(null!=rs){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(null!=stat){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(null!=conn){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void main(String[] args) throws Exception{
        getConn();
//        String sql="insert into grade(gradeId,gradeName) value(5,'研一')";
//        boolean  isCorrect= update(sql);
//        System.out.println(isCorrect?"插入成功":"插入失败");
        String sql = "select * from grade where gradeId=";
        query(sql,"4 or 1=1"); //sql注入 prepareStatement（防止sql注入）
        while (rs.next()) {
            System.out.println(rs.getString(2));
        }
//        String sql = "desc grade";
//        ResultSet set = query(sql);
//        while (set.next()) {
//            int i=1;
//            while (set.getMetaData().getColumnCount() >= i) {
//                System.out.print(set.getObject(i) + "\t|");
//                i++;
//            }
//            System.out.println();
//        }
//        set.close();
        close();
    }
}

二、prepareStatement

public class TestPrepared {
    private static Connection conn;
    private static PreparedStatement pst;
    private static ResultSet rs;
    private static final String URL="jdbc:mysql://192.168.186.100/test";
    public static void getConn(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(URL,"root","ok");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void query(String sql){
        try {
            pst=conn.prepareStatement(sql);
            rs=pst.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static boolean update(String sql){
        int num=0;
        try {
            pst=conn.prepareStatement(sql);
            num=pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num>0;
    }
    public static void close(){
        try {
            if (rs!=null) rs.close();
            if(pst!=null) pst.close();
            if(conn!=null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SQLException {
        getConn();
        String sql="select * from subject";
        query(sql);
        query(sql);
        while (rs.next()) {
            int i=1;
            while (rs.getMetaData().getColumnCount()>=i) {
                System.out.print(rs.getObject(i)+"\t");
                i++;
            }
            System.out.println();
        }
        close();
    }
}
###############################运行结果############################################
1	高等数学-1	120	1	
2	高等数学-2	110	2	
3	高等数学-3	100	3	
4	高等数学-4	130	4	
5	C语言-1	100	2	
6	C语言-2	110	2	
7	数据结构	110	2	
8	java	150	3	
9	python	120	4	

######################################################################

package cn.kgc.kb11;

import java.sql.*;

/**
 * @Author Wanglei
 * @Date 2021/3/9
 * @Description
 */
public class TestPrepared {
    private static Connection conn;
    private static PreparedStatement pst;
    private static ResultSet rs;
    private static final String URL="jdbc:mysql://192.168.186.100/test";
    public static void getConn(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(URL,"root","ok");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void query(String sql,String...params){
        try {
            pst=conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i+1,params[i]);
            }
            rs=pst.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static boolean update(String sql,String...params){
        int num=0;
        try {
            pst=conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i+1,params[i]);
            }
            num=pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num>0;
    }
    public static void close(){
        try {
            if (rs!=null) rs.close();
            if(pst!=null) pst.close();
            if(conn!=null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SQLException {
        getConn();
        String sql="select * from subject where subjectNo=?";
        query(sql,"3");
        while (rs.next()) {
            int i=1;
            while (rs.getMetaData().getColumnCount()>=i) {
                System.out.print(rs.getObject(i)+"\t");
                i++;
            }
            System.out.println();
        }
        close();
    }
}
##############################运行结果#############################################
3	高等数学-3	100	3	

三、JDBC的工作原理