目录
JDBC
什么是JDBC? JDBC全称是Java Database Connectivity(Java数据库连接),是Java连接数据库的标准和规范。
1. JDBC实现CRUD
-
导入驱动包
1.1 添加数据
package com.blb.demo1; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; /** * JDBC新增 */ public class Demo1 { public static void main(String[] args) throws SQLException { Connection connection = null; try { // 2.加载驱动 //8.xxx com.mysql.cj.jdbc.Driver //5.xxx com.mysql.jdbc.Driver Class.forName("com.mysql.cj.jdbc.Driver"); // 3.打开连接 connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC", "root", "root"); // 4.获取预处理器 PreparedStatement preparedStatement = connection.prepareStatement("insert into t_user(id, name) values(?, ?)"); // 5.设置参数 // setInt(第几个参数, 值); preparedStatement.setInt(1, 3); preparedStatement.setString(2, "李四"); // 6.执行SQL int len = preparedStatement.executeUpdate(); // 7.解析结果 System.out.println(len); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { // 8.关闭连接 connection.close(); } } }
1.2 修改数据
package com.blb.demo1; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; /** * JDBC修改操作 */ public class Demo2 { public static void main(String[] args) throws Exception { Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:mysql:///jdbc?serverTimezone=UTC", "root", "root"); PreparedStatement preparedStatement = connection.prepareStatement("update t_user set name = ? where id = ?"); preparedStatement.setString(1, "李四"); preparedStatement.setInt(2, 1); int len = preparedStatement.executeUpdate(); System.out.println(len); } }
1.3 删除数据
package com.blb.demo1; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; /** * JDBC删除 */ public class Demo3 { public static void main(String[] args) throws Exception { Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:mysql:///jdbc?serverTimezone=UTC", "root", "root"); PreparedStatement preparedStatement = connection.prepareStatement("delete from t_user where id = ?"); preparedStatement.setInt(1,1); int len = preparedStatement.executeUpdate(); System.out.println(len); } }
1.4 查询数据
package com.blb.demo1; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; /** * JDBC查询 */ public class Demo4 { public static void main(String[] args) throws Exception { Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:mysql:///jdbc?serverTimezone=UTC", "root", "root"); PreparedStatement preparedStatement = connection.prepareStatement("select * from t_user"); ResultSet resultSet = preparedStatement.executeQuery(); while(resultSet.next()){ int id = resultSet.getInt("id"); String name = resultSet.getString("name"); System.out.println(id+"\t"+name); } } }
1.5 JDBC的驱动加载
1.Class.forName("xxx"); 2.DriverManager.registerDriver(new Driver()); //重复加载 3.不显示加载驱动类 //依赖包的配置文件,有的jar包没有该文
2. SQL注入
什么是SQL注入? SQL注入是利用SQL拼接的特点而实现的一种攻击方式 SQL注入的案例 select * from t_user where name = 'werewrewrewe' and pwd = ' or 1=1'; 防止SQL注入的方法 使用?占位符形式设置参数 防止SQL注入的原理 内部对参数进行了处理
使用字符串拼接形式设置参数
package com.blb.demo1; import java.sql.*; import java.util.Scanner; /** * SQL注入 */ public class Demo5 { public static void main(String[] args) { Scanner sc = new Scanner(System.in); System.out.println("请输入登录的用户名:"); String name = sc.nextLine(); System.out.println("请输入登录的密码:"); String pwd = sc.nextLine(); try { System.out.println(pwd); Connection connection = DriverManager.getConnection("jdbc:mysql:///jdbc?serverTimezone=UTC", "root", "root"); String sql = "select * from t_user where name = '"+name+"' and pwd = '"+pwd+"'"; System.out.println(sql); PreparedStatement preparedStatement = connection.prepareStatement(sql); ResultSet resultSet = preparedStatement.executeQuery(); if(resultSet.next()){ System.out.println("登录成功"); }else { System.out.println("登录失败"); } } catch (SQLException e) { e.printStackTrace(); } } }
使用?占位符形式设置参数
package com.blb.demo1; import java.sql.*; import java.util.Scanner; /** * SQL注入 */ public class Demo6 { public static void main(String[] args) { Scanner sc = new Scanner(System.in); System.out.println("请输入登录的用户名:"); String name = sc.nextLine(); System.out.println("请输入登录的密码:"); String pwd = sc.nextLine(); try { System.out.println(pwd); Connection connection = DriverManager.getConnection("jdbc:mysql:///jdbc?serverTimezone=UTC", "root", "root"); String sql = "select * from t_user where name = ? and pwd = ?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, name); preparedStatement.setString(2, pwd); ResultSet resultSet = preparedStatement.executeQuery(); if(resultSet.next()){ System.out.println("登录成功"); }else { System.out.println("登录失败"); } } catch (SQLException e) { e.printStackTrace(); } } }
3. JDBC中的核心对象
-
DriverManager:驱动管理(驱动的注册,打开连接)
-
Connection:连接(控制连接、处理事务)
-
PreparedStatement:设置参数、执行SQL
PreparedStatement和Statement的联系与区别? 联系:两者都是处理器对象,都是负责执行SQL语句,PreparedStatement是Statement的子接口,拥有更丰富的API 区别:PreparedStatement可以防止SQL注入,Statement不可以
-
ResultSet:结果集(封装了查询语句中返回的所有数据)
//4. ResultSet:结果集(封装了查询语句中返回的所有数据) //1.获取结果数据 resultSet.getInt("id"); resultSet.getInt("id"); resultSet.getInt("id"); //2.获取元数据 //获取元数据对象 ResultSetMetaData metaData = resultSet.getMetaData(); //获取总列数 int count = metaData.getColumnCount(); for (int i = 1; i <= count; i++) { //获取列名 String columnName = metaData.getColumnName(i); //获取列类型名称 String columnTypeName = metaData.getColumnTypeName(i); //获取对应的Java中的数据类型 String columnClassName = metaData.getColumnClassName(i); System.out.println(columnName); System.out.println(columnTypeName); System.out.println(columnClassName); System.out.println("----------------"); } package com.blb.demo1; import java.sql.*; /** * Statement:处理器(不能防止SQL注入) */ public class Demo8 { public static void main(String[] args) throws Exception { Connection connection = DriverManager.getConnection("jdbc:mysql:///jdbc?serverTimezone=UTC", "root", "root"); Statement statement = connection.createStatement(); String sql = "select * from t_user where id = '1'"; ResultSet resultSet = statement.executeQuery(sql); if(resultSet.next()){ //1.获取结果集数据 // System.out.println(resultSet.getInt("id")); // System.out.println(resultSet.getString("name")); // System.out.println(resultSet.getString("pwd")); //2.获取元数据 //获取元数据对象 ResultSetMetaData metaData = resultSet.getMetaData(); //获取总列数 int count = metaData.getColumnCount(); for (int i = 1; i <= count; i++) { //获取列名 String columnName = metaData.getColumnName(i); //获取列类型名称 String columnTypeName = metaData.getColumnTypeName(i); //获取对应的Java中的数据类型 String columnClassName = metaData.getColumnClassName(i); System.out.println(columnName); System.out.println(columnTypeName); System.out.println(columnClassName); System.out.println("----------------"); } } } }
Statement
3.1 JDBC事务操作
1.设置手动提交 connection.setAutoCommit(false); 2.提交事务 connection.commit(); 3.回滚事务 connection.rollback();
package com.blb.demo1; import java.sql.*; /** * JDBC事务 */ public class Demo7 { public static void main(String[] args) throws SQLException { Connection connection = null; try { connection = DriverManager.getConnection("jdbc:mysql:///jdbc?serverTimezone=UTC", "root", "root"); connection.setAutoCommit(false); //设置手动提交 String sql = "update t_user set pwd = ? where id = ?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, "456"); preparedStatement.setInt(2, 1); preparedStatement.executeUpdate(); // System.out.println(1/0); preparedStatement.setString(1, "789"); preparedStatement.setInt(2, 2); preparedStatement.executeUpdate(); //提交事务 connection.commit(); } catch (Exception e) { e.printStackTrace(); connection.rollback(); } } }
4. 三层架构
什么是三层架构? 三层架构是一种软件设计架构,是一种组织代码的手段和方式。 表示层:数据的显示或者数据的录入 业务层:业务的具体操作流程(转账) 持久层:提供数据库表的CRUD 三层架构的优缺点
三层架构实现 目录说明 dao:持久层 entity:实体类 service:业务层 utils:工具类 view:表示层
5. 连接池(hikaricp)
什么是连接池? 一种存放数据库连接的容器,并且拥有动态新增连接、管理连接等功能于一体的容器。 为什么要使用连接池? 加快连接的获取速度 合理的应用连接
连接池的核心对象 DataSource
hikaricp连接池的使用 1.导包 2.创建连接池对象 3.从连接池中获取连接 4.使用连接 5.回收连接
package com.blb.demo1; import java.io.IOException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; /** * hikari连接池 */ public class Demo1 { public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException { // //创建连接池对象 // Properties properties = new Properties(); // properties.load(Demo1.class.getClassLoader().getResourceAsStream("jdbc.properties")); // HikariConfig hikariConfig = new HikariConfig(properties); // HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig); // // Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:mysql:///jdbc?serverTimezone=UTC", "root", "root"); // Connection connection = hikariDataSource.getConnection(); // // PreparedStatement preparedStatement = connection.prepareStatement("select count(1)n from t_user"); // ResultSet resultSet = preparedStatement.executeQuery(); // resultSet.next(); // System.out.println(resultSet.getInt("n")); Connection connection = DBUtils.getConnection(); ResultSet resultSet = DBUtils.executeQuery("select count(1)n from t_user", connection); resultSet.next(); System.out.println(resultSet.getInt("n")); } }
6. Spring-JDBC
一个由Spring团队开发的JDBC的工具类,作用和DBUtils一样,是目前代替DBUtils产物。
package com.blb.demo2; import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import java.io.IOException; import java.util.List; import java.util.Properties; public class Demo1 { public static void main(String[] args) throws IOException { //1.创建连接池 Properties properties = new Properties(); properties.load(Demo1.class.getClassLoader().getResourceAsStream("jdbc.properties")); HikariConfig hikariConfig = new HikariConfig(properties); HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig); //2.创建JdbcTemplate JdbcTemplate jdbcTemplate = new JdbcTemplate(hikariDataSource); //3.使用jdbcTemplate //查询用户表的总条数 Integer count = jdbcTemplate.queryForObject("select count(*) from t_user", Integer.class); System.out.println(count); //查询所有用户 List<User> list = jdbcTemplate.query("select * from t_user", new BeanPropertyRowMapper<User>(User.class)); System.out.println(list); //添加用户 // jdbcTemplate.execute("insert into t_user(name, pwd) values(?,?)","给第1个?赋值","给第2个?赋值"); } }