前言
在MyBatis学习-连接oracle实现CURD操作实现了MyBatis基本配置与CRUD操作。但是每次都是手工创建SqlSessionFactory本篇将通过spring来管理bean,同时使用Druid连接池替换自带的连接池。
什么是Druid连接池
Druid是一个JDBC组件,它包括三部分:
- DruidDriver 代理Driver,能够提供基于Filter-Chain模式的插件体系。
- DruidDataSource 高效可管理的数据库连接池。
- SQLParser
Druid可以做什么?
- 可以监控数据库访问性能,Druid内置提供了一个功能强大的StatFilter插件,能够详细统计SQL的执行性能,这对于线上分析数据库访问性能有帮助。
- 替换DBCP和C3P0。Druid提供了一个高效、功能强大、可扩展性好的数据库连接池。
- 数据库密码加密。直接把数据库密码写在配置文件中,这是不好的行为,容易导致安全问题。DruidDruiver和DruidDataSource都支持PasswordCallback。
- SQL执行日志,Druid提供了不同的LogFilter,能够支持Common-Logging、Log4j和JdkLog,你可以按需要选择相应的LogFilter,监控你应用的数据库访问情况。
扩展JDBC,如果你要对JDBC层有编程的需求,可以通过Druid提供的Filter-Chain机制,很方便编写JDBC层的扩展插件。
导入库包
连接oracle
如果我们要连接oracle数据库,需要导入oralce的jdbc的包。但是由于oracle收费, 因此maven没有oracle库包,需要我们自己手工导入外部包。或者也可以将oracle的jar导入到maven库中。具体导入步骤可以查看Maven添加Oracle的依赖及驱动
| <dependency> |
| <groupId>com.oracle.jdbc</groupId> |
| <artifactId>ojdbc6</artifactId> |
| <version>11.2.0.1.0</version> |
| </dependency> |
连接mysql
由于mysql是免费的,我们可以通过maven直接安装mysql的jdbc数据库连接包
| <dependency> |
| <groupId>mysql</groupId> |
| <artifactId>mysql-connector-java</artifactId> |
| <version>8.0.21</version> |
| </dependency> |
导入mybatis
| <dependency> |
| <groupId>org.mybatis</groupId> |
| <artifactId>mybatis</artifactId> |
| <version>3.5.5</version> |
| </dependency> |
导入druid
| <dependency> |
| <groupId>com.alibaba</groupId> |
| <artifactId>druid</artifactId> |
| <version>1.1.11</version> |
| </dependency> |
导入spring-jdbc包
| <dependency> |
| <groupId>org.springframework</groupId> |
| <artifactId>spring-jdbc</artifactId> |
| <version>5.2.8.RELEASE</version> |
| </dependency> |
导入spring包
| <dependency> |
| <groupId>org.springframework</groupId> |
| <artifactId>spring-context</artifactId> |
| <version>5.2.8.RELEASE</version> |
| </dependency> |
导入spring事务相关包
| <dependency> |
| <groupId>org.springframework</groupId> |
| <artifactId>spring-tx</artifactId> |
| <version>5.2.8.RELEASE</version> |
| </dependency> |
| <dependency> |
| <groupId>org.springframework</groupId> |
| <artifactId>spring-jdbc</artifactId> |
| <version>5.2.8.RELEASE</version> |
| </dependency> |
导入mybatis-spring整合包
| |
| <dependency> |
| <groupId>org.mybatis</groupId> |
| <artifactId>mybatis-spring</artifactId> |
| <version>1.3.1</version> |
| </dependency> |
配置
下面使用过mysql数据库为例。
数据库配置
在resources目录下新建一个mysql.properities文件,用于配置连接数据库的相关配置。
| druid.url=jdbc:mysql://localhost:3306/mysql?serverTimezone=UTC |
| |
| druid.driverClassName=com.mysql.cj.jdbc.Driver |
| druid.username=root |
| druid.password=123456 |
| |
| |
| druid.initialSize=10 |
| |
| druid.maxActive=30 |
| |
| druid.minIdle=10 |
| |
| druid.maxWait=2000 |
| |
| druid.poolPreparedStatements=true |
| |
| druid.maxOpenPreparedStatements=20 |
druid配置
在resources目录下新建一个applicationContext-mysql.xml文件,用于配置mysql的druid的数据库连接池配置以及注入到spring的bean。
- 数据源的配置从mysql.propertie获取的
| |
| <context:property-placeholder location="classpath:mysql.properties" /> |
| |
| <bean name="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource"> |
| <property name="url" value="${druid.url}" /> |
| <property name="driverClassName" value="${druid.driverClassName}" /> |
| <property name="username" value="${druid.username}" /> |
| <property name="password" value="${druid.password}" /> |
| <property name="initialSize" value="${druid.initialSize}"/> |
| <property name="maxActive" value="${druid.maxActive}" /> |
| <property name="minIdle" value="${druid.minIdle}" /> |
| <property name="maxWait" value="${druid.maxWait}" /> |
| <property name="poolPreparedStatements" value="${druid.poolPreparedStatements}" /> |
| <property name="maxOpenPreparedStatements" value="${druid.maxOpenPreparedStatements}" /> |
| </bean> |
- spring管理事务
| |
| |
| <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> |
| <property name="dataSource" ref="druidDataSource"/> |
| </bean> |
| |
| <tx:annotation-driven transaction-manager="transactionManager"/> |
- 根据mapper生成代理
sqlSessionFactory需要注入数据源和配置文件路径,spring会生成runoob_tblMapper
,我们通过这个值取bean就能对数据库进行操作了。
| |
| |
| |
| <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> |
| |
| <property name="dataSource" ref="druidDataSource"/> |
| <property name="configLocation" value="classpath:conf.xml"/> |
| </bean> |
| |
| |
| |
| <bean id="runoob_tblMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"> |
| <property name="mapperInterface" value="mysql.dao.runoob_tblMapper"/> |
| <property name="sqlSessionFactory" ref="sqlSessionFactory" /> |
| </bean> |
完整配置如下
| <context:property-placeholder location="classpath:mysql.properties" /> |
| |
| |
| <bean name="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource"> |
| <property name="url" value="${druid.url}" /> |
| <property name="driverClassName" value="${druid.driverClassName}" /> |
| <property name="username" value="${druid.username}" /> |
| <property name="password" value="${druid.password}" /> |
| <property name="initialSize" value="${druid.initialSize}"/> |
| <property name="maxActive" value="${druid.maxActive}" /> |
| <property name="minIdle" value="${druid.minIdle}" /> |
| <property name="maxWait" value="${druid.maxWait}" /> |
| <property name="poolPreparedStatements" value="${druid.poolPreparedStatements}" /> |
| <property name="maxOpenPreparedStatements" value="${druid.maxOpenPreparedStatements}" /> |
| </bean> |
| |
| |
| <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> |
| <property name="dataSource" ref="druidDataSource"/> |
| </bean> |
| |
| <tx:annotation-driven transaction-manager="transactionManager"/> |
| |
| |
| |
| |
| |
| <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> |
| |
| <property name="dataSource" ref="druidDataSource"/> |
| <property name="configLocation" value="classpath:conf.xml"/> |
| </bean> |
| |
| |
| |
| <bean id="runoob_tblMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"> |
| <property name="mapperInterface" value="mysql.dao.runoob_tblMapper"/> |
| <property name="sqlSessionFactory" ref="sqlSessionFactory" /> |
| </bean> |
- 添加conf.xml 配置映射的文件
| <configuration> |
| <mappers> |
| <mapper resource="mapper/runoob_tblMapper.xml"></mapper> |
| </mappers> |
| </configuration> |
| |
- 在resources/mapper下添加runoob_tblMapper.xml
| |
| <mapper namespace="mysql.dao.runoob_tblMapper"> |
| |
| <insert id="insert" parameterType="mysql.dto.runoob_tbl" keyProperty="runoob_id" useGeneratedKeys="true"> |
| insert into runoob_tbl(runoob_title, runoob_author, submission_date) values(#{runoob_title},#{runoob_author},#{submission_date}) |
| </insert> |
| </mapper> |
添加一个dto
在mysql.dto添加类
| |
| public class runoob_tbl { |
| |
| public String runoob_id; |
| public String runoob_title; |
| public String runoob_author; |
| public Date submission_date; |
| @Override |
| public String toString() { |
| return this.runoob_id + "," + this.runoob_title + "," + this.runoob_author + "," + this.submission_date; |
| } |
| |
| public String getRunoob_id() { |
| return runoob_id; |
| } |
| |
| public void setRunoob_id(String runoob_id) { |
| this.runoob_id = runoob_id; |
| } |
| |
| public String getRunoob_title() { |
| return runoob_title; |
| } |
| |
| public void setRunoob_title(String runoob_title) { |
| this.runoob_title = runoob_title; |
| } |
| |
| public String getRunoob_author() { |
| return runoob_author; |
| } |
| |
| public void setRunoob_author(String runoob_author) { |
| this.runoob_author = runoob_author; |
| } |
| |
| public Date getSubmission_date() { |
| return submission_date; |
| } |
| |
| public void setSubmission_date(Date submission_date) { |
| this.submission_date = submission_date; |
| } |
| } |
在数据库中添加表
| create table runoob_tbl |
| ( |
| runoob_id int unsigned auto_increment primary key, |
| runoob_title varchar(100) not null, |
| runoob_author varchar(40) not null, |
| submission_date date null |
| )charset = utf8; |
创建mapper
在mysql.dao添加runoob_tblMapper.java
这个接口名需要和runoob_tblMapper配置的命名空间一致
| |
| public interface runoob_tblMapper { |
| int insert(runoob_tbl tbl); |
| } |
| |
添加入库的单元测试
需要在pom引入junit包
| <dependency> |
| <groupId>junit</groupId> |
| <artifactId>junit</artifactId> |
| <version>4.12</version> |
| <scope>test</scope> |
| </dependency> |
| |
添加ruidmybatistest单元测试,插入一条记录。
| |
| public class ruidmybatistest { |
| |
| @Test |
| public void testGetUserList(){ |
| try |
| { |
| ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext-mysql.xml"); |
| runoob_tblMapper mapper = ctx.getBean("runoob_tblMapper",runoob_tblMapper.class); |
| runoob_tbl tbl = new runoob_tbl(); |
| tbl.setRunoob_author("jake"); |
| tbl.setRunoob_title("redis"); |
| tbl.setSubmission_date(new Date()); |
| int count = mapper.insert(tbl); |
| System.out.println(count); |
| }catch (Exception exception) |
| { |
| System.out.println(exception.getMessage()); |
| } |
| } |
| |
| } |
通过上面配置,每个mapper都需要配置bean,若mapper比较多,配置的就很麻烦,可以通过MapperScannerConfigurer实现自动扫描,而无需配置mapper了
在sqlSessionFactory添加一个mapperLocations属性,映射mapper下所有Mapper结尾的配置。
| <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> |
| |
| <property name="dataSource" ref="druidDataSource"/> |
| <property name="configLocation" value="classpath:mapperConf.xml"/> |
| <property name="mapperLocations" value="classpath:mapper/*Mapper.xml"/> |
| </bean> |
| |
| |
| |
| |
| <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> |
| <property name="basePackage" value="mysql.dao" /> |
| </bean> |
| |
新增一个student表
| create table student |
| ( |
| name varchar(32) null, |
| age int null, |
| id int auto_increment, |
| constraint student_pk |
| primary key (id) |
| ); |
添加studentdto
| |
| public class student { |
| |
| public String id; |
| public String name; |
| public int age; |
| @Override |
| public String toString() { |
| return this.id + "," + this.name + "," + this.age; |
| } |
| |
| public String getId() { |
| return id; |
| } |
| |
| public void setId(String id) { |
| this.id = id; |
| } |
| |
| public String getName() { |
| return name; |
| } |
| |
| public void setName(String name) { |
| this.name = name; |
| } |
| |
| public int getAge() { |
| return age; |
| } |
| |
| public void setAge(int age) { |
| this.age = age; |
| } |
| } |
添加studentdao
| |
| public interface studentMapper { |
| List<runoob_tbl> selectByNames(List<String> name); |
| } |
| |
添加studentmapper.xml
在resources/mapper下添加studentmapper.xml
| |
| <mapper namespace="mysql.dao.studentMapper"> |
| |
| <select id="selectByNames" resultType="mysql.dto.student"> |
| select * from student where name in |
| <foreach item="name" index="index" collection="list" open="(" separator="," close=")">#{name}</foreach> |
| </select> |
| </mapper> |
添加单元测试
| |
| public class studenttest { |
| |
| @Test |
| public void testGetList(){ |
| try |
| { |
| ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext-mysql.xml"); |
| studentMapper mapper = ctx.getBean("studentMapper",studentMapper.class); |
| List<Integer> ages= new ArrayList<Integer>(); |
| ages.add(10); |
| ages.add(20); |
| List<student> blog = mapper.selectByAge(ages); |
| for (student item : blog) { |
| System.out.println(item); |
| } |
| }catch (Exception exception) |
| { |
| System.out.println(exception.getMessage()); |
| } |
| } |
| |
| } |
参考文献
- Maven添加Oracle的依赖及驱动
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· DeepSeek 解答了困扰我五年的技术问题。时代确实变了!
· 本地部署DeepSeek后,没有好看的交互界面怎么行!
· 趁着过年的时候手搓了一个低代码框架
· 推荐一个DeepSeek 大模型的免费 API 项目!兼容OpenAI接口!