八、MySQL学习目录
2022-07-17 本文已影响0人
东方奇迹
1、数据库分类：关系型数据库，非关系型数据库
关系型数据库：数据分类存放，数据之间可以有联系；DB2、Oracal、MySQL、SqlServer
非关系型数据库：数据分类存放，数据之间没有联系；Redis、Memcache、MongoDB、Neo4j
2、SQL语言分类
DDL数据库定义语言(逻辑库、数据表、视图、索引)、
DML数据库操作语言(增删改查)、
DCL数据库控制语言(用户、权限、事务)
3、数据类型：数字、字符串、日期
4、约束：主键约束、非空约束、唯一约束、外键约束
5、JDBC的开发流程实现数据库增删改查、封装DbUtils工具类
（1）加载并注册JDBC驱动
（2）创建数据库连接
（3）创建statemet对象
（4）遍历查询结果
（5）关闭连接释放资源
数据库连接字符串：“com.mysql.cj.jdbc.Driver”
MySQL连接字符串：“jdbc:mysql://localhost:3306/imooc”
参数字符串：useSSL:true、useUnicode:true、characterEncoding:UTF-8、serverTimezone:Asia/Shanghai、allowPublicKeyRetrieval:true
//标准JDBC操作五步骤
public class StandardJDBCSample {
public static void main(String[] args) {
Connection conn = null;
try {
//1、加载并注册JDBC驱动(Class.forName表示加载指定的类)
Class.forName("com.mysql.cj.jdbc.Driver");
//2、创建数据库连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", "123456");
//3、创建Statement对象
Statement stmt = conn.createStatement();
ResultSet re = stmt.executeQuery("select * from employee where dname = '研发部'");
//4、遍历查询结果
while (re.next()) {
Integer eno = re.getInt(1);
String ename = re.getString("ename");
Float salary = re.getFloat("salary");
String dname = re.getString("dname");
Date hiredate = re.getDate("hiredate");
System.out.println(eno + "-" + ename + "-" + salary + "-" + dname + "-" + hiredate);
}
}catch (Exception e) {
e.printStackTrace();
}finally {
//5、关闭连接，释放资源
try {
if (conn != null && conn.isClosed() == false)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public class DbUtils {
/**
* 创建新的数据库连接
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public static Connection getConnection() throws ClassNotFoundException, SQLException {
//1、加载并注册JDBC驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2、创建数据库连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", "123456");
return conn;
}
/**
* 关闭连接、释放资源
* @param re 结果集对象
* @param stmt Statement对象
* @param conn Connection对象
*/
public static void closeConnection(ResultSet re, Statement stmt, Connection conn) {
if (re != null) {
try {
re.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
try {
if (conn != null && conn.isClosed() == false) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
6、SQL注入攻击的应对(PreparedStatement)、事务的控制、JDBC批处理
/**
* JDBC中事务控制、JDBC批处理
*/
public class BatchSample {
//标准方式插入若干数据
private static void tc1() {
Connection conn = null;
PreparedStatement stmt = null;
try {
long startTime = new java.util.Date().getTime();
conn = DbUtils.getConnection();
conn.setAutoCommit(false);
String sql = "insert into employee(eno,ename,salary,dname,hiredate) values(?,?,?,?,?)";
for (int i = 4000; i < 5000; i++) {
if (i == 4005) {
// throw new RuntimeException("哈哈爆炸了");
}
stmt = conn.prepareStatement(sql);
stmt.setInt(1, i);
stmt.setString(2, "员工" + i);
stmt.setFloat(3, 4000f);
stmt.setString(4, "市场部");
stmt.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
stmt.executeUpdate();
}
conn.commit();
long endTime = new java.util.Date().getTime();
System.out.println("tc1执行时长：" + (endTime - startTime));
} catch (Exception e) {
e.printStackTrace();
try {
if (conn != null && !conn.isClosed()) {
conn.rollback();//回滚数据
}
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
DbUtils.closeConnection(null, stmt, conn);
}
}
//使用批处理插入若干数据
private static void tc2() {
Connection conn = null;
PreparedStatement stmt = null;
try {
long startTime = new java.util.Date().getTime();
conn = DbUtils.getConnection();
conn.setAutoCommit(false);
String sql = "insert into employee(eno,ename,salary,dname,hiredate) values(?,?,?,?,?)";
stmt = conn.prepareStatement(sql);
for (int i = 6000; i < 7000; i++) {
if (i == 4005) {
// throw new RuntimeException("哈哈爆炸了");
}
stmt.setInt(1, i);
stmt.setString(2, "员工" + i);
stmt.setFloat(3, 4000f);
stmt.setString(4, "市场部");
stmt.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
// stmt.executeUpdate();
stmt.addBatch();//将参数将入批处理任务
}
stmt.executeBatch();//执行批处理任务
conn.commit();
long endTime = new java.util.Date().getTime();
System.out.println("tc2执行时长：" + (endTime - startTime));
} catch (Exception e) {
e.printStackTrace();
try {
if (conn != null && !conn.isClosed()) {
conn.rollback();//回滚数据
}
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
DbUtils.closeConnection(null, stmt, conn);
}
}
public static void main(String[] args) {
tc1();
tc2();
}
}
7、Druid连接池的配置与使用：
（1）加载属性文件:(druid-config.properties)
（2）获取DataSource数据源对象
（3）创建数据库连接
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username=root
password=123456
initialSize=20
maxActive=20
/**
* Druid连接池的配置与使用
*/
public class DruidSample {
public static void main(String[] args) {
//1、加载属性文件
Properties properties = new Properties();
String propertyFile = DruidSample.class.getResource("/druid-config.properties").getPath();
try {
propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
properties.load(new FileInputStream(propertyFile));
} catch (Exception e) {
e.printStackTrace();
}
Connection conn = null;
PreparedStatement stmt = null;
ResultSet re = null;
try {
//2、获取DataSource数据源对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//3、创建数据库连接
conn = dataSource.getConnection();
stmt = conn.prepareStatement("select * from employee limit 0,10");
re = stmt.executeQuery();
while (re.next()) {
Integer eno = re.getInt(1);
String ename = re.getString("ename");
Float salary = re.getFloat("salary");
String dname = re.getString("dname");
Date hiredate = re.getDate("hiredate");
System.out.println(eno + "-" + ename + "-" + salary + "-" + dname + "-" + hiredate);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
/**
* 不使用连接池的时候：conn.close()关闭连接
* 使用连接池的时候：conn.close()将连接回收到连接池
*/
DbUtils.closeConnection(re, stmt, conn);
}
}
}
8、C3P0连接池的配置与使用：
（1）加载属性文件:(c3p0-config.xml)
（2）获取DataSource数据源对象
（3）创建数据库连接
<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true</property>
<property name="user">root</property>
<property name="password">123456</property>
<!--连接池初始连接数量-->
<property name="initialPoolSize">10</property>
<!--最大连接数量-->
<property name="maxPoolSize">20</property>
</default-config>
</c3p0-config>
/**
* C3P0连接池的配置与使用
*/
public class C3P0Sample {
public static void main(String[] args) {
//1、加载配置文件
//2、创建DataSource数据源对象
ComboPooledDataSource dataSource = new ComboPooledDataSource();
//3、得到数据库连接
Connection conn = null;
PreparedStatement stmt = null;
ResultSet re = null;
try {
conn = dataSource.getConnection();
stmt = conn.prepareStatement("select * from employee limit 0,10");
re = stmt.executeQuery();
while (re.next()) {
Integer eno = re.getInt(1);
String ename = re.getString("ename");
Float salary = re.getFloat("salary");
String dname = re.getString("dname");
Date hiredate = re.getDate("hiredate");
System.out.println(eno + "-" + ename + "-" + salary + "-" + dname + "-" + hiredate);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
/**
* 不使用连接池的时候：conn.close()关闭连接
* 使用连接池的时候：conn.close()将连接回收到连接池
*/
DbUtils.closeConnection(re, stmt, conn);
}
}
}
9、Apache Commons DBUtils
commons-dbutils是Apache提供的开源JDBC工具类库；
/**
* APache DBUtils + Druid联合使用
*/
public class DbUtilsSample {
private static void query() {
Properties properties = new Properties();
String path = DbUtilsSample.class.getResource("/druid-config.properties").getPath();
try {
path = new URLDecoder().decode(path, "UTF-8");
properties.load(new FileInputStream(path));
DataSource dataSource = new DruidDataSourceFactory().createDataSource(properties);
QueryRunner qr = new QueryRunner(dataSource);
List<Employee> list = qr.query("select * from employee limit ?,10",
new BeanListHandler<Employee>(Employee.class),
new Object[]{10});
for (Employee emp : list) {
System.out.println(emp.getEname());
}
} catch (Exception e) {
e.printStackTrace();
}
}
private static void update() {
Properties properties = new Properties();
String path = DbUtilsSample.class.getResource("/druid-config.properties").getPath();
Connection conn = null;
try {
path = new URLDecoder().decode(path, "UTF-8");
properties.load(new FileInputStream(path));
DataSource dataSource = new DruidDataSourceFactory().createDataSource(properties);
conn = dataSource.getConnection();
conn.setAutoCommit(false);
String sql1 = "update employee set salary = salary + 1000 where eno = ? ";
String sql2 = "update employee set salary = salary - 500 where eno = ? ";
QueryRunner qr = new QueryRunner();
qr.update(conn,sql1,new Object[]{1000});
qr.update(conn,sql2,new Object[]{1001});
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try {
if (conn != null && !conn.isClosed()) {
conn.rollback();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
try {
if (conn != null && !conn.isClosed()) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
query();
update();
}
}
