JavaSE学习笔记 2024-1-3 --JDBC

JDBC

« 上一篇
个人整理非商业用途，欢迎探讨与指正！！

---

---

1.JDBC简介

Java与数据库的连接方案，数据库编程
Java DataBase Contectivity
分为两个部分:
 JDK:配置完成
 SDK:是由数据库厂商提供的api,可以帮助我们连接java
  根据数据库版本下载jar包(网上有)

2.JDBC连接步骤

1.加载驱动
2.创建连接
3.准备语句对象
4.执行SQL指令
5.处理结果
6.关闭资源

3.JDBC入门案例

3.1加载驱动

我们需要预先准备数据库厂商提供的SDK(jar文件)
 jar文件:其实就是一个压缩包,内部存放的是.class文件(第三方为我们编辑好的,可以直接使用的)
  例如:mysql-connector-java-8.0.17.jar
 mysql主流有两个版本:5.x 和 8.x 都有对应的jar文件提供

将驱动的jar文件,添加到java项目(工程)中
 1.在java项目下创建lib文件夹
 2.将下载好的jar文件,复制粘贴到lib中
 3.选择jar文件,右键"Build Path"

3.2第一个JDCB程序

package com.qf.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Demo01 {

	public static void main(String[] args) throws Exception {
//		1.加载驱动(之前已经"biu"成功) 
//		将SDK中提供的驱动类,可以帮助我们进行JDBC连接的一个类,添加到JVM中
		Class.forName("com.mysql.cj.jdbc.Driver");
//		2.创建连接
//		通过JDK提供的,DriverManager类获取数据库连接
//		url:数据库统一的资源定位器(为了连接到指定的数据库资源使用的)
		String url = "jdbc:mysql://localhost:3306/mysql?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true";
//		username:连接数据库的用户
		String username = "root";
//		password:连接数据库的用户对应的密码
		String password = "root";
		Connection connection = DriverManager.getConnection(url, username, password);
//		3.准备语句对象
		Statement createStatement = connection.createStatement();
//		4.执行SQL指令
		String sql = "select u_id,u_name,u_balance from t_user";
//		语句执行成功后,结果被封装到结果集中
		ResultSet resultSet = createStatement.executeQuery(sql);
//		5.处理结果
		while(resultSet.next()) {
			int uid = resultSet.getInt(1);
			String uname = resultSet.getString(2);
			double balance = resultSet.getDouble(3);
			System.out.println(uid+", "+uname+", "+balance);
//			得到的数据可以做判断
			if(uname.equals("大佑")) {
				System.out.println("登录成功");
			}else {
				System.out.println("登录失败");
			}
		}
//		6.关闭资源
		resultSet.close();
		createStatement.close();
		connection.close();
	}
}

4.JDBC的CRUD操作

sql语句一定是在工具中执行成功后,拿到jdbc中使用的
DML:使用excuteUpdate进行sql语句的执行
DQL:使用excuteQuery进行sql语句的执行

4.1insert操作

public class Demo02 {

	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.cj.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/mysql?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true";
		String username = "root";
		String password = "root";
		Connection connection = DriverManager.getConnection(url, username, password);
		Statement createStatement = connection.createStatement();
		
//		编写正确的sql语句
		String sql = "insert into t_user (u_name,u_balance) values ('jack',20)";
		
//		DQL使用executeQuery(返回结果为ResultSet) DML使用executeUpdate(int类型,受影响的行数)
		int result = createStatement.executeUpdate(sql);
		
		System.out.println("添加的行数为:"+result);
		
		
		createStatement.close();
		connection.close();
	}
}

4.2delete操作

public class Demo03 {

	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.cj.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/mysql?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true";
		String username = "root";
		String password = "root";
		Connection connection = DriverManager.getConnection(url, username, password);
		Statement createStatement = connection.createStatement();
		
//		编写正确的sql语句
		String sql = "delete from t_user where u_id = 13";
		
//		DQL使用executeQuery(返回结果为ResultSet) DML使用executeUpdate(int类型,受影响的行数)
		int result = createStatement.executeUpdate(sql);
		
		System.out.println("删除的行数为:"+result);
		
		
		createStatement.close();
		connection.close();
	}
}

4.3修改操作

public class Demo04 {

	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.cj.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/mysql?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true";
		String username = "root";
		String password = "root";
		Connection connection = DriverManager.getConnection(url, username, password);
		Statement createStatement = connection.createStatement();
		
//		编写正确的sql语句
		String sql = "update t_user set u_name = 'zs' where u_id = 17";
		
//		DQL使用executeQuery(返回结果为ResultSet) DML使用executeUpdate(int类型,受影响的行数)
		int result = createStatement.executeUpdate(sql);
		
		System.out.println("修改的行数为:"+result);
		
		
		createStatement.close();
		connection.close();
	}
}

4.4查询单个操作

public class Demo05 {

	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.cj.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/mysql?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true";
		String username = "root";
		String password = "root";
		Connection connection = DriverManager.getConnection(url, username, password);
		Statement createStatement = connection.createStatement();
//		CRUD的每次SQL都不相同
		String sql = "select u_id,u_name,u_balance from t_user where u_id = 17";
		
//		查询有结果集,使用executeQuery方法
		ResultSet resultSet = createStatement.executeQuery(sql);
		
		if(resultSet.next()) {
			int uid = resultSet.getInt(1);
			String uname = resultSet.getString(2);
			double balance = resultSet.getDouble(3);
			System.out.println(uid+", "+uname+", "+balance);
		}
//		6.关闭资源
		resultSet.close();
		createStatement.close();
		connection.close();
	}
}

4.5查询多条数据

public class Demo06 {

	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.cj.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/mysql?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true";
		String username = "root";
		String password = "root";
		Connection connection = DriverManager.getConnection(url, username, password);
		Statement createStatement = connection.createStatement();
//		CRUD的每次SQL都不相同
		String sql = "select u_id,u_name,u_balance from t_user";
		
//		查询有结果集,使用executeQuery方法
		ResultSet resultSet = createStatement.executeQuery(sql);
		
		while(resultSet.next()) {
			int uid = resultSet.getInt(1);
			String uname = resultSet.getString(2);
			double balance = resultSet.getDouble(3);
			System.out.println(uid+", "+uname+", "+balance);
		}
//		6.关闭资源
		resultSet.close();
		createStatement.close();
		connection.close();
	}
}

5.工具类的封装

封装:保护代码,提高代码的利用率,减少重复的代码

/**
 * 
 * @author y1538
 *	JDBC工具类:
 *		1.帮助我们获取数据库连接对象
 *		2.关闭资源
 */
public class DBUtil {
//	写成参数的作用是为了之后换库方便
	private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/mysql?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true";
	private static final String USERNAME = "root";
	private static final String PASSWORD = "root";
	
	static {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			System.out.println("加载驱动类失败...");
		}
	}

	/**
	 * 获取连接方法,返回值为数据库连接对象
	 */
	public static Connection getConnetion() {
		Connection connection = null;
		try {

			connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	}
	
	/**
	 * 关闭资源
	 * DQL的关闭
	 * */
	public static void close(Connection conn,Statement stmt,ResultSet rs) {
		try {
			if(rs != null && !rs.isClosed() /*开启状态时*/) {
				rs.close();
			}
			if(stmt != null && !stmt.isClosed() /*开启状态时*/) {
				stmt.close();
			}
			if(conn != null && !conn.isClosed() /*开启状态时*/) {
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 关闭资源
	 * DML的关闭
	 * */
	public static void close(Connection conn,Statement stmt) {
		close(conn,stmt,null);
	}
}

6.封装DTO和DAO

6.1封装DTO

Data Transfer Object
 数据传输对象,就是java实体类
DTO是实体类的封装规则
 规则:私有属性,get/set,构造方法(有参,无参),toString,hashCode,equals等
DTO是为数据表封装java实例类
 规则:类名和表名对应,属性和字段对应,对象和记录(元组)对应
DTO包的命名:
 pojo,vo,entry,dto…
DTO作用:
 1.为DML操作作为参数
 2.为DQL封装查询结果

public class Emp {
//	表中字段对应的属性
	private int empno;
	private String ename;
	private String job;
	private Date hiredate;
	private double sal;
	private double comm;
	private int deptno;
    ...

6.2使用DTO封装查询结果

public class Demo07 {

	public static void main(String[] args) throws Exception {
		Demo07 demo07 = new Demo07();
		/*List<Emp> queryAll = demo07.queryAll();
		System.out.println(queryAll);*/
		Emp emp = demo07.queryByNo(3);
		System.out.println(emp);
	}
	
//	封装了一个查询所有数据的方法
	public List<Emp> queryAll() throws Exception{
		List<Emp> list = new ArrayList<>();
		
		Connection connetion = DBUtil.getConnetion();
		Statement createStatement = connetion.createStatement();
		String sql = "select empno,ename,hiredate,sal,comm,deptno,job from emp";
		ResultSet rs = createStatement.executeQuery(sql);
		
		while(rs.next()) {
			int empno = rs.getInt(1);
			String ename = rs.getString(2);
			Date hiredate = rs.getDate(3);//jdbc中可以直接获取日期
			double sal = rs.getDouble(4);
			double comm = rs.getDouble(5);
			int deptno = rs.getInt(6);
			String job = rs.getString(7);
//			通过构造创建Emp对象
			Emp emp = new Emp(empno, ename, job, hiredate, sal, comm, deptno);
			list.add(emp);
		}
		DBUtil.close(connetion, createStatement, rs);
		return list;
	}
	
//	通过id查询
	public Emp queryByNo(int id) throws Exception {
		Connection connetion = DBUtil.getConnetion();
		Statement createStatement = connetion.createStatement();
		String sql = "select empno,ename,hiredate,sal,comm,deptno,job from emp where empno = " + id;
		ResultSet rs = createStatement.executeQuery(sql);
		
		while(rs.next()) {
			int empno = rs.getInt(1);
			String ename = rs.getString(2);
			Date hiredate = rs.getDate(3);//jdbc中可以直接获取日期
			double sal = rs.getDouble(4);
			double comm = rs.getDouble(5);
			int deptno = rs.getInt(6);
			String job = rs.getString(7);
			return new Emp(empno, ename, job, hiredate, sal, comm, deptno);
		}
		DBUtil.close(connetion, createStatement, rs);
		
		return null;
	}
}

6.3使用DTO作为DML方法参数

public class Demo08 {

	public static void main(String[] args) throws Exception {
		Demo08 demo08 = new Demo08();
//		demo08.add("bmh",new Date(System.currentTimeMillis()),1200,6000,40);
		/*Emp emp = new Emp(0, "wxy", "", new Date(2024,10,3), 12000, -300, 40);
		demo08.add(emp);*/
//		demo08.update("sxc",10000,2);
		Emp emp = new Emp();
		emp.setEmpno(3);
		emp.setEname("lz");
		emp.setSal(8000);
		demo08.update(emp);
	}
//	定义一个添加方法,使用JDBC将数据作为参数传到数据库中
	public void add(String name,Date hiredate,double sal,double comm,int deptno) throws Exception {
		Connection connection = DBUtil.getConnection();
		Statement createStatement = connection.createStatement();
		String sql = "insert into emp (ename,hiredate,sal,comm,deptno) values ('"+name+"','"+hiredate+"',"+sal+","+comm+","+deptno+")";
		System.out.println("SQL:"+sql);
		createStatement.executeUpdate(sql);
		DBUtil.close(connection, createStatement);
	}
	
//	使用DTO对上述的方法进行改造
	public void add(Emp emp) throws Exception {
		Connection connection = DBUtil.getConnection();
		Statement createStatement = connection.createStatement();
		String sql = "insert into emp (ename,hiredate,sal,comm,deptno) "
				+ "values ('"+emp.getEname()+"','"+emp.getHiredate()+"',"+emp.getSal()+","+emp.getComm()+","+emp.getDeptno()+")";
		System.out.println("SQL:"+sql);
		createStatement.executeUpdate(sql);
		DBUtil.close(connection, createStatement);
	}
//	修改操作
	public void update(String name,double sal/*涨薪的值*/,int empno) throws Exception {
		Connection connection = DBUtil.getConnection();
		Statement createStatement = connection.createStatement();
		String sql = "update emp set ename = '"+name+"',sal = sal + "+sal+" where empno = " + empno;
		System.out.println("SQL:"+sql);
		createStatement.executeUpdate(sql);
		DBUtil.close(connection, createStatement);
	}
//	使用DTO进行改造上述功能
	public void update(Emp emp) throws Exception {
		Connection connection = DBUtil.getConnection();
		Statement createStatement = connection.createStatement();
		String sql = "update emp set ename = '"+emp.getEname()+"',sal = sal + "+emp.getSal()+" where empno = " + emp.getEmpno();
		System.out.println("SQL:"+sql);
		createStatement.executeUpdate(sql);
		DBUtil.close(connection, createStatement);
	}
}