JDBC学习

一、JDBC是什么

JDBC是java数据库连接技术(Java Database Connectivity),能实现java程序对各种数据库的访问
JDBC由一组使用java语言编写的类和接口(JDBC API)组成,它们位于java.sql和jkavax.sql中

二、JDBC连接数据库步骤

1.Class.forName加载驱动
2.DiverManager获取Connection对象
3.创建Statement执行SQL语句
4.返回resultset查询结果
5.释放资源

三、配置JDBC

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.29</version>
</dependency>

四.JDBC代码演示

加载驱动,获取connection对象

        Connection connection = null;  //将connection对象体现创建在外面
        PreparedStatement preparedStatement = null;  //将preparedStatement对象体现创建在外面
        ResultSet resultSet = null;  //将resultSet对象体现创建在外面
        try {
            //第一步:加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //第二步:获取connection对象
            connection = DriverManager.getConnection(
                    "jdbc:mysql://192.168.153.142:3306/jdbcstudb", 
                    //mysql服务器地址,端口,数据库名
                    "root", //数据库的用户名 root
                    "root" //数据库的密码 root
            );
            System.out.println(connection);

JDBC新增操作及关闭资源

		String sql = "insert into dog(name,health,love,strain,lytm) value(?,?,?,?,now())";
            String name = "旺财";
            Integer health = 100;
            Integer love = 68;
            String strain = "中华田园犬";
            preparedStatement = connection.prepareStatement(sql);
            //对PreparedStatement塞值
            preparedStatement.setString(1, name);
            preparedStatement.setInt(2, health);
            preparedStatement.setInt(3, love);
            preparedStatement.setString(4, strain);
            int num = preparedStatement.executeUpdate();
            if (num > 0) {
                System.out.println("新增宠物狗成功!");
            }} catch (ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            //finally里面释放资源
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

运行结果(修改值多插入几条数据):

JDBC修改操作及关闭资源

	String sql = "update dog set name=?,health=?,love=? where id=?";
            String name = "泰日天";
            Integer health = 100;
            Integer love = 100;
            Integer id = 2;
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            preparedStatement.setInt(2,health);
            preparedStatement.setInt(3,love);
            preparedStatement.setInt(4,id);
            int num = preparedStatement.executeUpdate();
            if(num>0){
                System.out.println("修改成功!");
            }finally {
            //finally里面释放资源
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

运行结果:

JDBC删除操作及关闭资源

	String sql = "delete from dog where name = ? and health = ?";
            preparedStatement = connection.prepareStatement(sql);
            String name = "大黑子";
            Integer health = 78;
            preparedStatement.setString(1,name);
            preparedStatement.setInt(2,health);
            int num=preparedStatement.executeUpdate();
            if (num>0){
                System.out.println("删除成功!");
            }finally {
            //finally里面释放资源
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

运行结果:

JDBC查询操作及关闭资源

	String sql = "select id,name,health,love,strain,lytm from dog where health=? and strain=? limit ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 100);
            preparedStatement.setString(2, "中华田园犬");
            preparedStatement.setInt(3, 0);
            preparedStatement.setInt(4, 2);
            resultSet = preparedStatement.executeQuery();
            System.out.println("\t\t狗狗信息");
            System.out.println("编号\t名字\t健康值\t亲密度\t品种\t领养时间");
            while (resultSet.next()) {
                System.out.print(resultSet.getInt("id") + "\t");
                System.out.print(resultSet.getString("name") + "\t");
                System.out.print(resultSet.getInt("health") + "\t");
                System.out.print(resultSet.getInt("love") + "\t");
                System.out.print(resultSet.getString("strain") + "\t");
                System.out.println(resultSet.getDate("lytm") + "\t");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //finally里面释放资源
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

运行结果

---