文章目录
MySQL准备
JdbcTemplate
c3p0
mysql驱动
spring jar包
ID
LAST_NAME
EMAIL
DEPT_ID
drop database if exists spring4 ;
create database spring4 charset utf8;
use spring4 ;
create table emp(
ID int auto_increment primary key ,
LAST_NAME varchar(20) not null,
EMAIL varchar(50) ,
DEPT_ID int
) ;
desc emp;
insert into emp values(
null ,
"张珊",
"1191693505@qq.com",
1
) ,(
null ,
"李四",
"7402033595@qq.com",
1
) ,(
null ,
"王五",
"5955459555@qq.com",
2
) ;
select * from emp ;
create table dept (
id int auto_increment primary key ,
name varchar(20) not null
) ;
desc dept ;
insert into dept values(
null,
"销售部"
),(
null,
"生产部"
),(
null,
"财务部"
);
select * from dept ;
目录
db.properties
jdbc.user=root
jdbc.password=root
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql:///spring4?characterEncoding=utf8
jdbc.initialPoolSize=5
jdbc.maxPoolSize=10
applicationContext-jdbc.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">
<!-- 导入资源文件 -->
<context:property-placeholder location="classpath:vedio/db.properties"/>
<!-- 配置C3P0数据源 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="user" value="${jdbc.user}"></property>
<property name="password" value="${jdbc.password}"></property>
<property name="driverClass" value="${jdbc.driverClass}"></property>
<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
<property name="initialPoolSize" value="${jdbc.initialPoolSize}"></property>
<property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>
</bean>
<!-- 配置 Spring 的 JdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- Dao -->
<context:component-scan base-package="vedio.dao"></context:component-scan>
</beans>
Employee
package vedio;
public class Employee {
private Integer id ;
private String name ;
private String email ;
private Department department ;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Department getDepartment() {
return department;
}
public void setDepartment(Department department) {
this.department = department;
}
@Override
public String toString() {
return "Employee [id=" + id + ", name=" + name + ", email=" + email + ", department=" + department + "]";
}
}
Department
package vedio;
public class Department {
private Integer id ;
private String name ;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Department [id=" + id + ", name=" + name + "]";
}
}
EmployeeDao
package vedio.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import vedio.Employee;
@Repository
public class EmployeeDao {
@Autowired
private JdbcTemplate jdbcTemplate ;
public Employee get(Integer id) {
String sql = "select id , last_name name , email from emp where id=? ; ";
RowMapper<Employee> rowMapper =
new BeanPropertyRowMapper<>(Employee.class);
Employee employee = jdbcTemplate.queryForObject(sql , rowMapper, id);
return employee ;
}
}
DepartmentDao
package vedio.dao;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import vedio.Department;
@Repository
public class DepartmentDao extends JdbcDaoSupport{
@Autowired
public void setDataSource2(DataSource dataSource) {
setDataSource(dataSource);
}
public Department get(int id) {
String sql = "select id , name from dept where id = ?" ;
RowMapper<Department> rowMapper =
new BeanPropertyRowMapper<Department>(Department.class);
return getJdbcTemplate().queryForObject(sql, rowMapper , id);
}
}
JDBCTest - 测试类
package video;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.sql.DataSource;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import vedio.Department;
import vedio.Employee;
import vedio.dao.DepartmentDao;
import vedio.dao.EmployeeDao;
/** * spring jdbc ... 另外一个c3p0 * */
public class JDBCTest {
private ClassPathXmlApplicationContext cpax = null ;
private JdbcTemplate jdbcTemplate = null ;
private EmployeeDao employeeDao = null ;
private DepartmentDao departmentDao = null ;
{
cpax = new ClassPathXmlApplicationContext(
"vedio/applicationContext-jdbc.xml") ;
jdbcTemplate = cpax.getBean(JdbcTemplate.class);
employeeDao = cpax.getBean(EmployeeDao.class);
departmentDao = cpax.getBean(DepartmentDao.class) ;
}
@Test
public void testDepartmentDaoGet() {
Department department = departmentDao.get(1);
System.out.println(department);
}
@Test
public void testEmployeeDaoGet() {
Employee employee = employeeDao.get(1);
System.out.println(employee);
}
/** * 执行批量更新：批量的 Insert，update ， delete * 最后一个阐述是object[] 的 list 类型。 */
//@Test
public void testBatchInsert() {
String sql = "INSERT INTO emp(last_name,email , dept_id) values(? , ? , ?) ; " ;
List<Object[]> list = new ArrayList<>();
list.add(new Object[]{"AA" , "aa@atguigu" , 1}) ;
list.add(new Object[]{"BB" , "bb@atguigu" , 2}) ;
list.add(new Object[]{"CC" , "cc@atguigu" , 1}) ;
list.add(new Object[]{"DD" , "dd@atguigu" , 1}) ;
list.add(new Object[]{"EE" , "ee@atguigu" , 1}) ;
jdbcTemplate.batchUpdate(sql, list);
}
//@Test
public void testBatchDelete() {
String sql = "DELETE FROM emp WHERE LAST_NAME = ? " ;
List<Object[]> list = new ArrayList<>() ;
list.add(new Object[] {"AA"}) ;
list.add(new Object[] {"BB"}) ;
list.add(new Object[] {"CC"}) ;
list.add(new Object[] {"DD"}) ;
list.add(new Object[] {"EE"}) ;
jdbcTemplate.batchUpdate(sql, list) ;
}
@Test
public void testUpdate() {
String sql = "update emp set last_name = ? where id = ? " ;
jdbcTemplate.update(sql , "李尔王" , 2 ) ;
}
@Test
public void testDataSource() throws SQLException {
DataSource dataSource = cpax.getBean(DataSource.class);
System.out.println(dataSource.getConnection());
}
/** * 获取单个列的值，或做统计查询 * 使用 方法 queryForObject(String sql, Class<?> requiredType) */
@Test
public void testQueryForObject2() {
String sql = "select count(id) from emp ; ";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println("总人数："+ count);
}
/** * 从数据库中获取一条记录，实际得到对应的一个对象 * * 注意，不是用 * queryForObject * (String sql , Class<?> requiredType, Object ... args ) * * 注意， * queryForObject(sql, RowMapper<?> rowMapper, Object... args) ) * ?中有构造方***报错 * * 1. 其中 RowMapper 指定如何去映射结构集的行，常用的实现类为BeanPropertyRowMapper * 2. 使用SQL中列的别名来完成列名和类的属性名的映射，例如 last_name 和 name * 3. 不支持级联属性，JdbcTemplate 到底是一个 JDBC的小工具，而不是 ORM 框架 */
@Test
public void testQueryForObject() {
String sql = "select id , last_name name , email from emp where id=?" ;
RowMapper<Employee> rowMapper =
new BeanPropertyRowMapper<>(Employee.class);
Employee employee = jdbcTemplate.queryForObject(sql, rowMapper , 1 );
System.out.println(employee);
}
/** * 查到实例类的集合 * 注意调用的不是 queryForList 方法 */
@Test
public void testQueryForListObject() {
String sql = "select id , last_name name , email from emp ; " ;
RowMapper<Employee> rowMapper =
new BeanPropertyRowMapper<>(Employee.class) ;
List<Employee > employes = jdbcTemplate.query(sql, rowMapper) ;
for (Employee employee : employes) {
System.out.println(employee);
}
}
}
十二月 16, 2019 10:53:49 下午 org.springframework.context.support.AbstractApplicationContext prepareRefresh
信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@7b3300e5: startup date [Mon Dec 16 22:53:49 CST 2019]; root of context hierarchy
十二月 16, 2019 10:53:49 下午 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
信息: Loading XML bean definitions from class path resource [vedio/applicationContext-jdbc.xml]
十二月 16, 2019 10:53:49 下午 org.springframework.core.io.support.PropertiesLoaderSupport loadProperties
信息: Loading properties file from class path resource [vedio/db.properties]
十二月 16, 2019 10:53:49 下午 com.mchange.v2.log.MLog
信息: MLog clients using java 1.4+ standard logging.
十二月 16, 2019 10:53:50 下午 com.mchange.v2.c3p0.C3P0Registry
信息: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
十二月 16, 2019 10:53:50 下午 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 -> 1hge13ba747u87c1w59zmu|341b80b2, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge13ba747u87c1w59zmu|341b80b2, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql:///spring4?characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, 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 ]
Employee [id=1, name=张珊, email=1191693505@qq.com, department=null]
十二月 16, 2019 10:53:50 下午 org.springframework.context.support.AbstractApplicationContext prepareRefresh
信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@1283bb96: startup date [Mon Dec 16 22:53:50 CST 2019]; root of context hierarchy
十二月 16, 2019 10:53:50 下午 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
信息: Loading XML bean definitions from class path resource [vedio/applicationContext-jdbc.xml]
十二月 16, 2019 10:53:50 下午 org.springframework.core.io.support.PropertiesLoaderSupport loadProperties
信息: Loading properties file from class path resource [vedio/db.properties]
十二月 16, 2019 10:53:50 下午 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 -> 1hge13ba747u87c1w59zmu|397fbdb, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge13ba747u87c1w59zmu|397fbdb, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql:///spring4?characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, 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 ]
Employee [id=1, name=张珊, email=1191693505@qq.com, department=null]
十二月 16, 2019 10:53:50 下午 org.springframework.context.support.AbstractApplicationContext prepareRefresh
信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@36fc695d: startup date [Mon Dec 16 22:53:50 CST 2019]; root of context hierarchy
十二月 16, 2019 10:53:50 下午 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
信息: Loading XML bean definitions from class path resource [vedio/applicationContext-jdbc.xml]
十二月 16, 2019 10:53:50 下午 org.springframework.core.io.support.PropertiesLoaderSupport loadProperties
信息: Loading properties file from class path resource [vedio/db.properties]
十二月 16, 2019 10:53:50 下午 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 -> 1hge13ba747u87c1w59zmu|662ac478, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge13ba747u87c1w59zmu|662ac478, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql:///spring4?characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, 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 ]
Department [id=1, name=销售部]
十二月 16, 2019 10:53:50 下午 org.springframework.context.support.AbstractApplicationContext prepareRefresh
信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@62ddbd7e: startup date [Mon Dec 16 22:53:50 CST 2019]; root of context hierarchy
十二月 16, 2019 10:53:50 下午 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
信息: Loading XML bean definitions from class path resource [vedio/applicationContext-jdbc.xml]
十二月 16, 2019 10:53:50 下午 org.springframework.core.io.support.PropertiesLoaderSupport loadProperties
信息: Loading properties file from class path resource [vedio/db.properties]
十二月 16, 2019 10:53:50 下午 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 -> 1hge13ba747u87c1w59zmu|7bedc48a, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge13ba747u87c1w59zmu|7bedc48a, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql:///spring4?characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, 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 ]
总人数：8
十二月 16, 2019 10:53:50 下午 org.springframework.context.support.AbstractApplicationContext prepareRefresh
信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@66d18979: startup date [Mon Dec 16 22:53:50 CST 2019]; root of context hierarchy
十二月 16, 2019 10:53:50 下午 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
信息: Loading XML bean definitions from class path resource [vedio/applicationContext-jdbc.xml]
十二月 16, 2019 10:53:50 下午 org.springframework.core.io.support.PropertiesLoaderSupport loadProperties
信息: Loading properties file from class path resource [vedio/db.properties]
十二月 16, 2019 10:53:50 下午 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 -> 1hge13ba747u87c1w59zmu|6f45df59, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge13ba747u87c1w59zmu|6f45df59, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql:///spring4?characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, 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@5f8edcc5 [wrapping: com.mysql.jdbc.JDBC4Connection@7b02881e]
十二月 16, 2019 10:53:50 下午 org.springframework.context.support.AbstractApplicationContext prepareRefresh
信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@1ebd319f: startup date [Mon Dec 16 22:53:50 CST 2019]; root of context hierarchy
十二月 16, 2019 10:53:50 下午 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
信息: Loading XML bean definitions from class path resource [vedio/applicationContext-jdbc.xml]
十二月 16, 2019 10:53:50 下午 org.springframework.core.io.support.PropertiesLoaderSupport loadProperties
信息: Loading properties file from class path resource [vedio/db.properties]
十二月 16, 2019 10:53:50 下午 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 -> 1hge13ba747u87c1w59zmu|574b560f, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge13ba747u87c1w59zmu|574b560f, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql:///spring4?characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, 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 ]
Employee [id=1, name=张珊, email=1191693505@qq.com, department=null]
Employee [id=2, name=李尔王, email=7402033595@qq.com, department=null]
Employee [id=3, name=王五, email=5955459555@qq.com, department=null]
Employee [id=4, name=AA, email=aa@atguigu, department=null]
Employee [id=5, name=BB, email=bb@atguigu, department=null]
Employee [id=6, name=CC, email=cc@atguigu, department=null]
Employee [id=7, name=DD, email=dd@atguigu, department=null]
Employee [id=8, name=EE, email=ee@atguigu, department=null]
十二月 16, 2019 10:53:50 下午 org.springframework.context.support.AbstractApplicationContext prepareRefresh
信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@3c947bc5: startup date [Mon Dec 16 22:53:50 CST 2019]; root of context hierarchy
十二月 16, 2019 10:53:50 下午 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
信息: Loading XML bean definitions from class path resource [vedio/applicationContext-jdbc.xml]
十二月 16, 2019 10:53:50 下午 org.springframework.core.io.support.PropertiesLoaderSupport loadProperties
信息: Loading properties file from class path resource [vedio/db.properties]
十二月 16, 2019 10:53:50 下午 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 -> 1hge13ba747u87c1w59zmu|770d3326, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge13ba747u87c1w59zmu|770d3326, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, jdbcUrl -> jdbc:mysql:///spring4?characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, 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 ]
NamedParameterJdbcTemplate
具名参数，具有名字的参数
Employee_deptId extends Employee
package vedio;
public class Employee_deptId extends Employee {
private Integer dept_id ;
public Integer getDept_id() {
return dept_id;
}
public void setDept_id(Integer dept_id) {
this.dept_id = dept_id;
}
@Override
public String toString() {
return "Employee_deptId [getDept_id()=" + getDept_id() + ", getId()=" + getId() + ", getName()=" + getName()
+ ", getEmail()=" + getEmail() + ", getDepartment()=" + getDepartment() + ", toString()="
+ super.toString() + ", getClass()=" + getClass() + ", hashCode()=" + hashCode() + "]";
}
}
Test
package video;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.junit.Test;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import vedio.Department;
import vedio.Employee;
import vedio.Employee_deptId;
import vedio.dao.DepartmentDao;
import vedio.dao.EmployeeDao;
/** * spring jdbc ... 另外一个c3p0 * */
public class JDBCTest {
private ClassPathXmlApplicationContext cpax = null ;
private JdbcTemplate jdbcTemplate = null ;
private EmployeeDao employeeDao = null ;
private DepartmentDao departmentDao = null ;
private NamedParameterJdbcTemplate namedParameterJdbcTemplate = null ;
{
cpax = new ClassPathXmlApplicationContext(
"vedio/applicationContext-jdbc.xml") ;
jdbcTemplate = cpax.getBean(JdbcTemplate.class);
employeeDao = cpax.getBean(EmployeeDao.class);
departmentDao = cpax.getBean(DepartmentDao.class) ;
namedParameterJdbcTemplate =
cpax.getBean(NamedParameterJdbcTemplate.class) ;
}
/** * 使用具名参数时，可以使用 update(String sql , SqlParameterSource paramSource) 方法进行更新操作 * 1. SQL 语句中的参数名和类的属性一致 * 2. 使用 SqlParameterSource 的BeanPropertySqlParameterSource 实现类作为参数 */
@Test
public void testNamedParameterJdbcTemplateInsert2() {
String sql = "Insert Into emp(last_name, email , dept_id) values(:name, :email , :dept_id);" ;
Employee_deptId emp = new Employee_deptId();
emp.setName("尼古拉斯");
emp.setEmail("22222222222@赵四");
emp.setDept_id(2);
SqlParameterSource paramSource = new BeanPropertySqlParameterSource(emp);
namedParameterJdbcTemplate.update(sql, paramSource) ;
}
/** * 可以为参数起名了！ * 1. 好处：若有多个参数，则不用再去对应位置，直接对应参数名，便于维护。 * 2. 缺点：较为麻烦 （x） */
//@Test
public void testNamedParameterJdbcTemplateInsert() {
//冒号
//Insert Into emp(last_name, email , dept_id) values(:ln, :email , :deptid)
String sql = "Insert Into emp(last_name, email , dept_id) values(:ln, :email , :deptid)";
Map<String, Object > paramMap = new HashMap<>() ;
paramMap.put("ln", "路飞") ;
paramMap.put("email", "1111111111@qq.com") ;
paramMap.put("deptid", "1") ;
namedParameterJdbcTemplate.update(sql, paramMap) ;
}
@Test
public void testDepartmentDaoGet() {
Department department = departmentDao.get(1);
System.out.println(department);
}
@Test
public void testEmployeeDaoGet() {
Employee employee = employeeDao.get(1);
System.out.println(employee);
}
/** * 执行批量更新：批量的 Insert，update ， delete * 最后一个阐述是object[] 的 list 类型。 */
//@Test
public void testBatchInsert() {
String sql = "INSERT INTO emp(last_name,email , dept_id) values(? , ? , ?) ; " ;
List<Object[]> list = new ArrayList<>();
list.add(new Object[]{"AA" , "aa@atguigu" , 1}) ;
list.add(new Object[]{"BB" , "bb@atguigu" , 2}) ;
list.add(new Object[]{"CC" , "cc@atguigu" , 1}) ;
list.add(new Object[]{"DD" , "dd@atguigu" , 1}) ;
list.add(new Object[]{"EE" , "ee@atguigu" , 1}) ;
jdbcTemplate.batchUpdate(sql, list);
}
//@Test
public void testBatchDelete() {
String sql = "DELETE FROM emp WHERE LAST_NAME = ? " ;
List<Object[]> list = new ArrayList<>() ;
list.add(new Object[] {"AA"}) ;
list.add(new Object[] {"BB"}) ;
list.add(new Object[] {"CC"}) ;
list.add(new Object[] {"DD"}) ;
list.add(new Object[] {"EE"}) ;
jdbcTemplate.batchUpdate(sql, list) ;
}
@Test
public void testUpdate() {
String sql = "update emp set last_name = ? where id = ? " ;
jdbcTemplate.update(sql , "李尔王" , 2 ) ;
}
@Test
public void testDataSource() throws SQLException {
DataSource dataSource = cpax.getBean(DataSource.class);
System.out.println(dataSource.getConnection());
}
/** * 获取单个列的值，或做统计查询 * 使用 方法 queryForObject(String sql, Class<?> requiredType) */
@Test
public void testQueryForObject2() {
String sql = "select count(id) from emp ; ";
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println("总人数："+ count);
}
/** * 从数据库中获取一条记录，实际得到对应的一个对象 * * 注意，不是用 * queryForObject * (String sql , Class<?> requiredType, Object ... args ) * * 注意， * queryForObject(sql, RowMapper<?> rowMapper, Object... args) ) * ?中有构造方***报错 * * 1. 其中 RowMapper 指定如何去映射结构集的行，常用的实现类为BeanPropertyRowMapper * 2. 使用SQL中列的别名来完成列名和类的属性名的映射，例如 last_name 和 name * 3. 不支持级联属性，JdbcTemplate 到底是一个 JDBC的小工具，而不是 ORM 框架 */
@Test
public void testQueryForObject() {
String sql = "select id , last_name name , email from emp where id=?" ;
RowMapper<Employee> rowMapper =
new BeanPropertyRowMapper<>(Employee.class);
Employee employee = jdbcTemplate.queryForObject(sql, rowMapper , 1 );
System.out.println(employee);
}
/** * 查到实例类的集合 * 注意调用的不是 queryForList 方法 */
@Test
public void testQueryForListObject() {
String sql = "select id , last_name name , email from emp ; " ;
RowMapper<Employee> rowMapper =
new BeanPropertyRowMapper<>(Employee.class) ;
List<Employee > employes = jdbcTemplate.query(sql, rowMapper) ;
for (Employee employee : employes) {
System.out.println(employee);
}
}
}
京公网安备 11010502036488号