JDBC链接MySQL，实现对Goods表的增删改查并封装JDBC

项目目录结构

数据库配置

1.创建goods表

2.创建goods实体

package homework.MyJDBC;

public class Goods {
    private int id;
    private String gId;
    private String gName;
    private float gPrice;
    private int gNum;

    public int getId() {
        return id;
    }

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

    public String getgId() {
        return gId;
    }

    public void setgId(String gId) {
        this.gId = gId;
    }

    public String getgName() {
        return gName;
    }

    public void setgName(String gName) {
        this.gName = gName;
    }

    public float getgPrice() {
        return gPrice;
    }

    public void setgPrice(float gPrice) {
        this.gPrice = gPrice;
    }

    public int getgNum() {
        return gNum;
    }

    public void setgNum(int gNum) {
        this.gNum = gNum;
    }

    @Override
    public String toString() {
        return "Goods{" +
                "id=" + id +
                ", gId='" + gId + '\'' +
                ", gName='" + gName + '\'' +
                ", gPrice=" + gPrice +
                ", gNum=" + gNum +
                '}';
    }
}

3.创建接口GoodsDao

package homework.MyJDBC;

import java.sql.SQLException;
import java.util.List;

public interface GoodsDao {
    //增加
    public abstract void addGoods(Goods goods);

    //删除
    public abstract void delGoods(int id);

    //修改
    public abstract void updateGoods(Goods goods);

    //查询所有
    public abstract List<Goods> selectAllGoods() throws SQLException;

    //按照id查询
    public abstract List<Goods> selectGoodsById(int id) throws SQLException;
}

4.创建实现类工具包 MyJDBCUtil

package homework.MyJDBC;

import com.mysql.jdbc.Connection;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class MyJDBCUtil {
    private static Properties properties = new Properties();
    static {
        //1.注册驱动  告诉jdbc我们使用哪一个数据库厂商的驱动
        try {
            //1.1 读取文件中的信息
            FileInputStream in = null;
            try {
                in = new FileInputStream("src/homework/MyJDBC/resource/properties");
            } catch (FileNotFoundException e) {
                throw new RuntimeException(e);
            }
            // 1.2  Properties对象中有一个load方法
            properties.load(in);  //将文件相关的信息加载到properties 对象中
            Class.forName(properties.getProperty("driverClassName"));
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public static Connection GetConnection()  {

        //2.通过驱动管理器获取一个链接
        Connection connection = null;
        try {
            connection = (Connection) DriverManager.getConnection(properties.getProperty("url"),
                    properties.getProperty("username"), properties.getProperty("password"));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return  connection;
    }
    public static void release(Connection conn, Statement sm , ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs =null; //让jvm来回收它
        }
        if(sm!=null){
            try {
                sm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            sm =null; //让jvm来回收它
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn =null; //让jvm来回收它
        }
    }


}

5.创建实现类 ImplGoods

package homework.MyJDBC;

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

public class ImplGoods implements GoodsDao {
    @Override
    public void addGoods(Goods goods) {
        //获取conn
        Connection conn = MyJDBCUtil.GetConnection();

        Statement st = null;


        String sql = "INSERT INTO goods VALUES (NULL,'"+goods.getgId()+"','"+goods.getgName()+"',"+goods.getgPrice()+","+goods.getgNum()+")";
        System.out.println(sql);
        try {
            st = conn.createStatement();
            int result = st.executeUpdate(sql);
            if (result>0){
                System.out.println("添加成功!");
            }else {
                System.out.println("添加失败！");
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


    }

    @Override
    public void delGoods(int id) {
        //获取conn
        Connection conn = MyJDBCUtil.GetConnection();

        Statement st = null;


        String sql = "DELETE FROM goods WHERE id="+id;
        System.out.println(sql);
        try {
            st = conn.createStatement();
            int result = st.executeUpdate(sql);
            if (result>0){
                System.out.println(id+"商品删除成功!");
            }else {
                System.out.println(id+"商品删除失败！");
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void updateGoods(Goods goods) {
        //获取conn
        Connection conn = MyJDBCUtil.GetConnection();

        Statement st = null;


        String sql = "UPDATE goods SET g_id ='"+ goods.getgId()+"',g_name='"+goods.getgName()+"',g_price="+goods.getgPrice()+",g_num="+goods.getgNum()+ " WHERE id ="+goods.getId();
        System.out.println(sql);
        try {
            st = conn.createStatement();
            int result = st.executeUpdate(sql);
            if (result>0){
                System.out.println("修改成功!");
            }else {
                System.out.println("修改失败！");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public List<Goods> selectAllGoods() throws SQLException {
        com.mysql.jdbc.Connection connection = MyJDBCUtil.GetConnection();
        //3.创建向数据库发送sql的statement对象
        Statement st = connection.createStatement();
        //4.发送sql后获得一个封装了查询结果的ResultSet对象
        ResultSet rs = st.executeQuery("select * from goods");
        List<Goods> list = new ArrayList<Goods>();
        while(rs.next()){
            Goods goods = new Goods();
            goods.setId(rs.getInt("id"));
            goods.setgId(rs.getString("g_id"));
            goods.setgName(rs.getString("g_name"));
            goods.setgPrice(rs.getInt("g_price"));
            goods.setgNum(rs.getInt("g_num"));
            list.add(goods);
        }
        //释放资源
        MyJDBCUtil.release(connection,st,rs);
        return list;
    }

    @Override
    public List<Goods> selectGoodsById(int id) throws SQLException {

        com.mysql.jdbc.Connection connection = MyJDBCUtil.GetConnection();
        //3.创建向数据库发送sql的statement对象
        Statement st = connection.createStatement();
        //4.发送sql后获得一个封装了查询结果的ResultSet对象
        ResultSet rs = st.executeQuery("select * from goods where id="+id);
        List list = new ArrayList();
        while(rs.next()){
            Goods goods = new Goods();
            goods.setId(rs.getInt("id"));
            goods.setgId(rs.getString("g_id"));
            goods.setgName(rs.getString("g_name"));
            goods.setgPrice(rs.getInt("g_price"));
            goods.setgNum(rs.getInt("g_num"));
            list.add(goods);
        }
        //释放资源
        MyJDBCUtil.release(connection,st,rs);
        return list;
    }
}

6.创建测试类 test

package homework.MyJDBC;

import java.sql.SQLException;

public class test {
    public static void main(String[] args) throws SQLException {
        Goods goods = new Goods();
        goods.setId(1);
        goods.setgId("001");
        goods.setgName("qwe");
        goods.setgPrice(1);
        goods.setgNum(1);
        ImplGoods implGoods = new ImplGoods();
//        implGoods.addGoods(goods);
          implGoods.delGoods(6);

//        implGoods.updateGoods(goods);
//        System.out.println(implGoods.selectAllGoods());


        System.out.println(implGoods.selectGoodsById(1));
    }
}