1.实体类
package pojo;
public class Goods {
private int id; //商品编号
private String name;//商品名称
private float price;//商品价格
private int num;//商品数量
private int gid;//商品类型
public Goods() {
// TODO Auto-generated constructor stub
}
public Goods(int id, String name, float price, int num, int gid) {
super();
this.id = id;
this.name = name;
this.price = price;
this.num = num;
this.gid = gid;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public int getGid() {
return gid;
}
public void setGid(int gid) {
this.gid = gid;
}
}
2.工具类编写
package util;
import java.sql.*;
public class BaseDao {
public Connection cs=null;//数据库连接
public PreparedStatement ps=null;//SQL语句命令执行操作
//打开数据库连接方法
public void dakai(){
try {
Class.forName("com.mysql.jdbc.Driver");
cs= DriverManager.getConnection("jdbc:mysql://localhost:3306/db613","root","root");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//通用增删改的方法
public int aud(String sql,Object[]obj){//在这里并不会知道增删改是哪一个? 后面 Object[]obj 指的是有可能的参数
int row=0;
dakai();//父类的方法直接用
try {
ps= cs.prepareStatement(sql);
if(obj!=null){
for (int i = 0; i < obj.length; i++) {
ps.setObject((i+1),obj[i]); //Xxx指的是对应数据表 字段类型 对应变量 指的 对与预编译问号占位
}
}
row = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return row;
}
}
3.编写 dao
package dao;
import pojo.Goods;
public interface GoodsDao {
//查询全部
public void getAll();
//查询指定
public void getById(Goods g);
//添加商品
public void add(Goods g);
//删除一条商品信息
public void del(Goods g);
//修改一条商品信息
public void up(Goods g);
}
4.编写dao实现类
package dao.impl;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import pojo.Goods;
import util.BaseDao;
import dao.GoodsDao;
public class GoodsDaoImpl extends BaseDao implements GoodsDao {
//实现查询全部
public void getAll() {
dakai();
try {
String sql="select * from goods";
PreparedStatement ps = cs.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//实现查询指定
public void getById(Goods g) {
dakai();
try {
String sql="select * from goods where `goods_id`=?";
PreparedStatement ps = cs.prepareStatement(sql);
ps.setInt(1, g.getId());
ResultSet rs = ps.executeQuery();
if (rs.next()) {
System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//实现修改
public void up(Goods g) {
String sql="update `goods` set `goods_name`=?,`goods_price`=?,`goods_nums`=?,`gt_id`=? where `goods_id`=?";
Object[] o={g.getName(),g.getPrice(),g.getNum(),g.getGid(),g.getId()};
int r= aud(sql,o);
if(r>0){
System.out.println("修改成功!");
}
}
//实现商品信息添加
public void add(Goods g) {
String sql="INSERT goods VALUES (?,?,?,?,?)";
Object[] o={g.getId(),g.getName(),g.getPrice(),g.getNum(),g.getGid()};
int r= aud(sql,o);
if(r>0){
System.out.println("添加成功!");
}
}
//实现删除
public void del(Goods g) {
String sql="delete from goods where `goods_id`=?";
Object[] o={g.getId()};
int r= aud(sql,o);
if(r>0){
System.out.println("删除成功!");
}
}
}
5.编写测试类
package test;
import dao.GoodsDao;
import dao.impl.GoodsDaoImpl;
import pojo.Goods;
public class Test {
public static void main(String[] args) {
Goods g = new Goods();
g.setId(103);
GoodsDao gd = new GoodsDaoImpl();
//gd.getAll();
gd.getById(g);
/* Goods g = new Goods();
g.setId(107);
g.setName("青岛");
g.setPrice(4);
g.setNum(80);
g.setGid(2);//这个是外键 不能随便给值 只能给 提供
GoodsDao gd
= new GoodsDaoImpl();
gd.up(g);*
/*Goods g = new Goods();
g.setId(111);
g.setName("青岛");
g.setPrice(4);
g.setNum(20);
g.setGid(2);//这个是外键 不能随便给值 只能给 提供
GoodsDao gd = new GoodsDaoImpl();
gd.add(g);*/
/*Goods g = new Goods();
g.setId(111);
GoodsDao gd = new GoodsDaoImpl();
gd.del(g);*/
}
}
数据库执行脚本 可直接执行
/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 5.7.22-log : Database - db613
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`db613` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `db613`;
/*Table structure for table `goods` */
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
`goods_id` int(6) NOT NULL,
`goods_name` varchar(50) DEFAULT NULL,
`goods_price` float DEFAULT NULL,
`goods_nums` int(10) DEFAULT NULL,
`gt_id` int(4) DEFAULT NULL,
PRIMARY KEY (`goods_id`),
KEY `rf33` (`gt_id`),
CONSTRAINT `rf33` FOREIGN KEY (`gt_id`) REFERENCES `gt` (`gt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `goods` */
insert into `goods`(`goods_id`,`goods_name`,`goods_price`,`goods_nums`,`gt_id`) values (102,'哇哈哈',2,128,3),(103,'红牛',6,55,1),(104,'百岁山',3,261,3),(106,'冰红茶',2.5,73,1),(107,'??',4,80,2);
/*Table structure for table `gt` */
DROP TABLE IF EXISTS `gt`;
CREATE TABLE `gt` (
`gt_id` int(4) NOT NULL,
`gt_name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`gt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `gt` */
insert into `gt`(`gt_id`,`gt_name`) values (1,'饮料'),(2,'啤酒'),(3,'矿泉水');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;