JDBC基础

JDBC基础代码

使用JDBC连接数据库，并将查询到的信息使用集合保存。

JDBC步骤：
第⼀步：注册驱动程序
第⼆步: 请求连接
第三步: 获取执⾏sql语句的对象，发送给DBMS
第四步：返回结果集，程序员进⾏处理
第五步: 关闭连接操作

JDBC规范：
DriverManager:⽤于注册驱动
Connection: 表示与数据库创建的连接
Statement: 操作数据库sql语句的对象
ResultSet: 结果集或⼀张虚拟表

package day08;

import com.day08.javabean.Commodity;
import java.net.ConnectException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;

/**
 * @ Author     ：Mercury
 * @ Date       ：Created in 20:33 2022/2/23
 * @ Description：JDBC
 * @ Modified By：
 * @Version: $
 */
public class Demo01 {
    public static void main(String[] args) throws SQLException {

        //1 注册mysql的驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        //url：连接到指定的数据库的地址 jdbc:mysql://port/dbname
        //user：连接用户名
        //password：密码
        //2 连接数据库
        Connection connection = null;
        ResultSet resultSet = null;
        Statement statement = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/shoppingmall", "root", "root");
            //一个参数
            //connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/shoppingmall?user=root&passwprd=root");

            //3 建立小车并绑定sql语句
            statement = connection.createStatement();

            //定义sql语句
            String sql = "select * from commodityinfo";

            //4 接受结果
            resultSet = statement.executeQuery(sql);
            //存储数据
            List<Commodity> list = new ArrayList<>();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String spmc = resultSet.getString("spmc");
                String splb = resultSet.getString("splb");
                String xgxx = resultSet.getString("xgxx");
                String ghcs = resultSet.getString("ghcs");
                int kc = resultSet.getInt("kc");
                int jhj = resultSet.getInt("jhj");
                int xsj = resultSet.getInt("xsj");
                Commodity commodity = new Commodity(id, spmc, splb, xgxx, ghcs, kc, jhj, xsj);
                list.add(commodity);
            }
            System.out.println(list);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //5 关闭资源
            if(connection !=null){
                try{
                    connection.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(statement !=null){
                try{
                    statement.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(resultSet !=null){
                try{
                    resultSet.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
    }
}

commodity模型类，用于接收从数据库中查询到的信息。

package day08.javabean;

/**
 * @ Author     ：Mercury
 * @ Date       ：Created in 22:13 2022/2/23
 * @ Description：商品类
 * @ Modified By：
 * @Version: $
 */
public class Commodity {
        private int id;
        private String spmc;
        private String splb;
        private String xgxx;
        private String ghcs;
        private int kc;
        private int jhj;
        private int xsj;

        public int getId() {
            return id;
        }

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

        public String getSpmc() {
            return spmc;
        }

        public void setSpmc(String spmc) {
            this.spmc = spmc;
        }

        public String getSplb() {
            return splb;
        }

        public void setSplb(String splb) {
            this.splb = splb;
        }

        public String getXgxx() {
            return xgxx;
        }

        public void setXgxx(String xgxx) {
            this.xgxx = xgxx;
        }

        public String getGhcs() {
            return ghcs;
        }

        public void setGhcs(String ghcs) {
            this.ghcs = ghcs;
        }

        public int getKc() {
            return kc;
        }

        public void setKc(int kc) {
            this.kc = kc;
        }

        public int getJhj() {
            return jhj;
        }

        public void setJhj(int jhj) {
            this.jhj = jhj;
        }

        public int getXsj() {
            return xsj;
        }

        public void setXsj(int xsj) {
            this.xsj = xsj;
        }

        //构造方法
        public Commodity(int id,String spmc,String splb,String xgxx,String ghcs,int kc,int jhj,int xsj){
            this.id = id;
            this.spmc =spmc;
            this.splb =splb;
            this.ghcs =ghcs;
            this.xgxx = xgxx;
            this.jhj = jhj;
            this.kc = kc;
            this.xsj = xsj;
        }

        //重写输出方法
        @Override
        public String toString() {
            return "Commodity{" +
                    "id=" + id +
                    ", spmc='" + spmc + '\'' +
                    ", splb='" + splb + '\'' +
                    ", xgxx='" + xgxx + '\'' +
                    ", ghcs='" + ghcs + '\'' +
                    ", kc=" + kc +
                    ", jhj=" + jhj +
                    ", xsj=" + xsj +
                    '}';
        }
}