目录
SQL脚本
-- 创建brand表
create table 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 brand (brand_name, company_name, ordered, description, status)
values ('VIVO', '维沃移动通信有限公司', 200, 'V梦想,不放手。 敢让全世界成为我的舞台', 0),
('华为', '华为技术有限公司', 100, '华为致力于把数字世界带入每个人、每个家庭、每个组织,构建万物互联的智能世界', 1),
('小米', '小米科技有限公司', 50, 'are you ok', 1);
实体类 Brand代码
package brand;
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 Brand() {
}
public Brand(Integer id, String brandName, String companyName, Integer ordered, String description, Integer status) {
this.id = id;
this.brandName = brandName;
this.companyName = companyName;
this.ordered = ordered;
this.description = description;
this.status = status;
}
/**
* 获取
* @return id
*/
public Integer getId() {
return id;
}
/**
* 设置
* @param id
*/
public void setId(Integer id) {
this.id = id;
}
/**
* 获取
* @return brandName
*/
public String getBrandName() {
return brandName;
}
/**
* 设置
* @param brandName
*/
public void setBrandName(String brandName) {
this.brandName = brandName;
}
/**
* 获取
* @return companyName
*/
public String getCompanyName() {
return companyName;
}
/**
* 设置
* @param companyName
*/
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
/**
* 获取
* @return ordered
*/
public Integer getOrdered() {
return ordered;
}
/**
* 设置
* @param ordered
*/
public void setOrdered(Integer ordered) {
this.ordered = ordered;
}
/**
* 获取
* @return description
*/
public String getDescription() {
return description;
}
/**
* 设置
* @param description
*/
public void setDescription(String description) {
this.description = description;
}
/**
* 获取
* @return status
*/
public Integer getStatus() {
return status;
}
/**
* 设置
* @param status
*/
public void setStatus(Integer status) {
this.status = status;
}
public String toString() {
return "Brand{id = " + id + ", brandName = " + brandName + ", companyName = " + companyName + ", ordered = " + ordered + ", description = " + description + ", status = " + status + "}";
}
}
查询所有数据
import com.alibaba.druid.pool.DruidDataSourceFactory;
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.Properties;
public class BrandTest {
public static void main(String[] args) throws Exception {
//1.导入jar包
//2.定义配置文件
//3.加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("E:\\Java\\jdbc\\src\\druid.properties"));
//4.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//5.获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//定义sql
String sql = "select * from brand";
//获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//设置参数
//执行sql
ResultSet rs = pstmt.executeQuery();
//处理结果 List<Brand> 封装Brand对象,装载List集合
Brand brand = null;
//创建集合对象
ArrayList<Brand> list = new ArrayList<>();
while (rs.next()) {
//获取数据
int id = rs.getInt("id");
String brandName = rs.getString("brand_name");
String companyName = rs.getString("company_name");
int ordered = rs.getInt("ordered");
String description = rs.getString("description");
int status = rs.getInt("status");
//封装Brand对象
brand = new Brand();
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);
//释放资源
rs.close();
pstmt.close();
conn.close();
}
}
结果
添加数据
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;
public class BrandAdd {
public static void main(String[] args) throws Exception {
//接收页面提交的参数
String brandName = "OPPO";
String companyName = "OPPO广东移动通信有限公司";
int ordered = 1;
String description="前后2000万,拍照更清晰";
int status =1;
//1.导入jar包
//2.定义配置文件
//3.加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("E:\\Java\\jdbc\\src\\druid.properties"));
//4.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//5.获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//定义sql
String sql = "insert into brand(brand_name, company_name, ordered, description, status) values (?,?,?,?,?)";
//获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//设置参数
pstmt.setString(1,brandName);
pstmt.setString(2,companyName);
pstmt.setInt(3,ordered);
pstmt.setString(4,description);
pstmt.setInt(5,status);
//执行sql
int count = pstmt.executeUpdate(); //影响行数
//处理结果
System.out.println(count>0);
//释放资源
pstmt.close();
conn.close();
}
}
结果
true
添加数据注意点:id是自增,所以添加数据的时候不需要加id
修改数据
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;
public class BrandUpdate {
public static void main(String[] args) throws Exception {
//接收页面提交的参数
String brandName = "OPPO";
String companyName = "OPPO广东移动通信有限公司";
int ordered = 100000;
String description = "前后2000万,拍照更清晰";
int status = 1;
int id =4;
//1.导入jar包
//2.定义配置文件
//3.加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("E:\\Java\\jdbc\\src\\druid.properties"));
//4.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//5.获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//定义sql
String sql = "update brand\n" +
"set brand_name=?,\n" +
" company_name=?,\n" +
" ordered=?,\n" +
" description=?,\n" +
" status=? where id= ?;";
//获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//设置参数
pstmt.setString(1, brandName);
pstmt.setString(2, companyName);
pstmt.setInt(3, ordered);
pstmt.setString(4, description);
pstmt.setInt(5, status);
pstmt.setInt(6, id);
//执行sql
int count = pstmt.executeUpdate(); //影响行数
//处理结果
System.out.println(count > 0);
//释放资源
pstmt.close();
conn.close();
}
}
结果
true
修改数据注意点:修改数据时,所给的参数要与下面的SQL语句相对应
删除数据
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;
public class BrandDelete {
public static void main(String[] args) throws Exception {
//接收页面提交的参数
int id =4;
//1.导入jar包
//2.定义配置文件
//3.加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("E:\\Java\\jdbc\\src\\druid.properties"));
//4.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//5.获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//定义sql
String sql = "delete from brand where id=?";
//获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//设置参数
pstmt.setInt(1, id);
//执行sql
int count = pstmt.executeUpdate(); //影响行数
//处理结果
System.out.println(count > 0);
//释放资源
pstmt.close();
conn.close();
}
}
结果
true