JDBC

JDBC

前言：先在idea中导入驱动jar包

一、JDBC

JDBC访问数据库步骤

1. 加载驱动
2. 获取连接
3. 创建Statement，执行Sql语句
4. 返回查询结果
5. 关闭资源

具体代码如下：

Statement

 public static void main(String[] args) throws Exception {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.获取连接(url,username,password)
        String url = "jdbc:mysql://192.168.XXX.XXX:3306/test";
        Connection conn = DriverManager.getConnection(url,"root","root");

        //3.执行sql语句
        String sql = "select * from grade";
        Statement state = conn.createStatement();
        ResultSet rs = state.executeQuery(sql);
        while (rs.next()){
            int gradeId = rs.getInt(1);
            String gradeName = rs.getString(2);
            System.out.println(gradeId+"\t    "+gradeName);
        }

        //4.关闭
        rs.close();
        state.close();
        conn.close();
    }

//desc
String sql = "desc student";		//SQL语句
        ResultSet set = query(sql);   //返回结果集
        while (set.next()){
            int i = 1;
            while (i<=set.getMetaData().getColumnCount()){
                System.out.print(set.getObject(i)+"\t");
                i++;
            }
            System.out.println();
        }
        set.close();
        

PreparedStatement

package Test1;

import java.sql.*;

/**
 * @Author shall潇
 * @Date 2021/3/5
 * @Description
 */
public class TestPreparedState {
    private static final String URL = "jdbc:mysql://192.168.XXX.XXX:3306/test";
    private static Connection conn;
    private static PreparedStatement ps;
    private static ResultSet rs;

    public static void getConn(){		//获取连接
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(URL,"root","root");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void query(String sql,String...params){		//查询
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {		//多个参数依次赋值
                ps.setObject(i+1,params[i]);
            }
            rs = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static boolean update(String sql,String...params){		//增删改
        int num = -1;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i+1,params[i]);
            }
            num = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num>0;
    }

    public static void close(){
        if (null != conn) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (null != rs)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        if (null != ps) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void show() throws SQLException {
        while (rs.next()){
            int i = 1;
            while (rs.getMetaData().getColumnCount() >= i) {
                System.out.print(rs.getObject(i)+"\t");
                i++;
            }
            System.out.println();
        }
    }

    public static void main(String[] args) throws SQLException {
        getConn();

        System.out.println("原始数据");

        String querysql = "select * from student";
        query(querysql);
        show();

        String upsql1 = "update student set stuAddr =? where stuName=?";
        update(upsql1,"江西","李安");

        System.out.println("更新后");
        query(querysql);
        show();

        String insrtsql = "insert into student(stuId,stuName,gradeId,stuGender)values(?,?,?,?)";
        update(insrtsql,"10","宋慧乔","3","女");

        String desql = "delete from student where stuName = ?";
        update(desql,"宋慧乔");

        System.out.println("删除后");
        query(querysql);
        show();
    }
}

二、Statement和PreparedStatement区别

Statement由方法createStatement()创建，该对象用于发送简单的SQL语句

使用 Statement 对象。在对数据库只执行一次性存取的时侯，用 Statement 对象进行处理。PreparedStatement 对象的开销比Statement大，对于一次性操作并不会带来额外的好处。

PreparedStatement由方法prepareStatement()创建，该对象用于发送一个或多个输入参数的SQL语句

• SQL语句使用"?"作为数据占位符
• 使用setXXX() 方法设置数据

PreparedStatement比Statement具有更好的维护性和可读性
PreparedStatement—预编译
PreparedStatement可以阻止常见的SQL注入式攻击。【例如：or ‘1=1’】
PreparedStatement可以写动态查询语句
PreparedStatement可以写参数化查询，比Statement能获得更好的性能。
PreparedStatement，数据库可以使用已经编译过及定义好的执行计划，这种预处理语句查询比普通的查询运行速度更快。