package com.hspedu.jdbc.myjdbc;/*** @author 林然* @version 1.0* 我们规定的 jdbc 接口(方法)*/
public interface JdbcInterface {//连接public Object getConnection() ;//crudpublic void crud();//关闭连接public void close();
}

package com.hspedu.jdbc.myjdbc;/*** @author 林然* @version 1.0* mysql 数据库实现了 jdbc 接口 [模拟] 【mysql 厂商开发】*/
public class MysqlJdbcImpl implements JdbcInterface{@Overridepublic Object getConnection() {System.out.println("得到 mysql 的连接");return null;}@Overridepublic void crud() {System.out.println("完成 mysql 增删改查");}@Overridepublic void close() {System.out.println("关闭 mysql 的连接");}
}

package com.hspedu.jdbc.myjdbc;/*** @author 林然* @version 1.0*/
public class OracleJdbcImpl implements JdbcInterface{@Overridepublic Object getConnection() {System.out.println("得到 oracle 的连接 升级");return null;}@Overridepublic void crud() {System.out.println("完成 对 oracle 的增删改查");}@Overridepublic void close() {System.out.println("关闭 oracle 的连接");}
}

package com.hspedu.jdbc.myjdbc;/*** @author 林然* @version 1.0*/
public class TestJDBC {public static void main(String[] args) {//完成对 mysql 的操作JdbcInterface jdbcInterface = new MysqlJdbcImpl();jdbcInterface.getConnection(); //通过接口来调用实现类[动态绑定]jdbcInterface.crud();jdbcInterface.close();//完成对 oracle 的操作System.out.println("==============================");jdbcInterface = new OracleJdbcImpl();jdbcInterface.getConnection(); //通过接口来调用实现类[动态绑定]jdbcInterface.crud();jdbcInterface.close();}
}

package com.hspedu.jdbc;import com.mysql.jdbc.Driver;import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;/*** @author 林然* @version 1.0* 这是第一个 Jdbc 程序，完成简单的操作*/
public class Jdbc01 {public static void main(String[] args) throws SQLException {//前置工作： 在项目下创建一个文件夹比如 libs// 将 mysql.jar 拷贝到该目录下，点击 add to project ..加入到项目中//1. 注册驱动Driver driver = new Driver();//2. 得到连接// 老师解读//(1) jdbc:mysql:// 规定好表示协议，通过 jdbc 的方式连接 mysql//(2) localhost 主机，可以是 ip 地址//(3) 3306 表示 mysql 监听的端口//(4) hsp_db02 连接到 mysql dbms 的哪个数据库//(5) mysql 的连接本质就是前面学过的 socket 连接String url ="jdbc:mysql://localhost:3306/hsp_db02";//将 用户名和密码放入到 Properties 对象Properties properties = new Properties();//说明 user 和 password 是规定好，后面的值根据实际情况写properties.setProperty("user", "root");// 用户properties.setProperty("password", "123456"); //密码Connection connect = driver.connect(url, properties);//3. 执行 sqlString sql = "insert into actor values(null, '刘德华', '男', '1970-11-11', '110')";//statement 用于执行静态 SQL 语句并返回其生成的结果的对象Statement statement = connect.createStatement();int rows = statement.executeUpdate(sql); // 如果是 dml 语句，返回的就是影响行数System.out.println(rows > 0 ? "成功" : "失败");
//4. 关闭连接资源statement.close();connect.close();}
}

package com.hspedu.jdbc;import com.mysql.jdbc.Driver;
import org.junit.Test;import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;/*** @author 林然* @version 1.0* 分析 java 连接 mysql 的 5 中方式*/
public class JdbcConn {//方式 1@Testpublic void connect01() throws SQLException {Driver driver = new Driver(); //创建 driver 对象String url = "jdbc:mysql://localhost:3306/hsp_db02";
//将 用户名和密码放入到 Properties 对象Properties properties = new Properties();
//说明 user 和 password 是规定好，后面的值根据实际情况写properties.setProperty("user", "root");// 用户properties.setProperty("password", "123456"); //密码Connection connect = driver.connect(url, properties);System.out.println(connect);}//方式 2@Testpublic void connect02() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {//使用反射加载 Driver 类 , 动态加载，更加的灵活，减少依赖性Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");Driver driver =(Driver) aClass.newInstance();String url = "jdbc:mysql://localhost:3306/hsp_db02";
//将 用户名和密码放入到 Properties 对象Properties properties = new Properties();
//说明 user 和 password 是规定好，后面的值根据实际情况写properties.setProperty("user", "root");// 用户properties.setProperty("password", "123456"); //密码Connection connect = driver.connect(url, properties);System.out.println(connect);}@Testpublic void connect05() throws IOException, ClassNotFoundException, SQLException, IllegalAccessException, InstantiationException {//通过 Properties 对象获取配置文件的信息Properties properties =new Properties();properties.load(new FileInputStream("src\\mysql.properties"));///获取相关的值String user=properties.getProperty("user");String password=properties.getProperty("password");String driver = properties.getProperty("driver");String url = properties.getProperty("url");Class<?> aClass = Class.forName(driver);//建议写上Driver driver1=(Driver)aClass.newInstance();DriverManager.registerDriver(driver1);Connection connection = DriverManager.getConnection(url, user, password);Statement statement=connection.createStatement();String sql1="create table news (id int primary key,`name` varchar(12))";statement.executeUpdate(sql1);String insert_sql="insert into news values(1,'1'),(2,'2'),(3,'3'),(4,'4'),(5,'5')";statement.executeUpdate(insert_sql);statement.close();connection.close();}
}

package com.hspedu.jdbc.resultset_;import com.mysql.jdbc.Driver;import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;/*** @author 林然* @version 1.0* 演示 select 语句返回 ResultSet ,并取出结果*/
public class ResultSet_ {public static void main(String[] args) throws IOException, ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {Properties properties=new Properties();properties.load(new FileInputStream("src\\mysql.properties"));//通过 Properties 对象获取配置文件的信息String user = properties.getProperty("user");String password =properties.getProperty("password");String driver =properties.getProperty("driver");String url= properties.getProperty("url");//1. 注册驱动Class<?> aClass = Class.forName(driver);Driver driver1 =(Driver)aClass.newInstance();DriverManager.registerDriver(driver1);//2. 得到连接Connection conn =DriverManager.getConnection(url,user,password);//3. 得到 StatementStatement statement =conn.createStatement();String sql ="select id,name,sex,borndate from actor";//执行给定的 SQL 语句，该语句返回单个 ResultSet 对象ResultSet resultSet= statement.executeQuery(sql);//5. 使用 while 取出数据while (resultSet.next()){// 让光标向后移动，如果没有更多行，则返回 falseint id= resultSet.getInt("id");String name=resultSet.getString("name");String sex=resultSet.getString("sex");Date date =resultSet.getDate("borndate");System.out.println(id + "\t" + name + "\t" + sex + "\t" + date);}
//关闭连接resultSet.close();statement.close();conn.close();}
}

-- 演示 sql 注入
-- 创建一张表
CREATE TABLE admin ( -- 管理员表
NAME VARCHAR(32) NOT NULL UNIQUE, pwd VARCHAR(32) NOT NULL DEFAULT '') CHARACTER SET utf8; -- 添加数据
INSERT INTO admin VALUES('tom', '123'); 
-- 查找某个管理是否存在SELECT *
FROM admin
WHERE NAME = 'tom' AND pwd = '123';-- SQL
-- 输入用户名 为 1'or
-- 输入万能密码 为 or '1'= '1
SELECT *
FROM admin
WHERE NAME = '1' OR' AND pwd = 'OR '1'= '1' SELECT * FROM admin

​

package com.hspedu.jdbc.statement_;import com.mysql.jdbc.Driver;import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;/*** @author 林然* @version 1.0* 演示 statement 的注入问题*/
public class Statement_ {public static void main(String[] args) throws IOException, ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {Scanner scanner = new Scanner(System.in);//让用户输入管理员名和密码System.out.print("请输入管理员的名字: "); //next(): 当接收到 空格或者 '就是表示结束String admin_name = scanner.nextLine(); // 老师说明，如果希望看到 SQL 注入，这里需要用 nextLineSystem.out.print("请输入管理员的密码: ");String admin_pwd = scanner.nextLine();Properties properties=new Properties();properties.load(new FileInputStream("src\\mysql.properties"));//通过 Properties 对象获取配置文件的信息String user = properties.getProperty("user");String password =properties.getProperty("password");String driver =properties.getProperty("driver");String url= properties.getProperty("url");//1. 注册驱动Class<?> aClass = Class.forName(driver);Driver driver1 =(Driver)aClass.newInstance();DriverManager.registerDriver(driver1);//2. 得到连接Connection conn =DriverManager.getConnection(url,user,password);//3. 得到 StatementStatement statement =conn.createStatement();//4. 组织 SqLString sql = "select name , pwd from admin where name ='"+admin_name+"' and pwd = '"+admin_pwd+"'";ResultSet resultSet =statement.executeQuery(sql);if(resultSet.next()){System.out.println("恭喜， 登录成功");}else {System.out.println("对不起，登录失败");}//关闭连接resultSet.close();statement.close();conn.close();}
}

package com.hspedu.jdbc.preparedstatement_;import com.mysql.jdbc.Driver;import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;/*** @author 林然* @version 1.0* 演示 PreparedStatement 使用*/
public class PreparedStatement_ {public static void main(String[] args) throws IOException, ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {Scanner scanner = new Scanner(System.in);//让用户输入管理员名和密码System.out.print("请输入管理员的名字: "); //next(): 当接收到 空格或者 '就是表示结束String admin_name = scanner.nextLine(); // 老师说明，如果希望看到 SQL 注入，这里需要用 nextLineSystem.out.print("请输入管理员的密码: ");String admin_pwd = scanner.nextLine();Properties properties=new Properties();properties.load(new FileInputStream("src\\mysql.properties"));//通过 Properties 对象获取配置文件的信息String user = properties.getProperty("user");String password =properties.getProperty("password");String driver =properties.getProperty("driver");String url= properties.getProperty("url");//1. 注册驱动Class<?> aClass = Class.forName(driver);Driver driver1 =(Driver)aClass.newInstance();DriverManager.registerDriver(driver1);//2. 得到连接Connection conn =DriverManager.getConnection(url,user,password);//3. 得到 PreparedStatement//3.1 组织 SqL , Sql 语句的 ? 就相当于占位符String sql = "select name , pwd from admin where name = ? and pwd = ?";//3.2 preparedStatement 对象实现了 PreparedStatement 接口的实现类的对象PreparedStatement preparedStatement =conn.prepareStatement(sql);//3.3 给 ? 赋值preparedStatement.setString(1,admin_name);preparedStatement.setString(2,admin_pwd);//4. 执行 select 语句使用 executeQuery// 如果执行的是 dml(update, insert ,delete) executeUpdate()// 这里执行 executeQuery ,不要在写 sqlResultSet resultSet =preparedStatement.executeQuery();if(resultSet.next()){System.out.println("恭喜， 登录成功");}else {System.out.println("对不起，登录失败");}String sql1="insert into admin values(?,?)";PreparedStatement preparedStatement1 =conn.prepareStatement(sql1);preparedStatement1.setString(1,"linran");preparedStatement1.setString(2,"123456");//执行 dml 语句使用 executeUpdateint rows=preparedStatement1.executeUpdate();System.out.println(rows > 0 ? "执行成功" : "执行失败");//关闭连接preparedStatement1.close();resultSet.close();preparedStatement.close();conn.close();}
}

package com.hspedu.jdbc.utils;import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;/*** @author 林然* @version 1.0* 这是一个工具类，完成 mysql 的连接和关闭资源*/
public class JDBCUtils {//定义相关的属性(4 个), 因为只需要一份，因此，我们做出 staticprivate static String user; //用户名private static String password; //密码private static String url; //urlprivate static String driver; //驱动名//在 static 代码块去初始化static {try {Properties properties = new Properties();properties.load(new FileInputStream("src\\mysql.properties"));//读取相关的属性值user = properties.getProperty("user");password = properties.getProperty("password");url = properties.getProperty("url");driver = properties.getProperty("driver");} catch (IOException e) {//在实际开发中，我们可以这样处理//1. 将编译异常转成 运行异常//2. 调用者，可以选择捕获该异常，也可以选择默认处理该异常，比较方便.throw new RuntimeException(e);}}//连接数据库, 返回 Connectionpublic  static Connection getConnection() {try {Class<?> aClass = Class.forName(driver);Driver driver1 =(Driver)aClass.newInstance();DriverManager.registerDriver(driver1);return DriverManager.getConnection(url, user, password);} catch (SQLException | ClassNotFoundException | InstantiationException | IllegalAccessException e) {//1. 将编译异常转成 运行异常//2. 调用者，可以选择捕获该异常，也可以选择默认处理该异常，比较方便.throw new RuntimeException(e);}}//关闭相关资源/*1. ResultSet 结果集2. Statement 或者 PreparedStatement3. Connection4. 如果需要关闭资源，就传入对象，否则传入 null*/public static void close(ResultSet set, Statement statement, Connection connection) {
//判断是否为 nulltry {if (set != null) {set.close();}if (statement != null) {statement.close();}if (connection != null) {connection.close();}} catch (SQLException e) {
//将编译异常转成运行异常抛出throw new RuntimeException(e);}}}

package com.hspedu.jdbc.utils;import org.junit.Test;import java.sql.*;/*** @author 林然* @version 1.0* 该类演示如何使用 JDBCUtils 工具类，完成 dml 和 select*/
public class JDBCUtils_Use {@Testpublic void testSelect() {//1. 得到连接Connection connection=JDBCUtils.getConnection();//2. 组织一个 sqlString sql="select * from actor where id=?";PreparedStatement preparedStatement=null;ResultSet set = null;try {preparedStatement=connection.prepareStatement(sql);preparedStatement.setInt(1,2);set = preparedStatement.executeQuery();while (set.next()){int id = set.getInt("id");String name = set.getString("name");String sex = set.getString("sex");Date borndate = set.getDate("borndate");String phone = set.getString("phone");System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);}} catch (SQLException throwables) {throwables.printStackTrace();} finally {//关闭资源JDBCUtils.close(set, preparedStatement, connection);}}@Testpublic void testDML() {//insert , update, delete//得到连接Connection connection = (Connection) JDBCUtils.getConnection();//2. 组织一个 sqlString sql = "update actor set name = ? where id = ?";// 测试 delete 和 insert ,自己玩. PreparedStatement preparedStatement = null;//3. 创建 PreparedStatement 对象PreparedStatement preparedStatement=null;try {preparedStatement = (PreparedStatement) connection.prepareStatement(sql);preparedStatement.setString(1,"周星驰");preparedStatement.setInt(2,2);preparedStatement.executeUpdate();} catch (SQLException throwables) {throwables.printStackTrace();} finally {//关闭JDBCUtils.close(null,preparedStatement,connection);}}
}

package com.hspedu.jdbc.transaction_;import com.hspedu.jdbc.utils.JDBCUtils;
import org.junit.Test;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;/*** @author 林然* @version 1.0*/
public class Transaction_ {//没有使用事务.@Testpublic void noTransaction() {//操作转账的业务//1. 得到连接Connection connection = null;//2. 组织一个 sqlString sql = "update account set banlance = banlance - 100 where id = 1";String sql2 = "update account set banlance = banlance + 100 where id = 2";PreparedStatement preparedStatement = null;//3. 创建 PreparedStatement 对象try {connection = JDBCUtils.getConnection(); // 在默认情况下，connection 是默认自动提交preparedStatement = connection.prepareStatement(sql);preparedStatement.executeUpdate(); // 执行第 1 条 sqlint i = 1 / 0; //抛出异常preparedStatement = connection.prepareStatement(sql2);preparedStatement.executeUpdate(); // 执行第 3 条 sql} catch (SQLException e) {e.printStackTrace();} finally {//关闭资源JDBCUtils.close(null, preparedStatement, connection);}}//事务来解决@Testpublic void useTransaction() {//操作转账的业务//1. 得到连接Connection connection = null;//2. 组织一个 sqlString sql = "update account set banlance = banlance - 100 where id = 1";String sql2 = "update account set banlance = banlance + 100 where id = 2";PreparedStatement preparedStatement = null;//3. 创建 PreparedStatement 对象try {connection = JDBCUtils.getConnection(); // 在默认情况下，connection 是默认自动提交//将 connection 设置为不自动提交connection.setAutoCommit(false); //开启了事务preparedStatement = connection.prepareStatement(sql);preparedStatement.executeUpdate(); // 执行第 1 条 sqlint i = 1 / 0; //抛出异常preparedStatement = connection.prepareStatement(sql2);preparedStatement.executeUpdate(); // 执行第 3 条 sql//这里提交事务connection.commit();} catch (SQLException e) {e.printStackTrace();//这里我们可以进行回滚，即撤销执行的 SQL
//默认回滚到事务开始的状态. System.out.println("执行发生了异常，撤销执行的 sql");try {connection.rollback();} catch (SQLException throwables) {throwables.printStackTrace();}e.printStackTrace();} finally {//关闭资源JDBCUtils.close(null, preparedStatement, connection);}}}

package com.hspedu.jdbc.batch_;import com.hspedu.jdbc.utils.JDBCUtils;
import org.junit.Test;import java.sql.Connection;
import java.sql.PreparedStatement;/*** @author 林然* @version 1.0* 演示 java 的批处理*/
public class Batch_ {//传统方法，添加 5000 条数据到 admin2@Testpublic void noBatch() throws Exception {Connection connection = JDBCUtils.getConnection();String sql = "insert into admin2 values(null, ?, ?)";PreparedStatement preparedStatement = connection.prepareStatement(sql);System.out.println("开始执行");long start = System.currentTimeMillis();//开始时间for (int i = 0; i < 5000; i++) {//5000 执行preparedStatement.setString(1, "jack" + i);preparedStatement.setString(2, "666");preparedStatement.executeUpdate();}long end = System.currentTimeMillis();System.out.println("传统的方式 耗时=" + (end - start));//传统的方式 耗时=10702//关闭连接JDBCUtils.close(null, preparedStatement, connection);}//使用批量方式添加数据@Testpublic void batch() throws Exception {Connection connection = JDBCUtils.getConnection();String sql = "insert into admin2 values(null, ?, ?)";PreparedStatement preparedStatement = connection.prepareStatement(sql);System.out.println("开始执行");long start = System.currentTimeMillis();//开始时间for (int i = 0; i < 5000; i++) {//5000 执行preparedStatement.setString(1, "jack" + i);preparedStatement.setString(2, "666");//将sql语句加入p处理preparedStatement.addBatch();//当有 1000 条记录时，在批量执行if((i + 1) % 1000 == 0) {//满 1000 条 sqlpreparedStatement.executeBatch();//清空一把preparedStatement.clearBatch();}}long end = System.currentTimeMillis();System.out.println("批量方式 耗时=" + (end - start));//批量方式 耗时=108//关闭连接JDBCUtils.close(null, preparedStatement, connection);}
}

package com.hspedu.jdbc.datasource;import com.hspedu.jdbc.utils.JDBCUtils;
import org.junit.Test;import java.sql.Connection;/*** @author 林然* @version 1.0*/
public class ConQuestion {//代码 连接 mysql 5000 次@Testpublic void testCon() {//看看连接-关闭 connection 会耗用多久long start = System.currentTimeMillis();System.out.println("开始连接.....");for (int i = 0; i < 5000; i++) {//使用传统的 jdbc 方式，得到连接Connection connection = JDBCUtils.getConnection();//做一些工作，比如得到 PreparedStatement ，发送 sql//.......... //关闭JDBCUtils.close(null, null, connection);}long end = System.currentTimeMillis();System.out.println("传统方式 5000 次 耗时=" + (end - start));//传统方式 5000 次 耗时=7099}
}

package com.hspedu.jdbc.datasource;import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;import java.beans.PropertyVetoException;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;/*** @author 林然* @version 1.0* 演示 c3p0 的使用*/
public class C3P0_ {//方式 1： 相关参数，在程序中指定 user, url , password 等@Testpublic  void testC3P0_01() throws IOException, PropertyVetoException, SQLException {//1 建立一个数据源对象ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();//2. 通过配置文件 mysql.properties 获取相关连接的信息Properties properties = new Properties();properties.load(new FileInputStream("src\\mysql.properties"));//读取相关的属性值String user = properties.getProperty("user");String password = properties.getProperty("password");String url = properties.getProperty("url");String driver = properties.getProperty("driver");//给数据源 comboPooledDataSource 设置相关的参数//注意：连接管理是由 comboPooledDataSource 来管理comboPooledDataSource.setDriverClass(driver);comboPooledDataSource.setJdbcUrl(url);comboPooledDataSource.setUser(user);comboPooledDataSource.setPassword(password);//设置初始化连接数comboPooledDataSource.setInitialPoolSize(10);//最大连接数comboPooledDataSource.setMaxPoolSize(50);//测试连接池的效率, 测试对 mysql 5000 次操作long start = System.currentTimeMillis();for (int i = 0; i < 5000; i++) {Connection connection = comboPooledDataSource.getConnection();//这个方法就是从 DataSource 接口实现的//System.out.println("连接 OK");connection.close();}long end = System.currentTimeMillis();//c3p0 5000 连接 mysql 耗时=391System.out.println("c3p0 5000 连接 mysql 耗时=" + (end - start));}//第二种方式 使用配置文件模板来完成//1. 将 c3p0 提供的 c3p0.config.xml 拷贝到 src 目录下//2. 该文件指定了连接数据库和连接池的相关参数@Testpublic void testC3P0_02() throws SQLException {ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("lin_ran");//测试 5000 次连接 mysqllong start = System.currentTimeMillis();System.out.println("开始执行....");for (int i = 0; i < 5000; i++) {Connection connection = comboPooledDataSource.getConnection();//System.out.println("连接 OK~");connection.close();}long end = System.currentTimeMillis();//c3p0 的第二种方式 耗时=413System.out.println("c3p0 的第二种方式(5000) 耗时=" + (end - start));//1917}
}