package com.dept.main;
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 com.dept.pojo.Dept;
import com.dept.utils.DBUtil;
/**
* jdbc 增删改查
* 部门表:dept
* 字段:deptno,dname,loc(账号,名称,地区)
*
*/
public class DeptMain {
public static void main(String[] args) {
Dept dept=new Dept();
//跟新时需要id,插入时不需要id,因为id在数据库里是自增
dept.setDeptno(90);
dept.setDname("黑豹");
dept.setLoc("江南");
//新增,往表里添加数据
insert(dept);
//更新操作
update(dept);
//删除操作
delete(81);
//根据id查询单条记录
Dept oldDept = findById(10);
System.out.println(oldDept);
//查询列表
List<Dept> list=findlist("雪豹");
System.out.println(list);
}
private static List<Dept> findlist(String dname) {
String sql="SELECT DEPTNO,DNAME,LOC FROM dept WHERE dname like concat('%',?,'%')";
try {
Connection conn = DriverManager.getConnection(DBUtil.URL,DBUtil.USER,DBUtil.PASSWORD);
PreparedStatement stat=conn.prepareStatement(sql);
stat.setObject(1, dname);
ResultSet rs=stat.executeQuery();
List<Dept> depts=new ArrayList<>();
while(rs.next()) {
Dept dept=new Dept();
dept.setDeptno(rs.getInt("deptno"));
dept.setDname(rs.getString("dname"));
dept.setLoc(rs.getString("loc"));
depts.add(dept);
}
return depts;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
private static Dept findById(int deptno) {
//SELECT DEPTNO,DNAME,LOC FROM dept WHERE DEPTNO=10;
String sql="SELECT DEPTNO,DNAME,LOC FROM dept WHERE DEPTNO=?;";
try {
Connection conn = DriverManager.getConnection(DBUtil.URL,DBUtil.USER,DBUtil.PASSWORD);
PreparedStatement stat=conn.prepareStatement(sql);
stat.setObject(1, deptno);
ResultSet rs=stat.executeQuery();
Dept dept=new Dept();
if(rs.next()) {
dept.setDeptno(rs.getInt("deptno"));
dept.setDname(rs.getString("dname"));
dept.setLoc(rs.getString("loc"));
}
return dept;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
private static void delete(Integer deptno) {
//DELETE FROM dept WHERE DEPTNO=82;
String sql="DELETE FROM dept WHERE DEPTNO=?;";
try {
Connection conn=DriverManager.getConnection(DBUtil.URL,DBUtil.USER,DBUtil.PASSWORD);
PreparedStatement stat=conn.prepareStatement(sql);
stat.setObject(1, deptno);
int rlt=stat.executeUpdate();
if(rlt>0) {
System.out.println("删除了"+rlt+"记录");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
private static void update(Dept dept) {
//UPDATE dept SET DNAME = '白豹',LOC = '淮南' WHERE DEPTNO =90 ;
String sql="UPDATE dept SET DNAME = ?,LOC = ? WHERE DEPTNO =? ;";
try {
Connection conn=DriverManager.getConnection(DBUtil.URL,DBUtil.USER,DBUtil.PASSWORD);
PreparedStatement stat=conn.prepareStatement(sql);
stat.setObject(1, dept.getDname());
stat.setObject(2, dept.getLoc());
stat.setObject(3, dept.getDeptno());
int rlt=stat.executeUpdate();
if(rlt>0) {
System.out.println("更新了"+rlt+"记录");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
private static void insert(Dept dept) {
//自增长的 不用插入id
String sql="INSERT INTO dept(DNAME,LOC) VALUES(?,?);";
try {
//创建连接
Connection conn=DriverManager.getConnection(DBUtil.URL,DBUtil.USER,DBUtil.PASSWORD);
//预编译语句
PreparedStatement stat=conn.prepareStatement(sql);
//设置参数,参数要和预编译的参数个数一致
stat.setObject(1, dept.getDname());
stat.setObject(2, dept.getLoc());
//执行事务
int rlt = stat.executeUpdate();
if(rlt>0) {
System.out.println("成功插入:"+rlt+"条数据");
}
//关闭连接
stat.close();
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
package com.dept.utils;
public class DBUtil {
//中文乱码?useUnicode=true&characterEncoding=UTF-8
public static final String URL = "jdbc:mysql://localhost:3306/stores?useUnicode=true&characterEncoding=UTF-8";
public static final String USER = "root";
public static final String PASSWORD = "123";
static {
try {
//数据库驱动
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("缺少数据库驱动包");
}
}
}
package com.dept.pojo;
public class Dept {
private Integer deptno;
private String dname;
private String loc;
public Integer getDeptno() {
return deptno;
}
public void setDeptno(Integer deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
@Override
public String toString() {
return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";
}
}