目录
持久层总图:
1、JdbcTemplate入门
导入jar包:
JdbcDemo1:
package rumen;
import java.beans.PropertyVetoException;
import org.springframework.jdbc.core.JdbcTemplate;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JdbcDemo1 {
public static void main(String[] args) throws PropertyVetoException {
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass("com.mysql.cj.jdbc.Driver");
cpds.setJdbcUrl("jdbc:mysql://localhost:3306/my?serverTimezone=UTC");
cpds.setUser("root");
cpds.setPassword("mysql");
//获取JdbcTemplate对象
JdbcTemplate jt = new JdbcTemplate();
//设置数据源
//两种写法
//第一种
jt.setDataSource(cpds);
//第二种
//JdbcTemplate jt = new JdbcTemplate(cpds);
String sql = "insert into account (name,money) values('jdbc',10000)";
jt.execute(sql);
}
}
2、Spring容器配置
JdbcDemo2:
package rumen;
import java.beans.PropertyVetoException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
public class JdbcDemo2 {
public static void main(String[] args) throws PropertyVetoException {
ApplicationContext ac = new ClassPathXmlApplicationContext("rumen/bean.xml");
JdbcTemplate jt = ac.getBean("jdbcTemplate",JdbcTemplate.class);
String sql = "insert into account (name,money) values('jdbc2',10000)";
jt.execute(sql);
System.out.println("ok");
}
}
bean.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="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/my?serverTimezone=UTC"></property>
<property name="user" value="root"></property>
<property name="password" value="mysql"></property>
</bean>
</beans>
3、查询操作
JdbcDemo3:
package rumen;
import java.beans.PropertyVetoException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
public class JdbcDemo3 {
public static void main(String[] args) throws PropertyVetoException {
ApplicationContext ac = new ClassPathXmlApplicationContext("rumen/bean.xml");
JdbcTemplate jt = ac.getBean("jdbcTemplate",JdbcTemplate.class);
//保存
jt.update("insert into account (name,money) values(?,?)", "jdbc3",2000);
//修改
jt.update("update account set name = ? where id = ?","jdbc4",5);
//删除
jt.update("delete from account where id = ?",6);
//查询所有
List<Account> list = jt.query("select * from account", new AccountRow());
for(Account a : list) {
System.out.println(a.getId()+"\t"+a.getName()+"\t"+a.getMoney());
}
}
}
class AccountRow implements RowMapper<Account>{
@Override
public Account mapRow(ResultSet rs, int arg1) throws SQLException {
Account account = new Account();
account.setId(rs.getInt(1));
account.setName(rs.getString(2));
account.setMoney(rs.getFloat(3));
return account;
}
}
查询一个数据:
//查询一个
//第一种:
List<Account> list2 = jt.query("select * from account where id = ?", new AccountRow(),1);
System.out.println(list2.get(0));
//第二种:
Account a = jt.query("select * from account where id = ?", new AccountResult(),1);
System.out.println(a);
class AccountResult implements ResultSetExtractor<Account>{
@Override
public Account extractData(ResultSet rs) throws SQLException, DataAccessException {
Account account = null;
if(rs.next()) {
account = new Account();
account.setId(rs.getInt(1));
account.setName(rs.getString(2));
account.setMoney(rs.getFloat(3));
}
return account;
}
}
注:RowMapper接口有一个实现类BeanPropertyRowMapper用来封装,简化过程
使用条件:实体类中(Account)中的set方法和数据库中的列名要保存一致 setName = name
List<Account> list = jt.query("select * from account", new BeanPropertyRowMapper<Account>(Account.class));
for(Account a : list) {
System.out.println(a.getId()+"\t"+a.getName()+"\t"+a.getMoney());
}
查询一行一列:当我们sql语句使用了聚合函数,并且没有 order by子句时,返回的都是一行一列的结果
int i = jt.queryForObject("select count(*) from account", Integer.class);
System.out.println(i);
4、使用JdbcTemplate配置dao
bean.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="rumen.AccountDaoImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/my?serverTimezone=UTC"></property>
<property name="user" value="root"></property>
<property name="password" value="mysql"></property>
</bean>
</beans>
AccountDaoImpl:
package rumen;
import java.util.List;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
public class AccountDaoImpl implements IAccountDao{
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Override
public Account findById(int id) {
List<Account> list = jdbcTemplate.query("select * from account where id = ?",
new BeanPropertyRowMapper<Account>(Account.class),id );
return list.isEmpty()?null:list.get(0);
}
@Override
public Account findByName(String name) {
List<Account> list = jdbcTemplate.query("select * from account where name = ?",
new BeanPropertyRowMapper<Account>(Account.class),name);
if(list.isEmpty()) {
return null;
}
if(list.size()>1) {
throw new RuntimeException("账户名称不为一");
}
System.out.println(list.size());
return list.get(0);
}
@Override
public void update(Account account) {
jdbcTemplate.update("update account set name = ? where id = ?",account.getName(),account.getId());
}
}
JdbcDemo4:
package rumen;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class JdbcDemo4 {
public static void main(String[] args) {
ApplicationContext ac = new ClassPathXmlApplicationContext("rumen/bean.xml");
IAccountDao ad = ac.getBean("accountDao",IAccountDao.class);
Account account = ad.findById(7);
System.out.println(account);
Account account2 = new Account();
account2.setName("jdbc2");
account2.setId(7);
ad.update(account2);
Account account3 = ad.findByName("jdbc");
System.out.println(account3);
}
}
5、问题
重复代码:
解决:
JdbcDao:
package rumen;
import org.springframework.jdbc.core.JdbcTemplate;
public class JdbcDao {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
}
AccountDaoImpl:
package rumen;
import java.util.List;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
public class AccountDaoImpl extends JdbcDao implements IAccountDao{
@Override
public Account findById(int id) {
List<Account> list = getJdbcTemplate().query("select * from account where id = ?",
new BeanPropertyRowMapper<Account>(Account.class),id );
return list.isEmpty()?null:list.get(0);
}
@Override
public Account findByName(String name) {
List<Account> list = getJdbcTemplate().query("select * from account where name = ?",
new BeanPropertyRowMapper<Account>(Account.class),name);
if(list.isEmpty()) {
return null;
}
if(list.size()>1) {
throw new RuntimeException("账户名称不为一");
}
System.out.println(list.size());
return list.get(0);
}
@Override
public void update(Account account) {
getJdbcTemplate().update("update account set name = ? where id = ?",account.getName(),account.getId());
}
}
简化:
package rumen;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class JdbcDao {
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
if(jdbcTemplate == null) {
createJdbc(dataSource);
}
}
public void createJdbc(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
}
<bean id="accountDao" class="rumen.AccountDaoImpl">
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/my?serverTimezone=UTC"></property>
<property name="user" value="root"></property>
<property name="password" value="mysql"></property>
</bean>
spring提供了一个JdbcDaoSupport类,可以产生以上相同的效果。
区别:如果用注解进行注入,用继承JdbcDaoSupport会很麻烦。
6、Spring支持的数据源配置
第一种:
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/my?serverTimezone=UTC"></property>
<property name="user" value="root"></property>
<property name="password" value="mysql"></property>
</bean>
第二种:
<!-- spring提供的数据源 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/my?serverTimezone=UTC"></property>
<property name="username" value="root"></property>
<property name="password" value="mysql"></property>
</bean>
第三种:
导入:
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/my?serverTimezone=UTC"></property>
<property name="username" value="root"></property>
<property name="password" value="mysql"></property>
</bean>