java bean类
package com.itheima.pojo;
public class Brand {
// id 主键
private Integer id;
// -- 品牌名称
private String brandName;
// -- 企业名称
private String companyName;
// -- 排序字段
private Integer ordered;
//-- 描述信息
private String description;
// -- 状态:0:禁用 1:启用
private Integer status;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getBrandName() {
return brandName;
}
public void setBrandName(String brandName) {
this.brandName = brandName;
}
public String getCompanyName() {
return companyName;
}
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
public Integer getOrdered() {
return ordered;
}
public void setOrdered(Integer ordered) {
this.ordered = ordered;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
@Override
public String toString() {
return "Brand{" +
"id=" + id +
", brandName='" + brandName + '\'' +
", companyName='" + companyName + '\'' +
", ordered=" + ordered +
", description='" + description + '\'' +
", status=" + status +
'}';
}
}
实现类;查询数据
package com.itheima.example;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.itheima.pojo.Brand;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
@SuppressWarnings("all")
public class BrandTest {
//品牌数据的增删改查
@Test
public void testSelectAll() throws Exception {
Properties prop = new Properties();//创建对象
prop.load(new FileInputStream("src\\druid.properties"));//加载配置文件获取druidjar包下的properties对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);//获取连接池对象
Connection tion = dataSource.getConnection();//获取数据库连接 Connection
String sql = "select* from tb_brand";//查询tb_brand表下的所有数据
PreparedStatement pstm = tion.prepareStatement(sql);//获取PreparedStatement对象防止sql注入
ResultSet resultSet = pstm.executeQuery();//执行sql语句 因为是查询语句所以用executeQuery
List<Brand> list = new ArrayList<>();
while (resultSet.next()){//循环判断是否有值
// Brand brand = null;
int id = resultSet.getInt("id");
String brandName = resultSet.getString("brand_name");
String companyName = resultSet.getString("company_name");
int ordered = resultSet.getInt("ordered");
String description = resultSet.getString("description");
int status = resultSet.getInt("status");
Brand brand = new Brand();//封装brand对象撞到list集合中
brand.setId(id);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setOrdered(ordered);
brand.setDescription(description);
brand.setStatus(status);
list.add(brand);
}
System.out.println(list);
tion.close();
pstm.close();
resultSet.close();
}
}
实现类;根据id进行修改
package com.itheima.example;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;
@SuppressWarnings("all")
public class Brandupdate {
//修改数据
//sql语句
//参数 需要 根据id进行修改数据 需要所有的数据
//返回 结果(ture 或 falese);
@Test
public void testSelectUpdent() throws Exception {
//接收过来的页面传递的参数
String brandName = "香飘飘";
String companyName = "香飘飘";
int ordered =1000;
String description = "绕地球三圈";
int status = 1;
int id = 4;
Properties prop = new Properties();
prop.load(new FileInputStream("src\\druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection tion = dataSource.getConnection();
String sql = "update tb_brand\n" +
" set brand_name = ?,\n" +
" company_name= ?,\n" +
" ordered = ?,\n" +
" description = ?,\n" +
" status = ?\n" +
" where id = ?";
PreparedStatement pstm = tion.prepareStatement(sql);
pstm.setString(1,brandName);
pstm.setString(2,companyName);
pstm.setInt(3,ordered);
pstm.setString(4,description);
pstm.setInt(5,status);
pstm.setInt(6,id);
int count = pstm.executeUpdate();//影响的行数
System.out.println(count > 0);//给前端返回的值
tion.close();
pstm.close();
}
}
实现类;增加
package com.itheima.example;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.itheima.pojo.Brand;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
@SuppressWarnings("all")
public class BrandAdd {
//添加数据
//sql语句
//参数 需要 除了id之外的所有参数
//返回 结果(ture 或 falese);
@Test
public void testSelectAdd() throws Exception {
//接收过来的页面传递的参数
String brandName = "香飘飘";
String companyName = "香飘飘";
int ordered =1;
String description = "绕地球一圈";
int status = 1;
Properties prop = new Properties();
prop.load(new FileInputStream("src\\druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection tion = dataSource.getConnection();
String sql = "insert into tb_brand(brand_name,company_name,ordered,description,status)" +
"values (?,?,?,?,?)";
PreparedStatement pstm = tion.prepareStatement(sql);
pstm.setString(1,brandName);
pstm.setString(2,companyName);
pstm.setInt(3,ordered);
pstm.setString(4,description);
pstm.setInt(5,status);
int count = pstm.executeUpdate();//影响的行数
System.out.println(count > 0);//给前端返回的值
tion.close();
pstm.close();
}
}
实现类:根据id进行删除
package com.itheima.example;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;
@SuppressWarnings("all")
//删除数据
public class BrandDetel {
//sql语句
//参数 需要 根据id进行删除数据 需要所有的数据
//返回 结果(ture 或 falese);
@Test
public void testSelectUpdent() throws Exception {
//接收过来的页面传递的参数
int id = 4;
Properties prop = new Properties();
prop.load(new FileInputStream("src\\druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection tion = dataSource.getConnection();
String sql = "delete from tb_brand where id = ?";
PreparedStatement pstm = tion.prepareStatement(sql);
pstm.setInt(1,id);
int count = pstm.executeUpdate();//影响的行数
System.out.println(count > 0);//给前端返回的值
tion.close();
pstm.close();
}
}
sql脚本;
-- 删除tb_brand表
drop table if exists tb_brand;
-- 创建tb_brand表
create table tb_brand
(
-- id 主键
id int primary key auto_increment,
-- 品牌名称
brand_name varchar(20),
-- 企业名称
company_name varchar(20),
-- 排序字段
ordered int,
-- 描述信息
description varchar(100),
-- 状态:0:禁用 1:启用
status int
);
-- 添加数据
insert into tb_brand (brand_name, company_name, ordered, description, status)
values ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0),
('华为', '华为技术有限公司', 100, '华为致力于把数字世界带入每个人、每个家庭、每个组织,构建万物互联的智能世界', 1),
('小米', '小米科技有限公司', 50, 'are you ok', 1);
SELECT * FROM tb_brand;