JDBC - 2 - 学习记录

JBDC day02 note

回顾：
JDBC - 连接数据库的规范
		使用到的类
			Connection
			Statement
			ResultSet

连接数据库的步骤
	1.导入数据驱动的jar包
	2.加载驱动类
		Class.forName("oracle.jdbc.dirver.OracleDriver")
								"com.mysql.jdbc.Dirver"
	3.创建Connection 连接
		DriverManager.getConnection(url,user, password )
		url:jdbc.mysql://ip:port/datebase
			jdbc:oracle:thin:@ip:port:sid
	4.获得Statement 对象
		conn.createStatement();
		stmt.excute();  				-- DML DDL DQL
		stmt.executeUpdate();		-- DML
		stmt.executeQuery();		-- DQL
								- ResultSet
	5.处理结果集
		rs.next() - 询问并获得下一个
		rs.getInt/Double/String/Date(columName/index)
	6.关闭资源

解决数据库中硬编码 - driver url user password
	使用Properties配置文件 -> Hashtable / Hashmap 
		new Properties(); -> 读取 xx.properties 配置文件
		XML - 解析 - 读内容 - 使用Dom4j
		
批量执行SQL语句
	批量导入数据 - excel - 读取excel表
	stmt.addBatch(); 		 // 批量添加
	stmt.clearBatch();		 // 清空Batch 
	stmt.executeBatch();	 // 提交Batch
	
分页查询 - select * from user 分页;
	oracle: rownum 5~10
		select * from
			(select rownum n, * from user) u
		where u.n between 5 and 10;
	mysql: 
		select * from user limit startRow, pageSize;
		select * from user limit 30, 50;
			将前80条全部查询，取最后50条
			弊端：数据量越大，页数越大，效率越低

SQL注入：通过字符串拼接，拼接成一个恒成立的条件，改变了原来的语义
	解决SQL注入：使用预编译PrepareedStatement
			半成品SQL，先执行预编译，确定了语义，传参作为一个整体

JDBC 连接池
DBCP
C#P0

DataSource：连接池、数据源
连接池：减少频繁与数据库建立连接和关闭
	  连接的集合 -> 创建连接池时初始化若干个连接对象
		getConnection() -> 返回给一个连接
		close()	-> 返回给连接池
 DBCP：apache -> 开源 
	  dbcp/pool
	  BasicDataSource
 C3P0
 	  ComboPooledDataSource

DBCP 连接池 Demo 代码

import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DateSourceDemo01 {
	public static void main(String[] args) throws Exception {
		
		
		// 2.配置文件中的属性名，按规定填写
		Properties pros = new Properties();
		pros.load(new FileInputStream(
				DateSourceDemo01.class.getResource("dbcpconfig.properties").getFile()));
		
		// 1.使用工厂方法创建数据源，必须提供配置文件
		DataSource dateSource = BasicDataSourceFactory.createDataSource(pros);
		Connection conn = dateSource.getConnection();
		
		System.out.println(conn);
		conn.close(); // null -> 连接对象回到了连接池中
		System.out.println(conn);
		
	}
}

C3P0 连接池 Demo 代码

package day02;

import java.sql.Connection;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DateSourceDemo02 {
	public static void main(String[] args) throws Exception {
		// c3p0 连接池 -> 默认使用无参构造器
		// 				 使用有参构造器时，传入xml配置文件的其他内容
		ComboPooledDataSource db = new ComboPooledDataSource();
//		ComboPooledDataSource db1 = new ComboPooledDataSource("oracle");
		// 建立连接
		Connection conn = db.getConnection();
//		Connection conn1 = db1.getConnection();
		System.out.println(conn);	
//		System.out.println(conn1);
		
		/*
		 *  八月 01, 2019 4:24:42 下午 com.mchange.v2.log.MLog 
			信息: MLog clients using java 1.4+ standard logging.
			八月 01, 2019 4:24:43 下午 com.mchange.v2.c3p0.C3P0Registry 
			信息: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
			八月 01, 2019 4:24:43 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
			信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hgeby9a4l5y9tp1q8z20d|1698c449, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hgeby9a4l5y9tp1q8z20d|1698c449, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql:///test, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]
			com.mchange.v2.c3p0.impl.NewProxyConnection@6956de9 [wrapping: com.mysql.jdbc.JDBC4Connection@769c9116]
			
			控制台输出红字代表信息，日后常见，不是异常，不必惊慌
		 */
	}
}