JDBC连接数据库增删改查实例
查询所有
- dao层代码：
package example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import fengzhuang.Brand;
public class BrandTest {
/**
* 1.sql语句：select * from tb_brand
* 2.参数：不需要
* 3.结果：List<Brand>
*
*/
@Test
public void testSelectAll() throws Exception {
//获取连接
String url = "jdbc:mysql://localhost:3306/db1";
String name = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, name, password);
//定义sql语句
String sql = "select * from tb_brand";
//获取对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//执行sql
ResultSet rs = pstmt.executeQuery();
//处理结果
Brand brand = null;
List<Brand> brands = 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("odered");
String description = rs.getString("description");
int status = rs.getInt("status");
//封装对象
brand = new Brand();
brand.setId(id);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setOrdered(ordered);
brand.setDescription(description);
brand.setStatus(status);
//放到集合
brands.add(brand);
}
System.out.println(brands);
//释放资源
rs.close();
pstmt.close();
conn.close();
}
}
- 数据类代码：
package fengzhuang;
public class Brand {
private Integer id;
private String brandName;
private String companyName;
private Integer ordered;
private String description;
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 + "]";
}
}
-
数据库表
数据库名为db1，表名为tb_brand，数据库结构如下图：
-
运行结果
目前主要是学习JDBC，所有代码都是junit测试来运行的，暂时还没有前端界面。
添加数据
- dao层代码
/**
* 1.SQL语句：insert into tb_brand(brand_name,company_name,odered,description,status) values(?,?,?,?,?);
* 2.参数：需要，除了id外所有信息
* 3.结果：boolean
*/
@Test
public void testAdd() throws Exception {
//接受页面提交的参数
String brandName = "香飘飘";
String companyName = "香飘飘公司";
int ordered = 1;
String description = "香的要飘飘";
int status = 1;
//1. 获取连接
String url = "jdbc:mysql://localhost:3306/db1";
String name = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, name, password);
//2. 定义SQL语句
String sql = "insert into tb_brand(brand_name,company_name,odered,description,status) values(?,?,?,?,?)";
//3. 获取对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//4. 设置参数
pstmt.setString(1, brandName);
pstmt.setString(2, companyName);
pstmt.setInt(3, ordered);
pstmt.setString(4, description);
pstmt.setInt(5, status);
//5. 执行SQL
int count = pstmt.executeUpdate();
//6. 处理结果
System.out.println(count > 0);
//7. 释放资源
pstmt.close();
conn.close();
}
-
数据类代码
与查找相同 -
数据库表结构
与查找相同 -
运行结果
修改
- dao层代码：
/**
* 1.SQL语句：update tb_brand set brand_name = ?,company_name = ?,odered = ?,description = ?,status = ? where id = ?
* 2.参数：需要，所有数据
* 3.结果：boolean
*/
@Test
public void testUpdate() throws Exception {
//接受页面提交的参数
String brandName = "香飘飘";
String companyName = "香飘飘公司";
int ordered = 1000;
String description = "直接起飞！";
int status = 1;
int id = 4;
//1. 获取连接
String url = "jdbc:mysql://localhost:3306/db1";
String name = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, name, password);
//2. 定义SQL语句
String sql = "update tb_brand set brand_name = ?,company_name = ?,odered = ?,description = ?,status = ? where id = ?";
//3. 获取对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//4. 设置参数
pstmt.setString(1, brandName);
pstmt.setString(2, companyName);
pstmt.setInt(3, ordered);
pstmt.setString(4, description);
pstmt.setInt(5, status);
pstmt.setInt(6, id);
//5. 执行SQL
int count = pstmt.executeUpdate();
//6. 处理结果
System.out.println(count > 0);
//7. 释放资源
pstmt.close();
conn.close();
}
-
数据类代码
与查询相同 -
数据库结构
与查询相同 -
运行结果
删除
- dao层代码
/**
* 1.SQL语句：delete from tb_brand where id = ?
* 2.参数：需要，id
* 3.结果：boolean
*/
@Test
public void testDeleteById() throws Exception {
//接受页面提交的参数
int id = 4;
//1. 获取连接
String url = "jdbc:mysql://localhost:3306/db1";
String name = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, name, password);
//2. 定义SQL语句
String sql = "delete from tb_brand where id = ?";
//3. 获取对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//4. 设置参数
pstmt.setInt(1, id);
//5. 执行SQL
int count = pstmt.executeUpdate();
//6. 处理结果
System.out.println(count > 0);
//7. 释放资源
pstmt.close();
conn.close();
}
-
数据类代码：
与查找相同 -
数据库表结构
与查找相同 -
运行结果
