java原生JDBC

首先创建一个新的Object
然后创建一个libs的包
根据你数据库的版本导入mysql.jar包

然后右键点这个ok即可

创建一个数据库

然后直接上代码

public class JdbcDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //步骤
        //1  获取数据库连接的URL mysql8 必须要给一个时区  serverTimezone=UTC
        String url="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC";
        //2 获取数据库连接的用户名和密码
        String username="root";
        String password="123456";
        //3 加载驱动
        /* String driver ="com.mysql.cj.jdbc.Driver";
        Class.forName(driver);*/
        Class.forName("com.mysql.cj.jdbc.Driver");
        //4 获取数据库连接对象  DriverManager依据数据库的不同，管理JDBC驱动
        Connection connection= DriverManager.getConnection(url,username,password);
        //5 获取操作数据库的Statement对象
        Statement statement=connection.createStatement();
        //6向数据库发送sql
        String sql="select * from user";
        //7 通过statement对象 发送查询请求 拿回结果集
        ResultSet resultSet = statement.executeQuery(sql);
        //8遍历结果集 做一系列操作
        while (resultSet.next()){
            Object id = resultSet.getObject("id");
            System.out.println(id);
            Object uname = resultSet.getObject("name");
            System.out.println(uname);
            Object pwd = resultSet.getObject("pwd");
            System.out.println(pwd);
            System.out.println("==============");
        }
        //关闭资源
        resultSet.close();
        statement.close();
        connection.close();

    }

}

把结果查出来

1
张三
123456
==============
2
李四
123456
==============
3
王五
123890
==============

Process finished with exit code 0

还有一种把结果集放在集合里输出
首先要有一个pojo User类，类中属性要跟数据库中列名一致

package jdbc01.pojo;

public class User {
    private Integer id;
    private String name;
    private String pwd;
    public  User(){}

    public User(Integer id, String name, String pwd) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}

package jdbc01.demo01;

import jdbc01.pojo.User;

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

public class JdbcDemo02 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //步骤：
        //1  获取数据库连接的URL mysql8 必须要给一个时区  serverTimezone=UTC
        String url = "jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC";
        //2 获取数据库连接的用户名和密码
        String username = "root";
        String password = "123456";
        //3 加载驱动
        String driver ="com.mysql.cj.jdbc.Driver";
        Class.forName(driver);
        //4 获取数据库连接对象
        Connection connection = DriverManager.getConnection(url, username, password);

        //5 获取操作数据库的Statement对象
        Statement statement = connection.createStatement();
        //6向数据库发送sql
        String sql = "select * from user";
        //7 通过statement对象 发送查询请求 拿回结果集
        ResultSet resultSet = statement.executeQuery(sql);
        //8遍历结果集 做一系列操作
        List<User> userList=new ArrayList<>();
        while (resultSet.next()){
            User user=new User();
            Integer id = (Integer) resultSet.getObject("id");
            String name=(String) resultSet.getObject("name");
            String pwd=(String) resultSet.getObject("pwd");
            user.setId(id);
            user.setName(name);
            user.setPwd(pwd);
            userList.add(user);
        }
        //关闭资源
        resultSet.close();
        statement.close();
        connection.close();
        System.out.println(userList);
    }
}

结果为

[User{id=1, name='张三', pwd='123456'}, User{id=2, name='李四', pwd='123456'}, User{id=3, name='王五', pwd='123890'}]

Process finished with exit code 0

实现mysql增删改（自己运行看看）

package jdbc01.demo01;

import java.sql.*;

public class JdbcDemo03 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String url="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC";
        String username="root";
        String password="123456";
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url,username,password);
        Statement statement = connection.createStatement();
        //String sql="insert into user (id,name,pwd) values(4,'啦啦啦','123444')";
        //String sql="UPDATE user set name='嘎嘎' where id=4";
        String sql="delete from  user where  id=4";
        int i = statement.executeUpdate(sql);//返回int类型表示被影响行数 大于0true
        if (i>0){
            System.out.println("完成");
        }
        //关闭资源
        statement.close();
        connection.close();
    }
}

因为反复写配置很麻烦，所以我们把他封装在一个类中

package jdbc01.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtil {
    private static String url=null;
    private static String username=null;
    private static String password=null;
    private static String driver=null;
    static {
        try {
            InputStream is=JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(is);//把流读进来
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            driver = properties.getProperty("driver");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public  static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
    public static void  closeAll(ResultSet rs , Statement st, PreparedStatement ps , Connection coon){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(ps!=null){
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(coon!=null){
            try {
                coon.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            }
    }
}

封装完成后再进行查询，可以发现代码简洁了很多

package jdbc01.demo01;

import jdbc01.utils.JdbcUtil;

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

public class JdbcDemo04 {
    public static void main(String[] args) {
        Connection connection=null;
        Statement st=null;
        ResultSet rs=null;

        try {
            connection=JdbcUtil.getConnection();
            st = connection.createStatement();
            rs = st.executeQuery("select * from user");
            while (rs.next()){
                System.out.println(rs.getObject("id"));
                System.out.println(rs.getObject("name"));
                System.out.println(rs.getObject("pwd"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        JdbcUtil.closeAll(rs,st,null,connection);
    }
}

之后进行测试发现使用st拼接sql存在数据库不安全现象于是我们改用PreparedStatement

使用 PreparedStatement 预先准备好sql 进行预编译 ，提前准备 sql 如果sql中带有参数 用问号 占位

package jdbc01.demo01;

import jdbc01.utils.JdbcUtil;

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

public class JdbcDemo06 {
    public static void main(String[] args) {
        Connection connection = null;
        //ps需要 预先准备好sql 进行预编译
        PreparedStatement ps=null;
        ResultSet rs = null;

        try {
            connection= JdbcUtil.getConnection();
            //提前准备 sql   如果sql中带有参数 用问号 占位
            String sql  = "select *  from user where name = ?";
            ps = connection.prepareStatement(sql);
            //把问号进行处理
            ps.setString(1,"张三");
            rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getObject("id"));
                System.out.println(rs.getObject("name"));
                System.out.println(rs.getObject("pwd"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        JdbcUtil.closeAll(rs,null,ps,connection);
    }
}

运行后可以发现安全问题解决

1
张三
123456

Process finished with exit code 0

后来又用 PreparedStatement实现增删改(结果自己运行查看)理解?号在代码中的作用
还有如何给问号赋值

package jdbc01.demo01;

import jdbc01.utils.JdbcUtil;

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

public class JdbcDemo07 {
    public static void main(String[] args) {
        Connection connection = null;
        //ps需要 预先准备好sql 进行预编译
        PreparedStatement ps=null;
        try {
            connection = JdbcUtil.getConnection();
            //提前准备 sql   如果sql中带有参数 用问号 占位
           // String sql  = "insert into user(id,name,pwd) values(?,?,?)";
            //String sql="UPDATE user  set name=? where id=?";
            String sql="delete  from  user where id=?";
            ps = connection.prepareStatement(sql);
            //把问号进行处理
           // ps.setString(1,"大大");//第二个问号赋值
            ps.setInt(1,4);//第一个问号赋值4
            //ps.setString(3,"888544");//第三个问号赋值
            ps.executeUpdate();
            System.out.println("运行成功");

        } catch ( SQLException throwables) {
            throwables.printStackTrace();
        }
        JdbcUtil.closeAll(null,null,ps,connection);
    }
}