JDBC
一、导包
<!--mysql驱动包-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
二、步骤:
-
加载驱动
-
用户信息username,password和url
-
创建连接
-
通过连接获取执行sql的对象,执行sql,处理结果
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=true";
String username ="root";
String password ="123456";
Connection connection = DriverManager.getConnection(url,username,password);
Statement statement= connection.createStatement(); -
释放连接
三、编写配置文件,提取工具类
-
将数据库连接的信息写到配置文件db.properties中
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456 -
提取工具类JdbcUtils
static{
Properties p = new Properties();
try {
properties.load(JdbcUtils.class.getClassLoader()
.getResourceAsStream("db.properties"));
String driver = properties.getProperty("driver");
String url = properties.getProperty("url");
String username = properties.getProperty("username");
String password = properties.getProperty("password");
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException( "读取db.properties失败",e);
} catch (ClassNotFoundException e) {
e.printStackTrace();
throw new RuntimeException("找不到这个驱动类",e);
}
}
public static Connection getConnection()throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public static void close(Connection conn) {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("关闭连接失败",e);
}
}
}
四、增加连接池
1.DBCP
-
导包
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-pool/commons-pool -->
<dependency>
<groupId>commons-pool</groupId>
<artifactId>commons-pool</artifactId>
<version>1.6</version>
</dependency> -
编写配置文件
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
#!-- 初始化连接 --
initialSize=10
#最大连接数量
maxActive=50
#!-- 最大空闲连接 --
maxIdle=20
#!-- 最小空闲连接 --
minIdle=5
#!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:【属性名=property;】
#注意:user 与 password 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8
#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的只读(read-only)状态。
#如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix)
defaultReadOnly=
#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED -
重构工具类
static DataSource dataSource = null;
static{
Properties properties = new Properties();
try {
properties.load(JdbcUtils.class.getClassLoader()
.getResourceAsStream("db.properties"));
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("读取db.properties失败",e);
}
}
/**
* 由连接池创建的连接,其实现类由连接池提供.
*/
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
/**
* 连接池提供的实现类,其close方法内部逻辑是,
* 将连接归还给连接池,即它会清空连接对象中的数据,
* 并且将连接标记为空闲态.
*/
public static void close(Connection conn) {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException( "归还连接失败", e);
}
}
}
2.C3P0
-
导包
<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.mchange/mchange-commons-java -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>mchange-commons-java</artifactId>
<version>0.2.19</version>
</dependency> -
编写配置文件
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!--
c3p0的缺省(默认)配置
如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省(默认)
default-config
-->
<default-config>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&uesSSL=true&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<name-config name="MySQL">
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&uesSSL=true&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</name-config>
</c3p0-config> -
重构工具类
static DataSource dataSource = null;
static{
try{//xml不用读取,会自动匹配
//创建数据源 工厂模式创建
dataSource = new ComboPooledDataSource("MySQL");//配置文件写法
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 由连接池创建的连接,其实现类由连接池提供.
*/
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
/**
* 连接池提供的实现类,其close方法内部逻辑是,
* 将连接归还给连接池,即它会清空连接对象中的数据,
* 并且将连接标记为空闲态.
*/
public static void close(Connection conn) {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException( "归还连接失败", e);
}
}
}小结:无论使用什么数据源,本质都是一样的,DataSource接口不变,方法不变。
Mybatis
一、导包
<!--导入依赖-->
<dependencies>
<!--mysqlq驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
</dependencies>
<!--Maven静态资源过滤-->
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
二、步骤
-
通过SqlSessionFactoryBuilder获取SqlSessionFactory
-
通过SqlSessionFactory 获取SqlSession
-
通过SqlSession获取Mapper实现类
//使用Mybatis第一步 :获取sqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactoryBuilder bulider = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = builder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class); -
通过Mapper实现类就可以进行增删改查操作。
三、重构步骤
-
编写mybatis核心配置文件mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--configuration核心配置文件-->
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?userSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
</configuration> -
编写工具类MybatisUtis
//sqlSessionFactory --> sqlSession
public class MybatisUtils {
static SqlSessionFactory sqlSessionFactory = null;
static {
try {
//使用Mybatis第一步 :获取sqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
//既然有了 SqlSessionFactory,顾名思义,我们可以从中获得 SqlSession 的实例.
// SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
} -
编写接口UserMapper,UserMapper.xml
public interface UserMapper {
public List<User> getUserList();
}<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace=绑定一个指定的Dao/Mapper接口-->
<mapper namespace="com.kuang.dao.UserDao">
<select id="getUserList" resultType="com.kuang.pojo.User">
select * from USER
</select>
</mapper> -
将Mapper添加到mybatis-config.xml中
<mappers>
<mapper class="com.kuang.dao.UserMapper"/>
</mappers>注意点:
-
接口和他的Mapper配置文件必须同名
-
接口和他的Mapper配置文件必须在同一个包下
-
-
测试
@Test
public void test(){
//1.获取SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//2.执行SQL
// 方式一:getMapper
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = userDao.getUserList();
for (User user : userList) {
System.out.println(user);
}
//关闭sqlSession
sqlSession.close();
}
Spring
Spring整合Mybatis 三种方式:
-
SqlSessionTemplate(不常用,了解即可)
-
SqlSessionDaoSupport(也不常用,了解)
-
扫描包(重点)
一、导包
<!--导入依赖-->
<dependencies>
<!--mysqlq驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!-- 数据库连接池 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>mchange-commons-java</artifactId>
<version>0.2.19</version>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<!--spring整合mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>2.0.4</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.2.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>5.2.3.RELEASE</version>
</dependency>
<!--Servlet - JSP -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>2.2</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!--Spring AOP-->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.4</version>
</dependency>
</dependencies>
<!--Maven静态资源过滤-->
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
二、步骤
-
在spring配置文件中:读取数据库配置文件
-
配置数据源
-
配置SQLSessionFactory
-
配置扫描Dao接口包
<beans>
<!-- 配置整合mybatis -->
<!-- 1.关联数据库文件 -->
<context:property-placeholder location="classpath:database.properties"/>
<!-- 2.数据库连接池 -->
<!--数据库连接池
dbcp 半自动化操作 不能自动连接
c3p0 自动化操作(自动的加载配置文件 并且设置到对象里面)
-->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<!-- 配置连接池属性 -->
<property name="driverClass" value="${jdbc.driver}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<!-- c3p0连接池的私有属性 -->
<property name="maxPoolSize" value="30"/>
<property name="minPoolSize" value="10"/>
<!-- 关闭连接后不自动commit -->
<property name="autoCommitOnClose" value="false"/>
<!-- 获取连接超时时间 -->
<property name="checkoutTimeout" value="10000"/>
<!-- 当获取连接失败重试次数 -->
<property name="acquireRetryAttempts" value="2"/>
</bean>
<!-- 3.配置SqlSessionFactory对象 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 注入数据库连接池 -->
<property name="dataSource" ref="dataSource"/>
<!-- 配置MyBaties全局配置文件:mybatis-config.xml -->
<property name="configLocation" value="classpath:mybatis-config.xml"/>
</bean>
<!-- 4.配置扫描Dao接口包,动态实现Dao接口注入到spring容器中 -->
<!--解释 :https://www.cnblogs.com/jpfss/p/7799806.html-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!-- 注入sqlSessionFactory -->
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
<!-- 给出需要扫描Dao接口包 -->
<property name="basePackage" value="com.lele.dao"/>
</bean>
</beans> -
通过扫描Dao接口包获取UserMapper实现类
ApplicationContext ac = new ClassPathXmlApplicationContext( "spring-mybatis.xml");
UserMapper userMapper = ac.getBean("userMapper", UserMapper.class); -
通过UserMapper实现类就可以进行增删改查操作。
三、 重构步骤
-
编写数据库配置文件db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssmbuild?useSSL=true&useUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=123456 -
编写mybatis核心配置文件mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<package name="com.lele.pojo"/>
</typeAliases>
<mappers>
<mapper resource="com/lele/dao/UserMapper.xml"/>
</mappers>
</configuration> -
编写Spring配置文件spring-mybatis.xml(略)
-
编写实体类,UserMapper接口,UserMapper.xml
-
将UserMapper.xml关联到mybatis-config.xml中。
-
可以直接从Spring容器中获得UserMapper实现类;
-
通过UserMapper实现类就可以进行增删改查操作。
ApplicationContext ac = new ClassPathXmlApplicationContext( "spring-mybatis.xml");
UserMapper userMapper = ac.getBean("userMapper", UserMapper.class);
List<User> users = dao.findAll();
System.out.println(users);
SpringBoot
一、导包
<!--导入依赖-->
<dependencies>
<!--mysqlq驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<!--mybatis-spring 整合包-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-start-jdbc</artifactId>
</dependency>
</dependencies>
二、步骤
-
编写springboot配置文件application.yml
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
driver-class-name: com.mysql.cj.jdbc.Driver -
编写实体类,UserMapper接口,UserMapper.xml
-
在application.xml中配置mybatis
#整合mybatis
mybatis:
type-aliases-package: com.lele.pojo
mapper-locations: classpath:mybatis/mapper/*.xml -
获取UserMapper实现类
@autowired
UserMapper userMapper; -
通过UserMapper实现类就可以进行增删改查操作。
三、扩展
-
如何在SpringBoot中添加一个bean,如何绑定数据源。
//Druid强大之处
@Configuration
public class DruidConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource")//与配置文件DataSource绑定
public DataSource druidDataSource(){
return new DruidDataSource();
}
} -