java JDBC编程

加载驱动.

访问数据库需要利用第三方类,这些第三方类都被压缩到一个.jar文件里,这些例如例子中的MySQL数据库的com.mysql.cj.jdbc.Driver包,这些包可以在网上下载然后配置在项目中.,配置成功后加载驱动然后连接数据库.
例如:

try {
        Class.forName("com.mysql.jdbc.Driver");		
        } catch (ClassNotFoundException e) { 				
            e.printStackTrace();
        }
复制代码

建立jdbc和数据库之间的连接: 例子中的url为数据库ip,例子中为本地数据库. username为用户名,password为用户密码.

String url = "jdbc:mysql://localhost:3306/studentsdb?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=false";//?serverTimezone=UTC
String username = "root";
String password = "*******";
coon = DriverManager.getConnection(url, username, password);
复制代码

编写sql语句:

需要注意的是,sql本质上是一个字符串,当调用函数构造sql时注意引号的位置.注意sql语句是否合法.(建议现在sql数据库中实验一遍.)

String sql = "insert into tb_user(url,username,password) values ('0002','王五','123')";
复制代码

执行语句:

stmt.executeUpdate(sql);//执行语句.
复制代码

当sql语句为查询语句时需要接受查询结果时:

把结果集返回给集合ResultSet.

public static List<User> select(){//查询
    Statement stmt = null;
    Connection coon = null;
    ResultSet rs=null;
    List<User> users= new ArrayList<>();
    try {//加载驱动.
        Class.forName("com.mysql.cj.jdbc.Driver");
        //创建连接
        String url = "jdbc:mysql://localhost:3306/studentsdb?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=false";//?serverTimezone=UTC
        String username = "root";
        String password = "20010608csm";
        coon = DriverManager.getConnection(url, username, password);
        //创建stament对象,用于发送sql
        stmt = coon.createStatement();
        String sql = "select * from tb_user ";
        rs=stmt.executeQuery(sql);//执行语句,该函数与之前相比有返回值.
        while(rs.next()){
            User user=new User();
           user.setId(rs.getString("url"));
            user.setPassword(rs.getString("password"));
            user.setName(rs.getString("username"));
            users.add(user);
        }
        //stmt.executeUpdate(sql);
        //关闭链接
    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    } finally {
        try {if (rs!=null)
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {

            if (stmt != null)
                stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (coon != null)
                coon.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return users;
}
复制代码

释放资源:

在编码的过程中我们创建了Connection、ResultSet等资源，这些资源在使用完毕之后要进行关闭。关闭的过程中遵循从里到外的原则,先开后关,后开先关.

JDBC增删改查样例完整代码:

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

public class TestJDBC {
    public static void main(String[] args) {
//        add();
//        updata();
        List<User> users= new ArrayList<>();
        users=select();
        for (User user:users){
            System.out.println(user.toString());
        }
//        delete();

    }

    public static void add() {//增
        Statement stmt = null;
        Connection coon = null;
        try {//加载驱动.
            Class.forName("com.mysql.cj.jdbc.Driver");
            //创建连接
            String url = "jdbc:mysql://localhost:3306/studentsdb?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=false";//?serverTimezone=UTC
            String username = "root";
            String password = "20010608csm";
            coon = DriverManager.getConnection(url, username, password);
            //创建stament对象,用于发送sql
            stmt = coon.createStatement();//执行语句.
            String sql = "insert into tb_user(url,username,password) values ('0002','王五','123')";
            stmt.executeUpdate(sql);
            //关闭链接
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (coon != null)
                    coon.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void updata() {//改
        Statement stmt = null;
        Connection coon = null;
        try {//加载驱动.
            Class.forName("com.mysql.cj.jdbc.Driver");
            //创建连接
            String url = "jdbc:mysql://localhost:3306/studentsdb?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=false";//?serverTimezone=UTC
            String username = "root";
            String password = "20010608csm";
            coon = DriverManager.getConnection(url, username, password);
            //创建stament对象,用于发送sql
            stmt = coon.createStatement();
            String sql = "UPDATE tb_user SET username = '张三' WHERE  username = '李四' ";
            stmt.executeUpdate(sql);//执行语句.
            //关闭链接
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {

                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (coon != null)
                    coon.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static List<User> select(){//查询
        Statement stmt = null;
        Connection coon = null;
        ResultSet rs=null;
        List<User> users= new ArrayList<>();
        try {//加载驱动.
            Class.forName("com.mysql.cj.jdbc.Driver");
            //创建连接
            String url = "jdbc:mysql://localhost:3306/studentsdb?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=false";//?serverTimezone=UTC
            String username = "root";
            String password = "20010608csm";
            coon = DriverManager.getConnection(url, username, password);
            //创建stament对象,用于发送sql
            stmt = coon.createStatement();
            String sql = "select * from tb_user ";
            rs=stmt.executeQuery(sql);//执行语句,该函数与之前相比有返回值.
            while(rs.next()){
                User user=new User();
               user.setId(rs.getString("url"));
                user.setPassword(rs.getString("password"));
                user.setName(rs.getString("username"));
                users.add(user);
            }
            //stmt.executeUpdate(sql);
            //关闭链接
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {if (rs!=null)
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {

                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (coon != null)
                    coon.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return users;
    }

    public static void  delete(){//删
        Statement stmt = null;
        Connection coon = null;
        try {//加载驱动.
            Class.forName("com.mysql.cj.jdbc.Driver");
            //创建连接
            String url = "jdbc:mysql://localhost:3306/studentsdb?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=false";//?serverTimezone=UTC
            String username = "root";
            String password = "20010608csm";
            coon = DriverManager.getConnection(url, username, password);
            //创建stament对象,用于发送sql
            stmt = coon.createStatement();
            String sql = "DELETE  FROM tb_user where username = '李四' ";
            stmt.executeUpdate(sql);//执行语句.
            //关闭链接
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {

                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (coon != null)
                    coon.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
复制代码

运行截图: