java实验6-jdbc

一.实验内容

创建一个名为 books 的数据库表，其结构如下：

id INT（主键，自动递增）

title VARCHAR（书名）

author VARCHAR（作者）

price DOUBLE（价格）：

对该表完成以下任务：

1、插入数据：

使用 JDBC 插入至少五本书的信息到 books 表。

2、查询数据：

使用 JDBC 查询并打印出所有价格大于50元且书名包含“python”（不区分大小写）的书籍的信息。

3、更新数据：

使用 JDBC 更新其中一本书的价格。

4、删除数据：

使用 JDBC 删除其中一本书。

创建一个maven项目

 找到pom.xml文件导包

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

建议自己配置下maven要不让需要等待很久。

导完后看下maven包有没有存在。

能看到这两个就行。

创建表

CREATE TABLE `books` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`title`  varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '书名' ,
`author`  varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '作者' ,
`price`  double NULL DEFAULT NULL COMMENT '价格' ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci
ROW_FORMAT=DYNAMIC
;

 创建pojo包---用于放实体

package com.wen.easyutils.jdbc;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class BooksPojo {
    private Integer id;
    private String title;
    private String author;
    private Double price;
}

 创建一个utils包用于放工具类

package com.wen.easyutils.Test;

import com.wen.easyutils.jdbc.BooksPojo;
import com.wen.easyutils.jdbc.Connector;

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

/**
 * Jdbc工具类
 */
public class JdbcUtils {
    private static String url = 'jdbc:mysql://localhost:3306/自己的数据库?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC';
    private static String name = '自己的用户名';
    private static String pwd = '自己的密码';

    static {
        try {
            //加载并注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取数据库连接对象
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, name, pwd);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }

    //关闭连接对象
    public static void closeConnection(Connection connection) {
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //提交事务
    public static void commit(Connection connection) {
        try {
            connection.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //事务回滚
    public static void rollback(Connection connection) {
        try {
            connection.rollback();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //关闭Statement对象
    public static void closeStatement(Statement statement) {
        try {
            statement.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //关闭ResultSet
    public static void closeResultSet(ResultSet resultSet) {
        try {
            resultSet.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //DML操作时关闭资源
    public static void closeResource(Statement statement, Connection connection) {
        //先关闭Statement对象
        closeStatement(statement);
        //在关闭Connection对象
        closeConnection(connection);
    }

    //查询时关闭资源
    public static void closeResource(ResultSet resultSet, Statement statement, Connection connection) {
        //先关闭ResultSet
        closeResultSet(resultSet);
        //在闭Statement对象
        closeStatement(statement);
        //最后关闭Connection对象
        closeConnection(connection);
    }

    /**
    * @description:查询所有
    * @author: cws
    * @date: 2023/12/15
    []
    * @return: void
    */
    public static void selectBooksAll() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            //获取数据库连接
            connection = JdbcUtils.getConnection();
            //创建PreparedStatement对象
            ps = connection.prepareStatement("select * from books");
            //执行查询
            resultSet = ps.executeQuery();
            //操作ResultSet对象获取查询的结果集
            while (resultSet.next()) {
                //获取列中的数据
                BooksPojo pojo = new BooksPojo();
                pojo.setId(Integer.valueOf(resultSet.getInt("id")));
                pojo.setAuthor(resultSet.getString("author"));
                pojo.setTitle(resultSet.getString("title"));
                pojo.setPrice(resultSet.getDouble("price"));
                System.out.println(pojo.getId() + " " + pojo.getAuthor() + " " + pojo.getTitle() + " " + pojo.getPrice());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(resultSet, ps, connection);
        }
    }


    /**
     * @description:添加数据
     * @author: cws
     * @date: 2023/12/15
     * []
     * @return: void
     */
    public static void Save(String sql) throws Exception {
        // 注册驱动，获取连接
        Connection connection = null;
        // 获取操作sql的Statement对象
        Statement statement = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            int i = statement.executeUpdate(sql);
            if (i > 0) {
                System.out.println("新增成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            JdbcUtils.closeResource(statement, connection);
        }
    }


    /**
     * @description:修改
     * @author: cws
     * @date: 2023/12/15
    [sql]
     * @return: void
     */
    public static void Update(String sql) throws Exception {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            // 操作sql语句，并获取结果集
            int i = statement.executeUpdate(sql);
            // 处理结果集
            if (i > 0) {
                System.out.println("更新成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            JdbcUtils.closeResource(statement, connection);
        }
    }


    /**
     * @description:删除
     * @author: cws
     * @date: 2023/12/15
    [sql]
     * @return: void
     */
    public static void Delete(String sql) throws Exception {
        // 注册驱动，获取连接
        Connection connection = null;
        // 获取操作sql的Statement对象
        Statement statement = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            // 操作sql语句，并获取结果集
            int i = statement.executeUpdate(sql);
            // 处理结果集
            if (i > 0) {
                System.out.println("删除成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            JdbcUtils.closeResource(statement, connection);
        }
    }


    /**
    * @description:根据id查询
    * @author: cws
    * @date: 2023/12/15
    [id]
    * @return: void
    */
    public static void getById(Integer id) throws Exception {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            //获取数据库连接
            connection = JdbcUtils.getConnection();
            //创建PreparedStatement对象
            ps = connection.prepareStatement("select * from books where id="+id);
            //执行查询
            resultSet = ps.executeQuery();
            //操作ResultSet对象获取查询的结果集
            while (resultSet.next()) {
                //获取列中的数据
                BooksPojo pojo = new BooksPojo();
                pojo.setId(Integer.valueOf(resultSet.getInt("id")));
                pojo.setAuthor(resultSet.getString("author"));
                pojo.setTitle(resultSet.getString("title"));
                pojo.setPrice(resultSet.getDouble("price"));
                System.out.println(pojo.getId() + " " + pojo.getAuthor() + " " + pojo.getTitle() + " " + pojo.getPrice());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(resultSet, ps, connection);
        }
    }

    /**
     * @description:自定yisql查询
     * @author: cws
     * @date: 2023/12/15
    [id]
     * @return: void
     */
    public static void SelectByMeSql(String sql) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            //获取数据库连接
            connection = JdbcUtils.getConnection();
            //创建PreparedStatement对象
            ps = connection.prepareStatement(sql);
            //执行查询
            resultSet = ps.executeQuery();
            //操作ResultSet对象获取查询的结果集
            while (resultSet.next()) {
                //获取列中的数据
                BooksPojo pojo = new BooksPojo();
                pojo.setId(Integer.valueOf(resultSet.getInt("id")));
                pojo.setAuthor(resultSet.getString("author"));
                pojo.setTitle(resultSet.getString("title"));
                pojo.setPrice(resultSet.getDouble("price"));
                System.out.println(pojo.getId() + " " + pojo.getAuthor() + " " + pojo.getTitle() + " " + pojo.getPrice());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeResource(resultSet, ps, connection);
        }
    }

}

创建测试 

package com.wen.easyutils.jdbc;

import com.wen.easyutils.Test.JdbcUtilsTow;

public class Test{


    public static void main(String[] args) throws Exception {
////        插入数据
        String sql1="insert into books values(7,'565656','zs',89.9)";
        String sql2="insert into books values(8,'4444','ws',59.9)";
        String [] arr={sql1,sql2};
        for (String sql : arr) {
            JdbcUtils.Save(sql);
        }

////        使用 JDBC 查询并打印出所有价格大于50元且书名包含“python”（不区分大小写）的书籍的信息。
//        String sql = "SELECT * from books where like "%pyhton%" or title like like "%PYTHON%"  and price>50";
//        JdbcUtils.SelectByMeSql(sql);

//        String sql ="UPDATE books set author='lisi' where id =1";
//        JdbcUtils.Update(sql);

//        String sql ="delete from books where id = 5";
//        JdbcUtils.Delete(sql);


    }


}