尚硅谷网课《尚硅谷JDBC核心技术(新版jdbc)》
讲师:宋红康老师
链接地址:https://www.bilibili.com/video/BV1eJ411c7rf?p=7
学习时间:2021/07/17
第2章:获取数据库连接
2.1 要素一:Driver接口实现类
2.2 要素二:URL
2.3 要素三:用户名和密码
2.4 数据库连接方式举例
2.4.1 连接方式一:
@Test
public void testConnection1() throws SQLException {
Driver driver =new com.mysql.jdbc.Driver();
String url="jdbc:mysql://localhost:3306/test";
Properties info=new Properties();
info.setProperty("user","root");
info.setProperty("password","131702");
Connection connect = driver.connect(url, info);
System.out.println(connect);
}
2.4.2 连接方式二:
@Test
public void testConnection2() throws Exception {
Class<?> clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
String url="jdbc:mysql://localhost:3306/test";
Properties info=new Properties();
info.setProperty("user","root");
info.setProperty("password","131702");
Connection connect = driver.connect(url, info);
System.out.println(connect);
}
2.4.3 连接方式三:
@Test
public void testConnection3() throws Exception {
String url="jdbc:mysql://localhost:3306/test";
String user="root";
String password="131702";
Class<?> clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
DriverManager.registerDriver(driver);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
2.4.4 连接方式四:
@Test
public void testConnection4() throws Exception {
String url="jdbc:mysql://localhost:3306/test";
String user="root";
String password="131702";
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
2.4.5 连接方式五:
@Test
public void testConnection5() throws Exception {
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
user=root
password=abc123
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver
- 使用配置文件的好处?
1.实现了数据和代码的分离,实现了解耦;如果需要修改配置信息,直接在配置文件中修改,不需要深入代码
2.如果修改了配置文件信息,省去重新编译的过程。
第3章:使用PreparedStatement实现CRUD操作
3.1 操作和访问数据库
- 在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式:
1.Statement:用于执行静态 SQL 语句并返回它所生成结果的对象。
2.PreparedStatement:SQL 语句被预编译并存储在此对象中,可以使用此对象多次高效地执行该语句。
3.CallableStatement:提供对SQL 存储过程的调用。
3.2 使用Statement操作数据表的弊端
- 使用Statement操作数据表存在弊端:
1.问题一:需要拼写SQL语句
2.问题二:存在SQL注入问题 - SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查,而在用户输入数据中注入非法的 SQL 语句段
或命令(如:SELECT user, password FROM user_table WHERE user=‘a’ OR 1 = ’ AND password = ’ OR ‘1’ =
‘1’) ,从而利用系统的 SQL 引擎完成恶意行为的做法。
总结来说就是:输入错误的用户名和密码,也能进入数据库。 - 如何避免sql注入:只要用 PreparedStatement(从Statement扩展而来) 取代 Statement 就可以了。
- 除了解决Statement的拼串、sql问题之外,PreparedStatement还有哪些好处呢?
1.PreparedStatement可以操作Blob的数据,而Statement做不到。
2.PreparedStatement可以实现更高效的批量操作。
JDBC API小结
- 两种编程思想
1.面向接口编程的思想
2.ORM编程思想(object relational mapping)
一个数据表对于一个java类
表中的一条记录对应java的一个对象
表中的一个字段对应java类的一个属性 - 两种技术
1.JDBC结果集的元数据:ResultSetMetaDate
获取列数:getColumnCount()
获取列的别名:getColumnLable()
2.通过反射,创建指定类的对象,获取指定的属性并赋值
第8章
8.3.3 Druid(德鲁伊)数据库连接池
- Druid是阿里巴巴开源平台上一个数据库连接池实现,它结合了C3P0、DBCP、Proxool等DB池的优点,同时加入了日志监控,可以很好的监控DB池连接和SQL的执行情况,可以说是针对监控而生的DB连接池,可以说是目前最好的连接池之一。
package com.xiangll4.connection;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.pool.DruidPooledConnection;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
public class Druid {
@Test
public void testGetConnection1() throws Exception {
DruidDataSource source = new DruidDataSource();
source.setDriverClassName("com.mysql.jdbc.Driver");
source.setUrl("jdbc:mysql:///test");
source.setUsername("root");
source.setPassword("131702");
source.setInitialSize(10);
source.setMaxActive(10);
DruidPooledConnection connection = source.getConnection();
System.out.println(connection);
}
@Test
public void testGetConnection2() throws Exception {
Properties pros = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
pros.load(is);
DataSource source = DruidDataSourceFactory.createDataSource(pros);
Connection connection = source.getConnection();
System.out.println(connection);
}
}
- 其中,src下的配置文件为:【druid.properties】
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:/
username=root
password=131702
initialSize=10
maxActive=10
- 将Druid连接池的实现封装成一个JDBCUtils静态方法,方便使用:
package com.xiangll1.util;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
private static DataSource source;
static{
try {
Properties pros = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
pros.load(is);
source = DruidDataSourceFactory.createDataSource(pros);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection3() throws SQLException {
Connection connection = source.getConnection();
return connection;
}
}
public class ConnectionTest {
@Test
public void testGetConnection1() throws Exception {
Connection connection3 = JDBCUtils.getConnection3();
System.out.println(connection3);
}
}
第9章:Apache-DBUtils实现CRUD操作
package com.xiangll5.dbutils;
import com.xiangll1.bean.Customer;
import com.xiangll1.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
public class QueryRunnerTest {
@Test
public void testInsert() {
Connection connection = null;
try {
QueryRunner runner = new QueryRunner();
connection = JDBCUtils.getConnection3();
String sql = "insert into customers(name,email,birth) values(?,?,?);";
int insertCount = runner.update(connection, sql, "蔡徐坤", "caixukun@126.com", "1997-09-08");
System.out.println("添加了"+insertCount+"条记录!");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,null);
}
}
@Test
public void testQuery1() {
Connection connection = null;
try {
QueryRunner runner = new QueryRunner();
connection = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id = ? ;";
BeanHandler<Customer> handler=new BeanHandler<>(Customer.class);
Customer customer = runner.query(connection, sql, handler, 5);
System.out.println(customer);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,null);
}
}
@Test
public void testQuery2() {
Connection connection = null;
try {
QueryRunner runner = new QueryRunner();
connection = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id < ? ;";
BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
List<Customer> list = runner.query(connection, sql, handler, 5);
list.forEach(System.out::println);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,null);
}
}
@Test
public void testQuery3() {
Connection connection = null;
try {
QueryRunner runner = new QueryRunner();
connection = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id = ? ;";
MapHandler handler = new MapHandler();
Map<String, Object> map = runner.query(connection, sql,handler, 5);
System.out.println(map);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,null);
}
}
@Test
public void testQuery4() {
Connection connection = null;
try {
QueryRunner runner = new QueryRunner();
connection = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id < ? ;";
MapListHandler handler = new MapListHandler();
List<Map<String, Object>> mapList = runner.query(connection, sql, handler, 5);
mapList.forEach(System.out::println);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,null);
}
}
@Test
public void testQuery5() {
Connection connection = null;
try {
QueryRunner runner = new QueryRunner();
connection = JDBCUtils.getConnection3();
String sql = "select count(*) from customers ;";
ScalarHandler handler = new ScalarHandler();
Long count = (Long) runner.query(connection, sql, handler);
System.out.println(count);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,null);
}
}
@Test
public void testQuery6() {
Connection connection = null;
try {
QueryRunner runner = new QueryRunner();
connection = JDBCUtils.getConnection3();
String sql = "select max(birth) from customers ;";
ScalarHandler handler = new ScalarHandler();
Date maxBirth = (Date) runner.query(connection, sql, handler);
System.out.println(maxBirth);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,null);
}
}
@Test
public void testQuery7() {
Connection connection = null;
try {
QueryRunner runner = new QueryRunner();
connection = JDBCUtils.getConnection3();
String sql = "select id,name,email,birth from customers where id=?;";
ResultSetHandler<Customer> handler=new ResultSetHandler<Customer>() {
@Override
public Customer handle(ResultSet rs) throws SQLException {
if(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth = rs.getDate("birth");
Customer customer = new Customer(id, name, email, birth);
return customer;
}
return null;
}
};
Customer customer = runner.query(connection, sql, handler, 5);
System.out.println(customer);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,null);
}
}
}
public static void closeResource1(Connection connection, Statement ps,ResultSet rs){
DbUtils.closeQuietly(connection);
DbUtils.closeQuietly(ps);
DbUtils.closeQuietly(rs);
}