文章目录
一、jdcb
环境:最原始
连接数据库
1.注册驱动
2.获取连接
3.获取操作数据库的预处理对象
4.执行sql,得到结果集
5.遍历结果集
6.释放资源
1.注册驱动
方法一:通过系统的属性设置注册驱动
System.setProperty(“jdbc.drivers”,“com.mysql.jdbc.Driver”);
方法二:对参数指定的类进行装载
低耦合,推荐
Class.forName(“com.mysql.jdbc.Driver”);
方法三:实例化com.mysql.jdbc.Driver.class
此方法耦合太高,不推荐
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
2.获取连接
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/maven",
"root","密码");
如果报错:java.sql.SQLException: Unknown initial character set index ‘255’ received from server.
错误原因:驱动与数据库字符集不匹配
解决方法:在参数“jdbc:mysql://localhost:3306/maven”后追加?useUnicode=true&characterEncoding=utf8;若是在mybats中,则追加“?useUnicode=true&characterEncoding=utf8”
3.获取操作数据库的预处理对象
PreparedStatement pstm =conn.prepareStatement("select * from items;");
4.执行sql语句,得到结果集
ResultSet rs=pstm.executeQuery();
5.遍历结果集
while(rs.next()){
System.out.println(rs.getString("name"));
}
二、c3p0(commons-dbutils)
环境:maven 、spring、commons-dbutils、c3p0
方式:bean.xml对象注入
代码查看:G:\java\day02_eesy_02account_xmlioc
即:Spring 框架学习案例一
第一步:依赖搭建
在pom.xml中
<packaging>jar</packaging>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.0.2.RELEASE</version>
</dependency>
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<!--mysql版本注意-->
<version>8.0.18</version>
</dependency>
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.10</version>
</dependency>
</dependencies>
第二步:为对象注入做准备
准备runner对象的set方法的注入
private QueryRunner runner;
public void setRunner(QueryRunner runner) {
this.runner = runner;
}
其他对象的set方法不一一举例了,都是常规
第三步:bean.xml对象注入
<!--业务层service-->
<bean id="accountService" class="com.itheima.service.impl.AccountServiceImpl">
<!--注入dao对象--><!--使用的是set方法注入-->
<property name="accountDao" ref="accoutDao"></property>
</bean>
<bean id="accoutDao" class="com.itheima.dao.impl.AccountDaoImpl">
<!--注入runner对象--><!--使用的是set方法注入-->
<property name="runner" ref="runner"></property>
</bean>
<!--runner 多例,因为多个accoutDao用时,单例会导致线程互相干扰-->
<bean id="runner" class="org.apache.commons.dbutils.QueryRunner" scope="prototype">
<!--注入数据源--><!--使用构造默认函数-->
<constructor-arg name="ds" ref="dataSource"></constructor-arg>
</bean>
<!--配置数据源-->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<!--连接数据库的必备四大信息--><!--"com.mysql.jdbc.Driver"已经过时,可以用但不推荐-->
<property name="driverClass" value="com.mysql.jc.jdbc.Driver"></property>
<!--连接后面加上时区,否则报错,试试 ?serverTimezone=UTC-->
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/eesy?serverTimezone=UTC"></property>
<property name="user" value="root"></property>
<property name="password" value="034676"></property>
</bean>
第四步:test中测试使用
@Test
public void testFindAll() {
//1.获取容器
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
//2.得到业务层对象
IAccountService as =ac.getBean("accountService",IAccountService.class);
//3.执行方法
List<Account> accounts=as.findAllAccount();
for (Account account:accounts){
System.out.println(account);
}
}
三、spring的内置数据源
环境:maven 、spring
代码查看:G:\java\day04_eesy_01jdbctemplate
第一步:依赖搭建
<packaging>jar</packaging>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.0.2.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.0.2.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.0.2.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
</dependencies>
初步:基础用法(可跳过,仅供参考)
package com.itheima.jdbctemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
/**
* JdbcTemplate的最基本用法
*/
public class JdbcTemplateDemol {
public static void main(String[] args) {
//准备数据源c3p0 和 dbcp都可以,下面使用另一种,spring的内置数据源
DriverManagerDataSource ds=new DriverManagerDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost:3306/eesy?useUnicode=true&characterEncoding=utf8");
ds.setUsername("root");
ds.setPassword("034676");
//1.创建JdbcTemplate对象
JdbcTemplate jt =new JdbcTemplate();
//给jt设置数据源
jt.setDataSource(ds);
//2.执行操作
jt.execute("insert into account(name,money)values('ccc',1000) ");
}
}
第二步:Dao的接口和实现类
接口:
package com.itheima.dao;
import com.itheima.domain.Account;
/**
* 账户的持久层接口
*/
public interface IAccountDao {
/**
* 根据id查询账户
* @param accountId
* @return
*/
Account findAccountById(Integer accountId);
/**
* 根据名称查询账户
* @param accountName
* @return
*/
Account findAccountByName(Integer accountName);
/**
* 更新账户
* @param account
*/
void updateAccount(Account account);
}
实现类:
选择一、基于xml配置
package com.itheima.dao.impl;
import com.itheima.dao.IAccountDao;
import com.itheima.domain.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.util.List;
//JdbcDaoSupport本来是要自己写的,但是spring框架提供了现成的,所以我把JdbcDaoSupport注释掉了
//缺点,使用spring框架提供了现成的后,再想通过注解的方式来规定JdbcTemplate,就比较麻烦了因为你不能修改core里面的代码
// ,而自己写是可以的
//总结:如果使用注解方式,那么JdbcDaoSupport自己写
// 如果使用xml方式,那么可以使用core.support.JdbcDaoSupport现成的
public class AccountDaoImpl extends JdbcDaoSupport implements IAccountDao {
public Account findAccountById(Integer accountId) {
List<Account> accounts= getJdbcTemplate().query("select * from account where id=?",new BeanPropertyRowMapper<Account>(Account.class),accountId);
return accounts.isEmpty()?null:accounts.get(0);
}
public Account findAccountByName(Integer accountName) {
List<Account> accounts= getJdbcTemplate().query("select * from account where name=?",new BeanPropertyRowMapper<Account>(Account.class),accountName);
if (accounts.isEmpty()){
return null;
}
if(accounts.size()>1){
throw new RuntimeException("结果集不唯一");
}
return accounts.get(0);
}
public void updateAccount(Account account) {
getJdbcTemplate().update("update account set name =?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
}
}
选择二、基于注释配置
基于注释配置 JdbcDaoSupport要自己写
AccountDaoImpl:
package com.itheima.dao.impl;
import com.itheima.dao.IAccountDao;
import com.itheima.domain.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository(value = "accountDao")
public class AccountDaoImpl extends JdbcDaoSupport implements IAccountDao {
public Account findAccountById(Integer accountId) {
List<Account> accounts= getJdbcTemplate().query("select * from account where id=?",new BeanPropertyRowMapper<Account>(Account.class),accountId);
return accounts.isEmpty()?null:accounts.get(0);
}
public Account findAccountByName(Integer accountName) {
List<Account> accounts= getJdbcTemplate().query("select * from account where name=?",new BeanPropertyRowMapper<Account>(Account.class),accountName);
if (accounts.isEmpty()){
return null;
}
if(accounts.size()>1){
throw new RuntimeException("结果集不唯一");
}
return accounts.get(0);
}
public void updateAccount(Account account) {
getJdbcTemplate().update("update account set name =?,money=? where id=?",account.getName(),account.getMoney(),account.getId());
}
}
JdbcDaoSupport:
package com.itheima.dao.impl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;
import javax.sql.DataSource;
/**
* 此类用于抽取dao中的重复代码
*/
@Repository()
public class JdbcDaoSupport {
@Autowired
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; }
public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; }
public void setDataSource(DataSource dataSource) {
if(jdbcTemplate==null){
jdbcTemplate=createJdbcTemplate(dataSource);
}
}
private JdbcTemplate createJdbcTemplate(DataSource dataSource){
return new JdbcTemplate(dataSource);
}
}
第三步:domain中具体类(和数据库内容对应)
Account类:
package com.itheima.domain;
import java.io.Serializable;
/**
* 账户的实体类
*/
public class Account implements Serializable {
private Integer id;
private String name;
private Float money;
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 Float getMoney() {
return money;
}
public void setMoney(Float money) {
this.money = money;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", money=" + money +
'}';
}
}
第四步:bean.xml配置
都是用了JdbcDaoSupport,基于xml配置是使用了spring现成的JdbcDaoSupport,基于注释配置是自己写的
选择一、基于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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
<!--配置账户的持久层-->
<bean id="accountDao" class="com.itheima.dao.impl.AccountDaoImpl">
<!--<property name="jdbcTemplate" ref="jdbcTemplate"></property>-->
<!--使用-->
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--配置jdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--配置数据源-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/eesy?useUnicode=true&characterEncoding=utf8"></property>
<property name="username" value="root"></property>
<property name="password" value="034676"></property>
</bean>
</beans>
选择二、基于注释配置
<?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.xsd">
<context:component-scan base-package="com.itheima"></context:component-scan>
<!--配置jdbcTemplate-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--配置数据源-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/eesy?useUnicode=true&characterEncoding=utf8"></property>
<property name="username" value="root"></property>
<property name="password" value="034676"></property>
</bean>
</beans>
第五步:业务层使用
package com.itheima.jdbctemplate;
import com.itheima.dao.IAccountDao;
import com.itheima.domain.Account;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
/**
* JdbcTemplate的最基本用法
*/
public class JdbcTemplateDemol4 {
public static void main(String[] args) {
//1.获取容器
ApplicationContext ac = new ClassPathXmlApplicationContext("bean.xml");
//2.得到业务层对象
IAccountDao accountDao =ac.getBean("accountDao",IAccountDao.class);
//3.执行方法
Account account=accountDao.findAccountById(2);
System.out.println(account);
}
}