一.查询数据库里所有记录
数据库信息如下:
1.导入druid的jar包
2.配置druid.properties文件,连接到哪个数据库、获取连接时最长等待时间均在里面设置
driverClassName=com.mysql.jdbc.Driver
#URL连接数据库的URL,其中travel(以下面例子来说)为连接的数据库,后面的参数可不改但不删
url=jdbc:mysql://localhost:3306/test111?useSSL=false&useServerPrepStmts=true
characterEncoding=utf-8
#安装mysql时候设置的用户与密码
username=root
password=admin
#初始化物理连接的个数
initialSize=5
#最大连接池数量
maxActive=10
#获取连接时最大等待时间
maxWait=3000
3.定义Brand类
package pojo;
/*
品牌
alt+鼠标左键:整列编辑
在实体类中基本数据类型建议使用其对应的包装类型
*/
public class Brand {
private Integer id;//主键
private String brandName;//品牌名称
private String companyName;//企业名称
private Integer ordered;//排序字段
private String description;//描述信息
private Integer status=null;//状态: 0:禁用 1:启用 不用int是因为int默认值是0,表示状态时有歧义
//右键Generator,选择Gettr and Setter后 command+A快捷键全选补全代码
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;
}
//右键Generator,选择toString()
@Override
public String toString() {
return "Brand{" +
"id=" + id +
", brandName='" + brandName + '\'' +
", companyName='" + companyName + '\'' +
", ordered=" + ordered +
", description='" + description + '\'' +
", status=" + status +
'}';
}
}
4.查询数据库所有记录
package example;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.jupiter.api.Test;
import pojo.Brand;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Properties;
/*
品牌数据的增删改查操作
*/
public class BrandTest {
/*
查询数据库tb_brand里的所有信息
1.SQL:select * from tb_brand;
2.参数:不需要
3.结果:List<Brand>
*/
@Test
public void test111SelectAll() throws Exception {
//1.加载配置文件
Properties prop= new Properties();
//name后跟的是druid.properties的绝对路径
prop.load(new FileInputStream("/Users/apple/Downloads/jdbc1/jdbc-demo/src/druid.properties"));
//获取连接池对象
//写完DruidDataSourceFactory.createDataSource(prop);后alt加回车补全代码
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//2.获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//3.定义SQL语句
//选取哪个数据库到druid.properties配置文件里改
String sql = "select * from tb_brand";
//4.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//5.设置参数(本例不需要)
//6.执行sql
ResultSet rs = pstmt.executeQuery();
//7.处理结果 List<Brand> 一行行数据分别封装成Brand对象,装载List集合
Brand brand = new Brand();
ArrayList<Brand> brands = new ArrayList<Brand>();
while(rs.next()){
//获取数据,用rs.getXxx()方法
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是之前写好的类
brand.setId(id);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setDescription(description);
brand.setOrdered(ordered);
brand.setStatus(status);
//装载集合,把brand对象添加进集合
brands.add(brand);
}
System.out.println(brands);
//7.释放资源
rs.close();
pstmt.close();
conn.close();
}
}
二.添加
id自增长,insert除id外的所有信息
/*
数据库添加信息
1.编写sql语句 insert into tb_table(brand_name,company_name,ordered,description,status) values(?,?,?,?,?))
2.是否需要参数? 需要:除id外的所有信息
3.返回结果如何封装:返回boolean类型
*/
@Test
public void test111Add() throws Exception{
//接收页面提交的参数(模拟)
String brandName= "香飘飘";
String companyName = "香飘飘有限公司";
int ordered = 70;//订单量
String description = "很好喝!";//商品描述
int status = 1;//商品状态
//1.加载配置文件
Properties prop= new Properties();
//name后跟的是druid.properties的绝对路径
prop.load(new FileInputStream("/Users/apple/Downloads/jdbc1/jdbc-demo/src/druid.properties"));
//获取连接池对象
//写完DruidDataSourceFactory.createDataSource(prop);后alt加回车不全代码
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//2.获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//3.定义sql语句
String sql = "insert into tb_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?)";
//4.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//5.设置参数
pstmt.setString(1,brandName);
pstmt.setString(2,companyName);
pstmt.setInt(3,ordered);
pstmt.setString(4,description);
pstmt.setInt(5,status);
//6.执行sql
int count = pstmt.executeUpdate();//用.executeUpdate()方法,返回一个count,即被影响的行数
//7.处理结果
System.out.println(count>0);
//8.释放资源
pstmt.close();
conn.close();
}
三.修改:根据id修改
/*
数据库修改信息
1.编写sql语句 update tb_brand set brand_name =?,company_name=?,ordered = ?,description = ?,status=? where id =?
2.是否需要参数? 需要:除id外的所有信息
3.返回结果如何封装:返回boolean类型
*/
@Test
public void test111Update() throws Exception{
//接收页面提交的参数(模拟)
String brandName= "奥利奥";
String companyName = "奥利奥有限公司";
int ordered = 50;//订单量
String description = "好恰!";//商品描述
int status = 1;//商品状态
int id = 3;
//1.加载配置文件
Properties prop= new Properties();
//name后跟的是druid.properties的绝对路径
prop.load(new FileInputStream("/Users/apple/Downloads/jdbc1/jdbc-demo/src/druid.properties"));
//获取连接池对象
//写完DruidDataSourceFactory.createDataSource(prop);后alt加回车不全代码
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//2.获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//3.定义sql语句
String sql = "update tb_brand set brand_name =?,company_name=?,ordered = ?,description = ?,status=? where id =?";
//4.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//5.设置参数
pstmt.setString(1,brandName);
pstmt.setString(2,companyName);
pstmt.setInt(3,ordered);
pstmt.setString(4,description);
pstmt.setInt(5,status);
pstmt.setInt(6,id);
//6.执行sql
int count = pstmt.executeUpdate();//用.executeUpdate()方法,返回一个count,即被影响的行数
//7.处理结果
System.out.println(count>0);
//8.释放资源
pstmt.close();
conn.close();
}
四.删除
//接收页面提交的参数(模拟)
int id = 5;//删除id为5的记录
//1.加载配置文件
Properties prop= new Properties();
//name后跟的是druid.properties的绝对路径
prop.load(new FileInputStream("/Users/apple/Downloads/jdbc1/jdbc-demo/src/druid.properties"));
//获取连接池对象
//写完DruidDataSourceFactory.createDataSource(prop);后alt加回车不全代码
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//2.获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//3.定义sql语句
String sql = "delete from tb_brand where id=?";
//4.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//5.设置参数
pstmt.setInt(1,id);
//6.执行sql
int count = pstmt.executeUpdate();//用.executeUpdate()方法,返回一个count,即被影响的行数
//7.处理结果
System.out.println(count>0);
//8.释放资源
pstmt.close();
conn.close();