jdbc 增删改查 ,dept练习

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 + "]";
	}
	
}